13.3 Informations sur une table

To get a description of a table or statistics about it, use the commands shown below. We explain some of the information in more detail later.

isamchk -d nom_table

Runs isamchk in ``describe mode'' to produce a description of your table. If you start the MySQL server using the --skip-locking option, isamchk may report an error for a table that is updated while it runs. However, since isamchk doesn't change the table in describe mode, there isn't any risk of destroying data.

isamchk -d -v nom_table

To produce more information about what isamchk is doing, add -v to tell it to run in verbose mode.

isamchk -eis nom_table

Shows only the most important information from a table. It is slow since it must read the whole table.

isamchk -eiv nom_table

This is like -eis, but tells you what is being done.

Example of isamchk -d output:@:

ISAM file:@: company.ISM
Data records:@: 1403698 Deleted blocks: 0
Recordlength:@: 226
Record format:@: Fixed length

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 isamchk -d -v output:@:

ISAM file:@: company.ISM
Isam-version:@: 2
Creation time:@: 1996-08-28 11:44:22
Recover time:@: 1997-01-12 18:35:29
Data records:@: 1403698 Deleted blocks: 0
Datafile:@: Parts: 1403698 Deleted data: 0
Datafilepointer (bytes):@: 3 Keyfile pointer (bytes): 3
Max datafile length:@: 3791650815 Max keyfile length: 4294967294
Recordlength:@: 226
Record format:@: Fixed length

table description:@:
Key Start Len Index Type Root Blocksize Rec/key
1 2 8 unique double 15845376 1024 1
2 15 10 multip. text packed stripped 25062400 1024 2
3 219 8 multip. double 40907776 1024 73
4 63 10 multip. text packed stripped 48097280 1024 5
5 167 2 multip. unsigned short 55200768 1024 4840
6 177 4 multip. unsigned long 65145856 1024 1346
7 155 4 multip. text 75090944 1024 4995
8 138 4 multip. unsigned long 85036032 1024 87
9 177 4 multip. unsigned long 96481280 1024 178
193 1 text

Example of isamchk -eis output:@:

Checking ISAM file:@: company.ISM
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
Recordblocks:@: 1403698 Deleteblocks: 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 isamchk -eiv output:@:

Checking ISAM file:@: company.ISM
Data records:@: 1403698 Deleted blocks: 0
- check file-size
- check delete-chain
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
Recordblocks:@: 1403698 Deleteblocks: 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

Here are the sizes of the data and index files for the table used in the preceding examples:@:

-rw-rw-r-- 1 monty tcx 317235748 Jan 12 17:@:30 company.ISD
-rw-rw-r-- 1 davida tcx 96482304 Jan 12 18:@:35 company.ISM

Explanations for the types of information isamchk produces are given below. The ``keyfile'' is the index file. ``Record'' and ``row'' are synonymous.

ISAM file

Name of the ISAM (index) file.

Isam-version

Version of ISAM 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 records are in the table.

Deleted blocks

How many deleted blocks still have reserved space. You can optimize your table to minimize this space. 13.4.3 Optimisation de tables.

Datafile:@: Parts

For dynamic record format, this indicates how many data blocks there are. For an optimized table without fragmented records, this is the same as Data records.

Deleted data

How many bytes of non-reclaimed deleted data there are. You can optimize your table to minimize this space. 13.4.3 Optimisation de tables.

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 record 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's data file (.ISD file) can become, in bytes.

Max keyfile length

How long the table's key file (.ISM file) can become, in bytes.

Recordlength

How much space each record takes, in bytes.

Record format

The format used to store table rows. The examples shown above use Fixed length. Other possible values are Compressed and Packed.

table description

A list of all keys in the table. For each key, some low-level information is presented:@:

Key

This key's number.

Start

Where in the record this index part starts.

Len

How long this index part 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

unique or multip. (multiple). Indicates whether or not one value can exist multiple times in this index.

Type

What data-type this index part has. This is an NISAM data-type with the options 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.

Rec/key

This is a statistical value used by the optimizer. It tells how many records there are per value for this key. A unique key always has a value of 1. This may be updated after a table is loaded (or greatly changed) with isamchk -a. If this is not updated at all, a default value of 30 is given.

In the first example above, the 9th key is a multi-part key with two parts.

Keyblocks used

What percentage of the keyblocks are used. Since the table used in the examples had just been reorganized with isamchk, the values are very high (very near the theoretical maximum).

Packed

MySQL tries to pack keys with a common suffix. This can only be used for CHAR/VARCHAR/DECIMAL keys. For long strings like names, this can significantly reduce the space used. In the third example above, the 4th 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 keys get high values.

Records

How many rows are in the table.

M.recordlength

The average record length. For tables with fixed-length records, this is the exact record length.

Packed

MySQL strips spaces from the end of strings. The Packed value indicates the percentage 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 record (i.e., how many links a fragmented record is composed of). This is always 1 for fixed-format tables. This value should stay as close to 1.0 as possible. If it gets too big, you can reorganize the table with isamchk. 13.4.3 Optimisation de tables.

Recordblocks

How many blocks (links) are used. For fixed format, this is the same as the number of records.

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 record 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, record 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 pack_isam, isamchk -d prints additional information about each table column. See pack_isam, for an example of this information and a description of what it means.