Project

General

Profile

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.

In order to create an appropriate test plan, a default template can be used (File > Templates... > select JDBC Load Test > Create). This will generate a test plan containing:
  1. JDBC Connection Configuration: used for defining the connection parameters
    - Set the Auto Commit and Transaction Isolation to the reflect testcase's approach
    - Set the Database URL to 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_store flag 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 file instead of mem (example: jdbc:h2:file:~/.h2/fwd.h2;). Other connection string options can be found in the official H2 documentation.
    - Set JDBC Driver class to org.h2.Driver
    - Set Username to sa and let the Password empty
  2. Thread Group: used for defining the workload for each user.
    - Set Number of Threads (users) to the number of users which should test the database concurrently.
    - Set Loop count to the number of times the provided test should be executed sequentially by each user.
  3. JDBC Request: used for encapsulating the SQL which is meant to be executed by the database.
    - Set Query Type to the appropriate statement type (Update Statement is the most permissive as it allows commits and selects as well).
  4. 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:
  1. Saved test plans (*.jmx files). All of those contain a single local temporary table for each user named tt.
    - The full_test_2000.jmx test stresses 5 users with 5 batches of 2000 statements.
    - The *_test_200.jmx tests 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.
  2. A generating script generate_full_sql.py used 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:
  1. 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.
  2. CustomTest.java: is an example of how a H2 stress test should look like.
  3. SqlTest.java: is a helper which reads a file and executes the SQL statements inside line by line.
This type of profiler is at best used together with:
  1. log_to_sql.py, which parses the FWD generated server.log and 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 input.txt.
  2. log_to_java.py, which parses the FWD generated server.log and 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 input2.txt.

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:

ant deploy

4. Set 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 deploy/client-gui/perf-results.txt

© 2020 Golden Code Development Corporation. ALL RIGHTS RESERVED.

apache-jmeter-tests.zip (757 KB) Adrian Lungu, 07/15/2020 11:15 AM

fwd-h2-profiler.zip (3.93 KB) Adrian Lungu, 07/15/2020 11:17 AM