How to avoid database bloat

From: "Mindaugas Riauba" <mind(at)bi(dot)lt>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: How to avoid database bloat
Date: 2005-06-01 07:43:06
Message-ID: 00d501c5667d$8cc12320$f20214ac@bite.lt
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


Hello,

Our database increases in size 2.5 times during the day.
What to do to avoid this? Autovacuum running with quite
aggressive settings, FSM settings are high enough.

Database size should be more or less constant but it
has high turnover rate (100+ insert/update/delete per second).

Below is "du -sk" of database dir during the day. On 4:05
full vacuum+reindex runs and database size is once again
reduced.

Thanks,

Mindaugas

Tue May 31 11:00:01 EEST 2005
533808 /ora/pgsql/base/465436/
Tue May 31 11:30:01 EEST 2005
567344 /ora/pgsql/base/465436/
Tue May 31 12:00:01 EEST 2005
578632 /ora/pgsql/base/465436/
Tue May 31 12:30:01 EEST 2005
586336 /ora/pgsql/base/465436/
Tue May 31 13:00:01 EEST 2005
594716 /ora/pgsql/base/465436/
Tue May 31 13:30:01 EEST 2005
604932 /ora/pgsql/base/465436/
Tue May 31 14:00:01 EEST 2005
613668 /ora/pgsql/base/465436/
Tue May 31 14:30:01 EEST 2005
625752 /ora/pgsql/base/465436/
Tue May 31 15:00:01 EEST 2005
637704 /ora/pgsql/base/465436/
Tue May 31 15:30:01 EEST 2005
649700 /ora/pgsql/base/465436/
Tue May 31 16:00:01 EEST 2005
657392 /ora/pgsql/base/465436/
Tue May 31 16:30:02 EEST 2005
668228 /ora/pgsql/base/465436/
Tue May 31 17:00:01 EEST 2005
676332 /ora/pgsql/base/465436/
Tue May 31 17:30:01 EEST 2005
686376 /ora/pgsql/base/465436/
Tue May 31 18:00:01 EEST 2005
694080 /ora/pgsql/base/465436/
Tue May 31 18:30:02 EEST 2005
705876 /ora/pgsql/base/465436/
Tue May 31 19:00:01 EEST 2005
713916 /ora/pgsql/base/465436/
Tue May 31 19:30:01 EEST 2005
725460 /ora/pgsql/base/465436/
Tue May 31 20:00:01 EEST 2005
733892 /ora/pgsql/base/465436/
Tue May 31 20:30:01 EEST 2005
745344 /ora/pgsql/base/465436/
Tue May 31 21:00:01 EEST 2005
753048 /ora/pgsql/base/465436/
Tue May 31 21:30:02 EEST 2005
768228 /ora/pgsql/base/465436/
Tue May 31 22:00:01 EEST 2005
804796 /ora/pgsql/base/465436/
Tue May 31 22:30:01 EEST 2005
858840 /ora/pgsql/base/465436/
Tue May 31 23:00:02 EEST 2005
902684 /ora/pgsql/base/465436/
Tue May 31 23:30:01 EEST 2005
939796 /ora/pgsql/base/465436/
Wed Jun 1 00:00:02 EEST 2005
990840 /ora/pgsql/base/465436/
Wed Jun 1 00:30:11 EEST 2005
1005316 /ora/pgsql/base/465436/
Wed Jun 1 01:00:02 EEST 2005
1011408 /ora/pgsql/base/465436/
Wed Jun 1 01:30:01 EEST 2005
1010888 /ora/pgsql/base/465436/
Wed Jun 1 02:00:01 EEST 2005
1010872 /ora/pgsql/base/465436/
Wed Jun 1 02:30:01 EEST 2005
1010784 /ora/pgsql/base/465436/
Wed Jun 1 03:00:02 EEST 2005
1003260 /ora/pgsql/base/465436/
Wed Jun 1 03:30:02 EEST 2005
1003372 /ora/pgsql/base/465436/
Wed Jun 1 04:00:01 EEST 2005
1003380 /ora/pgsql/base/465436/
Wed Jun 1 04:30:01 EEST 2005
426508 /ora/pgsql/base/465436/
Wed Jun 1 05:00:01 EEST 2005
429036 /ora/pgsql/base/465436/
Wed Jun 1 05:30:01 EEST 2005
432156 /ora/pgsql/base/465436/
Wed Jun 1 06:00:01 EEST 2005
433332 /ora/pgsql/base/465436/
Wed Jun 1 06:30:01 EEST 2005
435052 /ora/pgsql/base/465436/
Wed Jun 1 07:00:02 EEST 2005
439908 /ora/pgsql/base/465436/
Wed Jun 1 07:30:01 EEST 2005
450144 /ora/pgsql/base/465436/
Wed Jun 1 08:00:01 EEST 2005
471120 /ora/pgsql/base/465436/
Wed Jun 1 08:30:02 EEST 2005
490712 /ora/pgsql/base/465436/
Wed Jun 1 09:00:01 EEST 2005
501652 /ora/pgsql/base/465436/
Wed Jun 1 09:30:01 EEST 2005
530128 /ora/pgsql/base/465436/
Wed Jun 1 10:00:01 EEST 2005
541580 /ora/pgsql/base/465436/
Wed Jun 1 10:30:01 EEST 2005
571204 /ora/pgsql/base/465436/

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message stig erikson 2005-06-01 08:13:02 Re: How to avoid database bloat
Previous Message Mark Kirkwood 2005-06-01 07:22:42 Re: postgresql-8.0.1 performance tuning