As I am “playing” a bit with PostgreSQL databases it is worth to looking at the underlying platform and measure performance. There is a famous tool called pgbench which can do this perfectly. Lets get into it !
Prepare OS
the pgbench tool is part of the package postgresql-contrib and is not installed by default.yum install postgresql-contrib
Create new Database
It is advisable to create a new DB just for the benchmarkpsql -c 'CREATE DATABASE pgbenchdb;'
Fill Database
Add sample data by using the “i”-option. With “-s” the scaling factor of data and therefore rows can be specified. In my example I am adding 100.000.000 rows where the DB had a size of around 15 GB on my disk.
$ pgbench -i -s 1000 pgbenchdb
dropping old tables...
creating tables...
generating data (client-side)...
100000000 of 100000000 tuples (100%) done (elapsed 156.94 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 207.15 s (drop tables 2.22 s, create tables 0.01 s, client-side generate 157.47 s, vacuum 0.18 s, primary keys 47.27 s).
Run Benchmark
Actually start running the benchmark by specifiy amount of con-current connection “-c”, Threads per connection “-j”, run time in seconds “-T 900” (15 minutes) and hostname “-h”. At the end db name where the sample data was inserted must be mentioned “pgbenchdb”.pgbench -c 10 -j 2 -T 900 -h hostname pgbenchdb
Result
Afterwards you get a summary and the most interesting part is line 16, where the transactions per second are displayed. Higher is better.
[postgres@postgresdb1 ~]$ pgbench -c 10 -j 2 -T 900 pgbenchdb -h postgresdb1
Password:
pgbench (15.5)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1000
query mode: simple
number of clients: 10
number of threads: 2
maximum number of tries: 1
duration: 900 s
number of transactions actually processed: 2133161
number of failed transactions: 0 (0.000%)
latency average = 4.219 ms
initial connection time = 111.928 ms
tps = 2370.444584 (without initial connection time)
Code-Sprache: HTML, XML (xml)Conclusion
Looking from the OS figures the benchmark is very CPU and IO intensive. The benchmark itself is easy to setup and should help you to identify the performance of your system.
09:28:00 AM CPU %user %nice %system %iowait %steal %idle
09:28:01 AM all 32.46 0.00 29.40 19.80 0.00 18.35
09:30:01 AM all 34.85 0.00 30.27 13.80 0.00 21.08
09:32:01 AM all 32.75 0.00 28.35 14.46 0.00 24.44
09:34:01 AM all 35.58 0.00 30.27 12.98 0.00 21.17
09:36:01 AM all 35.92 0.00 29.98 13.03 0.00 21.07
09:38:01 AM all 36.65 0.00 30.83 12.40 0.00 20.12
09:40:01 AM all 36.19 0.00 30.67 12.76 0.00 20.38
09:42:01 AM all 17.66 0.00 15.30 6.56 0.00 60.47
09:44:01 AM all 0.23 0.00 0.39 0.00 0.00 99.38Code-Sprache: Bash (bash)