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
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