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:

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".

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:

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

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.




Spec-Zone.ru - all specs in one place