1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
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
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
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
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
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
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!--
Copyright 2004-2008 H2 Group. Multiple-Licensed under the H2 License, Version 1.0,,
and under the Eclipse Public License, Version 1.0
(http://h2database.com/html/license.html).
Initial Developer: H2 Group
-->
<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>
Performance
</title><link rel="stylesheet" type="text/css" href="stylesheet.css" />
<!-- [search] { -->
<script type="text/javascript" src="navigation.js"></script>
</head><body onload="frameMe();">
<table class="content"><tr class="content"><td class="content"><div class="contentDiv">
<!-- } -->
<h1>Performance</h1>
<a href="#performance_comparison">
Performance Comparison</a><br />
<a href="#poleposition_benchmark">
PolePosition Benchmark</a><br />
<a href="#application_profiling">
Application Profiling</a><br />
<a href="#database_profiling">
Database Profiling</a><br />
<a href="#database_performance_tuning">
Performance Tuning</a><br />
<br /><a name="performance_comparison"></a>
<h2>Performance Comparison</h2>
<p>
In most cases H2 is a lot faster than all other
(open source and not open source) database engines.
Please note this is mostly a single connection benchmark run on one computer.
</p>
<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>
<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>
</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>
<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>
</table>
<h3>Benchmark Results and Comments</h3>
<h4>H2</h4>
<p>
Version 1.0.78 (2008-08-28) was used for the test.
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.
</p>
<h4>HSQLDB</h4>
<p>
Version 1.8.0.10 was used for the test.
Cached tables are used in this test (hsqldb.default_table_type=cached),
and the write delay is 1 second (SET WRITE_DELAY 1).
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:
</p>
<pre>
SELECT COUNT(DISTINCT S_I_ID) FROM ORDER_LINE, STOCK
WHERE OL_W_ID=? AND OL_D_ID=? AND OL_O_ID<? AND OL_O_ID>=?
AND S_W_ID=? AND S_I_ID=OL_I_ID AND S_QUANTITY<?
</pre>
<p>
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.
</p>
<h4>Derby</h4>
<p>
Version 10.4.2.0 was used for the test. Derby is clearly the slowest embedded database in this test.
This seems to be a structural problem, because all operations are really slow.
It will not be easy for the developers of Derby to improve the performance to a reasonable level.
A few problems have been identified: Leaving autocommit on is a problem for Derby.
If it is switched off during the whole test, the results are about 20% better for Derby.
</p>
<h4>PostgreSQL</h4>
<p>
Version 8.3.4 was used for the test.
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
MySQL, the reason could be the network layer.
The memory usage number is incorrect, because only the memory usage of the JDBC driver is measured.
</p>
<h4>MySQL</h4>
<p>
Version 5.0.67 was used for the test.
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.
</p>
<h4>Firebird</h4>
<p>
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.
</p>
<h4>Why Oracle / MS SQL Server / DB2 are Not Listed</h4>
<p>
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.
</p>
<h3>About this Benchmark</h3>
<h4>Number of Connections</h4>
<p>
This is mostly a single-connection benchmark.
BenchB uses multiple connections; the other tests use one connection.
</p>
<h4>Real-World Tests</h4>
<p>
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).
BenchB is similar to the TPC-B test, using multiple connections (one thread per connection).
BenchC is similar to the TPC-C test, but single connection / single threaded.
</p>
<h4>Comparing Embedded with Server Databases</h4>
<p>
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.
</p>
<h4>Test Platform</h4>
<p>
This test is run on Windows XP with the virus scanner switched off.
The VM used is Sun JDK 1.5.
</p>
<h4>Multiple Runs</h4>
<p>
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).
This benchmark runs three times, but only the last run is measured.
</p>
<h4>Memory Usage</h4>
<p>
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,
but there is only a limited amount of memory available on the system.
HSQLDB tables are kept fully in memory by default; this benchmark
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.
</p>
<h4>Delayed Operations</h4>
<p>
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).
</p>
<h4>Transaction Commit / Durability</h4>
<p>
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
business transaction in the other benchmarks. For databases that support delayed commits,
a delay of one second is used.
</p>
<h4>Using Prepared Statements</h4>
<p>
Wherever possible, the test cases use prepared statements.
</p>
<h4>Currently Not Tested: Startup Time</h4>
<p>
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.
</p>
<br /><a name="poleposition_benchmark"></a>
<h2>PolePosition Benchmark</h2>
<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>
<br /><a name="application_profiling"></a>
<h2>Application Profiling</h2>
<h3>Analyze First</h3>
<p>
Before trying to optimize the performance, it is important to know where the time is actually spent.
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.
There are various ways to analyze the application. In some situations it is possible to
compare two implementations and use System.currentTimeMillis() to find out which one is faster.
But this does not work for complex applications with many modules, and for memory problems.
</p>
<p>
A very good tool to measure both the memory and the CPU is the
<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.
</p>
<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>
java -cp h2.jar org.h2.tools.ConvertTraceFile
-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>
<br /><a name="database_performance_tuning"></a>
<h2>Database Performance Tuning</h2>
<h3>Virus Scanners</h3>
<p>
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.
</p>
<h3>Using the Trace Options</h3>
<p>
If the main performance hot spots are in the database engine, in many cases the performance
can be optimized by creating additional indexes, or changing the schema. Sometimes the
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>.
</p>
<h3>Index Usage</h3>
<p>
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.
</p>
<h3>Optimizer</h3>
<p>
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.
</p>
<h3>Expression Optimization</h3>
<p>
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.
</p>
<h3>COUNT(*) Optimization</h3>
<p>
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.
</p>
<h3>Updating Optimizer Statistics / Column Selectivity</h3>
<p>
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.
</p><p>
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.
</p><p>
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.
</p>
<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>
<!-- [close] { --></div></td></tr></table><!-- } --><!-- analytics --></body></html>