innodb压缩

Overview of Table Compression

     通过使用压缩技术，一般有两个好处：一是减少磁盘和内存之间的数据传输量从而提升性能，另外一个是减少磁盘和内存空间的使用量。尤其是二级索引意义更大，因为索引数据同样可以被压缩。在针对SSD设备，压缩非常重要，因为SSD通常比较贵，所以需要尽量控制空间的使用。

     由于处理器和cache技术发展超过磁盘存储设备技术，许多工作负载类型都是disk-bound。Disk-bound的定义为：一种工作负载类型，其主要的系统瓶颈是磁盘IO，也叫I/O-bound，通常表现为频繁的磁盘写，或者随机读操作超过了buffer pool的处理能力。这时候数据压缩可以通过使用更少的db大小来减少io，不过需要消耗一部分CPU资源来做压缩操作。

    Create innodb table的时候指定row_format=comparessed可以使用更小的page size，默认是16K。page size通过创建表时的key_block_size参数控制，不同的该值表示在它自己的.ibd文件中使用的page size，而不是系统表空间，所以必须在独立表空间模式下设置。该值太小的话可能会导致一个完整的页无法完全压缩，这时创建表或者更新表语句会报错。

Enabling Compression for a Table

    在创建一个压缩表之前，需要确保innodb_file_per_table为1，innodb_file_format为Barracuda，大概的步骤如下

    SET GLOBAL innodb_file_per_table=1;
    SET GLOBAL innodb_file_format=Barracuda;
    CREATE TABLE t1
    (c1 INT PRIMARY KEY)
    ROW_FORMAT=COMPRESSED
    KEY_BLOCK_SIZE=8;

    如果设置了ROW_FORMAT=COMPRESSED，那可以忽略key_block_size设置，这时默认的key_block_size为innodb_page_size值的一半；

    如果设置了key_block_size，那可以忽略ROW_FORMAT=COMPRESSED，这时压缩时自动打开的；

具体的key_block_size的值应该设置为多少，通常的方法是在实际工作负载下压测得到一个最佳值。Key_block_size值必须<=innodb_page_size,否则会被强制设置为innodb_page_size的一半，如果开启了innodb_strict_mode，则报错。如果两个值相等，或许没什么压缩效果，除非是表中有需要long blob，text等字段。

Tuning Compression for InnoDB Tables

一般需要考虑以下几点

1 哪些表需要压缩

2 page size该取值多少

3 如何根据工作负载类型调整buffer pool

4 压缩表上大量的dml操作可能会导致压缩失败，如何调整额外的参数来解决这个问题

Information Schema Tables about Compression

information_schema.innodb_cmp，有以下几列

page_size:一般有1k，2k，4k，8k，16k几个档位

compress_ops：b-tree页压缩的次数

compress_ops_ok：b-tree页成功压缩的次数

compress_time：压缩花掉的累计时间

uncompress_ops：没有被压缩成功的次数，一般是压缩失败或者第一次访问时

uncompress_time：没有压缩成功花掉的总时间

INNODB_CMP：该表为每一个档位的page size（key_block_size）展现压缩情况，一般有1k，2k，4k，8k，16k几个档位，可以利用这个表来测试某一张表A是否应该采用压缩技术（测试时只有这一张压缩表有访问）

INNODB_CMP_PER_INDEX：该表提供每一张表和索引的压缩情况，这个信息对某一张表是否应该采用压缩技术更加有效，适用于开发测试环境，这样可以模拟不同工作负载，数据量，压缩率下的性能表现。不过这个比较消耗系统资源，测试时候需要开启innodb_cmp_per_index_enabled参数。

如果大部分的cpu时间都花在压缩和解压上，那么使用更快或更多的cpu可以提升性能，或者是增大缓冲池大小，因为这样更多的未压缩页就能常驻在内存，从而减少了在内存中以压缩格式存储的数据需要解压的操作。

大量的压缩操作(insert update delete)证明压缩表更新太过频繁，这时选择一个较大的page size，或者压缩表上主要负责读操作可能有益于系统性能

