JDBC Database Load Testing and Connection-Pool Considerations: Configuration, Design Patterns, and Trade-Offs

1. Mandatory path stays local, free, and read-only

2. Direct DB load versus application/API load

Run both when both questions matter—but never merge conclusions. A DB can handle direct reads while the API still bottlenecks in serialization/connection-pool wait, or vice versa.

3. Per-thread versus shared JMeter pool

Max Wait defines how long a thread can wait before the pool throws an error. A pool timeout is a generator/client configuration symptom—not automatically a DB execution failure.

4. Lazy pool versus preinitialized pool

With Preinit Pool=false (default), the first JDBC requests may include connection-establishment time for pool creation. This is useful if session establishment belongs to the experiment; otherwise warm/preinitialize deliberately and separate cold-start from steady-state metrics.

5. Read-only versus write scenarios

Read-only SELECT tests are the mandatory baseline because cleanup and data corruption risk are low. Write scenarios introduce transactions, locks, logs/WAL, indexes, rollback/commit behavior, row ownership, idempotency, cleanup, and potentially irreversible data changes.

A write test should use a dedicated disposable schema/database, synthetic rows tagged to a run, an account granted only the minimum write rights, explicit transaction/rollback design, and verified cleanup. Do not “upgrade” the Chapter 16 lab by granting SA/admin to JMeter.

6. Prepared parameters versus string interpolation

Prepared Select Statement uses ? placeholders plus Parameter values/types. It reduces quoting/type errors and prevents a CSV value such as 1 OR 1=1 from becoming executable SQL text.

Broken construction:

SELECT ID, DESCRIPTION FROM PUBLIC.CATALOG_ITEM WHERE CATEGORY = '${CATEGORY}'

Preferred:

SQL: SELECT ID, DESCRIPTION FROM PUBLIC.CATALOG_ITEM WHERE CATEGORY = ?
Parameter value: ${CATEGORY}
Parameter type: VARCHAR

7. Embedded H2 versus local TCP server

Embedded mode is simplest and removes network transport, but the database runs inside the JMeter JVM/process path and a file DB cannot be opened by another process simultaneously. That complicates independent telemetry and conflates generator/database CPU.

Local H2 TCP server keeps the DB in a separate process while remaining free/local. It is the mandatory chapter choice because session/query telemetry and process utilization are easier to observe.

8. Local server versus containerized server

A container can improve reproducibility/isolation for PostgreSQL/MySQL or H2 server demos, but adds image/runtime/network/filesystem startup state. It is optional. The mandatory path uses a plain Java H2 server so Docker is not required.

If a container is used, pin the DB/driver version, mount only disposable data, bind only loopback, cap resources, and preserve container startup/CPU/memory telemetry separately from JMeter.

9. Driver-specific compatibility

JMeter JDBC support is generic; the vendor driver defines URL syntax, class name, SQL types, timeout support, transaction behavior, and authentication. JMeter lists common classes such as PostgreSQL, H2, MariaDB, SQL Server, Oracle, and others, but you must verify the current vendor driver documentation/version.

The plan and driver version belong together in evidence. A driver upgrade can change connection behavior/performance without JMX changes.

10. Auto-commit and transaction state

The SELECT-only baseline keeps Auto Commit=true. JDBC Request also exposes special query types for Autocommit(false/true), Commit, and Rollback. Those change connection transaction state and belong in explicitly designed transaction tests—not random add-ons around read queries.

11. Query timeout and Max Wait are different clocks

Max Wait limits how long JMeter waits to borrow a connection from its client pool. Query timeout asks the JDBC driver/database to limit query execution time after a connection is acquired. A connection-pool timeout should not be diagnosed as a slow SQL timeout.

12. Result-variable strategy

Variable Names can store returned columns as NAME_1, NAME_#, etc. Result Variable Name stores a Java object list of row maps. The beginner path uses simple scalar variables because they are easy to assert and lower scripting complexity.

13. Configuration-layer boundaries

14. Worked scenario

Requirement: “The API is slow and DBA suspects one indexed lookup. We need to know whether that query itself degrades with 20 DB connections.”

• First run direct JDBC against a disposable/read replica/test DB with the exact prepared SELECT and controlled connection count.
• Collect JTL latency/elapsed + DB execution/session telemetry + generator state.
• Separately run API load and inspect the application's actual connection pool.
• If direct SQL stays fast but API DB-pool wait rises, the application pool/lifecycle may be the bottleneck rather than SQL execution.

15. Decision table

16. Evidence contract

Preserve JMeter/Java/driver/DB versions, JDBC URL with credential redaction, pool settings, exact prepared query/parameters, thread/timer model, raw JTL + matching jmeter.log, generator CPU/memory, H2 process CPU/memory, session counts, query statistics, and a validity statement that direct JDBC bypasses application pooling.

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.