Re: [sqlite] In memory v/s tmpfs
> But I sqlite can still check the number of db connections open Even though as Michael have shown this is possible (with guaranteed results only when run as root) it will still have races. Because if you see only one process accessing database file at the end of transaction and only one process at the beginning of the next one it doesn't guarantee that there was no other processes writing to database between those checks. And btw I think scanning all contents in /proc directory will be much much slower than checking for hot-journal. Pavel On Wed, Aug 10, 2011 at 2:13 PM, Sreekumar TP wrote: > Ok.. But I sqlite can still check the number of db connections open and > optimise the checking=> as long as there is only one writer, do not check > for hot journals. > > Can a database have two connections opened with two different journal modes > ? > > On Wed, Aug 10, 2011 at 11:35 PM, Pavel Ivanov wrote: > >> > If you have one reader and many writers, consider PRAGMA >> journal_mode=WAL; >> >> Richard meant "one writer and many readers" of course. >> >> >> If the other process opens the db connection as read_only, will the hot >> >> journal check be still done (during queries operations of the second >> >> process)? >> >> >> > >> > How is the second process suppose to know whether or not the other >> process >> > used read_only? >> >> And even those who opened db connection as read_only will check for >> hot journal before executing any query. >> >> >> Pavel >> >> On Wed, Aug 10, 2011 at 1:44 PM, Richard Hipp wrote: >> > On Wed, Aug 10, 2011 at 1:35 PM, Sreekumar TP > >wrote: >> > >> >> Ok, its getting a bit clear. >> >> If there is only one process that is writing to the database, but has >> more >> >> than one process that reads the database, the locking mode can still be >> >> exclusive ? >> >> >> > >> > PRAGMA locking_mode=EXCLUSIVE; means "exclusive'. Only one process can >> > access. >> > >> > If you have one reader and many writers, consider PRAGMA >> journal_mode=WAL; >> > >> > >> > >> >> If the other process opens the db connection as read_only, will the hot >> >> journal check be still done (during queries operations of the second >> >> process)? >> >> >> > >> > How is the second process suppose to know whether or not the other >> process >> > used read_only? >> > >> > >> >> >> >> >> >> >> >> >> >> On Wed, Aug 10, 2011 at 10:54 PM, Richard Hipp wrote: >> >> >> >> > On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin >> >> > wrote: >> >> > >> >> > > >> >> > > On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote: >> >> > > >> >> > > > Thanks for the explanation. The journal mode was OFF which means >> >> there >> >> > > is >> >> > > > no journal file created. So why is it the check still performed ? >> >> > > >> >> > > Because the journal mode might have been 'ON' the last time that >> >> > database >> >> > > was used. >> >> > > >> >> > >> >> > More importantly, the journal mode might be 'ON' in the other process >> >> that >> >> > crashed in the middle of a transaction. >> >> > >> >> > >> >> > >> >> > > >> >> > > > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp >> >> wrote: >> >> > > > >> >> > > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP < >> >> > sreekumar...@gmail.com >> >> > > >>> wrote: >> >> > > >> >> >> > > >>> The 179148 failures on 'access' system call is due to access >> check >> >> of >> >> > > two >> >> > > >>> files - the journal file and the wal-file. >> >> > > >> >> > > Well, the file is obviously not being opened 179148 times. I >> thought >> >> it >> >> > > checked for these files only when sqlite3_open() was being run. Is >> it >> >> > > checking for these files every time a command is executed ? Surely >> >> that >> >> > is >> >> > > unnecessary and a source of a large reduction in speed. >> >> > > >> >> > >> >> > SQLite must check for hot journals at the start of each transaction. >> >> > >> >> > Or, more precisely, SQLite must check for hot journals whenever it >> starts >> >> > reading the database again after a period of time when another process >> >> > might >> >> > have been writing to the database (and might have crashed while >> writing). >> >> > >> >> > That's when PRAGMA locking_mode=EXCLUSIVE blocks the hot-journal >> checks. >> >> > If >> >> > your process has exclusive access to the database, it is not possible >> for >> >> > another process to write to the database (and crash in the middle of >> >> > writing). >> >> > >> >> > >> >> > >> >> > > >> >> > > Simon. >> >> > > ___ >> >> > > sqlite-users mailing list >> >> > > sqlite-users@sqlite.org >> >> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > >> >> > >> >> > >> >> > >> >> > -- >> >> > D. Richard Hipp >> >> > d...@sqlite.org >> >> > ___ >> >> > sqlite-users mailing list >> >> > sqlite-users@sqlite.org >> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > >> >>
Re: [sqlite] In memory v/s tmpfs
Under Linux: man fuser Will detect other processes which have the DB open. It's done by looking at all the /proc entries. Under windows http://www.codeproject.com/KB/IP/OpenFiles.aspx Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org] Sent: Wednesday, August 10, 2011 1:27 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] In memory v/s tmpfs On Wed, Aug 10, 2011 at 2:13 PM, Sreekumar TP wrote: > But I sqlite can still check the number of db connections open > No it cannot. In unix, there is no way for one process to know whether or not another process has a particular file open. And if there is a mechanism to do that in windows, I'm not familiar with it. And when you think about it, any mechanism that allowed one process to spy on the file connections of another unrelated process would probably be considered a security risk and hence undesirable. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
Oh Yes. I missed that point ! On Wed, Aug 10, 2011 at 11:57 PM, Richard Hipp wrote: > On Wed, Aug 10, 2011 at 2:13 PM, Sreekumar TP >wrote: > > > But I sqlite can still check the number of db connections open > > > > > No it cannot. > > In unix, there is no way for one process to know whether or not another > process has a particular file open. And if there is a mechanism to do that > in windows, I'm not familiar with it. > > And when you think about it, any mechanism that allowed one process to spy > on the file connections of another unrelated process would probably be > considered a security risk and hence undesirable. > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
On Wed, Aug 10, 2011 at 2:13 PM, Sreekumar TP wrote: > But I sqlite can still check the number of db connections open > No it cannot. In unix, there is no way for one process to know whether or not another process has a particular file open. And if there is a mechanism to do that in windows, I'm not familiar with it. And when you think about it, any mechanism that allowed one process to spy on the file connections of another unrelated process would probably be considered a security risk and hence undesirable. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
Ok.. But I sqlite can still check the number of db connections open and optimise the checking=> as long as there is only one writer, do not check for hot journals. Can a database have two connections opened with two different journal modes ? On Wed, Aug 10, 2011 at 11:35 PM, Pavel Ivanov wrote: > > If you have one reader and many writers, consider PRAGMA > journal_mode=WAL; > > Richard meant "one writer and many readers" of course. > > >> If the other process opens the db connection as read_only, will the hot > >> journal check be still done (during queries operations of the second > >> process)? > >> > > > > How is the second process suppose to know whether or not the other > process > > used read_only? > > And even those who opened db connection as read_only will check for > hot journal before executing any query. > > > Pavel > > On Wed, Aug 10, 2011 at 1:44 PM, Richard Hipp wrote: > > On Wed, Aug 10, 2011 at 1:35 PM, Sreekumar TP >wrote: > > > >> Ok, its getting a bit clear. > >> If there is only one process that is writing to the database, but has > more > >> than one process that reads the database, the locking mode can still be > >> exclusive ? > >> > > > > PRAGMA locking_mode=EXCLUSIVE; means "exclusive'. Only one process can > > access. > > > > If you have one reader and many writers, consider PRAGMA > journal_mode=WAL; > > > > > > > >> If the other process opens the db connection as read_only, will the hot > >> journal check be still done (during queries operations of the second > >> process)? > >> > > > > How is the second process suppose to know whether or not the other > process > > used read_only? > > > > > >> > >> > >> > >> > >> On Wed, Aug 10, 2011 at 10:54 PM, Richard Hipp wrote: > >> > >> > On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin > >> > wrote: > >> > > >> > > > >> > > On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote: > >> > > > >> > > > Thanks for the explanation. The journal mode was OFF which means > >> there > >> > > is > >> > > > no journal file created. So why is it the check still performed ? > >> > > > >> > > Because the journal mode might have been 'ON' the last time that > >> > database > >> > > was used. > >> > > > >> > > >> > More importantly, the journal mode might be 'ON' in the other process > >> that > >> > crashed in the middle of a transaction. > >> > > >> > > >> > > >> > > > >> > > > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp > >> wrote: > >> > > > > >> > > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP < > >> > sreekumar...@gmail.com > >> > > >>> wrote: > >> > > >> > >> > > >>> The 179148 failures on 'access' system call is due to access > check > >> of > >> > > two > >> > > >>> files - the journal file and the wal-file. > >> > > > >> > > Well, the file is obviously not being opened 179148 times. I > thought > >> it > >> > > checked for these files only when sqlite3_open() was being run. Is > it > >> > > checking for these files every time a command is executed ? Surely > >> that > >> > is > >> > > unnecessary and a source of a large reduction in speed. > >> > > > >> > > >> > SQLite must check for hot journals at the start of each transaction. > >> > > >> > Or, more precisely, SQLite must check for hot journals whenever it > starts > >> > reading the database again after a period of time when another process > >> > might > >> > have been writing to the database (and might have crashed while > writing). > >> > > >> > That's when PRAGMA locking_mode=EXCLUSIVE blocks the hot-journal > checks. > >> > If > >> > your process has exclusive access to the database, it is not possible > for > >> > another process to write to the database (and crash in the middle of > >> > writing). > >> > > >> > > >> > > >> > > > >> > > Simon. > >> > > ___ > >> > > sqlite-users mailing list > >> > > sqlite-users@sqlite.org > >> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > > >> > > >> > > >> > > >> > -- > >> > D. Richard Hipp > >> > d...@sqlite.org > >> > ___ > >> > sqlite-users mailing list > >> > sqlite-users@sqlite.org > >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > > > > > -- > > D. Richard Hipp > > d...@sqlite.org > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
> If you have one reader and many writers, consider PRAGMA journal_mode=WAL; Richard meant "one writer and many readers" of course. >> If the other process opens the db connection as read_only, will the hot >> journal check be still done (during queries operations of the second >> process)? >> > > How is the second process suppose to know whether or not the other process > used read_only? And even those who opened db connection as read_only will check for hot journal before executing any query. Pavel On Wed, Aug 10, 2011 at 1:44 PM, Richard Hipp wrote: > On Wed, Aug 10, 2011 at 1:35 PM, Sreekumar TP wrote: > >> Ok, its getting a bit clear. >> If there is only one process that is writing to the database, but has more >> than one process that reads the database, the locking mode can still be >> exclusive ? >> > > PRAGMA locking_mode=EXCLUSIVE; means "exclusive'. Only one process can > access. > > If you have one reader and many writers, consider PRAGMA journal_mode=WAL; > > > >> If the other process opens the db connection as read_only, will the hot >> journal check be still done (during queries operations of the second >> process)? >> > > How is the second process suppose to know whether or not the other process > used read_only? > > >> >> >> >> >> On Wed, Aug 10, 2011 at 10:54 PM, Richard Hipp wrote: >> >> > On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin >> > wrote: >> > >> > > >> > > On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote: >> > > >> > > > Thanks for the explanation. The journal mode was OFF which means >> there >> > > is >> > > > no journal file created. So why is it the check still performed ? >> > > >> > > Because the journal mode might have been 'ON' the last time that >> > database >> > > was used. >> > > >> > >> > More importantly, the journal mode might be 'ON' in the other process >> that >> > crashed in the middle of a transaction. >> > >> > >> > >> > > >> > > > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp >> wrote: >> > > > >> > > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP < >> > sreekumar...@gmail.com >> > > >>> wrote: >> > > >> >> > > >>> The 179148 failures on 'access' system call is due to access check >> of >> > > two >> > > >>> files - the journal file and the wal-file. >> > > >> > > Well, the file is obviously not being opened 179148 times. I thought >> it >> > > checked for these files only when sqlite3_open() was being run. Is it >> > > checking for these files every time a command is executed ? Surely >> that >> > is >> > > unnecessary and a source of a large reduction in speed. >> > > >> > >> > SQLite must check for hot journals at the start of each transaction. >> > >> > Or, more precisely, SQLite must check for hot journals whenever it starts >> > reading the database again after a period of time when another process >> > might >> > have been writing to the database (and might have crashed while writing). >> > >> > That's when PRAGMA locking_mode=EXCLUSIVE blocks the hot-journal checks. >> > If >> > your process has exclusive access to the database, it is not possible for >> > another process to write to the database (and crash in the middle of >> > writing). >> > >> > >> > >> > > >> > > Simon. >> > > ___ >> > > sqlite-users mailing list >> > > sqlite-users@sqlite.org >> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > >> > >> > >> > >> > -- >> > D. Richard Hipp >> > d...@sqlite.org >> > ___ >> > sqlite-users mailing list >> > sqlite-users@sqlite.org >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
On Wed, Aug 10, 2011 at 1:35 PM, Sreekumar TP wrote: > Ok, its getting a bit clear. > If there is only one process that is writing to the database, but has more > than one process that reads the database, the locking mode can still be > exclusive ? > PRAGMA locking_mode=EXCLUSIVE; means "exclusive'. Only one process can access. If you have one reader and many writers, consider PRAGMA journal_mode=WAL; > If the other process opens the db connection as read_only, will the hot > journal check be still done (during queries operations of the second > process)? > How is the second process suppose to know whether or not the other process used read_only? > > > > > On Wed, Aug 10, 2011 at 10:54 PM, Richard Hipp wrote: > > > On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin > > wrote: > > > > > > > > On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote: > > > > > > > Thanks for the explanation. The journal mode was OFF which means > there > > > is > > > > no journal file created. So why is it the check still performed ? > > > > > > Because the journal mode might have been 'ON' the last time that > > database > > > was used. > > > > > > > More importantly, the journal mode might be 'ON' in the other process > that > > crashed in the middle of a transaction. > > > > > > > > > > > > > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp > wrote: > > > > > > > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP < > > sreekumar...@gmail.com > > > >>> wrote: > > > >> > > > >>> The 179148 failures on 'access' system call is due to access check > of > > > two > > > >>> files - the journal file and the wal-file. > > > > > > Well, the file is obviously not being opened 179148 times. I thought > it > > > checked for these files only when sqlite3_open() was being run. Is it > > > checking for these files every time a command is executed ? Surely > that > > is > > > unnecessary and a source of a large reduction in speed. > > > > > > > SQLite must check for hot journals at the start of each transaction. > > > > Or, more precisely, SQLite must check for hot journals whenever it starts > > reading the database again after a period of time when another process > > might > > have been writing to the database (and might have crashed while writing). > > > > That's when PRAGMA locking_mode=EXCLUSIVE blocks the hot-journal checks. > > If > > your process has exclusive access to the database, it is not possible for > > another process to write to the database (and crash in the middle of > > writing). > > > > > > > > > > > > Simon. > > > ___ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > -- > > D. Richard Hipp > > d...@sqlite.org > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
Ok, its getting a bit clear. If there is only one process that is writing to the database, but has more than one process that reads the database, the locking mode can still be exclusive ? If the other process opens the db connection as read_only, will the hot journal check be still done (during queries operations of the second process)? On Wed, Aug 10, 2011 at 10:54 PM, Richard Hipp wrote: > On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin > wrote: > > > > > On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote: > > > > > Thanks for the explanation. The journal mode was OFF which means there > > is > > > no journal file created. So why is it the check still performed ? > > > > Because the journal mode might have been 'ON' the last time that > database > > was used. > > > > More importantly, the journal mode might be 'ON' in the other process that > crashed in the middle of a transaction. > > > > > > > > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp wrote: > > > > > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP < > sreekumar...@gmail.com > > >>> wrote: > > >> > > >>> The 179148 failures on 'access' system call is due to access check of > > two > > >>> files - the journal file and the wal-file. > > > > Well, the file is obviously not being opened 179148 times. I thought it > > checked for these files only when sqlite3_open() was being run. Is it > > checking for these files every time a command is executed ? Surely that > is > > unnecessary and a source of a large reduction in speed. > > > > SQLite must check for hot journals at the start of each transaction. > > Or, more precisely, SQLite must check for hot journals whenever it starts > reading the database again after a period of time when another process > might > have been writing to the database (and might have crashed while writing). > > That's when PRAGMA locking_mode=EXCLUSIVE blocks the hot-journal checks. > If > your process has exclusive access to the database, it is not possible for > another process to write to the database (and crash in the middle of > writing). > > > > > > > Simon. > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin wrote: > > On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote: > > > Thanks for the explanation. The journal mode was OFF which means there > is > > no journal file created. So why is it the check still performed ? > > Because the journal mode might have been 'ON' the last time that database > was used. > More importantly, the journal mode might be 'ON' in the other process that crashed in the middle of a transaction. > > > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp wrote: > > > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP >>> wrote: > >> > >>> The 179148 failures on 'access' system call is due to access check of > two > >>> files - the journal file and the wal-file. > > Well, the file is obviously not being opened 179148 times. I thought it > checked for these files only when sqlite3_open() was being run. Is it > checking for these files every time a command is executed ? Surely that is > unnecessary and a source of a large reduction in speed. > SQLite must check for hot journals at the start of each transaction. Or, more precisely, SQLite must check for hot journals whenever it starts reading the database again after a period of time when another process might have been writing to the database (and might have crashed while writing). That's when PRAGMA locking_mode=EXCLUSIVE blocks the hot-journal checks. If your process has exclusive access to the database, it is not possible for another process to write to the database (and crash in the middle of writing). > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
The journal mode was always OFF when the code executes. The db was/is never opened with a journal. I havent analysed when exactly these calls are made, but definitely, it quite a lot.. On Wed, Aug 10, 2011 at 10:39 PM, Simon Slavin wrote: > > On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote: > > > Thanks for the explanation. The journal mode was OFF which means there > is > > no journal file created. So why is it the check still performed ? > > Because the journal mode might have been 'ON' the last time that database > was used. > > > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp wrote: > > > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP >>> wrote: > >> > >>> The 179148 failures on 'access' system call is due to access check of > two > >>> files - the journal file and the wal-file. > > Well, the file is obviously not being opened 179148 times. I thought it > checked for these files only when sqlite3_open() was being run. Is it > checking for these files every time a command is executed ? Surely that is > unnecessary and a source of a large reduction in speed. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote: > Thanks for the explanation. The journal mode was OFF which means there is > no journal file created. So why is it the check still performed ? Because the journal mode might have been 'ON' the last time that database was used. > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp wrote: > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP >> wrote: >> >>> The 179148 failures on 'access' system call is due to access check of two >>> files - the journal file and the wal-file. Well, the file is obviously not being opened 179148 times. I thought it checked for these files only when sqlite3_open() was being run. Is it checking for these files every time a command is executed ? Surely that is unnecessary and a source of a large reduction in speed. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
Thanks for the explanation. The journal mode was OFF which means there is
no journal file created. So why is it the check still performed ?
On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp wrote:
> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP >wrote:
>
> > The 179148 failures on 'access' system call is due to access check of
> two
> > files - the journal file and the wal-file.
> > The journal mode was OFF as also WAL mode.
> > Why is sqlite checking access permissions for this file 179140 times?
> > Removing or optimising this will make it faster by 20% atleast!
> >
>
>
> SQLite must check for the existence of hot journals that were left behind
> by
> other processes that were writing to the database but crashed before they
> finished. If SQLite did not check for these, and if other processes did
> crash in the middle of a transaction, the database file would go corrupt.
>
> If your process is the only one accessing the database file, you can
> disable
> the journal-checks by doing:
>
> PRAGMA locking_mode=EXCLUSIVE;
>
>
>
>
> >
> >
> > access("/dev/shm/test.db-journal", F_OK) = -1 ENOENT (No such file or
> > directory)
> >
> > access("/dev/shm/test.db-wal", F_OK) = -1 ENOENT (No such file or
> > directory)
> >
> > -Sreekumar
> > On Wed, Aug 10, 2011 at 5:28 PM, Sreekumar TP > >wrote:
> >
> > > Hi,
> > >
> > > I have the results from the tests (below). Alot of the time is spent
> in
> > > checking file permissions and locking the file (40 %).
> > >
> > >
> > > Inmem
> > >
> > > % time seconds usecs/call callserrors syscall
> > >
> > > -- --- --- - -
> > >
> > > 28.530.124727 1118524 write
> > >
> > > 23.420.102382 0414624 gettimeofday
> > >
> > > 20.780.090840 1 76513 read
> > >
> > > 13.720.059977 0191255 _llseek
> > >
> > >
> > >
> > > Db in tmpfs
> > >
> > > % time seconds usecs/call callserrors syscall
> > >
> > > -- --- --- - -
> > >
> > > 21.830.257073 1263306 write
> > >
> > > 21.180.249488 1179148179148 access
> > >
> > > 20.610.242725 0509292 fcntl64
> > >
> > > 13.040.153551 0448720 _llseek
> > >
> > > 9.440.94 1189370 read
> > >
> > > 8.160.096124 0414624 gettimeofday
> > > 2.550.033750 8 fdatasync
> > >
> > > On Tue, Aug 9, 2011 at 7:34 PM, Pavel Ivanov
> > wrote:
> > >
> > >> > Journal mode is WAL
> > >>
> > >> I believe in-memory database can't have journal mode WAL. So you
> > >> compare completely different settings.
> > >>
> > >>
> > >> Pavel
> > >>
> > >>
> > >> On Tue, Aug 9, 2011 at 5:15 AM, wrote:
> > >> >
> > >> > Journal mode is WAL
> > >> >
> > >> >
> > >> > --Original Message--
> > >> > From: Roger Binns
> > >> > Sender: sqlite-users-boun...@sqlite.org
> > >> > To: General Discussion of SQLite Database
> > >> > ReplyTo: General Discussion of SQLite Database
> > >> > Subject: Re: [sqlite] In memory v/s tmpfs
> > >> > Sent: Aug 9, 2011 2:42 PM
> > >> >
> > >> > -BEGIN PGP SIGNED MESSAGE-
> > >> > Hash: SHA1
> > >> >
> > >> > On 08/08/2011 06:34 PM, sreekumar...@gmail.com wrote:
> > >> >> From the point of view of performance, I expected similar
> performance
> > ,
> > >> tmpfs being a little slower due to filesystem overhead. However, the
> > >> operations on tmpfs was much slower than expected.
> > >> >
> > >> > Using tmpfs requires many kernel calls which is considerably more
> > effort
> > >> > than the occasional malloc call. Additionally files have to be
> > locked,
> > >> > journals made etc (you didn'
Re: [sqlite] In memory v/s tmpfs
On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP wrote:
> The 179148 failures on 'access' system call is due to access check of two
> files - the journal file and the wal-file.
> The journal mode was OFF as also WAL mode.
> Why is sqlite checking access permissions for this file 179140 times?
> Removing or optimising this will make it faster by 20% atleast!
>
SQLite must check for the existence of hot journals that were left behind by
other processes that were writing to the database but crashed before they
finished. If SQLite did not check for these, and if other processes did
crash in the middle of a transaction, the database file would go corrupt.
If your process is the only one accessing the database file, you can disable
the journal-checks by doing:
PRAGMA locking_mode=EXCLUSIVE;
>
>
> access("/dev/shm/test.db-journal", F_OK) = -1 ENOENT (No such file or
> directory)
>
> access("/dev/shm/test.db-wal", F_OK) = -1 ENOENT (No such file or
> directory)
>
> -Sreekumar
> On Wed, Aug 10, 2011 at 5:28 PM, Sreekumar TP >wrote:
>
> > Hi,
> >
> > I have the results from the tests (below). Alot of the time is spent in
> > checking file permissions and locking the file (40 %).
> >
> >
> > Inmem
> >
> > % time seconds usecs/call callserrors syscall
> >
> > -- --- --- - -
> >
> > 28.530.124727 1118524 write
> >
> > 23.420.102382 0414624 gettimeofday
> >
> > 20.780.090840 1 76513 read
> >
> > 13.720.059977 0191255 _llseek
> >
> >
> >
> > Db in tmpfs
> >
> > % time seconds usecs/call callserrors syscall
> >
> > -- --- --- - -
> >
> > 21.830.257073 1263306 write
> >
> > 21.180.249488 1179148179148 access
> >
> > 20.610.242725 0509292 fcntl64
> >
> > 13.040.153551 0448720 _llseek
> >
> > 9.440.94 1189370 read
> >
> > 8.160.096124 0414624 gettimeofday
> > 2.550.033750 8 fdatasync
> >
> > On Tue, Aug 9, 2011 at 7:34 PM, Pavel Ivanov
> wrote:
> >
> >> > Journal mode is WAL
> >>
> >> I believe in-memory database can't have journal mode WAL. So you
> >> compare completely different settings.
> >>
> >>
> >> Pavel
> >>
> >>
> >> On Tue, Aug 9, 2011 at 5:15 AM, wrote:
> >> >
> >> > Journal mode is WAL
> >> >
> >> >
> >> > --Original Message--
> >> > From: Roger Binns
> >> > Sender: sqlite-users-boun...@sqlite.org
> >> > To: General Discussion of SQLite Database
> >> > ReplyTo: General Discussion of SQLite Database
> >> > Subject: Re: [sqlite] In memory v/s tmpfs
> >> > Sent: Aug 9, 2011 2:42 PM
> >> >
> >> > -BEGIN PGP SIGNED MESSAGE-
> >> > Hash: SHA1
> >> >
> >> > On 08/08/2011 06:34 PM, sreekumar...@gmail.com wrote:
> >> >> From the point of view of performance, I expected similar performance
> ,
> >> tmpfs being a little slower due to filesystem overhead. However, the
> >> operations on tmpfs was much slower than expected.
> >> >
> >> > Using tmpfs requires many kernel calls which is considerably more
> effort
> >> > than the occasional malloc call. Additionally files have to be
> locked,
> >> > journals made etc (you didn't mention your journal setting).
> >> >
> >> > Roger
> >> > -BEGIN PGP SIGNATURE-
> >> > Version: GnuPG v1.4.11 (GNU/Linux)
> >> > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
> >> >
> >> > iEYEARECAAYFAk5A+g0ACgkQmOOfHg372QTR8ACgqNeeuOxHRy7+hMH5RY/OAyV2
> >> > Wq0AoMaSRtoFN4obCgmgHlpHthd9z5Zp
> >> > =pkJt
> >> > -END PGP SIGNATURE-
> >> > ___
> >> > sqlite-users mailing list
> >> > sqlite-users@sqlite.org
> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >> >
> >> > Sent from BlackBerry® on Airtel
> >> > ___
> >> > sqlite-users mailing list
> >> > sqlite-users@sqlite.org
> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >>
> >
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
The 179148 failures on 'access' system call is due to access check of two
files - the journal file and the wal-file.
The journal mode was OFF as also WAL mode.
Why is sqlite checking access permissions for this file 179140 times?
Removing or optimising this will make it faster by 20% atleast!
access("/dev/shm/test.db-journal", F_OK) = -1 ENOENT (No such file or
directory)
access("/dev/shm/test.db-wal", F_OK) = -1 ENOENT (No such file or
directory)
-Sreekumar
On Wed, Aug 10, 2011 at 5:28 PM, Sreekumar TP wrote:
> Hi,
>
> I have the results from the tests (below). Alot of the time is spent in
> checking file permissions and locking the file (40 %).
>
>
> Inmem
>
> % time seconds usecs/call callserrors syscall
>
> -- --- --- - -
>
> 28.530.124727 1118524 write
>
> 23.420.102382 0414624 gettimeofday
>
> 20.780.090840 1 76513 read
>
> 13.720.059977 0191255 _llseek
>
>
>
> Db in tmpfs
>
> % time seconds usecs/call callserrors syscall
>
> -- --- --- - -
>
> 21.830.257073 1263306 write
>
> 21.180.249488 1179148179148 access
>
> 20.610.242725 0509292 fcntl64
>
> 13.040.153551 0448720 _llseek
>
> 9.440.94 1189370 read
>
> 8.160.096124 0414624 gettimeofday
> 2.550.033750 8 fdatasync
>
> On Tue, Aug 9, 2011 at 7:34 PM, Pavel Ivanov wrote:
>
>> > Journal mode is WAL
>>
>> I believe in-memory database can't have journal mode WAL. So you
>> compare completely different settings.
>>
>>
>> Pavel
>>
>>
>> On Tue, Aug 9, 2011 at 5:15 AM, wrote:
>> >
>> > Journal mode is WAL
>> >
>> >
>> > --Original Message--
>> > From: Roger Binns
>> > Sender: sqlite-users-boun...@sqlite.org
>> > To: General Discussion of SQLite Database
>> > ReplyTo: General Discussion of SQLite Database
>> > Subject: Re: [sqlite] In memory v/s tmpfs
>> > Sent: Aug 9, 2011 2:42 PM
>> >
>> > -BEGIN PGP SIGNED MESSAGE-
>> > Hash: SHA1
>> >
>> > On 08/08/2011 06:34 PM, sreekumar...@gmail.com wrote:
>> >> From the point of view of performance, I expected similar performance ,
>> tmpfs being a little slower due to filesystem overhead. However, the
>> operations on tmpfs was much slower than expected.
>> >
>> > Using tmpfs requires many kernel calls which is considerably more effort
>> > than the occasional malloc call. Additionally files have to be locked,
>> > journals made etc (you didn't mention your journal setting).
>> >
>> > Roger
>> > -BEGIN PGP SIGNATURE-
>> > Version: GnuPG v1.4.11 (GNU/Linux)
>> > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>> >
>> > iEYEARECAAYFAk5A+g0ACgkQmOOfHg372QTR8ACgqNeeuOxHRy7+hMH5RY/OAyV2
>> > Wq0AoMaSRtoFN4obCgmgHlpHthd9z5Zp
>> > =pkJt
>> > -END PGP SIGNATURE-
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> >
>> > Sent from BlackBerry® on Airtel
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
Hi, I have the results from the tests (below). Alot of the time is spent in checking file permissions and locking the file (40 %). Inmem % time seconds usecs/call callserrors syscall -- --- --- - - 28.530.124727 1118524 write 23.420.102382 0414624 gettimeofday 20.780.090840 1 76513 read 13.720.059977 0191255 _llseek Db in tmpfs % time seconds usecs/call callserrors syscall -- --- --- - - 21.830.257073 1263306 write 21.180.249488 1179148179148 access 20.610.242725 0509292 fcntl64 13.040.153551 0448720 _llseek 9.440.94 1189370 read 8.160.096124 0414624 gettimeofday 2.550.033750 8 fdatasync On Tue, Aug 9, 2011 at 7:34 PM, Pavel Ivanov wrote: > > Journal mode is WAL > > I believe in-memory database can't have journal mode WAL. So you > compare completely different settings. > > > Pavel > > > On Tue, Aug 9, 2011 at 5:15 AM, wrote: > > > > Journal mode is WAL > > > > > > --Original Message-- > > From: Roger Binns > > Sender: sqlite-users-boun...@sqlite.org > > To: General Discussion of SQLite Database > > ReplyTo: General Discussion of SQLite Database > > Subject: Re: [sqlite] In memory v/s tmpfs > > Sent: Aug 9, 2011 2:42 PM > > > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > On 08/08/2011 06:34 PM, sreekumar...@gmail.com wrote: > >> From the point of view of performance, I expected similar performance , > tmpfs being a little slower due to filesystem overhead. However, the > operations on tmpfs was much slower than expected. > > > > Using tmpfs requires many kernel calls which is considerably more effort > > than the occasional malloc call. Additionally files have to be locked, > > journals made etc (you didn't mention your journal setting). > > > > Roger > > -BEGIN PGP SIGNATURE- > > Version: GnuPG v1.4.11 (GNU/Linux) > > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ > > > > iEYEARECAAYFAk5A+g0ACgkQmOOfHg372QTR8ACgqNeeuOxHRy7+hMH5RY/OAyV2 > > Wq0AoMaSRtoFN4obCgmgHlpHthd9z5Zp > > =pkJt > > -END PGP SIGNATURE- > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > Sent from BlackBerry® on Airtel > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
> Journal mode is WAL I believe in-memory database can't have journal mode WAL. So you compare completely different settings. Pavel On Tue, Aug 9, 2011 at 5:15 AM, wrote: > > Journal mode is WAL > > > --Original Message-- > From: Roger Binns > Sender: sqlite-users-boun...@sqlite.org > To: General Discussion of SQLite Database > ReplyTo: General Discussion of SQLite Database > Subject: Re: [sqlite] In memory v/s tmpfs > Sent: Aug 9, 2011 2:42 PM > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 08/08/2011 06:34 PM, sreekumar...@gmail.com wrote: >> From the point of view of performance, I expected similar performance , >> tmpfs being a little slower due to filesystem overhead. However, the >> operations on tmpfs was much slower than expected. > > Using tmpfs requires many kernel calls which is considerably more effort > than the occasional malloc call. Additionally files have to be locked, > journals made etc (you didn't mention your journal setting). > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.11 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ > > iEYEARECAAYFAk5A+g0ACgkQmOOfHg372QTR8ACgqNeeuOxHRy7+hMH5RY/OAyV2 > Wq0AoMaSRtoFN4obCgmgHlpHthd9z5Zp > =pkJt > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > Sent from BlackBerry® on Airtel > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
Journal mode is WAL --Original Message-- From: Roger Binns Sender: sqlite-users-boun...@sqlite.org To: General Discussion of SQLite Database ReplyTo: General Discussion of SQLite Database Subject: Re: [sqlite] In memory v/s tmpfs Sent: Aug 9, 2011 2:42 PM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/08/2011 06:34 PM, sreekumar...@gmail.com wrote: > From the point of view of performance, I expected similar performance , tmpfs > being a little slower due to filesystem overhead. However, the operations on > tmpfs was much slower than expected. Using tmpfs requires many kernel calls which is considerably more effort than the occasional malloc call. Additionally files have to be locked, journals made etc (you didn't mention your journal setting). Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk5A+g0ACgkQmOOfHg372QTR8ACgqNeeuOxHRy7+hMH5RY/OAyV2 Wq0AoMaSRtoFN4obCgmgHlpHthd9z5Zp =pkJt -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Sent from BlackBerry® on Airtel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/08/2011 06:34 PM, sreekumar...@gmail.com wrote: > From the point of view of performance, I expected similar performance , tmpfs > being a little slower due to filesystem overhead. However, the operations on > tmpfs was much slower than expected. Using tmpfs requires many kernel calls which is considerably more effort than the occasional malloc call. Additionally files have to be locked, journals made etc (you didn't mention your journal setting). Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk5A+g0ACgkQmOOfHg372QTR8ACgqNeeuOxHRy7+hMH5RY/OAyV2 Wq0AoMaSRtoFN4obCgmgHlpHthd9z5Zp =pkJt -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
> What could be a possible explanation for this behaviour? > One difference int configurations is that the sqlite lib is built for > multithreading in the tmpfs scenario. Could it be an overhead of mutexes? Make tests with the same SQLite library. Run test with tmpfs using strace to see if filesystem overhead is more than you expect. And post brief results here - it will be interesting to see and/or try to explain them. Pavel On Mon, Aug 8, 2011 at 9:04 AM, wrote: > HI, > > I performed an experiment where I do db operations on an in-memory database > and compared it with the results from same operations on a database in > tmpfs.(Same db structure,records etc) > > From the point of view of performance, I expected similar performance , tmpfs > being a little slower due to filesystem overhead. However, the operations on > tmpfs was much slower than expected. > > What could be a possible explanation for this behaviour? > > One difference int configurations is that the sqlite lib is built for > multithreading in the tmpfs scenario. > > -Sreekumar > Sent from BlackBerry® on Airtel > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
