Re: database size growing continously

On Thu, 2009-10-29 at 17:00 +0100, Ludwik Dylag wrote:
> 2009/10/29 Peter Meszaros <pme(at)prolan(dot)hu>
> Hi All,
>
> I use postgresql 8.3.7 as a huge queue. There is a very simple
> table
> with six columns and two indices, and about 6 million records
> are
> written into it in every day continously commited every 10
> seconds from
> 8 clients. The table stores approximately 120 million records,
> because a
> cron job daily deletes those ones are older than 20 day.
> Autovacuum is
> on and every settings is the factory default except some
> unrelated ones
> (listen address, authorization). But my database is growing,
> characteristically ~600MByte/day, but sometimes much slower
> (eg. 10MB,
> or even 0!!!).
>
> I've also tried a test on another server running the same
> postgresql,
> where 300 million record was loaded into a freshly created
> database,
> and 25 million was deleted with single DELETE command. The
> 'vacuum
> verbose phaseangle;' command seems to be running forever for
> hours:
>
>
> Try increasing max_fsm_pages and shared_buffers
> These changes did speed up vacuum full on my database.
> With shared_buffers remember to increase max shm in your OS.

If you overran your max_fsm_pages you are going to have indexes that are
not properly cleaned up, even after a vacuum full. You will need to
cluster or reindex.

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander