1MYISAMPACK(1) MySQL Database System MYISAMPACK(1)
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% to 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 Please note the following:
26 · If the mysqld server was invoked with external locking disabled, it
28 is not a good idea to invoke myisampack if the table might be
29 updated by the server during the packing process. It is safest to
30 compress tables with the server stopped.
31 · After packing a table, it becomes read only. This is generally
33 intended (such as when accessing packed tables on a CD).
34 Invoke myisampack like this:
36 shell> myisampack [options] file_name ...
38 Each file name argument should be the name of an index (.MYI) file. If
40 you are not in the database directory, you should specify the path name
41 to the file. It is permissible to omit the .MYI extension.
42 After you compress a table with myisampack, you should use myisamchk
44 -rq to rebuild its indexes. myisamchk(1).
45 myisampack supports the following options. It also reads option files
47 and supports the options for processing them described at
48 Section 4.2.3.3.1, “Command-Line Options that Affect Option-File
49 Handling”.
50 · --help, -?
52 Display a help message and exit.
54 · --backup, -b
56 Make a backup of each table´s data file using the name
58 tbl_name.OLD.
59 · --character-sets-dir=path
61 The directory where character sets are installed. See Section 9.5,
63 “Character Set Configuration”.
64 · --debug[=debug_options], -# [debug_options]
66 Write a debugging log. A typical debug_options string is
68 ´d:t:o,file_name´. The default is ´d:t:o´.
69 · --force, -f
71 Produce a packed table even if it becomes larger than the original
73 or if the intermediate file from an earlier invocation of
74 myisampack exists. (myisampack creates an intermediate file named
75 tbl_name.TMD in the database directory while it compresses the
76 table. If you kill myisampack, the .TMD file might not be deleted.)
77 Normally, myisampack exits with an error if it finds that
78 tbl_name.TMD exists. With --force, myisampack packs the table
79 anyway.
80 · --join=big_tbl_name, -j big_tbl_name
82 Join all tables named on the command line into a single packed
84 table big_tbl_name. All tables that are to be combined must have
85 identical structure (same column names and types, same indexes, and
86 so forth).
87 big_tbl_name must not exist prior to the join operation. All source
89 tables named on the command line to be merged into big_tbl_name
90 must exist. The source tables are read for the join operation but
91 not modified. The join operation does not create a .frm file for
92 big_tbl_name, so after the join operation finishes, copy the .frm
93 file from one of the source tables and name it big_tbl_name.frm.
94 · --silent, -s
96 Silent mode. Write output only when errors occur.
98 · --test, -t
100 Do not actually pack the table, just test packing it.
102 · --tmpdir=path, -T path
104 Use the named directory as the location where myisampack creates
106 temporary files.
107 · --verbose, -v
109 Verbose mode. Write information about the progress of the packing
111 operation and its result.
112 · --version, -V
114 Display version information and exit.
116 · --wait, -w
118 Wait and retry if the table is in use. If the mysqld server was
120 invoked with external locking disabled, it is not a good idea to
121 invoke myisampack if the table might be updated by the server
122 during the packing process.
123 The following sequence of commands illustrates a typical table
125 compression session:
126 shell> ls -l station.*
128 -rw-rw-r-- 1 monty my 994128 Apr 17 19:00 station.MYD
129 -rw-rw-r-- 1 monty my 53248 Apr 17 19:00 station.MYI
130 -rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm
131 shell> myisamchk -dvv station
132 MyISAM file: station
133 Isam-version: 2
134 Creation time: 1996-03-13 10:08:58
135 Recover time: 1997-02-02 3:06:43
136 Data records: 1192 Deleted blocks: 0
137 Datafile parts: 1192 Deleted data: 0
138 Datafile pointer (bytes): 2 Keyfile pointer (bytes): 2
139 Max datafile length: 54657023 Max keyfile length: 33554431
140 Recordlength: 834
141 Record format: Fixed length
142 table description:
143 Key Start Len Index Type Root Blocksize Rec/key
144 1 2 4 unique unsigned long 1024 1024 1
145 2 32 30 multip. text 10240 1024 1
146 Field Start Length Type
147 1 1 1
148 2 2 4
149 3 6 4
150 4 10 1
151 5 11 20
152 6 31 1
153 7 32 30
154 8 62 35
155 9 97 35
156 10 132 35
157 11 167 4
158 12 171 16
159 13 187 35
160 14 222 4
161 15 226 16
162 16 242 20
163 17 262 20
164 18 282 20
165 19 302 30
166 20 332 4
167 21 336 4
168 22 340 1
169 23 341 8
170 24 349 8
171 25 357 8
172 26 365 2
173 27 367 2
174 28 369 4
175 29 373 4
176 30 377 1
177 31 378 2
178 32 380 8
179 33 388 4
180 34 392 4
181 35 396 4
182 36 400 4
183 37 404 1
184 38 405 4
185 39 409 4
186 40 413 4
187 41 417 4
188 42 421 4
189 43 425 4
190 44 429 20
191 45 449 30
192 46 479 1
193 47 480 1
194 48 481 79
195 49 560 79
196 50 639 79
197 51 718 79
198 52 797 8
199 53 805 1
200 54 806 1
201 55 807 20
202 56 827 4
203 57 831 4
204 shell> myisampack station.MYI
205 Compressing station.MYI: (1192 records)
206 - Calculating statistics
207 normal: 20 empty-space: 16 empty-zero: 12 empty-fill: 11
208 pre-space: 0 end-space: 12 table-lookups: 5 zero: 7
209 Original trees: 57 After join: 17
210 - Compressing file
211 87.14%
212 Remember to run myisamchk -rq on compressed tables
213 shell> ls -l station.*
214 -rw-rw-r-- 1 monty my 127874 Apr 17 19:00 station.MYD
215 -rw-rw-r-- 1 monty my 55296 Apr 17 19:04 station.MYI
216 -rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm
217 shell> myisamchk -dvv station
218 MyISAM file: station
219 Isam-version: 2
220 Creation time: 1996-03-13 10:08:58
221 Recover time: 1997-04-17 19:04:26
222 Data records: 1192 Deleted blocks: 0
223 Datafile parts: 1192 Deleted data: 0
224 Datafile pointer (bytes): 3 Keyfile pointer (bytes): 1
225 Max datafile length: 16777215 Max keyfile length: 131071
226 Recordlength: 834
227 Record format: Compressed
228 table description:
229 Key Start Len Index Type Root Blocksize Rec/key
230 1 2 4 unique unsigned long 10240 1024 1
231 2 32 30 multip. text 54272 1024 1
232 Field Start Length Type Huff tree Bits
233 1 1 1 constant 1 0
234 2 2 4 zerofill(1) 2 9
235 3 6 4 no zeros, zerofill(1) 2 9
236 4 10 1 3 9
237 5 11 20 table-lookup 4 0
238 6 31 1 3 9
239 7 32 30 no endspace, not_always 5 9
240 8 62 35 no endspace, not_always, no empty 6 9
241 9 97 35 no empty 7 9
242 10 132 35 no endspace, not_always, no empty 6 9
243 11 167 4 zerofill(1) 2 9
244 12 171 16 no endspace, not_always, no empty 5 9
245 13 187 35 no endspace, not_always, no empty 6 9
246 14 222 4 zerofill(1) 2 9
247 15 226 16 no endspace, not_always, no empty 5 9
248 16 242 20 no endspace, not_always 8 9
249 17 262 20 no endspace, no empty 8 9
250 18 282 20 no endspace, no empty 5 9
251 19 302 30 no endspace, no empty 6 9
252 20 332 4 always zero 2 9
253 21 336 4 always zero 2 9
254 22 340 1 3 9
255 23 341 8 table-lookup 9 0
256 24 349 8 table-lookup 10 0
257 25 357 8 always zero 2 9
258 26 365 2 2 9
259 27 367 2 no zeros, zerofill(1) 2 9
260 28 369 4 no zeros, zerofill(1) 2 9
261 29 373 4 table-lookup 11 0
262 30 377 1 3 9
263 31 378 2 no zeros, zerofill(1) 2 9
264 32 380 8 no zeros 2 9
265 33 388 4 always zero 2 9
266 34 392 4 table-lookup 12 0
267 35 396 4 no zeros, zerofill(1) 13 9
268 36 400 4 no zeros, zerofill(1) 2 9
269 37 404 1 2 9
270 38 405 4 no zeros 2 9
271 39 409 4 always zero 2 9
272 40 413 4 no zeros 2 9
273 41 417 4 always zero 2 9
274 42 421 4 no zeros 2 9
275 43 425 4 always zero 2 9
276 44 429 20 no empty 3 9
277 45 449 30 no empty 3 9
278 46 479 1 14 4
279 47 480 1 14 4
280 48 481 79 no endspace, no empty 15 9
281 49 560 79 no empty 2 9
282 50 639 79 no empty 2 9
283 51 718 79 no endspace 16 9
284 52 797 8 no empty 2 9
285 53 805 1 17 1
286 54 806 1 3 9
287 55 807 20 no empty 3 9
288 56 827 4 no zeros, zerofill(2) 2 9
289 57 831 4 no zeros, zerofill(1) 2 9
290 myisampack displays the following kinds of information:
292 · normal
294 The number of columns for which no extra packing is used.
296 · empty-space
298 The number of columns containing values that are only spaces. These
300 occupy one bit.
301 · empty-zero
303 The number of columns containing values that are only binary zeros.
305 These occupy one bit.
306 · empty-fill
308 The number of integer columns that do not occupy the full byte
310 range of their type. These are changed to a smaller type. For
311 example, a BIGINT column (eight bytes) can be stored as a TINYINT
312 column (one byte) if all its values are in the range from -128 to
313 127.
314 · pre-space
316 The number of decimal columns that are stored with leading spaces.
318 In this case, each value contains a count for the number of leading
319 spaces.
320 · end-space
322 The number of columns that have a lot of trailing spaces. In this
324 case, each value contains a count for the number of trailing
325 spaces.
326 · table-lookup
328 The column had only a small number of different values, which were
330 converted to an ENUM before Huffman compression.
331 · zero
333 The number of columns for which all values are zero.
335 · Original trees
337 The initial number of Huffman trees.
339 · After join
341 The number of distinct Huffman trees left after joining trees to
343 save some header space.
344 After a table has been compressed, the Field lines displayed by
346 myisamchk -dvv include additional information about each column:
347 · Type
349 The data type. The value may contain any of the following
351 descriptors:
352 · constant
354 All rows have the same value.
356 · no endspace
358 Do not store endspace.
360 · no endspace, not_always
362 Do not store endspace and do not do endspace compression for
364 all values.
365 · no endspace, no empty
367 Do not store endspace. Do not store empty values.
369 · table-lookup
371 The column was converted to an ENUM.
373 · zerofill(N)
375 The most significant N bytes in the value are always 0 and are
377 not stored.
378 · no zeros
380 Do not store zeros.
382 · always zero
384 Zero values are stored using one bit.
386 · Huff tree
388 The number of the Huffman tree associated with the column.
390 · Bits
392 The number of bits used in the Huffman tree.
394 After you run myisampack, you must run myisamchk to re-create any
396 indexes. At this time, you can also sort the index blocks and create
397 statistics needed for the MySQL optimizer to work more efficiently:
398 shell> myisamchk -rq --sort-index --analyze tbl_name.MYI
400 After you have installed the packed table into the MySQL database
402 directory, you should execute mysqladmin flush-tables to force mysqld
403 to start using the new table.
404 To unpack a packed table, use the --unpack option to myisamchk.
406
408 Copyright © 1997, 2011, Oracle and/or its affiliates. All rights
409 reserved.
410 This documentation is free software; you can redistribute it and/or
412 modify it only under the terms of the GNU General Public License as
413 published by the Free Software Foundation; version 2 of the License.
414 This documentation is distributed in the hope that it will be useful,
416 but WITHOUT ANY WARRANTY; without even the implied warranty of
417 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
418 General Public License for more details.
419 You should have received a copy of the GNU General Public License along
421 with the program; if not, write to the Free Software Foundation, Inc.,
422 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
423 http://www.gnu.org/licenses/.
424
427 For more information, please refer to the MySQL Reference Manual, which
428 may already be installed locally and which is also available online at
429 http://dev.mysql.com/doc/.
430
432 Oracle Corporation (http://dev.mysql.com/).
433MySQL 5.1 10/26/2011 MYISAMPACK(1)