Date: Tue, 17 Sep 2013 13:59:26 +0200
From: Andres Freund <andres@2ndquadrant.com>
To: =?utf-8?B?0JTQvNC40YLRgNC40Lkg0JTQtdCz0YLRj9GA0ZHQsg==?=
 <degtyaryov@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>,
 postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Cpu usage 100% on slave. s_lock problem.
Message-ID: <20130917115926.GA5452@awork2.anarazel.de>
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>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: 
 <CAFcrtwHU_iEfhoz-atgNXwVsw=3F4k8x5hfsrAxtB=ftOnWYBw@mail.gmail.com>
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi,

On 2013-09-17 17:55:01 +0600, =D0=94=D0=BC=D0=B8=D1=82=D1=80=D0=B8=D0=B9 =
=D0=94=D0=B5=D0=B3=D1=82=D1=8F=D1=80=D1=91=D0=B2 wrote:
> We have not been able to reproduce this problem on a test servers. Use th=
is
> patch to production servers do not dare.
>=20
> In the course of studying the problems we have identified that many queri=
es
> are executed on the slave several times slower. On master function
> heap_hot_search_buffer execute 100 cycles, on the slave the same query wi=
th
> 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.

Greetings,

Andres Freund

--=20
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance