Skip site navigation (1) Skip section navigation (2)

Re: Problem with 8.4 stats collector high load

From: Jakub Ouhrabka <kuba(at)comgate(dot)cz>
To: Greg Smith <greg(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Problem with 8.4 stats collector high load
Date: 2010-02-16 08:40:28
Message-ID: 4B7A59FC.7010905@comgate.cz (view raw or flat)
Thread:
Lists: pgsql-hackers
 > You might want to try setting log_autovacuum_min_duration=0 in the
 > postgresql.conf

Thanks, tried it. There is nothing in the log - the actual 
vacuum/analyze commands are not run (as there is no query activity). I 
suspect that autovacuum is checking each database if it should run - and 
decides not to run. See the randomly catch process in ps 
output/pg_stat_activity mentioned in earlier mail. I suspect that this 
checking generates the load. Is it possible?

 > With this many databases and this high of a statistics target

I've changed the default_statistics_target back to its default (100). No 
change, still stats collector generates load.

 > You're really pushing what you can do in a VM with this many
 > databases of this size.

Yes, it's a VM but on our dedicated hardware - there are few other 
containers running but they are not generating any load.

What's puzzling me is that there is no database activity (queries, 
connections) and stats collector is still eating CPU.

Kuba

Dne 16.2.2010 8:29, Greg Smith napsal(a):
> Jakub Ouhrabka wrote:
>> I've found similar reports but with older versions of postgres:
>> http://old.nabble.com/100--of-CPU-utilization-postgres-process-tt27302021.html
>>
>
> Those all looked like a FreeBSD issue, doubt it's related to yours.
>
>> The pgstat.stat is ~20MB. There are 650 databases, 140GB total.
>> default_statistics_target = 1000
>> The system is running Proxmox linux distribution. PostgreSQL is in
>> OpenVZ container.
>
> With this many databases and this high of a statistics target, running
> in a VM, suspecting autovacuum seems reasonable. You might want to try
> setting log_autovacuum_min_duration=0 in the postgresql.conf, restarting
> or signalling (pg_ctl reload) the server, and watching just what it's
> doing. You might need to reduce how aggressively that runs, or limit the
> higher target to only the tables that need it, to get this under
> control. You're really pushing what you can do in a VM with this many
> databases of this size.
>


In response to

Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2010-02-16 08:43:05
Subject: Re: ToDo: preload for fulltext dictionary
Previous:From: Joachim WielandDate: 2010-02-16 08:28:13
Subject: Re: LISTEN/NOTIFY and notification timing guarantees

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group