Item9: MySQL? database collects dust over time, slowing down map generation

Map generation becomes slower over time, apparently because the mysql database collects dust. The indexes and statistics used by the query planner become inaccurate and it appears that obsolete data is lingering in some of the tables.

We need a procedure that is run every once in a while that cleans out obsolete data (which i did rather rudely by a TRUNCATE, which results in colateral damage) and applies OPTIMIZE TABLE to the tables to update indexes/statistics.

-- KoenMartens - 12 Oct 2007

I wrote a script that applies 'optimize table' to every table in the issuemaps mysql database. Problem is that with deletes, mysql does not give the freed space back to the OS. Instead it will re-use, and this results in fragmentation of the data plus a growing size of the underlying file => slowness. Optimize table should prevent this.

The script is in /www/issuecrawler.net/svg2/db_maint.php and is run once a day by cron. The script is non-destructive, ie. existing data is not deleted.

-- KoenMartens - 06 Mar 2008