MySQL: MyISAM Table Gotchas!

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.

vi Tip of the day!

Something that I need to do frequently is to set some text to lowercase in vi which is currently in mixed or upper case. A quick way to do this is:


The above command acts on line 1 in the file, takes the entire line, enclosed in parentheses to create a group, then uses \L to specify lowercase on the entire line \1 that was previously matched.

Firefox 3!

Firefox 3 has been released!

I have been using this version for nearly one full day now and it’s great. I was disappointed when I loaded up all of my tabs and I normally just log out of my X session or Windows session to allow the handy crash recovery to recover all of my lost tabs on subsequent restart when, much to my dismay, it did not recover them. I had to explicitly specify through the options menu to revert to the previous set of tabs and windows on restart.

Download Firefox 3!