pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to shrink pg_attribute table in some database
12+ messages / 4 participants
[nested] [flat]
* how to shrink pg_attribute table in some database
@ 2018-03-26 13:35 Artem Tomyuk <admin@leboutique.com>
0 siblings, 1 reply; 12+ messages in thread
From: Artem Tomyuk @ 2018-03-26 13:35 UTC (permalink / raw)
To: pgsql-admin
Hi, Gurus!!!!
Maybe someone knows what is the best way to shrink pg_attribute in size?
Maybe backup and restore of this table will sanitize "dead" records in it?
Or only full vacuum can help in this situation?
Thanks!
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to shrink pg_attribute table in some database
@ 2018-03-26 13:41 Keith <keith@keithf4.com>
parent: Artem Tomyuk <admin@leboutique.com>
0 siblings, 2 replies; 12+ messages in thread
From: Keith @ 2018-03-26 13:41 UTC (permalink / raw)
To: Artem Tomyuk <admin@leboutique.com>; +Cc: pgsql-admin
On Mon, Mar 26, 2018 at 9:35 AM, Artem Tomyuk <admin@leboutique.com> wrote:
> Hi, Gurus!!!!
>
> Maybe someone knows what is the best way to shrink pg_attribute in size?
> Maybe backup and restore of this table will sanitize "dead" records in it?
> Or only full vacuum can help in this situation?
>
> Thanks!
>
VACUUM FULL would be the way to go if bloat is the issue you're trying to
fix. Trying to backup up & restore a single system catalog would not be
recommended.
Keith
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to shrink pg_attribute table in some database
@ 2018-03-26 13:43 Artem Tomyuk <admin@leboutique.com>
parent: Keith <keith@keithf4.com>
1 sibling, 0 replies; 12+ messages in thread
From: Artem Tomyuk @ 2018-03-26 13:43 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: pgsql-admin
Thanks for quick response, Keith.
Can you explain your point more deeply, why its a bad idea to do
backup/restore of pg_attribute?
2018-03-26 16:41 GMT+03:00 Keith <keith@keithf4.com>:
>
>
> On Mon, Mar 26, 2018 at 9:35 AM, Artem Tomyuk <admin@leboutique.com>
> wrote:
>
>> Hi, Gurus!!!!
>>
>> Maybe someone knows what is the best way to shrink pg_attribute in size?
>> Maybe backup and restore of this table will sanitize "dead" records in it?
>> Or only full vacuum can help in this situation?
>>
>> Thanks!
>>
>
> VACUUM FULL would be the way to go if bloat is the issue you're trying to
> fix. Trying to backup up & restore a single system catalog would not be
> recommended.
>
> Keith
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to shrink pg_attribute table in some database
@ 2018-03-26 14:15 Artem Tomyuk <admin@leboutique.com>
parent: Keith <keith@keithf4.com>
1 sibling, 1 reply; 12+ messages in thread
From: Artem Tomyuk @ 2018-03-26 14:15 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: pgsql-admin
For now pg_attribute bloated to 300GB in size, and we have only 260GB's of
free space on disk.
In normal situation pg_attribute takes 50mb in size for our DB...
Can we assume that if we will run vacuum full on pg_attribute it will take
additional 50MB on disk?
Thanks
2018-03-26 16:41 GMT+03:00 Keith <keith@keithf4.com>:
>
>
> On Mon, Mar 26, 2018 at 9:35 AM, Artem Tomyuk <admin@leboutique.com>
> wrote:
>
>> Hi, Gurus!!!!
>>
>> Maybe someone knows what is the best way to shrink pg_attribute in size?
>> Maybe backup and restore of this table will sanitize "dead" records in it?
>> Or only full vacuum can help in this situation?
>>
>> Thanks!
>>
>
> VACUUM FULL would be the way to go if bloat is the issue you're trying to
> fix. Trying to backup up & restore a single system catalog would not be
> recommended.
>
> Keith
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to shrink pg_attribute table in some database
@ 2018-03-26 14:17 hubert depesz lubaczewski <depesz@depesz.com>
parent: Artem Tomyuk <admin@leboutique.com>
0 siblings, 1 reply; 12+ messages in thread
From: hubert depesz lubaczewski @ 2018-03-26 14:17 UTC (permalink / raw)
To: Artem Tomyuk <admin@leboutique.com>; +Cc: Keith <keith@keithf4.com>; pgsql-admin
On Mon, Mar 26, 2018 at 05:15:14PM +0300, Artem Tomyuk wrote:
> For now pg_attribute bloated to 300GB in size, and we have only 260GB's of
> free space on disk.
> In normal situation pg_attribute takes 50mb in size for our DB...
> Can we assume that if we will run vacuum full on pg_attribute it will take
> additional 50MB on disk?
You can estimate by doing:
create table z as select * from pg_Attribute;
and then checking size of z.
bloat there suggests that you have huge churn in tables - like: create
table, drop table, lots of times.
Best regards,
depesz
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to shrink pg_attribute table in some database
@ 2018-03-26 14:33 Artem Tomyuk <admin@leboutique.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 12+ messages in thread
From: Artem Tomyuk @ 2018-03-26 14:33 UTC (permalink / raw)
To: Hubert Lubaczewski <depesz@depesz.com>; +Cc: Keith <keith@keithf4.com>; pgsql-admin
Can't, it generates huge IO spikes.
But....
Few hours ago i manually started vacuum verbose on pg_attribute, now its
finished and i have some outputs:
INFO: "pg_attribute": found 554728466 removable, 212058 nonremovable row
versions in 44550921 out of 49326696 pages DETAIL: 178215 dead row versions
cannot be removed yet. There were 53479 unused item pointers. 0 pages are
entirely empty. CPU 1097.53s/1949.50u sec elapsed 6337.86 sec. Query
returned successfully with no result in 01:47:3626 hours.
what do you think?
select count(*) on pg_attribute returns:
158340 rows
So as i understand vacuum full will create new pg_attribute and will wrote
those amount of "valid" rows, but still it will scan 300GB old table?
So estimate will be even ~same compering with regular vacuum?
2018-03-26 17:17 GMT+03:00 hubert depesz lubaczewski <depesz@depesz.com>:
> On Mon, Mar 26, 2018 at 05:15:14PM +0300, Artem Tomyuk wrote:
> > For now pg_attribute bloated to 300GB in size, and we have only 260GB's
> of
> > free space on disk.
> > In normal situation pg_attribute takes 50mb in size for our DB...
> > Can we assume that if we will run vacuum full on pg_attribute it will
> take
> > additional 50MB on disk?
>
> You can estimate by doing:
>
> create table z as select * from pg_Attribute;
>
> and then checking size of z.
>
> bloat there suggests that you have huge churn in tables - like: create
> table, drop table, lots of times.
>
> Best regards,
>
> depesz
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to shrink pg_attribute table in some database
@ 2018-03-26 15:24 hubert depesz lubaczewski <depesz@depesz.com>
parent: Artem Tomyuk <admin@leboutique.com>
0 siblings, 1 reply; 12+ messages in thread
From: hubert depesz lubaczewski @ 2018-03-26 15:24 UTC (permalink / raw)
To: Artem Tomyuk <admin@leboutique.com>; +Cc: Keith <keith@keithf4.com>; pgsql-admin
On Mon, Mar 26, 2018 at 05:33:19PM +0300, Artem Tomyuk wrote:
> Can't, it generates huge IO spikes.
>
> But....
>
> Few hours ago i manually started vacuum verbose on pg_attribute, now its
> finished and i have some outputs:
>
> INFO: "pg_attribute": found 554728466 removable, 212058 nonremovable row
> versions in 44550921 out of 49326696 pages DETAIL: 178215 dead row versions
> cannot be removed yet. There were 53479 unused item pointers. 0 pages are
> entirely empty. CPU 1097.53s/1949.50u sec elapsed 6337.86 sec. Query
> returned successfully with no result in 01:47:3626 hours.
>
> what do you think?
>
> select count(*) on pg_attribute returns:
> 158340 rows
>
> So as i understand vacuum full will create new pg_attribute and will wrote
> those amount of "valid" rows, but still it will scan 300GB old table?
> So estimate will be even ~same compering with regular vacuum?
more or less, yes.
the thing is - find and fix whatever is causing this insane churn of
tables/attributes.
Best regards,
depesz
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to shrink pg_attribute table in some database
@ 2018-03-26 15:32 Artem Tomyuk <admin@leboutique.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 12+ messages in thread
From: Artem Tomyuk @ 2018-03-26 15:32 UTC (permalink / raw)
To: Hubert Lubaczewski <depesz@depesz.com>; +Cc: Keith <keith@keithf4.com>; pgsql-admin
Thanks,
as we understand there is a lack of autovacuum max workers in
postgresql.conf.
One question more, what impact would be on streaming replication? Will full
vacuum create extra wal files during full vacuum?
Thanks a lot
2018-03-26 18:24 GMT+03:00 hubert depesz lubaczewski <depesz@depesz.com>:
> On Mon, Mar 26, 2018 at 05:33:19PM +0300, Artem Tomyuk wrote:
> > Can't, it generates huge IO spikes.
> >
> > But....
> >
> > Few hours ago i manually started vacuum verbose on pg_attribute, now its
> > finished and i have some outputs:
> >
> > INFO: "pg_attribute": found 554728466 removable, 212058 nonremovable row
> > versions in 44550921 out of 49326696 pages DETAIL: 178215 dead row
> versions
> > cannot be removed yet. There were 53479 unused item pointers. 0 pages are
> > entirely empty. CPU 1097.53s/1949.50u sec elapsed 6337.86 sec. Query
> > returned successfully with no result in 01:47:3626 hours.
> >
> > what do you think?
> >
> > select count(*) on pg_attribute returns:
> > 158340 rows
> >
> > So as i understand vacuum full will create new pg_attribute and will
> wrote
> > those amount of "valid" rows, but still it will scan 300GB old table?
> > So estimate will be even ~same compering with regular vacuum?
>
> more or less, yes.
>
> the thing is - find and fix whatever is causing this insane churn of
> tables/attributes.
>
> Best regards,
>
> depesz
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to shrink pg_attribute table in some database
@ 2018-03-26 15:33 hubert depesz lubaczewski <depesz@depesz.com>
parent: Artem Tomyuk <admin@leboutique.com>
0 siblings, 1 reply; 12+ messages in thread
From: hubert depesz lubaczewski @ 2018-03-26 15:33 UTC (permalink / raw)
To: Artem Tomyuk <admin@leboutique.com>; +Cc: Keith <keith@keithf4.com>; pgsql-admin
On Mon, Mar 26, 2018 at 06:32:25PM +0300, Artem Tomyuk wrote:
> Thanks,
>
> as we understand there is a lack of autovacuum max workers in
> postgresql.conf.
that is a contributing factor. but the sheer size of this table means
that something is doing something very bad in your application.
>
> One question more, what impact would be on streaming replication? Will full
> vacuum create extra wal files during full vacuum?
Yes it will - in the size of new table.
Best regards,
depesz
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to shrink pg_attribute table in some database
@ 2018-03-26 15:41 Artem Tomyuk <admin@leboutique.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 12+ messages in thread
From: Artem Tomyuk @ 2018-03-26 15:41 UTC (permalink / raw)
To: Hubert Lubaczewski <depesz@depesz.com>; +Cc: Keith <keith@keithf4.com>; pgsql-admin
There was broken query that every time was creating temp table and than was
trying to insert into this table some data in another query but within
single connection, what happened next that second query was unable to be
executed because of syntax error (yes, human factor), this query was
repeated every minute by cron 24/7.
2018-03-26 18:33 GMT+03:00 hubert depesz lubaczewski <depesz@depesz.com>:
> On Mon, Mar 26, 2018 at 06:32:25PM +0300, Artem Tomyuk wrote:
> > Thanks,
> >
> > as we understand there is a lack of autovacuum max workers in
> > postgresql.conf.
>
> that is a contributing factor. but the sheer size of this table means
> that something is doing something very bad in your application.
>
> >
> > One question more, what impact would be on streaming replication? Will
> full
> > vacuum create extra wal files during full vacuum?
>
> Yes it will - in the size of new table.
>
> Best regards,
>
> depesz
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to shrink pg_attribute table in some database
@ 2018-03-26 15:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Artem Tomyuk <admin@leboutique.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2018-03-26 15:46 UTC (permalink / raw)
To: Artem Tomyuk <admin@leboutique.com>; +Cc: Hubert Lubaczewski <depesz@depesz.com>; Keith <keith@keithf4.com>; pgsql-admin
Artem Tomyuk <admin@leboutique.com> writes:
> There was broken query that every time was creating temp table and than was
> trying to insert into this table some data in another query but within
> single connection, what happened next that second query was unable to be
> executed because of syntax error (yes, human factor), this query was
> repeated every minute by cron 24/7.
One temp table per minute doesn't explain this amount of bloat, unless
autovacuum had failed to process pg_attribute for a really really long
time. It would be good to figure out why that happened.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to shrink pg_attribute table in some database
@ 2018-03-26 15:51 Artem Tomyuk <admin@leboutique.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Artem Tomyuk @ 2018-03-26 15:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Hubert Lubaczewski <depesz@depesz.com>; Keith <keith@keithf4.com>; pgsql-admin
our monitoring shows that this bloat happened within 72h.
after query was fixed - db stopped to grow in size.... so what other ideas?
2018-03-26 18:46 GMT+03:00 Tom Lane <tgl@sss.pgh.pa.us>:
> Artem Tomyuk <admin@leboutique.com> writes:
> > There was broken query that every time was creating temp table and than
> was
> > trying to insert into this table some data in another query but within
> > single connection, what happened next that second query was unable to be
> > executed because of syntax error (yes, human factor), this query was
> > repeated every minute by cron 24/7.
>
> One temp table per minute doesn't explain this amount of bloat, unless
> autovacuum had failed to process pg_attribute for a really really long
> time. It would be good to figure out why that happened.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2018-03-26 15:51 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-26 13:35 how to shrink pg_attribute table in some database Artem Tomyuk <admin@leboutique.com>
2018-03-26 13:41 ` Keith <keith@keithf4.com>
2018-03-26 13:43 ` Artem Tomyuk <admin@leboutique.com>
2018-03-26 14:15 ` Artem Tomyuk <admin@leboutique.com>
2018-03-26 14:17 ` hubert depesz lubaczewski <depesz@depesz.com>
2018-03-26 14:33 ` Artem Tomyuk <admin@leboutique.com>
2018-03-26 15:24 ` hubert depesz lubaczewski <depesz@depesz.com>
2018-03-26 15:32 ` Artem Tomyuk <admin@leboutique.com>
2018-03-26 15:33 ` hubert depesz lubaczewski <depesz@depesz.com>
2018-03-26 15:41 ` Artem Tomyuk <admin@leboutique.com>
2018-03-26 15:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-03-26 15:51 ` Artem Tomyuk <admin@leboutique.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