Measuring PostgreSQL performance

Since psql performance was not really up to speed at the server I am currently using, I had a look at how to measure and optimize its performance. PostgreSQL comes with a neat benchmarking tool to help you tweak the performance settings to your environment.

Settings can be changed in your postgresql.conf file, the easiest way to change these is via the Backend Configuration Editor in PgAdmin. To see the currently used setting, use for example

SELECT name, setting, unit FROM   pg_settings WHERE  name = 'shared_buffers';

In order to reload the settings after a change without restarting the server, you can use the command

SELECT pg_reload_conf();

Here is how I used pgbench on the Windows Enterprise Server to get a sense of the PostgreSQL performance.

1. Create a new benchmarking database in PgAdmin. I called it "pgbench".

2. Create benchmarking tables using pgbench -i. Here's the command I used in Windows:

./pgbench.exe -i -s 1 -h [servername] -U [username] pgbench

The command line argument -s is the scaling factor used. I ran the test for a number of scaling factors between 1 and 1000 to see where there are drops in performance and how they relate to the database size.

3. In order to check the database size, use the following command:

select datname,pg_size_pretty(pg_database_size(oid)) from pg_database where datname = 'gpbench';

4. Run benchmarking

./pgbench.exe -t 2000 -c 8 -S -h [servername] -U [username] pgbench

The final line in the output is the number of transactions per second, which is what we use to measure performance.

 

psql_performance

Leave a Reply

Your email address will not be published. Required fields are marked *