`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. *Note
Crash recovery::. You can compress `MyISAM' tables with `myisampack' to
take up much less space. *Note `myisampack': myisampack.
The following is new in `MyISAM':
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').
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'). *Note CREATE TABLE::.
`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.
data
into a compressed index.
The following options to `mysqld' can be used to change the behaviour of
`MyISAM' tables. *Note SHOW VARIABLES::.
*Option* *Description*
`--myisam-recover=#' Automatic recovery of crashed tables.
`-O Buffer used when recovering tables.
myisam_sort_buffer_size=#'
`--delay-key-write=ALL' Don't flush key buffers between writes for
any MyISAM table
`-O Used to help MySQL to decide when to use
myisam_max_extra_sort_file_size=#'the slow but safe key cache index create
method. *Note* that this parameter is
given in megabytes before 4.0.3 and in
bytes beginning with this version.
`-O Don't use the fast sort index method to
myisam_max_sort_file_size=#' created index if the temporary file would
get bigger than this. *Note* that this
parameter is given in megabytes before
4.0.3 and in bytes beginning with this
version.
`-O Size of tree cache used in bulk insert
bulk_insert_buffer_size=#' optimisation. *Note* that this is a limit
*per thread*!
The automatic recovery is activated if you start `mysqld' with
`--myisam-recover=#'. *Note 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
a backup
media.
*Note Command-line options::.
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.
`MyISAM' Table Formats
----------------------
tool.
to
compress/decompress tables by specifying `ROW_FORMAT=compressed |
default' to `ALTER TABLE'. *Note CREATE TABLE::.
Static (Fixed-length) Table Characteristics
...........................................
This is the default format. It's used when the table contains no
`VARCHAR', `BLOB', or `TEXT' columns.
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.
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'.
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'.
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'.
`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.
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'. *Note
CHECK TABLE::.
You can repair a corrupted table with `REPAIR TABLE'. *Note REPAIR
TABLE::. 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! *Note Crashing::.
file).
If this isn't the case, then you should try to make a test case of
this. *Note Reproduceable test case::.
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 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-state'on 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.
[Назад] [Содержание] [Вперед]
| Главная |