7.1 MyISAM Tables

MyISAM is the default table type in MySQL Version 3.23. It's based on the ISAM code and has a lot of useful extensions.

The index is stored in a file with the `.MYI' (MYIndex) extension, and the data is stored in a file with the `.MYD' (MYData) extension. You can check/repair MyISAM tables with the myisamchk utility. See section Using myisamchk for Crash Recovery. You can compress MyISAM tables with myisampack to take up much less space. See section myisampack.

The following is new in MyISAM:

• There is a flag in the MyISAM file that indicates whether the table was closed correctly. If mysqld is started with --myisam-recover, MyISAM tables will automatically be checked and/or repaired on open if the table wasn't closed properly.
• You can INSERT new rows in a table that doesn't have free blocks in the middle of the datafile, at the same time other threads are reading from the table (concurrent insert). A free block can come from an update of a dynamic length row with much data to a row with less data or when deleting rows. When all free blocks are used up, all future inserts will be concurrent again.
• Support for big files (63-bit) on filesystems/operating systems that support big files.
• All data is stored with the low byte first. This makes the data machine and OS independent. The only requirement for binary portability is that the machine uses two's-complement signed integers (as every machine for the last 20 years has) and IEEE floating-point format (also totally dominant among mainstream machines). The only area of machines that may not support binary compatibility are embedded systems (because they sometimes have peculiar processors).

  There is no big speed penalty in storing data low byte first; the bytes in a table row is normally unaligned and it doesn't take that much more power to read an unaligned byte in order than in reverse order. The actual fetch-column-value code is also not time critical compared to other code.

• All number keys are stored with high byte first to give better index compression.
• Internal handling of one AUTO_INCREMENT column. MyISAM will automatically update this on INSERT/UPDATE. The AUTO_INCREMENT value can be reset with myisamchk. This will make AUTO_INCREMENT columns faster (at least 10%) and old numbers will not be reused as with the old ISAM. Note that when an AUTO_INCREMENT is defined on the end of a multi-part-key the old behaviour is still present.
• When inserted in sorted order (as when you are using an AUTO_INCREMENT column) the key tree will be split so that the high node only contains one key. This will improve the space utilisation in the key tree.
• BLOB and TEXT columns can be indexed.
• NULL values are allowed in indexed columns. This takes 0-1 bytes/key.
• Maximum key length is 500 bytes by default (can be changed by recompiling). In cases of keys longer than 250 bytes, a bigger key block size than the default of 1024 bytes is used for this key.
• Maximum number of keys/table is 32 as default. This can be enlarged to 64 without having to recompile myisamchk.
• myisamchk will mark tables as checked if one runs it with --update-state. myisamchk --fast will only check those tables that don't have this mark.
• myisamchk -a stores statistics for key parts (and not only for whole keys as in ISAM).
• Dynamic size rows will now be much less fragmented when mixing deletes with updates and inserts. This is done by automatically combining adjacent deleted blocks and by extending blocks if the next block is deleted.
• myisampack can pack BLOB and VARCHAR columns.
• You can use put the datafile and index file on different directories to get more speed (with the DATA/INDEX DIRECTORY="path" option to CREATE TABLE). See section CREATE TABLE Syntax.

MyISAM also supports the following things, which MySQL will be able to use in the near future:

