Thread: Connection terminates but postmaster proc lives on...?

Connection terminates but postmaster proc lives on...?

From
"Jeff Madison"
Date:
I have seen several postings in the list archives regarding this exact
issue but have never seen any response or resolution.  We are running
pgsql 7.1.3 installed from the RedHat RPM on RedHat Linux 7.2.  We are
running a fairly large accounting application written in PERl using the
PERL DBI and DBD:Postgres on PERL 5.6.1.  What we are seeing is when
people terminate the application in the middle of a query many times
postmaster does not see the connection terminate or at least ignores it
and then proceeds to take a large amount of system resources and never
dies.  It only takes 5 or 6 of these postmaster processes to make the
system unusable since each one will take 25 to 35% of the processor
capacity of the system.  Eventually the system will just cease to
respond - after 18 to 24 hours.  This is a web based application so it
does happen very frequently where someone will get tired of waiting for
a page to return and just close the browser.  We have adjusted the
max_connection, shared_buffers, fsync=false and increased the amount of
shared memory in the kernel to 1GB.

Thanks,

Jeff


Re: Connection terminates but postmaster proc lives on...?

From
Tom Lane
Date:
"Jeff Madison" <jeff@ikano.com> writes:
> ...  What we are seeing is when
> people terminate the application in the middle of a query many times
> postmaster does not see the connection terminate or at least ignores it
> and then proceeds to take a large amount of system resources and never
> dies.

As a general rule, the backend will not notice a connection closure
until it next tries to read from the connection.  If you issue some
query that takes a long time to compute, and then give up and abort
your client, the query will still run.

If you're not sure what query you are running, you could attach to
one of the CPU-hogging backends with gdb and issue "p debug_query_string"
to see what it's working on.

            regards, tom lane