SpatiaLite logo

System level performace hints

2011 January 28

Previous Slide Table of Contents Next Slide

We have examined since now several optimization related topics: but all this mainly was to be intended as “smartly writing well designed queries”.

Although defining a properly planned SQL query surely represents the main factor to achieve optimal performances, this isn't enough.
A second level of performance optimization (fine tuning) exist, i.e. the one concerning interactions between the DBMS and the underlying Operating System / File System.

DB pages / page cache

Any SQLite DB simply is a single monolithic file: any data and related info is stored within this files.
As in many others DBMS, disk space isn't allocated at random, but is properly structured:
the atomic allocation unit is defined as a page, so a DB file simply is a well organized collection of pages.
All pages within the same DB must have the same identical size (typically 1KB i.e. 1024 bytes):
  • adopting a bigger page size may actually reduce the I/O traffic, but may impose to waste a significant amount of unused space.
  • adopting a smaller page size is strongly discouraged, because will surely imply a much more sustained I/O traffic.
  • so the default page size of 1KB represents a mean case well fitted for the vast majority of real world situations.
Reading and writing from disk a single page at each time surely isn't an efficient process;
so SQLite maintains an internal page cache (stored in RAM), supporting fast access to the most often accessed pages.
Quite intuitively, adopting a bigger page cache can strongly reduce the overall I/O traffic;
and consequently an higher throughput can be achieved.

By default SQLite adopts a very conservative approach, so to require a light-weight memory footprint;
the initial page cache will simply store 2000 pages (corresponding to a total allocation of only 20MB).

But a so small default page cache surely isn't enough to properly support an huge DB, (may be one ranging in the many-GB size);
this will easily become a real bottleneck, causing very poor global performances.

PRAGMA page_size;
PRAGMA page_count;
PRAGMA freelist_count;
You can use several PRAGMAs to check the page status for the currently connected DB:
PRAGMA page_size = 4096;
PRAGMA page_size;
You can call a PRAGMA page_size so to set a different page size
(you must specify a power of two size argument, ranging from 512 to 65536):
Performing a VACUUM implies the following actions to be performed:
PRAGMA page_size;
PRAGMA page_count;


PRAGMA freelist_count;

Just a quick check: immediately after performing VACUUM the new page size has been effectively applied, and there are no unused pages at all.

PRAGMA cache_size;
PRAGMA cache_size = 1000000;
PRAGMA cache_size;
You can use PRAGMA cache_size in order to query or set the page cache:
Requesting a very generously (but wisely) dimensioned page cache usually will grant a great performance boost, most notably when you are processing a very large DB.

You can modify other important settings using the appropriate PRAGMAs supported by SQLite:
  • PRAGMA ignore_check_constraint can be used to query, enable or disable CHECK constraints
    (e.g. disabling check constraints is unsafe, but may be required during preliminary data loading).
  • PRAGMA foreign_key can be used to query, enable or disable FOREIGN KEY constraints
    (and this too may be useful or required during preliminary data loading).
  • PRAGMA journal_mode can be used to query or set fine details about TRANSACTION journaling.
PRAGMA's implementation change from time to time, so you can usefully consult the appropriate SQLite documentation

Previous Slide Table of Contents Next Slide

CC-BY-SA logo Author: Alessandro Furieri
This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

GNU logo Permission is granted to copy, distribute and/or modify this document under the terms of the
GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation;
with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.