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