Re: 9.3 performance issues, lots of bind and parse log entries

Tory M Blue <tmblue@xxxxxxxxx> · Tue, 4 Nov 2014 12:07:33 -0800

On Tue, Nov 4, 2014 at 9:01 AM, Tory M Blue <tmblue@xxxxxxxxx> wrote:
Not sure what is going on but other than upgrading to 9.3.4 from 9.2.4, i'm seeing major slowness in basic queries and seeing a ton of the bind and parse in my logs. These are standard lookups and should take micro seconds. I'm logging all queries that take over a second and this seems to be getting worse, seems like it's snowballing.

2014-11-04 08:54:52 PST clsdb cls 216.0.0.50(33569) 14857 2014-11-04 08:54:52.476 PSTLOG:  duration: 2206.070 ms  parse dbdpg_p12768_2: SELECT contact_seq_id, status FROM cls.contacts
					WHERE
						cust_seq_id = $1
						AND contact_id = $2

2014-11-04 08:54:52 PST clsdb cls 216.20.0.50(48450) 14882 2014-11-04 08:54:52.394 PSTLOG:  duration: 1624.847 ms  bind dbdpg_p21610_2: SELECT contact_seq_id, status FROM cls.contacts
					WHERE
						cust_seq_id = $1
						AND contact_id = $2

CentOS 6.x
Postgres:  9.3.4
256GB Mem
32Core

I'm tearing up my system to see what is happening, the postgres is the only change, the processes or the data is not. I tried tuning postgresql.conf and modifed effective cache etc, but I've reverted it all, so again just looking for some assistance.

Again no I/O have plenty of memory etc. The server is running hotter than it used to as well.

Thanks
Tory

Well after fighting this all day and dealing with a really sluggish db where even my slon processes were taking several seconds, I reduced my shared_buffers back to 2GB from 10GB and my work_mem from 7.5GB to 2GB. i actually undid all my changes, including dropping my effective_cache back to 7GB  and restarted.

I have 300 connections configured, we will use around 87 normally with some spikes, but I'm wondering if the 10GB shared memory caused me some grief, I don't believe it was the work_mem and don't believe it was the effective cache, but something caused my DB to run into issues with basic queries, same queries after restart are finishing in milliseconds instead of 2-3 seconds. No disk issues seen,.

So if this is not a 9.3 issue, it's an issue with me upping my config params to a level I thought would give a nice bump..

CentOS 6.x
Postgres:  9.3.4
256GB Mem
32Core