pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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(); }

view thread (19+ messages)  latest in thread

Message-ID: <1452.1112311390@sss.pgh.pa.us>
Permalink:  ../1452.1112311390@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1452.1112311390@sss.pgh.pa.us

 ·  · 

reply

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