Le 27/03/2010 14:00, Gnanakumar a écrit : > [...] > We're using PostgreSQL 8.2. Recently, in our production database, there was > a severe performance impact.. Even though, we're regularly doing both: > > 1. VACUUM FULL ANALYZE once in a week during low-usage time and > > 2. ANALYZE everyday at low-usage time > Which means you can be sure you have bloated indexes. > Also, we noticed that the physical database size has grown upto 30 GB. But, > if I dump the database in the form of SQL and import it locally in my > machine, it was only 3.2 GB. Then while searching in Google to optimize > database size, I found the following useful link: > > http://www.linuxinsight.com/optimize_postgresql_database_size.html > > It says that even vacuumdb or reindexdb doesn't really compact database > size, only dump/restore does because of MVCC architecture feature in > PostgreSQL and this has been proven here. > VACUUM doesn't compact a database. VACUUM FULL does for tables. REINDEX does for index. And this is why, I think, you have an issue. You do VACUUM FULL each week, but don't do a REINDEX. > So, finally we decided to took our production database offline and performed > dump/restore. After this, the physical database size has also reduced from > 30 GB to 3.5 GB and the performance was also very good than it was before. > Not surprising, indexes are recreated. > Physical database size was found using the following command: > > du -sh /usr/local/pgsql/data/base/<database-oid> > > I also cross-checked this size using > "pg_size_pretty(pg_database_size(datname))". > > Questions > > 1. Is there any version/update of PostgreSQL addressing this issue? > If you still want to use VACUUM FULL, then you need to use REINDEX. But you shouldn't need VACUUM FULL. Configure autovacuum so that your tables don't get bloated. > 2. How in real time, this issues are handled by other PostgreSQL users > without taking to downtime? > Using the autovacuum to VACUUM and ANALYZE when it's really needed. > 3. Any ideas or links whether this is addressed in upcoming PostgreSQL > version 9.0 release? > -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
