From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steve Crawford <scrawford@pinpointresearch.com>
Cc: Matthew T. O'Connor <matthew@zeut.net>
Cc: Jan Wieck <JanWieck@Yahoo.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [ADMIN] Excessive growth of pg_attribute and other system tables
Date: Thu, 31 Mar 2005 18:23:10 -0500
Message-ID: <1452.1112311390@sss.pgh.pa.us> (raw)
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>
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
inline patch 1: pgstat.c (+20/-0 lines) download inline-1.patchIndex: 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();
}
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, scrawford@pinpointresearch.com, matthew@zeut.net, JanWieck@Yahoo.com, pgsql-hackers@postgresql.org
Subject: Re: [ADMIN] Excessive growth of pg_attribute and other system tables
In-Reply-To: <1452.1112311390@sss.pgh.pa.us>
References: <200503171515.38214.scrawford@pinpointresearch.com> <7739.1112295848@sss.pgh.pa.us> <14686.1112299573@sss.pgh.pa.us> <200503311244.15055.scrawford@pinpointresearch.com> <1452.1112311390@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox