Thread: Speed while runnning large transactions.
Hi. I have a transaction running at the database for around 20 hours .. still isn't done. But during the last hours it has come to the point where it really hurts performance of "other queries". Given pg_stat_activity output there seems to be no locks interfering but the overall cpu-usage of all queries continue to rise. iowait numbers are also very low. What can I do to make the system handle other queries better? PG: 8.2 -- Jesper
> I have a transaction running at the database for around 20 hours .. still > isn't done. But during the last hours it has come to the point where it > really hurts performance of "other queries". > > Given pg_stat_activity output there seems to be no locks interfering but > the overall cpu-usage of all queries continue to rise. iowait numbers are > also very low. > > What can I do to make the system handle other queries better? Can you post the query? Do you 'vacuum analyze' on a regular basis? You can also post your conf-file and post the last five lines from a 'vacuum analyze verbose'. -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare
On Thu, Sep 24, 2009 at 9:27 AM, <jesper@krogh.cc> wrote:
Hi.
I have a transaction running at the database for around 20 hours .. still
isn't done. But during the last hours it has come to the point where it
really hurts performance of "other queries".
Given pg_stat_activity output there seems to be no locks interfering but
the overall cpu-usage of all queries continue to rise. iowait numbers are
also very low.
What can I do to make the system handle other queries better?
show us explain from the query(s).
use select * from pg_stat_activity to find out the state query is in, and perhaps which one of the queries it really is.
use select * from pg_stat_activity to find out the state query is in, and perhaps which one of the queries it really is.
--
GJ
On Thu, Sep 24, 2009 at 2:27 AM, <jesper@krogh.cc> wrote: > Hi. > > I have a transaction running at the database for around 20 hours .. still > isn't done. But during the last hours it has come to the point where it > really hurts performance of "other queries". What is your transaction doing during this time? > Given pg_stat_activity output there seems to be no locks interfering but > the overall cpu-usage of all queries continue to rise. iowait numbers are > also very low. What does select count(*) from pg_stat_activity where waiting; say? > What can I do to make the system handle other queries better? Really kinda depends on what your transaction is doing.
> On Thu, Sep 24, 2009 at 2:27 AM, <jesper@krogh.cc> wrote: >> Hi. >> >> I have a transaction running at the database for around 20 hours .. >> still >> isn't done. But during the last hours it has come to the point where it >> really hurts performance of "other queries". > > What is your transaction doing during this time? It is a massive DB-update affecting probably 1.000.000 records with a lot of roundtrips to the update-application during that. >> Given pg_stat_activity output there seems to be no locks interfering but >> the overall cpu-usage of all queries continue to rise. iowait numbers >> are >> also very low. > > What does > select count(*) from pg_stat_activity where waiting; > say? There is no "particular query". No indication of locks it just seems that having the transaction open (with a lot of changes hold in it) has an impact on the general performance. Even without touching the same records. >> What can I do to make the system handle other queries better? > > Really kinda depends on what your transaction is doing. insert's, updates, delete.. -- Jesper
> On Thu, Sep 24, 2009 at 9:27 AM, <jesper@krogh.cc> wrote: > >> Hi. >> >> I have a transaction running at the database for around 20 hours .. >> still >> isn't done. But during the last hours it has come to the point where it >> really hurts performance of "other queries". >> >> Given pg_stat_activity output there seems to be no locks interfering but >> the overall cpu-usage of all queries continue to rise. iowait numbers >> are >> also very low. >> >> What can I do to make the system handle other queries better? >> >> show us explain from the query(s). > use select * from pg_stat_activity to find out the state query is in, and > perhaps which one of the queries it really is. I'm actively monitoring pg_stat_activity for potential problems but the thread is spending most of the time in the application side. The transaction is holding a large set of inserts/update and delete for the DB. -- Jesper
2009/9/24 <jesper@krogh.cc>: >> On Thu, Sep 24, 2009 at 9:27 AM, <jesper@krogh.cc> wrote: >> >>> Hi. >>> >>> I have a transaction running at the database for around 20 hours .. >>> still >>> isn't done. But during the last hours it has come to the point where it >>> really hurts performance of "other queries". >>> >>> Given pg_stat_activity output there seems to be no locks interfering but >>> the overall cpu-usage of all queries continue to rise. iowait numbers >>> are >>> also very low. >>> >>> What can I do to make the system handle other queries better? >>> >>> show us explain from the query(s). >> use select * from pg_stat_activity to find out the state query is in, and >> perhaps which one of the queries it really is. > > I'm actively monitoring pg_stat_activity for potential problems but the > thread is spending most of the time in the application side. The > transaction is holding a large set of inserts/update and delete for the > DB. I don't think there's much you can do about this. Your other transactions are probably slowing down due to accumulation of dead row versions that VACUUM can't collect because they are still visible to your long-running transaction. You might need to think about replicating some of your data to a reporting server. ...Robert
On Thu, 24 Sep 2009, jesper@krogh.cc wrote: > I have a transaction running at the database for around 20 hours .. still > isn't done. But during the last hours it has come to the point where it > really hurts performance of "other queries". Open transactions grab an internal resource named a snapshot that lets them keep a consistent view of the database while running. If the transaction runs for a long time, that snapshot gets further and further behind, and it takes increasingly long to do some operations as a result. One common problem is that VACUUM can't do its normal cleanup for things that happened since the long running transaction began. I'm not aware of any good way to monitor or quanitify how bad snapshot related debris is accumulating, that's actually something I'd like to add more visibility to one day. About all you can do is note the old transaction in pg_stat_activity and presume it's potential impact increases the longer the transaction is open. There are only two good solutions here: 1) Rearchitect the app with the understanding that this problem exists and there's no easy way around it, breaking commits into smaller pieces. 2) Test if an upgrade to PG 8.4 improves your situation. There is some new code in that version (labeled in the release notes as "Track transaction snapshots more carefully") that has improved problems in this area quite a bit for me. There's a bit more detail about the change at http://archives.postgresql.org/pgsql-committers/2008-05/msg00220.php , all of the other descriptions I found of it require a lot of internals knowledge to read. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Greg Smith <gsmith@gregsmith.com> writes: > 2) Test if an upgrade to PG 8.4 improves your situation. There is some > new code in that version (labeled in the release notes as "Track > transaction snapshots more carefully") that has improved problems in this > area quite a bit for me. There's a bit more detail about the change at > http://archives.postgresql.org/pgsql-committers/2008-05/msg00220.php , all > of the other descriptions I found of it require a lot of internals > knowledge to read. It's not really that complex. Pre-8.4, VACUUM would always assume that every transaction still needed to be able to access now-dead rows that were live as of the transaction's start. So rows deleted since the start of your oldest transaction couldn't be recycled. As of 8.4, the typical case is that an open transaction blocks deletion of rows that were deleted since the transaction's current *statement* started. So this makes a huge difference if you have long-running transactions that consist of a series of not-so-long statements. It also means that transactions that sit "idle in transaction" are not a hazard for VACUUM anymore --- an idle transaction doesn't block deletion of anything. The hopefully-not-typical cases where we don't do this are: 1. A transaction executing in SERIALIZABLE mode still has the old behavior, because it uses its first snapshot throughout the transaction. 2. DECLARE CURSOR captures a snapshot, so it will block VACUUM as long as the cursor is open. (Or at least it's supposed to ... given discussion yesterday I fear this may be broken in 8.4 :-() regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote: > As of 8.4, the typical case is that an open transaction blocks > deletion of rows that were deleted since the transaction's current > *statement* started. So this makes a huge difference if you have > long-running transactions that consist of a series of not-so-long > statements. It also means that transactions that sit "idle in > transaction" are not a hazard for VACUUM anymore --- an idle > transaction doesn't block deletion of anything. Surely the original version of a row updated or deleted by the long-running transaction must be left until the long-running transaction completes; otherwise, how does ROLLBACK work? (The OP did mention that there were a large number of updates and deletes being performed by the long-running transaction....) -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> As of 8.4, the typical case is that an open transaction blocks
>> deletion of rows that were deleted since the transaction's current
>> *statement* started.
[ BTW, of course that should have read "blocks removal of" ... ]
> Surely the original version of a row updated or deleted by the
> long-running transaction must be left until the long-running
> transaction completes; otherwise, how does ROLLBACK work?
Right. What I was talking about was the impact of a long-running
transaction on the removal of rows outdated by *other* transactions.
The people who hollered loudest about this seemed to often have
long-running read-only transactions in parallel with lots of short
read-write transactions. That's the pattern that 8.4 can help with
anyway.
regards, tom lane
On Fri, 2 Oct 2009, Tom Lane wrote: > The people who hollered loudest about this seemed to often have > long-running read-only transactions in parallel with lots of short > read-write transactions. Which makes sense if you think about it. Long-running read-only reports are quite common in DBA land. I'm sure most people can think of an example in businesses they work with that you can't refactor away into smaller chunks, everybody seems to have their own variation on the big overnight report. Long-running read-write transactions are much less common, and a bit more likely to break into logical chunks if you architect the design right, using techniques like staging areas for bulk operations and write barriers for when they can be applied. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD