From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig James <craig_james@emolecules.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Who is causing all this i/o?
Date: Sun, 19 Jun 2011 13:08:30 -0400
Message-ID: <16478.1308503310@sss.pgh.pa.us> (raw)
In-Reply-To: <4DFE1756.9060101@emolecules.com>
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>
<4DFE1756.9060101@emolecules.com>
Craig James <craig_james@emolecules.com> writes:
> On 6/17/11 11:51 AM, Shianmiin wrote:
>> 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.
> 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.
Well, if you have a lot of databases then you definitely need to
increase the naptime to keep autovac's demands for stats within bounds.
I don't find that surprising, though I do wonder if we ought to redefine
the way that GUC works: right now, you get one autovac wakeup every
naptime/databases seconds.
The OP seems to have some other issue, though, since he says he's only
got 1 database.
regards, tom lane
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, craig_james@emolecules.com
Subject: Re: Who is causing all this i/o?
In-Reply-To: <16478.1308503310@sss.pgh.pa.us>
* 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