Message-ID: <51BDD415.4050506@vmware.com>
Date: Sun, 16 Jun 2013 18:04:53 +0300
From: Heikki Linnakangas <hlinnakangas@vmware.com>
Organization: VMware
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:10.0.12) Gecko/20130116 Icedove/10.0.12
MIME-Version: 1.0
To: Simon Riggs <simon@2ndQuadrant.com>
CC: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>,
 Tom Lane <tgl@sss.pgh.pa.us>,
 Gavin Flower <gavinflower@archidevsys.co.nz>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: In progress INSERT wrecks plans on table
References: <5179E77B.7050601@catalyst.net.nz>
 <5179ECC5.2010206@archidevsys.co.nz> <5179F259.3010509@catalyst.net.nz>
 <5179F5C6.8070702@archidevsys.co.nz> <5180E3C8.3010201@catalyst.net.nz>
 <2121.1367417215@sss.pgh.pa.us> <5181B80D.70404@catalyst.net.nz>
 <CA+U5nMKYYZDekhtxz1E1B11dAm9UJoHBQHrB6DiiZDJo3fLd0Q@mail.gmail.com>
 <916bff05a5ab130c76d7835abbaf7762.squirrel@mail.catalyst.net.nz>
 <CA+U5nMJHjQccVSB3SkAHdRn2AfuuO49wA8-ZGB366cddTynjdQ@mail.gmail.com>
In-Reply-To: 
 <CA+U5nMJHjQccVSB3SkAHdRn2AfuuO49wA8-ZGB366cddTynjdQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 03.05.2013 15:41, Simon Riggs wrote:
> What appears to be happening is we're spending a lot of time in
> TransactionIdIsInProgress() so we can set hints and then when we find
> it is still in progress we then spend more time in XidIsInSnapshot()
> while we check that it is still invisible to us. Even if the
> transaction we see repeatedly ends, we will still pay the cost in
> XidIsInSnapshot repeatedly as we execute.
>
> Given that code path, I would expect it to suck worse on a live system
> with many sessions, and even worse with many subtransactions.
>
> (1) A proposed fix is attached, but its only a partial one and barely tested.
>
> Deeper fixes might be
>
> (2)  to sort the xid array if we call XidIsInSnapshot too many times
> in a transaction. I don't think that is worth it, because a long
> running snapshot may be examined many times, but is unlikely to see
> multiple in-progress xids repeatedly. Whereas your case seems
> reasonably common.

Yeah, sorting would be a waste of time most of the time.

Instead of adding a new cache field, how about just swapping the matched 
XID to the beginning of the array?

Did you have some simple performance test script for this?

- Heikki


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