Обсуждение: Question about Idle in TX
I know that Idle in Transactions are a problem, however I'm trying to assess how much of a problem. for example: If a java program connects to the DB and does "begin;" and then internally does a "sleep 6 days" Does that cauz any issues other than eating a connection to the database? (note, nothing i have does this, i'm just trying to understand) I know that "Idle in TXs" can interfere with Vaccums for example, but I'm not sure if that's due to them usually having some form of lock on a table. Thanks, Dave
On 08/03/10 12:13 PM, David Kerr wrote: > I know that Idle in Transactions are a problem, however I'm trying to > assess how much of a problem. > > for example: If a java program connects to the DB and does "begin;" > and then internally does a "sleep 6 days" > > Does that cauz any issues other than eating a connection to the database? > > (note, nothing i have does this, i'm just trying to understand) > > I know that "Idle in TXs" can interfere with Vaccums for example, but > I'm not sure if that's due to them usually having some form of lock on a > table. no dead tuples created after the oldest active transaction (including said <Idle in Transaction>) can be vacuumed, from anywhere in the database. so, nothing deleted/updated since that BEGIN; you describe will get vacuumed. this will likely lead to a high amount of database bloat if you have a lot of update transactions. and when you finally terminate that idle transaction vacuum wil have a LOT of work to do.
David Kerr wrote: > I know that "Idle in TXs" can interfere with Vaccums for example, but > I'm not sure if that's due to them usually having some form of lock on a > table. > Locks aren't the issue. When you have a transaction open, the database makes sure it can deliver a consistent view of the database for the lifetime of that transaction, using MVCC: http://wiki.postgresql.org/wiki/MVCC What this means in practice is that VACUUM may stop cleaning up old data because your open transaction might still need to look at it. Table maintenance can grind to a halt when you have one of these long running transactions. Dead rows (ones left behind by DELETE or UPDATE) will stop being recycled, tables will grow, queries will slow down. If you're running 8.4 or later, there is a significant improvement to how pessimistic that gets in a typical case. To quote Alvaro, the author of that patch: "I expect to be able to remove dead rows created by transactions that are no longer in progress, but which started more recently than some currently-open long-running transaction." It's still something to be wary of. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
On Tue, Aug 03, 2010 at 03:30:46PM -0400, Greg Smith wrote: - David Kerr wrote: - >I know that "Idle in TXs" can interfere with Vaccums for example, but - >I'm not sure if that's due to them usually having some form of lock on a - >table. - > - - Locks aren't the issue. When you have a transaction open, the database - makes sure it can deliver a consistent view of the database for the Thanks guys! I thought that was the case but wanted to be 100% sure before i kicked up a fuss to my developers =) Dave
David Kerr <dmk@mr-paradox.net> writes:
> for example: If a java program connects to the DB and does "begin;"
> and then internally does a "sleep 6 days"
> Does that cauz any issues other than eating a connection to the database?
In recent versions of PG, no. Before about 8.3 it was a Really Bad Idea,
because the open transaction would prevent VACUUM from reclaiming storage.
It's *still* a Really Bad Idea to begin a transaction, do something,
and then sleep 6 days. But "BEGIN" without any following commands
has been fixed to be harmless, mainly because there are so many
badly designed clients that do exactly that ...
regards, tom lane
On Tue, Aug 03, 2010 at 03:49:57PM -0400, Tom Lane wrote: - David Kerr <dmk@mr-paradox.net> writes: - > for example: If a java program connects to the DB and does "begin;" - > and then internally does a "sleep 6 days" - - > Does that cauz any issues other than eating a connection to the database? - - In recent versions of PG, no. Before about 8.3 it was a Really Bad Idea, - because the open transaction would prevent VACUUM from reclaiming storage. - - It's *still* a Really Bad Idea to begin a transaction, do something, - and then sleep 6 days. But "BEGIN" without any following commands - has been fixed to be harmless, mainly because there are so many - badly designed clients that do exactly that ... - - regards, tom lane - ah ok, I think i'm in that group. We're using Talend and i think it might be doing that, and it might be a bit of an effort to stop that from happening. We're on 8.3.9, so hopefully it's fairly safe then? Thanks Dave
David Kerr <dmk@mr-paradox.net> writes:
> On Tue, Aug 03, 2010 at 03:49:57PM -0400, Tom Lane wrote:
> - In recent versions of PG, no. Before about 8.3 it was a Really Bad Idea,
> - because the open transaction would prevent VACUUM from reclaiming storage.
> We're on 8.3.9, so hopefully it's fairly safe then?
Should be. You might want to test it just to make sure I'm recalling
correctly when that got fixed. Do a BEGIN in one session, then in
another session insert and delete some rows in a table, then VACUUM
VERBOSE and see if they get cleaned up.
regards, tom lane
On Tue, Aug 03, 2010 at 03:57:27PM -0400, Tom Lane wrote: - David Kerr <dmk@mr-paradox.net> writes: - > On Tue, Aug 03, 2010 at 03:49:57PM -0400, Tom Lane wrote: - > - In recent versions of PG, no. Before about 8.3 it was a Really Bad Idea, - > - because the open transaction would prevent VACUUM from reclaiming storage. - - > We're on 8.3.9, so hopefully it's fairly safe then? - - Should be. You might want to test it just to make sure I'm recalling - correctly when that got fixed. Do a BEGIN in one session, then in - another session insert and delete some rows in a table, then VACUUM - VERBOSE and see if they get cleaned up. - - regards, tom lane - Ah yeah, good idea. I'll give it a shot. thanks! Dave
On 04/08/10 03:17, John R Pierce wrote: > On 08/03/10 12:13 PM, David Kerr wrote: >> I know that Idle in Transactions are a problem, however I'm trying to >> assess how much of a problem. >> >> for example: If a java program connects to the DB and does "begin;" >> and then internally does a "sleep 6 days" >> >> Does that cauz any issues other than eating a connection to the database? >> >> (note, nothing i have does this, i'm just trying to understand) >> >> I know that "Idle in TXs" can interfere with Vaccums for example, but >> I'm not sure if that's due to them usually having some form of lock on a >> table. > > no dead tuples created after the oldest active transaction (including > said <Idle in Transaction>) can be vacuumed, from anywhere in the database. Is that still true for READ COMMITTED transactions? Because it need not be. I seem to remember a previous discussion in which it emerged that as of 8.3 or 8.4 Pg is smart enough to realize that an open READ COMMITTED transaction can't ever refer to tuples from snapshots older than the currently running statement (if any), so it shouldn't impede vacuum. I can't seem to find any references for that, though. For that matter, a SERIALIZABLE transaction only acquires its snapshot on the first _real_ command (SELECT, etc) so it shouldn't impede VACUUM if it's just issued a BEGIN and a few SETs. However, I'm not totally sure it *doesn't* impede vacuum, it just doesn't have to. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/
On Tue, Aug 03, 2010 at 03:57:27PM -0400, Tom Lane wrote: - David Kerr <dmk@mr-paradox.net> writes: - > On Tue, Aug 03, 2010 at 03:49:57PM -0400, Tom Lane wrote: - > - In recent versions of PG, no. Before about 8.3 it was a Really Bad Idea, - > - because the open transaction would prevent VACUUM from reclaiming storage. - - > We're on 8.3.9, so hopefully it's fairly safe then? - - Should be. You might want to test it just to make sure I'm recalling - correctly when that got fixed. Do a BEGIN in one session, then in - another session insert and delete some rows in a table, then VACUUM - VERBOSE and see if they get cleaned up. - - regards, tom lane - Sorry for the delayed response, been in RedHat training all week. Seems like it worked fine in 9.0beta3: SESSION 1 psql test=> begin; BEGIN test=> SESSION 2 test=# insert into test (select generate_series(1001,1999,1)); INSERT 0 999 test=# delete from test; DELETE 1999 test=# VACUUM VERBOSE test; INFO: vacuuming "public.test" INFO: "test": removed 1999 row versions in 8 pages INFO: "test": found 1999 removable, 0 nonremovable row versions in 8 out of 8 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "test": truncated 8 to 0 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM