Previous Section  < Day Day Up >  Next Section

14.1 Special MyISAM Features

MyISAM tables have certain important characteristics that are described in this section. MyISAM is the default table type, but understanding these features helps you know when to choose MyISAM deliberately rather than some other table type. MyISAM characteristics include the following:

  • The table storage format is portable, allowing table files to be copied directly to another host and used by a server there.

  • MyISAM tables can be converted to compressed read-only format to save space.

  • You can tell MySQL the maximum number of rows that the table must be able to hold, which allows the server to adjust the table's internal row pointer size accordingly.

  • On systems that support appropriate symlinking capabilities, tables can be placed in a different location than the default (which is the database directory).

  • The rows of a MyISAM table normally are stored in a single datafile. MyISAM supports the option of setting up a "software RAID array" such that the datafile is implemented as a set of files. This can be advantageous when a table's datafile size is limited by the filesystem's maximum file size. Setting up a RAID array allows the file size limit to be circumvented.

  • When loading data into an empty MyISAM table, you can disable updating of nonunique indexes and enable the indexes after loading. This is faster than updating the indexes for each row inserted. In fact, when LOAD DATA INFILE is used for loading an empty MyISAM table, it automatically disables and enables index updating. LOAD DATA INFILE is faster than INSERT anyway, and this optimization speeds it up even more.

  • You can influence the scheduling mechanism for queries that use MyISAM tables by using a query modifier such as LOW_PRIORITY or HIGH_PRIORITY. Inserts into a table can be buffered on the server side until the table isn't busy by using INSERT DELAYED; this allows the client to proceed immediately instead of blocking until the insert operation completes.

  • If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until space becomes available, and then completes the operation.

14.1.1 MyISAM Table Portability

MyISAM tables generally are binary portable. This means they are machine independent and that you can directly copy the files for a MyISAM table from one MySQL server to another on a different machine and the second server will be able to access the table with no problems. Were this not the case, the only option for transferring tables between servers would be to dump them into some text format (for example, with mysqldump) and reload them into the destination server.

For a MyISAM table to be binary portable from one host to another, two conditions must be met:

  • Both machines must use two's-complement integer arithmetic.

  • Both machines must use IEEE floating-point format, or else the table must contain no floating-point columns (FLOAT or DOUBLE).

In practice, these conditions pose little restriction. Two's-complement integer arithmetic and IEEE floating-point format are the norm on modern hardware.

When you copy a table directly to another server, the table must not be in use by other programs (including the server) while the table files are being copied. If you stop the server while copying the table, there will be no problem of server interaction. If you leave the server running, use an appropriate locking protocol to prevent server access to the table. For example, to copy the Country table in the world database, lock the table and flush any pending changes like this:







mysql> USE world;

mysql> LOCK TABLES Country READ;

mysql> FLUSH TABLES Country;

Then (with the table still locked) use your operating system's file copy command to copy the table files. After the copy operation completes, release the lock on the table:







mysql> UNLOCK TABLES;

Note that Windows file-locking behavior is such that you might not be able to copy table files for tables that are locked by the server. In that case, you'll need to stop the server before copying table files.

If the conditions for binary portability are not satisfied, you can copy a MyISAM table from one server to another by using mysqldump. This process is described in section 14.4.1, "Using mysqldump."

14.1.2 Specifying MyISAM Maximum Row Count

Internally, the MyISAM storage engine represents pointers to rows within a table using values that take from 2 to 8 bytes each. The size is determined at table-creation time based on estimates that the server makes about how big the table might become. To give the server a hint when you create the table, you can specify an option in the CREATE TABLE statement that indicates how many rows the table must be able to hold. You can change the option later with ALTER TABLE should the table need to become larger.

If a table reaches the row limit allowed by its row pointer size, a data file full error occurs and you cannot add any more rows. This error is unrelated to running of out disk space or reaching the maximum file size allowed by MyISAM or the filesystem. To "pre-size" a table when you create it, use a MAX_ROWS option to indicate how many rows the table must be able to hold. The following statement indicates to MySQL that the table must be able to contain two million rows:







CREATE TABLE t (i INT) MAX_ROWS = 2000000;

To set or change the MAX_ROWS value for an existing table, use ALTER TABLE:







ALTER TABLE t MAX_ROWS = 4000000;

When you expect a table to contain many rows, MAX_ROWS is useful for telling MySQL that it needs to use larger internal row pointers. Conversely, if you know a table will be small, specifying a small MAX_ROWS value tells MySQL to use smaller pointers. This saves space and improves table processing efficiency.

A related option, AVG_ROW_LENGTH, also gives the server information that it can use to estimate how large the table may become. This option is unnecessary for tables with fixed-length rows because the server knows how long each row is. The option might be helpful for tables with variable-length rows.

The two options may be used separately or together. For example, if a table has a BIGINT column (8 bytes each) and a VARCHAR(255) column where you expect the average string length to be 100 bytes, you can specify an AVG_ROW_LENGTH value of 108. If you also want to make sure that the table can hold four million rows, create it like this:







CREATE TABLE t (i BIGINT, c VARCHAR(255))

AVG_ROW_LENGTH = 108 MAX_ROWS = 4000000;

Note that using MAX_ROWS and AVG_ROW_LENGTH does not allow the size of MyISAM table files to be expanded beyond the limit of what the filesystem allows. For example, if you create a MyISAM table on a filesystem that only allows file sizes up to 2GB, you cannot add more rows once the datafile or index file reach 2GB, no matter what value you set MAX_ROWS to.

To find the values of MAX_ROWS and AVG_ROW_LENGTH for a table, use SHOW TABLE STATUS and check the Create_options field of the output:







mysql> SHOW TABLE STATUS LIKE 't'\G

*************************** 1. row ***************************

           Name: t

           Type: MyISAM

     Row_format: Dynamic

           Rows: 0

 Avg_row_length: 0

    Data_length: 0

Max_data_length: 4294967295

   Index_length: 1024

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2003-10-28 11:36:04

    Update_time: 2003-10-28 11:36:04

     Check_time: NULL

 Create_options: max_rows=4000000 avg_row_length=108

        Comment:

14.1.3 MyISAM Table Symlinking

By default, a MyISAM table for a given database is created in the database directory under the data directory. This means that the .frm, .MYD, and .MYI files are created in the database directory. It's possible to create the table such that the datafile or index file (or both) are located elsewhere. You might do this to distribute storage for the table to a filesystem with more free space, for example. If the filesystem is on a different physical disk, moving the files has the additional effect of distributing database-related disk activity, which might improve performance.

To relocate a table's datafile or index file, use the DATA DIRECTORY or INDEX DIRECTORY options in the CREATE TABLE statement. For example, to put both files for a table t in the directory /var/mysql-alt/data/test, use a statement like this:







CREATE TABLE t (i INT)

DATA DIRECTORY = '/var/mysql-alt/data/test'

INDEX DIRECTORY = '/var/mysql-alt/data/test';

This statement puts the data and index files in the same directory. To put the files in different directories, specify different pathnames for each option. You can also relocate one file only and leave the other in its default location (the database directory) by omitting either the DATA DIRECTORY or the INDEX DIRECTORY other from the CREATE TABLE statement. Directory names for DATA DIRECTORY and INDEX DIRECTORY must be specified as full (absolute) pathnames, not as relative pathnames.

The server implements datafile or index file relocation by creating the file in the directory that you specify and placing in the database directory a symbolic link to the file. You can do the same thing manually to relocate an existing MyISAM table's datafile or index file, as long as the server does not have the table open and is not using it. For each file to be relocated, use this procedure:

  • Move the file to a different directory.

  • Create a symlink in the database directory that points to the new location of the moved file.

Table symlinking is subject to the following restrictions:

  • It does not work on Windows.

  • On Unix, the operating system must have a working realpath() system call, and must have thread-safe symlinks.

Relocating MyISAM datafiles and index files as just described makes it somewhat more difficult to keep track of just where your table files are located or how table storage space is distributed among your filesystems. Thus, although it's possible to relocate MyISAM tables using symlinking, it isn't necessarily recommended as an everyday technique. If you're thinking about relocating several MyISAM tables in a database, consider the simpler alternative of relocating the entire database directory and replacing the original database directory under the data directory with a symbolic link that points to the new location. This is just as effective as moving many tables individually, but requires only a single symlink. The server should not be running while you relocate a database directory. For instructions, see section 11.7.1, "Moving Databases Using Symbolic Links."

14.1.4 Using MyISAM RAID Tables

The MyISAM storage engine supports splitting a table's datafile into a set of files. Using a set of files for table data allows the amount of table data to exceed the maximum size of a single file. This is useful on systems that have a small maximum file size.

To create a MyISAM table that has a split data file, use the following table options:

  • RAID_TYPE = type

    This option identifies the table as a RAID table. type can be set to 1, STRIPED, or RAID0. All three values are synonymous.

  • RAID_CHUNKS = n

    n indicates the number of files to use for storing table data.

  • RAID_CHUNKSIZE = n

    n indicates the amount of space to allocate for each file, in MB.

The following statement creates a RAID table for which MySQL allocates 100 files of size 2,000MB each:







CREATE TABLE big_table ( ... )

RAID_TYPE = 1 RAID_CHUNKS = 100 RAID_CHUNKSIZE = 2000;

The RAID options apply only to the datafile, not to the index file. The indexes are still stored in a single file, so if a table's size is limited by the index file size, reconfiguring the table as a RAID table will not help.

To use MyISAM RAID tables, your server must have been configured with the --with-raid option when it was built. To find out whether a server supports RAID tables, issue the following statement:







mysql> SHOW VARIABLES LIKE 'have_raid';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| have_raid     | YES   |

+---------------+-------+

    Previous Section  < Day Day Up >  Next Section