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