4.6.5. myisampack — Generate Compressed, Read-Only MyISAM Tables

The myisampack utility compresses MyISAM tables. myisampack works by compressing each column in the table separately. Usually, myisampack packs the data file 40% to 70%.

When the table is used later, the server reads into memory the information needed to decompress columns. This results in much better performance when accessing individual rows, because you only have to uncompress exactly one row.

MySQL uses mmap() when possible to perform memory mapping on compressed tables. If mmap() does not work, MySQL falls back to normal read/write file operations.

Please note the following:

If the mysqld server was invoked with external locking disabled, it is not a good idea to invoke myisampack if the table might be updated by the server during the packing process. It is safest to compress tables with the server stopped.
After packing a table, it becomes read only. This is generally intended (such as when accessing packed tables on a CD).

Invoke myisampack like this:

shell> myisampack [options]
        file_name ...

Each file name argument should be the name of an index (.MYI) file. If you are not in the database directory, you should specify the path name to the file. It is permissible to omit the .MYI extension.

After you compress a table with myisampack, you should use myisamchk -rq to rebuild its indexes. Section 4.6.3, "myisamchk — MyISAM Table-Maintenance Utility".

myisampack supports the following options. It also reads option files and supports the options for processing them described at Section 4.2.3.4, "Command-Line Options that Affect Option-File Handling".

--help, -?

Display a help message and exit.
--backup, -b

Make a backup of each table's data file using the name tbl_name.OLD.
--character-sets-dir=path

The directory where character sets are installed. See Section 10.5, "Character Set Configuration".
--debug[=debug_options], -# [debug_options]

Write a debugging log. A typical debug_options string is 'd:t:o,file_name'. The default is 'd:t:o'.
--force, -f

Produce a packed table even if it becomes larger than the original or if the intermediate file from an earlier invocation of myisampack exists. (myisampack creates an intermediate file named tbl_name.TMD in the database directory while it compresses the table. If you kill myisampack, the .TMD file might not be deleted.) Normally, myisampack exits with an error if it finds that tbl_name.TMD exists. With --force, myisampack packs the table anyway.
--join=big_tbl_name, -j big_tbl_name

Join all tables named on the command line into a single packed table big_tbl_name. All tables that are to be combined must have identical structure (same column names and types, same indexes, and so forth).

big_tbl_name must not exist prior to the join operation. All source tables named on the command line to be merged into big_tbl_name must exist. The source tables are read for the join operation but not modified. The join operation does not create a .frm file for big_tbl_name, so after the join operation finishes, copy the .frm file from one of the source tables and name it big_tbl_name.frm.
--silent, -s

Silent mode. Write output only when errors occur.
--test, -t

Do not actually pack the table, just test packing it.
--tmpdir=path, -T path

Use the named directory as the location where myisampack creates temporary files.
--verbose, -v

Verbose mode. Write information about the progress of the packing operation and its result.
--version, -V

Display version information and exit.
--wait, -w

Wait and retry if the table is in use. If the mysqld server was invoked with external locking disabled, it is not a good idea to invoke myisampack if the table might be updated by the server during the packing process.

The following sequence of commands illustrates a typical table compression session:

