!!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://cloudblue.freshdesk.com/support/solutions/articles/44001887384