Message-ID: <4DD6FC11.4000706@emolecules.com>
Date: Fri, 20 May 2011 16:41:05 -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: Scott Marlowe <scott.marlowe@gmail.com>
CC: pgsql-admin@postgresql.org
Subject: Re: Who is causing all this i/o?
References: <4DD6D9A6.1010101@emolecules.com>
	<BANLkTikJ6xn-pKRhRA1HGiNotQt7N=1QDQ@mail.gmail.com>
In-Reply-To: <BANLkTikJ6xn-pKRhRA1HGiNotQt7N=1QDQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 5/20/11 4:25 PM, Scott Marlowe wrote:
> On Fri, May 20, 2011 at 3:14 PM, Craig James<craig_james@emolecules.com>  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.
>> After a lot of digging around, I found this in the /postgres/pg_stat_tmp
>> directory.  If I list the directory including the i-nodes once every second,
>> I find that a new 2MB file is being created roughly once every two seconds:
> Have you got a lot of databases in your development environment?  I
> think that can sometimes cause a lot of pg_stat writes.
Yes.  The production servers have a dozen or so databases, but the development server has a couple hundred databases. Does that count as "a lot of databases"?

We've had even more databases in the past (>500) and didn't see this sort of I/O activity.

The odd thing is that this activity is gradually growing, so slowly that you don't notice it right away.  A month or two ago, nothing.  A couple weeks ago, a constant stream of "grass" in xload's graph.  Today, xload is showing a 50% all the time.  I can only guess that the load will continue to increase.

Is there a way to tell Postgres not to do this, or to do it less?  It's not a big disaster (yet), but it's annoying to have the system spewing a couple megabytes of pg_stat_tmp data every few seconds.

Thanks,
Craig