SQLite

Check-in [73c904e57a]
Login

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

Overview
Comment:Update the speed comparison documentation to show the improved performance of PostgreSQL after performance tuning. (CVS 869)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 73c904e57a158820705daf5737e0fc825cfe1aea
User & Date: drh 2003-02-16 22:36:03.000
Context
2003-02-16
22:48
Couple of last-minute changes before 2.8.0. (CVS 871) (check-in: 8192c937d5 user: drh tags: trunk)
22:36
Update the speed comparison documentation to show the improved performance of PostgreSQL after performance tuning. (CVS 869) (check-in: 73c904e57a user: drh tags: trunk)
22:21
Added test code to check for file descriptor leaks. All regression tests pass now on both win2k and linux. (CVS 868) (check-in: 75ba78280f user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to www/speed.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the speed.html file.
#
set rcsid {$Id: speed.tcl,v 1.10 2003/01/25 14:25:42 drh Exp $ }

puts {<html>
<head>
  <title>Database Speed Comparison: SQLite versus PostgreSQL</title>
</head>
<body bgcolor=white>
<h1 align=center>




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the speed.html file.
#
set rcsid {$Id: speed.tcl,v 1.11 2003/02/16 22:36:03 drh Exp $ }

puts {<html>
<head>
  <title>Database Speed Comparison: SQLite versus PostgreSQL</title>
</head>
<body bgcolor=white>
<h1 align=center>
︙︙
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

The following are general
conclusions drawn from these experiments:
</p>

<ul>
<li><p>
  SQLite 2.7.6 is significantly faster (sometimes as much as 10 or
  20 times faster) than PostgreSQL 7.1.3
  for most common operations.  
</p></li>
<li><p>
  SQLite 2.7.6 is often faster (sometimes
  more than twice as fast) than MySQL 3.23.41
  for most common operations.
</p></li>
<li><p>








|
|








23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

The following are general
conclusions drawn from these experiments:
</p>

<ul>
<li><p>
  SQLite 2.7.6 is significantly faster (sometimes as much as 10 or
  20 times faster) than the default PostgreSQL 7.1.3 installation
  on RedHat 7.3 for most common operations.  
</p></li>
<li><p>
  SQLite 2.7.6 is often faster (sometimes
  more than twice as fast) than MySQL 3.23.41
  for most common operations.
</p></li>
<li><p>
︙︙
72
73
74
75
76
77
78




79
80
81
82
83



84
85
86





87
88
89
90
91
92
93

<p>
The PostgreSQL and MySQL servers used were as delivered by default on
RedHat 7.2.  (PostgreSQL version 7.1.3 and MySQL version 3.23.41.)
No effort was made to tune these engines.  Note in particular
the the default MySQL configuration on RedHat 7.2 does not support
transactions.  Not having to support transactions gives MySQL a
big speed advantage, but SQLite is still able to hold its own on most




tests.  On the other hand, I am told that the default PostgreSQL
configuration is unnecessarily conservative (it is designed to
work on a machine with 8MB of RAM) and that PostgreSQL could
be made to run a lot faster with some knowledgable configuration
tuning.  I have not, however, been able to personally confirm



these reports.
</p>






<p>
SQLite was tested in the same configuration that it appears
on the website.  It was compiled with -O6 optimization and with
the -DNDEBUG=1 switch which disables the many "assert()" statements
in the SQLite code.  The -DNDEBUG=1 compiler option roughly doubles
the speed of SQLite.
</p>








>
>
>
>
|
|


|
>
>
>
|


>
>
>
>
>








72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105

<p>
The PostgreSQL and MySQL servers used were as delivered by default on
RedHat 7.2.  (PostgreSQL version 7.1.3 and MySQL version 3.23.41.)
No effort was made to tune these engines.  Note in particular
the the default MySQL configuration on RedHat 7.2 does not support
transactions.  Not having to support transactions gives MySQL a
big speed advantage, but SQLite is still able to hold its own on most
tests.
</p>

<p>
I am told that the default PostgreSQL configuration in RedHat 7.3
is unnecessarily conservative (it is designed to
work on a machine with 8MB of RAM) and that PostgreSQL could
be made to run a lot faster with some knowledgable configuration
tuning.
Matt Sergeant reports that he has tuned his PostgreSQL installation
and rerun the tests shown below.  His results show that
PostgreSQL and MySQL run at about the same speed.  For Matt's
results, visit
</p>

<blockquote>
<a href="http://www.sergeant.org/sqlite_vs_pgsync.html">
http://www.sergeant.org/sqlite_vs_pgsync.html</a>
</blockquote>

<p>
SQLite was tested in the same configuration that it appears
on the website.  It was compiled with -O6 optimization and with
the -DNDEBUG=1 switch which disables the many "assert()" statements
in the SQLite code.  The -DNDEBUG=1 compiler option roughly doubles
the speed of SQLite.
</p>
︙︙