Re: high user cpu, massive SELECTs, no io waiting problem - Mailing list pgsql-performance

From Strange, John W
Subject Re: high user cpu, massive SELECTs, no io waiting problem
Date
Msg-id EF37296944B47C40ADDCCB7BFD6289FE048AEE31D7@EMASC201VS01.exchad.jpmchase.net
Whole thread Raw
In response to high user cpu, massive SELECTs, no io waiting problem  (Thomas Pöhler <tp@turtle-entertainment.de>)
List pgsql-performance

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@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Thomas Pöhler
Sent: 15 February 2011 17:19
To: pgsql-performance@postgresql.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.

pgsql-performance by date:

Previous
From: Steve Crawford
Date:
Subject: Re: pg_dumpall affecting performance
Next
From: "Kevin Grittner"
Date:
Subject: Re: pg_dumpall affecting performance