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