Documentation Source Text

Check-in [555bf82e10]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update the speed-and-size spreadsheet and the cpu.html page. Also make minor tweaks to the omitted.html page.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 555bf82e10e0f92754f0d1f23192264af91fe89de7c4eb43e4f468081f988dd5
User & Date: drh 2018-11-24 19:14:44
Context
2018-11-24
20:24
Minor enhancements and updates to various documents. check-in: 9e12c649c9 user: drh tags: trunk
19:14
Update the speed-and-size spreadsheet and the cpu.html page. Also make minor tweaks to the omitted.html page. check-in: 555bf82e10 user: drh tags: trunk
2018-11-21
21:46
Improvements to the althttpd documentation. check-in: 44a1928f55 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to images/cpu-usage.jpg.

cannot compute difference between binary files

Changes to misc/speed-size-graph.ods.

cannot compute difference between binary files

Changes to pages/cpu.in.

     2      2   <tcl>hd_keywords {CPU cycles used} {CPU performance measurement}</tcl>
     3      3   
     4      4   <table_of_contents>
     5      5   
     6      6   <h1>Overview</h1>
     7      7   
     8      8   <p>The graph below shows the number of CPU cycles used by SQLite on a
     9         -standard workload, for all versions of SQLite going back about 9 years.
    10         -Recent versions of SQLite use less than a third of the CPU cycles 
            9  +standard workload, for versions of SQLite going back about 10 years.
           10  +Recent versions of SQLite use about one third as many the CPU cycles
    11     11   compared to older versions.
    12     12   
    13     13   <p>
    14     14   This article describes how the SQLite developers measure CPU usage,
    15     15   what those measurements actually mean, and the techniques used by
    16     16   SQLite developers on their continuing quest to further reduce the
    17     17   CPU usage of the SQLite library.
................................................................................
    18     18   </p>
    19     19   
    20     20   <center>
    21     21   <hr>
    22     22   <div class="imgcontainer">
    23     23   <img src="./images/cpu-usage.jpg"></img></div><br>
    24     24   Measured using cachegrind on Ubuntu 16.04 on x64 with gcc 5.4.0 and -Os.<br>
    25         -Values normalized so that version 3.23.1 is 100%.
    26     25   <hr>
    27     26   </center>
    28     27   
    29     28   <h1>Measuring Performance</h1>
    30     29   
    31     30   <p>In brief, the CPU performance of SQLite is measured as follows:
    32     31   
