|< Day Day Up >|
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:
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.
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.
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:
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:
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.
mysql> SHOW VARIABLES LIKE 'have_raid'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_raid | YES | +---------------+-------+
|< Day Day Up >|