Make progress every day-MySQL-myisampack and mysqlmyisampack

Make progress every day-MySQL-myisampack and mysqlmyisampack

I. Introduction

Myisampack is a tool for compressing tables using the MyISAM engine. Generally, it compresses 40% ~ 70%. When you need to access data, the server will read the required information into the memory. Therefore, the performance will be better when you access a specific record, because you only need to extract one record

MySQL uses mmap () to compress and map the changes. If mmap () does not work, MySQL will return normal read/write operations.

The compressed table is read-only, and myisampack does not support partition tables.

Ii. Command Format

Myisampack option file name

Iii. Common Parameters

-- Backup-B -- backup uses tbl_name. OLD to back up data files

-- Force-f generates a compressed table, even if it is larger than the original table, or if the intermediate file that previously called myisampack exists. (Create an intermediate file named tbl_name.TMD in the database directory during myisampack table compression. If myisampack is killed, the. TMD file will be deleted ). In general, if myisampack finds that tbl_name.TMD exists, it exits and prompts an error. Use -- force and myisampack to compress the table.

-- Join = big_tbl_name-j big_tbl_name join all the tables in the command line into a table big_tbl_name. All tables to 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 storage size of the record length, in bytes. The value must be 1, 2, or 3. Myisampack stores all rows with the pointer 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 the compression process, it can prompt that it may have used a short length. In this case, myisampack outputs a prompt that you can use a shorter record length when compressing the same file.

-- Silent,-s silence mode. Output is written only when an error occurs.

-- Test,-t does not actually compress the table, but test compression.

-- Tmpdir = path,-T path: Use myisamchk to create the directory of the temporary file.

-- Verbose,-v lengthy mode. Information about the write compression operation and its results.

-- Version,-V displays the version information and exits.

-- Wait,-w. If the table is in use, wait and try again. If the mysqld server is called using the -- skip-external-locking option, it is not a good idea to call myisampack if the table may be updated during compression.

After running myisampack, you must run myisamchk to recreate the index. At this point, you can also sort the index blocks and create the statistical information required by the MySQL optimizer to work more effectively: shell> myisamchk-rq -- sort-index -- analyze tbl_name.MYI after installing the compressed table to the MySQL database directory, execute mysqladmin flush-tables to force mysqld to use the new table. To decompress a compressed table, use the -- unpack option of myisamchk or isamchk.

 

-- Help

Iv. Common examples

1. myisampack compression table

[Root @ localhosttest2] # ll-tr

Total usage 180

-Rw ---- 1 mysql 65 July 16 16:40 db. opt

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

-Rw ---- 1 mysql 1024 July 16 17:29 t1.MYI

-Rw ---- 1 mysql 161742 July 16 17:29 t1.MYD

[Root @ localhost test2] # myisampack t1

Compressing t1.MYD: (23106 records)

-Calculating statistics

-Compressing file

85.68%

[Root @ localhosttest2] # ll-tr

Total usage 44

-Rw ---- 1 mysql 65 July 16 16:40 db. opt

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

-Rw ---- 1 mysql 23167 July 16 17:29 t1.MYD

-Rw ---- 1 mysql 1024 July 16 17:30 t1.MYI

 

Query 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 back up database files

[Root @ localhost test2] # ll

-Rw ---- 1 mysql 65 July 16 16:40 db. opt

-Rw ---- 1 mysql 8572 July 17 09:28 t1.frm

-Rw ---- 1 mysql 73 July 17 09:50 t1.MYD

-Rw ---- 1 mysql 1024 July 17 10:04 t1.MYI

-Rw ---- 1 mysql 217 t1.OLD

-Rw ---- 1 mysql 8598 July 17 10:10 t2.frm

-Rw ---- 1 mysql mysql4344192 July 17 10:11 t2.MYD

-Rw ---- 1 mysql 4955136 July 17 10:11 t2.MYI

-Rw ---- 1 mysql 8572 July 17 10:06 t3.frm

-Rw ---- 1 mysql 147456 July 17 10:06 t3.ibd

-Rw ---- 1 mysql 8598 July 17 10:11 t4.frm

-Rw ---- 1 mysql 4344192 July 17 10:11 t4.MYD

-Rw ---- 1 mysql 1024 July 17 10:11 t4.MYI

-Rw ---- 1 mysql 8598 July 17 10:12 t5.frm

-Rw ---- 1 mysql 1996157 July 17 10:12 t5.MYD

-Rw ---- 1 mysql 1024 July 17 10:12 t5.MYI

-Rw ---- 1 mysql mysql4344192 July 17 10:12 t5.OLD

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

3. insert data to T5

Mysql> insert into t5 (str_number) values (1 );

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

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.