Automated Table Optimizations

[David Miller]

Benton, Kevin wrote on 8/11/06 11:23 AM:
> I just learned that doing either CHECK TABLE or ANALYZE TABLE can do a lot for us in optimizing disk space utilization as well as indexing of tables in MySQL.  This can improve performance of the back-end in tables that could potentially suffer from fragmenting (usually due to deletes followed by subsequent adds).  I think it'd be a good idea to recommend that users implement a cron job that does these operations on a weekly-monthly basis in an optimization section of the manual.  I also think it'd be good to document (if we don't already) how to set up MySQL replication in a similar section.  What do others think about this?

Yeah, we've had this going on b.m.o for a while.

[root at mrdb01 cron.weekly]# cat analyze-bugzilla-tables
#!/bin/sh
#
# Analyze the bugzilla tables to keep the indexes performant
mysql bugs -e "analyze table `mysql bugs --skip-column-names -e 'show 
tables' | tr '\n' ',' | sed 's/,$//'`"


-- 
Dave Miller                                   http://www.justdave.net/
System Administrator, Mozilla Corporation      http://www.mozilla.com/
Project Leader, Bugzilla Bug Tracking System  http://www.bugzilla.org/