Before you change database settings, move to a bigger server or upgrade to a new major version, you need numbers to compare. PostgreSQL ships with pgbench, a transactional benchmark that reports transactions per second (TPS) and latency, and MySQL ships with mysqlslap, a simple load emulator that runs queries from many concurrent clients. In this tutorial you will use both tools on Ubuntu 24.04 to create a baseline, test how the database scales with concurrency, run your own queries and measure the effect of a configuration change.

Prerequisites

To follow this guide you need:

  • A server running Ubuntu 24.04 LTS, such as a CubePath VPS, with at least 2 GB of RAM.
  • A non-root user with sudo privileges.
  • PostgreSQL, MySQL or both installed. Step 1 shows how to install them from the Ubuntu repositories.

The PostgreSQL and MySQL sections are independent: follow only the one for the database you use.

Step 1 - Installing the database servers

Ubuntu 24.04 ships PostgreSQL 16 and MySQL 8.0. The postgresql package includes pgbench, and the mysql-client package includes mysqlslap:

sudo apt update
sudo apt install postgresql mysql-server mysql-client

Check that both tools are available:

pgbench --version
mysqlslap --version

pgbench (PostgreSQL) 16.10 (Ubuntu 16.10-0ubuntu0.24.04.1)
mysqlslap  Ver 8.0.43-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu))

Your exact patch versions will differ.

Benchmarking PostgreSQL with pgbench

Step 2 - Creating and initializing the test database

pgbench runs against its own set of tables (pgbench_accounts, pgbench_branches, pgbench_tellers and pgbench_history). Create a separate database for them as the postgres system user:

sudo -u postgres createdb pgbench_test

Initialize the tables with -i. The scale factor -s sets the data volume: each unit adds 100,000 rows to pgbench_accounts, about 15 MB on disk. A scale of 50 creates 5 million rows, roughly 750 MB:

sudo -u postgres pgbench -i -s 50 pgbench_test

dropping old tables...
creating tables...
generating data (client-side)...
5000000 of 5000000 tuples (100%) done (elapsed 6.02 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 9.11 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 6.10 s, vacuum 0.71 s, primary keys 2.29 s).

Pick the scale deliberately. If the data set fits in shared_buffers, you are benchmarking memory. If it is much larger than RAM, you are benchmarking the disk. Match it to the size of your real database when possible.

Confirm the size of the data set:

sudo -u postgres psql -d pgbench_test -c "SELECT pg_size_pretty(pg_database_size('pgbench_test'));"

 pg_size_pretty
----------------
 751 MB
(1 row)

Step 3 - Running the baseline benchmark

The built-in workload is loosely based on TPC-B: each transaction runs three UPDATEs, a SELECT and an INSERT. Run it with 16 clients, 4 worker threads, for 60 seconds, printing progress every 10 seconds:

sudo -u postgres pgbench -c 16 -j 4 -T 60 -P 10 pgbench_test
  • -c is the number of concurrent client connections.
  • -j is the number of pgbench threads. It cannot be larger than -c; one thread per CPU core is a good starting point.
  • -T is the duration in seconds.
  • -P prints a progress line at that interval.

At the end pgbench prints a summary:

transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 16
number of threads: 4
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 112874
number of failed transactions: 0 (0.000%)
latency average = 8.503 ms
initial connection time = 18.422 ms
tps = 1881.689021 (without initial connection time)

The two figures that matter are tps (throughput) and latency average (response time). Also watch the progress lines: if TPS swings widely between intervals, checkpoints or the storage are causing stalls.

Save the baseline so you can compare later:

sudo -u postgres pgbench -c 16 -j 4 -T 60 pgbench_test | tee ~/pg-baseline.txt

Step 4 - Testing read-only and concurrency scaling

Many applications read far more than they write. The built-in select-only script (-S) runs a single primary key lookup per transaction:

sudo -u postgres pgbench -S -c 16 -j 4 -T 60 pgbench_test

Expect a much higher TPS than the read-write test.

To find the point where adding clients stops increasing throughput, run the same test with increasing concurrency. This loop prints only the latency and TPS lines for each run:

for c in 1 4 8 16 32 64; do
  echo "clients=$c"
  sudo -u postgres pgbench -c "$c" -j "$(( c < 4 ? c : 4 ))" -T 30 pgbench_test \
    | grep -E '^(latency average|tps)'
done

clients=1
latency average = 1.402 ms
tps = 713.221040 (without initial connection time)
clients=4
latency average = 2.118 ms
tps = 1888.513772 (without initial connection time)
...
clients=64
latency average = 34.771 ms
tps = 1840.602285 (without initial connection time)

When TPS flattens while latency keeps rising, the server is saturated. That knee is a good upper bound for your connection pool size.

Step 5 - Running a custom workload

The built-in scripts are useful for comparisons, but your application's queries are what matter. pgbench accepts SQL script files with its own variable syntax. Create a script that reads an account and records a history row:

nano ~/custom.sql

\set aid random(1, 100000 * :scale)
\set delta random(-5000, 5000)
BEGIN;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
  VALUES (1, 1, :aid, :delta, CURRENT_TIMESTAMP);
END;

:scale is filled in automatically from the initialized tables. Because the script is in your home directory, copy it somewhere the postgres user can read, then run it with -f. The -r option reports the average latency of each statement:

sudo install -o postgres -m 0644 ~/custom.sql /tmp/custom.sql
sudo -u postgres pgbench -f /tmp/custom.sql -c 16 -j 4 -T 60 -r pgbench_test

statement latencies in milliseconds and failures:
         0.001           0  \set aid random(1, 100000 * :scale)
         0.000           0  \set delta random(-5000, 5000)
         0.098           0  BEGIN;
         0.141           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.172           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
         2.310           0  END;

