Wednesday, February 06
MeeSQL 5.6
MySQL 5.6 is out today. This one has enough enhancements to be worth a 6.0 version number, though hopefully without the usual .0 bugs. In particular, it's the first version of MySQL to ship with full-text index support for InnoDB, previously only supported in MyISAM. It also supports live table migration for InnoDB, another thing you could only really do with MyISAM before.
Comments are disabled.
Post is locked.
MySQL 5.6 is out today. This one has enough enhancements to be worth a 6.0 version number, though hopefully without the usual .0 bugs. In particular, it's the first version of MySQL to ship with full-text index support for InnoDB, previously only supported in MyISAM. It also supports live table migration for InnoDB, another thing you could only really do with MyISAM before.
The problem with MyISAM is that its concurrency support is miserable. It doesn't have real transactions, and while it can support as many simultaneous reads as you like, writes lock the entire table.
The problem with InnoDB, at least, if you look back five years or so, is that it is significantly slower and larger than MyISAM. These days, though, neither of those is true any longer.
I tested copying 100,000 posts from Minx into nearly-empty tables built with MyISAM and both compressed and uncompressed InnoDB. I say "nearly" empty because MyISAM has an optimisation trick for loading data into empty tables, and benchmarking it that way doesn't give you realistic performance figures.
- MyISAM: 75.63 seconds / 64M data + 74M indexes = 138M
- InnoDB Uncompressed: 34.6 seconds / 152M
- InnoDB Compressed (8K block size): 49.41 seconds / 76M
So InnoDB uncompressed is 10% larger than MyISAM but over twice as fast, and InnoDB compressed is 40% smaller than MyISAM and 50% faster. This is on a table with 63 fields, ten regular indexes, and three separate full-text indexes, so it should be exercising the storage engine pretty thoroughly. More write-optimised tables would of course be faster to load.
At that rate, it only takes about an hour to load the whole mee.nu post and comment history into InnoDB - so that's exactly what I'm planning to do.
Those benchmarks above were actually timed while that full load was running, which would likely slow InnoDB down a bit; I'll re-run them once the load finishes. But it's not an unreasonable thing to do, since one of the major points of InnoDB is that it can run multiple write threads simultaneously.
Posted by: Pixy Misa at
09:02 PM
| No Comments
| Add Comment
| Trackbacks (Suck)
Post contains 345 words, total size 2 kb.
46kb generated in CPU 0.0118, elapsed 0.1099 seconds.
54 queries taking 0.1028 seconds, 336 records returned.
Powered by Minx 1.1.6c-pink.
54 queries taking 0.1028 seconds, 336 records returned.
Powered by Minx 1.1.6c-pink.
