From: Pavel Rotek <pavel.rotek@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Toast space grows
Date: Fri, 7 Mar 2008 15:11:55 +0100
Message-ID: <3556c08c0803070611p542eb317vfaf9b783172b6c3@mail.gmail.com> (raw)
In-Reply-To: <11848.1204896799@sss.pgh.pa.us>
References: <3556c08c0803070035p6d8b4aa8m6318f54728f61022@mail.gmail.com>
<47D10722.4080508@archonet.com>
<3556c08c0803070129y6ffabf32i1c8bae445ec460e0@mail.gmail.com>
<20080307065314.1248665b.wmoran@collaborativefusion.com>
<3556c08c0803070459i8598423t64e0704e48af533@mail.gmail.com>
<11848.1204896799@sss.pgh.pa.us>
2008/3/7, Tom Lane <tgl@sss.pgh.pa.us>:
>
> "Pavel Rotek" <pavel.rotek@gmail.com> writes:
> > No i do not mean long running transactions... Update of log entry
> (update of
> > row in dataaction) is performed in series of short transactions, but
> during
> > short transaction there is a lot of change log value, flush, change log
> > value ,flush ..... change log value, flush actions (flush means perform
> > flush operation via JDBC driver). I'm not sure if this flush affects
> toast
> > space... Maybe this is the reason.
>
>
> You mean that you build up the 5MB log entry by adding a few lines at a
> time? That's going to consume horrid amounts of toast space, because
> each time you add a few lines, an entire new toasted field value is
> created.
well, this will be the main problem... But... do uncomitted trasactions
affect toast space?
If you have to do it that way, you'll need very frequent vacuums on this
> table (not vacuum full, as noted already) to keep the toast space from
> bloating too much. And make sure you've got max_fsm_pages set high
> enough.
i'll set max_fsm_pages to 1 000 000. It should be enough and set
autovacuum_naptime to 10 minutes. May it be?
If you can restructure your code a bit, it might be better to accumulate
> log values in a short-lived table and only store the final form of a log
> entry into the main table.
I'll try to refactor the code... My application do following thing... long
running jobs (for example long imports) are broken into series of short
transactions to store snapshot of current state of long running job. Short
transaction consist of
(begin tx, load previous log, do business action, append new log, flush, do
business action, append new log, flush, ... do business action, append new
log, flush, commit tx). Is it enough to avoid multiple "append new log,
flush" in one short transaction and keep log changes for short transaction
in the buffer (only one update of log attribute at the end of transaction)?
From your answer probably not, but i ask for sure, it will be less work. Or
store logs for each one partial transaction and concat all at the end of
long running job??
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pavel.rotek@gmail.com
Subject: Re: Toast space grows
In-Reply-To: <3556c08c0803070611p542eb317vfaf9b783172b6c3@mail.gmail.com>
* 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