On Tue, Sep 17, 2013 at 9:54 AM, Eduardo Morras <emorrasg@yahoo.es> wrote:
> On Tue, 17 Sep 2013 09:19:29 -0700
> Lonni J Friedman <netllama@gmail.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.
No, it doesn't include a reindex. It merely invokes "vacuumdb --all
--analyze-only" with different values for default_statistics_target=1
-c vacuum_cost_delay=0.
According to the documentation for pg_upgrade, post-upgrade scripts to
rebuild tables and indexes will be generated automatically. Nothing
was generated for this purpose, at least not in any obvious place.
The analyze_new_cluster.sh script is the only one that was
automatically generated as far as I can tell.
> b) Did you configure postgresql.conf on 9.3.0 for your server/load? Perhaps it has default install values.
Yes, I'm using the same postgresql.conf as I was using when running
9.2.4. Its definitely not running with default install values.
> c) What does logs say?
The postgres server logs look perfectly normal, minus a non-trivial
slower run time for most queries. There's nothing unusual in any of
the OS level logs (/var/log/messages, etc) or dmesg.
Do you have any other suggestions?
