From: Andres Freund <andres@2ndquadrant.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Дмитрий Дегтярёв <degtyaryov@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Cpu usage 100% on slave. s_lock problem.
Date: Tue, 17 Sep 2013 15:24:39 +0200
Message-ID: <20130917132439.GD5452@awork2.anarazel.de> (raw)
In-Reply-To: <CAHyXU0weAh-qZQ_CCCNoded9wd9WrzVdmnyto_JN+kESjN-1Jw@mail.gmail.com>
References: <CAFcrtwHH5r9x=w5_09HF2LCa4TEVOmjMOW5f=+p-Gc6k6joNzQ@mail.gmail.com>
<CAHyXU0xAKkjRA03GXP71yqqqpdxepH5p5qZwddjPTOWQOkjoHQ@mail.gmail.com>
<CAHyXU0wZZPDU5AfsnC_z_kg5LVLzC9cXr+T84N2n+u=GDxNi8w@mail.gmail.com>
<CAHyXU0wavt2APZ89C7x9b3y-p-2trkdk6UpyQotiPXgPVKd_UQ@mail.gmail.com>
<CAHyXU0z3BbK=-Qu342Nxr9EbLtASKWq7XH+9tyaq17MQd+YP0w@mail.gmail.com>
<20130827155556.GA9061@awork2.anarazel.de>
<CAHyXU0yLWU4O=0n6qm5tDTDxBdNwzjhUfA456cYggWY6zUXDMg@mail.gmail.com>
<CAFcrtwHU_iEfhoz-atgNXwVsw=3F4k8x5hfsrAxtB=ftOnWYBw@mail.gmail.com>
<20130917115926.GA5452@awork2.anarazel.de>
<CAHyXU0weAh-qZQ_CCCNoded9wd9WrzVdmnyto_JN+kESjN-1Jw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 2013-09-17 08:18:54 -0500, Merlin Moncure wrote:
> On Tue, Sep 17, 2013 at 6:59 AM, Andres Freund <andres@2ndquadrant.com> wrote:
> > Hi,
> >
> > On 2013-09-17 17:55:01 +0600, Дмитрий Дегтярёв wrote:
> >> We have not been able to reproduce this problem on a test servers. Use this
> >> patch to production servers do not dare.
> >>
> >> In the course of studying the problems we have identified that many queries
> >> are executed on the slave several times slower. On master function
> >> heap_hot_search_buffer execute 100 cycles, on the slave the same query with
> >> the same plan function heap_hot_search_buffer execute 2000 cycles.
> >> Also, we were able to reproduce the problem on the master and detect that
> >> there s_lock of slow queries.
> >
> > What you describe is normally an indication that you have too many
> > longrunning transactions around preventing hot pruning from working.
>
> Do you think it's worth submitting the lock avoidance patch for formal review?
You mean the bufmgr.c thing? Generally I think that that code needs a
good of scalability work - there's a whole thread about it
somewhere. But TBH the theories you've voiced about the issues you've
seen haven't convinced me so far.
If you can manage to prove it has a benefit in some case that's
reproducable - why not go ahead?
Quick question: Do you happen to have pg_locks output from back then
around? We've recently found servers going into somewhat similar
slowdowns because they exhausted the fastpath locks which made lwlocks
far more expensive and made s_lock go up very high in the profle.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andres@2ndquadrant.com, mmoncure@gmail.com, degtyaryov@gmail.com
Subject: Re: Cpu usage 100% on slave. s_lock problem.
In-Reply-To: <20130917132439.GD5452@awork2.anarazel.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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