Issue in autovacuum

Поиск
Список
Период
Сортировка
От Agarwal, Prateek
Тема Issue in autovacuum
Дата
Msg-id TU4PR84MB0256B1221D53D167CEE497DDD5320@TU4PR84MB0256.NAMPRD84.PROD.OUTLOOK.COM
обсуждение исходный текст
Ответы Re: Issue in autovacuum  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general

I had 9.3.0 previously where auto vacuum was working fine where it was able to reclaim space by removing dead tuples.

Version: PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit

When I upgrade my db to 9.4.6 (my application remains same and hardly any code change w.r.t transaction declarations), it started complaining about dead rows below. I am not sure if it is something to do with upgrade or not.

One thing, that was happening on my system was there were continuous updates to few tables around 10 million times over a period of 2-3 days. 


2017-03-02 19:50:52 GMT [30498]: [41-1] [0] user=,db= LOG:  automatic vacuum of table "myschema.mytable": index scans: 0
    pages: 0 removed, 23045 remain
    tuples: 0 removed, 902169 remain, 900511 are dead but not yet removable
    buffer usage: 23060 hits, 26263 misses, 0 dirtied
    avg read rate: 2.331 MB/s, avg write rate: 0.000 MB/s
    system usage: CPU 0.26s/0.38u sec elapsed 88.02 sec

Not just mytable, even vaccum not able to claim dead rows on postgres internal tables like pg_class

2017-03-02 20:42:02 GMT [15592]: [3-1] [0] user=,db= LOG:  automatic vacuum of table "mydb.pg_catalog.pg_class": index scans: 0
    pages: 0 removed, 407 remain
    tuples: 0 removed, 18876 remain, 16568 are dead but not yet removable
    buffer usage: 411 hits, 696 misses, 0 dirtied
    avg read rate: 2.424 MB/s, avg write rate: 0.000 MB/s
    system usage: CPU 0.00s/0.01u sec elapsed 2.24 sec

CentOS 6.7


My Vacuum configuration:
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

autovacuum = on            # Enable autovacuum subprocess?  'on'
                    # requires track_counts to also be on.
log_autovacuum_min_duration = 100    # -1 disables, 0 logs all actions and
                    # their durations, > 0 logs only
                    # actions running at least this number
                    # of milliseconds.
#autovacuum_max_workers = 3        # max number of autovacuum subprocesses
                    # (change requires restart)
#autovacuum_naptime = 1min        # time between autovacuum runs
#autovacuum_vacuum_threshold = 50    # min number of row updates before
                    # vacuum
#autovacuum_analyze_threshold = 50    # min number of row updates before
                    # analyze
autovacuum_vacuum_scale_factor = 0.05    # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.05    # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000    # maximum XID age before forced vacuum
                    # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                    # autovacuum, in milliseconds;
                    # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1    # default vacuum cost limit for
                    # autovacuum, -1 means use
                    # vacuum_cost_limit


Any possible root cause? Is there a known issue in 9.4.6?

Thank you for your time!

 

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

Предыдущее
От: John McKown
Дата:
Сообщение: Re: browser interface to forums please?
Следующее
От: Glyn Astill
Дата:
Сообщение: Re: Trigger based logging alternative to table_log