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

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

Detailed documentation →

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).

The same statistics also tell you in what proportion to run the queries you picked: their call counts. Keeping a profile where one query is called 120,000 times in production and another 400 times in the same proportion in the test shows realistically how the most frequent query shares the cache and the connections. Spitfire imports those statistics directly and turns them into a weighted query mix.

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.

If the database can only be reached through a bastion, add an SSH tunnel to the connection. Instead of writing the queries by hand, you can also have them suggested from the database's own schema: query suggestions from the schema.

The SQL step runs one query or a query mix. 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.

Write protection

Reads are really read-only. Spitfire tokenizes every query by its database's own syntax and counts anything it cannot recognise as a read as a write. Keywords count only where a statement starts: the beginning, a CTE body, the statement after a CTE list or after EXPLAIN, and subqueries. So WITH d AS (DELETE … RETURNING *) SELECT … or EXPLAIN ANALYZE UPDATE … is caught as a write, while functions such as replace() and insert() or a column named update do not turn a read into a write. Reads that lock rows are flagged too: SELECT … FOR UPDATE and FOR SHARE, LOCK IN SHARE MODE on MySQL, and the UPDLOCK, XLOCK, HOLDLOCK and TABLOCKX hints on SQL Server. SELECT … INTO, procedure calls and multi-statement queries that write count as writes as well.

On PostgreSQL and MySQL reads also run in a read-only transaction that is rolled back, so the database itself refuses hidden writes such as a function with side effects. SQL Server and Oracle have no such transaction, and the SQL step form shows a warning saying so: there only the query check protects the data, so use a database user with read-only rights. 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.

Query mix: the production query profile in one step

A database never sees just one query; it sees hundreds of different queries in different proportions. A query mix reproduces that profile in a single SQL step: the step holds up to 200 queries and each iteration picks one of them at random by weight. Weights are relative, so call counts from pg_stat_statements work as they are. Every query has a unique name; parameters use the database's own placeholders, as in a single-query step. Every query goes through the write check like a single query; the step's write approval covers every write in the mix, and the warnings name the queries that write.

{ "id": "orders", "name": "Sipariş sorguları", "protocol": "sql", "connection": "orders-db",
  "sql": { "mix": [
    { "name": "order_by_id", "query": "SELECT id, status, total FROM orders WHERE id = $1",
      "params": ["{{$randInt 1 100000}}"], "weight": 120000 },
    { "name": "customer_orders", "query": "SELECT id, status, total, created_at FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20",
      "params": ["{{$randInt 1 20000}}"], "weight": 45000 },
    { "name": "pending_count", "query": "SELECT count(*) FROM orders WHERE status = $1",
      "params": ["pending"], "weight": 6000 },
    { "name": "hourly_summary", "query": "SELECT date_trunc('hour', created_at) AS h, count(*), sum(total) FROM orders WHERE created_at > now() - interval '1 day' GROUP BY 1 ORDER BY 1",
      "weight": 400 }
  ] } }

Per-query results and thresholds

Besides the step's own latency and error metrics, each query reports its own series (sql_query_duration, sql_query_failed; the series' check is the query's name). The run page and the HTML/PDF report show each query's calls, share, p50, p95, p99 and error rate in a table; the CLI summary lists the queries one by one too. So a rare but heavy report query that slows down does not hide behind a step total p95 that looks fine. A threshold can target one query: give the query's name as check in the filter; spitfire validate refuses the test if no query has that name.

"thresholds": [
  { "metric": "sql_query_duration", "filter": { "check": "order_by_id" }, "expr": "p(95)<20" },
  { "metric": "sql_query_duration", "filter": { "check": "customer_orders" }, "expr": "p(95)<50" },
  { "metric": "sql_query_duration", "filter": { "check": "hourly_summary" }, "expr": "p(95)<500" },
  { "metric": "req_failed", "expr": "rate<0.001" }
]

The full test is on the examples page as sql-sorgu-karisimi.json; it was checked with spitfire validate.

Bulk add: paste or upload a .sql file

There is no need to type the queries one by one. In the SQL step, Bulk add / import takes pasted statements or a .sql file and splits them by the database's syntax: ; outside quotes and comments, PostgreSQL's dollar quotes and nested comments, MySQL/MariaDB's # comments and DELIMITER, SQL Server's GO lines, Oracle's / lines and PL/SQL blocks. No query is run while doing so.

A review table shows each distinct statement with the line it first appears on, its count and its kind: read, write, lock, INTO or procedure. A statement that appears more than once is taken once and its count becomes its weight. Statements that are not reads start unticked; tick one and the step needs the write approval. The result is one query mix step or one step per statement.

Import from production statistics