................................................................................
   118    117   script compiles the speedtest1.c program, runs it under cachegrind,
   119    118   processes the cachegrind output using the
   120    119   [https://sqlite.org/src/file/tool/cg_anno.tcl | cg_anno.tcl] TCL
   121    120   script, then saves the results in a series of text files.
   122    121   Typical output from the speed-check.sh script looks like this:
   123    122   
   124    123   <blockquote><pre>
   125         -==16429== I   refs:      <b><font color="red">1,291,005,499</font></b>
   126         -==16429== I1  misses:       24,688,182
   127         -==16429== LLi misses:            5,027
   128         -==16429== I1  miss rate:          1.91%
   129         -==16429== LLi miss rate:          0.00%
   130         -==16429== 
   131         -==16429== D   refs:        663,242,182  (418,445,823 rd   + 244,796,359 wr)
   132         -==16429== D1  misses:        5,958,032  (  3,902,273 rd   +   2,055,759 wr)
   133         -==16429== LLd misses:           45,636  (     14,803 rd   +      30,833 wr)
   134         -==16429== D1  miss rate:           0.8% (        0.9%     +         0.8%  )
   135         -==16429== LLd miss rate:           0.0% (        0.0%     +         0.0%  )
   136         -==16429== 
   137         -==16429== LL refs:          30,646,214  ( 28,590,455 rd   +   2,055,759 wr)
   138         -==16429== LL misses:            50,663  (     19,830 rd   +      30,833 wr)
   139         -==16429== LL miss rate:            0.0% (        0.0%     +         0.0%  )
          124  +==8683== 
          125  +==8683== I   refs:      <font color="red">1,060,925,768</font>
          126  +==8683== I1  misses:       23,731,246
          127  +==8683== LLi misses:            5,176
          128  +==8683== I1  miss rate:          2.24%
          129  +==8683== LLi miss rate:          0.00%
          130  +==8683== 
          131  +==8683== D   refs:        557,686,925  (361,828,925 rd   + 195,858,000 wr)
          132  +==8683== D1  misses:        5,067,063  (  3,544,278 rd   +   1,522,785 wr)
          133  +==8683== LLd misses:           57,958  (     16,067 rd   +      41,891 wr)
          134  +==8683== D1  miss rate:           0.9% (        1.0%     +         0.8%  )
          135  +==8683== LLd miss rate:           0.0% (        0.0%     +         0.0%  )
          136  +==8683== 
          137  +==8683== LL refs:          28,798,309  ( 27,275,524 rd   +   1,522,785 wr)
          138  +==8683== LL misses:            63,134  (     21,243 rd   +      41,891 wr)
          139  +==8683== LL miss rate:            0.0% (        0.0%     +         0.0%  )
   140    140      text	   data	    bss	    dec	    hex	filename
   141         - 466711	   6256	   1864	 <b><font color="red">474831</font></b>	  73ecf	sqlite3.o
   142         - 199979  914462 7026217 sqlite3.c
          141  + 523044	   8240	   1976	 <font color="red">533260</font>	  8230c	sqlite3.o
          142  + 220507 1007870 7769352 sqlite3.c
   143    143   </pre></blockquote>
   144    144   
   145    145   <p>The important parts of the output (the parts that the developers pay
   146    146   the most attention to) are shown in red.
   147    147   Basically, the developers want to know the size of the compiled SQLite
   148    148   library and how many CPU cycles were needed to run the performance test.
   149    149   
   150    150   <p>The output from the 
   151    151   [https://sqlite.org/src/file/tool/cg_anno.tcl | cg_anno.tcl] script
   152    152   shows the number of CPU cycles spent on each line of code.
   153    153   The report is approximately 80,000 lines long.  The following is a brief
   154    154   snippet taken from the middle of the report to show what it looks like:
   155    155   
   156         -<hr>
   157    156   <blockquote><pre>
   158    157            .  SQLITE_PRIVATE int sqlite3BtreeNext(BtCursor *pCur, int *pRes){
   159    158            .    MemPage *pPage;
   160    159            .    assert( cursorOwnsBtShared(pCur) );
   161    160            .    assert( pRes!=0 );
   162    161            .    assert( *pRes==0 || *pRes==1 );
   163    162            .    assert( pCur-&gt;skipNext==0 || pCur-&gt;eState!=CURSOR_VALID );
................................................................................
   173    172      728,110    if( pPage-&gt;leaf ){
   174    173            .      return SQLITE_OK;
   175    174            .    }else{
   176    175        3,117      return moveToLeftmost(pCur);
   177    176            .    }
   178    177      721,876  }
   179    178   </pre></blockquote>
   180         -<hr>
   181    179   
   182    180   <p>
   183    181   The numbers on the left are the CPU cycle counts for that line of code,
   184    182   of course.
   185    183   
   186    184   <p>
   187    185   The cg_anno.tcl script removes extraneous details from the default 

Changes to pages/omitted.in.

    19     19   
    20     20   feature {RIGHT and FULL OUTER JOIN} {
    21     21     LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or
    22     22     FULL OUTER JOIN.
    23     23   }
    24     24   
    25     25   feature {Complete ALTER TABLE support} {
    26         -  Only the RENAME TABLE and ADD COLUMN variants of the 
           26  +  Only the RENAME TABLE, ADD COLUMN, and RENAME COLUMN variants of the 
    27     27     ALTER TABLE command are supported.  Other kinds of ALTER TABLE operations
    28     28     such as
    29     29     DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.
    30     30   }
    31     31   
    32     32   feature {Complete trigger support} {
    33     33     FOR EACH ROW triggers are supported but not FOR EACH STATEMENT