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

Problem with 8.4 stats collector high load

From: Jakub Ouhrabka <kuba(at)comgate(dot)cz>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Problem with 8.4 stats collector high load
Date: 2010-02-16 07:15:33
Message-ID: (view raw or flat)
Lists: pgsql-hackers

sorry for repost but previous message didn't get through. So I'm trying 
another list and sending without attachment which I can send privately 
upon request (strace output mentioned below).

We've migrated some of our databases to 8.4 cluster (from 8.2 and older 

These databases are archive databases, so there is no user activity - no 
connected users. But the stats collector generates load - 20-40% of 
modern 2.8GHz core all the time.

I've found similar reports but with older versions of postgres:

Any clues what does it cause and how to investigate it?

I'm attaching my findings below - I suspect autovacuum but don't know 
where the problem is exactly.



Detailed report:

PostgreSQL 8.4.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc 
(Debian 4.3.2-1.1) 4.3.2, 64-bit

The pgstat.stat is ~20MB. There are 650 databases, 140GB total.

Attached is strace output of stats collector running for 5s.

Restarting postgresql and/or pg_stat_reset() doesn't help.

When I do select * from pg_stat_activity, there is every 3rd try row 
like this:

template1# select * from pg_stat_activity;

datname - some database in the cluster
procpid - changing number
usename - postgres
current_query -  <command string not enabled>
xact_start - null
query_start - null
backend_start - few milliseconds ago

ps shows autovacuum worker:

21323 0:04 /opt/pg/bin/postmaster -D /var/lib/postgresql/8.4/data
21325 0:00 postgres: writer process
21326 0:00 postgres: wal writer process
21327 3:01 postgres: autovacuum launcher process
21328 22:30 postgres: stats collector process
21355 0:00 postgres: autovacuum worker process "name of db"

There are only minor modifications to postgresql.conf:

shared_buffers = 512MB
temp_buffers = 2MB
work_mem = 32MB
maintenance_work_mem = 128MB
max_stack_depth = 1MB
fsync = off
wal_buffers = 1MB
checkpoint_segments = 100
effective_cache_size = 2GB
default_statistics_target = 1000

The system is running Proxmox linux distribution. PostgreSQL is in 
OpenVZ container. The kernel is 2.6.18-2-pve. PostgreSQL data files are 
on local xfs filesystem. We don't have much experience with this setup 
yet. But we have a smaller cluster with 8.4 running without this problem 
on other machine. And we have a big 8.2 cluster on this setup without 
this problem.


pgsql-hackers by date

Next:From: Greg SmithDate: 2010-02-16 07:29:20
Subject: Re: Problem with 8.4 stats collector high load
Previous:From: Takahiro ItagakiDate: 2010-02-16 05:24:41
Subject: Re: psql tab-completion for new syntax

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