Re: Who is causing all this i/o?

On 5/21/11 8:11 AM, Tom Lane wrote:
> Craig James<craig_james(at)emolecules(dot)com> writes:
>> On 5/20/11 4:25 PM, Scott Marlowe wrote:
>>> On Fri, May 20, 2011 at 3:14 PM, Craig James<craig_james(at)emolecules(dot)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"?
> Yeah. I think what is happening is that the autovacuum launcher is
> visiting every database, doing accesses to the system catalogs (and not
> much more than that), which results in access-count updates in the stats
> collector, which have to get written to disk.
>
> 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,
$ ./postgres --version
postgres (PostgreSQL) 8.4.4

> or you
> have a monitoring task that is constantly asking to see stats.
Nothing like that. There's virtually nothing on this server but Postgres and Apache. There are some fast-cgi perl programs that stay connected to Postgres all the time, but the constant I/O starts up even if I kill Apache.
> One possible band-aid solution is to increase autovacuum_naptime. This
> is defined as the AV cycle time *for each database*, so AV wakes up and
> touches another database every naptime/#databases seconds.
I'll try this. Since it's a development system, it tends to get large bursts of loading followed by a bit of searching. Cutting down on vacuums should be fine.
> If your
> number of databases has been growing over time, this would probably
> explain why the problem is getting worse.
For the last few months the number of databases has been almost static. Maybe five added (of 200) in the last three months, and probably a few others dropped.

Thanks,
Craig
> regards, tom lane
>