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

Hi Tory,

On 4.11.2014 21:07, Tory M Blue wrote:
> 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.

Have you been using the same parameter values on 9.2, or have you bumped
them up only on the new 9.3? I'm wondering whether 9.2 was performing
better with the values?

> 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,.

I assume only some of the connections will be active (running queries)
at the same time. If you expect >> 32 active queries at the same time,
you're only increasing latency.

Based on your description I assume you're CPU bound (otherwise the
machine would not get "hotter", and planning is not about I/O).

I'm not sure if this is a production machine or how much you can
experiment with it, but it'd be helpful if you could provide some
profiling information

$ iostat -x -k 1
$ vmstat 1

and such data. A perf profile would be even better, but to get the most
useful info it may be necessary to recompile the postgres with debug
info and '-fno-omit-frame-pointer'. Then this should do the trick:

perf record -a -g (for a few seconds, then Ctrl-C)
perf report

or just "perf top" to see what functions are at the top.

> 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

What kernel version are you using? I assume 6.x means 6.5, or are you
using an older CentOS version?

Are you using transparent huge pages, NUMA or similar features?
Althought, that'd probably impact 9.2 too.

Also, what package is this? Is it coming from the CentOS repository,
yum.postgresql.org or some other repository?

regards
Tomas