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
Re: Problem with 8.4 stats collector high load |
Список | 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 по дате отправления: