Skip to content

H
h2database
切换分支/标签
查找文件
Normal viewHistoryPermalink
performance.html 22.5 KB
Newer Older
Thomas Mueller's avatar
--no commit message
35e27cd5
 
Thomas Mueller committed
1 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
Thomas Mueller's avatar
--no commit message
95620da6
 
Thomas Mueller committed
2 
<!--
Thomas Mueller's avatar
Typo in the license
a812a4c3
 
Thomas Mueller committed
3 
Copyright 2004-2008 H2 Group. Multiple-Licensed under the H2 License, Version 1.0,
Thomas Mueller's avatar
add eclipse public license
2ed183fb
 
Thomas Mueller committed
4 5 
and under the Eclipse Public License, Version 1.0
(http://h2database.com/html/license.html).
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
6 7 
Initial Developer: H2 Group
-->
Thomas Mueller's avatar
--no commit message
35e27cd5
 
Thomas Mueller committed
8 9 
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head><meta http-equiv="Content-Type" content="text/html;charset=utf-8" /><title>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
10 
Performance
Thomas Mueller's avatar
--no commit message
b492f851
 
Thomas Mueller committed
11 
</title><link rel="stylesheet" type="text/css" href="stylesheet.css" />
Thomas Mueller's avatar
The documentation no longer uses a frameset (except the Javadocs).
20d71e3b
 
Thomas Mueller committed
12 
<!-- [search] { -->
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
13 14 15 
<script type="text/javascript" src="navigation.js"></script>
</head><body onload="frameMe();">
<table class="content"><tr class="content"><td class="content"><div class="contentDiv">
Thomas Mueller's avatar
The documentation no longer uses a frameset (except the Javadocs).
20d71e3b
 
Thomas Mueller committed
16 
<!-- } -->
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
17 18 19 

<h1>Performance</h1>
<a href="#performance_comparison">
Thomas Mueller's avatar
--no commit message
b492f851
 
Thomas Mueller committed
20 
    Performance Comparison</a><br />
Thomas Mueller's avatar
--no commit message
95620da6
 
Thomas Mueller committed
21 22 
<a href="#poleposition_benchmark">
    PolePosition Benchmark</a><br />
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
23 
<a href="#application_profiling">
Thomas Mueller's avatar
--no commit message
b492f851
 
Thomas Mueller committed
24 
    Application Profiling</a><br />
Thomas Mueller's avatar
--no commit message
755bd88a
 
Thomas Mueller committed
25 26 
<a href="#database_profiling">
    Database Profiling</a><br />
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
27 
<a href="#database_performance_tuning">
Thomas Mueller's avatar
--no commit message
b492f851
 
Thomas Mueller committed
28 
    Performance Tuning</a><br />
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
29
Thomas Mueller's avatar
--no commit message
b492f851
 
Thomas Mueller committed
30 
<br /><a name="performance_comparison"></a>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
31 
<h2>Performance Comparison</h2>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
32 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
33 
In most cases H2 is a lot faster than all other
Thomas Mueller's avatar
--no commit message
95620da6
 
Thomas Mueller committed
34 
(open source and not open source) database engines.
Thomas Mueller's avatar
--no commit message
927afe2d
 
Thomas Mueller committed
35 
Please note this is mostly a single connection benchmark run on one computer.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
36 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
37 38 39 40 

<h3>Embedded</h3>
<table border="1" class="bar">
<tr><th>Test Case</th><th>Unit</th><th>H2</th><th>HSQLDB</th><th>Derby</th></tr>
Thomas Mueller's avatar
--no commit message
7667ff29
 
Thomas Mueller committed
41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 
<tr><td>Simple: Init</td><td>ms</td><td>610</td><td>657</td><td>3187</td></tr>
<tr><td>Simple: Query (random)</td><td>ms</td><td>297</td><td>312</td><td>1828</td></tr>
<tr><td>Simple: Query (sequential)</td><td>ms</td><td>203</td><td>266</td><td>1766</td></tr>
<tr><td>Simple: Update (random)</td><td>ms</td><td>1078</td><td>1484</td><td>22031</td></tr>
<tr><td>Simple: Delete (sequential)</td><td>ms</td><td>234</td><td>281</td><td>7407</td></tr>
<tr><td>Simple: Memory Usage</td><td>MB</td><td>6</td><td>7</td><td>11</td></tr>
<tr><td>BenchA: Init</td><td>ms</td><td>859</td><td>438</td><td>4047</td></tr>
<tr><td>BenchA: Transactions</td><td>ms</td><td>5266</td><td>2875</td><td>17500</td></tr>
<tr><td>BenchA: Memory Usage</td><td>MB</td><td>9</td><td>14</td><td>10</td></tr>
<tr><td>BenchB: Init</td><td>ms</td><td>4016</td><td>2687</td><td>16875</td></tr>
<tr><td>BenchB: Transactions</td><td>ms</td><td>2609</td><td>3282</td><td>4250</td></tr>
<tr><td>BenchB: Memory Usage</td><td>MB</td><td>9</td><td>10</td><td>8</td></tr>
<tr><td>BenchC: Init</td><td>ms</td><td>891</td><td>594</td><td>5766</td></tr>
<tr><td>BenchC: Transactions</td><td>ms</td><td>4359</td><td>75438</td><td>11718</td></tr>
<tr><td>BenchC: Memory Usage</td><td>MB</td><td>9</td><td>18</td><td>9</td></tr>
<tr><td>Executed statements</td><td>#</td><td>594255</td><td>594255</td><td>594255</td></tr>
<tr><td>Total time</td><td>ms</td><td>20422</td><td>88314</td><td>96375</td></tr>
<tr><td>Statements per second</td><td>#</td><td>29098</td><td>6728</td><td>6166</td></tr>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
59 60 61 62 63 
</table>

<h3>Client-Server</h3>
<table border="1" class="bar">
<tr><th>Test Case</th><th>Unit</th><th>H2</th><th>HSQLDB</th><th>Derby</th><th>PostgreSQL</th><th>MySQL</th></tr>
Thomas Mueller's avatar
--no commit message
7667ff29
 
Thomas Mueller committed
64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 
<tr><td>Simple: Init</td><td>ms</td><td>3125</td><td>3312</td><td>7140</td><td>5109</td><td>3594</td></tr>
<tr><td>Simple: Query (random)</td><td>ms</td><td>3390</td><td>3328</td><td>9953</td><td>5422</td><td>4812</td></tr>
<tr><td>Simple: Query (sequential)</td><td>ms</td><td>3235</td><td>3219</td><td>9813</td><td>5296</td><td>3969</td></tr>
<tr><td>Simple: Update (random)</td><td>ms</td><td>3437</td><td>4562</td><td>26594</td><td>6766</td><td>5703</td></tr>
<tr><td>Simple: Delete (sequential)</td><td>ms</td><td>1391</td><td>1625</td><td>9343</td><td>2750</td><td>2094</td></tr>
<tr><td>Simple: Memory Usage</td><td>MB</td><td>7</td><td>7</td><td>13</td><td>0</td><td>1</td></tr>
<tr><td>BenchA: Init</td><td>ms</td><td>2781</td><td>2954</td><td>7828</td><td>4875</td><td>3859</td></tr>
<tr><td>BenchA: Transactions</td><td>ms</td><td>13015</td><td>11718</td><td>30031</td><td>17672</td><td>13187</td></tr>
<tr><td>BenchA: Memory Usage</td><td>MB</td><td>9</td><td>17</td><td>10</td><td>1</td><td>1</td></tr>
<tr><td>BenchB: Init</td><td>ms</td><td>12000</td><td>13047</td><td>30562</td><td>20687</td><td>15328</td></tr>
<tr><td>BenchB: Transactions</td><td>ms</td><td>5375</td><td>3688</td><td>8063</td><td>6250</td><td>4594</td></tr>
<tr><td>BenchB: Memory Usage</td><td>MB</td><td>10</td><td>12</td><td>8</td><td>1</td><td>1</td></tr>
<tr><td>BenchC: Init</td><td>ms</td><td>2390</td><td>1875</td><td>7015</td><td>2515</td><td>4062</td></tr>
<tr><td>BenchC: Transactions</td><td>ms</td><td>11391</td><td>77922</td><td>23766</td><td>13203</td><td>8141</td></tr>
<tr><td>BenchC: Memory Usage</td><td>MB</td><td>11</td><td>18</td><td>15</td><td>1</td><td>1</td></tr>
<tr><td>Executed statements</td><td>#</td><td>594255</td><td>594255</td><td>594255</td><td>594255</td><td>594255</td></tr>
<tr><td>Total time</td><td>ms</td><td>61530</td><td>127250</td><td>170108</td><td>90545</td><td>69343</td></tr>
<tr><td>Statements per second</td><td>#</td><td>9657</td><td>4669</td><td>3493</td><td>6563</td><td>8569</td></tr>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
82 83 84 85 86 
</table>

<h3>Benchmark Results and Comments</h3>

<h4>H2</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
87 
<p>
Thomas Mueller's avatar
--no commit message
7667ff29
 
Thomas Mueller committed
88 
Version 1.0.78 (2008-08-28) was used for the test.
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
89 90 91 92 93 94 95 96 
For simpler operations, the performance of H2 is about the same as for HSQLDB.
For more complex queries, the query optimizer is very important.
However H2 is not very fast in every case, certain kind of queries may still be slow.
One situation where is H2 is slow is large result sets, because they are buffered to
disk if more than a certain number of records are returned.
The advantage of buffering is, there is no limit on the result set size.
The open/close time is almost fixed, because of the file locking protocol: The engine waits
20 ms after opening a database to ensure the database files are not opened by another process.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
97 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
98 99 

<h4>HSQLDB</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
100 
<p>
Thomas Mueller's avatar
--no commit message
7667ff29
 
Thomas Mueller committed
101 
Version 1.8.0.10 was used for the test.
Thomas Mueller's avatar
--no commit message
9d0f0920
 
Thomas Mueller committed
102 103 
Cached tables are used in this test (hsqldb.default_table_type=cached),
and the write delay is 1 second (SET WRITE_DELAY 1).
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
104 105 106 
HSQLDB is fast when using simple operations.
HSQLDB is very slow in the last test (BenchC: Transactions), probably because is has a bad query optimizer.
One query where HSQLDB is slow is a two-table join:
Thomas Mueller's avatar
--no commit message
e59d810e
 
Thomas Mueller committed
107 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
108 109 110 111 112 
<pre>
SELECT COUNT(DISTINCT S_I_ID) FROM ORDER_LINE, STOCK
WHERE OL_W_ID=? AND OL_D_ID=? AND OL_O_ID&lt;? AND OL_O_ID>=?
AND S_W_ID=? AND S_I_ID=OL_I_ID AND S_QUANTITY&lt;?
</pre>
Thomas Mueller's avatar
--no commit message
e59d810e
 
Thomas Mueller committed
113 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
114 115 116 
The PolePosition benchmark also shows that the query optimizer does not do a very good job for some queries.
A disadvantage in HSQLDB is the slow startup / shutdown time (currently not listed) when using bigger databases.
The reason is, a backup of the database is created whenever the database is opened or closed.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
117 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
118 119 

<h4>Derby</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
120 
<p>
Thomas Mueller's avatar
--no commit message
7667ff29
 
Thomas Mueller committed
121 
Version 10.4.2.0 was used for the test. Derby is clearly the slowest embedded database in this test.
Thomas Mueller's avatar
--no commit message
95620da6
 
Thomas Mueller committed
122 
This seems to be a structural problem, because all operations are really slow.
Thomas Mueller's avatar
--no commit message
b09fd536
 
Thomas Mueller committed
123 
It will not be easy for the developers of Derby to improve the performance to a reasonable level.
Thomas Mueller's avatar
--no commit message
95620da6
 
Thomas Mueller committed
124 
A few problems have been identified: Leaving autocommit on is a problem for Derby.
Thomas Mueller's avatar
--no commit message
cb967665
 
Thomas Mueller committed
125 
If it is switched off during the whole test, the results are about 20% better for Derby.
Thomas Mueller's avatar
new experimental page store
8b03f980
 
Thomas Mueller committed
126 
Derby supports a testing mode (system property derby.system.durability=test) where durability is
Thomas Mueller's avatar
document derby.system.durability=test
bb6fe6fd
 
Thomas Mueller committed
127 128 129 130 
disabled. According to the documentation, this setting should be used for testing only,
as the database may not recover after a crash. Enabling this setting improves performance 
by a factor of 2.6 (embedded mode) or 1.4 (server mode). Even if enabled, Derby is still less 
than half as fast as H2 in default mode.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
131 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
132 133 

<h4>PostgreSQL</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
134 
<p>
Thomas Mueller's avatar
--no commit message
7667ff29
 
Thomas Mueller committed
135 
Version 8.3.4 was used for the test.
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
136 137 138 
The following options where changed in postgresql.conf:
fsync = off, commit_delay = 1000.
PostgreSQL is run in server mode. It looks like the base performance is slower than
Thomas Mueller's avatar
--no commit message
9d0f0920
 
Thomas Mueller committed
139 
MySQL, the reason could be the network layer.
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
140 
The memory usage number is incorrect, because only the memory usage of the JDBC driver is measured.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
141 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
142 143 

<h4>MySQL</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
144 
<p>
Thomas Mueller's avatar
--no commit message
7667ff29
 
Thomas Mueller committed
145 
Version 5.0.67 was used for the test.
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
146 147 148 149 150 151 152 153 154 155 
MySQL was run with the InnoDB backend.
The setting innodb_flush_log_at_trx_commit
(found in the my.ini file) was set to 0. Otherwise (and by default), MySQL is really slow
(around 140 statements per second in this test) because it tries to flush the data to disk for each commit.
For small transactions (when autocommit is on) this is really slow.
But many use cases use small or relatively small transactions.
Too bad this setting is not listed in the configuration wizard,
and it always overwritten when using the wizard.
You need to change this setting manually in the file my.ini, and then restart the service.
The memory usage number is incorrect, because only the memory usage of the JDBC driver is measured.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
156 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
157 158 

<h4>Firebird</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
159 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
160 161 162 
Firebird 1.5 (default installation) was tested, but the results are not published currently.
It is possible to run the performance test with the Firebird database,
and any information on how to configure Firebird for higher performance are welcome.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
163 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
164 165 

<h4>Why Oracle / MS SQL Server / DB2 are Not Listed</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
166 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
167 168 169 170 
The license of these databases does not allow to publish benchmark results.
This doesn't mean that they are fast. They are in fact quite slow,
and need a lot of memory. But you will need to test this yourself.
SQLite was not tested because the JDBC driver doesn't support transactions.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
171 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
172 173 174 175 

<h3>About this Benchmark</h3>

<h4>Number of Connections</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
176 
<p>
Thomas Mueller's avatar
--no commit message
95620da6
 
Thomas Mueller committed
177 
This is mostly a single-connection benchmark.
Thomas Mueller's avatar
--no commit message
ac3c906a
 
Thomas Mueller committed
178 
BenchB uses multiple connections; the other tests use one connection.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
179 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
180 181 

<h4>Real-World Tests</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
182 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
183 184 185 
Good benchmarks emulate real-world use cases. This benchmark includes 3 test cases:
A simple test case with one table and many small updates / deletes.
BenchA is similar to the TPC-A test, but single connection / single threaded (see also: www.tpc.org).
Thomas Mueller's avatar
--no commit message
927afe2d
 
Thomas Mueller committed
186 
BenchB is similar to the TPC-B test, using multiple connections (one thread per connection).
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
187 
BenchC is similar to the TPC-C test, but single connection / single threaded.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
188 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
189 190 

<h4>Comparing Embedded with Server Databases</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
191 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
192 193 194 195 
This is mainly a benchmark for embedded databases (where the application runs in the same
virtual machine than the database engine). However MySQL and PostgreSQL are not Java
databases and cannot be embedded into a Java application.
For the Java databases, both embedded and server modes are tested.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
196 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
197 198 

<h4>Test Platform</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
199 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
200 
This test is run on Windows XP with the virus scanner switched off.
Thomas Mueller's avatar
--no commit message
b09fd536
 
Thomas Mueller committed
201 
The VM used is Sun JDK 1.5.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
202 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
203 204 

<h4>Multiple Runs</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
205 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
206 207 208 
When a Java benchmark is run first, the code is not fully compiled and
therefore runs slower than when running multiple times. A benchmark
should always run the same test multiple times and ignore the first run(s).
Thomas Mueller's avatar
--no commit message
ac3c906a
 
Thomas Mueller committed
209 
This benchmark runs three times, but only the last run is measured.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
210 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
211 212 

<h4>Memory Usage</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
213 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
214 215 
It is not enough to measure the time taken, the memory usage is important as well.
Performance can be improved in databases by using a bigger in-memory cache,
Thomas Mueller's avatar
--no commit message
bccf4e46
 
Thomas Mueller committed
216 
but there is only a limited amount of memory available on the system.
Thomas Mueller's avatar
--no commit message
ac3c906a
 
Thomas Mueller committed
217 
HSQLDB tables are kept fully in memory by default; this benchmark
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
218 219 220 221 
uses 'disk based' tables for all databases.
Unfortunately, it is not so easy to calculate the memory usage of PostgreSQL
and MySQL, because they run in a different process than the test. This benchmark currently
does not print memory usage of those databases.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
222 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
223 224 

<h4>Delayed Operations</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
225 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
226 227 228 
Some databases delay some operations (for example flushing the buffers)
until after the benchmark is run. This benchmark waits between
each database tested, and each database runs in a different process (sequentially).
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
229 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
230 231 

<h4>Transaction Commit / Durability</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
232 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
233 234 235 236 237 238 239 240 
Durability means transaction committed to the database will not be lost.
Some databases (for example MySQL) try to enforce this by default by calling fsync() to flush the buffers, but
most hard drives don't actually flush all data. Calling fsync() slows down transaction commit a lot,
but doesn't always make data durable. When comparing the results, it is important to
think about the effect. Many database suggest to 'batch' operations when possible.
This benchmark switches off autocommit when loading the data, and calls commit after each 1000
inserts. However many applications need 'short' transactions at runtime (a commit after each update).
This benchmark commits after each update / delete in the simple benchmark, and after each
Thomas Mueller's avatar
--no commit message
95620da6
 
Thomas Mueller committed
241 
business transaction in the other benchmarks. For databases that support delayed commits,
Thomas Mueller's avatar
--no commit message
9d0f0920
 
Thomas Mueller committed
242 
a delay of one second is used.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
243 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
244 245 

<h4>Using Prepared Statements</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
246 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
247 
Wherever possible, the test cases use prepared statements.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
248 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
249 250 

<h4>Currently Not Tested: Startup Time</h4>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
251 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
252 253 254 255 256 257 258 
The startup time of a database engine is important as well for embedded use.
This time is not measured currently.
Also, not tested is the time used to create a database and open an existing database.
Here, one (wrapper) connection is opened at the start,
and for each step a new connection is opened and then closed.
That means the Open/Close time listed is for opening a connection
if the database is already in use.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
259 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
260
Thomas Mueller's avatar
--no commit message
95620da6
 
Thomas Mueller committed
261 262 
<br /><a name="poleposition_benchmark"></a>
<h2>PolePosition Benchmark</h2>
Thomas Mueller's avatar
--no commit message
9cf66529
 
Thomas Mueller committed
263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 
<p>
The PolePosition is an open source benchmark. The algorithms are all quite simple.
It was developed / sponsored by db4o.
</p>
<table border="1" class="bar">
<tr><th>Test Case</th><th>Unit</th><th>H2</th><th>HSQLDB</th><th>MySQL</th></tr>
<tr><td>Melbourne write</td><td>ms</td><td>369</td><td>249</td><td>2022</td></tr>
<tr><td>Melbourne read</td><td>ms</td><td>47</td><td>49</td><td>93</td></tr>
<tr><td>Melbourne read_hot</td><td>ms</td><td>24</td><td>43</td><td>95</td></tr>
<tr><td>Melbourne delete</td><td>ms</td><td>147</td><td>133</td><td>176</td></tr>
<tr><td>Sepang write</td><td>ms</td><td>965</td><td>1201</td><td>3213</td></tr>
<tr><td>Sepang read</td><td>ms</td><td>765</td><td>948</td><td>3455</td></tr>
<tr><td>Sepang read_hot</td><td>ms</td><td>789</td><td>859</td><td>3563</td></tr>
<tr><td>Sepang delete</td><td>ms</td><td>1384</td><td>1596</td><td>6214</td></tr>
<tr><td>Bahrain write</td><td>ms</td><td>1186</td><td>1387</td><td>6904</td></tr>
<tr><td>Bahrain query_indexed_string</td><td>ms</td><td>336</td><td>170</td><td>693</td></tr>
<tr><td>Bahrain query_string</td><td>ms</td><td>18064</td><td>39703</td><td>41243</td></tr>
<tr><td>Bahrain query_indexed_int</td><td>ms</td><td>104</td><td>134</td><td>678</td></tr>
<tr><td>Bahrain update</td><td>ms</td><td>191</td><td>87</td><td>159</td></tr>
<tr><td>Bahrain delete</td><td>ms</td><td>1215</td><td>729</td><td>6812</td></tr>
<tr><td>Imola retrieve</td><td>ms</td><td>198</td><td>194</td><td>4036</td></tr>
<tr><td>Barcelona write</td><td>ms</td><td>413</td><td>832</td><td>3191</td></tr>
<tr><td>Barcelona read</td><td>ms</td><td>119</td><td>160</td><td>1177</td></tr>
<tr><td>Barcelona query</td><td>ms</td><td>20</td><td>5169</td><td>101</td></tr>
<tr><td>Barcelona delete</td><td>ms</td><td>388</td><td>319</td><td>3287</td></tr>
<tr><td>Total</td><td>ms</td><td>26724</td><td>53962</td><td>87112</td></tr>
</table>
Thomas Mueller's avatar
--no commit message
b492f851
 
Thomas Mueller committed
291 
<br /><a name="application_profiling"></a>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
292 293 
<h2>Application Profiling</h2>
Thomas Mueller's avatar
--no commit message
95620da6
 
Thomas Mueller committed
294 
<h3>Analyze First</h3>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
295 
<p>
Thomas Mueller's avatar
--no commit message
95620da6
 
Thomas Mueller committed
296 
Before trying to optimize the performance, it is important to know where the time is actually spent.
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
297 298 
The same is true for memory problems.
Premature or 'blind' optimization should be avoided, as it is not an efficient way to solve the problem.
Thomas Mueller's avatar
--no commit message
95620da6
 
Thomas Mueller committed
299 
There are various ways to analyze the application. In some situations it is possible to
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
300 
compare two implementations and use System.currentTimeMillis() to find out which one is faster.
Thomas Mueller's avatar
--no commit message
95620da6
 
Thomas Mueller committed
301 
But this does not work for complex applications with many modules, and for memory problems.
Thomas Mueller's avatar
--no commit message
755bd88a
 
Thomas Mueller committed
302 303 
</p>
<p>
Thomas Mueller's avatar
--no commit message
95620da6
 
Thomas Mueller committed
304 
A very good tool to measure both the memory and the CPU is the
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
305 306 
<a href="http://www.yourkit.com">YourKit Java Profiler</a>. This tool is also used
to optimize the performance and memory footprint of this database engine.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
307 
</p>
Thomas Mueller's avatar
--no commit message
755bd88a
 
Thomas Mueller committed
308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 
<p>
A simple way to profile an application is to use the built-in profiling tool of java. Example:
</p>
<pre>
java -Xrunhprof:cpu=samples,depth=16 com.acme.Test
</pre>
<p>
Unfortunately, it is only possible to profile the application from start to end.
</p>

<br /><a name="database_profiling"></a>
<h2>Database Profiling</h2>
<p>
The ConvertTraceFile tool generates SQL statement statistics at the end of the SQL script file.
The format used is similar to the profiling data generated when using java -Xrunhprof. 
As an example, execute the the following script using the H2 Console:
</p>
<pre>
SET TRACE_LEVEL_FILE 3;
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
@LOOP 1000 INSERT INTO TEST VALUES(?, ?);
SET TRACE_LEVEL_FILE 0;
</pre>
<p>
Now convert the .trace.db file using the ConvertTraceFile tool:
</p>
<pre>
Thomas Mueller's avatar
h2*.jar instead of h2.jar
d2448d9f
 
Thomas Mueller committed
336 
java -cp h2*.jar org.h2.tools.ConvertTraceFile
Thomas Mueller's avatar
--no commit message
755bd88a
 
Thomas Mueller committed
337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 
  -traceFile "~/test.trace.db" -script "~/test.sql"
</pre>
<p>
The generated file <code>test.sql</code> will contain the SQL statements as well as the
following profiling data (results vary):
</p>
<pre>
-----------------------------------------
-- SQL Statement Statistics
-- time: total time in milliseconds (accumulated)
-- count: how many times the statement ran
-- result: total update count or row count
-----------------------------------------
-- self accu    time   count  result sql
--  62%  62%     158    1000    1000 INSERT INTO TEST VALUES(?, ?);
--  37% 100%      93       1       0 CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
--   0% 100%       0       1       0 DROP TABLE IF EXISTS TEST;
--   0% 100%       0       1       0 SET TRACE_LEVEL_FILE 3;
</pre>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
356
Thomas Mueller's avatar
--no commit message
b492f851
 
Thomas Mueller committed
357 
<br /><a name="database_performance_tuning"></a>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
358 359 360 
<h2>Database Performance Tuning</h2>

<h3>Virus Scanners</h3>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
361 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
362 363 364 365 366 367 
Some virus scanners scan files every time they are accessed.
It is very important for performance that database files are not scanned for viruses.
The database engine does never interprets the data stored in the files as programs,
that means even if somebody would store a virus in a database file, this would
be harmless (when the virus does not run, it cannot spread).
Some virus scanners allow excluding file endings. Make sure files ending with .db are not scanned.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
368 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
369 370 

<h3>Using the Trace Options</h3>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
371 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
372 
If the main performance hot spots are in the database engine, in many cases the performance
Thomas Mueller's avatar
--no commit message
95620da6
 
Thomas Mueller committed
373 
can be optimized by creating additional indexes, or changing the schema. Sometimes the
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
374 375 376 
application does not directly generate the SQL statements, for example if an O/R mapping tool
is used. To view the SQL statements and JDBC API calls, you can use the trace options.
For more information, see <a href="features.html#trace_options">Using the Trace Options</a>.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
377 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
378 379 

<h3>Index Usage</h3>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
380 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
381 382 383 384 385 386 387 388 
This database uses indexes to improve the performance of SELECT, UPDATE and DELETE statements.
If a column is used in the WHERE clause of a query, and if an index exists on this column,
then the index can be used. Multi-column indexes are used if all or the first columns of the index are used.
Both equality lookup and range scans are supported.
Indexes are not used to order result sets: The results are sorted in memory if required.
Indexes are created automatically for primary key and unique constraints.
Indexes are also created for foreign key constraints, if required.
For other columns, indexes need to be created manually using the CREATE INDEX statement.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
389 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
390 391 

<h3>Optimizer</h3>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
392 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
393 394 395 396 397 398 
This database uses a cost based optimizer. For simple and queries and queries with medium complexity
(less than 7 tables in the join), the expected cost (running time) of all possible plans is calculated,
and the plan with the lowest cost is used. For more complex queries, the algorithm first tries
all possible combinations for the first few tables, and the remaining tables added using a greedy algorithm
(this works well for most joins). Afterwards a genetic algorithm is used to test at most 2000 distinct plans.
Only left-deep plans are evaluated.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
399 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
400 401 

<h3>Expression Optimization</h3>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
402 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
403 404 405 406 
After the statement is parsed, all expressions are simplified automatically if possible. Operations
are evaluated only once if all parameters are constant. Functions are also optimized, but only
if the function is constant (always returns the same result for the same parameter values).
If the WHERE clause is always false, then the table is not accessed at all.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
407 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
408 409 

<h3>COUNT(*) Optimization</h3>
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
410 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
411 412 413 
If the query only counts all rows of a table, then the data is not accessed.
However, this is only possible if no WHERE clause is used, that means it only works for
queries of the form SELECT COUNT(*) FROM table.
Thomas Mueller's avatar
--no commit message
81c34acc
 
Thomas Mueller committed
414 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
415 416 

<h3>Updating Optimizer Statistics / Column Selectivity</h3>
Thomas Mueller's avatar
--no commit message
2ceb8a1f
 
Thomas Mueller committed
417 
<p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
418 419 420 421 
When executing a query, at most one index per joined table can be used.
If the same table is joined multiple times, for each join only one index is used.
Example: for the query SELECT * FROM TEST T1, TEST T2 WHERE T1.NAME='A' AND T2.ID=T1.ID,
two index can be used, in this case the index on NAME for T1 and the index on ID for T2.
Thomas Mueller's avatar
--no commit message
2ceb8a1f
 
Thomas Mueller committed
422 
</p><p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
423 424 425 426 427 428 429 430 
If a table has multiple indexes, sometimes more than one index could be used.
Example: if there is a table TEST(ID, NAME, FIRSTNAME) and an index on each column,
then two indexes could be used for the query SELECT * FROM TEST WHERE NAME='A' AND FIRSTNAME='B',
the index on NAME or the index on FIRSTNAME. It is not possible to use both indexes at the same time.
Which index is used depends on the selectivity of the column. The selectivity describes the 'uniqueness' of
values in a column. A selectivity of 100 means each value appears only once, and a selectivity of 1 means
the same value appears in many or most rows. For the query above, the index on NAME should be used
if the table contains more distinct names than first names.
Thomas Mueller's avatar
--no commit message
2ceb8a1f
 
Thomas Mueller committed
431 
</p><p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
432 433 
The SQL statement ANALYZE can be used to automatically estimate the selectivity of the columns in the tables.
This command should be run from time to time to improve the query plans generated by the optimizer.
Thomas Mueller's avatar
--no commit message
2ceb8a1f
 
Thomas Mueller committed
434 
</p>
Thomas Mueller's avatar
--no commit message
23c4f01c
 
Thomas Mueller committed
435
Thomas Mueller's avatar
javadocs
528531e5
 
Thomas Mueller committed
436 437 438 439 440 441 
<h3>Optimization Examples</h3>
<p>
See <code>src/test/org/h2/samples/optimizations.sql</code> for a few examples of queries 
that benefit from special optimizations built into the database. 
</p>
Thomas Mueller's avatar
The documentation no longer uses a frameset (except the Javadocs).
20d71e3b
 
Thomas Mueller committed
442 
<!-- [close] { --></div></td></tr></table><!-- } --><!-- analytics --></body></html>