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 по дате отправления:

Предыдущее
От: "A.M."
Дата:
Сообщение: Re: why postgresql over other RDBMS
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: index vs. seq scan choice?