A large time on END (the commit) points to storage flush latency rather than query cost.

Step 6 - Measuring the effect of a configuration change

As an example, change shared_buffers from the Ubuntu default of 128 MB to 1 GB. ALTER SYSTEM writes the setting to postgresql.auto.conf, and this parameter needs a restart:

sudo -u postgres psql -c "ALTER SYSTEM SET shared_buffers = '1GB';"
sudo systemctl restart postgresql

Verify the new value:

sudo -u postgres psql -c "SHOW shared_buffers;"

 shared_buffers
----------------
 1GB
(1 row)

Run exactly the same benchmark as the baseline and compare:

sudo -u postgres pgbench -c 16 -j 4 -T 60 pgbench_test | tee ~/pg-after.txt
grep -E '^(latency average|tps)' ~/pg-baseline.txt ~/pg-after.txt

Change one setting at a time and run each benchmark at least twice. A difference of a few percent is within normal run-to-run variation.

When you are done, remove the test database:

sudo -u postgres dropdb pgbench_test

Benchmarking MySQL with mysqlslap

Step 7 - Running an auto-generated benchmark

On Ubuntu, the MySQL root account authenticates through the Unix socket, so sudo mysqlslap connects without a password. mysqlslap creates a temporary schema called mysqlslap, runs the test and drops it again.

Run a mixed read/write workload with 1, 8 and 32 concurrent clients, 3 iterations each, and 10,000 queries per iteration:

sudo mysqlslap \
  --auto-generate-sql \
  --auto-generate-sql-load-type=mixed \
  --auto-generate-sql-add-autoincrement \
  --number-int-cols=2 --number-char-cols=3 \
  --concurrency=1,8,32 \
  --iterations=3 \
  --number-of-queries=10000 \
  --engine=innodb

mysqlslap prints one block per concurrency level:

Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 1.842 seconds
	Minimum number of seconds to run all queries: 1.790 seconds
	Maximum number of seconds to run all queries: 1.911 seconds
	Number of clients running queries: 8
	Average number of queries per client: 1250

The key figure is the average time to run all queries: lower is better. Divide --number-of-queries by that time to get queries per second (10,000 / 1.842 s is about 5,400 queries per second).

The --auto-generate-sql-load-type option accepts read, write, key, update and mixed. Use read or write to isolate each side of the workload.

Step 8 - Benchmarking your own queries

Auto-generated SQL is synthetic. To test a query that matches your application, give mysqlslap a schema to create and the statements to run. Separate multiple statements with the character given in --delimiter:

sudo mysqlslap \
  --create-schema=slaptest \
  --delimiter=";" \
  --create="CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, total DECIMAL(10,2), created_at DATETIME, KEY (customer_id)); INSERT INTO orders (customer_id, total, created_at) VALUES (1, 10.50, NOW()), (2, 99.00, NOW()), (3, 5.25, NOW())" \
  --query="SELECT SUM(total) FROM orders WHERE customer_id = 2; INSERT INTO orders (customer_id, total, created_at) VALUES (2, 12.00, NOW())" \
  --concurrency=16 \
  --iterations=5 \
  --number-of-queries=20000

mysqlslap creates the slaptest schema, runs the --create statements once, runs the queries from 16 clients five times and drops the schema at the end. For longer scripts, put the statements in files and pass the file paths to --create and --query instead.

Step 9 - Comparing MySQL configurations

Write results to a CSV file with --csv so runs are easy to compare. Take a baseline:

sudo mysqlslap --auto-generate-sql --auto-generate-sql-load-type=mixed \
  --concurrency=32 --iterations=5 --number-of-queries=20000 \
  --csv=/tmp/mysql-baseline.csv

Then change a setting. For example, raise the InnoDB buffer pool to 1 GB in a new configuration file:

sudo nano /etc/mysql/mysql.conf.d/zz-benchmark.cnf

[mysqld]
innodb_buffer_pool_size = 1G

Restart MySQL and confirm the value (it is reported in bytes):

sudo systemctl restart mysql
sudo mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 |
+-------------------------+------------+

Run the identical command with a new CSV file and compare both:

sudo mysqlslap --auto-generate-sql --auto-generate-sql-load-type=mixed \
  --concurrency=32 --iterations=5 --number-of-queries=20000 \
  --csv=/tmp/mysql-after.csv
cat /tmp/mysql-baseline.csv /tmp/mysql-after.csv

Each CSV line contains the engine, the load type, the average, minimum and maximum seconds, the number of clients and the queries per client.

Troubleshooting

pgbench: error: connection to server on socket ... failed: FATAL: role "your_user" does not exist: you ran pgbench as your own user. Run it as sudo -u postgres, or create a PostgreSQL role for your user.

ERROR: relation "pgbench_accounts" does not exist: the database was not initialized. Run pgbench -i against it first (Step 2).

mysqlslap: Error when connecting to server: 1698 Access denied for user 'root'@'localhost': you ran mysqlslap without sudo, so socket authentication failed. Use sudo, or pass a dedicated MySQL user with -u your_user -p.

Results vary a lot between runs: the benchmark is too short or the data set is too small. Run for at least 60 seconds, repeat each test, and make sure nothing else is loading the server.

Conclusion

You created repeatable baselines for PostgreSQL with pgbench and for MySQL with mysqlslap, found the concurrency level where each database saturates, benchmarked custom queries and measured the effect of a configuration change. As next steps, run the benchmark client from a separate server so it does not compete for CPU, use sysbench for more realistic MySQL OLTP workloads, and benchmark the underlying storage with fio to see whether the disk is the limiting factor.