Re: more about pg_toast growth
От | John Gray |
---|---|
Тема | Re: more about pg_toast growth |
Дата | |
Msg-id | 1016017619.1326.13.camel@adzuki обсуждение исходный текст |
Ответ на | more about pg_toast growth ("Jeffrey W. Baker" <jwb@saturn5.com>) |
Список | pgsql-general |
Caveat: Because it's otherwise quiet, I'm trying to be helpful. If you already have someone more knowledgeable than I on the case, please don't feel you need to respond! On Tue, 2002-03-12 at 21:30, 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? > TOAST compresses data where possible -if your data is English text, then that seems like a reasonable compression factor. > 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. > This implies to me that there are some updates going on against the table. Presumably the sum(length(chunk_data)) hasn't increased during the same period? I should point out that although I know how TOAST is implemented, I have no real knowledge of lazy vacuum and the free space map. I notice that ordinary 'lazy' vacuum will only attempt a truncation if it thinks it can reclaim at least 1/16 of the relation size. Furthermore, it does need an exclusive lock to do this. You don't have any process that performs lemgthy operations on resp_body which would prevent vacuum from getting its lock? -if it can't get the lock, it will just give up rather than blocking. Perhaps in this case, a message could be printed in the vacuum verbose output - "Could not get exclusive lock on relation -not truncating" - to distinguish this case from the case where the relation is not seen as a good candidate for attempting truncation. > The free space map settings in postgresql.conf are commented out. > This implies they'll have their default values. How many tables in your database get touched in your update process? What is the ballpark volume of data that gets updated/inserted per day? > 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. > LZ Compression, as mentioned above. You may wish to wait for a better answer before doing anything drastic with your DB! Regards John
В списке pgsql-general по дате отправления: