X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 72DF553AF5 for ; Fri, 1 Apr 2005 00:23:12 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 07092-06 for ; Thu, 31 Mar 2005 23:23:09 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 13E83534B9 for ; Fri, 1 Apr 2005 00:23:08 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2VNNAov001453; Thu, 31 Mar 2005 18:23:10 -0500 (EST) To: Steve Crawford Cc: "Matthew T. O'Connor" , Jan Wieck , pgsql-hackers@postgresql.org Subject: Re: [ADMIN] Excessive growth of pg_attribute and other system tables In-reply-to: <200503311244.15055.scrawford@pinpointresearch.com> References: <200503171515.38214.scrawford@pinpointresearch.com> <7739.1112295848@sss.pgh.pa.us> <14686.1112299573@sss.pgh.pa.us> <200503311244.15055.scrawford@pinpointresearch.com> Comments: In-reply-to Steve Crawford message dated "Thu, 31 Mar 2005 12:44:15 -0800" Date: Thu, 31 Mar 2005 18:23:10 -0500 Message-ID: <1452.1112311390@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/1 X-Sequence-Number: 65870 Steve Crawford 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); 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(); }