From: Heikki Linnakangas <hlinnakangas@vmware.com>
To: Simon Riggs <simon@2ndQuadrant.com>
Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Gavin Flower <gavinflower@archidevsys.co.nz>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: In progress INSERT wrecks plans on table
Date: Sun, 16 Jun 2013 18:04:53 +0300
Message-ID: <51BDD415.4050506@vmware.com> (raw)
In-Reply-To: <CA+U5nMJHjQccVSB3SkAHdRn2AfuuO49wA8-ZGB366cddTynjdQ@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: hlinnakangas@vmware.com, simon@2ndQuadrant.com, mark.kirkwood@catalyst.net.nz, tgl@sss.pgh.pa.us, gavinflower@archidevsys.co.nz
Subject: Re: In progress INSERT wrecks plans on table
In-Reply-To: <51BDD415.4050506@vmware.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