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