Make a little progress every day up to--mysql--myisampack

First, Introduction

Myisampack is a tool that compresses the use of MyISAM engine tables, typically compresses 40%~70%, and when access to data is required, the server reads the required information into memory, so performance is better when accessing specific records because only one record needs to be extracted

MySQL uses mmap () to change the compression mapping, if mmap () does not work, MySQL will return to the normal read-write file operation

The compressed table will become read-only and Myisampack does not support partitioned tables.

Second, command Format

Myisampack option file name

Third, Common Reference

--backup-b--backup uses Tbl_name. Old backup data file

--FORCE-F produces a compressed table, even if it is larger than the original table, or if the intermediate file that was previously called Myisampack exists. (Myisampack creates a name of tbl_name in the database directory when the table is compressed.) TMD's Intermediate file. If you kill Myisampack,. TMD files will be deleted). Usually, if Myisampack finds Tbl_name. The TMD is present and exits with a hint of error. The table must be compressed with--force,myisampack.

--join=big_tbl_name-j Big_tbl_name joins all tables in the command line as a table big_tbl_name. All tables that will be connected must have an equal structure (the same column name and type, the same index, and so on).

--packlength=len,-p len Specifies the record-length storage size, in bytes. The value should be 1, 2, or 3. Myisampack holds all rows with a length of 1, 2, or 3 bytes. In most normal cases, myisampack can determine the exact length value before starting to compress the file, but during compression it can indicate that it may have used a short length. In this case, myisampack outputs a hint that you can use a shorter record length the next time you compress the same file.

--silent,-s silent mode. The output is only written if an error occurs.

--TEST,-T does not actually compress the table, just test compression.

--tmpdir=path,-t Path uses MYISAMCHK to create a directory of temporary files.

--verbose,-v verbose mode. Writes information about the process and the results of the compression operation.

--VERSION,-V Displays the version information and exits.

--wait,-w If the table is being used, wait and try again. If the MYSQLD server is called with the--skip-external-locking option, calling Myisampack is not a good idea if the table may be updated during the compression process.

After you run Myisampack, you must run Myisamchk to re-create the index. At this point, you can also sort the index blocks and create the statistics required by the MySQL optimizer to work more efficiently: shell> Myisamchk-rq--sort-index--analyze tbl_name.  MYI you install a compressed table into the MySQL database directory, you should perform mysqladmin flush-tables to force mysqld to use the new table. To decompress a compressed table, use the Myisamchk or isamchk--unpack option.

--help Help

Four, Common examples

1. myisampack Compression table

[[Email protected]]# ll-tr

Total Dosage 180

-RW-RW----1 MySQL mysql 65 July 16:40 db.opt

-RW-RW----1 mysql mysql 8556 July 16:46 t1.frm

-RW-RW----1 MySQL mysql 1024 July 17:29 t1. MYI

-RW-RW----1 mysql mysql 161742 July 17:29 t1. MYD

[Email protected] test2]# Myisampack T1

compressing t1. MYD: (23106 Records)

-Calculating statistics

-Compressing file

85.68%

[[Email protected]]# ll-tr

Total Dosage 44

-RW-RW----1 MySQL mysql 65 July 16:40 db.opt

-RW-RW----1 mysql mysql 8556 July 16:46 t1.frm

-RW-RW----1 mysql mysql 23167 July 17:29 t1. MYD

-RW-RW----1 MySQL mysql 1024 July 17:30 t1. MYI

Querying in MySQL

Mysql>show table status like ' T1 ' \g;

1. row***************************

Name:t1

Engine:myisam

Version:10

Row_format:fixed

rows:22857

Avg_row_length:7

data_length:159999

max_data_length:1970324836974591

index_length:1024

data_free:0

Auto_increment:null

Create_time:2015-07-16 16:46:17

Update_time:2015-07-16 17:29:40

Check_time:null

Collation:latin1_swedish_ci

Checksum:null

Create_options:

Comment:

1 row in Set (0.00 sec)

ERROR:

No query specified

Mysql>show table status like ' T1 ' \g;

1. row***************************

Name:t1

Engine:myisam

Version:10

Row_format:fixed

rows:23177

Avg_row_length:7

data_length:162239

max_data_length:1970324836974591

index_length:1024

data_free:0

Auto_increment:null

Create_time:2015-07-16 16:46:17

Update_time:2015-07-16 17:30:48

Check_time:null

Collation:latin1_swedish_ci

Checksum:null

Create_options:

Comment:

1 row in Set (0.00 sec)

T8 Lines:7 Columns:9

2. Myisampack backing Up Database files

[email protected] test2]# LL

-RW-RW----1 MySQL mysql 65 July 16:40 db.opt

-RW-RW----1 mysql mysql 8572 July 09:28 t1.frm

-RW-RW----1 MySQL mysql 73 July 09:50 t1. MYD

-RW-RW----1 MySQL mysql 1024 July 10:04 t1. MYI

-RW-RW----1 mysql mysql 217 month 09:50 t1. Old

-RW-RW----1 mysql mysql 8598 July 10:10 t2.frm

-RW-RW----1 MySQL mysql4344192 7 Month 10:11 T2. MYD

-RW-RW----1 mysql mysql 4955136 July 10:11 t2. MYI

-RW-RW----1 mysql mysql 8572 July 10:06 t3.frm

-RW-RW----1 mysql mysql 147456 July 10:06 t3.ibd

-RW-RW----1 mysql mysql 8598 July 10:11 t4.frm

-RW-RW----1 mysql mysql 4344192 July 10:11 T4. MYD

-RW-RW----1 MySQL mysql 1024 July 10:11 T4. MYI

-RW-RW----1 mysql mysql 8598 July 10:12 t5.frm

-RW-RW----1 mysql mysql 1996157 July 10:12 T5. MYD

-RW-RW----1 MySQL mysql 1024 July 10:12 T5. MYI

-RW-RW----1 MySQL mysql4344192 7 Month 10:12 T5. old

We found that the backed up data file is the same size as the original file, but the compressed data file will be smaller

3. Inserting data into the T5

Mysql> INSERT INTO T5 (Str_number) values (1);

ERROR 1036 (HY000): Table ' T5 ' is read only

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Make a little progress every day up to--mysql--myisampack