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?

7
  • I'm no sqlite pro, but have you tried batching it into smaller transactions? – LowlyDBA - John M Nov 15 '18 at 20:26
  • Yeah, I guess I could do that, but that does not really speed up the writes. The thing is that the writing of the 10 is actually quite fast, but then Sqlite does something else for 40 seconds that doesn't seem to have anything to do with writing to disk.. – FFMG Nov 15 '18 at 20:40
  • Committing a transaction is more than just writing the data to disk - that is why huge commits are usually something to avoid. Your problem isn't the speed of writing to disk, it is that you're performing a huge commit. – LowlyDBA - John M Nov 15 '18 at 20:45
  • Thanks, but I wouldn't have thought that 100K insert be considered as "huge", I will try with smaller transactions and see if the 'overall' time improves. – FFMG Nov 16 '18 at 3:36
  • I have changed the size of the transaction and it seems faster, but I am still testing further. But my understanding of transactions is that they are made to speed thing up ... not slow things down. – FFMG Nov 16 '18 at 13:05
0

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 '18 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 '18 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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