SEO

#1 (**permalink**) 04-10-2008, 05:04 AM

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

I was wondering if I could use CREATE TEMPORARY TABLE <tblname> ... ON
COMMIT PRESERVE ROWS to basically defer the commit of changes to hard disk
until I say 'COMMIT'. The database is non-logging; don't know if use of
COMMIT will be affected by that or not.

If the above won't get me what I want, does anyone have suggestions of
another way to get the same effect? The effect I want is a temporary table
that can be updated in RAM, but not create a lot of I/O activity to the
disk.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

#2 (**permalink**) 04-10-2008, 05:04 AM

"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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Defer Committing Updates on High-Activity Table