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

Thanks Thomas,

>
> 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?
>
>
Things seem to have been running better on 9.2 at this point I'm using the
same config file from 9.2 and I'm still experiencing slowness under heavier
write access. And my disk subsystem has not changed. Hardware has not
changed, heck i'm even running the old version of slony (have not upgraded
it yet).

But since the upgrade to 9.3 even calls to my sl_log tables which are tiny
can take:

2014-11-04 02:58:40 PST clsdb postgres 10.13.200.242(52022) 21642
2014-11-04 02:58:40.515 PSTLOG: duration: 1627.019 ms statement: fetch
500 from LOG; (log had 145K items).

> 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.
>
>
This is a production server, but it was not really CPU bound with 9.2 so
something is odd and I'm starting to stress, because it is a production
environment :)

Connections correct, I have less than 20 or so active requests at a time,
but i would say active queries are in the handful. I was was not seeing IO,
but was seeing load increase as queries started taking longer, but nothing
in iostat or vmstat/free showed any contention. Heck even Top while showed
some cores as busy, nothing was sitting at over 60% utilized. And we are
talking a load of 12-14 here on a 32 core system, when it's normally asleep!

This is my master slon insert server, so I can run commands, tweak configs
but any type of rebuild or restart of postgres is a scheduled affair.

These work loads that seem to be creating the issues run between midnight
and now almost 6am, prior to 9.3 it was taking maybe 4 hours, now it's
taking 6. So tomorrow AM , I'll grab some stats when I see that it's
struggling.

But even now with almost no connections or really any major access i'm
seeing the sl_log grab 500 rows take 1-3 seconds, which is just plain silly
(but it's not a constant, so I may see 1 of these alerts every hour)

>
> > 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?
>

6.5 yes sir.. 2.6.32-431.5.1.el6.x86_64

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

ya nothing here. No difference from the 9.2 to 9.3 roll. My sysctl.conf is
pretty boring.

Also, what package is this? Is it coming from the CentOS repository,
> yum.postgresql.org or some other repository?
>
>
It's a self spun RPM. follows the same procedures since earlier 7.x, with
adding required includes as it went along. We spin this rpm with our slon
package together.

Thanks Tomas

Tory Blue