Re: HIGH IO and Less CPU utilization

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx> 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. 

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux