From Pro MySQL, Berkeley, Apress, July 2005.

Related Podcast: Jay Pipes and Michael Kruckenberg - Open Source Pros

Part 1  |  Part 2  |  Part 3  |  Part 4  |  Part 5  |  Part 6

In this chapter, we’ll delve into an aspect of MySQL that sets it apart from other relational database management systems: its ability to use entirely different storage mechanisms for various data within a single database. These mechanisms are known as storage engines, and each one has different strengths, restrictions, and uses. We’ll examine these storage engines in depth, suggesting how each one can best be utilized for common data storage and access requirements.

After discussing each storage engine, we’ll review the various types of information that can be stored in your database tables. We’ll look at how each data type can play a role in your system, and then provide guidelines on which data types to apply to your table columns. In some cases, you’ll see how your choice of storage engine, and indeed your choice of primary and secondary keys, will influence which type of data you store in each table.

In our discussion of storage engines and data types, we’ll cover the following topics:

• Storage engine considerations
• The MyISAM storage engine
• The InnoDB storage engine
• The MERGE storage engine
• The MEMORY storage engine
• The ARCHIVE storage engine
• The CSV storage engine
• The FEDERATED storage engine
• The NDB Cluster storage engine
• Guidelines for choosing a storage engine
• Considerations for choosing data types

Storage Engine Considerations

The MySQL storage engines exist to provide flexibility to database designers, and also to allow for the server to take advantage of different types of storage media. Database designers can choose the appropriate storage engines based on their application’s needs. As with all software, to provide specific functionality in an implementation, certain trade-offs, either in performance or flexibility, are required. The implementations of MySQL’s storage engines are no exception-each one comes with a distinct set of benefits and drawbacks.

Note: Storage engines used to be called table types (or table handlers). In the MySQL documentation, you will see both terms used. They mean the same thing, although the preferred description is storage engine.

As we discuss each of the available storage engines in depth, keep in mind the following questions:

• What type of data will you eventually be storing in your MySQL databases?
• Is the data constantly changing?
• Is the data mostly logs (INSERTs)?
• Are your end users constantly making requests for aggregated data and other reports?
• For mission-critical data, will there be a need for foreign key constraints or multiplestatement transaction control?

The answers to these questions will affect the storage engine and data types most appropriate for your particular application.

Tip: In order to specify a storage engine, use the CREATE TABLE (… ) ENGINE=EngineType option, where EngineType is one of the following: MYISAM, MEMORY, MERGE, INNODB, FEDERATED, ARCHIVE, or CSV.

The MyISAM Storage Engine

ISAM stands for indexed sequential access method. The MyISAM storage engine, an improved version of the original but now deprecated ISAM storage engine, allows for fast retrieval of its data through a non-clustered index and data organization. (See Chapter 2 to learn about nonclustered index organization and the index sequential access method.)

MyISAM is the default storage engine for all versions of MySQL. However, the Windows installer version of MySQL 4.1 and later offers to make InnoDB the default storage enginewhen you install it.

The MyISAM storage engine offers very fast and reliable data storage suitable for a variety of common application requirements. Although it does not currently have the transaction processing or relational integrity capacity of the InnoDB engine, it more than makes up for these deficiencies in its speed and in the flexibility of its storage formats. We’ll cover those storage formats here, and take a detailed look at the locking strategy that MyISAM deploys in order to provide consistency to table data while keeping performance a priority.

MyISAM File and Directory Layout

All of MySQL’s storage engines use one or more files to handle operations within data setsv structured under the storage engine’s architecture. The data_dir directory contains one subdirectory for each schema housed on the server. The MyISAM storage engine creates a separate file for each table’s row data, index data, and metadata:

• table_name.frm contains the meta information about the MyISAM table definition.
• table_name.MYD contains the table row data.
• table_name.MYI contains the index data.

Because MyISAM tables are organized in this way, it is possible to move a MyISAM table from one server to another simply by moving these three files (this is not the case with InnoDB tables). When the MySQL server starts, and a MyISAM table is first accessed, the server reads the table_name.frm data into memory as a hash entry in the table cache (see Chapter 4 for more information about the table cache for MyISAM tables).

