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@Yahoo.com #



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