Performance Testing the H2 Database¶
Apache JMeter for H2 profiling¶
Apache JMeter is a powerful tool for profiling any database through a JDBC connection (https://jmeter.apache.org/download_jmeter.cgi). By default, JMeter does not contain any H2 driver. A H2 JAR should be added to
apache-jmeter-*/lib/ in order to be able to use an
org.h2.Driver JDBC Driver class. Also recommended is to increase the Java memory limit by editing
apache-jmeter-*/bin/jmeter. For the examples below, the 5.3 version was used.
File > Templates... > select JDBC Load Test > Create). This will generate a test plan containing:
- JDBC Connection Configuration: used for defining the connection parameters
- Set the
Transaction Isolationto the reflect testcase's approach
- Set the
Database URLto the connection string used by FWD (example:
jdbc:h2:mem:_temp;db_close_delay=-1;mv_store=false;query_cache_size=1024;). In order to change to the MVStore engine, set the
mv_storeflag to true. For using private databases for each user, omit the name of the database (example:
jdbc:h2:mem:;). For using a not in-memory database, use
jdbc:h2:file:~/.h2/fwd.h2;). Other connection string options can be found in the official H2 documentation.
JDBC Driver classto
saand let the
- Thread Group: used for defining the workload for each user.
Number of Threads (users)to the number of users which should test the database concurrently.
Loop countto the number of times the provided test should be executed sequentially by each user.
- JDBC Request: used for encapsulating the SQL which is meant to be executed by the database.
Query Typeto the appropriate statement type (
Update Statementis the most permissive as it allows commits and selects as well).
- View Results Tree: displays the results for each JDBC request.
- Check the Sampler results periodically to ensue that JDBC requests do not fail.
After configuring the test plan accordingly, press the run button to trigger the testing. In the top-right corner, there is information about the time elapsed, JMeter related problems, the number of active threads and the total number of threads. A plan should be run multiple times - first run's time usually is far from the times of the next runs.
For comparing several H2 versions, the H2 JAR in
apache-jmeter-*/lib/ should be changed. In order to load the replacing JAR, JMeter should be restarted. Note that sometimes the
Database URL should be changed as some options are added/removed from one version to another.
The testing results are based on the elapsed time which is displayed in the top-right corner.A set of test plans was already created. The apache-jmeter-tests.zip contains several tests used by now when profiling H2 through Apache JMeter. The archive contains:
- Saved test plans (
*.jmxfiles). All of those contain a single local temporary table for each user named
full_test_2000.jmxtest stresses 5 users with 5 batches of 2000 statements.
*_test_200.jmxtests stress 200 users with 5 batches of 200 statements. The "heavy" tests contain 50% statements of a specific type, while the full tests contain 20% statements of each type.
- A generating script
generate_full_sql.pyused for randomly generating SQL tests. This was used for creating the JDBC requests in the test plans above.
The tests above contain randomly generated SQL and it doesn't necessarily reflect the use case in FWD. They are mainly used for plain performance tests in order to prove an increase or a decrease of performance from one version to another as an overall matter. They can also be used in order to test different behaviors by changing the connection string options, transaction isolation, auto commit or users.
H2 native profiling¶
Another way to profile H2 is to make use of the native profiling tool described at http://h2database.com/html/performance.html#database_profiling. This can tell how much time was spent on each statement, or the total time spent on a repetitive statement. Beside this, it does a counting job and accuracy computing. However, this is not a very strong tool after all as is does not provide a very accurate estimation of the times. In fact, it parses the trace file generated by the H2 database and interprets the log times, which finally can provide inaccurate information for certain cases. Another strong disadvantage is that the trace file seems to be generated only for databases which are not in-memory (TODO: is a trace file generated for an in-memory database?), and that this type of profiling is for one user only.
The usage instructions for this type of profiling are already in depth described in the official documentation. For simple manual profiling, one can check the trace file in order to compute the time difference between the start and the finish. This can be more reliable than the "stetement-wise" profiling. For comparing several H2 versions, make sure that different databases are used. Otherwise, there will be only one trace file which will contain data from multiple runs.
Finally, a strong point is that this type of profiling can be easily used together with FWD, as the trace file is generated next to the disk persisted database (if the required changes are done in FWD in order to work which such database type) and it contains FWD specific generated statements.
Custom Java profiler¶A way to profile H2 in regard with FWD is by using the fwd-h2-profiler.zip custom Java profiler. This contains:
- Test.java: used for spawning several threads/users which will execute the same H2 test. It can run the same testing plan multiple times and compute the average, maximum time and minimum time.
- CustomTest.java: is an example of how a H2 stress test should look like.
- SqlTest.java: is a helper which reads a file and executes the SQL statements inside line by line.
log_to_sql.py, which parses the FWD generated
server.logand extracts all SQL statements which are run against H2 (requires INFO logging level and H2 TRACE_LEVEL_FILE set on level 4). The file to be parsed should be in the same folder as the script and renamed to
log_to_java.py, which parses the FWD generated
server.logand extracts all Java statements which use the H2 database (requires FINE logging level and H2 TRACE_LEVEL_FILE set on level 4). The file to be parsed should be in the same folder as the script and renamed to
Once the SQL statements are extracted, they can be used as separate testcase both for Apache JMeter and H2 native profiling. The custom Java profiler allows the testing of such SQL statements through
SqlTest.java. The disadvantage of using a plain SQL file is that there are some connection options which are not expressed. By using the generated Java statements, the exact behavior as in FWD is described (including connection settings). However, the parsed Java statements can't be included in a single Java file as it exceeds the maximum method size allowed. This means that the file should be either automatically parsed or manually reinterpreted.
This type of profiling is closer to the real FWD use case. The
Test.java class handles all the testing job by computing the times. Comparing H2 versions means including different H2 JAR in the class-path.
Running FWD-based performance tests for H2¶
There is a set of testcases designed to test performance of H2 database engine in FWD usage scenarios. That is performed by accessing (create/read/update/delete) a large number of records in the temp database. These testcases are a part of the New Test Suite, they are located in the
h2_performance subdirectory of this suite. You can use the following instructions to run these testcases:
1. Set up the New Test Suite using the instructions provided here New Test Suite. You don't need to run conversion step or any of the steps following it.
2. Put the names of all testcases in
h2_performance subdirectory into the conversion list
file-cvt-list.txt, which is:
h2_performance/perf-delete-bulk.p h2_performance/perf-update.p h2_performance/perf-create.p h2_performance/perf-update-foreach.p h2_performance/perf-buffer-copy.p h2_performance/perf-read.p h2_performance/perf-find-read.p h2_performance/perf-delete.p h2_performance/perf-read-foreach.p h2_performance/perf-test-runner.p
3. Convert these testcases and build jars:
h2_performance/perf-test-runner.p as the entry procedure by modifying
deploy/server/directory.xml. Search for
p2j-entry and update this parameter:
<node class="string" name="p2j-entry"> <node-attribute name="value" value="com.goldencode.testcases.h2_performance.PerfTestRunner.execute"/> </node>
5. Start FWD server:
cd deploy/server ./server.sh
6. Run FWD client in a separate terminal:
cd ../client-gui ./client.sh
7. Wait for the client to finish. The results are in
© 2020 Golden Code Development Corporation. ALL RIGHTS RESERVED.