• Support for a true VARCHAR type; a VARCHAR column starts with a length stored in 2 bytes.
• Tables with VARCHAR may have fixed or dynamic record length.
• VARCHAR and CHAR may be up to 64K. All key segments have their own language definition. This will enable MySQL to have different language definitions per column.
• A hashed computed index can be used for UNIQUE. This will allow you to have UNIQUE on any combination of columns in a table. (You can't search on a UNIQUE computed index, however.)

Note that index files are usually much smaller with MyISAM than with ISAM. This means that MyISAM will normally use less system resources than ISAM, but will need more CPU time when inserting data into a compressed index.

The following options to mysqld can be used to change the behaviour of MyISAM tables. See section SHOW VARIABLES.

The automatic recovery is activated if you start mysqld with --myisam-recover=#. See section mysqld Command-line Options. On open, the table is checked if it's marked as crashed or if the open count variable for the table is not 0 and you are running with --skip-external-locking. If either of the above is true the following happens.

• The table is checked for errors.
• If we found an error, try to do a fast repair (with sorting and without re-creating the datafile) of the table.
• If the repair fails because of an error in the datafile (for example a duplicate key error), we try again, but this time we re-create the datafile.
• If the repair fails, retry once more with the old repair option method (write row by row without sorting) which should be able to repair any type of error with little disk requirements..

If the recover wouldn't be able to recover all rows from a previous completed statement and you didn't specify FORCE as an option to myisam-recover, then the automatic repair will abort with an error message in the error file:

Error: Couldn't repair table: test.g00pages

If you in this case had used the FORCE option you would instead have got a warning in the error file:

Warning: Found 344 of 354 rows when repairing ./test/g00pages

Note that if you run automatic recover with the BACKUP option, you should have a cron script that automatically moves file with names like `tablename-datetime.BAK' from the database directories to a backup media.

See section mysqld Command-line Options.

7.1.1 Space Needed for Keys

MySQL can support different index types, but the normal type is ISAM or MyISAM. These use a B-tree index, and you can roughly calculate the size for the index file as (key_length+4)/0.67, summed over all keys. (This is for the worst case when all keys are inserted in sorted order and we don't have any compressed keys.)

String indexes are space compressed. If the first index part is a string, it will also be prefix compressed. Space compression makes the index file smaller than the above figures if the string column has a lot of trailing space or is a VARCHAR column that is not always used to the full length. Prefix compression is used on keys that start with a string. Prefix compression helps if there are many strings with an identical prefix.

In MyISAM tables, you can also prefix compress numbers by specifying PACK_KEYS=1 when you create the table. This helps when you have many integer keys that have an identical prefix when the numbers are stored high-byte first.

7.1.2 MyISAM Table Formats

MyISAM supports 3 different table types. Two of them are chosen automatically depending on the type of columns you are using. The third, compressed tables, can only be created with the myisampack tool.

When you CREATE or ALTER a table you can for tables that doesn't have BLOBs force the table format to DYNAMIC or FIXED with the ROW_FORMAT=# table option. In the future you will be able to compress/decompress tables by specifying ROW_FORMAT=compressed | default to ALTER TABLE. See section CREATE TABLE Syntax.

7.1.2.1 Static (Fixed-length) Table Characteristics

This is the default format. It's used when the table contains no VARCHAR, BLOB, or TEXT columns.

This format is the simplest and most secure format. It is also the fastest of the on-disk formats. The speed comes from the easy way data can be found on disk. When looking up something with an index and static format it is very simple. Just multiply the row number by the row length.

Also, when scanning a table it is very easy to read a constant number of records with each disk read.

The security is evidenced if your computer crashes when writing to a fixed-size MyISAM file, in which case myisamchk can easily figure out where each row starts and ends. So it can usually reclaim all records except the partially written one. Note that in MySQL all indexes can always be reconstructed:

• All CHAR, NUMERIC, and DECIMAL columns are space-padded to the column width.
• Very quick.
• Easy to cache.
• Easy to reconstruct after a crash, because records are located in fixed positions.
• Doesn't have to be reorganised (with myisamchk) unless a huge number of records are deleted and you want to return free disk space to the operating system.
• Usually requires more disk space than dynamic tables.

7.1.2.2 Dynamic Table Characteristics

This format is used if the table contains any VARCHAR, BLOB, or TEXT columns or if the table was created with ROW_FORMAT=dynamic.

This format is a little more complex because each row has to have a header that says how long it is. One record can also end up at more than one location when it is made longer at an update.

You can use OPTIMIZE table or myisamchk to defragment a table. If you have static data that you access/change a lot in the same table as some VARCHAR or BLOB columns, it might be a good idea to move the dynamic columns to other tables just to avoid fragmentation:

• All string columns are dynamic (except those with a length less than 4).
• Each record is preceded by a bitmap indicating which columns are empty ('') for string columns, or zero for numeric columns. (This isn't the same as columns containing NULL values.) If a string column has a length of zero after removal of trailing spaces, or a numeric column has a value of zero, it is marked in the bit map and not saved to disk. Non-empty strings are saved as a length byte plus the string contents.
• Usually takes much less disk space than fixed-length tables.
• Each record uses only as much space as is required. If a record becomes larger, it is split into as many pieces as are required. This results in record fragmentation.
• If you update a row with information that extends the row length, the row will be fragmented. In this case, you may have to run myisamchk -r from time to time to get better performance. Use myisamchk -ei tbl_name for some statistics.
• Not as easy to reconstruct after a crash, because a record may be fragmented into many pieces and a link (fragment) may be missing.
• The expected row length for dynamic sized records is:

  3 + (number of columns + 7) / 8 + (number of char columns) + packed size of numeric columns + length of strings + (number of NULL columns + 7) / 8

  There is a penalty of 6 bytes for each link. A dynamic record is linked whenever an update causes an enlargement of the record. Each new link will be at least 20 bytes, so the next enlargement will probably go in the same link. If not, there will be another link. You may check how many links there are with myisamchk -ed. All links may be removed with myisamchk -r.

7.1.2.3 Compressed Table Characteristics

This is a read-only type that is generated with the optional myisampack tool (pack_isam for ISAM tables):

• All MySQL distributions, even those that existed before MySQL went GPL, can read tables that were compressed with myisampack.
• Compressed tables take very little disk space. This minimises disk usage, which is very nice when using slow disks (like CD-ROMs).
• Each record is compressed separately (very little access overhead). The header for a record is fixed (1-3 bytes) depending on the biggest record in the table. Each column is compressed differently. Some of the compression types are:
  • - There is usually a different Huffman table for each column.
  • - Suffix space compression.
  • - Prefix space compression.
  • - Numbers with value 0 are stored using 1 bit.
  • - If values in an integer column have a small range, the column is stored using the smallest possible type. For example, a BIGINT column (8 bytes) may be stored as a TINYINT column (1 byte) if all values are in the range 0 to 255.
  • - If a column has only a small set of possible values, the column type is converted to ENUM.
  • - A column may use a combination of the above compressions.
• Can handle fixed- or dynamic-length records.
• Can be uncompressed with myisamchk.

7.1.3 MyISAM Table Problems

The file format that MySQL uses to store data has been extensively tested, but there are always circumstances that may cause database tables to become corrupted.

7.1.3.1 Corrupted MyISAM Tables

Even if the MyISAM table format is very reliable (all changes to a table is written before the SQL statements returns) , you can still get corrupted tables if some of the following things happens:

• The mysqld process being killed in the middle of a write.
• Unexpected shutdown of the computer (for example, if the computer is turned off).
• A hardware error.
• You are using an external program (like myisamchk) on a live table.
• A software bug in the MySQL or MyISAM code.

Typial typical symptoms for a corrupt table is:

• You get the error Incorrect key file for table: '...'. Try to repair it while selecting data from the table.
• Queries doesn't find rows in the table or returns incomplete data.

You can check if a table is ok with the command CHECK TABLE. See section CHECK TABLE Syntax.

You can repair a corrupted table with REPAIR TABLE. See section REPAIR TABLE Syntax. You can also repair a table, when mysqld is not running with the myisamchk command. myisamchk syntax.

If your tables get corrupted a lot you should try to find the reason for this! See section What To Do If MySQL Keeps Crashing.

In this case the most important thing to know is if the table got corrupted if the mysqld died (one can easily verify this by checking if there is a recent row restarted mysqld in the mysqld error file). If this isn't the case, then you should try to make a test case of this. See section Making a Test Case If You Experience Table Corruption.

7.1.3.2 Clients is using or hasn't closed the table properly

Each MyISAM `.MYI' file has in the header a counter that can be used to check if a table has been closed properly.

If you get the following warning from CHECK TABLE or myisamchk:

# clients is using or hasn't closed the table properly

this means that this counter has come out of sync. This doesn't mean that the table is corrupted, but means that you should at least do a check on the table to verify that it's okay.

The counter works as follows:

• The first time a table is updated in MySQL, a counter in the header of the index files is incremented.
• The counter is not changed during further updates.
• When the last instance of a table is closed (because of a FLUSH or because there isn't room in the table cache) the counter is decremented if the table has been updated at any point.
• When you repair the table or check the table and it was okay, the counter is reset to 0.
• To avoid problems with interaction with other processes that may do a check on the table, the counter is not decremented on close if it was 0.

In other words, the only ways this can go out of sync are:

• The MyISAM tables are copied without a LOCK and FLUSH TABLES.
• MySQL has crashed between an update and the final close. (Note that the table may still be okay, as MySQL always issues writes for everything between each statement.)
• Someone has done a myisamchk --recover or myisamchk --update-stateon a table that was in use by mysqld.
• Many mysqld servers are using the table and one has done a REPAIR or CHECK of the table while it was in use by another server. In this setup the CHECK is safe to do (even if you will get the warning from other servers), but REPAIR should be avoided as it currently replaces the datafile with a new one, which is not signaled to the other servers.

Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:45