WAL mode much slower in certain use cases

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

WAL mode much slower in certain use cases

Andrew Cunningham
I am using SQLite 3.24.0, as a single user persistent data store for
storing simulation data.

The database can grow to many gigabytes as the software can ingest a lot of
binary data which  I store as multiple BLOBs.

In the following example I am reading several 22GB of data into the DB.

Times in seconds
In WAL mode
Read data & creating DB records 503.227
Commit of transaction,close database  334

In non-WAL mode
Read data & creating DB records 244
Commit of transaction ,close database   4

WAL mode is 80-100x slower to commit to the database.

I am probably abusing WAL mode, but I do not see anything in the
documentation which says "don't use WAL mode in the following use case".

Full disclosure, I am using ODB ( C++ ORM) that hides what is going on
under the hood, but my experience is that in a simple use case like this
ODB is not the problem.

Andrew
_______________________________________________
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: WAL mode much slower in certain use cases

David Raymond
When you're inserting in WAL mode everything will basically have to be written twice. First the new page data will be written to the WAL, and then when a checkpoint happens the WAL file will be copied over to the main database file.

With rollback journal mode the contents of the original page are written to the rollback journal, then the data is written to the main database file. But if the page being written to is brand new (because the database size in increasing), then there is nothing to be written to the rollback journal, and only 1 write needs to happen. (Same if the page being written used to be a free page... I think)

So if there's unused room in the file for the inserts, then the two modes should be similar, but if it's a brand new database file, or your insert is going to grow the file size, then rollback mode will result in fewer writes being needed.

So technically the "commit" times are probably similar, but it's probably the "checkpoint" time that's killing you. Though I _am_ still curious as to why your insert times are twice as long.

(Someone correct me if I'm off on this)


-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Andrew Cunningham
Sent: Monday, July 01, 2019 2:04 PM
To: SQLite Maillist <[hidden email]>
Subject: [sqlite] WAL mode much slower in certain use cases

I am using SQLite 3.24.0, as a single user persistent data store for
storing simulation data.

The database can grow to many gigabytes as the software can ingest a lot of
binary data which  I store as multiple BLOBs.

In the following example I am reading several 22GB of data into the DB.

Times in seconds
In WAL mode
Read data & creating DB records 503.227
Commit of transaction,close database  334

In non-WAL mode
Read data & creating DB records 244
Commit of transaction ,close database   4

WAL mode is 80-100x slower to commit to the database.

I am probably abusing WAL mode, but I do not see anything in the
documentation which says "don't use WAL mode in the following use case".

Full disclosure, I am using ODB ( C++ ORM) that hides what is going on
under the hood, but my experience is that in a simple use case like this
ODB is not the problem.

Andrew
_______________________________________________
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