myisampack



MYISAMPACK(1)                MySQL Database System               MYISAMPACK(1)




NAME

       myisampack - generate compressed, read-only MyISAM tables pack_isam -
       generate compressed, read-only ISAM tables


SYNOPSIS

       myisampack [options] filename ...

       pack_isam [options] filename ...


DESCRIPTION

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

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

       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.

       A similar utility, pack_isam, compresses ISAM tables. Because ISAM
       tables are deprecated, this section discusses only myisampack, but the
       general procedures for using myisampack are also true for pack_isam
       unless otherwise specified.

       Please note the following:

       ·  If the mysqld server was invoked with the --skip-external-locking
          option, it is not a good idea to invoke myisampack if the table
          might be updated by the server during the packing process.

       ·  After packing a table, it becomes read-only. This is generally
          intended (such as when accessing packed tables on a CD). Allowing
          writes to a packed table is on our TODO list, but with low priority.

       ·  myisampack can pack BLOB or TEXT columns. The older pack_isam
          program for ISAM tables cannot.

       Invoke myisampack like this:

       shell> myisampack [options] filename ...

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

       myisampack supports the following options:

       ·  --help, -?

          Display a help message and exit.

       ·  --backup, -b

          Make a backup of the table data file using the name tbl_name.OLD.

       ·  --debug[=debug_options], -# [debug_options]

          Write a debugging log. The debug_options string often is
          ´d:t:o,file_name’.

       ·  --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 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).

       ·  --packlength=len, -p len

          Specify the record length storage size, in bytes. The value should
          be 1, 2, or 3.  myisampack stores all rows with length pointers of
          1, 2, or 3 bytes. In most normal cases, myisampack can determine the
          right length value before it begins packing the file, but it may
          notice during the packing process that it could have used a shorter
          length. In this case, myisampack prints a note that the next time
          you pack the same file, you could use a shorter record length.

       ·  --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 myisamchk 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 the --skip-external-locking option, 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.frm
       shell> myisamchk -dvv station
       MyISAM file:     station
       Isam-version:  2
       Creation time: 1996-03-13 10:08:58
       Recover time:  1997-02-02  3:06:43
       Data records:              1192  Deleted blocks:              0
       Datafile parts:            1192  Deleted data:                0
       Datafile pointer (bytes):     2  Keyfile pointer (bytes):     2
       Max datafile length:   54657023  Max keyfile length:   33554431
       Recordlength:               834
       Record format: Fixed length
       table description:
       Key Start Len Index   Type                 Root  Blocksize    Rec/key
       1   2     4   unique  unsigned long        1024       1024          1
       2   32    30  multip. text                10240       1024          1
       Field Start Length Type
       1     1     1
       2     2     4
       3     6     4
       4     10    1
       5     11    20
       6     31    1
       7     32    30
       8     62    35
       9     97    35
       10    132   35
       11    167   4
       12    171   16
       13    187   35
       14    222   4
       15    226   16
       16    242   20
       17    262   20
       18    282   20
       19    302   30
       20    332   4
       21    336   4
       22    340   1
       23    341   8
       24    349   8
       25    357   8
       26    365   2
       27    367   2
       28    369   4
       29    373   4
       30    377   1
       31    378   2
       32    380   8
       33    388   4
       34    392   4
       35    396   4
       36    400   4
       37    404   1
       38    405   4
       39    409   4
       40    413   4
       41    417   4
       42    421   4
       43    425   4
       44    429   20
       45    449   30
       46    479   1
       47    480   1
       48    481   79
       49    560   79
       50    639   79
       51    718   79
       52    797   8
       53    805   1
       54    806   1
       55    807   20
       56    827   4
       57    831   4
       shell> myisampack station.MYI
       Compressing station.MYI: (1192 records)
       - Calculating statistics
       normal:     20  empty-space:   16  empty-zero:     12  empty-fill:  11
       pre-space:   0  end-space:     12  table-lookups:   5  zero:         7
       Original trees:  57  After join: 17
       - Compressing file
       87.14%
       Remember to run myisamchk -rq on compressed tables
       shell> 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.frm
       shell> myisamchk -dvv station
       MyISAM file:     station
       Isam-version:  2
       Creation time: 1996-03-13 10:08:58
       Recover time:  1997-04-17 19:04:26
       Data records:               1192  Deleted blocks:              0
       Datafile parts:             1192  Deleted data:                0
       Datafile pointer (bytes):      3  Keyfile pointer (bytes):     1
       Max datafile length:    16777215  Max keyfile length:     131071
       Recordlength:                834
       Record format: Compressed
       table description:
       Key Start Len Index   Type                 Root  Blocksize    Rec/key
       1   2     4   unique  unsigned long       10240       1024          1
       2   32    30  multip. text                54272       1024          1
       Field Start Length Type                         Huff tree  Bits
       1     1     1      constant                             1     0
       2     2     4      zerofill(1)                          2     9
       3     6     4      no zeros, zerofill(1)                2     9
       4     10    1                                           3     9
       5     11    20     table-lookup                         4     0
       6     31    1                                           3     9
       7     32    30     no endspace, not_always              5     9
       8     62    35     no endspace, not_always, no empty    6     9
       9     97    35     no empty                             7     9
       10    132   35     no endspace, not_always, no empty    6     9
       11    167   4      zerofill(1)                          2     9
       12    171   16     no endspace, not_always, no empty    5     9
       13    187   35     no endspace, not_always, no empty    6     9
       14    222   4      zerofill(1)                          2     9
       15    226   16     no endspace, not_always, no empty    5     9
       16    242   20     no endspace, not_always              8     9
       17    262   20     no endspace, no empty                8     9
       18    282   20     no endspace, no empty                5     9
       19    302   30     no endspace, no empty                6     9
       20    332   4      always zero                          2     9
       21    336   4      always zero                          2     9
       22    340   1                                           3     9
       23    341   8      table-lookup                         9     0
       24    349   8      table-lookup                        10     0
       25    357   8      always zero                          2     9
       26    365   2                                           2     9
       27    367   2      no zeros, zerofill(1)                2     9
       28    369   4      no zeros, zerofill(1)                2     9
       29    373   4      table-lookup                        11     0
       30    377   1                                           3     9
       31    378   2      no zeros, zerofill(1)                2     9
       32    380   8      no zeros                             2     9
       33    388   4      always zero                          2     9
       34    392   4      table-lookup                        12     0
       35    396   4      no zeros, zerofill(1)               13     9
       36    400   4      no zeros, zerofill(1)                2     9
       37    404   1                                           2     9
       38    405   4      no zeros                             2     9
       39    409   4      always zero                          2     9
       40    413   4      no zeros                             2     9
       41    417   4      always zero                          2     9
       42    421   4      no zeros                             2     9
       43    425   4      always zero                          2     9
       44    429   20     no empty                             3     9
       45    449   30     no empty                             3     9
       46    479   1                                          14     4
       47    480   1                                          14     4
       48    481   79     no endspace, no empty               15     9
       49    560   79     no empty                             2     9
       50    639   79     no empty                             2     9
       51    718   79     no endspace                         16     9
       52    797   8      no empty                             2     9
       53    805   1                                          17     1
       54    806   1                                           3     9
       55    807   20     no empty                             3     9
       56    827   4      no zeros, zerofill(2)                2     9
       57    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, myisamchk -dvv prints additional
       information about each column:

       ·  Type

          The column 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

       A similar procedure applies for ISAM tables. After using pack_isam, use
       isamchk to re-create the indexes:

       shell> isamchk -rq --sort-index --analyze tbl_name.ISM

       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 or
       isamchk.


SEE ALSO

       isamchk(1), isamlog(1), msql2mysql(1), myisamchk(1), myisamlog(1),
       mysql(1), mysql.server(1), mysql_config(1),
       mysql_fix_privilege_tables(1), mysql_zap(1), mysqlaccess(1),
       mysqladmin(1), mysqlbinlog(1), mysqlcheck(1), mysqld(1),
       mysqld_multi(1), mysqld_safe(1), mysqldump(1), mysqlhotcopy(1),
       mysqlimport(1), mysqlshow(1), pack_isam(1), perror(1), replace(1),
       safe_mysqld(1)

       For more information, please refer to the MySQL Reference Manual, which
       may already be installed locally and which is also available online at
       http://dev.mysql.com/doc/.


AUTHOR

       MySQL AB (http://www.mysql.com/).  This software comes with no
       warranty.



MySQL 4.1                         11/30/2005                     MYISAMPACK(1)

Man(1) output converted with man2html