Re: High CPU usage / load average after upgrading to Ubuntu 12.04 - Mailing list pgsql-performance

Thanks for the info.
Our application does have a lot of concurrency.  We checked the zone reclaim parameter and it is turn off (that was the
default,we did not have to change it). 

Dan

-----Original Message-----
From: Merlin Moncure [mailto:mmoncure@gmail.com]
Sent: Thursday, February 14, 2013 9:08 AM
To: Dan Kogan
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On Tue, Feb 12, 2013 at 11:25 AM, Dan Kogan <dan@iqtell.com> wrote:
> Hello,
>
>
>
> We upgraded from Ubuntu 11.04 to Ubuntu 12.04 and almost immediately
> obeserved increased CPU usage and significantly higher load average on
> our database server.
>
> At the time we were on Postgres 9.0.5.  We decided to upgrade to
> Postgres
> 9.2 to see if that resolves the issue, but unfortunately it did not.
>
>
>
> Just for illustration purposes, below are a few links to cpu and load
> graphs pre and post upgrade.
>
>
>
> https://s3.amazonaws.com/iqtell.ops/Load+Average+Post+Upgrade.png
>
> https://s3.amazonaws.com/iqtell.ops/Load+Average+Pre+Upgrade.png
>
>
>
> https://s3.amazonaws.com/iqtell.ops/Server+CPU+Post+Upgrade.png
>
> https://s3.amazonaws.com/iqtell.ops/Server+CPU+Pre+Upgrade.png
>
>
>
> We also tried tweaking kernel parameters as mentioned here -
> http://www.postgresql.org/message-id/50E4AAB1.9040902@optionshouse.com
> , but have not seen any improvement.
>
>
>
>
>
> Any advice on how to trace what could be causing the change in CPU
> usage and load average is appreciated.
>
>
>
> Our postgres version is:
>
>
>
> PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc
> (Ubuntu/Linaro
> 4.6.3-1ubuntu5) 4.6.3, 64-bit
>
>
>
> OS:
>
>
>
> Linux ip-10-189-175-25 3.2.0-37-virtual #58-Ubuntu SMP Thu Jan 24
> 15:48:03 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
>
>
>
> Hardware (this an Amazon Ec2 High memory quadruple extra large instance):
>
>
>
> 8 core Intel(R) Xeon(R) CPU E5-2665 0 @ 2.40GHz
>
> 68 GB RAM
>
> RAID10 with 8 drives using xfs
>
> Drives are EBS with provisioned IOPS, with 1000 iops each
>
>
>
> Postgres Configuration:
>
>
>
> archive_command = rsync -a %p
> slave:/var/lib/postgresql/replication_load/%f
>
> archive_mode = on
>
> checkpoint_completion_target = 0.9
>
> checkpoint_segments = 64
>
> checkpoint_timeout = 30min
>
> default_text_search_config = pg_catalog.english
>
> external_pid_file = /var/run/postgresql/9.2-main.pid
>
> lc_messages = en_US.UTF-8
>
> lc_monetary = en_US.UTF-8
>
> lc_numeric = en_US.UTF-8
>
> lc_time = en_US.UTF-8
>
> listen_addresses = *
>
> log_checkpoints=on
>
> log_destination=stderr
>
> log_line_prefix = %t [%p]: [%l-1]
>
> log_min_duration_statement =500
>
> max_connections=300
>
> max_stack_depth=2MB
>
> max_wal_senders=5
>
> shared_buffers=4GB
>
> synchronous_commit=off
>
> unix_socket_directory=/var/run/postgresql
>
> wal_keep_segments=128
>
> wal_level=hot_standby
>
> work_mem=8MB

does your application have a lot of concurrency?  history has shown that postgres is highly sensitive to changes in the
o/sscheduler (which changes a lot from release to release). 

also check this:
zone reclaim (http://frosty-postgres.blogspot.com/2012/08/postgresql-numa-and-zone-reclaim-mode.html)

merlin