Measuring and Reducing CPU Usage in SQLite
Small. Fast. Reliable.<br>Choose any three.
Home<br>Menu<br>About<br>Documentation<br>Download<br>License<br>Support<br>Purchase
Search
About<br>Documentation<br>Download<br>Support<br>Purchase
Search Documentation<br>Search Changelog
Measuring and Reducing CPU Usage in SQLite
Table Of Contents<br>1. Overview
2. Measuring Performance
2.1. Compile Options
2.2. Workload
2.2.1. Update As Of 2026-01-06
2.3. Performance Measurement
2.4. Microoptimizations
3. Performance Measurement Workflow
4. Limitations
5. Why Does The Graph End In 2025?
1. Overview
The graph below shows the number of CPU cycles used by SQLite on a<br>standard workload, for versions of SQLite between 3.6.1 (2008-08-06)<br>and 3.48.0 (2025-01-14), a span of about 16.4 years.<br>Over this interval, the number of CPU cycles used dropped from about<br>3.376 million down to 0.965 million. So by 2025, SQLite required only<br>about 29% as many CPU cycles as it did in 2008.
This article describes how the SQLite developers measure CPU usage,<br>what those measurements actually mean, and the techniques used by<br>SQLite developers on their continuing quest to further reduce the<br>CPU usage of the SQLite library.
Measured using cachegrind on Ubuntu 16.04 on x64 with gcc 5.4.0 and -Os.
2. Measuring Performance
In brief, the CPU performance of SQLite is measured as follows:
Compile SQLite in an as-delivered configuration, without any special<br>telemetry or debugging options.
Link SQLite against a test program that runs approximately 30,000<br>SQL statements representing a typical workload.
Count the number of CPU cycles consumed using<br>cachegrind.
2.1. Compile Options
For performance measurement, SQLite is compiled in approximately the same<br>way as it would be for use in production systems. The compile-time configuration<br>is "approximate" in the sense that every production use of SQLite is<br>different. Compile-time options used by one system are not necessarily<br>the same as those used by others. The key point is that options that<br>significantly impact the generated machine code are avoided. For example,<br>the -DSQLITE_DEBUG option is omitted because that option inserts thousands<br>of assert() statements in the middle of performance critical sections of the<br>SQLite library. The -pg option (on GCC) is omitted because it causes the<br>compiler to emit extra probabilistic performance measuring code which interferes<br>with actual performance measurements.
For performance measurements,<br>the -Os option is used (optimize for size) rather than -O2 because the<br>-O2 option creates so much code movement that it is difficult to associate<br>specific CPU instructions to C source code lines.
2.2. Workload
The "typical" workload is generated by the<br>speedtest1.c<br>program in the canonical SQLite source tree. This program strives to<br>exercise the SQLite library in a way that is typical of real-world<br>applications. Of course, every application is different, and so<br>no test program can exactly mirror the behavior of all applications.
The speedtest1.c program is updated from time to time as the SQLite<br>developers' understanding of what constitutes "typical" usage evolves.
The<br>speed-check.sh shell<br>script, also in the canonical source tree, is used to run the speedtest1.c<br>program. To replicate the performance measurements, collect the following<br>files into a single directory:
the "speed-check.sh" script,
the "speedtest1.c" test program, and
the SQLite amalgamation source files "sqlite3.c" and<br>"sqlite3.h"
Then run "sh speed-check.sh trunk".
2.2.1. Update As Of 2026-01-06
This article was originally written on 2016-12-08.<br>The speed-check.sh script is no longer used. A new script named<br>speedtest.tcl has<br>been devised and has been used since early 2025. See the<br>speedtest.md<br>documentation file for details. This article continues to<br>reference the older performance measurement script, as part of<br>the historical record.
2.3. Performance Measurement
Cachegrind is used to<br>measure performance because it gives answers that are repeatable to<br>7 or more significant digits. In comparison, actual (wall-clock)<br>run times are scarcely repeatable beyond one significant digit.
2.4. Microoptimizations
The high repeatability of cachegrind allows the SQLite developers to<br>implement and measure "microoptimizations". A microoptimization is<br>a change to the code that results in a very small performance increase.<br>Typical micro-optimizations reduce the number of CPU cycles by 0.1% or<br>0.05% or even less. Such improvements are impossible to measure with<br>real-world timings. But hundreds or thousands of microoptimizations<br>add up, resulting in measurable real-world performance gains.
3. Performance Measurement Workflow
As SQLite developers edit the SQLite source code, they run the<br>speed-check.sh<br>shell script to track the performance impact of changes. This<br>script compiles the speedtest1.c program, runs it under cachegrind,<br>processes the cachegrind output using the<br>cg_anno.tcl TCL<br>script, then saves the results in a series of...