[isf-wifidog] Progressive slowdown of wifidog

[Benoit Grégoire]

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/
-------------- section suivante --------------
Une pièce jointe non texte a été nettoyée...
Nom: non disponible
Type: application/pgp-signature
Taille: 189 octets
Desc: non disponible
Url: http://listes.ilesansfil.org/pipermail/wifidog/attachments/20060120/b23c1310/attachment.pgp