Optimization Series | InnoDB engine Data Sheet compression characteristics test

First, preface
Innodb Plugin engine began to introduce a variety of formats of row storage mechanism, currently supports: Antelope, barracuda two kinds. Where Barracuda is compatible with Antelope format.
In addition, the Innodb plugin supports the row data compression feature, but only if the Barracuda row storage format is used.
The table space enables compression only if the InnoDB tablespace file storage format is modified to: Barracuda, 2 options need to be modified:
Innodb_file_format = "Barracuda"
Innodb_file_format_max = "Barracuda"

Here is the comparison test results
Second, table space compression ratio
1. A project Data sheet compression ratio
2.1 Data Sheet Taba
Before compressing
-RW-RW----1 mysql mysql 19038208 Mar 13:59 taba.ibd (18.1G)
After compression
-RW-RW----1 mysql mysql 9.2G 19:11 taba.ibd
Difference: 12414976 ~= 12124 MB ~= 11.83 Gb, save 49.32%

2.2 Data Sheet Tabb
Before compression
-RW-RW----1 mysql mysql 1.1G 13:51 tabb.ibd
After compression
-RW-RW----1 mysql mysql 404M 09:59 tabb.ibd
Difference: 1.1G-404MB ~= 696MB, save 63.27%

2.3 Data Sheet TABC
Before compression
-RW-RW----1 mysql mysql 16195584 Mar 14:25 tabc.ibd (15.4G)
After compression
-RW-RW----1 mysql mysql 8130560 Mar 10:49 tabc.ibd (7.7G)
Difference: 16195584-8130560 = 8065024 ~= 7G, save 49.79%

2. TPCC Test Library

Data table        Pre-compression (in bytes)      after compression (in bytes)      Reduce the number of bytes      save ratio warehoused  192                    128                    64              33.33%districtd   9216                   5120                   4096            44.44%itemd        17408                  11264                 6144            35.29%new_ordersd 397312                 155648                 241664         60.82%o rdersd     2957312                786432                 2170880        73.41%historyd     4694016               1347584                3346432         71.29%customerd   20619264               12881920               7737344        37.52%stockd       35737600              23801856               11935744        33.40%order_lined 43991040               15740928               28250112       64.22% Full Library          109603176             59028260               50574916        46.14%

Three, TPCC contrast test

1. Non-compressed table 4212.650 tpmc 4203.600 TpmC2. Compression table 428.983 TPMC 663 .017 tpmc 707.883 tpmc 707.183 tpmc

Iv. Testing and exporting
1. Non-compression

Time Mysqldump tpcc1000 | gzip > Tpcc1000.sql.gzreal 148m22.614suser 148m56.915ssys 3m33.802s

2. Compress the table

Time Mysqldump tpcc1000_compress | gzip > Tpcc1000_compress.sql.gzreal 129m42.541suser 149m12.407ssys 3m44.835s

Five, test import
1. Non-compressed table
1.1 Concurrent Import

Start: 2012-04-01 23:43:03.777173866 +0800 end: 2012-04-02 03:21:05.003590866 +0800 Time: 03:38:02

2. Compress the table
1.1 Concurrent Import

Start: 2012-04-01 23:40:34.289173866 +0800 end: 2012-04-03 11:06:44.740992866 +0800 Time: 35:26:10

Vi. Conclusion
Judging from the test results, InnoDB engine compression features good results, after compression and MyISAM engine almost.
However, the degradation of transaction concurrency after compression is very serious, from this point of view, it is suitable for high compression, but the concurrency transaction requirements are not high, such as some high CPU performance, but also need to read a large number of scenarios, the scope of application is limited.

--------------------------------------Split Line--------------------------------------

http://zhishuedu.comTraining is a professional quality training brand jointly launched by senior MySQL expert Ye Jinlong and Wu Bingxi, with MySQL dba combat optimization and Python devops Development course, which is the most conscientious and The most quality training courses.

This article is from the "Lao Ye teahouse" blog, please be sure to keep this source http://imysql.blog.51cto.com/1540006/1879730

Optimization Series | InnoDB engine Data Sheet compression characteristics test