SQlite 3 - bottleneck with rbuFindMaindb

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

SQlite 3 - bottleneck with rbuFindMaindb

Roger Cuypers
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?

Thanks for your suggestions.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQlite 3 - bottleneck with rbuFindMaindb

Simon Slavin-3
On 19 Sep 2018, at 7:49pm, Roger Cuypers <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQlite 3 - bottleneck with rbuFindMaindb

Dan Kennedy-4
In reply to this post by Roger Cuypers
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQlite 3 - bottleneck with rbuFindMaindb

Roger Cuypers
In reply to this post by Simon Slavin-3
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 <[hidden email]>:
>
> On 19 Sep 2018, at 7:49pm, Roger Cuypers <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQlite 3 - bottleneck with rbuFindMaindb

Roger Cuypers
In reply to this post by Dan Kennedy-4
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 <[hidden email]>:
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQlite 3 - bottleneck with rbuFindMaindb

Simon Slavin-3
In reply to this post by Roger Cuypers
On 19 Sep 2018, at 8:47pm, Roger Cuypers <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQlite 3 - bottleneck with rbuFindMaindb

Dan Kennedy-4
In reply to this post by Roger Cuypers
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 <[hidden email]>:
>>
>> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQlite 3 - bottleneck with rbuFindMaindb

Roger Cuypers
In reply to this post by Simon Slavin-3
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
<here comes the application>

> Am 19.09.2018 um 22:27 schrieb Simon Slavin <[hidden email]>:
>
> On 19 Sep 2018, at 8:47pm, Roger Cuypers <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQlite 3 - bottleneck with rbuFindMaindb

Simon Slavin-3
On 20 Sep 2018, at 10:31pm, Roger Cuypers <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users