Thread: Re: [ADMIN] Excessive growth of pg_attribute and other system tables

Steve Crawford <scrawford@pinpointresearch.com> writes:
> On Monday 21 March 2005 11:40 am, Tom Lane wrote:
>> However, given that there are 9334 tuples in 82282 pages, I'd say
>> that autovacuum has already failed Steve rather badly :-(.  There
>> shouldn't be more than a couple hundred pages given that number of
>> rows.  Perhaps the FSM settings are too small?

> Results time. FSM settings were too small but the real problem seems 
> to be that pg_autovacuum isn't getting the job done.

The light just went on ... system catalog updates don't generate
statistics reports.  Hence, autovacuum doesn't know any work is needed.

Should we fix that, or change autovacuum to special-case the system
catalogs somehow, or ???
        regards, tom lane

I wrote:
> The light just went on ... system catalog updates don't generate
> statistics reports.  Hence, autovacuum doesn't know any work is needed.

The above claim is too strong --- they do normally generate stats
updates.  However, in a simple test I observed that
pg_stat_all_tables.n_tup_del did not seem to increment for the deletes
that occur when a temp table is dropped during backend exit.  (Most
likely we aren't flushing out the final stats messages...)

Steve, is your app in the habit of creating lots of temp tables that are
not dropped explicitly?  That would explain why you are getting bit more
than other people.
        regards, tom lane

On Thursday 31 March 2005 12:06 pm, Tom Lane wrote:
> I wrote:
> > The light just went on ... system catalog updates don't generate
> > statistics reports.  Hence, autovacuum doesn't know any work is
> > needed.
>
> The above claim is too strong --- they do normally generate stats
> updates.  However, in a simple test I observed that
> pg_stat_all_tables.n_tup_del did not seem to increment for the
> deletes that occur when a temp table is dropped during backend
> exit.  (Most likely we aren't flushing out the final stats
> messages...)
>
> Steve, is your app in the habit of creating lots of temp tables
> that are not dropped explicitly?  That would explain why you are
> getting bit more than other people.

Yes, various processes create in total well over 100 temporary tables 
every hour. None of them are explicitly dropped.

Cheers,
Steve

Tom Lane wrote:

>Steve Crawford <scrawford@pinpointresearch.com> writes:
>  
>
>>On Monday 21 March 2005 11:40 am, Tom Lane wrote:
>>    
>>
>>>However, given that there are 9334 tuples in 82282 pages, I'd say
>>>that autovacuum has already failed Steve rather badly :-(.  There
>>>shouldn't be more than a couple hundred pages given that number of
>>>rows.  Perhaps the FSM settings are too small?
>>>      
>>>
>
>  
>
>>Results time. FSM settings were too small but the real problem seems 
>>to be that pg_autovacuum isn't getting the job done.
>>    
>>
>
>The light just went on ... system catalog updates don't generate
>statistics reports.  Hence, autovacuum doesn't know any work is needed.
>
>Should we fix that, or change autovacuum to special-case the system
>catalogs somehow, or ???
>  
>

Really?!?!?!?  Wow, if that is true, that is a big gaping hole in the 
autovacuum design.  Is that true for all types of system catalog 
updates?  The reason I ask is that the stats system is reporting at 
least some of activity on pg_attribute in this example.  So why would it 
report some but not all?

Is there any chance fixing the stats system to include system catalog 
updates would be simple enough to put into the 8.0.x branch?  I don't 
know a another way for pg_autovacuum know if it's time for an vacuum.

Hopefully the autovacuum in 8.1 will be a fairly different animal.

Steve Crawford <scrawford@pinpointresearch.com> writes:
> On Thursday 31 March 2005 12:06 pm, Tom Lane wrote:
>> Steve, is your app in the habit of creating lots of temp tables
>> that are not dropped explicitly?  That would explain why you are
>> getting bit more than other people.

> Yes, various processes create in total well over 100 temporary tables 
> every hour. None of them are explicitly dropped.

Try this patch (it's against 8.0, but applies with some fuzz to 7.4
too).
        regards, tom lane

Index: pgstat.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.87.4.1
diff -c -r1.87.4.1 pgstat.c
*** pgstat.c    25 Mar 2005 00:35:14 -0000    1.87.4.1
--- pgstat.c    31 Mar 2005 23:12:21 -0000
***************
*** 160,165 ****
--- 160,166 ---- static void pgstat_recvbuffer(void); static void pgstat_exit(SIGNAL_ARGS); static void
pgstat_die(SIGNAL_ARGS);
+ static void pgstat_beshutdown_hook(int code, Datum arg);  static int    pgstat_add_backend(PgStat_MsgHdr *msg);
staticvoid pgstat_sub_backend(int procpid);
 
***************
*** 670,675 ****
--- 671,695 ----      pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_BESTART);     pgstat_send(&msg, sizeof(msg));
+ 
+     /*
+      * Set up a process-exit hook to ensure we flush the last batch of
+      * statistics to the collector.
+      */
+     on_proc_exit(pgstat_beshutdown_hook, 0);
+ }
+ 
+ /*
+  * Flush any remaining statistics counts out to the collector at process
+  * exit.   Without this, operations triggered during backend exit (such as
+  * temp table deletions) won't be counted.  This is an on_proc_exit hook,
+  * not on_shmem_exit, so that everything interesting must have happened
+  * already.
+  */
+ static void
+ pgstat_beshutdown_hook(int code, Datum arg)
+ {
+     pgstat_report_tabstat(); }  

"Tom Lane" <tgl@sss.pgh.pa.us> writes
> Steve Crawford <scrawford@pinpointresearch.com> writes:
> > On Monday 21 March 2005 11:40 am, Tom Lane wrote:
> >> However, given that there are 9334 tuples in 82282 pages, I'd say
> >> that autovacuum has already failed Steve rather badly :-(.  There
> >> shouldn't be more than a couple hundred pages given that number of
> >> rows.  Perhaps the FSM settings are too small?
>

Seems this is another question pointing to the inproper setting of
"can-be-avoided" shared memory parameters. Maybe we should eliminate GUC
parameters related to the FSM. Can we follow Alvaro's idea like spilling
some data of FSM into disk while keeping the indices and maybe part of data
in the memory? So no free page would be discarded due to no space to record
them in FSM? Also, in this handling, efficiency should not be a problem.

Regards,
Qingqing