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

On Tue, Nov 4, 2014 at 9:01 AM, Tory M Blue <tmblue(at)gmail(dot)com> 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