MySQL INNODB Plugin 测试（二）
前言：
继上次对Innodb Plugin 测试之后，对新的文件格式没有做很好的测试，现在将对他的新文件格式（Barracuda）做下测试，看Barracuda新格式到底相比Antelope老格式有那些提升。数据压缩的理念是，通过提高CPU利用率和节约成本，降低数据库容量及I/O负载，从而使数据吞吐率得到显著提高。数据压缩不仅大大减少了数据库所需的存储空间，而且还减少了I/O的工作量，提高了数据吞吐率，从而节约开销处理成本。节省存储成本固然重要，但是减少I/O成本更为关键。
概念：（可以看第一次的测试文档）
Antelope 是 InnoDB 老的文件格式，数据表格式支持：Redundant和Compact 。在这些格式下，InnoDB在聚集索引和主键中存储了BLOB, VARCHAR 和 TEXT字段的前768个字节。紧随768字节的前缀之后的，是一个20字节的指针，指针指向溢出页且该溢出页包含有余下的列值。
Barracuda 是 InnoDB Plugin 支持的新的文件格式，在原来的基础上新增了两种数据表格式的支持：Dynamic 和 Compressed，这里特别对Compressed进行测试。对于off-page存储的表而言，聚集索引的记录只包含20字节的指针指向溢出页，每一列一个指针。是否off-page存储某一列，取决于页面大小和该行的总大小。
条件：
创建新格式的条件是：innodb_file_format配置参数来启动“Barracuda”数据库文件格式时，压缩才能被指定，还需要开启innodb_file_per_table。
语法：
InnoDB数据页通常是16KB的（未压缩）。从InnoDB Plugin开始，你可以使用ROW_FORMAT=COMPRESSED, KEY_BLOCK_SIZE属性，或者使用CREATE TABLE和ALTER TABLE语句来实现表的压缩。InnoDB依据选项值的组合尝试将每个数据页压缩成1KB、2KB、4KB、8KB或16KB。压缩页面大小设置得太大会浪费空间，但是并不需要像往常那样对页面进行压缩。如果压缩页面大小设置得太小，插入和更新时则需要重新压缩，非常耗时；同时，B-tree节点会更频繁地分裂，最终导致更大的数据文件和更低效率的索引,通常情况下可以将压缩页面大小设置成8K或4K字节。考虑到InnoDB最大记录大小在8K左右，KEY_BLOCK_SIZE=8是最为保险的设置方案。
ROW_FORMAT各个值的意义：
|
选项 |
用途 |
描述 |
|
ROW_FORMAT=REDUNDANT |
设置存储格式，适用于 MySQL 5.0.3之前的版本 |
效率不如 ROW_FORMAT=COMPACT; 用于向下兼容 |
|
ROW_FORMAT=COMPACT |
设置默认存储格式，适用于MySQL 5.0.3及其之后的版本 |
在聚集索引页中存储768字节前缀的长列值，剩余字节则存储在溢出页 |
|
ROW_FORMAT=DYNAMIC |
仅在设置innodb_file_format=Barracuda时可用 |
若适合，将列值存储在聚集索引页内；反之，则只存储20字节的指针到溢出页（无前缀） |
|
ROW_FORMAT=COMPRESSED |
仅在设置innodb_file_format=Barracuda时可用 |
使用zlib压缩表和索引，默认压缩页的大小为8K 字节；使用KEY_BLOCK_SIZE=8 |
|
KEY_BLOCK_SIZE=n |
仅在设置innodb_file_format=Barracuda时可用 |
指定压缩页大小为1，2，4，8或16K字节；使用ROW_FORMAT=DYNAMIC |
要创建一个压缩表，应使用如下语句：
CREATE TABLE name
(column1 INT PRIMARY KEY)
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=1/2/4/8/16;
加载数据：
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| page_size | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| 1024 | 0 | 0 | 0 | 0 | 0 |
| 2048 | 0 | 0 | 0 | 0 | 0 |
| 4096 | 131736 | 121419 | 59 | 4022 | 0 | (2 min 21.62 sec)
| 8192 | 44574 | 44524 | 12 | 129 | 0 | (1 min 41.66 sec)
| 16384 | 27936 | 27936 | 5 | 131 | 0 | (1 min 35.74 sec)
+-----------+--------------+-----------------+---------------+----------------+-----------------+
压缩比率越高，加载数据越慢（后面进行分析）
测试：
还是一样用sysbench脚本测试：
1：sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=test_plugin --oltp-table-size=5000000 --mysql-user=root --mysql-password=123456 --mysql-socket=/var/run/mysqld/mysqld.sock prepare
2：sysbench --num-threads=(1~512) --test=oltp --mysql-table-engine=innodb --mysql-db=test_plugin --oltp-table-size=500000 --mysql-user=root --mysql-password=123456 --mysql-socket=/var/run/mysqld/mysqld.sock run
先在test_plugin库中生成测试表；
root@localhost : test_plugin 05:05:41>desc sbtest;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| k | int(10) unsigned | NO | MUL | 0 | |
| c | char(120) | NO | | | |
| pad | char(60) | NO | | | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)
第二步对该表进行操作（SELECT，UPDATE，DELETE）；
跑完脚本之后，转换成新格式的Compressed，执行：
root@localhost : test_plugin 04:26:59>alter table sbtest row_format=compressed key_block_size=8;
继续用脚本测试，执行出来的结果图：
| Thread | 500bp_compact | 500bp_compressed |
| 1 | 256 | 236 |
| 2 | 471 | 418 |
| 4 | 457 | 401 |
| 8 | 439 | 413 |
| 16 | 436 | 404 |
| 32 | 386 | 385 |
| 64 | 376 | 380 |
| 128 | 356 | 350 |
| 256 | 337 | 330 |
| 512 | 313 | 293 |
从图中看出，默认的compact比compressed的TPS更高，在前半部分大致高出10%左右。虽然Compressed的文件被压缩了，看来效果不明显。
表的数据类型是CHAR，长度远远没有达到需要溢出页。那来测试大字段看看怎么样：把生成的表的字段改成text，再填充尽量多的数据到字段中（中文），再用上面一样的脚本跑：
| Thread | 500bp_compact | 500bp_compressed |
| 1 | 262 | 241 |
| 2 | 446 | 418 |
| 4 | 470 | 447 |
| 8 | 464 | 431 |
| 16 | 432 | 408 |
| 32 | 414 | 400 |
| 64 | 385 | 380 |
| 128 | 379 | 356 |
| 256 | 371 | 335 |
| 512 | 325 | 300 |
发现情况还是一样，compact还是比compressed好，从测试上证明目前compressed只是有存储空间上的优势。
综合可以压缩的大小做了统计4k，8k，16k：
表大小及结构：
大小:
-rw-rw---- 1 mysql mysql 8.5K 2012-10-27 17:55 sbtest_compact.frm
-rw-rw---- 1 mysql mysql 692M 2012-10-28 20:59 sbtest_compact.ibd
-rw-rw---- 1 mysql mysql 8.5K 2012-10-28 20:33 sbtest_compressed_16.frm
-rw-rw---- 1 mysql mysql 584M 2012-10-28 21:29 sbtest_compressed_16.ibd
-rw-rw---- 1 mysql mysql 8.5K 2012-10-28 20:33 sbtest_compressed_4.frm
-rw-rw---- 1 mysql mysql 168M 2012-10-28 21:13 sbtest_compressed_4.ibd
-rw-rw---- 1 mysql mysql 8.5K 2012-10-28 20:39 sbtest_compressed_8.frm
-rw-rw---- 1 mysql mysql 296M 2012-10-28 21:20 sbtest_compressed_8.ibd
结构:
root@localhost : test_plugin 10:14:36>show table status like 'sbtest%'\G;
*************************** 1. row ***************************
Name: sbtest_compact
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 354727
Avg_row_length: 1941
Data_length: 688668672
Max_data_length: 0
Index_length: 17317888
Data_free: 7340032
Auto_increment: 1000001
Create_time: 2012-10-28 21:02:26
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************
Name: sbtest_compressed_16
Engine: InnoDB
Version: 10
Row_format: Compressed
Rows: 2668749
Avg_row_length: 216
Data_length: 577470464
Max_data_length: 0
Index_length: 17317888
Data_free: 6291456
Auto_increment: 1000001
Create_time: 2012-10-28 22:11:51
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=COMPRESSED KEY_BLOCK_SIZE=16
Comment:
*************************** 3. row ***************************
Name: sbtest_compressed_4
Engine: InnoDB
Version: 10
Row_format: Compressed
Rows: 2673613
Avg_row_length: 60
Data_length: 161210368
Max_data_length: 0
Index_length: 5128192
Data_free: 4718592
Auto_increment: 1000001
Create_time: 2012-10-28 21:14:44
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=COMPRESSED KEY_BLOCK_SIZE=4
Comment:
*************************** 4. row ***************************
Name: sbtest_compressed_8
Engine: InnoDB
Version: 10
Row_format: Compressed
Rows: 2668749
Avg_row_length: 108
Data_length: 288743424
Max_data_length: 0
Index_length: 9199616
Data_free: 5767168
Auto_increment: 1000001
Create_time: 2012-10-28 21:21:24
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=COMPRESSED KEY_BLOCK_SIZE=8
Comment:
4 rows in set (0.14 sec)
INNODB默认每页是16k，理论上来讲key_block_size=16 相当于没有压缩，他们应该大小一致。其实，B-tree节点本身在未压缩时占用了同样多的页面。使用16K的压缩页面大小，可以为BLOB, VARCHAR或TEXT字段节省存储及I/O成本，因为这样的数据往往是易于压缩的，也不需要太多“溢出页”。
分析：
为什么压缩（compressed）的比紧凑（compact）的性能差呢？在官方文档上看到：如果更新主要改变的是非索引列或者一些包含了碰巧被存储为“off-page”的BLOBs及大的字符串的列，压缩的开销是可以接受的。如果表中唯一更改的是使用单递增主键的INSERTs语句，并且不存过太多非聚集索引，那么，便没必要重组或重新压缩索引页。那压缩有哪些开销呢？其实在安装好INNODB PLUGIN 之后，在information_schema数据库中看到INNODB开头的7张表。其中压缩主要关注 INNODB_CMP开头的表。像上面做出来的测试，表中的数据是：
root@localhost : information_schema 09:26:28>select * from INNODB_CMP;
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| page_size | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| 1024 | 0 | 0 | 0 | 0 | 0 |
| 2048 | 0 | 0 | 0 | 0 | 0 |
| 4096 | 197439 | 97429 | 589 | 85529 | 18 |
| 8192 | 21740 | 19853 | 337 | 19513 | 4 |
| 16384 | 9731 | 9731 | 114 | 17853 | 3 |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
root@localhost : information_schema 10:27:25>select * from INNODB_CMPMEM;
+-----------+------------+------------+----------------+-----------------+
| page_size | pages_used | pages_free | relocation_ops | relocation_time |
+-----------+------------+------------+----------------+-----------------+
| 1024 | 0 | 0 | 0 | 0 |
| 2048 | 0 | 0 | 0 | 0 |
| 4096 | 5051 | 1 | 5111 | 0 |
| 8192 | 4348 | 0 | 17707 | 0 |
| 16384 | 10281 | 0 | 0 | 0 |
+-----------+------------+------------+----------------+-----------------+
INNODB_CMP:
|
字段名 |
注释 |
|
PAGE_SIZE |
压缩页面的大小（以为字节单位） |
|
COMPRESS_OPS |
B-tree页的压缩次数（页面大小由PAGE_SIZE指定）。当出现空白页时，或者未压缩的修改日志空间已满时，页面就会被压缩。 |
|
COMPRESS_OPS_OK |
成功压缩B-tree页面的次数（页面大小由PAGE_SIZE指定）。该次数不能超过COMPRESS_OPS的值。 |
|
COMPRESS_TIME |
压缩B-tree页面（页面大小由PAGE_SIZE指定）总共所花的时间（以秒为单位）。 |
|
UNCOMPRESS_OPS |
B-tree页的解压次数（页面大小由PAGE_SIZE指定）。压缩失败的情况下，或者第一次访问B-tree页面时buffer pool中不存在未压缩的页面，那么B-tree页面就会被解压。 |
|
UNCOMPRESS_TIME |
解压B-tree页面（页面大小由PAGE_SIZE指定）总共所花的时间（以秒为单位）。 |
INNODB_CMPMEM:
|
字段名 |
注释 |
|
PAGE_SIZE |
Block 的页面大小（以为字节单位），同时也记录在表中。 |
|
PAGES_USED |
当前使用的block的数量（页面大小由PAGE_SIZE指定）。 |
|
PAGES_FREE |
当前可分配的block的数量（页面大小由PAGE_SIZE指定）。 该字段显示的是memory pool中的外部碎片。理想状态下，这个数量应该不超过1。 |
|
RELOCATION_OPS |
重定位一个block的次数（页面大小由PAGE_SIZE指定）。想得到一个更大的freed block（释放块），buddy system会重定位freed block的“buddy neighbor”。从表INNODB_CMPMEM_RESET中读取该字段 将会reset 这一数字。 |
|
RELOCATION_TIME |
重定位block（页面大小由PAGE_SIZE指定）总共所花的时间（以微秒为单位）。从表INNODB_CMPMEM_RESET中读取该字段将会reset 这一数字。 |
通常情况下,对于字符串数量适中的表来说,读取数据比写入数据速度更快,压缩性能最佳。由于没有保险的方法来预测压缩是否有利于某一特定的情况,我们通常在一些具有代表性的配置器上通过某一具体的工作量及运行的数据来测试其性能。在决定哪些表需要进行压缩。理论上讲,当 CPU 时间可用于压缩及解压数据时,压缩效果最佳。因此,如果工作量是由 I/O引起的,而不是由 CPU 引起,压缩便能够提高整体性能。
上面做的测试例子,B-tree 页面经常更新,因此需要特殊处理。我们应当尽量减少 B-tree 节点分裂的次数,同时也要减少解压和重新压缩。要是一张表频繁更新和写入,既读少写多,用压缩表造成的性能问题很明显(INNODB_CMP 表中的数据刚好证明了这点)。
解释：
通过INNODB_CMP的表可以看到，之前测试的key_blocks_size=4的时候，成果压缩比率很低，这张表不适合用4k来压缩，8k压缩比率也只有91%，16k有100%，从上面的信息看出页面压缩比率越高，压缩和解压时间就越高，性能消耗就越差（具体原因后面分析），测试当中刚好证明了这点。而压缩比率越高，使用的block就越少，占用空间就越少。
顾名思义压缩消耗的CPU资源，因为数据都是以压缩形式存储到B-Tree节点中，而读取，更新都需要解压。为了最大限度地降低I/O以及减少解压页面的可能性，有时候缓冲池会同时包含压缩和未压缩的数据库页面。但是，为了给其他所需的数据库页面腾出空间，InnoDB通过使用“最近最少使用列表”（LRU），InnoDB会把未压缩的页面从缓冲池中“驱逐”出去，而将压缩页面保留在内存里。在已压缩的InnoDB表中，每个（不管是1K、2K、4K还是8K的）压缩页面都对应一个16K字节的非压缩页面。想要访问页面的数据，InnoDB必须从磁盘读取压缩页（除非内存中已存在该压缩页），然后将压缩页解压成最初的16K字节。
结论：
经过以上测试得出结论是:Compressed 压缩还是比 compact 性能差 10%左右。基本理念是,当 CPU 繁忙时,应避免在解压页面上花费太多的处理时间;当 CPU 有闲置周期可用于解压页面时。一 般情况下看是否值得用压缩特性的原因是:1,把一个未压缩表中的某些数据拷贝到另一个类似的压缩表(具有相同的索引),然后查看生成文件的大小。 2,你可以检查出压缩成功的操作在整体压缩操作中所占的比例(在表 INNODB_CMP 中,试比较 COMPRESS_OPS 和COMPRESS_OPS_OK。详情参考 INNODB_CMP)。如果某个表完成的压缩操作成功比例很高,
那么这个表将很适合也很容易进行压缩操作。
一个数据库系统瓶颈要是在 CPU 上,最好就不要使用压缩表。反之要是 CPU 比较闲置,并且存储空间有限,可以牺牲 10%左右的性能,使用压缩表是一个不错的选择。总之,是否能使用压缩表已经多大的压缩比例,需要不断的测试和实验
