Cannot get autovacuum configured
От | Walter Vaughan |
---|---|
Тема | Cannot get autovacuum configured |
Дата | |
Msg-id | 465643B7.50800@steelerubber.com обсуждение исходный текст |
Список | 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
В списке pgsql-general по дате отправления: