3

I have a sqlite database that is, (I think), fairly small.

I has about 1 million rows in a couple of table and the total space on disk is about 150 Mb.

I then open a transaction and insert about 100000 rows, the time it takes is about 10 seconds and I think this is acceptable for that number of rows, (and given the fact that my app does some 'other' processing.

My concern is when I do the 'Commit()' itself.

It takes around 50 seconds to write the data to disk... (around 10Mb is actually written).

This is what I do:

-- pseudo code for brevity...
Data Source=database.db;Version=3;Pooling=True;Max Pool Size=100;

PRAGMA journal_mode=WAL;
PRAGMA wal_autocheckpoint = 16;
PRAGMA journal_size_limit = 1536;
PRAGMA cache_size = 10000;
PRAGMA temp_store = MEMORY;

-- BEGIN TRANS

-- 100K Inserts/ 10K Deletes

-- COMMIT 

Can anybody think why the commit would take so much longer than the entire transaction?

Any suggestions on how I could speed the commit up?

0

1 Answer 1

1

In WAL mode, the database must do regular checkpoints to prevent the log from growing infinitely large.

So after a large amount of changes, it decides to do an automatic checkpoint (especially since you have set wal_autocheckpoint to a rather small value).

You can move the checkpoint delay to another time (but not avoid it altogether) by doing checkpoints manually.

2
  • Thanks, I have tried to change it to a larger amount, but it does not really make a difference to be honest. The smaller amount was originally suggested by the Firefox performance wiki
    – FFMG
    Nov 16, 2018 at 12:51
  • The changes mades by your large transaction will exceed any reasonable limit. This is why I did not tell you to change that setting, but to do something else.
    – CL.
    Nov 16, 2018 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.