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