Performance Tuning - NO2. Storage Engine A fast and furious guide to MySQL database engines

What if you were a racecar driver and could swap engines with the flip of a switch instead of having to make a trip to the garage? The MySQL database does something like this for developers; it gives you a choice of database engines and an easy way to switch them.

The MySQL stock engine is certainly adequate, but there are circumstances where the other available engines may be better suited to the task at hand. If you want, you can even bore out the cylinders and slap on a four-barrel carburetor by building your own database engine using the MySQL++ API. Let's look at how you choose the engine and how to change between engines that are available to you.

Choose your engine
The number of database engines available to you depends on how your installation of MySQL was compiled. To add a new engine, MySQL must be recompiled. The concept of compiling an application just to add a feature may seem odd to Windows developers, but in the UNIX world, it's the norm. By default, MySQL supports three database engines: ISAM, MyISAM, and HEAP . Two other types, InnoDB and Berkley (BDB), are often available as well.

ISAM
ISAM is a well-defined, time-tested method of managing data tables, designed with the idea that a database will be queried far more often than it will be updated. As a result, ISAM performs very fast read operations and is very easy on memory and storage resources .

The two main downsides of ISAM are that it doesn't support transactions and isn't fault-tolerant : If your hard drive crashes, the data files will not be recoverable. If you're using ISAM in a mission-critical application, you’ll want to have a provision for constantly backing up all your live data, something MySQL supports through its capable replication features .

MyISAM
MyISAM is MySQL's extended ISAM format and default database engine . In addition to providing a number of indexing and field management functions not available in ISAM, MyISAM uses a table-locking mechanism to optimize multiple simultaneous reads and writes. The trade-off is that you need to run the OPTIMIZE TABLE command from time to time to recover space wasted by the update algorithms. MyISAM also has a few useful extensions such as the MyISAMChk utility to repair database files and the MyISAMPack utility for recovering wasted space.

MyISAM, with its emphasis on speedy read operations, is probably the major reason MySQL is so popular for Web development, where the vast majority of the data operations you’ll be carrying out are read operations. As a result, most hosting and Internet Presence Provider (IPP) companies will allow the use of only the MyISAM format.

HEAP
HEAP allows for temporary tables that reside only in memory . Residing in memory makes HEAP faster than ISAM or MyISAM, but the data it manages is volatile and will be lost if it's not saved prior to shutdown. HEAP also doesn’t waste as much space when rows are deleted. HEAP tables are very useful in situations where you might use a nested SELECT statement to select and manipulate data. Just remember to destroy the table after you’re done with it. Let me repeat that: Don’t forget to destroy the table after you’re done with it.

InnoDB and Berkley DB
The InnoDB and Berkley DB (BDB) database engines are direct products of the technology that makes MySQL so flexible, the MySQL++ API. Almost every challenge you're likely to face when using MySQL stems directly from the fact that the ISAM and MyISAM database engines aren't transactional and lack foreign-key support. A lthough much slower than the ISAM and MyISAM engines , InnoDB and BDB include the transactional and foreign-key support missing from the former two choices. As such, if your design requires either or both of these features, you’re actually compelled to use one of these two choices.

If you’re feeling particularly capable, you can create your own database engine using MySQL++. The API provides all the functions you need for working with fields, records, tables, databases, connections, security accounts, and all of the other myriad functions that make up a DBMS such as MySQL. Going too heavily into the API is beyond the scope of this article, but it’s important to know that MySQL++ exists and that it’s the technology behind MySQL’s swappable engines. Presumably, this model of plug-in database engines could even be used to build a native XML provider for MySQL. (Any MySQL++ developers reading this may consider that a request.)

Flipping the switch
The switch that makes all this flexibility possible is an extension MySQL provides to ANSI SQL, the TYPE parameter. MySQL allows you to specify database engines at the table level, so they are sometimes referred to as table formats . The following sample code shows how to create tables that use the MyISAM, ISAM, and HEAP engines, respectively. Notice that the code to create each table is the same, with the exception of the trailing TYPE parameter, which specifies the database engine.
 
CREATE TABLE tblMyISAM (
       id INT NOT NULL AUTO_INCREMENT,
       PRIMARY KEY (id),
       value_a TINYINT
) TYPE=MyISAM
CREATE TABLE tblISAM (
       id INT NOT NULL AUTO_INCREMENT,
       PRIMARY KEY (id),
       value_a TINYINT
) TYPE=ISAM
CREATE TABLE tblHeap (
       id INT NOT NULL AUTO_INCREMENT,
       PRIMARY KEY (id),
       value_a TINYINT
) TYPE=Heap
 

You can also use the ALTER TABLE command to move an existing table from one engine to another. The following code shows the use of ALTER TABLE to move a MyISAM table to InnoDB's engine:
 
ALTER TABLE tblMyISAM CHANGE TYPE=InnoDB
 

MySQL makes this happen with three steps. First, an exact copy of the table is created. Next, any incoming data changes are queued, while the copy is moved to the other engine. Finally, any queued data changes are committed to the new table, and the original one is deleted.

---

An ALTER TABLE shortcut
If you’re simply upgrading your tables from ISAM to MyISAM, you can use the command mysql_convert_table_format instead of writing ALTER TABLE statements.

---

You can use the SHOW TABLE command (which is another MySQL extension to the ANSI standard) to determine which engine is managing a particular table. SHOW TABLE returns a result set with multiple columns that you can query to get all kinds of information: The name of the database engine is in the Type field. The following sample code illustrates the use of SHOW TABLE:
 
SHOW TABLE STATUS FROM tblInnoDB
 

---

A SHOW TABLE alternative
You can use SHOW CREATE TABLE [TableName] to retrieve the same information that SHOW TABLE retrieves.

---

Finally, it's worth noting that if you try to use an engine that isn't compiled into MySQL and activated, MySQL won’t complain about it. It will instead quietly give you a table in the default format (MyISAM) instead. There are plans to have MySQL throw an error in addition to using the default table format, but for now, you’ll want to check the table format using SHOW TABLE if you’re not certain that a particular database engine is available.

More choices means better performance
Considering the additional complications of recompiling and tracking which engines are used for particular tables, why would you want to use any of the nondefault database engines? The answer is simple: to tune the database to meet your needs.

Sure, MyISAM is fast, but if your logical design requires transactions, you’re free to use one of the transaction-enabled engines. Further, since MySQL allows you to apply database engines on the table level , you can take the performance hit on only the tables that require transactions and leave the nontransactional tables to be managed by the more lightweight MyISAM engine . With MySQL, flexibility is the key.

 

note: from envykok

MyISAM: faster, but does not support transaction and FK ( good for read-heavy data)

Heap : super faster, but it is in memory, once server shutdown , everything will gone. (better to do replication)

InnoDB : a little bit slow, but support transactions lock and suport FK( good for write-heavy data)