Hi Justin, Thanks for the quick response and your help, Please go through the inputs and let me know if need to change anything at OS level parameters tune and DB parameters.
On Tue, 29 Mar 2022 at 23:54, Justin Pryzby <pry...@telsasoft.com> wrote: > 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 ? > > There are three indexes defined on the table, each one is around 20 to 25GB and the indexes is create on postgres=# explain select distinct empno from emp where sname='test' and tp='EMP NAME 1' QUERY PLAN ------------------------------------------------------------------------------------------------------ HashAggregate (cost=71899575.17..71900816.97 rows=124179 width=9) Group Key: empno -> Gather (cost=1000.00..71820473.80 rows=31640550 width=9) Workers Planned: 2 -> Parallel Seq Scan on emp (cost=0.00..68655418.80 rows=13183562 width=9) Filter: (((sname)::text = 'test'::text) AND ((tp)::text = 'EMP NAME 1'::text) > > 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 ? > > No, the Ec2 VM is delicate to postgres DB instances only. > You can also check: > SELECT COUNT(1), wait_event FROM pg_stat_activity GROUP BY 2 ORDER BY 1 > DESC; > postgres=# SELECT COUNT(1), wait_event FROM pg_stat_activity GROUP BY 2 ORDER BY 1 DESC; count | wait_event -------+--------------------- 70 | ClientRead 34 | DataFileRead 3 | 1 | LogicalLauncherMain 1 | WalWriterMain 1 | BgWriterMain 1 | AutoVacuumMain (7 rows) > And the pg_buffercache extension: > SELECT COUNT(nullif(isdirty,'f')) dirty, COUNT(1) as 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; > > postgres=# SELECT COUNT(nullif(isdirty,'f')) dirty, COUNT(1) as 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; dirty | all | coalesce -------+---------+---------------------------------------------------- 189 | 237348 | emp_status 97 | 1214949 | emp 77 | 259 | public_group 75 | 432 | public_gid 74 | 233 | public_utpu 26 | 115 | code_evd 15 | 55 | group 15 | 49 | output 14 | 77 | output_status (9 rows > > 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 > > > -bash-4.2$ 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 ==> /sys/kernel/mm/ksm/run <== 0 ==> /sys/kernel/mm/transparent_hugepage/khugepaged/defrag <== 1 ==> /sys/kernel/mm/transparent_hugepage/enabled <== [always] madvise never ==> /sys/kernel/mm/transparent_hugepage/defrag <== [always] madvise never > -- > Justin > Regards, Rambabu.