Re: [HACKERS] [ADMIN] Excessive growth of pg_attribute and other system tables
Steve Crawford [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [ADMIN] Excessive growth of pg_attribute and other system tables
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [ADMIN] Excessive growth of pg_attribute and other system tables
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [ADMIN] Excessive growth of pg_attribute and other system tables
Tom Lane wrote: Steve Crawford [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [ADMIN] Excessive growth of pg_attribute and other system tables
Steve Crawford [EMAIL PROTECTED] 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.c25 Mar 2005 00:35:14 - 1.87.4.1
--- pgstat.c31 Mar 2005 23:12:21 -
***
*** 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 intpgstat_add_backend(PgStat_MsgHdr *msg);
static void 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();
}
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] [ADMIN] Excessive growth of pg_attribute and other system tables
Tom Lane [EMAIL PROTECTED] writes Steve Crawford [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
