DB performance troubleshooting guide

Modified on: Sat, 18 Nov 2023 9:10 AM

Original Publishing Date:
2023-06-22

!!Please kindly note that the following article encompasses a comprehensive set of PostgreSQL database administration checks and is not exclusively tailored to the CloudBlue platform.

The provided steps aim to offer valuable guidance and insights into general PostgreSQL database administration practices. We sincerely appreciate your understanding and hope that you find this information beneficial in managing your PostgreSQL databases effectively.

Check disk IO throughput

Make sure block device at "/var/lib/pgsql" can deliver 1k iops both for read and write:

# yum install -y epel-release
# yum install -y fio
 
### read test
fio --ioengine=libaio --direct=1 --runtime=60s --time_based --name=testfio --filename /var/lib/pgsql/testfio --bs=4k --iodepth=64 --size=8G --readwrite=randread

### write test
# fio --ioengine=libaio --direct=1 --runtime=60s --time_based --name=testfio --filename /var/lib/pgsql/testfio --bs=4k --iodepth=64 --size=8G --readwrite=randwrite

Check pgbench database tps, should be above 1k

Choose appropriate DB size through scale-factor: https://www.cybertec-postgresql.com/en/a-formula-to-calculate-pgbench-scaling-factor-for-target-db-size/. Choose scale-factor to get double of available system memory, to make sure disk is involved.

# su - postgres
$ createdb testdb
$ /usr/pgsql-11/bin/pgbench -s10000 -i testdb
 
#read/write load
$ /usr/pgsql-11/bin/pgbench -T60 -c32 -P10 -r testdb
...
tps = 20994.537177 (without initial connection time)
statement latencies in milliseconds:
         0.098  BEGIN;
         0.262  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.110  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.113  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.102  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.087  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.723  END;
...
 
 
#read load
$ /usr/pgsql-11/bin/pgbench -T60 -c32 -P10 -r -S testdb
...
tps = 143746.161233 (without initial connection time)
statement latencies in milliseconds:
         0.000  \set aid random(1, 100000 * :scale)
         0.213  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
...

Check shared buffer sizes adequate to workset

oss=> show shared_buffers;
 shared_buffers
----------------
 3840MB
 
 
SELECT relname, heap_blks_read, heap_blks_hit, round(heap_blks_hit::numeric/(heap_blks_hit+heap_blks_read+1), 3) as heap_ratio, idx_blks_read, idx_blks_hit, round(idx_blks_hit::numeric/(idx_blks_hit+idx_blks_read+1), 3) as idx_ratio FROM pg_statio_user_tables WHERE relname IN ('aps_resource', 'aps_security_link', 'aps_resource_link', 'aps_property_value');
      relname       | heap_blks_read | heap_blks_hit |    heap_ratio     | idx_blks_read | idx_blks_hit |     idx_ratio
--------------------+----------------+---------------+-------------------+---------------+--------------+-------------------
 aps_resource       |    54487439979 |  374114799825 | 0.87              |    1543001724 | 519862569274 | 0.99
 aps_resource_link  |   151346031230 | 1763546331360 | 0.92              |   10123813837 | 605611978756 | 0.98
 aps_property_value |     7869251824 |   27971938761 | 0.78              |    4312642679 | 179548174263 | 0.97
(3 rows)
 
SELECT relname, heap_blks_read, heap_blks_hit, round(heap_blks_hit::numeric/(heap_blks_hit+heap_blks_read+1), 3) as heap_ratio, idx_blks_read, idx_blks_hit, round(idx_blks_hit::numeric/(idx_blks_hit+idx_blks_read+1), 3) as idx_ratio FROM pg_statio_user_tables WHERE relname IN ('SalesOrder', 'Plan', 'Account');
  relname   | heap_blks_read | heap_blks_hit |     heap_ratio     | idx_blks_read | idx_blks_hit |     idx_ratio     
------------+----------------+---------------+--------------------+---------------+--------------+--------------------
 Account    |              1 |           600 | 0.99               |            28 |         1793 | 0.98
 Plan       |              1 |           150 | 0.98               |            28 |          464 | 0.94
 SalesOrder |              0 |             0 |                  0 |            16 |          502 | 0.96

heap_ratio, idx_ratio should be above 0.99. Shared buffers of 3840MB is not enough for this dataset.

Check database bloat

https://cbportal.freshservice.com/support/solutions/articles/23000097177