Re: high user cpu, massive SELECTs, no io waiting problem

You have also run analyze verbose, and checked to make sure you don't have a ton of bloated indexes?

- check the process with strace -p PID
- check the diskIO with iostat, not vmstat
- run analyze verbose, and possible reindex the database, or cluster the larger tables.
- dump from pg_stat_activity, and check what the largest objects are based on relpages from pg_class.
- check index scans/table scans from pg_statio tables if you have track_activities on in the .conf file.

- John

From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Thomas Pöhler
Sent: 15 February 2011 17:19
To: pgsql-performance(at)postgresql(dot)org
Cc: Felix Feinhals; Verteiler_A-Team; Björn Metzdorf
Subject: [PERFORM] high user cpu, massive SELECTs, no io waiting problem

Hi list,

first time for me here, hope you're not dealing too severely with me regarding guidelines. Giving my best.

We are running PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Debian 4.3.2-1.1) 4.3.2, 64-bit on a Supermicro SuperServer 8026B-6RF.
This version is downloaded from postgresql.org and selfcompiled, running for over a year now. The Server has 128 GB RAM and Four Intel® Xeon® X7550 with 64 logical cores.
Operating System is "Linux database1 2.6.32-bpo.5-amd64 #1 SMP Mon Dec 13 17:10:39 UTC 2010 x86_64 GNU/Linux".

The System boots through iscsi over a Qlogic QLE4062C HBA. Pgdata and xlog is logged in over iscsi HBA too. We tried en and disabling jumbo frames. Makes no difference.
We are using a DELL Equallogic SAN Backend with SAS drives.

Postgres is used as backend for a high performance website. We are using nginx with php-fastcgi and memcached.

Since a few weeks we have really strange peaks on this system. User CPU is increasing up to 100% and we have lots of SELECTs running.
There is no iowait at this time, only high user cpu and we don't know where this is coming from. It seems like this is only happening under certain circumstances.

We can solve this problem by simply removing the load from the website by delivering an offline page. We let database calm down for a while and then slowly throttling users.

See ganglia: http://dl.dropbox.com/u/183323/CPUloadprobsdb1.jpg

Has someone made similar experiences? Perhaps there is some issue between Postgres 8.4.4 and kernel 2.6.32?

Thank in advance
Thomas

--
Turtle Entertainment GmbH
Thomas Pöhler, Manager IT Operations
Siegburger Str. 189
50679 Cologne
Germany
fon. +49 221 880449-331
fax. +49 221 880449-399
http://www.turtle-entertainment.com/
http://www.esl.eu/
http://www.consoles.net/
Managing Director: Ralf Reichert
Register Court: Local Court Cologne, HRB 36678

This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
and affiliates.

This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and any
attachments are believed to be free of any virus or other defect
that might affect any computer system into which it is received and
opened, it is the responsibility of the recipient to ensure that it
is virus free and no responsibility is accepted by JPMorgan Chase &
Co., its subsidiaries and affiliates, as applicable, for any loss
or damage arising in any way from its use. If you received this
transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard
copy format. Thank you.

Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to European legal entities.