Thread: Catalog Bloat

On Fri, 30 Jan 2015 09:30:31 -0600
Jeff Amiel <jeff.amiel@gmail.com> wrote:

> Probably temp table related ...but catalog bloat on one of my databases
> appears to be pretty bad.
>
> Is the below bloat (table and index) something to worry about?
> pg_stat_all_tables show the relations ARE getting successfully vacuumed...

A few hundred meg of extra space on a modern system isn't that much to
worry about. The concern I would have (personally) is whether this is a
stable amount of bloat or whether it's going to keep getting worse. I
recommend you slap something on that system to track it (such as a Cacti
graph) and keep an eye on it to see if it's stable. The thing is, even if
you cleaned up the bloat, if what you're seeing is the amount of bloat
necessary to efficiently use those tables, it's just going to come back
anyway.

> Any suggestions on eliminating?  Not sure if tools like pg_reorg are
> appropriate (or effective) or even vacuum full (yikes).
> I'd prefer not to take a complete outage - but I would if this bloat is
> really an issue.

I don't know the parameters of the system that uses this DB, but you should
be able to VACUUM FULL or REINDEX those tables pretty quickly. If you have
a slow period where you can tolerate a few seconds lag while it runs, you
can probably sneak it in without any trouble. Of course, such a thing could
also bite you in the ass by taking longer than you expect. My experience
recommends:
1) Graph the bloat for a while first ... see if it's even worth it.
2) If you decide to do it, do 1 table or index at a time so you don't
   overcommit yourself.
3) Recreate the system and its bloat in a test environment to get a more
   realistic idea of how long it will really take and how much it will
   really interrupt operations. Hopefully you have such an environment
   available.

> (I know about "reindex system" (duh) - but as that requires me to take an
> outage, my question about IF the bloat is a cause for concern still
> stands....)
>
> schemaname |  tablename  | tbloat | wastedbytes |              iname
>       | ibloat | wastedibytes
> ------------+--------------+--------+-------------+---------------------------------+--------+--------------
> pg_catalog | pg_attribute |    9.0 |    27648000 |
> pg_attribute_relid_attnam_index |  243.5 |    361627648
> pg_catalog | pg_attribute |    9.0 |    27648000 |
> pg_attribute_relid_attnum_index |  168.5 |    253894656
> pg_catalog | pg_type      |  10.8 |    4890624 | pg_type_oid_index
>     |  135.8 |    28721152
> pg_catalog | pg_type      |  10.8 |    4890624 | pg_type_typname_nsp_index
>     |  287.2 |    60956672
> pg_catalog | pg_class    |  10.3 |    4562944 | pg_class_oid_index
>     |  94.1 |    26689536
> pg_catalog | pg_class    |  10.3 |    4562944 | pg_class_relname_nsp_index
>     |  270.1 |    77144064
> pg_catalog | pg_depend    |    5.3 |    3948544 |
> pg_depend_reference_index      |  337.0 |    156901376
> pg_catalog | pg_depend    |    5.3 |    3948544 | pg_depend_depender_index
>       |  359.6 |    167436288
> pg_catalog | pg_index    |    6.1 |    1130496 | pg_index_indexrelid_index
>     |  72.9 |      7659520
> pg_catalog | pg_index    |    6.1 |    1130496 | pg_index_indrelid_index
>     |  72.9 |      7659520
> (10 rows)
>
> Thanks in advance


--
Bill Moran

On 1/30/15 11:22 AM, Bill Moran wrote:
>> >Any suggestions on eliminating?  Not sure if tools like pg_reorg are
>> >appropriate (or effective) or even vacuum full (yikes).
>> >I'd prefer not to take a complete outage - but I would if this bloat is
>> >really an issue.
> I don't know the parameters of the system that uses this DB, but you should
> be able to VACUUM FULL or REINDEX those tables pretty quickly.

Be careful if you're running an old version though... at one point
VACUUM FULL would actually make index bloat a lot worse because it
physically moved tuples in the same heap instead of rebuilding the heap
from scratch. If your index bloat is still bad you can REINDEX (at least
I think that's allowed on catalog tables).
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com