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 paiva...@gmail.com 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, sreekumar...@gmail.com 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
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 sreekumar...@gmail.comwrote: 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 paiva...@gmail.com 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, sreekumar...@gmail.com 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
On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP sreekumar...@gmail.comwrote: 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 sreekumar...@gmail.com 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 paiva...@gmail.com 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, sreekumar...@gmail.com 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
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 d...@sqlite.org 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. 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 sreekumar...@gmail.com 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 paiva...@gmail.com 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, sreekumar...@gmail.com 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 ___ 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 d...@sqlite.org 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. 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
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 slav...@bigfraud.org 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 d...@sqlite.org 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. 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 Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin slav...@bigfraud.org 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 d...@sqlite.org 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
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 d...@sqlite.org wrote: On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin slav...@bigfraud.org 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 d...@sqlite.org 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:35 PM, Sreekumar TP sreekumar...@gmail.comwrote: 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 d...@sqlite.org wrote: On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin slav...@bigfraud.org 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 d...@sqlite.org 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
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 d...@sqlite.org wrote: On Wed, Aug 10, 2011 at 1:35 PM, Sreekumar TP sreekumar...@gmail.comwrote: 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 d...@sqlite.org wrote: On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin slav...@bigfraud.org 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 d...@sqlite.org 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
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 paiva...@gmail.com 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 d...@sqlite.org wrote: On Wed, Aug 10, 2011 at 1:35 PM, Sreekumar TP sreekumar...@gmail.com 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 d...@sqlite.org wrote: On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin slav...@bigfraud.org 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 d...@sqlite.org 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
On Wed, Aug 10, 2011 at 2:13 PM, Sreekumar TP sreekumar...@gmail.comwrote: 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
Oh Yes. I missed that point ! On Wed, Aug 10, 2011 at 11:57 PM, Richard Hipp d...@sqlite.org wrote: On Wed, Aug 10, 2011 at 2:13 PM, Sreekumar TP sreekumar...@gmail.com 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
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 sreekumar...@gmail.comwrote: 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
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 sreekumar...@gmail.com 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 paiva...@gmail.com 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 d...@sqlite.org wrote: On Wed, Aug 10, 2011 at 1:35 PM, Sreekumar TP sreekumar...@gmail.com 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 d...@sqlite.org wrote: On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin slav...@bigfraud.org 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 d...@sqlite.org 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
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
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
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, sreekumar...@gmail.com 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
[sqlite] In memory v/s tmpfs
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
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, sreekumar...@gmail.com 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
