Thread: pg_class (system) table increasing size.

pg_class (system) table increasing size.

From
dhaval jaiswal
Date:

PostgreSQL 9.4.0


Why pg_class table is getting bigger in size. 

How to stop increasing it. 

Does it affect the performance.  


Re: pg_class (system) table increasing size.

From
"David G. Johnston"
Date:
On Wed, Nov 16, 2016 at 7:30 PM, dhaval jaiswal <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.​

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.
 

Re: pg_class (system) table increasing size.

From
dhaval jaiswal
Date:


>> 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 the pg_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> 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.

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.
 

Re: pg_class (system) table increasing size.

From
Adrian Klaver
Date:
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


Re: pg_class (system) table increasing size.

From
Adrian Klaver
Date:
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


Re: pg_class (system) table increasing size.

From
dhaval jaiswal
Date:

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. 


From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, November 17, 2016 8:29 PM
To: dhaval jaiswal; David G. Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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.
>
>
> 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

Re: pg_class (system) table increasing size.

From
Melvin Davidson
Date:


On Thu, Nov 17, 2016 at 1:33 PM, dhaval jaiswal <dhavallj@hotmail.com> 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. 


From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, November 17, 2016 8:29 PM
To: dhaval jaiswal; David G. Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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.
>
>
> 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


>Yes, the size of pg_class table is of 5 GB.  However, the existing row is only 2380 only. It's got fragmented.

I strongly believe you are incorrect about the size of the pg_class table.
The correct way to determine that size is:

SELECT n.nspname as schema,
       c.relname as table,
       a.rolname as owner,
       c.relfilenode as filename,
       c.reltuples::bigint,
       pg_size_pretty(pg_relation_size(n.nspname|| '.' || c.relname)) as size,
       pg_size_pretty(pg_total_relation_size(n.nspname|| '.' || c.relname)) as total_size,
       pg_relation_size(n.nspname|| '.' || c.relname) as size_bytes,
       pg_total_relation_size(n.nspname|| '.' || c.relname) as total_size_bytes,
       CASE WHEN c.reltablespace = 0
            THEN 'pg_default'
            ELSE (SELECT t.spcname
                    FROM pg_tablespace t WHERE (t.oid = c.reltablespace) )                   
        END as tablespace
FROM pg_class c
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_authid a ON ( a.oid = c.relowner )
  WHERE relname = 'pg_class' ;

 
  What does that show for reltuples and total_size ?

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: pg_class (system) table increasing size.

From
Alvaro Herrera
Date:
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


Re: pg_class (system) table increasing size.

From
Kevin Grittner
Date:
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


Re: pg_class (system) table increasing size.

From
dhaval jaiswal
Date:

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. 


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. 


Due to business impact auto vacuum is off. 




Sent from Outlook




From: Kevin Grittner <kgrittn@gmail.com>
Sent: Friday, November 18, 2016 3:49 AM
To: Alvaro Herrera
Cc: dhaval jaiswal; Adrian Klaver; David G. Johnston; pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_class (system) table increasing size.
 
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

Re: pg_class (system) table increasing size.

From
Alvaro Herrera
Date:
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


Re: pg_class (system) table increasing size.

From
"David G. Johnston"
Date:
On Mon, Nov 21, 2016 at 10:34 AM, dhaval jaiswal <dhavallj@hotmail.com> wrote:

Due to business impact auto vacuum is off. 


​You won't get much sympathy if you turn this feature off.  The question you should be pondering is how to properly configure ​it for your environment.

​pg_class gets bloated for the same reasons other tables do - updates and deletes​ to rows (which here represent system objects).

David J.

Re: pg_class (system) table increasing size.

From
dhaval jaiswal
Date:

Adding to above.  Below are the outputs. 


 select count(*) from pg_stat_sys_tables  where n_tup_ins =0 ;
 count 
-------
   326
(1 row)

 select count(*) from pg_stat_sys_tables  where n_tup_upd =0 ;
 count 
-------
   358
(1 row)

 select count(*) from pg_stat_sys_tables  where n_tup_del =0 ;
 count 
-------
   344
(1 row)

 select count(*) from pg_stat_sys_tables  where n_tup_hot_upd =0 ;
 count 
-------
   358
(1 row)

 select count(*) from pg_stat_sys_tables  where n_live_tup =0 ;
 count 
-------
   326
(1 row)

 select count(*) from pg_stat_sys_tables  where n_dead_tup =0 ;
 count 
-------
   346
(1 row)




Sent from Outlook




From: pgsql-general-owner@postgresql.org <pgsql-general-owner@postgresql.org> on behalf of dhaval jaiswal <dhavallj@hotmail.com>
Sent: Monday, November 21, 2016 11:04 PM
To: Kevin Grittner; Alvaro Herrera
Cc: Adrian Klaver; David G. Johnston; pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_class (system) table increasing size.
 

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. 


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. 


Due to business impact auto vacuum is off. 




Sent from Outlook




From: Kevin Grittner <kgrittn@gmail.com>
Sent: Friday, November 18, 2016 3:49 AM
To: Alvaro Herrera
Cc: dhaval jaiswal; Adrian Klaver; David G. Johnston; pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_class (system) table increasing size.
 
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

Re: pg_class (system) table increasing size.

From
Igor Neyman
Date:

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of dhaval jaiswal
Sent: Monday, November 21, 2016 12:35 PM
To: Kevin Grittner <kgrittn@gmail.com>; Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_class (system) table increasing size.

 

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. 

 

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. 

 

Due to business impact auto vacuum is off. 

 

 

 

Sent from Outlook

 


You just stated the reason:

auto vacuum is off

 

Regards,

Igor Neyman

Re: pg_class (system) table increasing size.

From
Alvaro Herrera
Date:
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


Re: pg_class (system) table increasing size.

From
Kevin Grittner
Date:
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