Problem with 8.4 stats collector high load

Поиск
Список
Период
Сортировка
От Jakub Ouhrabka
Тема Problem with 8.4 stats collector high load
Дата
Msg-id 4B7A4615.40909@comgate.cz
обсуждение исходный текст
Ответы Re: Problem with 8.4 stats collector high load  (Greg Smith <greg@2ndquadrant.com>)
Re: Problem with 8.4 stats collector high load  (Euler Taveira de Oliveira <euler@timbira.com>)
Список pgsql-hackers
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.


В списке pgsql-hackers по дате отправления:

Предыдущее
От: Takahiro Itagaki
Дата:
Сообщение: Re: psql tab-completion for new syntax
Следующее
От: Greg Smith
Дата:
Сообщение: Re: Problem with 8.4 stats collector high load