Re: how to investigate GIN fast updates and cleanup cycles?

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: how to investigate GIN fast updates and cleanup cycles?
Дата
Msg-id CAMkU=1zL0KoMJgpwmXX1b-UPCAbXGzTeUPWuKypxSBQ30ftRxA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: how to investigate GIN fast updates and cleanup cycles?  (Steve Kehlet <steve.kehlet@gmail.com>)
Ответы Re: how to investigate GIN fast updates and cleanup cycles?
Список pgsql-general
On Fri, Aug 28, 2015 at 12:25 PM, Steve Kehlet <steve.kehlet@gmail.com> wrote:
On Fri, Aug 28, 2015 at 12:10 PM Jeff Janes <jeff.janes@gmail.com> wrote:
Did you change the system-wide autovacuum_analyze_scale_factor?  If so, don't do that.  You can use a table's storage parameters to set a custom autovacuum_analyze_scale_factor just for individual tables.  So just the table with the troublesome gin index:
 
No I did it just to the problematic table:

ALTER TABLE my_table SET (autovacuum_vacuum_scale_factor=0);
ALTER TABLE my_table SET (autovacuum_analyze_scale_factor=0);

You should RESET the autovacuum_vacuum_scale_factor for the table.  You don't want it to be vacuumed aggressively, just autoanalyzed aggressively.  Sorry if my copy-paste error led you astray on that.

 

My system-wide autovacuum_analyze_threshold is 50 so I think that's ok.

autovacuum_analyze_scale_factor is the more important one.  Only when that is close to zero does autovacuum_analyze_threshold matter very much.

 
There is a bulk load going on right now so a lot of tables are needing vacuuming. I really need to increase my autovacuum_max_workers.

But those workers all share the same IO throttling amongst themselves.  Increasing it mostly just gives you more workers all working more slowly.  Assuming your IO subsystem can handle it, you are better off lowering autovacuum_vacuum_cost_delay, which can be done without a server restart (although the change won't take full effect until the existing workers go away and restart).  I also set vacuum_cost_page_hit and vacuum_cost_page_miss to zero and rely exclusively on vacuum_cost_page_dirty to do the throttling.
 
Cheers,

Jeff

В списке pgsql-general по дате отправления:

Предыдущее
От: kingl
Дата:
Сообщение: wal files stay in the pg_xlog dir
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: wal files stay in the pg_xlog dir