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.*
