[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