Note: Files are not the same as file descriptors. A file is a collection of data records and data pages into a logical unit. A file descriptor is an integer that corresponds to a file or device opened by a specific process. The file descriptor contains a mode, which informs the system whether the process opened the file in an attempt to read or write to the file, and where the first offset (base address) of the underlying file can be found. This offset does not need to be the zero-position address. If the file descriptor’s mode was append, this offset may be the address at the end of the file where data may first be written.

As we noted in Chapter 2, the MyISAM storage engine manages only index data, not record data, in pages. As sequential access implies, MyISAM stores records one after the other in a single file (the .MYD file). The MyISAM record cache (discussed in Chapter 4) reads records through an IO_CACHE structure into main memory record by record, as opposed to a larger-sized page at a time. In contrast, the InnoDB storage engine loads and manages record data in memory as entire 16KB pages.

Additionally, since the MyISAM engine does not store the record data on disk in a paged format (as the InnoDB engine does), there is no wasted “fill factor” space (free space available for inserting new records) between records in the .MYD file. Practically speaking, this means that the actual data portion of a MyISAM table will likely be smaller than an identical table managed by InnoDB. This fact, however, should not be a factor in how you choose your storage engines, as the differences between the storage engines in functional capability are much more significant than this slight difference in size requirements of the data files.

For managing index data, MyISAM uses a 1KB page (internally, the developers refer to this index page as an index block). If you remember from our coverage of the MyISAM key cache in Chapter 4, we noted that the index blocks were read from disk (the .MYI file) if the block was not found in the key cache (see Figure 4-2). In this way, the MyISAM and InnoDB engine’s treatment of index data using fixed-size pages is similar. (The InnoDB storage engine uses a clustered index and data organization, so the 16KB data pages are actually the index leaf pages.)

MyISAM Record Formats

When analyzing a table creation statement (CREATE TABLE or ALTER TABLE), MyISAM determines whether the data to be stored in each row of the table will be a static (fixed) length or if the length of each row’s data might vary from row to row (dynamic). The physical format of the .MYD file and the records contained within the file depend on this distinction. In addition to the fixed and dynamic record formats, the MyISAM storage engine supports a compressed row format. We’ll cover each of these record formats in the following sections.

Note: The MyISAM record formats are implemented in the following source files: /myisam/mi_sta? trec.c (for fixed records), /myisam/mi_dynrec.c (for dynamic records), and /myisam/mi_packrec.c (for compressed records).

Fixed Record Format

When the record format is of a fixed length, the .MYD file will contain each MyISAM record in sequential order, with a NULL byte (0x00) between each record. Each record contains a bitmap record header. By bitmap, we’re not referring to the graphic. A bitmap in programming is a set of single bits, arranged in segments of eight (to align them into a byte structure), where each bit in the byte is a flag that represents some status or Boolean value. For instance, the bitmap 1111 0101 in binary, or 0xF5 in hexadecimal, would have the second and fourth bits turned off (set to 0) and all other bits turned on (set to 1). Remember that a byte is composed of a loworder and a high-order byte, and is read right to left. Therefore, the first bit is the rightmost bit.

The MyISAM bitmap record header for fixed-length records is composed of the following bits, in this order:

• One bit representing whether the record has been deleted (0 means the row is deleted).
• One bit for each field in the MyISAM table that can be NULL. If the record contains a NULL value in the field, the bit is equal to 1, else 0.
• One or more “filler” bits set to 1 up to the byte mark.

The total size of the record header bitmap subsequently depends on the number of nullable fields the table contains. If the table contains zero to seven nullable fields, the header bitmap will be 1 byte; eight to fifteen nullable fields, it will be 2 bytes; and so on. Therefore, although it is advisable to have as few NULL fields as possible in your schema design, there will be no practical effect on the size of the .MYD file unless your table contains more than seven nullable fields.

After each record header, the values of the record’s fields, in order of the columns defined in the table creation, will follow, consuming as much space as the data type requires.

Since it can rely on the length of the row data being static for fixed-format records, the MyISAM table cache (see Chapter 4) will contain information about the maximum length of each row of data. With this information available, when row data is sequentially read (scanned) by the separate MyISAM access requests, there is no need to calculate the next record’s offset in the record buffer. Instead, it will always be x bytes forward in the buffer, where x is the maximum row length plus the size of the header bitmap. Additionally, when seeking for a specific data record through the key cache, the MyISAM engine can very quickly locate the needed row data by simply multiplying the sum of the record length and header bitmap size by the row’s internal record number (which starts at zero). This allows for faster access to tables with fixed-length records, but can lead to increased actual storage space on disk.

