Re: more about pg_toast growth

Jeffrey W. Baker wrote:
> I have more data about unchecked growth of pg_toast tables. After
> another day's worth of inserting and deleting, the table in question has
> 39504 tuples. The pg_toast table has 234773 tuples, and 126697 are
> marked as unused. The sum(length(chunk_data)) from pg_toast is
> 433165242. The sum(length(resp_body)) -- the actual data in the table
> -- is much bigger: 921615964. How is that possible?
>
> In any case it is clear that the table is just growing again. The file
> increased from 420MB to 730MB overnight, without a corresponding
> increase in tuples.
>
> The free space map settings in postgresql.conf are commented out.
>
> I'd be very interested to find out how the sum of the length of the
> tuples can be much larger than both the sum of lengths from the toast
> table and the actual size of the file.

Remember, TOAST doesn't only come in slices, don't you
usually brown it? Meaning, the data gets compressed (with a
lousy but really fast algorithm). What kind of data is
resp_body? 50% compression ratio ... I guess it's html,
right?

Anyway, I would suggest you increase the max_fsm_pages
parameter. Commented out parameters in the postgresql.conf
file means "default". You said you're doing about 1,000
inserts an hour and a daily bulk delete of approx. 24,000.
Assuming most of the toast tuples are contigous, that'd mean
you are freeing something like 35,000 toast pages. I would
suggest a freespace map size of 50,000 pages, to start with.
That should at least lower the growth rate. If you still see
growth, go ahead and increase it further.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com