In this example we run pgbench with a scale factor of 1000 which equates to a database size of around 15GB. The linux VM has 32G RAM, so we don’t expect to see many reads.

Using prometheus with the Linux node exporter we can see the disk IO pattern from pgbench. As expected the write pattern to the log disk (sda) is quite constant, while the write pattern to the database files (sdb) is bursty.

I had to tune the parameter checkpoint_completion_target from 0.5 to 0.9 otherwise the SCSI stack became overwhelmed during checkpoints, and caused log-writes to stall.

In this example, we use Postgres and the pgbench workload generator to drive some load in a virtual machine.  Assume a Linux virtual machine that has Postgres installed. Specifically using a Bitnami virtual appliance.

• Once the VM has been started, connect to the console
• Allow access to postgres port 5432 – which is the postgres DB port or allow ssh

$ sudo ufw allow 5432

• Note the postgres user password (cat ./bitnami_credentials)
•  Login to psql from the console or ssh

psql -U postgres

• Optionally change password (the password prompted is the one from bitnami_credentials for the postgres database user).

psql -U postgres
postgres=# alter user postgres with password 'NEW_PASSWORD';
postgresl=# \q

• Create a DB to run the pgbench workload.  In this case I name the db pgbench-sf10 for “Scale Factor 10”.  Scale Factors are how the size of the database is determined.

$ sudo -u postgres createdb pgbench-sf10

• Initialise the DB with data ready to run the benchmark.  The “createdb” step just creates an empty schema.
  • -i means “initialize”
  • -s means “scale factor” e.g. 10
  • pgbench-sf10 is the database schema to use.  We use the one just created pgbench-sf10

$ sudo -u postgres pgbench -i -s 10 pgbench-sf10

• Noe run a workload against the DB schema called pgbench-sf10

$ sudo -u postgres pgbench pgbench-sf10

The workload pattern, and load on the system will vary greatly depending on the scale factor.  

Scale-Factor        Working Set Size

1                                   23M
10                                157M
100                             1.7GB
1000                          15GB
2500                          37GB
5000                         74GB
10000                       147GB