JDBC Database Load Testing and Connection-Pool Considerations: Guided Hands-On Workflow

1. Hard safety envelope

2. Obtain and verify the H2 driver

Download the current H2 2.5.250 all-platform distribution from the official H2 site. Copy h2-2.5.250.jar to a lab tools/ directory and copy the same JAR into JMETER_HOME/lib. Restart JMeter after adding/removing a JDBC driver.

Verify:

java -version
java -cp tools/h2-2.5.250.jar org.h2.tools.Shell -help

H2 2.5.250 requires Java 11+; this course uses Java 17.

3. Create synthetic rows and the SELECT-only account

Save fixtures/setup.sql:

DROP TABLE IF EXISTS PUBLIC.CATALOG_ITEM;
DROP USER IF EXISTS JMETER_READ;

CREATE TABLE PUBLIC.CATALOG_ITEM (
    ID INTEGER PRIMARY KEY,
    CATEGORY VARCHAR(32) NOT NULL,
    PRICE_CENTS INTEGER NOT NULL,
    DESCRIPTION VARCHAR(128) NOT NULL
);

INSERT INTO PUBLIC.CATALOG_ITEM (ID, CATEGORY, PRICE_CENTS, DESCRIPTION)
SELECT
    X,
    'CAT-' || MOD(X, 10),
    1000 + MOD(X * 37, 9000),
    'synthetic-item-' || X
FROM SYSTEM_RANGE(1, 5000);

CREATE INDEX IDX_CATALOG_CATEGORY ON PUBLIC.CATALOG_ITEM(CATEGORY);

CREATE USER JMETER_READ PASSWORD 'fake-read-pass';
GRANT SELECT ON PUBLIC.CATALOG_ITEM TO JMETER_READ;

SET QUERY_STATISTICS TRUE;
ALTER USER SA SET PASSWORD 'fake-admin-pass';

The setup script performs writes only while creating the disposable lab. JMeter itself never receives write permissions.

4. Initialize from a fresh lab directory

PowerShell:

New-Item -ItemType Directory -Force .\db, .\results | Out-Null
java -cp .\tools\h2-2.5.250.jar org.h2.tools.RunScript `
  -url "jdbc:h2:./db/jmeterlab" `
  -user sa `
  -script .\fixtures\setup.sql

Bash:

mkdir -p db results
java -cp tools/h2-2.5.250.jar org.h2.tools.RunScript   -url "jdbc:h2:./db/jmeterlab"   -user sa   -script fixtures/setup.sql

The script ends by changing SA's password to the synthetic fake-admin-pass; subsequent monitoring uses that fake admin credential only on localhost.

5. Start H2 TCP server on loopback

H2 documents -baseDir for restricting database files and rejects remote hosts by default. Add an explicit bind address system property:

PowerShell:

java -Dh2.bindAddress=127.0.0.1 `
  -cp .\tools\h2-2.5.250.jar `
  org.h2.tools.Server `
  -tcp `
  -tcpPort 9092 `
  -baseDir .\db

Bash:

java -Dh2.bindAddress=127.0.0.1   -cp tools/h2-2.5.250.jar   org.h2.tools.Server   -tcp   -tcpPort 9092   -baseDir ./db

6. Preflight rows, permissions, and versions

Admin inspection:

java -cp .\tools\h2-2.5.250.jar org.h2.tools.Shell `
  -url "jdbc:h2:tcp://127.0.0.1:9092/jmeterlab;IFEXISTS=TRUE" `
  -user sa `
  -password fake-admin-pass `
  -sql "SELECT H2VERSION(); SELECT COUNT(*) AS ROWS FROM PUBLIC.CATALOG_ITEM; SELECT GRANTEE, RIGHTS, TABLE_NAME FROM INFORMATION_SCHEMA.RIGHTS WHERE GRANTEE='JMETER_READ';"

Expected: H2 2.5.250, 5,000 rows, and SELECT right on CATALOG_ITEM.

7. Prove the load credential can read

java -cp .\tools\h2-2.5.250.jar org.h2.tools.Shell `
  -url "jdbc:h2:tcp://127.0.0.1:9092/jmeterlab;IFEXISTS=TRUE" `
  -user JMETER_READ `
  -password fake-read-pass `
  -sql "SELECT ID, CATEGORY, PRICE_CENTS, DESCRIPTION FROM PUBLIC.CATALOG_ITEM WHERE ID=1;"

This confirms connectivity/permission without modifying data.

8. Configure JDBC Connection Configuration

The pool variable labdb is the lookup key JDBC Request uses. If names do not match exactly, the sampler cannot borrow the intended connection.

9. Parameterize item IDs

Create data/item-ids.csv:

