Re: [sqlite] SQlite 3 - bottleneck with rbuFindMaindb
On 20 Sep 2018, at 10:31pm, Roger Cuypers wrote: > rbuFindMaindb > rbuVfsAccess > sqlite3OsAccess > hasHotJournal > sqlite3PagerSharedLock > zipvfsLockFile Thanks. That's very useful. Your stack includes both zipvfsLockFile and rbuVfsAccess, and I'm not familiar with either of these. So I leave your problem to the others who will see this. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite 3 - bottleneck with rbuFindMaindb
Ok, I have more info now. The database consists of multiple individual database files which are opened and closed individually each with their own connection, multiple at at time. There is a root file but its just another database file whose only purpose is to tell the application where to find the other files. Here is an example call stack of the a high load call: rbuFindMaindb rbuVfsAccess sqlite3OsAccess hasHotJournal sqlite3PagerSharedLock zipvfsLockFile sqlite3OsLock pagerLockDb pagerLockDb pager_wait_on_lock sqlite3PagerSharedLock lockBtree sqlite3BtreeBeginTrans sqlite3VdbeExec sqlite3Step sqlite3_step > Am 19.09.2018 um 22:27 schrieb Simon Slavin : > > On 19 Sep 2018, at 8:47pm, Roger Cuypers wrote: > >> the database has a root file. The subfiles are all loaded via separate >> connections as far as I know. > > Sorry, but this makes no sense. Each database file can have only one WAL > file. > > You say that the program is looking through lots of WAL files. The only way > it should be doing that is if the program has lots of database files open at > the same time. If a database is not open, then SQLite does not even know its > WAL file exists. > > Does your program really have numerous database files open at one time ? > > If so, does it do that using the ATTACH command, and attaching them all to > one connection, or by opening a separate connection to each database ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite 3 - bottleneck with rbuFindMaindb
On 09/20/2018 03:05 AM, Roger Cuypers wrote: I think it does at some point. I’m at home right now so I have to check this again tomorrow when I have access to the source. Should there be rbu calls if the application is only _reading_ fro the database and not updating? Maybe. If the app called sqlite3rbu_create_vfs() to add an RBU VFS to the VFS stack. AFAIK the only reason to do this is if you are using ZipVFS. Are you using ZipVFS? Dan. Am 19.09.2018 um 21:48 schrieb Dan Kennedy : On 09/20/2018 01:49 AM, Roger Cuypers wrote: I'm trying to optimize a C++ application that uses sqlite 3 for database access. As far as I know it uses journaling with WAL and has a lot of files/tables (about 400). Profiling this application with Linux perf, I found that it spends about 30% of its time inside the rbuFindMaindb function of SQlite3. This function mostly consists of a loop that goes through all of the journaling WAL files in the virtual file system, so it seems that in order to bring down the cost of said loop I would have to reduce the number of WAL files. Seeing that apparently sqlite creates a WAL for every database file, I'm not aware how to fix this. Now my question: How can I optimize this? Can I reduce the number of WAL files without reducing the number of database files? Is there a different journaling mode that does not call rbuFindMaindb so often? Can I optimize my program so that this function is called less often? Are you actually using the RBU extension? https://www.sqlite.org/rbu.html Has your application called any sqlite3rbu_*() APIs? Dan. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite 3 - bottleneck with rbuFindMaindb
On 19 Sep 2018, at 8:47pm, Roger Cuypers wrote: > the database has a root file. The subfiles are all loaded via separate > connections as far as I know. Sorry, but this makes no sense. Each database file can have only one WAL file. You say that the program is looking through lots of WAL files. The only way it should be doing that is if the program has lots of database files open at the same time. If a database is not open, then SQLite does not even know its WAL file exists. Does your program really have numerous database files open at one time ? If so, does it do that using the ATTACH command, and attaching them all to one connection, or by opening a separate connection to each database ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite 3 - bottleneck with rbuFindMaindb
Hello, the database has a root file. The subfiles are all loaded via separate connections as far as I know. Another idea of mine: If I know the database will be only written to very rarely, can I prevent sqlite from using the WAL files at all in the meantime? > Am 19.09.2018 um 21:36 schrieb Simon Slavin : > > On 19 Sep 2018, at 7:49pm, Roger Cuypers wrote: > >> As far as I know it uses journaling with WAL and has a lot of files/tables >> (about 400). > > Excuse the low-end questions, but they might help save us a lot of silly > suggestions. > > Does SQLite have lots of these open at one time ? If so, does it do it by > opening a main database and attaching lots of these to it, or by opening each > hone on a separate connection ? > > If they're all attached to a single connection, can you open each one on a > separate connection instead ? > > Are you aware that you can put more than one table in a database file ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite 3 - bottleneck with rbuFindMaindb
I think it does at some point. I’m at home right now so I have to check this again tomorrow when I have access to the source. Should there be rbu calls if the application is only _reading_ fro the database and not updating? > Am 19.09.2018 um 21:48 schrieb Dan Kennedy : > > On 09/20/2018 01:49 AM, Roger Cuypers wrote: >> I'm trying to optimize a C++ application that uses sqlite 3 for database >> access. As far as I know it uses journaling with WAL and has a lot of >> files/tables (about 400). Profiling this application with Linux perf, I >> found that it spends about 30% of its time inside the rbuFindMaindb function >> of SQlite3. This function mostly consists of a loop that goes through all of >> the journaling WAL files in the virtual file system, so it seems that in >> order to bring down the cost of said loop I would have to reduce the number >> of WAL files. Seeing that apparently sqlite creates a WAL for every database >> file, I'm not aware how to fix this. >> >> Now my question: How can I optimize this? Can I reduce the number of WAL >> files without reducing the number of database files? Is there a different >> journaling mode that does not call rbuFindMaindb so often? Can I optimize my >> program so that this function is called less often? > > Are you actually using the RBU extension? > > https://www.sqlite.org/rbu.html > > Has your application called any sqlite3rbu_*() APIs? > > Dan. > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite 3 - bottleneck with rbuFindMaindb
On 09/20/2018 01:49 AM, Roger Cuypers wrote: I'm trying to optimize a C++ application that uses sqlite 3 for database access. As far as I know it uses journaling with WAL and has a lot of files/tables (about 400). Profiling this application with Linux perf, I found that it spends about 30% of its time inside the rbuFindMaindb function of SQlite3. This function mostly consists of a loop that goes through all of the journaling WAL files in the virtual file system, so it seems that in order to bring down the cost of said loop I would have to reduce the number of WAL files. Seeing that apparently sqlite creates a WAL for every database file, I'm not aware how to fix this. Now my question: How can I optimize this? Can I reduce the number of WAL files without reducing the number of database files? Is there a different journaling mode that does not call rbuFindMaindb so often? Can I optimize my program so that this function is called less often? Are you actually using the RBU extension? https://www.sqlite.org/rbu.html Has your application called any sqlite3rbu_*() APIs? Dan. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite 3 - bottleneck with rbuFindMaindb
On 19 Sep 2018, at 7:49pm, Roger Cuypers wrote: > As far as I know it uses journaling with WAL and has a lot of files/tables > (about 400). Excuse the low-end questions, but they might help save us a lot of silly suggestions. Does SQLite have lots of these open at one time ? If so, does it do it by opening a main database and attaching lots of these to it, or by opening each hone on a separate connection ? If they're all attached to a single connection, can you open each one on a separate connection instead ? Are you aware that you can put more than one table in a database file ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
