From: Andres Freund <andres@2ndquadrant.com>
To: Metin Doslu <metin@citusdata.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Cc: Amit Kapila <amit.kapila16@gmail.com>
Cc: Claudio Freire <klaussfreire@gmail.com>
Subject: Re: Parallel Select query performance and shared buffers
Date: Thu, 5 Dec 2013 10:42:26 +0100
Message-ID: <20131205094226.GC14419@alap2.anarazel.de> (raw)
In-Reply-To: <CAL1dPcfJmYNaegTUVi9nDYZRBwr8F=dWUTcdUg9Y1P1gO-nYWw@mail.gmail.com>
References: <CAL1dPcfZJFO2gr+53mU14VrrHxT-Bs6QOYgzQNV_VgxnkKUaZA@mail.gmail.com>
<CAL1dPcdm1q0q0VtOpe2gfOyCwS7FkzURw=ZQ1RsLgfKgz5=gMg@mail.gmail.com>
<CAL1dPccxwZkh8Vih4X0kKSCtGA-S7HasmRZe49TK3bqqy2rSBg@mail.gmail.com>
<20131205091841.GE28793@alap2.anarazel.de>
<CAL1dPcfJmYNaegTUVi9nDYZRBwr8F=dWUTcdUg9Y1P1gO-nYWw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 2013-12-05 11:33:29 +0200, Metin Doslu wrote:
> > Is your workload bigger than RAM?
>
> RAM is bigger than workload (more than a couple of times).
> > I think a good bit of the contention
> > you're seeing in that listing is populating shared_buffers - and might
> > actually vanish once you're halfway cached.
> > From what I've seen so far the bigger problem than contention in the
> > lwlocks itself, is the spinlock protecting the lwlocks...
>
> Could you clarify a bit what do you mean by "halfway cached"
Well, your stats showed a) fairly low lock counts overall b) a high
percentage of exclusive locks.
a) indicates the system wasn't running long.
b) tells me there were lots of changes to the buffer mapping - which
basically only happens if a buffer is placed or removed from
shared-buffers.
If your shared_buffers is big enough to contain most of the data you
shouldn't see many exclusive locks in comparison to the number of shared
locks.
> and "spinlock protecting the lwlocks".
Every LWLock has an internal spinlock to protect its state. So whenever
somebody does a LWLockAcquire()/Release(), even if only in shared mode,
we currently acquire that spinlock, manipulate the LWLocks state, and
release the spinlock again. In lots of workloads that internal spinlock
is the contention point, not the lenght over which the lwlock is
held. Especially when they are mostly held in shared mode.
Makes sense?
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, metin@citusdata.com, pgsql-hackers@postgresql.org, amit.kapila16@gmail.com, klaussfreire@gmail.com
Subject: Re: Parallel Select query performance and shared buffers
In-Reply-To: <20131205094226.GC14419@alap2.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