CODEWITHSUNDEEP
pythonlinuxsqlite
Roman Dmitrienko
Roman Dmitrienko

Reputation: 4235

sqlite3 commit in a Python app freezes PC for several seconds

I am retrieving data from a remote web application and I store it in a local sqlite DB. From time to time I perform a commit() call on the connection, which results in committing about 50 inserts to 8 tables. There are about 1000000 records in most tables. I don't use explicit BEGIN and END commands.

I measured commit() call time and got 100-150 ms. But during the commit, my PC freezes for ~5-15 seconds. The INSERTs themselves are naive (i.e. one execute() call per insert) but are performed fast enough (their rate is limited by the speed of records retrieval, anyways, which is fairly low). I'm using Arch Linux x64 on a PC with AMD FX 6200 CPU, 8 GB RAM and a SATA HDD, Python 3.4.1, sqlite 3.8.4.3.

Does anyone have an idea why this could happen? I guess it has something to do with HDD caching. If so, is there something I could optimize?

UPD: switched to WAL and synchronous=1, no improvements.

UPD2: I have seriously underestimated number of INSERTs per commit. I measured it using sqlite3.Connection's total_changes property, and it appears there are 30000-60000 changes per commit. Is it possible to optimize inserts, or maybe it is about time to switch to postgres?

Upvotes: 0

Views: 217

Answers (1)

Anders Piniesjö
Anders Piniesjö

Reputation: 181

If the call itself is quick enough, as you say, it surely sounds like an IO problem. You could use tools such as iotop to check this maybe. If possible I would suggest that you divide the inserts into smaller and more frequent ones instead of large chunks. If that is not possible you should consider investing in an SSD disk instead of traditional harddisk, due to normally quicker write speeds.

There sure could be system parameters to investigate. You should at least make sure you mount your disk with noatime and nodiratime flags. You could also try data=writebackas parameter. See the following for more details:

https://www.kernel.org/doc/Documentation/filesystems/ext4.txt

Upvotes: 1

Related Questions