9.4. Scaling MySQL

We've already looked at bottlenecks in MySQL and talked about performance, but we haven't yet explored the various storage engines that MySQL offers and how we can use these to our advantage. Strictly speaking, switching MySQL backends present a performance rather than scalability issue, although if you're hitting locking issues with MyISAM tables, you won't be able to scale until you deal with performance issues.

We'll look at a few methods of scaling MySQL installations after we've discussed the various storage backends and their capabilities. For limited nonlinear read capacity scaling, we can use MySQL's replication system. For architectural linear scaling, we can go vertical and partition our table space into clusters or go horizontal and partition our data into shards.

Before we dig into it, another word of warning about database scaling approaches and terminology. As with many technical concepts, there are several ambiguous ways of describing database scaling approaches. Vertical partitioning is sometimes called clustering and sometimes called segregation. The segments produced are referred to variously as clusters, partitions, and pools. Horizontal federation is sometimes called clustering or data partitioning. The segments produced are typically called shards, but can also be called cells, clusters, or partitions. We'll stick with partitioning/partitions and federation/shards, but it's a good idea to be wary when talking to others about these topics because misunderstandings are common.

9.4.1. Storage Backends

At the core of a MySQL database is something called a storage engine, often referred to as a backend or table type. Storage backends sit underneath the parser and optimizer and provide the storage and indexing facilities. MySQL comes with a number of built-in storage engines and allows custom ones to be built and added by users. Each storage engine implements simple opening, closing, reading, inserting, updating, and deleting semantics, on top of which MySQL can build its environment. Different table types provide different features and benefits, but all of them, as seen in Figure 9-5, sit below the query parser level.

Figure 9-5. "Storage engines and clients

To find out which storage engines are available in your installation, you can query the MySQL server and ask:

mysql> SHOW STORAGE ENGINES;
+------------+---------+------------------------------------------------------------+
| Engine     | Support | Comment                                                    |
+------------+---------+------------------------------------------------------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  |
| MERGE      | YES     | Collection of identical MyISAM tables                      |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys |
| BDB        | YES     | Supports transactions and page-level locking               |
| NDBCLUSTER | NO      | Clustered, fault-tolerant, memory-based tables             |
+------------+---------+------------------------------------------------------------+
6 rows in set (0.00 sec)

This command shows us all the possible MySQL storage engines as well as which are available in this installation and which will be used by default for new tables. We can find out which engine each of our tables is using by showing the table status for a database. Most of the columns here have been truncated for claritythe Engine column (labeled Type before MySQL 4.2.1) tells us the engine used by the table:

mysql> SHOW TABLE STATUS;
+--------------+--------+---------+------------+------+----------------+------------+
| Name         | Engine | Version | Row_format | Rows | Avg_row_length | Data_length|
+--------------+--------+---------+------------+------+----------------+------------+
| columns_priv | MyISAM |       7 | Fixed      |    0 |              0 |          0 |
| db           | MyISAM |       7 | Fixed      |    2 |            153 |        306 |
| func         | MyISAM |       7 | Fixed      |    0 |              0 |          0 |
| host         | MyISAM |       7 | Fixed      |    0 |              0 |          0 |
| tables_priv  | MyISAM |       7 | Fixed      |    0 |              0 |          0 |
| user         | MyISAM |       7 | Dynamic    |   10 |             66 |        660 |
+--------------+--------+---------+------------+------+----------------+------------+
6 rows in set (0.00 sec)

We can change the engine used to store a table by issuing an ALTER TABLE command. Be careful performing this operation on tables with existing data because it can take a long time. You should also be wary of transforming tables with data in them into engines that can lose data such as Heap or MySQL 5's Blackhole engine. We can also set the engine type of a table as part of the table creation syntax:

mysql> CREATE TABLE my_table ( ... ) ENGINE=MyISAM;
mysql> ALTER TABLE my_table ENGINE=Heap;

MySQL 5 has support for 10 different storage engines out of the box, doing a variety of interesting things. Unfortunately for us, until MySQL 5 becomes usable in a production environment, we're stuck with the limited storage engines offered by MySQL 4. We'll discuss the four most important engines available in MySQL 4 and compare the different features they offer.