[isf-wifidog] Progressive slowdown of wifidog

[François Proulx]

exactly, running the Query plan optimizer (ANALYZE) is not a big  
performance hit if done regularly... unless you have just done a full  
database restore, you will need a FULL statement. Becase it will  
clean up all  temporary tables, deleted rows, indexes.


On 20-Jan-2006, at 20:46 , Benoit Grégoire wrote:

> On January 20, 2006 08:14 pm, Max Horváth wrote:
>> - gpg control packet
>> I resolved the problem ...
>>
>> Run VACUUM FULL ANALYZE on your db ...
>>
>> It'll lock the tables while doing this procedure, but afterwards your
>> server will be fast again ;)
>
> Just to add to this, VACUUM FULL ANALYZE solved Max's problem  
> because he
> didn't run VACUUM ANALYSE for a long time.  If you run it every  
> night or
> every hour, wou shouldn't need VACUUM FULL ANALYSE.
>
> What happened is that the connection and node tables grew and grew  
> (because
> they are written to frequently, and postgres being a true ACID  
> database, keep
> pass version of tuples to insure read consistency).  Simply  
> vacuuming doesn't
> reclaim space used by old tuples, it just makes it available for  
> old tuples.
> But no max probably had millions of free tuples that he would,  
> never, ever
> use up in a single day.
>
> VACUUM FULL ANALYZE fixed that.
>
> So the lesson:
>
> 1-Run VACUUM ANALYSE often from a script (it's a very cheap, non  
> locking
> operation, and considering wifidog's very write heavy SQL mix,  
> runing it
> every hour isn't excessive).
> 2-Make sure your script works.
> 3-If you see you auth server getting progressively slower, run   
> VACUUM FULL
> ANALYSE once, and GO TO STEP 2.
> -- 
> Benoit Grégoire, http://benoitg.coeus.ca/
> _______________________________________________
> WiFiDog mailing list
> WiFiDog at listes.ilesansfil.org
> http://listes.ilesansfil.org/cgi-bin/mailman/listinfo/wifidog