From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Scott Carey <scott@richrelevance.com>
Cc: Greg Smith <gsmith@gregsmith.com>
Cc: Jignesh K. Shah <J.K.Shah@sun.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Proposal of tunable fix for scalability of 8.4
Date: Sat, 14 Mar 2009 10:23:57 +0200
Message-ID: <49BB699D.1070809@enterprisedb.com> (raw)
In-Reply-To: <12292.1236964604@sss.pgh.pa.us>
References: <alpine.GSO.2.01.0903121613270.1925@westnet.com>
<C5DED7A7.33B2%scott@richrelevance.com>
<603c8f070903121829r31cf1472ha9876e5c480a542e@mail.gmail.com>
<12292.1236964604@sss.pgh.pa.us>
Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> I think that changing the locking behavior is attacking the problem at
>> the wrong level anyway.
>
> Right. By the time a patch here could have any effect, you've already
> lost the game --- having to deschedule and reschedule a process is a
> large cost compared to the typical lock hold time for most LWLocks. So
> it would be better to look at how to avoid blocking in the first place.
I think the elephant in the room is that we have a single lock that
needs to be acquired every time a transaction commits, and every time a
backend takes a snapshot. It has worked well, and it still does for
smaller numbers of CPUs, but I'm not surprised it starts to become a
bottleneck on a test like the one Jignesh is running. To make matters
worse, the more backends there are, the longer the lock needs to be held
to take a snapshot.
It's going require some hard thinking to bust that bottleneck. I've
sometimes thought about maintaining a pre-calculated array of
in-progress XIDs in shared memory. GetSnapshotData would simply memcpy()
that to private memory, instead of collecting the xids from ProcArray.
Or we could try to move some of the if-tests inside the for-loop to
after the ProcArrayLock is released. For example, we could easily remove
the check for "proc == MyProc", and remove our own xid from the array
afterwards. That's just linear speed up, though. I can't immediately
think of a way to completely avoid / partition away the contention.
WALInsertLock is also quite high on Jignesh's list. That I've seen
become the bottleneck on other tests too.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
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: heikki.linnakangas@enterprisedb.com, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, scott@richrelevance.com, gsmith@gregsmith.com, J.K.Shah@sun.com, Kevin.Grittner@wicourts.gov
Subject: Re: Proposal of tunable fix for scalability of 8.4
In-Reply-To: <49BB699D.1070809@enterprisedb.com>
* 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