~vlad-lesin/percona-server/mysql-5.0.33-original

When the table is used later, the server reads into memory the information needed to decompress columns. This results in much better performance when accessing individual rows, because you only have to uncompress exactly one row.

.PP

MySQL uses

mmap()

when possible to perform memory mapping on compressed tables. If

mmap()

does not work, MySQL falls back to normal read/write file operations.

.PP

Please note the following:

.TP 3n

\(bu

If the

\fBmysqld\fR

server was invoked with external locking disabled, it is not a good idea to invoke

\fBmyisampack\fR

if the table might be updated by the server during the packing process. It is safest to compress tables with the server stopped.

.TP 3n

\(bu

After packing a table, it becomes read\-only. This is generally intended (such as when accessing packed tables on a CD). Allowing writes to a packed table is on our TODO list, but with low priority.

.TP 3n

\(bu

\fBmyisampack\fR

can pack

BLOB

TEXT

columns. (The older

\fBpack_isam\fR

program for

ISAM

tables did not have this capability.)

.sp

.RE

.PP

Invoke

\fBmyisampack\fR

like this:

.sp

.RS 3n

.nf

shell> \fBmyisampack [\fR\fB\fIoptions\fR\fR\fB] \fR\fB\fIfile_name\fR\fR\fB ...\fR

.fi

.RE

.PP

Each filename argument should be the name of an index (\fI.MYI\fR) file. If you are not in the database directory, you should specify the pathname to the file. It is permissible to omit the

\fI.MYI\fR

extension.

.PP

After you compress a table with

\fBmyisampack\fR, you should use

\fBmyisamchk \-rq\fR

to rebuild its indexes.

\fBmyisamchk\fR(1).

.PP

\fBmyisampack\fR

supports the following options:

.TP 3n

\(bu

\fB\-\-help\fR,

\fB\-?\fR

.sp

Display a help message and exit.

.TP 3n

\(bu

\fB\-\-backup\fR,

\fB\-b\fR

.sp

Make a backup of each table's data file using the name

\fI\fItbl_name\fR\fR\fI.OLD\fR.

.TP 3n

100

\(bu

101

\fB\-\-character\-sets\-dir=\fR\fB\fIpath\fR\fR

102

.sp

103

The directory where character sets are installed. See

104

Section\ 9.1, \(lqThe Character Set Used for Data and Sorting\(rq.

105

.TP 3n

106

\(bu

107

\fB\-\-debug[=\fR\fB\fIdebug_options\fR\fR\fB]\fR,

108

\fB\-# [\fR\fB\fIdebug_options\fR\fR\fB]\fR

109

.sp

110

Write a debugging log. The

111

\fIdebug_options\fR

112

string often is

113

\'d:t:o,\fIfile_name\fR'.

114

.TP 3n

115

\(bu

116

\fB\-\-force\fR,

117

\fB\-f\fR

118

.sp

119

Produce a packed table even if it becomes larger than the original or if the intermediate file from an earlier invocation of

120

\fBmyisampack\fR

121

exists. (\fBmyisampack\fR

122

creates an intermediate file named

123

\fI\fItbl_name\fR\fR\fI.TMD\fR

124

in the database directory while it compresses the table. If you kill

125

\fBmyisampack\fR, the

126

\fI.TMD\fR

127

file might not be deleted.) Normally,

128

\fBmyisampack\fR

129

exits with an error if it finds that

130

\fI\fItbl_name\fR\fR\fI.TMD\fR

131

exists. With

132

\fB\-\-force\fR,

133

\fBmyisampack\fR

134

packs the table anyway.

135

.TP 3n

136

\(bu

137

\fB\-\-join=\fR\fB\fIbig_tbl_name\fR\fR,

138

\fB\-j \fR\fB\fIbig_tbl_name\fR\fR

139

.sp

140

Join all tables named on the command line into a single table

141

\fIbig_tbl_name\fR. All tables that are to be combined

142

\fImust\fR

143

have identical structure (same column names and types, same indexes, and so forth).

144

.TP 3n

145

\(bu

146

\fB\-\-packlength=\fR\fB\fIlen\fR\fR,

147

\fB\-p \fR\fB\fIlen\fR\fR

148

.sp

149

Specify the row length storage size, in bytes. The value should be 1, 2, or 3.

150

\fBmyisampack\fR

151

stores all rows with length pointers of 1, 2, or 3 bytes. In most normal cases,

152

\fBmyisampack\fR

153

can determine the correct length value before it begins packing the file, but it may notice during the packing process that it could have used a shorter length. In this case,

154

\fBmyisampack\fR

155

prints a note that you could use a shorter row length the next time you pack the same file.

156

.TP 3n

157

\(bu

158

\fB\-\-silent\fR,

159

\fB\-s\fR

160

.sp

161

Silent mode. Write output only when errors occur.

162

.TP 3n

163

\(bu

164

\fB\-\-test\fR,

165

\fB\-t\fR

166

.sp

167

Do not actually pack the table, just test packing it.

168

.TP 3n

169

\(bu

170

\fB\-\-tmpdir=\fR\fB\fIpath\fR\fR,

171

\fB\-T \fR\fB\fIpath\fR\fR

172

.sp

173

Use the named directory as the location where

174

\fBmyisampack\fR

175

creates temporary files.

176

.TP 3n

177

\(bu

178

\fB\-\-verbose\fR,

179

\fB\-v\fR

180

.sp

181

Verbose mode. Write information about the progress of the packing operation and its result.

182

.TP 3n

183

\(bu

184

\fB\-\-version\fR,

185

\fB\-V\fR

186

.sp

187

Display version information and exit.

188

.TP 3n

189

\(bu

190

\fB\-\-wait\fR,

191

\fB\-w\fR

192

.sp

193

Wait and retry if the table is in use. If the

194

\fBmysqld\fR

195

server was invoked with external locking disabled, it is not a good idea to invoke

196

\fBmyisampack\fR

197

if the table might be updated by the server during the packing process.

198

.sp

199

.RE

200

.PP

201

The following sequence of commands illustrates a typical table compression session:

202

.sp

203

.RS 3n

204

.nf

205

shell> \fBls \-l station.*\fR

206

\-rw\-rw\-r\-\- 1 monty my 994128 Apr 17 19:00 station.MYD

207

\-rw\-rw\-r\-\- 1 monty my 53248 Apr 17 19:00 station.MYI

208

\-rw\-rw\-r\-\- 1 monty my 5767 Apr 17 19:00 station.frm

209

shell> \fBmyisamchk \-dvv station\fR

210

MyISAM file: station

211

Isam\-version: 2

212

Creation time: 1996\-03\-13 10:08:58

213

Recover time: 1997\-02\-02 3:06:43

214

Data records: 1192 Deleted blocks: 0

215

Datafile parts: 1192 Deleted data: 0

216

Datafile pointer (bytes): 2 Keyfile pointer (bytes): 2

217

Max datafile length: 54657023 Max keyfile length: 33554431

218

Recordlength: 834

219

Record format: Fixed length

220

table description:

221

Key Start Len Index Type Root Blocksize Rec/key

222

1 2 4 unique unsigned long 1024 1024 1

223

2 32 30 multip. text 10240 1024 1

224

Field Start Length Type

225

1 1 1

226

2 2 4

227

3 6 4

228

4 10 1

229

5 11 20

230

6 31 1

231

7 32 30

232

8 62 35

233

9 97 35

234

10 132 35

235

11 167 4

236

12 171 16

237

13 187 35

238

14 222 4

239

15 226 16

240

16 242 20

241

17 262 20

242

18 282 20

243

19 302 30

244

20 332 4

245

21 336 4

246

22 340 1

247

23 341 8

248

24 349 8

249

25 357 8

250

26 365 2

251

27 367 2

252

28 369 4

253

29 373 4

254

30 377 1

255

31 378 2

256

32 380 8

257

33 388 4

258

34 392 4

259

35 396 4

260

36 400 4

261

37 404 1

262

38 405 4

263

39 409 4

264

40 413 4

265

41 417 4

266

42 421 4

267

43 425 4

268

44 429 20

269

45 449 30

270

46 479 1

271

47 480 1

272

48 481 79

273

49 560 79

274

50 639 79

275

51 718 79

276

52 797 8

277

53 805 1

278

54 806 1

279

55 807 20

280

56 827 4

281

57 831 4

282

shell> \fBmyisampack station.MYI\fR

283

Compressing station.MYI: (1192 records)

284

\- Calculating statistics

285

normal: 20 empty\-space: 16 empty\-zero: 12 empty\-fill: 11

286

pre\-space: 0 end\-space: 12 table\-lookups: 5 zero: 7

287

Original trees: 57 After join: 17

288

\- Compressing file

289

87.14%

290

Remember to run myisamchk \-rq on compressed tables

291

shell> \fBls \-l station.*\fR

292

\-rw\-rw\-r\-\- 1 monty my 127874 Apr 17 19:00 station.MYD

293

\-rw\-rw\-r\-\- 1 monty my 55296 Apr 17 19:04 station.MYI

294

\-rw\-rw\-r\-\- 1 monty my 5767 Apr 17 19:00 station.frm

295

shell> \fBmyisamchk \-dvv station\fR

296

MyISAM file: station

297

Isam\-version: 2

298

Creation time: 1996\-03\-13 10:08:58

299

Recover time: 1997\-04\-17 19:04:26

300

Data records: 1192 Deleted blocks: 0

301

Datafile parts: 1192 Deleted data: 0

302

Datafile pointer (bytes): 3 Keyfile pointer (bytes): 1

303

Max datafile length: 16777215 Max keyfile length: 131071

304

Recordlength: 834

305

Record format: Compressed

306

table description:

307

Key Start Len Index Type Root Blocksize Rec/key

308

1 2 4 unique unsigned long 10240 1024 1

309

2 32 30 multip. text 54272 1024 1

310

Field Start Length Type Huff tree Bits

311

1 1 1 constant 1 0

312

2 2 4 zerofill(1) 2 9

313

3 6 4 no zeros, zerofill(1) 2 9

314

4 10 1 3 9

315

5 11 20 table\-lookup 4 0

316

6 31 1 3 9

317

7 32 30 no endspace, not_always 5 9

318

8 62 35 no endspace, not_always, no empty 6 9

319

9 97 35 no empty 7 9

320

10 132 35 no endspace, not_always, no empty 6 9

321

11 167 4 zerofill(1) 2 9

322

12 171 16 no endspace, not_always, no empty 5 9

323

13 187 35 no endspace, not_always, no empty 6 9

324

14 222 4 zerofill(1) 2 9

325

15 226 16 no endspace, not_always, no empty 5 9

326

16 242 20 no endspace, not_always 8 9

327

17 262 20 no endspace, no empty 8 9

328

18 282 20 no endspace, no empty 5 9

329

19 302 30 no endspace, no empty 6 9

330

20 332 4 always zero 2 9

331

21 336 4 always zero 2 9

332

22 340 1 3 9

333

23 341 8 table\-lookup 9 0

334

24 349 8 table\-lookup 10 0

335

25 357 8 always zero 2 9

336

26 365 2 2 9

337

27 367 2 no zeros, zerofill(1) 2 9

338

28 369 4 no zeros, zerofill(1) 2 9

339

29 373 4 table\-lookup 11 0

340

30 377 1 3 9

341

31 378 2 no zeros, zerofill(1) 2 9

342

32 380 8 no zeros 2 9

343

33 388 4 always zero 2 9

344

34 392 4 table\-lookup 12 0

345

35 396 4 no zeros, zerofill(1) 13 9

346

36 400 4 no zeros, zerofill(1) 2 9

347

37 404 1 2 9

348

38 405 4 no zeros 2 9

349

39 409 4 always zero 2 9

350

40 413 4 no zeros 2 9

351

41 417 4 always zero 2 9

352

42 421 4 no zeros 2 9

353

43 425 4 always zero 2 9

354

44 429 20 no empty 3 9

355

45 449 30 no empty 3 9

356

46 479 1 14 4

357

47 480 1 14 4

358

48 481 79 no endspace, no empty 15 9

359

49 560 79 no empty 2 9

360

50 639 79 no empty 2 9

361

51 718 79 no endspace 16 9

362

52 797 8 no empty 2 9

363

53 805 1 17 1

364

54 806 1 3 9

365

55 807 20 no empty 3 9

366

56 827 4 no zeros, zerofill(2) 2 9

367

57 831 4 no zeros, zerofill(1) 2 9

368

.fi

369

.RE

370

.PP

371

\fBmyisampack\fR

372

displays the following kinds of information:

373

.TP 3n

374

\(bu

375

normal

376

.sp

377

The number of columns for which no extra packing is used.

378

.TP 3n

379

\(bu

380

empty\-space

381

.sp

382

The number of columns containing values that are only spaces. These occupy one bit.

383

.TP 3n

384

\(bu

385

empty\-zero

386

.sp

387

The number of columns containing values that are only binary zeros. These occupy one bit.

388

.TP 3n

389

\(bu

390

empty\-fill

391

.sp

392

The number of integer columns that do not occupy the full byte range of their type. These are changed to a smaller type. For example, a

393

BIGINT

394

column (eight bytes) can be stored as a

395

TINYINT

396

column (one byte) if all its values are in the range from

397

\-128

398

399

127.

400

.TP 3n

401

\(bu

402

pre\-space

403

.sp

404

The number of decimal columns that are stored with leading spaces. In this case, each value contains a count for the number of leading spaces.

405

.TP 3n

406

\(bu

407

end\-space

408

.sp

409

The number of columns that have a lot of trailing spaces. In this case, each value contains a count for the number of trailing spaces.

410

.TP 3n

411

\(bu

412

table\-lookup

413

.sp

414

The column had only a small number of different values, which were converted to an

415

ENUM

416

before Huffman compression.

417

.TP 3n

418

\(bu

419

zero

420

.sp

421

The number of columns for which all values are zero.

422

.TP 3n

423

\(bu

424

Original trees

425

.sp

426

The initial number of Huffman trees.

427

.TP 3n

428

\(bu

429

After join

430

.sp

431

The number of distinct Huffman trees left after joining trees to save some header space.

432

.sp

433

.RE

434

.PP

435

After a table has been compressed,

436

\fBmyisamchk \-dvv\fR

437

prints additional information about each column:

438

.TP 3n

439

\(bu

440

Type

441

.sp

442

The data type. The value may contain any of the following descriptors:

443

.RS 3n

444

.TP 3n

445

\(bu

446

constant

447

.sp

448

All rows have the same value.

449

.TP 3n

450

\(bu

451

no endspace

452

.sp

453

Do not store endspace.

454

.TP 3n

455

\(bu

456

no endspace, not_always

457

.sp

458

Do not store endspace and do not do endspace compression for all values.

459

.TP 3n

460

\(bu

461

no endspace, no empty

462

.sp

463

Do not store endspace. Do not store empty values.

464

.TP 3n

465

\(bu

466

table\-lookup

467

.sp

468

The column was converted to an

469

ENUM.

470

.TP 3n

471

\(bu

472

zerofill(\fIN\fR)

473

.sp

474

The most significant

475

\fIN\fR

476

bytes in the value are always 0 and are not stored.

477

.TP 3n

478

\(bu

479

no zeros

480

.sp

481

Do not store zeros.

482

.TP 3n

483

\(bu

484

always zero

485

.sp

486

Zero values are stored using one bit.

487

.RE

488

.TP 3n

489

\(bu

490

Huff tree

491

.sp

492

The number of the Huffman tree associated with the column.

493

.TP 3n

494

\(bu

495

Bits

496

.sp

497

The number of bits used in the Huffman tree.

498

.sp

499

.RE

500

.PP

501

After you run

502

\fBmyisampack\fR, you must run

503

\fBmyisamchk\fR

504

to re\-create any indexes. At this time, you can also sort the index blocks and create statistics needed for the MySQL optimizer to work more efficiently:

505

.sp

506

.RS 3n

507

.nf

508

shell> \fBmyisamchk \-rq \-\-sort\-index \-\-analyze \fR\fB\fItbl_name\fR\fR\fB.MYI\fR

509

.fi

510

.RE

511

.PP

512

After you have installed the packed table into the MySQL database directory, you should execute

513

\fBmysqladmin flush\-tables\fR

514

to force

515

\fBmysqld\fR

516

to start using the new table.

517

.PP

518

To unpack a packed table, use the

519

\fB\-\-unpack\fR

520

option to

521

\fBmyisamchk\fR.

522

.SH "COPYRIGHT"

523

.PP

524

525

.PP

526

This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the following terms: You may create a printed copy of this documentation solely for your own personal use. Conversion to other formats is allowed as long as the actual content is not altered or edited in any way. You shall not publish or distribute this documentation in any form or on any media, except if you distribute the documentation in a manner similar to how MySQL disseminates it (that is, electronically for download on a Web site with the software) or on a CD\-ROM or similar medium, provided however that the documentation is disseminated together with the software on the same medium. Any other use, such as any dissemination of printed copies or use of this documentation, in whole or in part, in another publication, requires the prior written consent from an authorized representative of MySQL AB. MySQL AB reserves any and all rights to this documentation not expressly granted above.

527

.PP

528

Please email

529

<docs@mysql.com>

530

for more information.

531

.SH "SEE ALSO"

532

msql2mysql(1),

533

my_print_defaults(1),

534

myisam_ftdump(1),

535

myisamchk(1),

536

myisamlog(1),

537

mysql(1),

538

mysql.server(1),

539

mysql_config(1),

540

mysql_explain_log(1),

541

mysql_fix_privilege_tables(1),

542

mysql_tzinfo_to_sql(1),

543

mysql_upgrade(1),

544

mysql_zap(1),

545

mysqlaccess(1),

546

mysqladmin(1),

547

mysqlbinlog(1),

548

mysqlcheck(1),

549

mysqld(8),

550

mysqld_multi(1),

551

mysqld_safe(1),

552

mysqldump(1),

553

mysqlhotcopy(1),

554

mysqlimport(1),

555

mysqlmanager(8),

556

mysqlshow(1),

557

perror(1),

558

replace(1),

559

safe_mysqld(1)

560

561

For more information, please refer to the MySQL Reference Manual,

562

which may already be installed locally and which is also available

563

online at http://dev.mysql.com/doc/.

564

.SH AUTHOR

565

MySQL AB (http://www.mysql.com/).

566

This software comes with no warranty.