Re: database size growing continously

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.

Ludwik

>
> phasor=# vacuum VERBOSE phaseangle;
> INFO: vacuuming "public.phaseangle"
> INFO: scanned index "i" to remove 2796006 row versions
> DETAIL: CPU 9.49s/120.30u sec elapsed 224.20 sec.
> INFO: scanned index "t" to remove 2796006 row versions
> DETAIL: CPU 13.57s/105.70u sec elapsed 192.71 sec.
> INFO: "phaseangle": removed 2796006 row versions in 24748 pages
> DETAIL: CPU 0.65s/0.30u sec elapsed 39.97 sec.
> INFO: scanned index "i" to remove 2795924 row versions
> DETAIL: CPU 9.58s/121.63u sec elapsed 239.06 sec.
> INFO: scanned index "t" to remove 2795924 row versions
> DETAIL: CPU 13.10s/103.59u sec elapsed 190.84 sec.
> INFO: "phaseangle": removed 2795924 row versions in 24743 pages
> DETAIL: CPU 0.68s/0.28u sec elapsed 40.21 sec.
> INFO: scanned index "i" to remove 2796014 row versions
> DETAIL: CPU 9.65s/117.28u sec elapsed 231.92 sec.
> INFO: scanned index "t" to remove 2796014 row versions
> DETAIL: CPU 13.48s/103.59u sec elapsed 194.49 sec.
> INFO: "phaseangle": removed 2796014 row versions in 24774 pages
> DETAIL: CPU 0.69s/0.28u sec elapsed 40.26 sec.
> INFO: scanned index "i" to remove 2795935 row versions
> DETAIL: CPU 9.55s/119.02u sec elapsed 226.85 sec.
> INFO: scanned index "t" to remove 2795935 row versions
> DETAIL: CPU 13.09s/102.84u sec elapsed 194.74 sec.
> INFO: "phaseangle": removed 2795935 row versions in 25097 pages
> DETAIL: CPU 0.67s/0.28u sec elapsed 41.21 sec.
>
> still running...
>
> These are the very same problems?
> Should I delete mor frequently in smaller chunks? It seems to have a
> limit...
>
> Thanks
>
> Peter
>
> --
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

--
Ludwik Dyląg