Re: [Netdisco] Postgres <7.4 index bloat

[Richard S. <ric...@ea...>]

WOW!

Old server, just over 1 yr in use.
du -h pgsql
Before:4.5G
After: 161M

New server, only a couple of weeks old:
Before: 92M
After: 66M

Made a HUGE difference in the vacuum time on the old box. Thanks Max!

--Richard

-- 
------------------------------
Richard Silver
Sr Network Engineer
East Alabama Medical Center
2000 Pepperell Parkway
Opelika, AL 36801
334-705-1859
www.eamc.org
------------------------------
On Sun, 2004-10-24 at 10:30, Max Baker wrote:
> Hey All,
> 
> I was just trying to solve some of the problems with Postgres getting really
> slow over time and found out that on top of the VACUUM FULL that is
> recommended every so often by hand in the README (once a month?) that we
> should have been running a REINDEX TABLE on all the big tables.
> 
> Another huge benefit of this is that I found out that huge portions of time
> spent during macsucks and arpnips were actually being spent doing the SQL
> VACUUM command at the end of the job, and not the actual go out and get the
> data via SNMP part.    The new instructions will be up at
>     http://netdisco.org/readme.html#netdisco%20maintenance
> by the end of today (Sunday).
> 
> From the Log:
> mac     7414 distinct nodes. 87069 forwarding table entries. 102.13 minutes.
> mac     7205 distinct nodes. 84335 forwarding table entries. 342.10 minutes.
> mac     6178 distinct nodes. 65782 forwarding table entries. 214.27 minutes.
> (did the reindex)
> mac     5866 distinct nodes. 66154 forwarding table entries. 21.22 minutes.
> mac     6612 distinct nodes. 78507 forwarding table entries. 22.63 minutes.
> 
> arp     23158 entries. 43.05 minutes.
> arp     22967 entries. 77.20 minutes.
> arp     21887 entries. 36.12 minutes.
> (did the reindex)
> arp     20424 entries. 11.13 minutes.
> arp     22085 entries. 11.33 minutes.
> arp     22892 entries. 11.08 minutes.
> 
> Here's a summary of the space results at UCSC : 
>     Before:
>     # du -h pgsql       
>          16G    pgsql/
> 
>     After Reindex:
>     # du pgsql
>         484M    pgsql/
> 
>     After Vacuum:
>     # du pgsql                          
>         381M    pgsql/
> 
> So there is this phenomenon known as "Index Bloat" where Postgres is not
> able to reclaim space used by the indexes with just a Vacuum.    The REINDEX
> TABLE command blows away the index and then rebuilds it.  Ironically I added
> all the indexing for speed, and it's what is killing the speed of the
> database.   
> 
> One of their developers told me that this is mostly all fixed in Postgres
> 7.4.  So I strongly suggest that when you upgrade to Netdisco 0.94 that you
> also upgrade to Postgres 7.4 .   It is  a bit of a pain because you have to
> do a dump and restore on the database, but seems like it's worth it.  If
> that is not an option, make sure to follow the new instructions in the
> README for netdisco under the heading "Things are getting really slow" at
> least once a month.
> 
> Since this requires that all other things not be using the database, I can't
> really automate the procedure, because you have to turn Netdisco off before
> doing it.
> 
> Enjoy,
> -m
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: IT Product Guide on ITManagersJournal
> Use IT products in your business? Tell us what you think of them. Give us
> Your Opinions, Get Free ThinkGeek Gift Certificates! Click to find out more
> http://productguide.itmanagersjournal.com/guidepromo.tmpl
> _______________________________________________
> Netdisco mailing list
> net...@li...
> https://lists.sourceforge.net/lists/listinfo/netdisco-users