man pages section 1: User Commands

Exit Print View

Updated: July 2014
 
 

myisampack (1)

Name

myisampack - only MyISAM tables

Synopsis

myisampack [options] file_name ...

Description




MySQL Database System                               MYISAMPACK(1)



NAME
     myisampack - generate compressed, read-only MyISAM tables

SYNOPSIS
     myisampack [options] file_name ...

DESCRIPTION
     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:

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

     o   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.  myisamchk(1).

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

     o   --help, -?

         Display a help message and exit.



MySQL 5.5            Last change: 03/22/2013                    1






MySQL Database System                               MYISAMPACK(1)



     o   --backup, -b

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

     o   --character-sets-dir=path

         The directory where character sets are installed. See
         Section 10.5, "Character Set Configuration".

     o   --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'.

     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.

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

     o   --silent, -s

         Silent mode. Write output only when errors occur.

     o   --test, -t

         Do not actually pack the table, just test packing it.

     o   --tmpdir=path, -T path



MySQL 5.5            Last change: 03/22/2013                    2






MySQL Database System                               MYISAMPACK(1)



         Use the named directory as the location where myisampack
         creates temporary files.

     o   --verbose, -v

         Verbose mode. Write information about the progress of
         the packing operation and its result.

     o   --version, -V

         Display version information and exit.

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



MySQL 5.5            Last change: 03/22/2013                    3






MySQL Database System                               MYISAMPACK(1)



         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



MySQL 5.5            Last change: 03/22/2013                    4






MySQL Database System                               MYISAMPACK(1)



         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



MySQL 5.5            Last change: 03/22/2013                    5






MySQL Database System                               MYISAMPACK(1)



         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:

     o   normal

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

     o   empty-space

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

     o   empty-zero

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

     o   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)



MySQL 5.5            Last change: 03/22/2013                    6






MySQL Database System                               MYISAMPACK(1)



         can be stored as a TINYINT column (one byte) if all its
         values are in the range from -128 to 127.

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

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

     o   table-lookup

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

     o   zero

         The number of columns for which all values are zero.

     o   Original trees

         The initial number of Huffman trees.

     o   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:

     o   Type

         The data type. The value may contain any of the
         following descriptors:

         o   constant

             All rows have the same value.

         o   no endspace

             Do not store endspace.

         o   no endspace, not_always



MySQL 5.5            Last change: 03/22/2013                    7






MySQL Database System                               MYISAMPACK(1)



             Do not store endspace and do not do endspace
             compression for all values.

         o   no endspace, no empty

             Do not store endspace. Do not store empty values.

         o   table-lookup

             The column was converted to an ENUM.

         o   zerofill(N)

             The most significant N bytes in the value are always
             0 and are not stored.

         o   no zeros

             Do not store zeros.

         o   always zero

             Zero values are stored using one bit.

     o   Huff tree

         The number of the Huffman tree associated with the
         column.

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

COPYRIGHT
     Copyright (C) 1997, 2013, Oracle and/or its affiliates. All
     rights reserved.





MySQL 5.5            Last change: 03/22/2013                    8






MySQL Database System                               MYISAMPACK(1)



     This software and related documentation are provided under a
     license agreement containing restrictions on use and
     disclosure and are protected by intellectual property laws.
     Except as expressly permitted in your license agreement or
     allowed by law, you may not use, copy, reproduce, translate,
     broadcast, modify, license, transmit, distribute, exhibit,
     perform, publish, or display any part, in any form, or by
     any means. Reverse engineering, disassembly, or
     decompilation of this software, unless required by law for
     interoperability, is prohibited.

     The information contained herein is subject to change
     without notice and is not warranted to be error-free. If you
     find any errors, please report them to us in writing.

     If this software or related documentation is delivered to
     the U.S. Government or anyone licensing it on behalf of the
     U.S. Government, the following notice is applicable:

     U.S. GOVERNMENT RIGHTS Programs, software, databases, and
     related documentation and technical data delivered to U.S.
     Government customers are "commercial computer software" or
     "commercial technical data" pursuant to the applicable
     Federal Acquisition Regulation and agency-specific
     supplemental regulations. As such, the use, duplication,
     disclosure, modification, and adaptation shall be subject to
     the restrictions and license terms set forth in the
     applicable Government contract, and, to the extent
     applicable by the terms of the Government contract, the
     additional rights set forth in FAR 52.227-19, Commercial
     Computer Software License (December 2007). Oracle USA, Inc.,
     500 Oracle Parkway, Redwood City, CA 94065.

     This software is developed for general use in a variety of
     information management applications. It is not developed or
     intended for use in any inherently dangerous applications,
     including applications which may create a risk of personal
     injury. If you use this software in dangerous applications,
     then you shall be responsible to take all appropriate fail-
     safe, backup, redundancy, and other measures to ensure the
     safe use of this software. Oracle Corporation and its
     affiliates disclaim any liability for any damages caused by
     use of this software in dangerous applications.

     Oracle is a registered trademark of Oracle Corporation
     and/or its affiliates. MySQL is a trademark of Oracle
     Corporation and/or its affiliates, and shall not be used
     without Oracle's express written authorization. Other names
     may be trademarks of their respective owners.

     This software and documentation may provide access to or
     information on content, products, and services from third



