I’ve recently had to learn a lot about MySQL and how to manage it effectively, especially after it has not been managed very well for a long time. As a Linux Administrator I’ve had the opportunity to use MySQL in many roles over the years but never at an enterprise level. I’m still learning and trying things out, but so far here are some guidelines:
- MyISAM is a very fast engine, do not delete rows without performing an optimize directly afterward to prevent holes, fragmentation, and subsequent table locking as the holes are filled while fragmenting the tables — create a data maintenance plan and don’t delete data without cause and use batches.
- Running an optimize on a large MyISAM table might take a day or two.
- MyISAM will lock the table on inserts if there are holes from data removal when an optimize has not been performed to remove the holes.
- InnoDB consumes a significant amount of space compared to MyISAM, a general guideline might be 5x as much.
If you find that after deleting data from a table you have performance issues, look at running optimize on your MyISAM tables and check for locks using ’show processlist’.
I don’t know if converting to InnoDB will help in situations with large data sets where table maintenance is not performed regularly, but time will tell. I am skeptical that the performance on an InnoDB table will be there with large data sets as well.
I find that as an open source advocate, without knowing these things, it’s very difficult to make an argument for open source solutions when there are SQL Server advocates around calling the architecture shots.