shell> ls -l station.*-rw-rw-r--   1 monty    my         994128 Apr 17 19:00 station.MYD-rw-rw-r--   1 monty    my          53248 Apr 17 19:00 station.MYI-rw-rw-r--   1 monty    my           5767 Apr 17 19:00 station.frmshell> myisamchk -dvv stationMyISAM file:     stationIsam-version:  2Creation time: 1996-03-13 10:08:58Recover time:  1997-02-02  3:06:43Data records:              1192  Deleted blocks:              0Datafile parts:            1192  Deleted data:                0Datafile pointer (bytes):     2  Keyfile pointer (bytes):     2Max datafile length:   54657023  Max keyfile length:   33554431Recordlength:               834Record format: Fixed lengthtable description:Key Start Len Index   Type                 Root  Blocksize    Rec/key1   2     4   unique  unsigned long        1024       1024          12   32    30  multip. text                10240       1024          1Field Start Length Type1     1     12     2     43     6     44     10    15     11    206     31    17     32    308     62    359     97    3510    132   3511    167   412    171   1613    187   3514    222   415    226   1616    242   2017    262   2018    282   2019    302   3020    332   421    336   422    340   123    341   824    349   825    357   826    365   227    367   228    369   429    373   430    377   131    378   232    380   833    388   434    392   435    396   436    400   437    404   138    405   439    409   440    413   441    417   442    421   443    425   444    429   2045    449   3046    479   147    480   148    481   7949    560   7950    639   7951    718   7952    797   853    805   154    806   155    807   2056    827   457    831   4shell> myisampack station.MYICompressing station.MYI: (1192 records)- Calculating statisticsnormal:     20  empty-space:   16  empty-zero:     12  empty-fill:  11pre-space:   0  end-space:     12  table-lookups:   5  zero:         7Original trees:  57  After join: 17- Compressing file87.14%Remember to run myisamchk -rq on compressed tablesshell> ls -l station.*-rw-rw-r--   1 monty    my         127874 Apr 17 19:00 station.MYD-rw-rw-r--   1 monty    my          55296 Apr 17 19:04 station.MYI-rw-rw-r--   1 monty    my           5767 Apr 17 19:00 station.frmshell> myisamchk -dvv stationMyISAM file:     stationIsam-version:  2Creation time: 1996-03-13 10:08:58Recover time:  1997-04-17 19:04:26Data records:               1192  Deleted blocks:              0Datafile parts:             1192  Deleted data:                0Datafile pointer (bytes):      3  Keyfile pointer (bytes):     1Max datafile length:    16777215  Max keyfile length:     131071Recordlength:                834Record format: Compressedtable description:Key Start Len Index   Type                 Root  Blocksize    Rec/key1   2     4   unique  unsigned long       10240       1024          12   32    30  multip. text                54272       1024          1Field Start Length Type                         Huff tree  Bits1     1     1      constant                             1     02     2     4      zerofill(1)                          2     93     6     4      no zeros, zerofill(1)                2     94     10    1                                           3     95     11    20     table-lookup                         4     06     31    1                                           3     97     32    30     no endspace, not_always              5     98     62    35     no endspace, not_always, no empty    6     99     97    35     no empty                             7     910    132   35     no endspace, not_always, no empty    6     911    167   4      zerofill(1)                          2     912    171   16     no endspace, not_always, no empty    5     913    187   35     no endspace, not_always, no empty    6     914    222   4      zerofill(1)                          2     915    226   16     no endspace, not_always, no empty    5     916    242   20     no endspace, not_always              8     917    262   20     no endspace, no empty                8     918    282   20     no endspace, no empty                5     919    302   30     no endspace, no empty                6     920    332   4      always zero                          2     921    336   4      always zero                          2     922    340   1                                           3     923    341   8      table-lookup                         9     024    349   8      table-lookup                        10     025    357   8      always zero                          2     926    365   2                                           2     927    367   2      no zeros, zerofill(1)                2     928    369   4      no zeros, zerofill(1)                2     929    373   4      table-lookup                        11     030    377   1                                           3     931    378   2      no zeros, zerofill(1)                2     932    380   8      no zeros                             2     933    388   4      always zero                          2     934    392   4      table-lookup                        12     035    396   4      no zeros, zerofill(1)               13     936    400   4      no zeros, zerofill(1)                2     937    404   1                                           2     938    405   4      no zeros                             2     939    409   4      always zero                          2     940    413   4      no zeros                             2     941    417   4      always zero                          2     942    421   4      no zeros                             2     943    425   4      always zero                          2     944    429   20     no empty                             3     945    449   30     no empty                             3     946    479   1                                          14     447    480   1                                          14     448    481   79     no endspace, no empty               15     949    560   79     no empty                             2     950    639   79     no empty                             2     951    718   79     no endspace                         16     952    797   8      no empty                             2     953    805   1                                          17     154    806   1                                           3     955    807   20     no empty                             3     956    827   4      no zeros, zerofill(2)                2     957    831   4      no zeros, zerofill(1)                2     9

myisampack displays the following kinds of information:

normal

The number of columns for which no extra packing is used.
empty-space

The number of columns containing values that are only spaces. These occupy one bit.
empty-zero

The number of columns containing values that are only binary zeros. These occupy one bit.
empty-fill

The number of integer columns that do not occupy the full byte range of their type. These are changed to a smaller type. For example, a BIGINT column (eight bytes) can be stored as a TINYINT column (one byte) if all its values are in the range from -128 to 127.
pre-space

The number of decimal columns that are stored with leading spaces. In this case, each value contains a count for the number of leading spaces.
end-space

The number of columns that have a lot of trailing spaces. In this case, each value contains a count for the number of trailing spaces.
table-lookup

The column had only a small number of different values, which were converted to an ENUM before Huffman compression.
zero

The number of columns for which all values are zero.
Original trees

The initial number of Huffman trees.
After join

The number of distinct Huffman trees left after joining trees to save some header space.

After a table has been compressed, the Field lines displayed by myisamchk -dvv include additional information about each column:

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.

After you run myisampack, you must run myisamchk to re-create any indexes. At this time, you can also sort the index blocks and create statistics needed for the MySQL optimizer to work more efficiently:

shell> myisamchk -rq --sort-index --analyze tbl_name.MYI

After you have installed the packed table into the MySQL database directory, you should execute mysqladmin flush-tables to force mysqld to start using the new table.

To unpack a packed table, use the --unpack option to myisamchk.