Hi,
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
versions).
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:
http://old.nabble.com/100--of-CPU-utilization-postgres-process-tt27302021.html
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.
Thanks,
Kuba
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.
