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