Message-ID: <4DD6F45E.6000206@pinpointresearch.com>
Date: Fri, 20 May 2011 16:08:14 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.14) Gecko/20110223 Lightning/1.0b2 Thunderbird/3.1.8
MIME-Version: 1.0
To: Craig James <craig_james@emolecules.com>
CC: pgsql-admin@postgresql.org
Subject: Re: Who is causing all this i/o?
References: <4DD6D9A6.1010101@emolecules.com>
In-Reply-To: <4DD6D9A6.1010101@emolecules.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 05/20/2011 02:14 PM, Craig James wrote:
> Our development server (PG 8.4.4 on Ubuntu server) is constantly doing 
> something, and I can't figure out what.  The two production servers, 
> which are essentially identical, don't show these symptoms.  In a 
> nutshell, it's showing 10K blocks per second of data going out, all 
> the time, and essentially zero blocks per second of input.
...
There are people on this list better equipped than I to get into the 
guts of diagnosis, but a quick Google shows you aren't the only person 
to see high IO with disk activity. There are suggestions that a db with 
lots of temp-table creation and nothing triggering a vacuum could be 
related. Another thread suggested that bloated system tables could be to 
blame. Others look at the stats collectors. Groping a bit in the dark, I 
would try...

Checking PG logs for info - especially anything indicating that vacuum 
isn't running or isn't being triggered.

Manual vacuum.

Vacuum full of system tables (pg_catalog - especially pg_class and 
pg_attribute) - possibly followed by a reindex all followed by a manual 
vacuum. Note: IIRC, that file is shared across databases so you will 
need to clean each one.

Reset stats collector at startup.

Check system after each change and report back if anything improves or 
degrades the situation.

Cheers,
Steve