Hi, Thanks for providing all this info. On Tue, Mar 29, 2022 at 11:34:18PM +0530, Rambabu g wrote: > 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. What indexes are defined on this table ? How large are they ? > Red Hat Enterprise Linux Server release 7.9 (Maipo) > 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 > > shared_buffers=12GB > work_mem=128MB > 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 ; Is the only only problem query, or just one example or ?? Are your issues with loading data, querying data or both ? > -bash-4.2$ iostat -x It shows that you only have a few filesystems in use. It's common to have WAL and temp_tablespaces on a separate FS. That probably wouldn't help your performance at all, but it would help to tell what's doing I/O. Is there anything else running on the VM besides postgres ? You can also check: SELECT COUNT(1), wait_event FROM pg_stat_activity GROUP BY 2 ORDER BY 1 DESC; And the pg_buffercache extension: SELECT COUNT(nullif(isdirty,'f')) dirty, COUNT(1) all, COALESCE(c.relname, b.relfilenode::text) FROM pg_buffercache b LEFT JOIN pg_class c ON b.relfilenode=pg_relation_filenode(c.oid) GROUP BY 3 ORDER BY 1 DESC,2 DESC LIMIT 9; > Hypervisor vendor: KVM Are KSM or THP enabled on the hypervisor ? tail /sys/kernel/mm/ksm/run /sys/kernel/mm/transparent_hugepage/khugepaged/defrag /sys/kernel/mm/transparent_hugepage/enabled /sys/kernel/mm/transparent_hugepage/defrag -- Justin
