Project

General

Profile

Wiki »

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.

Executing statements one-by-one (H2 v1.4.210 and newer versions)

The apache-jmeter-new-version-tests.zip contains several tests for profiling H2 through Apache JMeter. The archive contains:

1. A test plan (h2_profiling.jmx file). This file contains several JDBC Connection Configurations and Thread Groups depending on the type of test that we want to execute:
  • Each thread group represents a type of test (delete, insert, update, etc.) and have the following specifications:
    - A single local temporary table with 10 fields named tt.
    - full_test_5000, heavy_commit_test_5000, heavy_delete_test_5000 and heavy_update_test_5000 tests stress 10 users with 5 batches of 5000 statements.
    - heavy_insert_test_5000 test stresses 5 users with 2 batches of 5000 statements.
    - heavy_select_test_5000 test stresses 5 users with 5 batches of 5000 statements.
  • An additional thread group for testing the Hotel GUI application:
    - hotel_test_6000 stresses 1 user with 250 batches of 6000 statements.

Note: the heavy tests contain 50% statements of a specific type, while the full test contains 20% statements of each type.

2. A generating script generate.py derived from generate_full_sql.py in apache-jmeter-tests.zip used to randomly generate SQL tests. It was only modified to generate SQL statements for a larger table.

3. Another generating script sql_to_csv.py used to parse SQL statements generated by the generate.py script and add the Query Type of the statement that will be executed by Apache JMeter. The statements are split into Select Statement (select) and Update Statement (update, delete, create, etc.).

Note: Output the results of generate.py into a *.txt file. Execute sql_to_csv.py [input_filename] [output_filename]. The input should be the filename of the generated .txt file.

4. A few CSV files within a tests folder than can be used directly for the current configured Thread Groups. The CSV file for the hotel_test_6000 should prove to be really useful for future testing.

The test plans for the newer versions use a Loop Controller and their structure is the following:

1. JDBC Connection Configuration: used for defining the connection parameters

  • h2_profiling.jmx already contains 4 JDBC Connection Configurations:
    • jdbc:h2:mem:;db_close_delay=-1;mv_store=false;query_cache_size=1024; for testing version 1.4.200 in PageStore mode with private databases for each user.
    • jdbc:h2:mem:;db_close_delay=-1;query_cache_size=1024; for testing using MVStore (default mode) is any version with private databases.
    • jdbc:h2:mem:_temp;db_close_delay=-1;mv_store=false;query_cache_size=1024; for testing version 1.4.200 in PageStore mode with shared database.
    • jdbc:h2:mem:_temp;db_close_delay=-1;query_cache_size=1024; for testing using MVStore (default mode) in any version with shared database.
  • each JDBC Connection Configuration can be enabled/disabled (only 1 connection should be enabled at once).

2. Thread Group: used for defining the workload for each user.

  • Thread Groups can be enabled/disabled depending on the type of test that we want to do. (only 1 Thread Group should be enabled at once, running more tests may influence the results obtained).
  • The Thread Group has the following structure:
    • JDBC Request: used for encapsulating the SQL which is meant to be executed by the database.
      - Used in most Thread Groups to create an initial table for running statements. Exception from this is hotel_test_6000 since statements were taken directly from server.log.
    • Loop Controller: used for running iterations infinitely or a number of times.
      • CSV Data Set Config: used to set a .csv file for reading
        - Set the filename to the path which contains the CSV created by the sql_to_csv.py.
        - Create 2 variable names: STMT,TYPE. STMT will represent the SQL statement and TYPE will represent the Query Type for the SQL statement.
        - Set the delimiter to &. The delimiter is used to split a line and get STMT and TYPE
        - Set the Recycle on EOF to True. Once JMeter reaches the end of the file, it will go to the beginning and iterate the CSV file
        - Stop thread on EOF to False. Once a thread reaches the end of the file, it will not close.
        - Sharing mode: Current thread. Each thread will open and read the CSV file separately.
        - The CSV Data Set Config reads line by line from the specified .csv file, splits the lines using the delimiter and sets the STMT and TYPE variables.
      • JDBC Request:
        - Set the Query Type to ${TYPE} and the Query contents to ${STMT}.
        - STMT and TYPE will be taken from the CSV Data Set Config.
      • View Results Tree: displays the results for each JDBC request.

      - Uncheck the Infinite option and specify the number of statements in the CSV file (number of lines) unless Stop Thread on EOF is true.

This profiling approach allows for clear results between 1.4.200 and newer versions. Old tests use a large string as Query in a JDBC Request while this method uses a CSV Data Set Config that runs a single JDBC Request per statement (line by line).

Executing batch statements (H2 v1.4.206 and previous versions)

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.

This kind of tests use a single JDBC request per user iteration. This may be seriously affected by latency, parsing, large string manipulation, lack of caching, etc. Use these only if you are going to test older versions of H2, as the newer ones (>1.4.200) do not even allow such configuration (especially if using prepared statements). Even so, it is more accurate to test FWD using the SQL statements "one-by-one". There is no use-case in FWD (at the moment of writing) where several statements (> 5) are batched, so this kind of tests are strictly speculative.

Also, 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 number of 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 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 Testcases repository, 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 tests using the instructions provided here Testcases. 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

apache-jmeter-new-version-tests.zip (4.22 MB) Dănuț Filimon, 09/13/2022 05:30 AM