myisamchk Table Information

myisamchk can be used to obtain information about MyISAM tables, particularly with the -d, -e, -i and -v options.

Common options for gathering information include:

  • myisamchk -d
  • myisamchk -dv
  • myisamchk -dvv
  • myisamchk -ei
  • myisamchk -eiv

The -d option returns a short description of the table and its keys. Running the option while the table is being updated, and with external locking disabled, may result in an error, but no damage will be done to the table. Each extra v adds more output. -e checks the table thoroughly (but slowly), and the -i options adds statistical information,

-dvv output

The following table describes the output from the running myisamchk with the -dvv option:

HeadingDescription
MyISAM fileName and path of the MyISAM index file (without the extension)
Record formatStorage format. One of packed (dynamic), fixed or compressed.
Chararacter setDefault character set for the table.
File-versionAlways 1.
Creation timeTime the data file was created
Recover timeMost recent time the file was reconstructed.
StatusTable status. One or more of analyzed, changed, crashed, open, optimized keys and sorted index pages.
Auto increment keyIndex number of the table's auto-increment column. Not shown if no auto-increment column exists.
Last valueMost recently generated auto-increment value. Not shown if no auto-increment column exists.
Data recordsNumber of records in the table.
Deleted blocksNumber of deleted blocks that are still reserving space. Use OPTIMIZE TABLE to defragment.
Datafile partsFor dynamic tables, the number of data blocks. If the table is optimized, this will match the number of data records.
Deleted dataNumber of bytes of unreclaimed deleted data, Use OPTIMIZE TABLE to reclaim the space.
Datafile pointerSize in bytes of the data file pointer. The size of the data file pointer, in bytes.
Keyfile pointerSize in bytes of the index file pointer.
Max datafile lengthMaximum length, in bytes, that the data file could become.
Max keyfile lengthMaximum length, in bytes, that the index file could become.
RecordlengthSpace, in bytes, that each row takes.
table descriptionDescription of all indexes in the table, followed by all columns
KeyIndex number, starting with one. If not shown, the index is part of a multiple-column index.
StartWhere the index part starts in the row.
LenLength of the index or index part. The length of a multiple-column index is the sum of the component lengths. Indexes of string columns will be shorter than the full column length if only a string prefix is indexed.
IndexWhether an index value is unique or not. Either multip. or unique.
TypeData type of the index of index part.
Rec/keyRecord of the number of rows per value for the index or index part. Used by the optimizer to calculate query plans. Can be updated with myisamchk-a. If not present, defaults to 30.
RootRoot index block address.
BlocksizeIndex block size, in bytes.
FieldColumn number, starting with one. The first line will contain the position and number of bytes used to store NULL flags, if any (see Nullpos and Nullbit, below).
StartColumn's byte position within the table row.
LengthColumn length, in bytes.
NullposByte containing the flag for NULL values. Empty if column cannot be NULL.
NullbitBit containing the flag for NULL values. Empty if column cannot be NULL.
TypeData type - see the table below for a list of possible values.
Huff treeOnly present for packed tables, contains the Huffman tree number associated with the column.
BitsOnly present for packed tables, contains the number of bits used in the Huffman tree.
Data typeDescription
constantAll rows contain the same value.
no endspaceNo endspace is stored.
no endspace, not_alwaysNo endspace is stored, and endspace compression is not always performed for all values.
no endspace, no emptyNo endspace is stored, no empty values are stored.
table-lookupColumn was converted to an ENUM.
zerofill(N)Most significant N bytes of the value are not stored, as they are always zero.
no zerosZeros are not stored.
always zeroZero values are stored with one bit.

-eiv output

The following table describes the output from the running myisamchk with the -eiv option:

HeadingDescription
Data recordsNumber of records in the table.
Deleted blocksNumber of deleted blocks that are still reserving space. Use OPTIMIZE TABLE to defragment.
KeyIndex number, starting with one.
Keyblocks usedPercentage of the keyblocks that are used. Percentages will be higher for optimized tables.
PackedPercentage space saved from packing key values with a common suffix.
Max levelsDepth of the b-tree index for the key. Larger tables and longer key values result in higher values.
RecordsNumber of records in the table.
M.recordlengthAverage row length. For fixed rows, will be the actual length of each row.
PackedPercentage saving from stripping spaces from the end of strings.
Recordspace usedPercentage of the data file used.
Empty spacePercentage of the data file unused.
Blocks/RecordAverage number of blocks per record. Values higher than one indicate fragmentation. Use OPTIMIZE TABLE to defragment.
RecordblocksNumber of used blocks. Will match the number of rows for fixed or optimized tables.
DeleteblocksNumber of deleted blocks
RecorddataUsed bytes in the data file.
Deleted dataUnused bytes in the data file.
Lost spaceTotal bytes lost, such as when a row is updated to a shorter length.
LinkdataSum of the bytes used for pointers linking disconnected blocks. Each is four to seven bytes in size.