The same dialog imports PostgreSQL's pg_stat_statements and MySQL's performance_schema.events_statements_summary_by_digest. There are two ways: paste the export query's output as CSV or TSV with its header row, or, as an admin, read it straight from the step's connection. When it reads from the connection, the controller runs only the statistics query: the top N rows by calls (50 by default, at most 200), in a read-only transaction and with a timeout. Spitfire runs on your own servers, so the query texts and the statistics stay in your network.

On PostgreSQL (with psql; the pg_stat_statements extension must be enabled):

\copy (SELECT query, calls, total_exec_time, rows
  FROM pg_stat_statements
  WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  ORDER BY calls DESC LIMIT 200) TO 'stats.csv' WITH CSV HEADER

On MySQL (save the result as CSV or TSV with its header row; mysql --batch gives TSV):

SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT
  FROM performance_schema.events_statements_summary_by_digest
  WHERE SCHEMA_NAME = DATABASE() AND DIGEST_TEXT IS NOT NULL
  ORDER BY COUNT_STAR DESC LIMIT 200;

Call counts become the weights. Transaction commands, session commands such as SET and the statistics query itself are skipped; writes start unticked. Queries in the statistics are normalized: placeholders such as $1 or ? stand where the constants were. In the review table you bind each one to a column of a CSV data file or to a fake value. Collapsed value lists such as MySQL's IN (...) and cut query texts are flagged for a manual edit.

Query suggestions from the schema

A realistic database load does not need a single line of SQL from you. Spitfire reads your database's catalog (tables, primary keys, indexes, foreign keys) and suggests read queries from it: fetching a row by id, lookups by an indexed column, foreign key joins, pagination and counts. It holds to two things while doing so: nothing it reads leaves your servers, and nothing is read before you consent. Everything runs from your own Spitfire server against your own database; no external service or AI is used, and Spitfire's maker never connects to your database. It works on PostgreSQL, MySQL/MariaDB, SQL Server and Oracle.

Who can use it. Installation admins only: the Suggest from schema button of a SQL connection under Connections, or Bulk add / import → Suggest from schema in a SQL step. Workspace admins and users cannot open it, and the server refuses their requests at every step, so the restriction is not only in the interface.

Consent first, and the consent record

Before the first read the admin reads and accepts a consent text. It says plainly: what will be read; that a dedicated, least-privilege read-only user should be used (never root, a superuser or an admin); that a production database is strongly discouraged; that reading the catalog and EXPLAIN put a light load on the database; and that the customer is responsible for the account given and for being allowed to read it. The consent is for one of two scopes: schema only (no table data) or schema and sample values.

The consent is stored in Spitfire's database and written to the audit log: who (the user and e-mail), when, from which IP address and browser, for which connection (type, host, port, database, user, SSH tunnel and production flag; no secrets such as passwords), with which text version and language, which scope, the result of the privilege check and the warnings exactly as shown. When the connection's address, user, tunnel or production flag changes, when the scope widens or when the consent text changes, the old consent no longer counts and a new one is asked for; without a valid consent the server refuses to read. A consent can be revoked; the record is not deleted but marked revoked. All consents of a connection are listed under Consent records.

Privilege check

When the feature opens, Spitfire first reads the connected user's privileges, read-only: role attributes and table/schema grants on PostgreSQL, SHOW GRANTS on MySQL, sysadmin, db_owner, db_datawriter and database permissions on SQL Server, roles and SESSION_PRIVS on Oracle. The result is one of three colors:

• Green, read-only: the recommended case.
• Orange, has write privileges: the user can change data; creating a separate read-only user is advised.
• Red, superuser / admin: a strong warning and an extra checkbox; you cannot go on without ticking it, and the consent record keeps it.

SQL connections also have a Production database flag. A flagged connection brings the same red warning and extra checkbox. Our advice is plain: use it not against production but against a test, staging or restored copy with the same schema.

A least-privilege read-only user

The dialog shows a ready script that creates such a user for each database; the database name comes from the connection (shop below). Replace <CHANGE_ME_STRONG_PASSWORD> with a strong password of your own, then put the user and the password in the connection: Spitfire never makes up a password, and it is stored only in the connection, encrypted. A database admin runs the scripts; Spitfire does not. For PostgreSQL (the required part is enough for schema reading and EXPLAIN; default_transaction_read_only keeps reads as reads even if a grant is added later):

-- Run as an admin, connected to the database shop.
CREATE ROLE spitfire_ro WITH LOGIN PASSWORD '<CHANGE_ME_STRONG_PASSWORD>'
  NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
GRANT CONNECT ON DATABASE shop TO spitfire_ro;
GRANT USAGE ON SCHEMA public TO spitfire_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO spitfire_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO spitfire_ro;
-- Reads stay reads even if a grant is added later:
ALTER ROLE spitfire_ro SET default_transaction_read_only = on;

The optional part is only for statement statistics (reading pg_stat_statements, for example):

-- Optional: statement statistics (pg_stat_statements) and server statistics.
GRANT pg_read_all_stats TO spitfire_ro;

