Обсуждение: pg_class (system) table increasing size.

On 11/16/2016 07:08 PM, dhaval jaiswal wrote:
>
>>> Because you are creating (specific) objects.
>
> I have gone through the link and  how would i figure out which
> specific object is causing this.  Can you please elaborate more here.

It is not any one object, it is the total of the objects(relations) as
defined here:

https://www.postgresql.org/docs/9.4/static/catalog-pg-class.html

48.11. pg_class

relkind     char           r = ordinary table, i = index, S = sequence, v = view,
m = materialized view, c = composite type, t = TOAST table, f = foreign
table

So if you do something like:

select relkind, relname from pg_class order by relkind, relname;

you should see what the entries are the table by their type. Might give
you a clue as to what is causing the growth.

>
>
> We do not have the much temporary table usage.
>
>
> Since the size is bigger (5 GB) to maintain. does it requires

The size you are referring to is the database size, the table size or
something else?


> maintenance as well for thepg_class.
>
>
> It seems its affecting performance.
>
>
> ------------------------------------------------------------------------
> *From:* David G. Johnston <david.g.johnston@gmail.com>
> *Sent:* Thursday, November 17, 2016 8:13 AM
> *To:* dhaval jaiswal
> *Cc:* pgsql-general@postgresql.org
> *Subject:* Re: [GENERAL] pg_class (system) table increasing size.
>
> On Wed, Nov 16, 2016 at 7:30 PM, dhaval jaiswal <dhavallj@hotmail.com
> <mailto:dhavallj@hotmail.com>>wrote:
>
>     PostgreSQL 9.4.0
>
> Are generalizing here or are you really running 2+ year old patch version?
>
>     Why pg_class table is getting bigger in size.
>
> Because you are creating (specific) objects.
>
> See: https://www.postgresql.org/docs/9.6/static/catalog-pg-class.html
>
>     How to stop increasing it.
>
> Stop creating (those specific) objects.
>
>     Does it affect the performance.
>
> It can - depends greatly on scale.
>
> Note, frequent usage of temporary tables is a common cause for this kind
> of behavior.
>
> David J.
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/16/2016 07:08 PM, dhaval jaiswal wrote:
>
>>> Because you are creating (specific) objects.
>
> I have gone through the link and  how would i figure out which
> specific object is causing this.  Can you please elaborate more here.
>
>
> We do not have the much temporary table usage.
>
>
> Since the size is bigger (5 GB) to maintain. does it requires
> maintenance as well for thepg_class.

Should have added to my previous post. What does:

select * from pg_stat_sys_tables where relname = 'pg_class';

show?

>
>
> It seems its affecting performance.
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com

dhaval jaiswal wrote:
> select * from pg_stat_sys_tables where relname = 'pg_class';
>
> -[ RECORD 1 ]-------+-----------
> relid               | 1259
> schemaname          | pg_catalog
> relname             | pg_class
> seq_scan            | 1838
> seq_tup_read        | 3177416
> idx_scan            | 1027456557
> idx_tup_fetch       | 959682909
> n_tup_ins           | 0
> n_tup_upd           | 0
> n_tup_del           | 0
> n_tup_hot_upd       | 0
> n_live_tup          | 0
> n_dead_tup          | 0
> n_mod_since_analyze | 0
> last_vacuum         |
> last_autovacuum     |
> last_analyze        |
> last_autoanalyze    |
> vacuum_count        | 0
> autovacuum_count    | 0
> analyze_count       | 0
> autoanalyze_count   | 0
>
>
> Yes, the size of pg_class table is of 5 GB.  However, the existing row is only 2380 only. It's got fragmented.

Looks like you lost the stat data awhile ago (probably due to a server
crash, or pg_stats_reset()) and it never got updated.  I suggest doing
"ANALZYE pg_class" to create initial stats; that might prompt autovacuum
to vacuum the table.  If the bloat is excessive, vacuuming might take a
very long time, in which case perhaps consider VACUUM FULL (but be very
aware of its consequences first).

I think it's likely that this has happened to other catalogs as well, so
check the pg_stat_sys_tables view for other entries with all zeroes in
the n_tup_* columns.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Thu, Nov 17, 2016 at 3:16 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

> Looks like you lost the stat data awhile ago (probably due to a server
> crash, or pg_stats_reset()) and it never got updated.  I suggest doing
> "ANALZYE pg_class" to create initial stats; that might prompt autovacuum
> to vacuum the table.  If the bloat is excessive, vacuuming might take a
> very long time, in which case perhaps consider VACUUM FULL (but be very
> aware of its consequences first).
>
> I think it's likely that this has happened to other catalogs as well, so
> check the pg_stat_sys_tables view for other entries with all zeroes in
> the n_tup_* columns.

+1

Also, you may want to review your autovacuum settings to make sure
they are aggressive enough.  You didn't describe you machine, your
workload, or your vacuum regimen, but if it's a large machine you
would probably need to raise autovacuum_vacuum_cost limit.  And if
autovacuum somehow got turned *off* you are likely to have all
kinds of problems with bloat, and may need to schedule some down
time to get it cleaned up.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

dhaval jaiswal wrote:
> I did check and found it was the bloated size of pg_class which was slowing down the performance.
>
> It got fixed by adding in routine maintenance task. Things are fine now.

Good to know.

> However, I want to know how come pg_class  (system table) get bloated/affected.
>
> What could be the possible ways, where i can look into.

The most common cause is high traffic temp table usage.

> Due to business impact auto vacuum is off.

Yeah, that's a really bad idea and you should turn it on and configure
it so that it doesn't impact business.  Having it turned off is
definitely not recommended.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

dhaval jaiswal wrote:
> Adding to above.  Below are the outputs.

I just meant that you need to ANALYZE all these system catalogs so that
autovacuum can pick up vacuuming them to remove dead tuples.  Do not
leave autovacuum turned off anymore.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Mon, Nov 21, 2016 at 11:34 AM, dhaval jaiswal <dhavallj@hotmail.com> wrote:

> Due to business impact auto vacuum is off.

You have now discovered some of the the negative business impact of
turning it off.  If you leave it off, much worse is likely to
follow.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company