HIGH IO and Less CPU utilization

Hi All,

We have an issue with high load and IO Wait's but less cpu on postgres
Database, The emp Table size is around 500GB, and the connections are very
less.

Please suggest to us do we need to change and config parameters at system
level or Postgres configuration.

postgres=# select version();

version

----------------------------------------------------------------------------------------------------------

PostgreSQL 11.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-44), 64-bit

(1 row)

postgres=# \q

*Postgres Parameters Setting :*

shared_buffers=12GB
work_mem=128MB
effective_cache_size=48GB
maintenance_work_mem=2GB
max_connections=500

14428 | 04:45:59.712892 | active | INSERT INTO target (empno, name)
SELECT
empno, '' AS name FROM (select distinct empno from emp where sname='test'
and tp='EMP NAME 1' LIMIT 10) AS query ;

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+
COMMAND

14428 postgres 20 0 12.6g 12.2g 12.2g D 5.3 13.3 4:43.57
postgres: postgres postgres (59436) INSERT

29136 postgres 20 0 12.6g 401812 398652 D 4.7 0.4 0:01.20
postgres: postgres postgres (48220) SELECT

29119 postgres 20 0 12.6g 677704 674064 S 3.3 0.7 0:02.05
postgres: postgres postgres (37684) idle

29121 postgres 20 0 12.6g 758428 755252 S 3.0 0.8 0:02.33
postgres: postgres postgres (57392) idle

29166 postgres 20 0 12.6g 260436 257408 S 3.0 0.3 0:00.63
postgres: postgres postgres (59424) idle

29181 postgres 20 0 12.6g 179136 175860 D 2.3 0.2 0:00.18
postgres: postgres postgres (57092) SELECT

29129 postgres 20 0 12.6g 442444 439212 S 1.7 0.5 0:01.33
postgres: postgres postgres (36560) idle

-bash-4.2$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 7.9 (Maipo)

-bash-4.2$ uname

Linux

-bash-4.2$ uname -a

Linux ip.ec2.internal 3.10.0-1160.59.1.el7.x86_64 #1 SMP Wed Feb 16
12:17:35 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

-bash-4.2$ top

top - 17:02:52 up 1 day, 1:44, 2 users, load average: 11.60, 22.27, 22.22

Tasks:* 316 *total,* 1 *running,* 315 *sleeping,* 0 *stopped,* 0 *
zombie

%Cpu(s):* 0.5 *us,* 0.5 *sy,* 0.0 *ni,* 92.0 *id,* 7.0 *wa,* 0.0 *hi,
* 0.0 *si,* 0.0 *st

KiB Mem :* 96639952 *total,* 483896 *free,* 1693960 *used,* 94462096 *
buff/cache

KiB Swap:* 0 *total,* 0 *free,* 0 *used.* 81408928 *avail
Mem

-bash-4.2$ iostat -x

Linux 3.10.0-1160.59.1.el7.x86_64 (ip.ec2.internal) 03/29/2022 _x86_64_ (24
CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.33 0.00 0.24 7.54 0.00 91.88

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz
avgqu-sz await r_await w_await svctm %util

nvme1n1 0.00 3.45 1042.22 29.88 41998.88 1476.75
81.10 7.61 7.10 6.62 23.70 0.40 43.19

nvme2n1 0.00 0.02 0.02 1.06
0.15 268.80 497.00 0.09 80.87 0.85 82.56 1.40 0.15

nvme0n1 0.00 0.01 0.21 0.08 4.94 7.07
81.37 0.00 6.88 0.61 22.83 0.64 0.02

-bash-4.2$ vmstat -a

procs -----------memory---------- ---swap-- -----io---- -system--
------cpu-----

r b swpd free inact active si so bi bo in cs us sy id
wa st

1 8 0 476180 40092640 53043492 0 0 1753 73 2 14 0 0
92 8 0

-bash-4.2$ vmstat -d

disk- ------------reads------------ ------------writes-----------
-----IO------

total merged sectors ms total merged sectors ms cur
sec

nvme1n1 99492480 0 8016369922 658540488 2849690 329519 281661496
67518819 0 41210

nvme2n1 2126 0 27946 1811 101078 2312 51264208 8344670
0 144

nvme0n1 20254 6 942763 12340 7953 641 1348866 181438
0 18

-bash-4.2$ sar

Linux 3.10.0-1160.59.1.el7.x86_64 (ip.ec2.internal) 03/29/2022 _x86_64_ (24
CPU)

04:20:01 PM CPU %user %nice %system %iowait %steal
%idle

04:30:01 PM all 0.70 0.00 0.69 27.92 0.00
70.68

04:40:01 PM all 0.71 0.00 0.70 27.76 0.00
70.84

04:50:01 PM all 0.70 0.00 0.69 26.34 0.00
72.27

05:00:01 PM all 0.70 0.00 0.68 27.32 0.00
71.31

05:10:01 PM all 0.70 0.00 0.69 27.83 0.00
70.77

05:20:01 PM all 0.70 0.00 0.69 28.16 0.00
70.45

05:30:01 PM all 0.71 0.00 0.69 26.62 0.00
71.98

05:40:01 PM all 0.69 0.00 0.68 25.77 0.00
72.85

Average: all 0.70 0.00 0.69 27.21 0.00
71.40

-bash-4.2$

-bash-4.2$ free -g

total used free shared buff/cache
available

Mem: 92 1 0 12 90
77

Swap: 0 0 0

-bash-4.2$ free -m

total used free shared buff/cache
available

Mem: 94374 1721 474 12581 92178
79430

Swap: 0 0 0

-bash-4.2$ lscpu

Architecture: x86_64

CPU op-mode(s): 32-bit, 64-bit

Byte Order: Little Endian

CPU(s): 24

On-line CPU(s) list: 0-23

Thread(s) per core: 2

Core(s) per socket: 12

Socket(s): 1

NUMA node(s): 1

Vendor ID: GenuineIntel

CPU family: 6

Model: 85

Model name: Intel(R) Xeon(R) Platinum 8252C CPU @ 3.80GHz

Stepping: 7

CPU MHz: 3799.998

BogoMIPS: 7599.99

Hypervisor vendor: KVM

Virtualization type: full

L1d cache: 32K

L1i cache: 32K

L2 cache: 1024K

L3 cache: 25344K

NUMA node0 CPU(s): 0-23

Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb
rdtscp lm constant_tsc arch_perfmon rep_good nopl xtopology nonstop_tsc
aperfmperf eagerfpu pni pclmulqdq monitor ssse3 fma cx16 pcid sse4_1 sse4_2
x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor
lahf_lm abm 3dnowprefetch invpcid_single fsgsbase tsc_adjust bmi1 avx2 smep
bmi2 erms invpcid mpx avx512f avx512dq rdseed adx smap clflushopt clwb
avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 ida arat pku ospke
avx512_vnni

*Thanks & Regards,*

*Ramababu.*