For MySQL and MariaDB ('%' allows any client host; narrow it to your Spitfire server's address):

-- Run as an admin. '%' allows any client host; narrow it to your Spitfire server's address.
CREATE USER 'spitfire_ro'@'%' IDENTIFIED BY '<CHANGE_ME_STRONG_PASSWORD>';
GRANT SELECT, SHOW VIEW ON `shop`.* TO 'spitfire_ro'@'%';

-- Optional: statement statistics (performance_schema digests).
GRANT SELECT ON performance_schema.* TO 'spitfire_ro'@'%';
-- Optional: see other sessions' queries (SHOW PROCESSLIST).
GRANT PROCESS ON *.* TO 'spitfire_ro'@'%';

For SQL Server the script grants db_datareader, VIEW DEFINITION and SHOWPLAN for EXPLAIN (optionally VIEW SERVER STATE); for Oracle CREATE SESSION, SELECT on the application schema's tables and SELECT_CATALOG_ROLE (optionally SELECT ANY DICTIONARY).

What is read

Catalog metadata only: schemas, tables, columns (name, type, nullable), primary keys, indexes, foreign keys and the catalog's row estimates. No table data is read. The read runs in a read-only transaction where the database has one, with a timeout; at most 2000 tables are read, and system schemas stay out unless you include them. The result is kept per connection; Read again refreshes it.

What the suggestions look like

For each table you pick: a lookup by primary key, a lookup by each unique or indexed column, a join fetching a row with its parent over a foreign key and a parent's child rows, a page ordered by the key, and a count over a range of an indexed number or time column. Every query has a row limit (LIMIT, TOP, FETCH FIRST), uses the database's own placeholders and passes the write guard as a read. The same schema always gives the same suggestions.

SELECT c.*, p.email, p.full_name, p.city, p.created_at
FROM orders c
JOIN customers p ON p.id = c.customer_id
WHERE c.id = $1
LIMIT 100

EXPLAIN check. Every suggestion is checked with EXPLAIN: only the plan is fetched, the query itself never runs (never EXPLAIN ANALYZE; SHOWPLAN_XML on SQL Server, EXPLAIN PLAN on Oracle). Each row shows the plan's cost and estimated rows. Queries that scan a whole table are marked caution: full table scan; those expected to read more than 100,000 rows, or that scan a table of that size end to end, are marked high cost and start unticked. So a full scan that production would never run does not slip into the load test unnoticed.

Output. Names, weights, SQL and parameter bindings are editable. The ones you pick go into an existing test or a new one: as one query mix step or as one step per query. From there on it is like any SQL test: thresholds, stages, runners.

Sample values (with a separate consent). Random ids often hit rows that do not exist. If you want to load with real keys, you consent separately to the schema and sample values scope: for a column you pick, up to 1,000 distinct values are read, read-only, from a bounded sample of the table (TABLESAMPLE / SAMPLE where available) and saved on the Spitfire server as a data file the workspace's tests can use; the parameter binds to it, as in {{orders_customer_id.customer_id}}. The values stay on your server; the logs and the audit log get only their count, never the values.

Every privilege check, schema read, EXPLAIN batch and sample read is logged and written to the audit log: the connection, the user, counts and durations; no secrets and no values (security statement).

SSH tunnel

Databases often sit in a network that cannot be reached directly, entered through a bastion. PostgreSQL, MySQL/MariaDB, SQL Server and Oracle connections can reach the database through such an SSH server. The tunnel works not only on the controller but on the runners during a load test too: the connection test, statistics and schema reading go from the controller, the queries of a run from every runner.

1. Under Connections, edit the SQL connection and turn on Connect through an SSH server in the SSH tunnel section.
2. Enter the SSH host, port (22 by default) and user. Authentication is with a private key (PEM/OpenSSH, with an optional passphrase) or a password.
3. Press Fetch host key: Spitfire reads the key the server presents (it does not log in) and shows its SHA256 fingerprint. Compare it with the one on the SSH server itself, for example ssh-keygen -lf /etc/ssh/ssh_host_ed25519_key.pub. Only if they match, choose The fingerprints match: trust this key. You can also paste the host key or the fingerprint yourself.
4. Write the database host by the name the SSH server knows it as: the name is resolved on the SSH server's side. Save and test the connection.

The host key is always verified. A tunnel cannot be saved without a host key or fingerprint, and there is no setting that skips the check. If the server later presents a different key, the connection is refused before anything is sent, so a server in between never sees your credentials. The SSH password and private key are stored encrypted like the other connection secrets, and the API never returns them.

Who can change it. Only installation admins add, change or remove a tunnel; workspace admins can edit the rest of the connection but not its SSH settings. Each runner opens one SSH connection per database connection and the whole connection pool shares it; it is kept alive and re-established if it drops. Tunnels log when they open and close.