Sqlite - how to use more memory and cache, and make it run faster

Question

I'm inserting into a table in Sqlite around 220GB of data,

and I noticed it use a lot of Disk I/O, read and write, but doesn't use the computer's memory in any significant way, though there is a lot of free memory, and I don't use commit to often.

I think the disk I/O is my bottle neck not CPU nor Memory. how can I ask it to use more memory, or insert in bulk so it could run faster?

Solution

Review all options in http://www.sqlite.org/pragma.html. You can tuning a lot of performance relative aspect of SQLite in your application.

All I/O activity is for the integrity of data. SQLite by default is very safe.

Your filesystem is also important for the performance. Not all FS play fair with fsync and the (default) config for internal logging of SQLite.

Comments

[Rafe Kettler]

SQLite probably is not the right choice for what you're doing. You may want to use a heavier DB, like MySQL or Postgres.

[Roger Binns]

This kind of question comes up frequently on the sqlite-users mailing list. In particular how many rows per second are you inserting and roughly what size are they? Ensure you are using a transaction. Memory would only need to be used if there were indexes but it is far faster to create them after inserting the data.