How to Benchmark PostgreSQL Performance Using Sysbench

Benchmarking is a way of discovering the performance of your infrastructure. Sysbench is a great tool to benchmark PostgreSQL servers. In this blog post, we’ll show you how to generate test loads using sysbench. We will make use of a two-node master-slave streaming replication setup by ClusterControl. This will also help us generate some activity on the cluster, and check that replication is working as expected.

We will install the latest version of sysbench, currently maintained here. We’ll use the more updated package provided in the official Github page to install sysbench. We’ll also use the standard PostgreSQL 9.6 binaries from the PostgreSQL download page. Take note that the path used in this blog post might be different depending on the PostgreSQL version and vendor that you have installed.

As a side note, we have covered a similar blog post on benchmarking PostgreSQL using pgbench in this blog post, How to Benchmark PostgreSQL Performance.

Installing Sysbench

Installing sysbench is easy. For Debian/Ubuntu:

$ curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
$ sudo apt -y install sysbench

And for RHEL/CentOS:

$ curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
$ sudo yum -y install sysbench

Install the sysbench package:

$ yum install sysbench

Verify the version:

$ sysbench --version
sysbench 1.0.15

We have now installed sysbench.

Initializing Test Data

If you are familiar with sysbench, it uses the following defaults for PostgreSQL parameters:

• pgsql-host=localhost
• pgsql-port=5432
• pgsql-user=sbtest
• pgsql-password=password
• pgsql-db=sbtest

Firstly, create the database and user inside PostgreSQL:

$ su - postgres
$ psql
> CREATE USER sbtest WITH PASSWORD 'password';
> CREATE DATABASE sbtest;
> GRANT ALL PRIVILEGES ON DATABASE sbtest TO sbtest;

Then edit the host-based access file, pg_hba.conf:

$ vim /var/lib/pgsql/9.6/data/pg_hba.conf

And add the following line to allow connections for user sbtest, to database sbtest from all hosts under 192.168.55.0 network:

host    sbtest          sbtest          192.168.55.0/24         md5

Reload the server to apply the changes:

$ /usr/pgsql-9.6/bin/pg_ctl --reload

Verify from psql command line client if the user authentication works correctly:

$ psql -U sbtest -h 192.168.55.61 -p 5432 -d sbtest -W

You should be able to get into the server under sbtest database:

$ psql -U sbtest -h 192.168.55.61 -p 5432 -W
Password for user sbtest:
Type "help" for help.

sbtest=>

Run “q” to quit from the terminal. We can now initialize the database using sysbench with the following command:

$ sysbench 
--db-driver=pgsql 
--oltp-table-size=100000 
--oltp-tables-count=24 
--threads=1 
--pgsql-host=192.168.55.61 
--pgsql-port=5432 
--pgsql-user=sbtest 
--pgsql-password=password 
--pgsql-db=sbtest 
/usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua 
run

The above command generates 100,000 rows per table for 24 tables (sbtest1 to sbtest24) inside database ‘sbtest’. The schema name is “public” which is the default. The data is prepared by a script called parallel_prepare.lua which available under /usr/share/sysbench/tests/include/oltp_legacy.

Verify the generated tables with the following command:

$ psql -U sbtest -h 192.168.55.61 -p 5432 -W -c 'dt+'
Password for user sbtest:
                    List of relations
 Schema |   Name   | Type  | Owner  | Size  | Description
--------+----------+-------+--------+-------+-------------
 public | sbtest1  | table | sbtest | 21 MB |
 public | sbtest10 | table | sbtest | 21 MB |
 public | sbtest11 | table | sbtest | 21 MB |
 public | sbtest12 | table | sbtest | 21 MB |
 public | sbtest13 | table | sbtest | 21 MB |
 public | sbtest14 | table | sbtest | 21 MB |
 public | sbtest15 | table | sbtest | 21 MB |
 public | sbtest16 | table | sbtest | 21 MB |
 public | sbtest17 | table | sbtest | 21 MB |
 public | sbtest18 | table | sbtest | 21 MB |
 public | sbtest19 | table | sbtest | 21 MB |
 public | sbtest2  | table | sbtest | 21 MB |
 public | sbtest20 | table | sbtest | 21 MB |
 public | sbtest21 | table | sbtest | 21 MB |
 public | sbtest22 | table | sbtest | 21 MB |
 public | sbtest23 | table | sbtest | 21 MB |
 public | sbtest24 | table | sbtest | 21 MB |
 public | sbtest3  | table | sbtest | 21 MB |
 public | sbtest4  | table | sbtest | 21 MB |
 public | sbtest5  | table | sbtest | 21 MB |
 public | sbtest6  | table | sbtest | 21 MB |
 public | sbtest7  | table | sbtest | 21 MB |
 public | sbtest8  | table | sbtest | 21 MB |
 public | sbtest9  | table | sbtest | 21 MB |
(24 rows)

The test data is now loaded.

Generate Test Loads

There are different kind of database workload you can perform with sysbench, as shown in the following sections.

Read/Write Load

The command is similar to MySQL’s version of sysbench. Similar parameters can be used except PostgreSQL related parameters:

$ sysbench 
--db-driver=pgsql 
--report-interval=2 
--oltp-table-size=100000 
--oltp-tables-count=24 
--threads=64 
--time=60 
--pgsql-host=192.168.55.61 
--pgsql-port=5432 
--pgsql-user=sbtest 
--pgsql-password=password 
--pgsql-db=sbtest 
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua 
run

