pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to debug the postgres performance issue
3+ messages / 3 participants
[nested] [flat]
* how to debug the postgres performance issue
@ 2018-09-14 09:34 范国腾 <fanguoteng@highgo.com>
0 siblings, 2 replies; 3+ messages in thread
From: 范国腾 @ 2018-09-14 09:34 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; +Cc: 王亮 <wangliang@highgo.com>
Hi,
We are doing the performance test in two nodes system(active/standby). Now we find that the disk IO is very high but the CPU is low. Is there any method or tool to help us debug to find which function makes this issue?
We try to use the gprof but it does not help us. The result show the LWLockAssign（the shared buffer init） is called the most time. The other function is called no more that 33139. That seems no problem.
Could you please give any suggestion how to debug the high IO/low CPU issue?
Thanks
Steven
===================================
no time accumulated
% cumulative self self total
time seconds seconds calls Ts/call Ts/call name
0.00 0.00 0.00 9240928 0.00 0.00 LWLockAssign
0.00 0.00 0.00 33139 0.00 0.00 ShmemAlloc
0.00 0.00 0.00 33084 0.00 0.00 seg_alloc
….
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to debug the postgres performance issue
@ 2018-09-15 08:21 Ron <ronljohnsonjr@gmail.com>
parent: 范国腾 <fanguoteng@highgo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Ron @ 2018-09-15 08:21 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 09/14/2018 04:34 AM, 范国腾 wrote:
>
> Hi,
>
> We are doing the performance test in two nodes system(active/standby).
> Now we find that the disk IO is very high but the CPU is low.
>
On both nodes or just one (and if so, which one)?
If you incrementally increase the workload, starting from the quiescent
state, when do you see this happening?
> Is there any method or tool to help us debug to find which function
> makes this issue?
>
> We try to use the gprof but it does not help us. The result show the
> LWLockAssign（the shared buffer init）is called the most time. The other
> function is called no more that 33139. That seems no problem.
>
> Could you please give any suggestion how to debug the high IO/low CPU
> issue?
>
> Thanks
>
> Steven
>
> ===================================
>
> no time accumulated
>
> % cumulative self self total
>
> time seconds seconds calls Ts/call Ts/call name
>
> 0.00 0.00 0.00 9240928 0.00 0.00 LWLockAssign
>
> 0.00 0.00 0.00 33139 0.00 0.00 ShmemAlloc
>
> 0.00 0.00 0.00 33084 0.00 0.00 seg_alloc
>
> ….
>
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to debug the postgres performance issue
@ 2018-09-16 11:56 Olivier Gautherot <olivier@gautherot.net>
parent: 范国腾 <fanguoteng@highgo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Olivier Gautherot @ 2018-09-16 11:56 UTC (permalink / raw)
To: fanguoteng@highgo.com; +Cc: pgsql-admin@lists.postgresql.org; wangliang@highgo.com
On Fri, Sep 14, 2018 at 6:29 AM 范国腾 <fanguoteng@highgo.com> wrote:
> Hi,
>
>
>
> We are doing the performance test in two nodes system(active/standby). Now
> we find that the disk IO is very high but the CPU is low. Is there any
> method or tool to help us debug to find which function makes this issue?
>
>
>
> We try to use the gprof but it does not help us. The result show the
> LWLockAssign（the shared buffer init） is called the most time. The other
> function is called no more that 33139. That seems no problem.
>
>
>
> Could you please give any suggestion how to debug the high IO/low CPU
> issue?
>
>
>
> Thanks
>
> Steven
>
>
>
> ===================================
>
> no time accumulated
>
>
>
> % cumulative self self total
>
> time seconds seconds calls Ts/call Ts/call name
>
> 0.00 0.00 0.00 9240928 0.00 0.00 LWLockAssign
>
> 0.00 0.00 0.00 33139 0.00 0.00 ShmemAlloc
>
> 0.00 0.00 0.00 33084 0.00 0.00 seg_alloc
>
> ….
>
>
There are a few things you should investigate for a start, as you seem to
be lifting more data than you probably should.
1) Check the sizes of your tables (in particular the number of rows) and
identify the heaviest
2) With pgfouine or pgbadger, check which queries take most time and see if
there is a correlation (there probably is)
3) Run EXPLAIN on these queries to see if you have a sequential scan on the
whole table that should be optimized
4) Design indexes to relieve these queries (note that the queries will run
faster but the INSERT on these tables will be heavier)
5) In postgresql.conf, check the following parameters:
* shared_buffers (I've set it to 1/4 of the RAM with good success)
* temp_buffers (you may need to experiment in double increments)
* work_mem (try to increase it too)
6) Check how often the autovacuum runs (could be another indicator)
Olivier
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-09-16 11:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-09-14 09:34 how to debug the postgres performance issue 范国腾 <fanguoteng@highgo.com>
2018-09-15 08:21 ` Ron <ronljohnsonjr@gmail.com>
2018-09-16 11:56 ` Olivier Gautherot <olivier@gautherot.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox