Thread: pessimal trivial-update performance
Consider updating a PL/pgsql variable repeatedly, and then consider
updating a single-column, single-row table repeatedly, thus:
CREATE OR REPLACE FUNCTION update_var() RETURNS void AS $$
DECLARE
x int := 0;
BEGIN
FOR i IN 1..100000 LOOP
x := x + 1;
END LOOP;
END
$$ LANGUAGE plpgsql;
CREATE TABLE tab (x integer);
CREATE OR REPLACE FUNCTION update_tab() RETURNS void AS $$
BEGIN
INSERT INTO tab VALUES (0);
FOR i IN 1..100000 LOOP
UPDATE tab SET x = x + 1;
END LOOP;
END
$$ LANGUAGE plpgsql;
On my Fedora 12 VM, the first of these takes 33-36 ms, and the second
takes 114-121 s. While you'd expect updating a table to be slower
than updating a variable, a factor of 3000x seems rather excessive to
me. Profiling reveals that 80%+ of the time is spend testing tuple
visibility, which apparently needs to be done an average of over 7000
times per loop iteration. Full gprof results are attached, bzip'd so
as to avoid hitting the attachment size limit for this list.
Highlights below:
27.00 42.60 42.60 1410265409 0.00 0.00
TransactionIdIsCurrentTransactionId
23.51 79.69 37.09 705082704 0.00 0.00 HeapTupleSatisfiesMVCC
19.65 110.69 31.00 705182704 0.00 0.00 HeapTupleHeaderGetCmin
13.04 131.26 20.57 704982704 0.00 0.00 HeapTupleHeaderGetCmax
8.09 144.02 12.76 22173923 0.00 0.01 heapgetpage
1.09 145.74 1.72 XidInMVCCSnapshot
heapgettup_pagemode is called 200,000 times exactly; it makes
2,217,932 calls to heapgetpage (or approximately 110 per call), which
makes 705,082,704 calls to HeapTupleSatisfiesMVCC (or approximately
317 per heapgetpage call). Is there anything we can do about this?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
Attachment
Robert Haas <robertmhaas@gmail.com> writes:
> CREATE OR REPLACE FUNCTION update_tab() RETURNS void AS $$
> BEGIN
> INSERT INTO tab VALUES (0);
> FOR i IN 1..100000 LOOP
> UPDATE tab SET x = x + 1;
> END LOOP;
> END
> $$ LANGUAGE plpgsql;
I believe that none of the dead row versions can be vacuumed during this
test. So yes, it sucks, but is it representative of real-world cases?
regards, tom lane
On Sun, Jul 4, 2010 at 12:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> CREATE OR REPLACE FUNCTION update_tab() RETURNS void AS $$ >> BEGIN >> INSERT INTO tab VALUES (0); >> FOR i IN 1..100000 LOOP >> UPDATE tab SET x = x + 1; >> END LOOP; >> END >> $$ LANGUAGE plpgsql; > > I believe that none of the dead row versions can be vacuumed during this > test. Yep, you seem to be right. The table grows to 802 pages. But why is it that we can't vacuum them as we go along? > So yes, it sucks, but is it representative of real-world cases? Hard to say, but I think it probably is to some degree. I stumbled on it more-or-less by accident, but it wouldn't surprise me to find out that there are people doing such things in real applications. It's not uncommon to want to store an updateable counter somewhere. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Am 04.07.2010 06:11, wrote Tom Lane: > ... but is it representative of real-world cases? > > regards, tom lane > Hi Tom, we do run an application in productive use that suffered from a similar effect. We did not have 1000000 updates per row, but 10-100 updates per row on about 1-10 million rows of a table. In the end we managed to increase performance by factor of more than two by adding support to the application to track updates internally and only "flush" changes to the database at the (final) application commit. This did cost a lot as now we needed to adjust queries on the table with data stored internally (as not yet reflected in the database). This still is more efficient as updating and performing operation an the database directly. (e.g. an update using the primary key of the table (about 50 million rows total) would have lasted over 3 seconds(!) while initially the very same update was done within far below 1ms). So I think this could qualify as a real world example of that case. Regards, Rainer -- Rainer Pruy Managing Director Acrys Consult GmbH & Co. KG Theodor-Heuss-Str. 53-63, 61118 Bad Vilbel, Germany Phone: +49-6101-98760-0 Fax: +49-6101-98760-50 Web: http://www.acrys.com - Email: office@acrys.com Registered: Frankfurt am Main, HRA 31151 General partner: Acrys Verwaltungs GmbH Theodor-Heuss-Str. 53-63, D-61118 Bad Vilbel Registered: Frankfurt am Main, HRB 57625
Robert Haas <robertmhaas@gmail.com> writes:
> On Sun, Jul 4, 2010 at 12:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I believe that none of the dead row versions can be vacuumed during this
>> test.
> Yep, you seem to be right. The table grows to 802 pages. But why is
> it that we can't vacuum them as we go along?
Sure. What you'd need is for HeapTupleSatisfiesVacuum to observe that
(a) the tuple's xmin and xmax are equal,
(b) they're equal to my own transaction's XID,
(c) none of the live snapshots in my backend can see cmin but not cmax,
(d) cmax < currentCommandId, ensuring that every future snapshot will see cmax too (not quite convinced this is
certainto hold).
Now that we have a centralized list of all live snapshots, it's at least
possible in principle to do (c).
(I'm ignoring the possibility that the xmin and xmax are from different
subtransactions of my own XID --- that seems to complicate matters
greatly in order to handle even-more-cornerish cases.)
Of course, you'd also need to get to HeapTupleSatisfiesVacuum in the
first place. The complained-of case lacks any VACUUM call. Maybe a HOT
cleanup would happen at the right time but I'm not sure. If it doesn't,
adding one would represent a significant expenditure that would usually
not be repaid.
Another issue here is that since xmin is certainly within the GlobalXmin
horizon, it would be essential to preserve the update chain ctid links,
ie, make the tuple's update predecessor point to its successor. That
seems workable for the case of cleaning out an intermediate entry in a
HOT chain, but not otherwise.
Details left as an exercise for the student.
regards, tom lane
On 2010-07-04 06:11, Tom Lane wrote: > Robert Haas<robertmhaas@gmail.com> writes: > >> CREATE OR REPLACE FUNCTION update_tab() RETURNS void AS $$ >> BEGIN >> INSERT INTO tab VALUES (0); >> FOR i IN 1..100000 LOOP >> UPDATE tab SET x = x + 1; >> END LOOP; >> END >> $$ LANGUAGE plpgsql; >> > I believe that none of the dead row versions can be vacuumed during this > test. So yes, it sucks, but is it representative of real-world cases? > > The problem can generally be written as "tuples seeing multiple updates in the same transaction"? I think that every time PostgreSQL is used with an ORM, there is a certain amount of multiple updates taking place. I have actually been reworking clientside to get around multiple updates, since they popped up in one of my profiling runs. Allthough the time I optimized away ended being both "roundtrip time" + "update time", but having the database do half of it transparently, might have been sufficient to get me to have had a bigger problem elsewhere.. To sum up. Yes I think indeed it is a real-world case. Jesper -- Jesper
> The problem can generally be written as "tuples seeing multiple > updates in the same transaction"? > > I think that every time PostgreSQL is used with an ORM, there is > a certain amount of multiple updates taking place. I have actually > been reworking clientside to get around multiple updates, since they > popped up in one of my profiling runs. Allthough the time I optimized > away ended being both "roundtrip time" + "update time", but having > the database do half of it transparently, might have been sufficient > to get me to have had a bigger problem elsewhere.. > > To sum up. Yes I think indeed it is a real-world case. > > Jesper On the Python side, elixir and sqlalchemy have an excellent way of handling this, basically when you start a transaction, all changes are accumulated in a "session" object and only flushed to the database on session commit (which is also generally the transaction commit). This has multiple advantages, for instance it is able to issue multiple-line statements, updates are only done once, you save a lot of roundtrips, etc. Of course it is most of the time not compatible with database triggers, so if there are triggers the ORM needs to be told about them.
On 2010-07-05 12:11, Pierre C wrote:<br /><span style="white-space: pre;">> <br /> >> The problem can generallybe written as "tuples seeing multiple <br /> >> updates in the same transaction"?<br /> >> <br /> >>I think that every time PostgreSQL is used with an ORM, there is a<br /> >> certain amount of multiple updatestaking place. I have actually <br /> >> been reworking clientside to get around multiple updates, since<br />>> they popped up in one of my profiling runs. Allthough the time I<br /> >> optimized away ended being both"roundtrip time" + "update time",<br /> >> but having the database do half of it transparently, might have<br />>> been sufficient to get me to have had a bigger problem elsewhere..<br /> >> <br /> >> To sum up. YesI think indeed it is a real-world case.<br /> >> <br /> >> Jesper<br /> > <br /> > On the Python side,elixir and sqlalchemy have an excellent way of<br /> > handling this, basically when you start a transaction, allchanges<br /> > are accumulated in a "session" object and only flushed to the<br /> > database on session commit(which is also generally the transaction<br /> > commit). This has multiple advantages, for instance it is ableto<br /> > issue multiple-line statements, updates are only done once, you save<br /> > a lot of roundtrips, etc.Of course it is most of the time not<br /> > compatible with database triggers, so if there are triggers the ORM<br/> > needs to be told about them.</span><br /><br /> How about unique constraints, foreign key violations and checks?Would <br /> you also pospone those errors to commit time? And transactions with lots of data? <br /><br /> It doesn'treally seem like a net benefit to me, but I can see applications <br /> where it easily will fit. <br /><br /> Jesper<br/>
On Monday 05 July 2010 12:11:38 Pierre C wrote: > > The problem can generally be written as "tuples seeing multiple > > updates in the same transaction"? > > > > I think that every time PostgreSQL is used with an ORM, there is > > a certain amount of multiple updates taking place. I have actually > > been reworking clientside to get around multiple updates, since they > > popped up in one of my profiling runs. Allthough the time I optimized > > away ended being both "roundtrip time" + "update time", but having > > the database do half of it transparently, might have been sufficient > > to get me to have had a bigger problem elsewhere.. > > > > To sum up. Yes I think indeed it is a real-world case. > > > > Jesper > > On the Python side, elixir and sqlalchemy have an excellent way of > handling this, basically when you start a transaction, all changes are > accumulated in a "session" object and only flushed to the database on > session commit (which is also generally the transaction commit). This has > multiple advantages, for instance it is able to issue multiple-line > statements, updates are only done once, you save a lot of roundtrips, etc. > Of course it is most of the time not compatible with database triggers, so > if there are triggers the ORM needs to be told about them. Its also not concurrency safe in many cases. Andres
On Sun, Jul 4, 2010 at 9:48 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Sure. What you'd need is for HeapTupleSatisfiesVacuum to observe that > (a) the tuple's xmin and xmax are equal, > (b) they're equal to my own transaction's XID, > (c) none of the live snapshots in my backend can see cmin but not cmax, > (d) cmax < currentCommandId, ensuring that every future snapshot will > see cmax too (not quite convinced this is certain to hold). [...] > Of course, you'd also need to get to HeapTupleSatisfiesVacuum in the > first place. The complained-of case lacks any VACUUM call. Maybe a HOT > cleanup would happen at the right time but I'm not sure. If it doesn't, > adding one would represent a significant expenditure that would usually > not be repaid. It looks like a HOT cleanup happens when pd_prune_xid falls behind OldestXmin. Normally, we set pd_prune_xid to the xmax of the deleted tuple, but we could perhaps fudge that here to get the desired behavior; maybe just set it to FrozenXID. Where it gets sticky is that the proposed rules for HeapTupleSatisfiesVacuum() give different answers depending on who does the vacuuming, so if backend A sets a hint say, hey, there's vacuumable stuff on this page, and then backend B tries to prune it, nothing will happen. What would be nicer is if there were a way for the updater to mark the item pointer or tuple in some way that would make it look vacuumable to everyone, but without breaking the HOT chain. > Another issue here is that since xmin is certainly within the GlobalXmin > horizon, it would be essential to preserve the update chain ctid links, > ie, make the tuple's update predecessor point to its successor. That > seems workable for the case of cleaning out an intermediate entry in a > HOT chain, but not otherwise. Yeah, that's a shame. HOT is huge, but it would be great if we had a way to do partial vacuuming even when the indexed columns are updated. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company