SpatiaLite Cookbook

	System level performace hints
2011 January 28

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;

1024

PRAGMA page_count;

31850

PRAGMA freelist_count;

12326

You can use several PRAGMAs to check the page status for the currently connected DB:

PRAGMA page_size; will report the currently set page size.
PRAGMA page_count; will report the total number of allocated pages.
PRAGMA freelist_count; will report the total number of unused pages.
- please note: each time you perform lots of DELETEs or some DROP [TABLE | INDEX] statement, then several unused pages will be left in the DB.

PRAGMA page_size = 4096;

PRAGMA page_size;

1024

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):

anyway, the page size still remains unchanged.
this is because a complete DB reorganization is required in order to make such change to actually materialize.

VACUUM;

Performing a VACUUM implies the following actions to be performed:

the whole DB will be checked and completely rewritten from scratch.
any structural change (e.g. changing the current page size) will now be applied.
any unused page will be discarded, so the DB will be effectively compacted.
please note: VACUUMing a large DB may require a long time.

PRAGMA page_size;

4096

PRAGMA page_count;

5197

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;

1000

PRAGMA cache_size = 1000000;

PRAGMA cache_size;

1000000

You can use PRAGMA cache_size in order to query or set the page cache:

please note: the size is measured as the number of pages to be stored into the cache:
so the corresponding memory allocation (in bytes) will be page_size * cache_size
requesting a bigger cache size usually implies better performances.
Anyway carefully consider that:
- an exaggeratedly big cache is completely useless: you'll simply waste a lot of precious RAM for nothing.
- when the memory allocation required by the page cache exceed the amount of physically available RAM, performance will then be catastrophically impaired, because this will actually cause an enormous I/O traffic due to memory-to-disk swapping.
- on 32bit platforms a further limit exist: on such platforms any process cannot allocate more than 4GB memory.
  But for practical reasons this limit is more likely to be as low as 1.5GB.

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

	Author: Alessandro Furieri a.furieri@lqt.it
	This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

	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.