Re: database size growing continously
От
Ludwik Dylag
Тема
Re: database size growing continously
Дата
Msg-id
2fe468a20910290900k1e6e90eawd902db72997cc911@mail.gmail.com
Ответ на
database size growing continously (Peter Meszaros)
Список
Дерево обсуждения
database size growing continously Peter Meszaros <pme@prolan.hu>
Re: database size growing continously Josh Rovero <rovero@sonalysts.com>
Re: database size growing continously Matthew Wakeling <matthew@flymine.org>
Re: database size growing continously Ludwik Dylag <ldylag@gmail.com>
Re: database size growing continously Ludwik Dylag <ldylag@gmail.com>
Re: database size growing continously "Joshua D. Drake" <jd@commandprompt.com>
Re: database size growing continously "Joshua D. Drake" <jd@commandprompt.com>
Re: database size growing continously Chris Ernst <cernst@esoft.com>
Re: database size growing continously Steve Crawford <scrawford@pinpointresearch.com>
Re: database size growing continously Steve Crawford <scrawford@pinpointresearch.com>
Re: database size growing continously Merlin Moncure <mmoncure@gmail.com>
Re: database size growing continously Jeremy Harris <jgh@wizmail.org>
Re: database size growing continously Anj Adu <fotographs@gmail.com>
Re: database size growing continously Greg Stark <gsstark@mit.edu>
Re: database size growing continously Jeremy Harris <jgh@wizmail.org>
Re: database size growing continously Greg Stark <gsstark@mit.edu>
Re: database size growing continously Anj Adu <fotographs@gmail.com>
Re: database size growing continously Scott Marlowe <scott.marlowe@gmail.com>
Re: database size growing continously Peter Meszaros <pme@prolan.hu>
Re: database size growing continously Robert Haas <robertmhaas@gmail.com>
Re: database size growing continously Anj Adu <fotographs@gmail.com>
2009/10/29 Peter Meszaros <pme@prolan.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
--
Ludwik Dyląg
В списке pgsql-performance по дате отправления
