Re: How to investiage slow insert problem

On Monday, August 19, 2013, Rural Hunter wrote:

> Hi,
>
> I'm on 9.2.4 with Ubuntu server. There are usually hundereds of
> connections doing the same insert with different data from different
> networks every minute, through pgbouncer in the same network of the
> database server. The database has been running for about one year without
> problem. Yesterday I got a problem that the connection count limit of the
> database server is reached.

I think that this should generally not happen at the server if you are
using pgbouncer, as you should configure it so that pgbouncer has a lower
limit than postgresql itself does. What pooling method (session,
transaction, statement) are you using?

> I checked the connections and found that there are many inserts hanging
> there. I checked the load(cpu,memory,io) of the db server but seems
> everything is fine.

Can you provide some example numbers for the io load?

> I also checked pg log and I only found there are one "incomplete message
> from client" error message every several minute.

Could you post the complete log message and a few lines of context around
it?

> The I recycled pgbouncer and kept monitoring the connections. I found the
> majority of the inserts finish quickly but every minute there are several
> inserts left and seems hanging there .

How long had they been hanging there? It makes a big difference whether
there are several hanging there at one moment, but a few milliseconds later
there are several different ones, versus the same few that hang around of
many seconds or minutes at a time.

...

From the error message in pg log, I supect it might be the network problem
> from some clients. Could anyone point out if there are other possible
> causes?

If the identities of the "hung" processes are rapidly changing, it could
just be that you are hitting a throughput limit. When you do a lot of
inserts into indexed the tables, the performance can drop precipitously
once the size of the actively updated part of the indexes exceeds
shared_buffers. This would usually show up in the io stats, but if you
always have a lot of io going on, it might not be obvious.

If it is the same few processes hung for long periods, I would strace them,
or gdb them and get a backtrace.

> I'm also wondering what those inserts are doing actually when they are
> hanging there, such as if they are in the trigger or not. Anything I can
> get similar with the connection snapshots in db2?
>

Sorry, I don't know what a connection snapshot in db2 looks like.

Cheers,

Jeff