成功压缩的次数（COMPRESS_OPS_OK）和总共的压缩次数（COMPRESS_OPS）比例很高的话，通常表明系统运行良好，如果这个比例很低，mysql将会重新压缩。在这种情况下，需要避免某些表的压缩操作，或者增大key_block_size。通常来说，失败率超过1%或者2%的情况下，就建议关闭压缩。

 

innodb压缩的内部实现

压缩算法

压缩算法采用LZ77，在这个算法下，如果压缩效率好点的话，压缩后的大小和未压缩的数据大小比如在25-50%左右，在这种情况下就会有效地通过消耗一些CPU来减少IO操作，增大吞吐量，可以通过调节压缩程度（innodb_compression_level参数）来权衡压缩比和CPU使用率

Innodb数据存储和压缩

    一般来说所有的innodb表数据存储在b-tree的page上，而在btree节点和overflow pages上的压缩处理方式是不同的，所谓的overflow page就是单独分配磁盘pages来存储varchar，blob，text字段，因为他们通常超过了一个btree page的大小，这些列也就是off-page列。

    如果是btree pages的压缩，由于它们经常被更新，btreepages需要做特殊的处理，所以怎么尽量减少btree的分裂次数，解缩和重新压缩的次数。为了避免在oltp应用下的高频率写操作引起的高频率压缩失败，可以调整innodb_compression_failure_threshold_pct和innodb_compression_pad_pct_max两个参数来优化。一般情况下，mysql需要在每个btree page上至少存储两条记录，而对于压缩表而言只需要能够存储一条记录就行，但是需要在modification log里面fit。如果create table或者createindex报错ERROR HY000：Too big row，那就是最大的行记录无法fit，这时就需要增大key_block_size或者禁用压缩功能。

    对于blob，text或长varchar列，压缩模式下存储可能也是完全的off-page的，这主要取决于这一列自身的长度和整行记录的长度。如果是off-page模式存储，聚集索引上只会为每一列包含一个20字节的指针指向对应的overflow pages。当整行记录大小无法fit这个聚集索引所在的page时，mysql会选在最大的那一列作为off-page存储，直到行记录能够完全fit这个聚集索引page，所以如果一行记录无法在一个压缩页上fit时将会报错。

    当一个表是压缩模式，所有写到overflow page的数据都会被压缩，所以对于blob等字段的压缩时非常高效的，不过一些image数据比如JPEG文件，它本身就是已经被压缩过的，这时候再二次压缩就没什么太大效果了，这只会浪费CPU资源而收效甚微。Blob等字段的压缩建议使用16K的key_block_size，因为这样通常能够更好地压缩，并且使用到更少的overflow page。

Compression and the InnoDB Buffer Pool

    在一个压缩表中，每一个压缩过的页要和未压缩的页保持一致。当在一个page里访问数据时，如果数据不在缓冲池，mysql从磁盘中读取压缩页，然后解压到它原来的格式。为了减少IO和不必要的解压操作，有时缓冲池会同时包含压缩过和未压缩的数据页，当在一段时间内该页没有访问时，mysql会将它压缩后写回磁盘，从而来释放缓冲池空间。所以说，任何一个时间点，缓冲池里可能包含压缩和未压缩的页，也可能只有压缩页，也可能两者都没有，淘汰策略采用的是LRU。

几个关于压缩的调优参数

innodb_compression_level：默认值为6，可选值0-9，数值越大表示压缩程度越大，消耗的CPU也越多

innodb_compression_failure_threshold_pct：默认为5，可取值0-100，表示更新一个压缩表时，指定一个压缩失败的临界值。当超过这个临界值，mysql会为每个压缩页添加额外的空间来避免再次压缩失败。值为0表示禁用监控压缩效率，改为动态调整。

innodb_compression_pad_pct_max：重新压缩时为每个压缩页额外分配的空间比例，默认50，可取值0-75.这个参数值只有当参数innodb_compression_failure_threshold_pct非0时才生效