Re: truncate a table instead of vaccum full when count(*) is 0
От | Pomarede Nicolas |
---|---|
Тема | Re: truncate a table instead of vaccum full when count(*) is 0 |
Дата | |
Msg-id | Pine.LNX.4.64.0705081210540.22289@localhost обсуждение исходный текст |
Ответ на | Re: truncate a table instead of vaccum full when count(*) is 0 (Guillaume Cottenceau <gc@mnc.ch>) |
Список | pgsql-performance |
On Tue, 8 May 2007, Guillaume Cottenceau wrote: > Pomarede Nicolas <npomarede 'at' corp.free.fr> writes: > >> Hello to all, >> >> I have a table that is used as a spool for various events. Some >> processes write data into it, and another process reads the resulting >> rows, do some work, and delete the rows that were just processed. >> >> As you can see, with hundreds of thousands events a day, this table >> will need being vaccumed regularly to avoid taking too much space >> (data and index). >> >> Note that processing rows is quite fast in fact, so at any time a >> count(*) on this table rarely exceeds 10-20 rows. >> >> >> For the indexes, a good way to bring them to a size corresponding to >> the actual count(*) is to run 'reindex'. >> >> But for the data (dead rows), even running a vacuum analyze every day >> is not enough, and doesn't truncate some empty pages at the end, so >> the data size remains in the order of 200-300 MB, when only a few >> effective rows are there. > > As far as I know, you probably need to increase your > max_fsm_pages, because your pg is probably not able to properly > track unused pages between subsequent VACUUM's. > > http://www.postgresql.org/docs/8.2/interactive/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM > > Have you investigated this? It seems that you already know about > the FSM stuff, according to your question about FSM and 8.3. > > You can also run VACUUM ANALYZE more frequently (after all, it > doesn't lock the table). thanks, but max FSM is already set to a large enough value (I'm running a vacuum analyze every day on the whole database, and set max fsm according to the last lines of vacuum, so all pages are stored in the FSM). Nicolas
В списке pgsql-performance по дате отправления: