Re: [GENERAL] pg_class (system) table increasing size.

2016-11-21 Thread Kevin Grittner 
On Mon, Nov 21, 2016 at 11:34 AM, dhaval jaiswal  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_class (system) table increasing size.

2016-11-21 Thread Alvaro Herrera 
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 Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_class (system) table increasing size.

2016-11-21 Thread Igor Neyman 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of dhaval jaiswal
Sent: Monday, November 21, 2016 12:35 PM
To: Kevin Grittner <kgri...@gmail.com>; Alvaro Herrera 
<alvhe...@2ndquadrant.com>
Cc: Adrian Klaver <adrian.kla...@aklaver.com>; David G. Johnston 
<david.g.johns...@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<http://aka.ms/weboutlook>


You just stated the reason:
"auto vacuum is off"

Regards,
Igor Neyman

Re: [GENERAL] pg_class (system) table increasing size.

2016-11-21 Thread dhaval jaiswal 
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<http://aka.ms/weboutlook>



From: pgsql-general-ow...@postgresql.org <pgsql-general-ow...@postgresql.org> 
on behalf of dhaval jaiswal <dhava...@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<http://aka.ms/weboutlook>



From: Kevin Grittner <kgri...@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
<alvhe...@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: [GENERAL] pg_class (system) table increasing size.

2016-11-21 Thread David G. Johnston 
On Mon, Nov 21, 2016 at 10:34 AM, dhaval jaiswal 
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: [GENERAL] pg_class (system) table increasing size.

2016-11-21 Thread Alvaro Herrera 
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 Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_class (system) table increasing size.

2016-11-21 Thread dhaval jaiswal 
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<http://aka.ms/weboutlook>



From: Kevin Grittner <kgri...@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
<alvhe...@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: [GENERAL] pg_class (system) table increasing size.

2016-11-17 Thread Kevin Grittner 
On Thu, Nov 17, 2016 at 3:16 PM, Alvaro Herrera
 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_class (system) table increasing size.

2016-11-17 Thread Alvaro Herrera 
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 Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_class (system) table increasing size.

2016-11-17 Thread Melvin Davidson 
On Thu, Nov 17, 2016 at 1:33 PM, dhaval jaiswal <dhava...@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.kla...@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.kla...@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 =
0THEN 'pg_default'ELSE (SELECT t.spcname
FROM pg_tablespace t WHERE (t.oid = c.reltablespace)
)END as tablespaceFROM 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: [GENERAL] pg_class (system) table increasing size.

2016-11-17 Thread dhaval jaiswal 
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.kla...@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.kla...@aklaver.com

Re: [GENERAL] pg_class (system) table increasing size.

2016-11-17 Thread Adrian Klaver 

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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_class (system) table increasing size.

2016-11-17 Thread Adrian Klaver 

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.johns...@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 <dhava...@hotmail.com
<mailto:dhava...@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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_class (system) table increasing size.

2016-11-16 Thread dhaval jaiswal 

>> 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.johns...@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 
<dhava...@hotmail.com<mailto:dhava...@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.

Re: [GENERAL] pg_class (system) table increasing size.

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