pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_attribute getting bloated in size
6+ messages / 4 participants
[nested] [flat]
* pg_attribute getting bloated in size
@ 2018-12-14 10:40 Artem Tomyuk <admin@leboutique.com>
0 siblings, 1 reply; 6+ messages in thread
From: Artem Tomyuk @ 2018-12-14 10:40 UTC (permalink / raw)
To: pgsql-admin
Hi,
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: 1
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
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!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_attribute getting bloated in size
@ 2018-12-14 11:29 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Artem Tomyuk <admin@leboutique.com>
0 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2018-12-14 11:29 UTC (permalink / raw)
To: Artem Tomyuk <admin@leboutique.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_attribute getting bloated in size
@ 2018-12-14 12:13 Ron <ronljohnsonjr@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 6+ messages in thread
From: Ron @ 2018-12-14 12:13 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_attribute getting bloated in size
@ 2018-12-14 12:21 Artem Tomyuk <admin@leboutique.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Artem Tomyuk @ 2018-12-14 12:21 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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.
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_attribute getting bloated in size
@ 2018-12-14 14:33 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Artem Tomyuk <admin@leboutique.com>
0 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2018-12-14 14:33 UTC (permalink / raw)
To: Artem Tomyuk <admin@leboutique.com>; Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_attribute getting bloated in size
@ 2018-12-20 09:20 Robert Simmons <rsimmons514@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 6+ messages in thread
From: Robert Simmons @ 2018-12-20 09:20 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Artem Tomyuk <admin@leboutique.com>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
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
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2018-12-20 09:20 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-12-14 10:40 pg_attribute getting bloated in size Artem Tomyuk <admin@leboutique.com>
2018-12-14 11:29 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-12-14 12:13 ` Ron <ronljohnsonjr@gmail.com>
2018-12-14 12:21 ` Artem Tomyuk <admin@leboutique.com>
2018-12-14 14:33 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-12-20 09:20 ` Robert Simmons <rsimmons514@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox