Checkpoint Lab — JDBC Database Load Testing and Connection-Pool Considerations

1. Exact assumptions and ceilings

2. Setup and target authorization/preflight

Recreate the disposable database with Lesson 2's setup.sql, start H2 using -Dh2.bindAddress=127.0.0.1, then record:

• JMeter 5.6.3 / Java version;
• H2 2.5.250 / driver hash or filename;
• 5,000 synthetic rows;
• JMETER_READ rights = SELECT on CATALOG_ITEM;
• initial JMETER_READ session count = 0;
• H2/JMeter process CPU/memory baseline.

3. Exact baseline tree

Test Plan
├── JDBC Connection Configuration — labdb
│   Max Connections=0
│   Max Wait=1000 ms
│   URL=jdbc:h2:tcp://127.0.0.1:9092/jmeterlab;IFEXISTS=TRUE
│   Driver=org.h2.Driver
│   User=JMETER_READ
├── CSV Data Set Config — item-ids.csv
└── Thread Group — 2 threads × 10 loops
    └── Prepared Item Lookup
        Query Type=Prepared Select Statement
        SQL=SELECT ID,CATEGORY,PRICE_CENTS,DESCRIPTION
            FROM PUBLIC.CATALOG_ITEM WHERE ID=?
        Value=${item_id}
        Type=INTEGER
        Variables=DB_ID,DB_CATEGORY,DB_PRICE,DB_DESCRIPTION
        ├── assert DB_ID_# == 1
        └── assert DB_DESCRIPTION_1 contains synthetic-item-

4. Predictions before baseline

Prediction A — connections: with Max Connections=0 and two active threads, each thread owns a one-connection pool, so the DB should observe up to roughly two JMETER_READ sessions while queries are active.

Prediction B — correctness: 20 prepared lookup opportunities (2×10) should return exactly one synthetic row each and produce zero assertion failures.

Prediction C — JTL timing: first samples can have larger connection-acquisition latency because Preinit Pool=false; later samples should reuse each thread's connection.

5. Execute baseline and monitor

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

In a second terminal, sample INFORMATION_SCHEMA.SESSIONS for USER_NAME='JMETER_READ'. After the run, save QUERY_STATISTICS and generator/H2 process utilization.

6. Verify baseline independently

• JTL: zero failures; 20 Prepared Item Lookup samples plus any explicitly enabled reporting samples.
• Assertions: every DB_ID_# is 1.
• H2 sessions: no more than expected small connection demand.
• Query statistics: lookup SQL execution count consistent with run.
• Generator/H2 process: neither saturated.

7. Add a controlled Pool Hold Probe

Create a second checkpoint plan/profile containing one read-only heavier query per thread:

SELECT COUNT(*) AS MATCHES
FROM SYSTEM_RANGE(1, 3000000)
WHERE MOD(X, 7) = 0

This query is not a production benchmark. It exists only to hold a JDBC connection long enough to make client-pool contention visible in a tiny local experiment.

8. Intentionally constrain JMeter's shared pool

Change only:

Keep run ≤10 seconds. Do not increase threads or DB privileges.

9. Predictions before broken run

Prediction D — acquisition: only one JMeter thread can hold the shared connection at a time. The other thread will wait; JDBC latency should increase, and on many machines Max Wait=5 ms may cause a pool-borrow timeout.

Prediction E — DB sessions: the database should see approximately one JMETER_READ physical session from that shared JMeter pool, even though two JMeter threads are configured.

Prediction F — target execution: H2 query execution time may remain similar to the valid profile; an acquisition timeout can occur before the second query even reaches the DB.

10. Run the constrained profile and preserve it

jmeter.bat -n `
  -t plans\checkpoint-jdbc-pool1.jmx `
  -l results\pool1\results.jtl `
  -j results\pool1\jmeter.log

Do not overwrite the baseline. Preserve any pool timeout/error text, JTL latency/elapsed, H2 session count/query stats, and generator state.

11. Diagnose from connection acquisition versus DB execution

The checkpoint is green when you can explain the symptom correctly—not when the broken run has zero errors.

12. Restore the valid configuration

Restore Max Connections=0, Max Wait=1000, and the original Prepared Item Lookup. Rerun the 2×10 baseline as results/restored/....

Require zero row/assertion failures and expected per-thread connection behavior.

13. Required target telemetry snapshot

Preserve:

SELECT USER_NAME, SESSION_STATE, COUNT(*) AS CONNECTIONS
FROM INFORMATION_SCHEMA.SESSIONS
WHERE USER_NAME='JMETER_READ'
GROUP BY USER_NAME, SESSION_STATE;

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;

14. Evidence packet

15. Validity statement

16. Verification checklist

• H2/driver/JMeter/Java versions recorded.
• Server bound only to 127.0.0.1:9092; no -tcpAllowOthers.
• JMeter account is JMETER_READ with SELECT only.
• All JMeter SQL is SELECT/read-only.
• Baseline 2×10 lookups return one row each.
• Broken pool profile preserved separately and correctly diagnosed.
• Restored profile is max=0 / max-wait=1000 and passes.
• Configured versus achieved sample counts and generator headroom recorded.
• DB session/query evidence preserved independently.

17. Cleanup / rollback

1. Stop JMeter runs and verify no active JMETER_READ sessions remain.
2. Stop the H2 TCP server cleanly with Ctrl+C in its lab terminal.
3. Retain JTL/log/telemetry evidence until review completes.
4. After review, delete only the disposable db/, results/, and lab data files.
5. Remove the H2 JAR from JMeter lib only if it was added solely for this lab and no other plans depend on it; restart JMeter after classpath changes.
6. No production/shared DB, real credential, OS/JVM global tuning, remote engine, container, or paid service was modified.

18. What Chapter 16 adds to the operating model

The performance-testing operating model now has a database/JDBC contract: driver/DB version, authorized JDBC URL, least-privilege credential source, JMeter pool variable/max/wait/preinit/autocommit/isolation, exact prepared SQL/parameter types, row/result assertions, connection/session/query telemetry, generator state, configured-versus-achieved load, and an explicit statement separating direct DB results from application-pool/end-to-end results.

Chapter 17 moves to JMS, FTP, LDAP, TCP, and Other Protocol Test Patterns. The same discipline carries forward: each protocol introduces its own connection/session/data/credential/result semantics, but target authorization, bounded load, correlation/state ownership, causal telemetry, and cleanup remain unchanged.

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.