Обсуждение: pg_attribute getting bloated in size
Hi,
pages: 0 removed, 1127929 remain
tuples: 169 removed, 14656351 remain, 14501557 are dead but not yet removable
buffer usage: 1350052 hits, 996326 misses, 20842 dirtied
avg read rate: 19.732 MB/s, avg write rate: 0.413 MB/s
system usage: CPU 5.61s/5.27u sec elapsed 394.47 sec
i am faced with bloated in size pg_attribute table, now its 15 GB in size, from logs autovacuum is showing a lot of now removable rows:
===========>6915 2018-12-14 03:40:02 EET 00000 [7-1]LOG: 00000: automatic vacuum of table "lb_upr.pg_catalog.pg_attribute": index scans: 1pages: 0 removed, 1127929 remain
tuples: 169 removed, 14656351 remain, 14501557 are dead but not yet removable
buffer usage: 1350052 hits, 996326 misses, 20842 dirtied
avg read rate: 19.732 MB/s, avg write rate: 0.413 MB/s
system usage: CPU 5.61s/5.27u sec elapsed 394.47 sec
Why autovacuum cant delete those rows?
What is the main reason that pg_attribute getting bloated?
My autovacuum settings are:
log_autovacuum_min_duration = 0
autovacuum_max_workers = 4
autovacuum_naptime = 51min
autovacuum_vacuum_threshold = 750
autovacuum_analyze_threshold = 5500
autovacuum_vacuum_scale_factor = 0
autovacuum_analyze_scale_factor = 0.2
#autovacuum_freeze_max_age = 200000000
#autovacuum_multixact_freeze_max_age = 400000000
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1500
Will be grateful for any advise!
Artem Tomyuk wrote: > Why autovacuum cant delete those rows? Either long running transactions (check pg_stat_activity) or prepared transactions (check pg_prepared_xacts) or stale replication slots (check pg_replication_slots). > What is the main reason that pg_attribute getting bloated? Creating and dropping many (maybe temporary) tables. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
On 12/14/2018 05:29 AM, Laurenz Albe wrote: > Artem Tomyuk wrote: >> Why autovacuum cant delete those rows? > Either long running transactions (check pg_stat_activity) > or prepared transactions (check pg_prepared_xacts) > or stale replication slots (check pg_replication_slots). Would a manual "VACUUM FULL pg_attribute;" solve the problem? -- Angular momentum makes the world go 'round.
yes, it helps
пт, 14 дек. 2018 г. в 14:14, Ron <ronljohnsonjr@gmail.com>:
On 12/14/2018 05:29 AM, Laurenz Albe wrote:
> Artem Tomyuk wrote:
>> Why autovacuum cant delete those rows?
> Either long running transactions (check pg_stat_activity)
> or prepared transactions (check pg_prepared_xacts)
> or stale replication slots (check pg_replication_slots).
Would a manual "VACUUM FULL pg_attribute;" solve the problem?
--
Angular momentum makes the world go 'round.
On Fri, 2018-12-14 at 14:21 +0200, Artem Tomyuk wrote: > пт, 14 дек. 2018 г. в 14:14, Ron <ronljohnsonjr@gmail.com>: > > On 12/14/2018 05:29 AM, Laurenz Albe wrote: > > > Artem Tomyuk wrote: > > >> Why autovacuum cant delete those rows? > > > Either long running transactions (check pg_stat_activity) > > > or prepared transactions (check pg_prepared_xacts) > > > or stale replication slots (check pg_replication_slots). > > > > Would a manual "VACUUM FULL pg_attribute;" solve the problem? > > yes, it helps Only if the problems have been removed. Otherwise, VACUUM (FULL) won't help. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
On Fri, Dec 14, 2018, 8:33 AM Laurenz Albe <laurenz.albe@cybertec.at wrote:
On Fri, 2018-12-14 at 14:21 +0200, Artem Tomyuk wrote:
> пт, 14 дек. 2018 г. в 14:14, Ron <ronljohnsonjr@gmail.com>:
> > On 12/14/2018 05:29 AM, Laurenz Albe wrote:
> > > Artem Tomyuk wrote:
> > >> Why autovacuum cant delete those rows?
> > > Either long running transactions (check pg_stat_activity)
> > > or prepared transactions (check pg_prepared_xacts)
> > > or stale replication slots (check pg_replication_slots).
> >
> > Would a manual "VACUUM FULL pg_attribute;" solve the problem?
>
> yes, it helps
Only if the problems have been removed.
Otherwise, VACUUM (FULL) won't help.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com