Database load testing: query performance on PostgreSQL, MySQL, SQL Server and Oracle

Algebran Yazılım & Danışmanlık · Published 6 October 2026

The database is the most common reason an application slows down under load, but a test through the application makes it hard to tell: is the slowness in the application's code, its connection pool, or the query itself? Loading the database directly narrows that question. A database load test runs the application's most frequent and heaviest queries with realistic parameters at the target concurrency and asks: at what latency do the queries return at this load, where is the connection limit, and when do locks or data volume start to break things.

What are we measuring?

• Query latency: p95 and p99 for each kind of query separately. Averaging a single-key read and a report query together hides both (the p95 and p99 guide).
• Throughput: queries or transactions completed per second, and how that changes with concurrency. Past a certain point more concurrent queries bring no more throughput, only more latency.
• Connections: the database's connection limit (such as max_connections in PostgreSQL) and the memory per connection. Waiting for a connection from the pool adds to latency too.
• Locks and contention: concurrent writes updating the same rows wait for each other; deadlocks and lock timeouts only show at real concurrency.
• Resources: CPU, disk I/O, the cache (buffer pool) hit ratio and replica lag if there are replicas. Read these from the database's own monitoring alongside the load stages.

Shaping the load

Pick the queries from the application's query statistics (pg_stat_statements in PostgreSQL, the performance schema in MySQL, Query Store in SQL Server and the like): the most frequent ones and the ones that spend the most time in total. Work out concurrency from the application's real pool sizes: if 6 instances each use a pool of 20 connections, the database sees at most 120 concurrent queries. A VU can be thought of as one connection; to watch latency as it grows, raise the VUs in steps (breakpoint testing).

Common mistakes

• A small test dataset. A table of a thousand rows sits entirely in memory and every query looks fast. The test database's size and data distribution should be close to production; otherwise the query plans differ too.
• Always the same parameter. Querying the same id again and again measures the cache, not the database. Pick parameters at random or from a CSV file of real ids.
• Measuring the warm-up. A cold database reads from disk in its first minutes. Judge the ramp-up and the first minutes apart from the result, or keep the steady part long enough.
• The load generator's connections. When the load comes from several machines, their pools add up: 4 machines × 50 connections = 200 connections. If that exceeds the database's limit, what you measure is refused connections, not queries.
• Writing to live data. Run write tests on a copy of production, not on the live database, and plan the cleanup of the writes up front.

With Spitfire

First add a database connection under Connections: PostgreSQL, MySQL (MariaDB included), SQL Server or Oracle; host, port, database (the service name for Oracle), username, password, driver parameters (for TLS, sslmode=require on PostgreSQL or encrypt=true on SQL Server and the like) and the maximum open connections (50 by default). The password is stored encrypted and the test names only the connection. The VUs of each runner share one connection pool per connection; the maximum open connections is per runner.

The SQL step runs one query. The query is fixed text; values are passed as parameters with the database's own placeholders: $1 on PostgreSQL, ? on MySQL, @p1 on SQL Server, :1 on Oracle. Parameters can contain {{$randInt 1 100000}}, extracted variables or values from a CSV data file; templates cannot go inside the query text, so there is no SQL injection. A read's duration covers the wait for a pooled connection, the query and fetching every row. Rows go to checks and extraction as an array of JSON objects (such as $[0].status); the first 100 rows are kept (configurable) while the row count covers all of them and can be tested with a rowCount check and with thresholds on the rows metric. Errors are told apart by kind: timeout, refused connection, permission, missing table, constraint violation, syntax and read-only violation.

Reads are really read-only. Spitfire works out whether a query reads or writes, and counts anything it cannot recognise as a read as a write: INSERT, UPDATE, DELETE, SELECT … FOR UPDATE, SELECT … INTO, procedure calls and multi-statement queries that write. On PostgreSQL and MySQL reads run in a read-only transaction that is rolled back, so the database itself refuses hidden writes such as a function with side effects. Oracle refuses data changes inside a query on its own; on SQL Server use a read-only database user. Writes cannot be saved without an explicit approval on the step, and once approved the test asks for confirmation again every time it starts, recorded in the audit log (security statement). Approved writes commit one by one, without a transaction.

{
  "name": "PostgreSQL okuma yükü",
  "scenarios": [
    { "name": "okuma",
      "executor": { "type": "ramping-vus", "startVUs": 0,
        "stages": [ { "duration": "1m", "target": 30 }, { "duration": "5m", "target": 30 },
                    { "duration": "30s", "target": 0 } ] },
      "steps": [
        { "id": "order", "name": "Sipariş getir", "protocol": "sql", "connection": "orders-db",
          "sql": { "query": "SELECT id, status, total FROM orders WHERE id = $1",
                   "params": ["{{$randInt 1 100000}}"] },
          "checks": [ { "type": "rowCount", "op": "lte", "value": 1 } ] },
        { "id": "report", "name": "Günlük özet", "protocol": "sql", "connection": "orders-db",
          "sql": { "query": "SELECT date_trunc('hour', created_at) AS h, count(*) FROM orders WHERE created_at > now() - interval '1 day' GROUP BY 1 ORDER BY 1" },
          "thinkTime": { "min": "500ms", "max": "1s" } }
      ] }
  ],
  "thresholds": [
    { "metric": "req_duration", "filter": { "step": "order" }, "expr": "p(95)<20" },
    { "metric": "req_duration", "filter": { "step": "report" }, "expr": "p(95)<300" },
    { "metric": "req_failed", "expr": "rate<0.001" }
  ]
}

During the run you watch per-step query rates, p95, p99, returned row counts and error kinds live. If you collect the database's own metrics (connections, lock waits, cache hits, replica lag) with a Prometheus exporter, add them to a Prometheus connection under Observability; the run page's Backend tab shows them aligned with the load stages. To run the same test against different environments you can pick another connection per environment (for example orders-db-staging instead of orders-db). If there is a cache in front of the database, see the Redis load testing guide to load it too.