pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedslow server : s_lock and _bt_checkkeys on perf top
4+ messages / 3 participants
[nested] [flat]
* slow server : s_lock and _bt_checkkeys on perf top
@ 2015-03-04 19:31 Soni M <diptatapa@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Soni M @ 2015-03-04 19:31 UTC (permalink / raw)
To: pgsql-performance
Hello All,
Master db size 1.5 TB
All postgres 9.1.13 installed from RHEL package.
It has streaming replica and slony replica to another servers.
Server performance is slower than usual, before that, there's a big query
got cancelled and then performance get slow.
No sign of IO wait.
on sar, it's %user and %system dominate the cpu usage
01:25:04 PM CPU %user %nice %system %iowait %steal
%idle
Average: all 51.91 0.00 12.03 0.66 0.00
35.39
on perf top, i saw
18.93% postgres [.] s_lock
10.72% postgres [.] _bt_checkkeys
almost always at top.
I don't have any idea, what's causing it or how to resolve it ?
Any answer is very appreciated.
--
Regards,
Soni Maula Harriz
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: slow server : s_lock and _bt_checkkeys on perf top
@ 2015-03-06 01:29 Jim Nasby <Jim.Nasby@BlueTreble.com>
parent: Soni M <diptatapa@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jim Nasby @ 2015-03-06 01:29 UTC (permalink / raw)
To: Soni M <diptatapa@gmail.com>; pgsql-performance
On 3/4/15 1:31 PM, Soni M wrote:
> Hello All,
>
> Master db size 1.5 TB
> All postgres 9.1.13 installed from RHEL package.
> It has streaming replica and slony replica to another servers.
>
> Server performance is slower than usual, before that, there's a big
> query got cancelled and then performance get slow.
>
> No sign of IO wait.
>
> on sar, it's %user and %system dominate the cpu usage
> 01:25:04 PM CPU %user %nice %system %iowait %steal
> %idle
> Average: all 51.91 0.00 12.03 0.66 0.00
> 35.39
>
> on perf top, i saw
> 18.93% postgres [.] s_lock
> 10.72% postgres [.] _bt_checkkeys
> almost always at top.
_bt_checkkeys is the function that compares a row in a btree index to a
condition. s_lock is a spinlock; the high CPU usage in there indicates
there's heavy lock contention somewhere.
Is there one PG process that's clearly using more CPU than the others?
What else is running in the database? Are there any unusual data types
involved?
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: slow server : s_lock and _bt_checkkeys on perf top
@ 2015-03-06 14:31 Merlin Moncure <mmoncure@gmail.com>
parent: Soni M <diptatapa@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Merlin Moncure @ 2015-03-06 14:31 UTC (permalink / raw)
To: Soni M <diptatapa@gmail.com>; +Cc: pgsql-performance
On Wed, Mar 4, 2015 at 1:31 PM, Soni M <diptatapa@gmail.com> wrote:
> Hello All,
>
> Master db size 1.5 TB
> All postgres 9.1.13 installed from RHEL package.
> It has streaming replica and slony replica to another servers.
>
> Server performance is slower than usual, before that, there's a big query
> got cancelled and then performance get slow.
>
> No sign of IO wait.
>
> on sar, it's %user and %system dominate the cpu usage
> 01:25:04 PM CPU %user %nice %system %iowait %steal
> %idle
> Average: all 51.91 0.00 12.03 0.66 0.00
> 35.39
>
> on perf top, i saw
> 18.93% postgres [.] s_lock
> 10.72% postgres [.] _bt_checkkeys
> almost always at top.
>
> I don't have any idea, what's causing it or how to resolve it ?
Can you post the entire 'perf top'? do you see (specifically I'm
wondering if you are bumping against the RecoveryInProgress s_lock
issue). If so, upgrading postgres might be the best way to resolve
the issue.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: slow server : s_lock and _bt_checkkeys on perf top
@ 2015-03-06 16:04 Soni M <diptatapa@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Soni M @ 2015-03-06 16:04 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
Thanks All for the response, finally we figure it out. The slow is due to
high number of dead rows on main table, repack these tables wipe out the
issue.
On Mar 6, 2015 9:31 PM, "Merlin Moncure" <mmoncure@gmail.com> wrote:
> On Wed, Mar 4, 2015 at 1:31 PM, Soni M <diptatapa@gmail.com> wrote:
> > Hello All,
> >
> > Master db size 1.5 TB
> > All postgres 9.1.13 installed from RHEL package.
> > It has streaming replica and slony replica to another servers.
> >
> > Server performance is slower than usual, before that, there's a big query
> > got cancelled and then performance get slow.
> >
> > No sign of IO wait.
> >
> > on sar, it's %user and %system dominate the cpu usage
> > 01:25:04 PM CPU %user %nice %system %iowait %steal
> > %idle
> > Average: all 51.91 0.00 12.03 0.66 0.00
> > 35.39
> >
> > on perf top, i saw
> > 18.93% postgres [.] s_lock
> > 10.72% postgres [.] _bt_checkkeys
> > almost always at top.
> >
> > I don't have any idea, what's causing it or how to resolve it ?
>
> Can you post the entire 'perf top'? do you see (specifically I'm
> wondering if you are bumping against the RecoveryInProgress s_lock
> issue). If so, upgrading postgres might be the best way to resolve
> the issue.
>
> merlin
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2015-03-06 16:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-03-04 19:31 slow server : s_lock and _bt_checkkeys on perf top Soni M <diptatapa@gmail.com>
2015-03-06 01:29 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
2015-03-06 14:31 ` Merlin Moncure <mmoncure@gmail.com>
2015-03-06 16:04 ` Soni M <diptatapa@gmail.com>
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