Note: You can force MySQL to apply a specific row format using the ROW_FORMAT option in your CREATE ? TABLE statement.

Dynamic Record Format

When a MyISAM table contains variably sized data types (VARCHAR, TEXT, BLOB, and so on), the format of the records in the .MYD file is known as dynamic. Similar to the fixed-length record storage, each dynamically sized record contains a record header, and records are laid out in the .MYD file in sequential order, one after the next. That is where the similarities end, however.

The header for a dynamically sized record is composed of more elements, including the following:

• A 2-byte record header start element indicates the beginning of the record header. This is necessary because, unlike the fixed-length record format, the storage engine cannot rely on record headers being at a static offset in the .MYD file.
• One or more bytes that store the actual length (in bytes) of the record.
• One or more bytes that store the unused length (in bytes) of the record. MyISAM leaves space in each record to allow for the data to expand a certain amount without needing to move records around within the .MYD file. This part of the record header indicates how much unused space exists from the end of the actual data stored in the record to the beginning of the next record.
• A bitmap similar to the one used for fixed-length record, indicating NULL fields and whether the record has been deleted.
• An overflow pointer that points to a location in the .MYD file if the record has been updated and now contains more data than existed in the original record length. The overflow location is simply the address of another record storing the rest of the record data.

After this record header, the actual data is stored, followed by the unused space until the next record’s record header. Unlike the fixed-record format, however, the dynamic record format does not consume the full field size when a NULL value is inserted. Instead, it stores only a single NULL value (0x00) instead of one or more NULL values up to the size of the same nullable field in a fixed-length record.

A significant difference between the static-length row format and this dynamic-length row format is the behavior associated with updating a record. For a static-length row record, updating the data does not have any effect on the structure of the record, because the length of the data being inserted is the same as the data being deleted.¹ For a varying-length row record, if the updating of the row data causes the length of the record to be greater than it was before, a link is inserted into the row pointing to another record where the remainder of the data can be found (the overflow pointer). The reason for this linking is to avoid needing to facilitate the rearrangement of multiple buffers of row records in order to accommodate the new record. The link serves as a placeholder for the new information, and the link will point to an address location that is available to the engine at the time of the update. This fragmentation of the record data can be corrected by running an OPTIMIZE TABLE command, or by running #> myisamchk -r.

Compressed Record Format

An additional flavor of MyISAM allows you to specify that the entire contents of a specified table are read-only, and the records should be compressed on insertion to save disk space. Each data record is compressed separately and uncompressed when read.

To compress a MyISAM table, use the myisampack utility on the .MYI index data file:

#> myisampack [options] tablename.MYI

MyISAM uses Huffman encoding (see Chapter 2) to compress data, along with a technique where fields with few distinct values are compressed to an ENUM format. Typical compression ratios are between 40% and 70% of the original size. The myisampack utility can, among other things, combine multiple large MyISAM tables into a single compressed table (suitable for CD distribution for instance). For more information about the myisampack utility, visit http://dev.mysql.com/doc/mysql/en/myisampack.html.

Footnotes

¹ Remember that an UPDATE is really a DELETE of the existing data and an INSERT of the new data.

--

Michael Kruckenberg started his career with web technologies more than 10 years ago. His first major undertaking was bringing a small mail-order company online (using MySQL). After hopping around a bit during the 1990s Internet boom and spending time in the Internet startup world, Mike put his feet down at his current gig, senior programmer at Tufts University. He is now the technical manager for the Apache/Perl/MySQL-driven Tufts University Sciences Knowledgebase (TUSK), a content repository for faculty and students. Mike likes to spend his elusive free time with his wife and kids on New England adventures, has a healthy addiction to music, and likes to dabble in the arts (photography, video, and literature).

For the past 10 years, Jay Pipes has worked with all kinds of companies, large and small, to identify the value of the information they collect and help them build software that best addresses the needs of their businesses. From e-commerce to work-order management systems, Jay has been involved in projects with both Microsoft and open-source technologies. Passionate about programming and all things technical, Jay now runs his own consulting business, based in Columbus, Ohio. When not being bothered by his two cats, two dogs, and a constantly ringing phone, you can find him, headphones pulled across his ears, happily coding away at home.