Posted on 6 Comments

MyISAM: good for SELECTs – and INSERTs!

There are these everlasting misconceptions about MyISAM’s capabilities, many documents and articles “out there” just state that it’s merely suitable for reads. Well, I’m afraid that’s an over-simplification. Quite unfortunate, since users could explicitly benefit from MyISAM strong points for quite a few other situations too. Just remember that MyISAM does not have transactions/rollback, so if you need that, you’ll want to use another MySQL storage engine like InnoDB, NDB Cluster, PBXT, Solid, or soon Falcon. That’s fine. Whatever suits best.

So what’s the deal with MyISAM? There’s multiple aspects to the story. First of all, it’s important to realise that the table-level locking comes in different flavours. There’s not just one single type of lock, instead we see READ (shared) locks for selects, and WRITE (exclusive) locks for write operations. And there’s a special READ LOCAL lock to allow inserts during selects. By the way, MySQL handles all this stuff automatically when it processes requests.

Did you already spot the magic? Indeed, it’s the READ LOCAL lock. The actual term for it is concurrent inserts. Peter Zaitsev referred to this a few days ago. MyISAM uses one data-file per table, and if inserts are written at the end of the file this insert need not block select operations. That is of course very nice for concurrency.

The server variable controlling this is concurrent_insert. The default value of 1 (since MySQL 4.0) enables concurrent inserts, but if there is any free space in the data file (from deleted rows, for instance) that space is filled instead. Setting this variable to 2 however, does something very smart: if there are any selects going on when the write needs to take place, the freespace is ignored and the row is written at the end of the file anyway. So it basically prioritises concurrency over space-efficiency, where necessary. And then, inserts don’t block selects at all.

So, amending the simplistic statements about MyISAM, I would state that MyISAM is particularly suitable for high-SELECT environments with concurrent INSERTs. That actually covers a lot of ground, many tables have 90-95% reads anyway, and the rest may well be inserts, not updates or deletes. Depends on the app, of course. This doesn’t mean that you can’t also have some updates/deletes… it all depends on the balance.

There are other aspects, also. Many servers run without any tuning whatsoever, and perform amazingly well. However, in its default settings, MySQL aims for minimal use of memory and disk space. One really can’t draw any conclusions on suitability when no tuning has been done! And for a production environment, a server should definitely be tuned. Do take a look at Peter’s blog article for an overview. When tuning, start with the key (index) and read/read_rnd buffers. The read buffers are per-connection, and so you’ll want to set them fairly small globally, and then increase only within a connection, when needed.

The delay_key_write variable deserves special mention. If enabled, the server will not immediately flush changed index blocks. If you were to enable this setting, a server crash (and remember, MySQL should not crash – we just must account for all possibilities including power and hardware failure) would see corrupted indexes on disk. The data will be fine, it’s just the indexes that are affected. However, this index corruption may not be detectable automatically, so you’d make sure to rebuild the indexes from the data using myisamcheck during server startup. It’s a trade-off between operational speed versus time required for recovery.
Even without this variable enabled, MyISAM index corruption is possible if the crash occurs during an index write. To cover this situation, you should set myisam-recover-options to QUICK,BACKUP. This will make the server check for tables that weren’t properly closed, make a backup copy, and then do a quick recover. Easy.

It really serves to know a little bit more about how things work, you can use it to your benefit. And if you see some very short but adament statement about something, chances are it’s over-simplified. Always use your own smarts! 😉

Posted on 6 Comments

6 thoughts on “MyISAM: good for SELECTs – and INSERTs!

  1. I think this option should be myisam_recover = QUICK,BACKUP,FORCE in my.cnf.
    Also, rebuilding all indexes after a crash seems a bit redundant, MySQL automatically runs a check for each table that wasn’t properly marked as closed. (And yes, MySQL sometimes just crashes 😉 there seem to be some unfixed SMP bugs in statements dealing with grants).

  2. Well, FORCE would be needed if there are older backup files already. However, I would hope that users keep an eye on their error log, check out the issues, and clean up. There’s a good reason for having a backup made, and just having it overwritten is not something I wish to recommend.

  3. re your note on crashes and possible bugs… that’s still not “normal”.
    Any crash is either a bad build, caused by external nasties in the OS/hardware, or a bug that should be reported.
    MySQL writes a stack trace and other info into the error log. You can resolve this stack trace and submit all the info to bugs.mysql.com. This will help the developers pinpoint where the problem lies.
    This is vital feedback, please don’t just take these occurrances as “oh it just happens sometimes”. Thanks.

  4. MyISAM does not overwrite it’s backups – it places timestamp in backup file name instead.

  5. Yes, but on production build it’s not always possible to create meaningful bug report. 🙁

  6. Provided it’s a build by MySQL, a cut/paste from your errorlog with the stack trace and the other stuff may still provide sufficient info to find the problem – particularly if it comes in from multiple people, it might create a complete picture.

    Note that non-debug builds still have a symbol file, so you should always be able to resolve the stack trace with symbols.

Comments are closed.