Documentation Source Text

<title>Measuring and Reducing CPU Usage in SQLite</title>
<tcl>hd_keywords {CPU cycles used} {CPU performance measurement}</tcl>

<table_of_contents>

<h1>Overview</h1>

<p>The graph below shows the number of CPU cycles used by SQLite on a
standard workload, for all versions of SQLite going back about 9 years.
Recent versions of SQLite use less than a third of the CPU cycles 
compared to older versions.

<p>
This article describes how the SQLite developers measure CPU usage,
what those measurements actually mean, and the techniques used by
SQLite developers on their continuing quest to further reduce the
CPU usage of the SQLite library.
</p>

<center>
<hr>
<div class="imgcontainer">
<img src="./images/cpu-usage.jpg"></img></div><br>
Measured using cachegrind on Ubuntu 16.04 on x64 with gcc 5.4.0 and -Os.<br>
Values normalized so that version 3.23.1 is 100%.
<hr>
</center>

<h1>Measuring Performance</h1>

<p>In brief, the CPU performance of SQLite is measured as follows:

script compiles the speedtest1.c program, runs it under cachegrind,
processes the cachegrind output using the
[https://sqlite.org/src/file/tool/cg_anno.tcl | cg_anno.tcl] TCL
script, then saves the results in a series of text files.
Typical output from the speed-check.sh script looks like this:

<blockquote><pre>

==16429== I   refs:      <b><font color="red">1,291,005,499</font></b>
==16429== I1  misses:       24,688,182
==16429== LLi misses:            5,027
==16429== I1  miss rate:          1.91%
==16429== LLi miss rate:          0.00%
==16429== 
==16429== D   refs:        663,242,182  (418,445,823 rd   + 244,796,359 wr)
==16429== D1  misses:        5,958,032  (  3,902,273 rd   +   2,055,759 wr)
==16429== LLd misses:           45,636  (     14,803 rd   +      30,833 wr)
==16429== D1  miss rate:           0.8% (        0.9%     +         0.8%  )
==16429== LLd miss rate:           0.0% (        0.0%     +         0.0%  )
==16429== 
==16429== LL refs:          30,646,214  ( 28,590,455 rd   +   2,055,759 wr)
==16429== LL misses:            50,663  (     19,830 rd   +      30,833 wr)
==16429== LL miss rate:            0.0% (        0.0%     +         0.0%  )
   text	   data	    bss	    dec	    hex	filename
 466711	   6256	   1864	 <b><font color="red">474831</font></b>	  73ecf	sqlite3.o
 199979  914462 7026217 sqlite3.c
</pre></blockquote>

<p>The important parts of the output (the parts that the developers pay
the most attention to) are shown in red.
Basically, the developers want to know the size of the compiled SQLite
library and how many CPU cycles were needed to run the performance test.

<p>The output from the 
[https://sqlite.org/src/file/tool/cg_anno.tcl | cg_anno.tcl] script
shows the number of CPU cycles spent on each line of code.
The report is approximately 80,000 lines long.  The following is a brief
snippet taken from the middle of the report to show what it looks like:

<hr>
<blockquote><pre>
         .  SQLITE_PRIVATE int sqlite3BtreeNext(BtCursor *pCur, int *pRes){
         .    MemPage *pPage;
         .    assert( cursorOwnsBtShared(pCur) );
         .    assert( pRes!=0 );
         .    assert( *pRes==0 || *pRes==1 );
         .    assert( pCur-&gt;skipNext==0 || pCur-&gt;eState!=CURSOR_VALID );

   728,110    if( pPage->leaf ){
         .      return SQLITE_OK;
         .    }else{
     3,117      return moveToLeftmost(pCur);
         .    }
   721,876  }
</pre></blockquote>
<hr>

<p>
The numbers on the left are the CPU cycle counts for that line of code,
of course.

<p>
The cg_anno.tcl script removes extraneous details from the default 

feature {RIGHT and FULL OUTER JOIN} {
  LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or
  FULL OUTER JOIN.
}

feature {Complete ALTER TABLE support} {
  Only the RENAME TABLE and ADD COLUMN variants of the 
  ALTER TABLE command are supported.  Other kinds of ALTER TABLE operations
  such as
  DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.
}

feature {Complete trigger support} {
  FOR EACH ROW triggers are supported but not FOR EACH STATEMENT