Re: Performance issue after upgrading from 9.4 to 9.6

2017-03-09 18:28 GMT+01:00 Robert Haas <robertmhaas(at)gmail(dot)com>:

> On Thu, Mar 9, 2017 at 7:47 AM, Naytro Naytro <naytro(at)googlemail(dot)com>
> wrote:
> > We are having some performance issues after we upgraded to newest
> > version of PostgreSQL, before it everything was fast and smooth.
> >
> > Upgrade was done by pg_upgrade from 9.4 directly do 9.6.1. Now we
> > upgraded to 9.6.2 with no improvement.
> >
> > Some information about our setup: Freebsd, Solaris (SmartOS), simple
> > master-slave using streaming replication.
> >
> > Problem:
> > Very high system CPU when master is streaming replication data, CPU
> > goes up to 77%. Only one process is generating this load, it's a
> > postgresql startup process. When I attached a truss to this process I
> > saw a lot o read calls with almost the same number of errors (EAGAIN).
> > read(6,0x7fffffffa0c7,1) ERR#35 'Resource temporarily unavailable'
> >
> > Descriptor 6 is a pipe
> >
> > Read call try to read one byte over and over, I looked up to source
> > code and I think this file is responsible for this behavior
> > src/backend/storage/ipc/latch.c. There was no such file in 9.4.
>
> Our latch implementation did get overhauled pretty thoroughly in 9.6;
> see primarily commit 98a64d0bd713cb89e61bef6432befc4b7b5da59e. But I
> can't guess what is going wrong here based on this information. It
> might help if you can pull some stack backtraces from the startup
> process.

Dtrace from solaris: http://pastebin.com/u03uVKbr