Benchmarking with Postgres PT1

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