myisampack - Man Page

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%–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​. 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.3.1, “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​.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:

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

See Also

For more information, please refer to the MariaDB Documentation, available online at https://mariadb.com/docs/

Author

MariaDB Foundation (http://www.mariadb.org/).

Info

15 May 2020 MariaDB 10.11 MariaDB Database System