back to the OSM tools main page

How-to optimize SQLite performances

SQLite is a very simple DBMS: it actually is surprisingly fast under many circumstances, but its performances could easily degradate when some really huge DB-file has to be processed.

Just a quick technical rationale: SQLite fully relies on the underlying file system efficiency, no special data access strategy is implemented by the DBMS itself.
So, as a rule-of-the-thumb, since the DB-file size well fits the available RAM size you can expect to get a good buffering support (fast, efficient disk access). On the opposite side, once that the DB-file widely exceeds the available RAM size a long sequence of continuous buffer stalls will be experienced, thus causing performance degradation (unexpected slowness).
Different operating systems have different intrinsic file system efficiency, and the brute force of the underlaying hardware (HDD) obviously plays a strong role in all this. On some platforms disk fragmentation could actually be a real plague, and antivirus software very often are obnoxious performance killers.

Anyway SQLite actively supports few configuration options really useful in order to get a real performance boost:

• using a memory DB: SQLite has the wonderful capability to completely load a whole DB in RAM. This obviously grants astonishing super-fast performance levels.
  Be very careful: this isn't at all the universal silver bullet; you obviously cannot succesfully load a memory DB, if the available free RAM isn't enough.
  Using 32 bit sw a futher limit exists: due to address limits you cannot realistically hope to get more than 1.2 / 1.5 GB of RAM allocation; on the other side, using 64 bit sw you can succesfully squeeze out every bit from your RAM. Anyway a physical limit (RAM size) always exists: be very conscious of this.
• explicitly set an internal cache reserved to SQLite: by default SQLite has a very thin memory footprint; enabling this option you'll authorize SQLite to use a much bigger memory amount, thus strongly reducing the need to perform a physical disk access.
• disable at all journaling: SQLite is a full-ACID DBMS, i.e. it's a Transctional DBMS. Transaction handling is a cool and smart advanced feature, but imposes an high disk traffic. Disabling at all Transactions is intrinsically unsafe (you cannot safely recover from an error condition), but implies a strong reduction of the required workload. After all, when you are simply loading a new DB from scratch, any catastrophic error condition (power failure, system failure ...) isn't really so dramatic. You can simply restart the whole process from the very beginning, without any other adverse effect.

All SpatiaLite's OSM tools supports the same arguments set useful in order to control DB optimization:

• you can explicitly set the -m (or --in-memory) argument in order to force the process using a memory DB.
• alternatively, you can set the -cs num-of-pages (or --cache-size num-of-pages) argument in order to set an internal page cache.
  Please note: the num-of-pages value establishes how many DB pages can be stored on the internal cache: using SQLite's default settings each page has a size of 1024 bytes. So -cs 512000 implies a RAM allocation of 512 MB, and -cs 1024000 implies 1 GB.
• and finally you can set the -jo (or --journal-off) argument in order to disable at all Transactions.

back to the OSM tools main page