MySQL 5.5            Last change: 03/22/2013                    9






MySQL Database System                               MYISAMPACK(1)



     parties. Oracle Corporation and its affiliates are not
     responsible for and expressly disclaim all warranties of any
     kind with respect to third-party content, products, and
     services. Oracle Corporation and its affiliates will not be
     responsible for any loss, costs, or damages incurred due to
     your access to or use of third-party content, products, or
     services.

     This document in any form, software or printed matter,
     contains proprietary information that is the exclusive
     property of Oracle. Your access to and use of this material
     is subject to the terms and conditions of your Oracle
     Software License and Service Agreement, which has been
     executed and with which you agree to comply. This document
     and information contained herein may not be disclosed,
     copied, reproduced, or distributed to anyone outside Oracle
     without prior written consent of Oracle or as specifically
     provided below. This document is not part of your license
     agreement nor can it be incorporated into any contractual
     agreement with Oracle or its subsidiaries or affiliates.

     This documentation is NOT distributed under a GPL license.
     Use of this documentation is subject to the following terms:

     You may create a printed copy of this documentation solely
     for your own personal use. Conversion to other formats is
     allowed as long as the actual content is not altered or
     edited in any way. You shall not publish or distribute this
     documentation in any form or on any media, except if you
     distribute the documentation in a manner similar to how
     Oracle disseminates it (that is, electronically for download
     on a Web site with the software) or on a CD-ROM or similar
     medium, provided however that the documentation is
     disseminated together with the software on the same medium.
     Any other use, such as any dissemination of printed copies
     or use of this documentation, in whole or in part, in
     another publication, requires the prior written consent from
     an authorized representative of Oracle. Oracle and/or its
     affiliates reserve any and all rights to this documentation
     not expressly granted above.

     For more information on the terms of this license, or for
     details on how the MySQL documentation is built and
     produced, please visit blue]MySQL Contact & Questions].

     For additional licensing information, including licenses for
     third-party libraries used by MySQL products, see Preface
     and Legal Notices.

     For help with using MySQL, please visit either the
     blue]MySQL Forums] or blue]MySQL Mailing Lists] where you
     can discuss your issues with other MySQL users.



MySQL 5.5            Last change: 03/22/2013                   10






MySQL Database System                               MYISAMPACK(1)



     For additional documentation on MySQL products, including
     translations of the documentation into other languages, and
     downloadable versions in variety of formats, including HTML
     and PDF formats, see the blue]MySQL Documentation Library].



ATTRIBUTES
     See attributes(5) for descriptions of the following
     attributes:

     +---------------+-------------------+
     |ATTRIBUTE TYPE | ATTRIBUTE VALUE   |
     +---------------+-------------------+
     |Availability   | database/mysql-55 |
     +---------------+-------------------+
     |Stability      | Uncommitted       |
     +---------------+-------------------+
SEE ALSO
     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
     Oracle Corporation (http://dev.mysql.com/).



NOTES
     This software was built from source available at
     https://java.net/projects/solaris-userland.  The original
     community source was downloaded from
     http://downloads.mysql.com/archives/mysql-5.5/mysql-5.5.31.tar.gz

     Further information about this software can be found on the
     open source community website at http://dev.mysql.com/.



















MySQL 5.5            Last change: 03/22/2013                   11