pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Greg Smith <greg@2ndquadrant.com>
To: Thomas Pöhler <tp@turtle-entertainment.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: high user cpu, massive SELECTs, no io waiting problem
Date: Wed, 16 Feb 2011 15:36:01 -0500
Message-ID: <4D5C3531.3070308@2ndquadrant.com> (raw)
In-Reply-To: <F1CD4AC5B4A5024AAB60E28A82A8450A03CEB2DA@winexch1.office.turtle-entertainment.de>
References: <F1CD4AC5B4A5024AAB60E28A82A8450A03CEB0C7@winexch1.office.turtle-entertainment.de>
	<4D5A6C60020000250003AA98@gw.wicourts.gov>
	<4D5B7DC2.6030807@2ndquadrant.com>
	<4D5B8CCC020000250003AAFD@gw.wicourts.gov>
	<4D5BE56B.4030307@2ndquadrant.com>
	<AANLkTi=mYVFE7QYfrTskUMuJXO34wbzY=1SNi+qgbUhf@mail.gmail.com>
	<4D5BA236020000250003AB0E@gw.wicourts.gov>
	<F1CD4AC5B4A5024AAB60E28A82A8450A03CEB2DA@winexch1.office.turtle-entertainment.de>

Thomas Pöhler wrote:
> We are running a biweekly downtime where we do a complete reindex and vaccum full. We cannot identify certain queries causing this.

If you feel that you need VACUUM FULL, either something terribly wrong 
has happened, or someone has gotten confused.  In both cases it's 
unlikely you want to keep doing that.  See 
http://wiki.postgresql.org/wiki/VACUUM_FULL for a nice document leading 
through figuring what to do instead.

Note that if you have a database that fits in RAM, but is filled with 
the sort of index bloat garbage that using VACUUM FULL will leave 
behind, it will cause excessive CPU use when running queries.  If you 
already have planned downtime, you really should try to use use CLUSTER 
instead, to remove that from the list of possible causes for your issue.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books




view thread (21+ messages)  latest in thread

Message-ID: <4D5C3531.3070308@2ndquadrant.com>
Permalink:  ../4D5C3531.3070308@2ndquadrant.com/
Also on:    postgresql.org/message-id/4D5C3531.3070308@2ndquadrant.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: greg@2ndquadrant.com, tp@turtle-entertainment.de
  Subject: Re: high user cpu, massive SELECTs, no io waiting problem
  In-Reply-To: <4D5C3531.3070308@2ndquadrant.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox