On Tue, Sep 17, 2013 at 9:54 AM, Eduardo Morras <emorrasg@xxxxxxxx> wrote: > On Tue, 17 Sep 2013 09:19:29 -0700 > Lonni J Friedman <netllama@xxxxxxxxx> 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? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
