Thread: Performance, vacuum and reclaiming space, fsm
Hi, I did a search in the discussion lists and found several pointers about setting the max_fsm_relations and pages. 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 ! 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. Looks like vacuum has no effect. I did vacuum tablename and don't intend to use the full option since it locks the table. I have 7.3.3 running in Solaris 9. Any recommendation ? Thanks. Gan -- +--------------------------------------------------------+ | Seum-Lim GAN email : slgan@lucent.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 | +--------------------------------------------------------+
On Mon, 13 Oct 2003, Seum-Lim Gan wrote:
> Hi,
>
> I did a search in the discussion lists and found several
> pointers about setting the max_fsm_relations and pages.
>
> 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 !
>
> 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.
>
> Looks like vacuum has no effect.
>
> I did vacuum tablename and don't intend to use
> the full option since it locks the table.
>
> I have 7.3.3 running in Solaris 9.
>
> Any recommendation ?
>
> Thanks.
>
> Gan
>
Try auto_vacuum (its in the 7.4beta4 contrib directory) I find it
very useful. Often you find that every minute in fact can be a little too
often. My table updates every couple of seconds but is vacuumed
(automatically) every hmm hour.
If you have lots of overlapping vacumms and or editing connections
records may be held on to by one vacuum so the next can't do its job.
Always ensure that there is only one vacuum process. (You can't do this
easily with cron!)
I'm still using 7.3.2. 7.3.3 is sposed to have some big bugs and
7.3.4 was produced within 24 hours.(must upgrade at some point)
Oh yes Index have problems (I think this is fix in later
versions...) so you might want to try reindex.
They are all worth a try its a brief summary of what been on
preform for weeks and weeks now.
Peter Childs
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
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@lucent.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 | +--------------------------------------------------------+
On Monday 13 October 2003 19:22, Seum-Lim Gan wrote: > 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. If you have 150MB type of data as you said last time, you could take a pg_dump of database, drop the database and recreate it. By all chances it will take less time than compacting a database from 2GB to 150MB. It does involve downtime but can't help it. Thats closet you can get. > Any suggestion on what to do besides shutting down to > do full vacuum ? Drop the indexes and recreate them. While creating the index, all the updates will be blocked anyways. > Peter Child also mentions there is indexing bugs. > Is this fixed in 7.3.4 ? I did notice after the database No. It is fixed in 7.4 and 7.4 is in beta still.. > grew in disk usage, its performance greatly decreases ! Obviously that is due to unnecessary IO it has to do. Thing is your database has reached a state that is really bad for it's operation. I strongly encourage you to recreate the database from backup, from scratch, tune postgresql properly and run autovacuum daemon from 7.4 source dir. Besides that you would need to reindex nightly or per 5-6 hour depending upon rate of insertion. Check http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html for performance tuning starter tips.. HTH Shridhar
>>>>> "SD" == Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes: SD> If you have 150MB type of data as you said last time, you could SD> take a pg_dump of database, drop the database and recreate it. By SD> all chances it will take less time than compacting a database from SD> 2GB to 150MB. That's it? That's not so big of a disk footprint. SD> Drop the indexes and recreate them. While creating the index, all SD> the updates will be blocked anyways. Be *very careful* doing this, especially with UNIQUE indexes on a live system! My recommendation is to get a list of all indexes on your system with \di in psql, then running "reindex index XXXX" per index. Be sure to bump sort_mem beforehand. Here's a script I ran over the weekend (during early morning low-usage time) on my system: SET sort_mem = 131072; SELECT NOW(); SELECT relname,relpages FROM pg_class WHERE relname LIKE 'user_list%' ORDER BY relname; SELECT NOW(); REINDEX INDEX user_list_pkey ; SELECT NOW(); REINDEX INDEX user_list_XXX ; SELECT NOW(); REINDEX INDEX user_list_YYY ; SELECT NOW(); SELECT relname,relpages FROM pg_class WHERE relname LIKE 'user_list%' ORDER BY relname; The relpages used by the latter two indexes shrunk dramatically: user_list_XXX | 109655 user_list_YYY | 69837 to user_list_XXX | 57032 user_list_YYY | 30911 and disk usage went down quite a bit as well. Unfortunately, the pkey reindex failed due to a deadlock being detected, but the XXX index is most popular... This is my "hottest" table, so I reindex it about once a month. My other "hot" table takes 45 minutes per index to redo, so I try to avoid that until I *really* have to do it (about 6 months). I don't think you'll need a nightly reindex. Of course, regular vacuums throughout the day on the busy talbes help keep it from getting too fragmented. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/