2
6 myisampack - generate compressed, read-only MyISAM tables
7
9 myisampack [options] file_name ...
10
12 The myisampack utility compresses MyISAM tables. myisampack works by
13 compressing each column in the table separately. Usually, myisampack
14 packs the data file 40%-70%.
15 When the table is used later, the server reads into memory the
17 information needed to decompress columns. This results in much better
18 performance when accessing individual rows, because you only have to
19 uncompress exactly one row.
20 MySQL uses mmap() when possible to perform memory mapping on compressed
22 tables. If mmap() does not work, MySQL falls back to normal read/write
23 file operations.
24 A similar utility, pack_isam, compresses ISAM tables. Because ISAM
26 tables are deprecated, this section discusses only myisampack, but the
27 general procedures for using myisampack are also true for pack_isam
28 unless otherwise specified. References to myisamchk should be read as
29 references to isamchk if you are using pack_isam.
30 Please note the following:
32 · If the mysqld server was invoked with external locking disabled, it
34 is not a good idea to invoke myisampack if the table might be
35 updated by the server during the packing process. It is safest to
36 compress tables with the server stopped.
37 · After packing a table, it becomes read-only. This is generally
39 intended (such as when accessing packed tables on a CD). Allowing
40 writes to a packed table is on our TODO list, but with low priority.
41 · myisampack can pack BLOB or TEXT columns. (The older pack_isam
43 program for ISAM tables did not have this capability.)
44Invoke myisampack like this:
47 shell> myisampack [options] file_name ...
49Each filename argument should be the name of an index (.MYI) file. If you are
51not in the database directory, you should specify the pathname to the file. It
52is permissible to omit the .MYI extension.
53After you compress a table with myisampack, you should use myisamchk -rq to
55rebuild its indexes. myisamchk(1).
56
58· --help, -?
60 Display a help message and exit.
62· --backup, -b
64 Make a backup of each table's data file using the name tbl_name.OLD.
66· --character-sets-dir=path
68 The directory where character sets are installed. See Section 8.1, “The
70 Character Set Used for Data and Sorting”.
71· --debug[=debug_options], -# [debug_options]
73 Write a debugging log. The debug_options string often is ´d:t:o,file_name'.
75· --force, -f
77 Produce a packed table even if it becomes larger than the original or if
79 the intermediate file from an earlier invocation of myisampack exists.
80 (myisampack creates an intermediate file named tbl_name.TMD in the database
81 directory while it compresses the table. If you kill myisampack, the .TMD
82 file might not be deleted.) Normally, myisampack exits with an error if it
83 finds that tbl_name.TMD exists. With --force, myisampack packs the table
84 anyway.
85· --join=big_tbl_name, -j big_tbl_name
87 Join all tables named on the command line into a single table big_tbl_name.
89 All tables that are to be combined must have identical structure (same
90 column names and types, same indexes, and so forth).
91· --silent, -s
93 Silent mode. Write output only when errors occur.
95· --test, -t
97 Do not actually pack the table, just test packing it.
99· --tmpdir=path, -T path
101 Use the named directory as the location where myisampack creates temporary
103 files.
104· --verbose, -v
106 Verbose mode. Write information about the progress of the packing operation
108 and its result.
109· --version, -V
111 Display version information and exit.
113· --wait, -w
115 Wait and retry if the table is in use. If the mysqld server was invoked
117 with external locking disabled, it is not a good idea to invoke myisampack
118 if the table might be updated by the server during the packing process.
119The following sequence of commands illustrates a typical table compression
122session:
123 shell> ls -l station.*
125 -rw-rw-r-- 1 monty my 994128 Apr 17 19:00 station.MYD
126 -rw-rw-r-- 1 monty my 53248 Apr 17 19:00 station.MYI
127 -rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm
128 shell> myisamchk -dvv station
129 MyISAM file: station
130 Isam-version: 2
131 Creation time: 1996-03-13 10:08:58
132 Recover time: 1997-02-02 3:06:43
133 Data records: 1192 Deleted blocks: 0
134 Datafile parts: 1192 Deleted data: 0
135 Datafile pointer (bytes): 2 Keyfile pointer (bytes): 2
136 Max datafile length: 54657023 Max keyfile length: 33554431
137 Recordlength: 834
138 Record format: Fixed length
139 table description:
140 Key Start Len Index Type Root Blocksize Rec/key
141 1 2 4 unique unsigned long 1024 1024 1
142 2 32 30 multip. text 10240 1024 1
143 Field Start Length Type
144 1 1 1
145 2 2 4
146 3 6 4
147 4 10 1
148 5 11 20
149 6 31 1
150 7 32 30
151 8 62 35
152 9 97 35
153 10 132 35
154 11 167 4
155 12 171 16
156 13 187 35
157 14 222 4
158 15 226 16
159 16 242 20
160 17 262 20
161 18 282 20
162 19 302 30
163 20 332 4
164 21 336 4
165 22 340 1
166 23 341 8
167 24 349 8
168 25 357 8
169 26 365 2
170 27 367 2
171 28 369 4
172 29 373 4
173 30 377 1
174 31 378 2
175 32 380 8
176 33 388 4
177 34 392 4
178 35 396 4
179 36 400 4
180 37 404 1
181 38 405 4
182 39 409 4
183 40 413 4
184 41 417 4
185 42 421 4
186 43 425 4
187 44 429 20
188 45 449 30
189 46 479 1
190 47 480 1
191 48 481 79
192 49 560 79
193 50 639 79
194 51 718 79
195 52 797 8
196 53 805 1
197 54 806 1
198 55 807 20
199 56 827 4
200 57 831 4
201 shell> myisampack station.MYI
202 Compressing station.MYI: (1192 records)
203 - Calculating statistics
204 normal: 20 empty-space: 16 empty-zero: 12 empty-fill: 11
205 pre-space: 0 end-space: 12 table-lookups: 5 zero: 7
206 Original trees: 57 After join: 17
207 - Compressing file
208 87.14%
209 Remember to run myisamchk -rq on compressed tables
210 shell> ls -l station.*
211 -rw-rw-r-- 1 monty my 127874 Apr 17 19:00 station.MYD
212 -rw-rw-r-- 1 monty my 55296 Apr 17 19:04 station.MYI
213 -rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm
214 shell> myisamchk -dvv station
215 MyISAM file: station
216 Isam-version: 2
217 Creation time: 1996-03-13 10:08:58
218 Recover time: 1997-04-17 19:04:26
219 Data records: 1192 Deleted blocks: 0
220 Datafile parts: 1192 Deleted data: 0
221 Datafile pointer (bytes): 3 Keyfile pointer (bytes): 1
222 Max datafile length: 16777215 Max keyfile length: 131071
223 Recordlength: 834
224 Record format: Compressed
225 table description:
226 Key Start Len Index Type Root Blocksize Rec/key
227 1 2 4 unique unsigned long 10240 1024 1
228 2 32 30 multip. text 54272 1024 1
229 Field Start Length Type Huff tree Bits
230 1 1 1 constant 1 0
231 2 2 4 zerofill(1) 2 9
232 3 6 4 no zeros, zerofill(1) 2 9
233 4 10 1 3 9
234 5 11 20 table-lookup 4 0
235 6 31 1 3 9
236 7 32 30 no endspace, not_always 5 9
237 8 62 35 no endspace, not_always, no empty 6 9
238 9 97 35 no empty 7 9
239 10 132 35 no endspace, not_always, no empty 6 9
240 11 167 4 zerofill(1) 2 9
241 12 171 16 no endspace, not_always, no empty 5 9
242 13 187 35 no endspace, not_always, no empty 6 9
243 14 222 4 zerofill(1) 2 9
244 15 226 16 no endspace, not_always, no empty 5 9
245 16 242 20 no endspace, not_always 8 9
246 17 262 20 no endspace, no empty 8 9
247 18 282 20 no endspace, no empty 5 9
248 19 302 30 no endspace, no empty 6 9
249 20 332 4 always zero 2 9
250 21 336 4 always zero 2 9
251 22 340 1 3 9
252 23 341 8 table-lookup 9 0
253 24 349 8 table-lookup 10 0
254 25 357 8 always zero 2 9
255 26 365 2 2 9
256 27 367 2 no zeros, zerofill(1) 2 9
257 28 369 4 no zeros, zerofill(1) 2 9
258 29 373 4 table-lookup 11 0
259 30 377 1 3 9
260 31 378 2 no zeros, zerofill(1) 2 9
261 32 380 8 no zeros 2 9
262 33 388 4 always zero 2 9
263 34 392 4 table-lookup 12 0
264 35 396 4 no zeros, zerofill(1) 13 9
265 36 400 4 no zeros, zerofill(1) 2 9
266 37 404 1 2 9
267 38 405 4 no zeros 2 9
268 39 409 4 always zero 2 9
269 40 413 4 no zeros 2 9
270 41 417 4 always zero 2 9
271 42 421 4 no zeros 2 9
272 43 425 4 always zero 2 9
273 44 429 20 no empty 3 9
274 45 449 30 no empty 3 9
275 46 479 1 14 4
276 47 480 1 14 4
277 48 481 79 no endspace, no empty 15 9
278 49 560 79 no empty 2 9
279 50 639 79 no empty 2 9
280 51 718 79 no endspace 16 9
281 52 797 8 no empty 2 9
282 53 805 1 17 1
283 54 806 1 3 9
284 55 807 20 no empty 3 9
285 56 827 4 no zeros, zerofill(2) 2 9
286 57 831 4 no zeros, zerofill(1) 2 9
287
289· normal
291 The number of columns for which no extra packing is used.
293· empty-space
295 The number of columns containing values that are only spaces. These occupy
297 one bit.
298· empty-zero
300 The number of columns containing values that are only binary zeros. These
302 occupy one bit.
303· empty-fill
305 The number of integer columns that do not occupy the full byte range of
307 their type. These are changed to a smaller type. For example, a BIGINT
308 column (eight bytes) can be stored as a TINYINT column (one byte) if all
309 its values are in the range from -128 to 127.
310· pre-space
312 The number of decimal columns that are stored with leading spaces. In this
314 case, each value contains a count for the number of leading spaces.
315· end-space
317 The number of columns that have a lot of trailing spaces. In this case,
319 each value contains a count for the number of trailing spaces.
320· table-lookup
322 The column had only a small number of different values, which were
324 converted to an ENUM before Huffman compression.
325· zero
327 The number of columns for which all values are zero.
329· Original trees
331 The initial number of Huffman trees.
333· After join
335 The number of distinct Huffman trees left after joining trees to save some
337 header space.
338After a table has been compressed, myisamchk -dvv prints additional
341information about each column:
342· Type
344 The data type. The value may contain any of the following descriptors:
346 · constant
348 All rows have the same value.
350 · no endspace
352 Do not store endspace.
354 · no endspace, not_always
356 Do not store endspace and do not do endspace compression for all values.
358 · no endspace, no empty
360 Do not store endspace. Do not store empty values.
362 · table-lookup
364 The column was converted to an ENUM.
366 · zerofill(N)
368 The most significant N bytes in the value are always 0 and are not
370 stored.
371 · no zeros
373 Do not store zeros.
375 · always zero
377 Zero values are stored using one bit.
379· Huff tree
381 The number of the Huffman tree associated with the column.
383· Bits
385 The number of bits used in the Huffman tree.
387After you run myisampack, you must run myisamchk to re-create any indexes. At
390this time, you can also sort the index blocks and create statistics needed for
391the MySQL optimizer to work more efficiently:
392 shell> myisamchk -rq --sort-index --analyze tbl_name.MYI
394A similar procedure applies for ISAM tables. After using pack_isam, use
397 shell> isamchk -rq --sort-index --analyze tbl_name.ISM
399After you have installed the packed table into the MySQL database directory,
401you should execute mysqladmin flush-tables to force mysqld to start using the
402new table.
403To unpack a packed table, use the --unpack option to myisamchk or isamchk.
405
407 Copyright 1997-2007 MySQL AB
408 This documentation is NOT distributed under a GPL license. Use of this
410 documentation is subject to the following terms: You may create a
411 printed copy of this documentation solely for your own personal use.
412 Conversion to other formats is allowed as long as the actual content is
413 not altered or edited in any way. You shall not publish or distribute
414 this documentation in any form or on any media, except if you
415 distribute the documentation in a manner similar to how MySQL
416 disseminates it (that is, electronically for download on a Web site
417 with the software) or on a CD-ROM or similar medium, provided however
418 that the documentation is disseminated together with the software on
419 the same medium. Any other use, such as any dissemination of printed
420 copies or use of this documentation, in whole or in part, in another
421 publication, requires the prior written consent from an authorized
422 representative of MySQL AB. MySQL AB reserves any and all rights to
423 this documentation not expressly granted above.
424 Please email <docs@mysql.com> for more information.
426
428 For more information, please refer to the MySQL Reference Manual, which
429 may already be installed locally and which is also available online at
430 http://dev.mysql.com/doc/.
431
433 MySQL AB (http://www.mysql.com/). This software comes with no
434 warranty.
435MySQL 5.0 07/04/2007 MYISAMPACK(1)