item_id
1
37
101
509
999
1501
2401
3333
4096
4999

CSV Data Set Config: Recycle=true for the tiny read workload, Stop Thread=false, Sharing mode All Threads.

10. Prepared SELECT sampler

JDBC Request — Prepared Item Lookup:

JMeter creates DB_ID_# for row count and DB_ID_1, DB_DESCRIPTION_1 for the first row.

11. Add safe result assertions

Add Response Assertion on JMeter Variable DB_ID_# equals 1. Add another on DB_DESCRIPTION_1 containing synthetic-item-. These checks prove a valid row was returned without relying only on a JDBC success flag.

12. Recommended baseline tree

Test Plan
├── JDBC Connection Configuration — labdb
├── CSV Data Set Config — item-ids.csv
└── Thread Group — 2 threads × 10 loops max
    └── JDBC Request — Prepared Item Lookup
        ├── Response Assertion DB_ID_# == 1
        └── Response Assertion DB_DESCRIPTION_1 contains synthetic-item-

Use View Results Tree only for a 1-thread ×1-loop GUI check. Disable it before CLI comparison runs.

13. Observe active JMeter DB sessions separately

While the tiny test runs, a separate admin shell can query only the synthetic load user's sessions:

1..20 | ForEach-Object {
  java -cp .\tools\h2-2.5.250.jar org.h2.tools.Shell `
    -url "jdbc:h2:tcp://127.0.0.1:9092/jmeterlab;IFEXISTS=TRUE" `
    -user sa -password fake-admin-pass `
    -sql "SELECT USER_NAME, SESSION_STATE, COUNT(*) AS CONNECTIONS FROM INFORMATION_SCHEMA.SESSIONS WHERE USER_NAME='JMETER_READ' GROUP BY USER_NAME, SESSION_STATE;"
  Start-Sleep -Milliseconds 250
}

The monitoring shell creates its own admin connection, but the query filters only JMETER_READ.

14. Run the baseline in CLI mode

jmeter.bat -n `
  -t plans\jdbc-read-baseline.jmx `
  -l results\baseline\results.jtl `
  -j results\baseline\jmeter.log `
  -e -o results\baseline\report

Preserve JTL and matching jmeter.log. Record generator CPU/memory and H2 process CPU/memory during the run.

15. Inspect query statistics after the run

java -cp .\tools\h2-2.5.250.jar org.h2.tools.Shell `
  -url "jdbc:h2:tcp://127.0.0.1:9092/jmeterlab;IFEXISTS=TRUE" `
  -user sa -password fake-admin-pass `
  -sql "SELECT SQL_STATEMENT, EXECUTION_COUNT, MIN_EXECUTION_TIME, MAX_EXECUTION_TIME, AVERAGE_EXECUTION_TIME, CUMULATIVE_EXECUTION_TIME FROM INFORMATION_SCHEMA.QUERY_STATISTICS ORDER BY CUMULATIVE_EXECUTION_TIME DESC FETCH FIRST 10 ROWS ONLY;"

Compare H2 execution time with JTL total elapsed and JDBC latency. The figures use different measurement boundaries and should not be expected to match exactly.

16. Small shared-pool comparison

Duplicate the plan and change Max Number of Connections from 0 to 2. Now both threads use one shared JMeter pool that can hold two physical connections. Keep threads/query/data identical.

Do not interpret this as “application pool=2.” It is only a JMeter client configuration comparison.

17. Challenge

A team wants to benchmark an API whose application server has a 20-connection pool. Should you set JMeter JDBC pool to 20 and call the direct DB result an application-pool test?

No. Direct JDBC bypasses the application pool entirely. Use API/application load to test that pool. Use direct JDBC separately if the question is database/query behavior under a deliberately defined number of JMeter client connections.

Knowledge check

Official references and version notes

• JMeter Component Reference — JDBC Connection Configuration — pool variable, max connections, max wait, validation, auto-commit, isolation, driver class, and credential storage.
• JMeter Component Reference — JDBC Request — prepared/select query types, parameters, result variables, query timeout, and connection-acquisition latency.
• JMeter Getting Started — Java requirements and GUI-versus-CLI execution.
• JMeter Dashboard Report — result-file requirements and post-run reporting.
• JMeter Best Practices — non-GUI load execution and generator validity.
• Apache JMeter downloads — current stable release and Java requirement.
• H2 Database Engine — current release and JDBC capabilities.
• H2 Quickstart — driver class and JDBC URL basics.
• H2 Tutorial — server mode, JDBC access, Shell, and Java tooling.
• H2 System Tables — SESSIONS and QUERY_STATISTICS telemetry.
• H2 Commands — CREATE USER, GRANT SELECT, SET QUERY_STATISTICS, and read-only SQL semantics.