I am currently trying to benchmark the performance of PostgreSQL when:

  1. the tablespace is set to a folder on an HDD partition
  2. the tablespace is set to a folder on an SSD partition

I am using the pgbench tool to perform the benchmarking, and am following the procedure according to this tutorial. So basically, I am looking at the tps (avg. transactions per second) at the end of the run as the metric for comparison.

Note: The size of my database is close to 10 GB so there is sufficient load for a stress test.

Before each run, I am manually changing the tablespace to hdd_tbspc (tablespace on HDD parition) and ssd_tbspc (tablespace on SSD parition), using pgAdmin.

The question

  • However the value that I am getting at the end of each of the runs is more or less the same, i.e., I can't observe any significant performance improvements when I switch to an SSD. Is there any reason for this?

  • Also, please let me know if the benchmarking protocol I am following makes sense.

Commands used

Here are the commands that I am using:

To initialize and scale the DB:

  • $: pgbench -U postgres -i -s 50 db-name

The above command is run only once. The two queries shown below are run on the same database with different tablespaces to contrast the performances.

Using the number of transactions as a metric:

  • $: pgbench -U postgres -c 10 -j 2 -t 10000 db-name

Using time as a metric:

  • $: pgbench -U postgres -c 10 -T 600 db-name
  • @jjanes so I run the pgbench -i command once and then for the next commands (-t/-T) will need to be executed on the same database with different tablespaces. So I am instructing the DB/server as to which tablespace is to be used using pgAdmin (right click the DB name > properties > select tablespace from dropdown). – sujaypatil May 10 '19 at 6:24
  • 2
    What are the actual performances? What do you get from pg_test_fsync from each drive? – jjanes May 10 '19 at 12:08
  • 1
    Also, what does iostat -mNx 1 report for the %iowait column? If your workload is I/O bound, you should see values above 10 there. – Laurenz Albe May 10 '19 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.