(mysql.info) table-info
Info Catalog
(mysql.info) table-optimization
(mysql.info) table-maintenance
(mysql.info) maintenance-schedule
5.10.4.5 Getting Information About a Table
..........................................
To obtain a description of a table or statistics about it, use the
commands shown here. We explain some of the information in more detail
later.
* `myisamchk -d TBL_NAME'
Runs `myisamchk' in `describe mode' to produce a description of
your table. If you start the MySQL 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 -d -v TBL_NAME'
Adding -v runs `myisamchk' in verbose mode so that it produces
more information about what it is doing.
* `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.
Sample output for some of these commands follows. They are based on a
table with these data and index file sizes:
-rw-rw-r-- 1 monty tcx 317235748 Jan 12 17:30 company.MYD
-rw-rw-r-- 1 davida tcx 96482304 Jan 12 18:35 company.MYI
Example of `myisamchk -d' output:
MyISAM file: company.MYI
Record format: Fixed length
Data records: 1403698 Deleted blocks: 0
Recordlength: 226
table description:
Key Start Len Index Type
1 2 8 unique double
2 15 10 multip. text packed stripped
3 219 8 multip. double
4 63 10 multip. text packed stripped
5 167 2 multip. unsigned short
6 177 4 multip. unsigned long
7 155 4 multip. text
8 138 4 multip. unsigned long
9 177 4 multip. unsigned long
193 1 text
Example of `myisamchk -d -v' output:
MyISAM file: company
Record format: Fixed length
File-version: 1
Creation time: 1999-10-30 12:12:51
Recover time: 1999-10-31 19:13:01
Status: checked
Data records: 1403698 Deleted blocks: 0
Datafile parts: 1403698 Deleted data: 0
Datafile pointer (bytes): 3 Keyfile pointer (bytes): 3
Max datafile length: 3791650815 Max keyfile length: 4294967294
Recordlength: 226
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 8 unique double 1 15845376 1024
2 15 10 multip. text packed stripped 2 25062400 1024
3 219 8 multip. double 73 40907776 1024
4 63 10 multip. text packed stripped 5 48097280 1024
5 167 2 multip. unsigned short 4840 55200768 1024
6 177 4 multip. unsigned long 1346 65145856 1024
7 155 4 multip. text 4995 75090944 1024
8 138 4 multip. unsigned long 87 85036032 1024
9 177 4 multip. unsigned long 178 96481280 1024
193 1 text
Example of `myisamchk -eis' output:
Checking MyISAM file: company
Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 4
Key: 2: Keyblocks used: 98% Packed: 50% Max levels: 4
Key: 3: Keyblocks used: 97% Packed: 0% Max levels: 4
Key: 4: Keyblocks used: 99% Packed: 60% Max levels: 3
Key: 5: Keyblocks used: 99% Packed: 0% Max levels: 3
Key: 6: Keyblocks used: 99% Packed: 0% Max levels: 3
Key: 7: Keyblocks used: 99% Packed: 0% Max levels: 3
Key: 8: Keyblocks used: 99% Packed: 0% Max levels: 3
Key: 9: Keyblocks used: 98% Packed: 0% Max levels: 4
Total: Keyblocks used: 98% Packed: 17%
Records: 1403698 M.recordlength: 226
Packed: 0%
Recordspace used: 100% Empty space: 0%
Blocks/Record: 1.00
Record blocks: 1403698 Delete blocks: 0
Recorddata: 317235748 Deleted data: 0
Lost space: 0 Linkdata: 0
User time 1626.51, System time 232.36
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 627, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 639, Involuntary context switches 28966
Example of `myisamchk -eiv' output:
Checking MyISAM file: company
Data records: 1403698 Deleted blocks: 0
- check file-size
- check delete-chain
block_size 1024:
index 1:
index 2:
index 3:
index 4:
index 5:
index 6:
index 7:
index 8:
index 9:
No recordlinks
- check index reference
- check data record references index: 1
Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 4
- check data record references index: 2
Key: 2: Keyblocks used: 98% Packed: 50% Max levels: 4
- check data record references index: 3
Key: 3: Keyblocks used: 97% Packed: 0% Max levels: 4
- check data record references index: 4
Key: 4: Keyblocks used: 99% Packed: 60% Max levels: 3
- check data record references index: 5
Key: 5: Keyblocks used: 99% Packed: 0% Max levels: 3
- check data record references index: 6
Key: 6: Keyblocks used: 99% Packed: 0% Max levels: 3
- check data record references index: 7
Key: 7: Keyblocks used: 99% Packed: 0% Max levels: 3
- check data record references index: 8
Key: 8: Keyblocks used: 99% Packed: 0% Max levels: 3
- check data record references index: 9
Key: 9: Keyblocks used: 98% Packed: 0% Max levels: 4
Total: Keyblocks used: 9% Packed: 17%
- check records and index references
*** LOTS OF ROW NUMBERS DELETED ***
Records: 1403698 M.recordlength: 226 Packed: 0%
Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00
Record blocks: 1403698 Delete blocks: 0
Recorddata: 317235748 Deleted data: 0
Lost space: 0 Linkdata: 0
User time 1639.63, System time 251.61
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0
Blocks in 4 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 10604, Involuntary context switches 122798
Explanations for the types of information `myisamchk' produces are
given here. `Keyfile' refers to the index file. `Record' and `row'
are synonymous.
* `MyISAM file'
Name of the `MyISAM' (index) file.
* `File-version'
Version of `MyISAM' format. Currently always 2.
* `Creation time'
When the data file was created.
* `Recover time'
When the index/data file was last reconstructed.
* `Data records'
How many rows are in the table.
* `Deleted blocks'
How many deleted blocks still have reserved space. You can
optimize your table to minimize this space. See
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
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 MySQL 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 MySQL. 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.
* `Record format'
The format used to store table rows. The preceding examples use
`Fixed length'. Other possible values are `Compressed' and
`Packed'.
* `table description'
A list of all keys in the table. For each key, `myisamchk'
displays some low-level information:
* `Key'
This key's number.
* `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.
* `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 MySQL 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.
For the table shown in the examples, there are two `table
description' lines for the ninth index. This indicates that it is
a multiple-part index with two parts.
* `Keyblocks used'
What percentage of the keyblocks are used. When a table has just
been reorganized with `myisamchk', as for the table in the
examples, the values are very high (very near the theoretical
maximum).
* `Packed'
MySQL 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 third of the preceding
examples, the fourth key is 10 characters long and a 60% 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'
MySQL 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
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.
If a table has been compressed with `myisampack', `myisamchk -d' prints
additional information about each table column. See myisampack,
for an example of this information and a description of what it means.
Info Catalog
(mysql.info) table-optimization
(mysql.info) table-maintenance
(mysql.info) maintenance-schedule
automatically generated byinfo2html