Re: upgrade from 9.2.x to 9.3 causes significant performance degradation

On Tue, 17 Sep 2013 09:19:29 -0700
Lonni J Friedman <netllama(at)gmail(dot)com> wrote:

> Greetings,
> I'm running a PostgreSQL 9.3.0 cluster (1 master with two streaming
> replication hot standby slaves) on RHEL6-x86_64. Yesterday I upgraded
> from 9.2.4 to 9.3.0, and since the upgrade I'm seeing a significant
> performance degradation. PostgreSQL simply feels slower. Nothing
> other than the version of PostgreSQL changed yesterday. I used
> pg_upgrade to perform the upgrade, and ran the generated
> analyze_new_cluster.sh immediately afterwards, which completed
> successfully.
>
> Prior to the upgrade, I'd generally expect a load average of less than
> 2.00 on the master, and less than 1.00 on each of the slaves. Since
> the upgrade, the load average on the master has been in double digits
> (hitting 100.00 for a few minutes), and the slaves are consistently
> above 5.00.
>
> There are a few things that are jumping out at me as behaving
> differently since the upgrade. vmstat processes waiting for runtime
> counts have increased dramatically. Prior to the upgrade the process
> count would be consistently less than 10, however since upgrading it
> hovers between 40 & 60 at all times. /proc/interrupts "Local timer
> interrupts" has increased dramatically as well. It used to hover
> around 6000 and is now over 20k much of the time. However, I'm
> starting to suspect that they are both symptoms of the problem rather
> than the cause.
>
> At this point, I'm looking for guidance on how to debug this problem
> more effectively.

Don't know what happens but:

a) Does analyze_new_cluster.sh include a reindex? If not, indexs are useless because analyze statistics says so.
b) Did you configure postgresql.conf on 9.3.0 for your server/load? Perhaps it has default install values.
c) What does logs say?

> thanks
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--- ---
Eduardo Morras <emorrasg(at)yahoo(dot)es>