Re: Performance, vacuum and reclaiming space, fsm

From: Seum-Lim Gan <slgan(at)lucent(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance, vacuum and reclaiming space, fsm
Date: 2003-10-13 13:52:05
Message-ID: p05100303bbb05d6f27dd@[192.168.10.43]
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


I am not sure I can do the full vacuum.
If my system is doing updates in realtime and needs to be
ok 24 hours and 7 days a week non-stop, once I do
vacuum full, even on that table, that table will
get locked out and any quiery or updates that come in
will timeout.

Any suggestion on what to do besides shutting down to
do full vacuum ?

Peter Child also mentions there is indexing bugs.
Is this fixed in 7.3.4 ? I did notice after the database
grew in disk usage, its performance greatly decreases !

Gan

>Seum-Lim Gan wrote:
>>I have a table that keeps being updated and noticed
>>that after a few days, the disk usage has growned to
>>from just over 150 MB to like 2 GB !
>
>Hmm... You have quite a lot of wasted space there..
>>
>>I followed the recommendations from the various search
>>of the archives, changed the max_fsm_relations, pages,
>>keep doing vacuum like every minute while the
>>table of interest in being updated. I kept
>>watching the disk space usage and still noticed that
>>it continues to increase.
>
>That will help if your table is in good shape. Otherwise it will
>have little effect particularly after such amount of wasted space.
>
>>Looks like vacuum has no effect.
>
>Its not that.
>
>>I did vacuum tablename and don't intend to use
>>the full option since it locks the table.
>
>You got to do that. simple vacuum keeps a running instance of server
>clean. But once dead tuples spill to disk, nothing but vacumm full
>can reclaim that space.
>
>And don't forget, you got to reindex the indexes as well.
>
>Once your table is in good shape, you can tune max_fsm_* and vacuum
>once a minute. That will keep it good..
>
>>I have 7.3.3 running in Solaris 9.
>>
>>Any recommendation ?
>
> HTH
>
> Shridhar

--
+--------------------------------------------------------+
| Seum-Lim GAN email : slgan(at)lucent(dot)com |
| Lucent Technologies |
| 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 |
| Naperville, IL 60566, USA. fax : (630)-713-7272 |
| web : http://inuweb.ih.lucent.com/~slgan |
+--------------------------------------------------------+

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Vivek Khera 2003-10-13 14:04:35 Re: go for a script! / ex: PostgreSQL vs. MySQL
Previous Message Vivek Khera 2003-10-13 13:35:17 Re: further testing on IDE drives