Обсуждение: Backends "idle in transaction"

[Apologies if you have seen this before. I just discovered that posting
to the group via NNTP, at Teranews.com, apparently isn't working since
my posts aren't showing up in the mailing list archives.]


Our system consists of a number of daemon processes running Perl code,
and using the DBI layer. Their normal mode of operation is to respond to
an external signal, run some transaction against the database, and then
go back to sleep (which might last seconds or hours).  This resulted in
a number of PG backend processes that spent most of their long lifetimes
in the "idle in transaction" state. I've been told that this is Not A
Good Thing, as it may cost resources.

I just spent some time rooting around in our Perl/DBI code, and managed
to change it so that it:

1) Creates its DBI handles with AutoCommit turned on.

2) Switches the handles to AutoCommit off when the daemon wakes up.

As a result, the system now has only one or two "idle in transaction"
connections, compared to the usual condition where it has several dozen.
(We have a "test" and "production" system, so I can observe this change
in parallel for a while if need be.)

What I'm hoping to find here is some way to measure just what this has
gained me. I can't measure exact performance between the two systems at
the application level, because due to external factors they aren't
always doing the same things at the same moments.

Could I glean some info from the postmaster log? For example, I think I
know that

 ReceiveSharedInvalidMessages: cache state reset

has something to do with this "idle in transaction" state. Could the
incidence of these be used as a measurement?

--
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise

Jeff Boes <jboes@nexcerpt.com> writes:
> Could I glean some info from the postmaster log? For example, I think I
> know that
>  ReceiveSharedInvalidMessages: cache state reset
> has something to do with this "idle in transaction" state. Could the
> incidence of these be used as a measurement?

Yeah, it could.  That particular symptom is relatively harmless though.
The real reason staying "idle in transaction" for long periods is evil
is that it prevents VACUUM from reclaiming deleted tuples (because they
are still live as far as the oldest open transaction is concerned, and
VACUUM has no way to know whether that transaction will choose to look
at any particular table).  So what you should really be looking at is
VACUUM VERBOSE output.

Here is a simple example (using CVS tip, but I believe 7.3 and 7.2
act the same):


regression=# create table foo (f1 serial, f2 int);
NOTICE:  CREATE TABLE will create implicit sequence 'foo_f1_seq' for SERIAL column 'foo.f1'
CREATE TABLE
regression=# insert into foo default values;
INSERT 154419 1
regression=# insert into foo default values;
INSERT 154420 1
regression=# insert into foo default values;
INSERT 154421 1
regression=# vacuum verbose foo;
INFO:  --Relation public.foo--
INFO:  Pages 1: Changed 1, Empty 0; Tup 3: Vac 0, Keep 0, UnUsed 0.
        Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

-- That says we have three tuples.  Duh.

regression=# update foo set f2 = 11;
UPDATE 3
regression=# vacuum verbose foo;
INFO:  --Relation public.foo--
INFO:  Removed 3 tuples in 1 pages.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Pages 1: Changed 1, Empty 0; Tup 3: Vac 3, Keep 0, UnUsed 0.
        Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

-- That says we have three live tuples left, and we reclaimed (vacuumed)
-- three dead ones, namely the old versions of the updated rows.

-- Now in another psql session I do
--    begin;
-- so that that guy is in "idle in transaction" state.

-- Now repeat the experiment:

regression=# update foo set f2 = 12;
UPDATE 3
regression=# vacuum verbose foo;
INFO:  --Relation public.foo--
INFO:  Pages 1: Changed 1, Empty 0; Tup 6: Vac 0, Keep 3, UnUsed 0.
        Total CPU 0.01s/0.00u sec elapsed 0.00 sec.
VACUUM
regression=#

-- See the "keep 3"?  VACUUM knows those tuples are committed dead,
-- but it has to keep them around because my other session can still
-- possibly see them.  So we've failed to reclaim any space.


So the short answer is you should see fewer "kept" tuples and more
effective re-use of space in vacuuming, now that you're not holding
transactions open.

            regards, tom lane