Nathan’s MySQL Crash Course: Part One
Our senior sysadmin, Nathan March, has been compiling some notes on MySQL and InnoDB for internal use, but we thought they might be of some use and interest to a broader audience. We’ve split Nathan’s insights up into three posts which we’ll be posting daily. First up, Nathan talks about the distinctions between MyISAM and InnoDB, two of MySQL’s storage engines:
I initially started writing this for sysadmins but thought it might be of use to programmers on the whole to help give some insight into MySQL’s storage engine, how they work and why they do certain things. Hopefully this will help demystify things a little on how InnoDB and replication work internally.
I’d like to talk a bit about the fundamental differences between the two storage engines:
MyISAM is the old default MySQL storage engine. It stores files in /var/lib/mysql/dbname/ as table.frm (table structure), table.MYI (indexes), and table.MYD (data). These files are portable and can be moved between servers and databases easily. MyISAM is faster than InnoDB but suffers in areas of data integrity in the case of crashes.
Its biggest disadvantage it has is table level locking, in which an update to a single row on a table requires the entire table to be locked while the update happens. The next biggest issue is crash recovery: if the server crashes and you’ve got a 20GB table, MyISAM will completely recreate that table piece by piece, whereas InnoDB will go through the log and only re-execute the most recent alterations, taking a fraction of the time.
A much more modern storage engine, InnoDB can store files in /var/lib/mysql/ibdata1 or /var/lib/mysql/dbname/table.ibd. You cannot move these files or alter them at all (more details on that in subsequent posts). InnoDB is generally slower and has more overhead than MyISAM, but it guarantees data consistency even after a crash.
InnoDB uses RAM far more efficiently in creating a cache, but its biggest advantage is its support for row level locking. This means that multiple users can make updates to a table at the same time and will only be blocked if someone else is trying to update that exact same row. In web applications where you have many threads running simultaneously, MyISAM’s need to lock an entire table can quickly cause problems. InnoDB generally has roughly 1.5x – 2x the storage requirements of a table in MyISAM, meaning that a 20GB MyISAM table will likely require 30-40GB in InnoDB.
As a rule, our client’s servers run on InnoDB
After starting to run into performance limitations and bugs in MySQL, we started to deploy Percona Server on our larger customers and we now use it as our default database. Percona is a fork (an alternate version) of the main MySQL release and functions as a drop-in replacement for the stock MySQL. Percona developers have done significant work to improve manageability, scalability and stability, especially on larger multi-processor systems. Percona Server uses what its developers call XtraDB, effectively their modified fork of InnoDB which is compatible with the InnoDB data files. Although we use Percona Server and XtraDB, it’s referred to as MySQL and InnoDB for the purposes of these articles.
Tomorrow, I’ll give some general MySQL tips, before getting into some Percona-specific talk on Friday.