Database size growing over time and leads to performance impact

Список
Период
Сортировка
От Gnanakumar
Тема Database size growing over time and leads to performance impact
Дата
Msg-id 004701cacdad$7013e9f0$503bbdd0$@com
обсуждение исходный текст
Ответы Re: Database size growing over time and leads to performance impact  (Andy Colson)
Re: Database size growing over time and leads to performance impact  ("Pierre C")
Re: Database size growing over time and leads to performance impact  (Greg Smith)
Список pgsql-performance
Дерево обсуждения
Database size growing over time and leads to performance impact  ("Gnanakumar", )
 Re: Database size growing over time and leads to performance impact  (Andy Colson, )
  Re: Database size growing over time and leads to performance impact  ("Gnanakumar", )
   Re: Database size growing over time and leads to performance impact  (Andy Colson, )
  Re: Database size growing over time and leads to performance impact  (Scott Carey, )
   Re: Database size growing over time and leads to performance impact  (Robert Haas, )
    Re: Database size growing over time and leads to performance impact  (Scott Carey, )
     Re: Database size growing over time and leads to performance impact  (Tom Lane, )
      Re: Database size growing over time and leads to performance impact  (Scott Carey, )
   Re: Database size growing over time and leads to performance impact  (Alvaro Herrera, )
 Re: Database size growing over time and leads to performance impact  ("Pierre C", )
  Re: Database size growing over time and leads to performance impact  (Greg Smith, )
 Re: Database size growing over time and leads to performance impact  (Greg Smith, )

Hi,

 

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

 

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.

 

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.

 

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?

2.     How in real time, this issues are handled by other PostgreSQL users without taking to downtime?

3.     Any ideas or links whether this is addressed in upcoming PostgreSQL version 9.0 release?

 

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Pierre C"
Дата:
Сообщение: Re: Database size growing over time and leads to performance impact
Следующее
От: Reydan Cankur
Дата:
Сообщение: Pgbench TPS Calculation