How to analyse/fix a perf issue on DBMS (PostgreSQL 9.6 server) ?

[Eddy Fras]

We have been experiencing performance issues on our DBMS server used by a 3 nodes Jira DC cluster for one month.

During heavy load, the CPU consumption reaches regularly 100%, spending most of its time in IOWAIT (~80%) as shown on attached screenshot :

Logically, during these spikes, Jira becomes very slow or even worse, not available at all.

I'm clearly not a performance expert but my first guess is that this is due to poor disk perf.

An iotop command gives the following results :

14:46:39 Total DISK READ : 18.65 M/s | Total DISK WRITE : 936.14 K/s
14:46:39 Actual DISK READ: 18.66 M/s | Actual DISK WRITE: 614.34 K/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
14:46:39 12602 be/4 postgres 2.99 M/s 65.82 K/s 0.00 % 99.99 % postgres: 9.6/main: jsw_pg_1 jsw_p_db_1 192.168.1.101(40040) SELECT
14:46:39 5594 be/4 postgres 1060.47 K/s 21.94 K/s 0.00 % 99.99 % postgres: 9.6/main: jsw_pg_1 jsw_p_db_1 192.168.1.102(42790) SELECT
14:46:39 7649 be/4 postgres 1484.66 K/s 36.57 K/s 0.00 % 99.99 % postgres: 9.6/main: jsw_pg_1 jsw_p_db_1 192.168.1.100(52296) SELECT
14:46:39 7059 be/4 postgres 906.89 K/s 36.57 K/s 0.00 % 99.99 % postgres: 9.6/main: jsw_pg_1 jsw_p_db_1 192.168.1.100(50748) SELECT
14:46:39 7047 be/4 postgres 841.06 K/s 0.00 B/s 0.00 % 99.99 % postgres: 9.6/main: jsw_pg_1 jsw_p_db_1 192.168.1.101(50296) SELECT
14:46:39 10102 be/4 postgres 841.06 K/s 36.57 K/s 0.00 % 99.99 % postgres: 9.6/main: jsw_pg_1 jsw_p_db_1 192.168.1.100(60466) SELECT
14:46:39 10049 be/4 postgres 1272.57 K/s 21.94 K/s 0.00 % 99.99 % postgres: 9.6/main: jsw_pg_1 jsw_p_db_1 192.168.1.100(60246) SELECT
14:46:39 12566 be/4 postgres 811.81 K/s 14.63 K/s 0.00 % 99.99 % postgres: 9.6/main: jsw_pg_1 jsw_p_db_1 192.168.1.101(38448) SELECT
...

Did someone already encounter this kind of problem ?

What could be done to fix this ?

Thanks for your help !

Eddy

[Gonchik Tsymzhitov]

Please, check parameters

https://wiki.postgresql.org/wiki/Performance_Optimization

https://community.atlassian.com/t5/Data-Center-articles/So-when-we-need-to-adjust-configures-for-RDBMS-Or-DB-Performance/ba-p/1393528