Message-ID: <4DFE1756.9060101@emolecules.com>
Date: Sun, 19 Jun 2011 08:35:50 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Who is causing all this i/o?
References: <4DD6D9A6.1010101@emolecules.com>
	<BANLkTikJ6xn-pKRhRA1HGiNotQt7N=1QDQ@mail.gmail.com>
	<4DD6FC11.4000706@emolecules.com> <1174.1305990672@sss.pgh.pa.us>
	<1308336703621-4499692.post@n5.nabble.com>
In-Reply-To: <1308336703621-4499692.post@n5.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 6/17/11 11:51 AM, Shianmiin wrote:
> Tom Lane-2 wrote:
>> What's not apparent however is why the stats collector is writing disk
>> so much.  8.4 does have the logic change to not write stats out unless
>> something is asking to see them.  So either it's really pre-8.4, or you
>> have a monitoring task that is constantly asking to see stats.
>>
> We have a PostgreSQL 9.0.4 on CentOs for performance testing and we are
> seeing the similar issue.
> we have a "crazy" setup it has 1 database with 1000 identical schemas. There
> are occasional I/O write storm
> of over 100 MB/sec without any disk reads, and it could last for a couple of
> minutes when the schemas/data are aggressively populated by pg_restore. All
> the io writes seem to be on pgstat.tmp.
>
> The I/O write storm seemed to be trigger by Vacuum.
Based on the advice I got from my original question, I changed autovacuum_naptime to "5min", and the problem completely disappeared.  (I know that's a long interval, but this particular server gets maybe 5-10 heavy updates per week and is idle the rest of the time.)

select count(1) from pg_database ;
  count
-------
    267

It seems like there's a problem somewhere.  Autovacuum has improved enormously in the last couple of years, but some change to its algorithm causes a lot of I/O thrashing when there are more than a few hundred separate databases.

Craig