Re: HIGH IO and Less CPU utilization

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 <pryzby(at)telsasoft(dot)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.