Cannot get autovacuum configured

From: Walter Vaughan <wvaughan(at)steelerubber(dot)com>
To: Pg general <pgsql-general(at)postgresql(dot)org>
Subject: Cannot get autovacuum configured
Date: 2007-05-25 02:02:31
Message-ID: 465643B7.50800@steelerubber.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Configuring autovacuum shouldn't be so hard.
:( I had a similar problem to this months ago, and I can't seem to fix it again

opentaps=# SELECT name, setting from pg_settings where name like '%stats_%';
name | setting
-----------------------------+---------
stats_block_level | off
stats_command_string | on
stats_reset_on_server_start | off
stats_row_level | on
stats_start_collector | on
(5 rows)

opentaps=# SELECT name, setting from pg_settings where name like '%autovacuum%';
name | setting
---------------------------------+-----------
autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 250
autovacuum_freeze_max_age | 200000000
autovacuum_naptime | 60
autovacuum_vacuum_cost_delay | -1
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 500
(9 rows)

select last_autovacuum, last_autoanalyze from pg_stat_all_tables;
last_autovacuum | last_autoanalyze
-----------------+------------------
|
[about 6 entries] [about 20 entries]
|
|
(978 rows)

It looks like I have a huge configuration issue.

The database has 769 tables... And I can run vacuum from pgadminIII just fine
It completes in about 30 seconds. But postgresql seems to slow down fast, so I
believe it's not running correctly,

# ps uax | grep postgres | grep 4:
pgsql 23538 0.0 1.3 107528 55664 ?? Ss 4:32PM 0:01.20 postgres: writer
pgsql 23539 0.0 0.2 15352 6788 ?? Ss 4:32PM 0:06.72 postgres: stats c
# pkg_info | grep post
postgresql-client-8.2.4 PostgreSQL database (client)
postgresql-server-8.2.4 The most advanced open-source database available anywhere
# uname -a
FreeBSD xxxx 7.0-CURRENT FreeBSD 7.0-CURRENT #6: Mon May 21 13:56:15 EDT 2007
xxxx:/usr/obj/usr/src/sys/MYKERNEL amd64

For performance issues I was encouraged to run with

Configured kernel with SCHED_ULE
sched.kern.pick_pri=0
opentaps=# SELECT name, setting from pg_settings where name like '%title%';
name | setting
----------------------+---------
update_process_title | off

CPU: Intel(R) Xeon(R) CPU E5335 @ 2.00GHz (2004.98-MHz K8-class CPU)
usable memory = 4279037952 (4080 MB)
avail memory = 4092456960 (3902 MB)
FreeBSD/SMP: Multiprocessor System Detected: 8 CPUs
acpi0: <INTEL S5000XVN> on motherboard

4 more Gig to be installed tomorrow, thus the 64bit OS in the mix...

If someone could point me to some kind of rule of thumb, I would be ever so
grateful. This is maddening that I cannot even get a starting point setup.

--
Walter

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2007-05-25 02:39:22 Re: index vs. seq scan choice?
Previous Message A.M. 2007-05-25 01:40:45 Re: why postgresql over other RDBMS