The above command will generate the OLTP workload from the LUA script called /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua, against 100,000 rows of 24 tables with 64 worker threads for 60 seconds on host 192.168.55.61 (master). Every 2 seconds, sysbench will report the intermediate statistics (–report-interval=2).

Once executed, you would get something like below:

sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 16
Report intermediate results every 2 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 2s ] thds: 64 tps: 0.00 qps: 466.69 (r/w/o: 406.55/28.33/31.81) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 64 tps: 30.55 qps: 525.38 (r/w/o: 335.56/128.72/61.10) lat (ms,95%): 3639.94 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 64 tps: 39.55 qps: 718.41 (r/w/o: 496.13/142.68/79.60) lat (ms,95%): 4128.91 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 64 tps: 35.98 qps: 840.95 (r/w/o: 604.11/163.89/72.95) lat (ms,95%): 2198.52 err/s: 0.50 reconn/s: 0.00
[ 10s ] thds: 64 tps: 65.57 qps: 1314.94 (r/w/o: 912.00/271.80/131.14) lat (ms,95%): 3040.14 err/s: 0.00 reconn/s: 0.00
...

When the test was ongoing, we can monitor the PostgreSQL activity using pg_activity or pg_top, to confirm the intermediate statistic reported by sysbench. In another terminal, do:

$ su - postgres
$ pg_activity
 PostgreSQL 9.6.9 - postgres1.local - postgres@localhost:5432/postgres - Ref.: 2s
  Size:  654.62M -     7.67K/s        | TPS:          74
  Mem.:   39.10% -   382.72M/979.68M  | IO Max:     3395/s
  Swap:    0.20% -     3.57M/2.00G    | Read :      8.36M/s -   2141/s
  Load:    20.20 6.02 2.44            | Write:      2.54M/s -    650/s
                                                                   RUNNING QUERIES
PID    DATABASE              USER           CLIENT   CPU% MEM%   READ/s  WRITE/s     TIME+  W  IOW              state   Query
5130   sbtest              sbtest    192.168.55.61    1.0  2.8  791.57K    3.84K  0.788732  N    N             active   SELECT c FROM sbtest7 WHERE id BETWEEN 33195
 AND 33294
...

As well as the replication stream by looking at the pg_stat_replication table on the master server:

$ su - postgres
$ watch -n1 'psql -xc "select * from pg_stat_replication"'
Every 1.0s: psql -xc "select * from pg_stat_replication"      Tue Jul 31 13:12:08 2018
-[ RECORD 1 ]----+------------------------------
pid              | 3792
usesysid         | 16448
usename          | slave
application_name | walreceiver
client_addr      | 192.168.55.62
client_hostname  |
client_port      | 44654
backend_start    | 2018-07-30 13:41:41.707514+08
backend_xmin     |
state            | streaming
sent_location    | 0/60933D78
write_location   | 0/60933D78
flush_location   | 0/60933D78
replay_location  | 0/60933D78
sync_priority    | 0
sync_state       | async

The above “watch” command runs the psql command every 1 second. You should see “*_location” columns are updated accordingly when replication happens.

At the end of the test, you should see the summary:

SQL statistics:
    queries performed:
        read:                            67704
        write:                           19322
        other:                           9682
        total:                           96708
    transactions:                        4830   (79.34 per sec.)
    queries:                             96708  (1588.53 per sec.)
    ignored errors:                      6      (0.10 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.8723s
    total number of events:              4830

Latency (ms):
         min:                                    4.52
         avg:                                  799.70
         max:                                 8082.70
         95th percentile:                     2279.14
         sum:                              3862532.62

Threads fairness:
    events (avg/stddev):           75.4688/7.39
    execution time (avg/stddev):   60.3521/0.20

The above summary tells us that our PostgreSQL database server can handle in average around 80 transactions per second and around 1588 queries per second under 64 worker threads.

Read-Only Load

For read-only test, you can use the same command, but change the LUA script to select.lua, select_random_points.lua, select_random_ranges.lua or oltp_simple.lua:

$ sysbench 
--db-driver=pgsql 
--report-interval=2 
--oltp-table-size=100000 
--oltp-tables-count=24 
--threads=64 
--time=60 
--pgsql-host=192.168.55.62 
--pgsql-port=5432 
--pgsql-user=sbtest 
--pgsql-password=password 
--pgsql-db=sbtest 
/usr/share/sysbench/tests/include/oltp_legacy/select.lua 
run

The above command runs a read-only workload called select.lua against a PostgreSQL slave server (streaming replication), 192.168.55.62 with 64 worker threads.

Other Loads

There are many other OLTP workloads that you can generate with sysbench, as listed under this directory, /usr/share/sysbench/tests/include/oltp_legacy:

$ ls -1 /usr/share/sysbench/tests/include/oltp_legacy/
bulk_insert.lua
common.lua
delete.lua
insert.lua
oltp.lua
oltp_simple.lua
parallel_prepare.lua
select.lua
select_random_points.lua
select_random_ranges.lua
update_index.lua
update_non_index.lua

You may use the similar command and change the path to the LUA script to load it up.

Final Thoughts

Using sysbench, we can generate test loads for our PostgreSQL server (as well as for MySQL). Note that the best benchmark would be with your real data and applications, but that might not always be possible. It could also be a new application that will quickly evolve. Despite the load generated by sysbench may not depict your real-world OLTP workload, it might be just good enough.