pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Lane Van Ingen <lvaningen@esncc.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Defer Committing Updates on High-Activity Table
Date: Wed, 03 Aug 2005 15:39:00 -0400
Message-ID: <3433.1123097940@sss.pgh.pa.us> (raw)
In-Reply-To: <EKEMKEFLOMKDDLIALABIOEAFCBAA.lvaningen@esncc.com>
References: <EKEMKEFLOMKDDLIALABIOEAFCBAA.lvaningen@esncc.com>

"Lane Van Ingen" <lvaningen@esncc.com> writes:
> I have a situation where I have a temporary table (because of how it is
> being used) that creates a lot of (legitimate) I/O activity. The table
> contains information which is transient and tends to stay in shared buffers
> because of frequency of use, and it is only necessary to commit the changes
> to disk every hour or so (mainly for backup purposes).

If it's actually a temp table, then the data isn't in shared buffers;
it's kept in backend-local buffers, and isn't written to disk at all
unless the backend needs to evict a page from those buffers to make
room for more temp data.

What you probably really need is to enlarge the number of buffers
available for temp-table data.  This number is user-configurable
as of Postgres 8.1, but unfortunately is hard-wired at a pretty
small value in existing releases.  If you're desperate you could
try increasing NLocBuffer in src/backend/storage/buffer/localbuf.c,
but I'm not sure how well that will work --- the pre-8.1 code is
not designed to perform well with large values of NLocBuffer.

			regards, tom lane



view thread (2+ messages)

Message-ID: <3433.1123097940@sss.pgh.pa.us>
Permalink:  ../3433.1123097940@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3433.1123097940@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, lvaningen@esncc.com
  Subject: Re: Defer Committing Updates on High-Activity Table
  In-Reply-To: <3433.1123097940@sss.pgh.pa.us>
  References: <EKEMKEFLOMKDDLIALABIOEAFCBAA.lvaningen@esncc.com> <3433.1123097940@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