Re: SAVEPOINTs and COMMIT performance
От | Simon Riggs |
---|---|
Тема | Re: SAVEPOINTs and COMMIT performance |
Дата | |
Msg-id | 1297026570.1770.2730.camel@ebony обсуждение исходный текст |
Ответ на | Re: SAVEPOINTs and COMMIT performance (Bruce Momjian <bruce@momjian.us>) |
Ответы |
Re: SAVEPOINTs and COMMIT performance
Re: SAVEPOINTs and COMMIT performance |
Список | pgsql-hackers |
On Sun, 2011-02-06 at 12:11 -0500, Bruce Momjian wrote: > Did this ever get addressed? Patch attached. Seems like the easiest fix I can come up with. > Simon Riggs wrote: > > > > As part of a performance investigation for a customer I've noticed an > > O(N^2) performance issue on COMMITs of transactions that contain many > > SAVEPOINTs. I've consistently measured COMMIT times of around 9 seconds, > > with 49% CPU, mostly in LockReassignCurrentOwner(). > > > > BEGIN; > > INSERT... > > SAVEPOINT ... > > INSERT... > > SAVEPOINT ... > > ... (repeat 10,000 times) > > COMMIT; > > > > The way SAVEPOINTs work is that each is nested within the previous one, > > so that at COMMIT time we must recursively commit all the > > subtransactions before we issue final commit. > > > > That's a shame because ResourceOwnerReleaseInternal() contains an > > optimisation to speed up final commit, by calling ProcReleaseLocks(). > > > > What we actually do is recursively call LockReassignCurrentOwner() which > > sequentially scans LockMethodLocalHash at each level of transaction. The > > comments refer to this as "retail" rather than the wholesale method, > > which never gets to execute anything worthwhile in this case. > > > > This issue does NOT occur in PLpgSQL functions that contain many > > EXCEPTION clauses in a loop, since in that case the subtransactions are > > started and committed from the top level so that the subxact nesting > > never goes too deep. > > > > Fix looks like we need special handling for the depth-first case, rather > > than just a recursion loop in CommitTransactionCommand(). > > > > Issues looks like it goes all the way back, no fix for 9.0. > > > > I notice also that the nesting model of SAVEPOINTs also means that > > read-only subtransactions will still generate an xid when followed by a > > DML statement. That's unnecessary, but required given current design. > > > > -- > > Simon Riggs www.2ndQuadrant.com > > PostgreSQL Development, 24x7 Support, Training and Services > > > > > > -- > > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-hackers > -- Simon Riggs http://www.2ndQuadrant.com/books/ PostgreSQL Development, 24x7 Support, Training and Services
Вложения
В списке pgsql-hackers по дате отправления: