Database load testing: why your app tests pass and your database still falls over

Diego Salinas
Enterprise Content Manager
Gatling
Table of contents
Add to Google preferred sources

Summarize this article with AI

Database load testing: why your app tests pass and your database still falls over

Your load test passed. p95 was green, error rate was zero, the release went out. Then peak traffic arrived, checkout slowed to a crawl, and the root cause was the database: a connection pool that ran dry, a lock queue on one hot row, or a query that was fast on 10,000 rows and slow on 40 million.

The database is the hardest tier to scale horizontally, so it is usually the first real bottleneck under load. It is also the tier most load tests treat as a black box behind an API.

This guide is for developers, QA engineers, and DevOps teams who already run application load tests and have never tested the data layer on purpose. By the end, you will have a repeatable method to find your database's saturation point, explain why it happens, and catch regressions before they ship.

What database load testing is (and isn't)

Database load testing means generating realistic concurrent database work and measuring latency, throughput, resource use, and failure behaviour as demand rises. It sits next to a few related practices that often get mixed up:

PracticeQuestion it answersTypical tools
BenchmarkingHow fast is this engine on this hardware, under a standard workload?HammerDB, sysbench, pgbench, YCSB, BenchBase
Load testingDoes the database hold its SLOs at expected peak, with our queries?Gatling, JMeter, k6, pgbench custom scripts
Stress testingWhere does it break, and how does it fail? (more on stress testing)Same tools, pushed past peak
Soak testingWhat degrades after hours: bloat, vacuum, log growth, memory?Same tools, run for 4 to 24 hours

A benchmark score is useful for comparing instance types. It says little about your schema, your query mix, or your hot rows. HammerDB itself labels its workloads TPROC-C and TPROC-H to make clear they are derived from TPC standards, not official TPC results.

There are also two places to apply load:

  • Through the application. You hit your API and the database receives load the way it does in production: through the ORM, the connection pool, and the cache. This tells you whether the service meets its SLO.
  • Directly against the database. You send queries or transactions over the database driver. This isolates database capacity and lets you test one query, index, or configuration change at a time.

Most teams need both. We will come back to how to combine them.

What to measure

A database test needs two sets of numbers: what the client saw, and what the database was doing at the same moment. One without the other tells you something is slow, not why.

SignalWhere it comes fromWhy it matters
Latency at p50, p95, p99Load toolAverages hide the tail. Real latency distributions are skewed and multi-modal, so the mean describes almost nothing.
Throughput (TPS or QPS) at each load levelLoad tool and DB statsFlat throughput under rising load means you have hit a ceiling.
Connection pool usage and wait timeApp pool metrics (HikariCP, PgBouncer, ProxySQL)Pool waits look like slow queries from the outside.
Lock waits and deadlockspg_stat_activity, MySQL Performance Schema, SQL Server DMVsContention grows non-linearly with concurrency.
Replication lagpg_stat_replication, replica statusRead replicas that fall behind serve stale data.
CPU, memory, disk I/O, IOPSHost or cloud metricsTells you whether you are compute-bound or storage-bound.
Errors under concurrencyLoad tool and DB logsTimeouts, refused connections, and serialization failures show up only under load.

For query-level detail, enable the engine's own statistics before the run: pg_stat_statements on PostgreSQL, Performance Schema and the sys schema on MySQL, Query Store on SQL Server, the profiler on MongoDB.

Common bottlenecks to look for

Most database incidents under load come from a short list of causes. Knowing them in advance tells you which metrics to watch.

  • Connection exhaustion. 10,000 virtual users usually map to 100 to 500 pooled connections. An undersized pool queues requests; an oversized one overwhelms the database with context switching.
  • N+1 queries. An ORM issues one query per row instead of one join. Invisible at 10 users, dominant at 1,000.
  • Missing or unused indexes. A sequential scan on a 50,000-row table is fast. On 50 million rows it is an outage.
  • Lock contention on hot rows. Inventory counters, account balances, and sequence tables serialize every writer that touches them.
  • Cache misses. When an application cache expires or restarts, the full read load lands on the database at once.
  • Write amplification. Triggers, audit tables, and secondary indexes turn one logical write into several physical ones.

Designing a realistic test

The most precise result is useless if the workload is wrong. Realism matters more than tool choice.

  1. Use production-like data volume and skew. A 1 GB test dataset against a 1 TB production database turns an I/O-bound system into an in-memory benchmark. Keep the skew too: real traffic hits a few hot customers and recent rows, not uniformly random keys. If you cannot copy production for privacy reasons (GDPR, HIPAA), preserve its shape: cardinality, row sizes, null rates, and key distribution.
  2. Model the real query mix. Pull the top queries, their frequency, and the read/write ratio from pg_stat_statements, the MySQL slow log, or Query Store. That list becomes your scenario.
  3. Model arrival rate, not just concurrency. Production users keep arriving when the database slows down. Use an open workload model (users per second) so your test does the same. A closed model slows its own arrival rate when the system struggles, which hides the problem you are looking for.
  4. Mirror the connection setup. Same pool size, same pooler (PgBouncer, ProxySQL), same driver version as production.
  5. Plan data reset. Every run mutates data. Restore a snapshot or truncate between runs, and decide whether you are measuring a cold or a warm cache. Write it down.
  6. Record a baseline before changing anything. Same environment, same data, same load. Every later result is compared against it.

Choosing your approach

ApproachWhat it capturesWhat it missesBest for
Through the APIORM behaviour, pool pressure, caching, retries, the full request pathWhich query or lock is to blameValidating SLOs before a release or peak event
Directly against the databaseRaw query and transaction capacity, one variable at a timeApplication logic, caching, pool configurationIndex changes, engine upgrades, instance sizing
HybridBoth, in sequenceRequires correlating two sets of resultsMost production teams

The hybrid sequence that works: run the API test, watch database metrics, reproduce the slowest query mix directly against the database, tune, then rerun the API test to confirm.

Where Gatling fits

Gatling covers both layers: realistic traffic through the endpoints that hit your database, and SQL load sent straight to the database over JDBC.

  • Test as code. Scenarios are written in Java, Kotlin, Scala, JavaScript, or TypeScript and live in the same repository as the schema migrations they test.
  • Open workload models. constantUsersPerSec, rampUsersPerSec, and incrementUsersPerSec let you hold an arrival rate steady while the database slows down, so tail latency is measured, not masked.
  • Real data from the database itself. The built-in JDBC feeder reads test data (customer IDs, order IDs, SKUs) straight from a database with any JDBC4 driver, so requests hit the same hot keys production does.
  • Assertions as pass/fail gates. Fail the run on p99 latency or error rate, in code.
  • Direct SQL load. For driver-level tests, Gatling Enterprise Edition includes a JDBC plugin built and maintained by Gatling, so you can load the database directly with the same scenarios, injection profiles, and reports as your API tests. Open-source users can use community JDBC plugins. Engine-native tools such as pgbench and sysbench are also a good complement for isolating the database.

A scenario that feeds real IDs from Postgres into a checkout API and steps the arrival rate up:

import static io.gatling.javaapi.core.CoreDsl.*;
import static io.gatling.javaapi.http.HttpDsl.*;
import static io.gatling.javaapi.jdbc.JdbcDsl.jdbcFeeder;

import io.gatling.javaapi.core.*;
import io.gatling.javaapi.http.*;

public class CheckoutDbSimulation extends Simulation {

  FeederBuilder customers = jdbcFeeder(
      "jdbc:postgresql://staging-db:5432/shop", "perf_user", System.getenv("DB_PASSWORD"),
      "SELECT customer_id, sku FROM perf_customers"
  ).random();

  HttpProtocolBuilder httpProtocol = http.baseUrl("https://staging.example.com");

  ScenarioBuilder checkout = scenario("Checkout")
      .feed(customers)
      .exec(http("Get cart").get("/api/customers/#{customer_id}/cart"))
      .pause(1)
      .exec(http("Place order")
          .post("/api/orders")
          .body(StringBody("{\"customerId\":\"#{customer_id}\",\"sku\":\"#{sku}\"}"))
          .asJson());

  {
    setUp(
        checkout.injectOpen(
            incrementUsersPerSec(20)
                .times(5)
                .eachLevelLasting(300)
                .separatedByRampsLasting(30)
                .startingFrom(20)
        )
    ).protocols(httpProtocol)
     .assertions(
         global().responseTime().percentile(99.0).lt(200),
         global().failedRequests().percent().lt(0.1)
     );
  }
}

Five steps of five minutes each, from 20 to 100 new users per second, tell you exactly which level pushed the database past its knee.

{{card}}

Running the test

Run a sequence of tests, not one big one. Each answers a different question:

  1. Smoke. A few users for a couple of minutes. Confirms the script, data, and monitoring work.
  2. Baseline. Normal production load. The reference point for everything else.
  3. Step load to peak and beyond. Ramp in stages and hold each stage for 5 to 10 minutes so caches settle and queues reach steady state. Do not jump straight to maximum load: you will miss the point where latency starts to bend.
  4. Spike. A sudden jump in arrival rate. This is where connection storms, pool exhaustion, and autoscaling lag show up.
  5. Soak. Expected load for 4 to 24 hours. Autovacuum, table bloat, WAL or log growth, and memory leaks only appear with time.

While the test runs, watch database metrics on the same timeline as client results. If your load test reports in UTC and your database dashboard is in local time, fix that first. Also watch the load generator's own CPU and network: if the client saturates before the database does, you are testing the generator.

Reading the results

The output you want is a throughput-versus-latency curve, not a single TPS number.

Find the knee. As load increases, throughput rises roughly in line with it and latency stays flat. At some level, throughput flattens while p99 climbs sharply. That level is your practical capacity. Your safe operating point sits below it, with headroom for the peaks you expect.

Correlate the knee with a cause. At the moment latency bends, check which database signal moved first:

What you see at the kneeLikely causeWhere to look next
Pool wait time rises, DB CPU is lowPool too small or connections held too longPool config, long transactions
Lock waits or deadlocks riseHot rows, long transactionspg_locks, InnoDB lock waits, transaction scope
DB CPU near 100%Expensive queries, missing indexespg_stat_statements, EXPLAIN (ANALYZE, BUFFERS)
Disk I/O or IOPS at the capWorking set larger than memory, storage limitBuffer hit ratio, cloud volume limits and burst credits
Replication lag climbsWrite volume beyond replica apply rateReplica metrics, WAL generation rate

Separate database limits from everything else. If database CPU, I/O, locks, and pool waits all look healthy while API latency rises, the bottleneck is in the application, the network, or the load generator. Reproducing the same query mix directly against the database confirms it either way.

One rule for distributed tests: never average p99 values across load generators. Percentiles must be computed from the merged latency distribution.

[EDITOR NOTE: ADD SOURCED EXAMPLE RESULT: a real run's curve and the cause found at the knee. Not a hypothetical number.]

Making it repeatable in CI/CD

A database test you run once before a launch catches one regression. A test that runs on every schema migration catches all of them.

  • Set performance budgets. Define them before you look at results: for example, p99 under 200 ms and errors under 0.1% at 1.5x expected peak. In Gatling, these live in the simulation as assertions, so a breach fails the build.
  • Compare against a stored baseline. A 10% throughput drop on the same data and environment is a regression, even if absolute numbers still pass. Gatling Enterprise Edition tracks this across runs with run trends and run comparison.
  • Split by cost. Run a short smoke test (a few minutes, low load) on every pull request that touches a migration, query, or ORM mapping. Run the full step-load and soak tests on a schedule or before a release.
  • Version everything together. Keep the test, the schema migration, and the seed data scripts in the same repository, so a reviewer sees the performance test change alongside the query change.

Common mistakes

  • Empty or tiny datasets. Everything fits in RAM, every query is fast, and the result says nothing about production.
  • Averages instead of percentiles. A 40 ms average can hide a 2-second p99.
  • A closed model for open traffic. When the database slows down, a closed-model test sends fewer requests and reports reassuring latency.
  • Ignoring pool settings. Opening one connection per virtual user tests connection setup, not queries.
  • A shared test database. Another team's batch job runs mid-test and your results become noise.
  • Short tests on burstable cloud storage. Burst credits and T-class CPU credits make a 10-minute test look far better than a 2-hour one.
  • Changing several variables at once. A new index, a bigger instance, and a driver upgrade in one run means no single result is attributable.

Conclusion

Your database will hit a limit. The only question is whether you find it in a test or in production. The method fits in four steps:

  1. Model the real workload: production-sized data, the real query mix, an open arrival rate.
  2. Load it in steps, through the API and directly, and watch database metrics on the same timeline.
  3. Find the knee and name its cause: pool, locks, CPU, I/O, or replication.
  4. Automate it: budgets as assertions, a baseline to compare against, a smoke test on every migration.

Start with the endpoints that hit your database hardest. Write the scenario in the language your team already uses, feed it real IDs from your database, and step the load until something bends.

{{card}}

About the author
‍
Diego Salinas
Gatling

Diego Salinas Gardón is a senior technical copywriter and content strategist specializing in performance testing, developer tools, SaaS, and software infrastructure. With hands-on experience infront-end development, testing, and modern web technologies.

‍

He currently works at Gatling, where he creates content that helps developers and engineering teams better understand performance, testing, and DevOps.

FAQ

What is database load testing?

It generates realistic concurrent queries and transactions against your data layer and measures latency, throughput, and resource use as load rises, so you find the point where performance degrades before production does.

Should I test the database directly or through the API?

Both. API tests show whether the service meets its SLO, including ORM, pooling, and caching. Direct tests isolate database capacity. Run the API test, reproduce the slowest query mix directly, tune, then rerun the API test.

Which metrics matter most in a database load test?

p95 and p99 latency, throughput at each load level, connection pool wait time, lock waits, replication lag, and database CPU and I/O. Read client and database metrics on the same timeline to find the cause, not just the symptom.

Can Gatling load test a database directly?

Yes. Gatling Enterprise Edition includes a JDBC plugin built and maintained by Gatling for direct SQL load. Open-source users can use community JDBC plugins, and every edition includes a JDBC feeder for pulling test data from a database.

Ready to move beyond local tests?

Start building a performance strategy that scales with your business.

Need technical references and tutorials?

Minimal features, for local use only