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
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 |