Examples

myisamchk -d /var/lib/mysql/test/posts

MyISAM file:         /var/lib/mysql/test/posts
Record format:       Compressed
Character set:       utf8mb4_unicode_ci (224)
Data records:                 1680  Deleted blocks:                 0
Recordlength:                 2758
Using only keys '0' of 5 possibly keys

table description:
Key Start Len Index   Type
1   1     8   unique  ulonglong            
2   2265  80  multip. varchar prefix       
    63    80          varchar              
    17    5           binary               
    1     8           ulonglong            
3   1231  8   multip. ulonglong            
4   9     8   multip. ulonglong            
5   387   764 multip. ? prefix
myisamchk -dvv /var/lib/mysql/test/posts

MyISAM file:         /var/lib/mysql/test/posts
Record format:       Compressed
Character set:       utf8mb4_unicode_ci (224)
File-version:        1
Creation time:       2015-08-10 16:26:54
Recover time:        2015-08-10 16:26:54
Status:              checked,analyzed,optimized keys
Auto increment key:              1  Last value:                  1811
Checksum:               2299272165
Data records:                 1680  Deleted blocks:                 0
Datafile parts:               1680  Deleted data:                   0
Datafile pointer (bytes):        6  Keyfile pointer (bytes):        6
Datafile length:           4298092  Keyfile length:            156672
Max datafile length: 281474976710654  Max keyfile length: 288230376151710719
Recordlength:                 2758
Using only keys '0' of 5 possibly keys

table description:
Key Start Len Index   Type                     Rec/key         Root  Blocksize
1   1     8   unique  ulonglong                      1                    1024
2   2265  80  multip. varchar prefix               336                    1024
    63    80          varchar                      187
    17    5           binary                         1
    1     8           ulonglong                      1
3   1231  8   multip. ulonglong                     10                    1024
4   9     8   multip. ulonglong                    840                    1024
5   387   764 multip. ? prefix                       1                    4096

Field Start Length Nullpos Nullbit Type                         Huff tree  Bits
1     1     8                      zerofill(6)                          1     9
2     9     8                      zerofill(7)                          1     9
3     17    5                                                           1     9
4     22    5                                                           1     9
5     27    12                     blob                                 2     9
6     39    10                     blob                                 3     9
7     49    4                      always zero                          1     9
8     53    10                     blob                                 1     9
9     63    81                     varchar                              4     9
10    144   81                     varchar                              5     5
11    225   81                     varchar                              5     5
12    306   81                     varchar                              1     9
13    387   802                    varchar                              6     9
14    1189  10                     blob                                 1     9
15    1199  10                     blob                                 7     9
16    1209  5                                                           1     9
17    1214  5                                                           1     9
18    1219  12                     blob                                 1     9
19    1231  8                      no zeros, zerofill(6)                1     9
20    1239  1022                   varchar                              7     9
21    2261  4                      always zero                          1     9
22    2265  81                     varchar                              8     8
23    2346  402                    varchar                              2     9
24    2748  8                      no zeros, zerofill(7)                1     9
myisamchk -eiv /var/lib/mysql/test/posts
Checking MyISAM file: /var/lib/mysql/test/posts
Data records:    1680   Deleted blocks:       0
- check file-size
- check record delete-chain
No recordlinks
- check key delete-chain
block_size 1024:
block_size 2048:
block_size 3072:
block_size 4096:
- check index reference
- check data record references index: 1
Key:  1:  Keyblocks used:  92%  Packed:    0%  Max levels:  2
- check data record references index: 2
Key:  2:  Keyblocks used:  93%  Packed:   90%  Max levels:  2
- check data record references index: 3
Key:  3:  Keyblocks used:  92%  Packed:    0%  Max levels:  2
- check data record references index: 4
Key:  4:  Keyblocks used:  92%  Packed:    0%  Max levels:  2
- check data record references index: 5
Key:  5:  Keyblocks used:  88%  Packed:   97%  Max levels:  2
Total:    Keyblocks used:  91%  Packed:   91%

- check records and index references
Records:              1680    M.recordlength:     4102   Packed:             0%
Recordspace used:      100%   Empty space:           0%  Blocks/Record:   1.00
Record blocks:        1680    Delete blocks:         0
Record data:       6892064    Deleted data:          0
Lost space:           1284    Linkdata:           6264

User time 0.11, System time 0.00
Maximum resident set size 3036, Integral resident set size 0
Non-physical pagefaults 925, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 0, Involuntary context switches 74

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.