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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
