Re: more about pg_toast growth
От | Jeffrey W. Baker |
---|---|
Тема | Re: more about pg_toast growth |
Дата | |
Msg-id | 1016043338.5966.7.camel@heat обсуждение исходный текст |
Ответ на | Re: more about pg_toast growth (Jan Wieck <janwieck@yahoo.com>) |
Ответы |
Re: more about pg_toast growth
|
Список | pgsql-general |
On Wed, 2002-03-13 at 07:22, Jan Wieck wrote: > 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? It is gzipped and base64-encoded text. It's somewhat strange that a fast LZ would deflate it very much, but I guess it must be an artifact of the base64. The initial gzip tends to deflate the data by about 90%. > 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. I will happily do so. What is the argument against increasing the free space map? Does it consume more memory? The machine has 4GB main memory, and I wouln't notice increased consumption of a few megabytes. What triggers storage in the toast tables? Is it because of a tuple longer than some threshold? -jwb
В списке pgsql-general по дате отправления: