On 5/21/11 8:11 AM, Tom Lane wrote:
Craig James<craig_ja...@emolecules.com> writes:
On 5/20/11 4:25 PM, Scott Marlowe wrote:
On Fri, May 20, 2011 at 3:14 PM, Craig James<craig_ja...@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"?
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
