To obtain a description of a MyISAM table or statistics about it,
use the commands shown here. The output from these commands is
explained later in this section.
• myisamchk -d tbl_name
Runs myisamchk in 'describe mode' to produce a description of
your table. If you start the MariaDB server with external
locking disabled, myisamchk may report an error for a table
that is updated while it runs. However, because myisamchk
does not change the table in describe mode, there is no risk
of destroying data.
• myisamchk -dv tbl_name
Adding -v runs myisamchk in verbose mode so that it produces
more information about the table. Adding -v a second time
produces even more information.
• myisamchk -eis tbl_name
Shows only the most important information from a table. This
operation is slow because it must read the entire table.
• myisamchk -eiv tbl_name
This is like -eis, but tells you what is being done.
The tbl_name argument can be either the name of a MyISAM table or
the name of its index file, as described in myisamchk(1).
Multiple tbl_name arguments can be given.
Suppose that a table named person has the following structure.
(The MAX_ROWS table option is included so that in the example
output from myisamchk shown later, some values are smaller and
fit the output format more easily.)
CREATE TABLE person
(
id INT NOT NULL AUTO_INCREMENT,
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
birth DATE,
death DATE,
PRIMARY KEY (id),
INDEX (last_name, first_name),
INDEX (birth)
) MAX_ROWS = 1000000;
Suppose also that the table has these data and index file sizes:
-rw-rw---- 1 mysql mysql 9347072 Aug 19 11:47 person.MYD
-rw-rw---- 1 mysql mysql 6066176 Aug 19 11:47 person.MYI
Example of myisamchk -dvv output:
MyISAM file: person
Record format: Packed
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2009-08-19 16:47:41
Recover time: 2009-08-19 16:47:56
Status: checked,analyzed,optimized keys
Auto increment key: 1 Last value: 306688
Data records: 306688 Deleted blocks: 0
Datafile parts: 306688 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 3
Datafile length: 9347072 Keyfile length: 6066176
Max datafile length: 4294967294 Max keyfile length: 17179868159
Recordlength: 54
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 4 unique long 1 99328 1024
2 6 20 multip. varchar prefix 512 3563520 1024
27 20 varchar 512
3 48 3 multip. uint24 NULL 306688 6065152 1024
Field Start Length Nullpos Nullbit Type
1 1 1
2 2 4 no zeros
3 6 21 varchar
4 27 21 varchar
5 48 3 1 1 no zeros
6 51 3 1 2 no zeros
Explanations for the types of information myisamchk produces are
given here. 'Keyfile' refers to the index file. 'Record' and
'row' are synonymous, as are 'field' and 'column.'
The initial part of the table description contains these values:
• MyISAM file
Name of the MyISAM (index) file.
• Record format
The format used to store table rows. The preceding examples
use Fixed length. Other possible values are Compressed and
Packed. (Packed corresponds to what SHOW TABLE STATUS reports
as Dynamic.)
• Chararacter set
The table default character set.
• File-version
Version of MyISAM format. Currently always 1.
• Creation time
When the data file was created.
• Recover time
When the index/data file was last reconstructed.
• Status
Table status flags. Possible values are crashed, open,
changed, analyzed, optimized keys, and sorted index pages.
• Auto increment key, Last value
The key number associated the table´s AUTO_INCREMENT column,
and the most recently generated value for this column. These
fields do not appear if there is no such column.
• Data records
The number of rows in the table.
• Deleted blocks
How many deleted blocks still have reserved space. You can
optimize your table to minimize this space. See
Section 6.6.4, 'MyISAM Table Optimization'.
• Datafile parts
For dynamic-row format, this indicates how many data blocks
there are. For an optimized table without fragmented rows,
this is the same as Data records.
• Deleted data
How many bytes of unreclaimed deleted data there are. You can
optimize your table to minimize this space. See
Section 6.6.4, 'MyISAM Table Optimization'.
• Datafile pointer
The size of the data file pointer, in bytes. It is usually 2,
3, 4, or 5 bytes. Most tables manage with 2 bytes, but this
cannot be controlled from MariaDB yet. For fixed tables, this
is a row address. For dynamic tables, this is a byte address.
• Keyfile pointer
The size of the index file pointer, in bytes. It is usually
1, 2, or 3 bytes. Most tables manage with 2 bytes, but this
is calculated automatically by MariaDB. It is always a block
address.
• Max datafile length
How long the table data file can become, in bytes.
• Max keyfile length
How long the table index file can become, in bytes.
• Recordlength
How much space each row takes, in bytes.
The table description part of the output includes a list of all
keys in the table. For each key, myisamchk displays some
low-level information:
• Key
This key´s number. This value is shown only for the first
column of the key. If this value is missing, the line
corresponds to the second or later column of a
multiple-column key. For the table shown in the example,
there are two table description lines for the second index.
This indicates that it is a multiple-part index with two
parts.
• Start
Where in the row this portion of the index starts.
• Len
How long this portion of the index is. For packed numbers,
this should always be the full length of the column. For
strings, it may be shorter than the full length of the
indexed column, because you can index a prefix of a string
column. The total length of a multiple-part key is the sum of
the Len values for all key parts.
• Index
Whether a key value can exist multiple times in the index.
Possible values are unique or multip. (multiple).
• Type
What data type this portion of the index has. This is a
MyISAM data type with the possible values packed, stripped,
or empty.
• Root
Address of the root index block.
• Blocksize
The size of each index block. By default this is 1024, but
the value may be changed at compile time when MariaDB is
built from source.
• Rec/key
This is a statistical value used by the optimizer. It tells
how many rows there are per value for this index. A unique
index always has a value of 1. This may be updated after a
table is loaded (or greatly changed) with myisamchk -a. If
this is not updated at all, a default value of 30 is given.
The last part of the output provides information about each
column:
• Field
The column number.
• Start
The byte position of the column within table rows.
• Length
The length of the column in bytes.
• Nullpos, Nullbit
For columns that can be NULL, MyISAM stores NULL values as a
flag in a byte. Depending on how many nullable columns there
are, there can be one or more bytes used for this purpose.
The Nullpos and Nullbit values, if nonempty, indicate which
byte and bit contains that flag indicating whether the column
is NULL.
The position and number of bytes used to store NULL flags is
shown in the line for field 1. This is why there are six
Field lines for the person table even though it has only five
columns.
• Type
The data type. The value may contain any of the following
descriptors:
• constant
All rows have the same value.
• no endspace
Do not store endspace.
• no endspace, not_always
Do not store endspace and do not do endspace compression
for all values.
• no endspace, no empty
Do not store endspace. Do not store empty values.
• table-lookup
The column was converted to an ENUM.
• zerofill(N)
The most significant N bytes in the value are always 0
and are not stored.
• no zeros
Do not store zeros.
• always zero
Zero values are stored using one bit.
• Huff tree
The number of the Huffman tree associated with the column.
• Bits
The number of bits used in the Huffman tree.
The Huff tree and Bits fields are displayed if the table has been
compressed with myisampack. See myisampack(1), for an example of
this information.
Example of myisamchk -eiv output:
Checking MyISAM file: person
Data records: 306688 Deleted blocks: 0
- check file-size
- check record delete-chain
No recordlinks
- check key delete-chain
block_size 1024:
- check index reference
- check data record references index: 1
Key: 1: Keyblocks used: 98% Packed: 0% Max levels: 3
- check data record references index: 2
Key: 2: Keyblocks used: 99% Packed: 97% Max levels: 3
- check data record references index: 3
Key: 3: Keyblocks used: 98% Packed: -14% Max levels: 3
Total: Keyblocks used: 98% Packed: 89%
- check records and index references
*** LOTS OF ROW NUMBERS DELETED ***
Records: 306688 M.recordlength: 25 Packed: 83%
Recordspace used: 97% Empty space: 2% Blocks/Record: 1.00
Record blocks: 306688 Delete blocks: 0
Record data: 7934464 Deleted data: 0
Lost space: 256512 Linkdata: 1156096
User time 43.08, System time 1.68
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 0, Physical pagefaults 0, Swaps 0
Blocks in 0 out 7, Messages in 0 out 0, Signals 0
Voluntary context switches 0, Involuntary context switches 0
Maximum memory usage: 1046926 bytes (1023k)
myisamchk -eiv output includes the following information:
• Data records
The number of rows in the table.
• Deleted blocks
How many deleted blocks still have reserved space. You can
optimize your table to minimize this space. See
Section 6.6.4, 'MyISAM Table Optimization'.
• Key
The key number.
• Keyblocks used
What percentage of the keyblocks are used. When a table has
just been reorganized with myisamchk, the values are very
high (very near theoretical maximum).
• Packed
MariaDB tries to pack key values that have a common suffix.
This can only be used for indexes on CHAR and VARCHAR
columns. For long indexed strings that have similar leftmost
parts, this can significantly reduce the space used. In the
preceding example, the second key is 40 bytes long and a 97%
reduction in space is achieved.
• Max levels
How deep the B-tree for this key is. Large tables with long
key values get high values.
• Records
How many rows are in the table.
• M.recordlength
The average row length. This is the exact row length for
tables with fixed-length rows, because all rows have the same
length.
• Packed
MariaDB strips spaces from the end of strings. The Packed
value indicates the percentage of savings achieved by doing
this.
• Recordspace used
What percentage of the data file is used.
• Empty space
What percentage of the data file is unused.
• Blocks/Record
Average number of blocks per row (that is, how many links a
fragmented row is composed of). This is always 1.0 for
fixed-format tables. This value should stay as close to 1.0
as possible. If it gets too large, you can reorganize the
table. See Section 6.6.4, 'MyISAM Table Optimization'.
• Recordblocks
How many blocks (links) are used. For fixed-format tables,
this is the same as the number of rows.
• Deleteblocks
How many blocks (links) are deleted.
• Recorddata
How many bytes in the data file are used.
• Deleted data
How many bytes in the data file are deleted (unused).
• Lost space
If a row is updated to a shorter length, some space is lost.
This is the sum of all such losses, in bytes.
• Linkdata
When the dynamic table format is used, row fragments are
linked with pointers (4 to 7 bytes each). Linkdata is the
sum of the amount of storage used by all such pointers.
