On 07/27/2015 08:42 AM, Herouth Maoz wrote:
On 27 ביולי 2015, at 18:01, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
Not sure what yo have set up for logging, but you might to crank it up. 13 days
between entries for a system that is in use all the time seems sort of light to
me.
Most of the log settings are just the Debian default (except the log prefix).
This system is one of several we have, but it’s intended for quick processing
of messages by some of our customers that send low volumes but need quick
response. As such, it processes around 50,000 messages a day, which boils down
to around 200,000 or so database requests a day. It’s very light load, so we
don’t get many timeouts or other load-related log entries. I don’t want to log
each query, though, as that seems wasteful to me.
I am not sure that logging every query is necessary. I was thinking more
on the lines of connect/disconnect, more info in the log line prefix,
moving the log_min_messages down. Anything to provide a little more
context. It seems this actual shut down is coming from an external
source, but it would be helpful to see if there was a correlation with
what is happening in the database and use that to get to causation.
NEW INFORMATION: I believe that today’s mishap is related to the July 21
shutdown problem, based on the logs of our Java program and an inquiry into the
way it handles messages that go through a particular path. It seems that one of
the modules has a bug and retains stale connections, and since messages that
arrive through that path are rare, the first time one came after the July 21st
shutdown was today.
So what is the module doing?
Or more the point what is the application doing to deal with the stale
connections?
The module keeps a connection pool available to the threads that call it, one
connection per thread. It runs a JDBC prepared statement. If that prepared
statement hits an SQLException, it then logs it in two places and attempts to
recreate the connection, which is where the bug is. But that bug is just the
reason why I didn’t get a fresh connection afterwards, it’s not the reason why
the SQLException happened to begin with (which is that the connection kept for
this particular thread was from before the shut down).
So how are the stale connections dealt with?
I hope this information helps,
Herouth
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general