Hi Peter, Sounds like you're experiencing index bloat and vacuums do nothing to help that. You can do one of 2 thing to remedy this: 1) The fastest and simplest (but most disruptive) way is to use REINDEX. But this will exclusively lock the table while rebuilding the indexes: REINDEX TABLE phaseangle; 2) The slower but less disruptive way is to do a concurrent build of each index and then drop the old ones. For example, to rebuild the "i" index: CREATE INDEX CONCURRENTLY i_new ON phaseangle (<indexed columns>); DROP INDEX i; ALTER INDEX i_new RENAME TO i; ANALYZE phaseangle (<indexed columns>); Do this regularly to keep the index sizes in check. - Chris Peter Meszaros wrote: > 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: > > 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
