MySQL InnoDB表压缩过程

最新推荐文章于 2023-05-05 10:28:18 发布

糯玉米珍珠奶茶

最新推荐文章于 2023-05-05 10:28:18 发布

阅读量460

点赞数

分类专栏： mysql 文章标签： mysql innodb 运维

本文链接：https://blog.csdn.net/wnll_08/article/details/116491328

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL InnoDB表压缩过程

起因
简单介绍下MyISAM表压缩
InnoDB表压缩

起因

服务器磁盘空间不足，且数据库处于冷表状态，即及其少量的数据变动，和较多的查询过程。因此考虑压缩表来释放空间。其中，MySQL版本为5.6，还是比较常用的老版本了。

简单介绍下MyISAM表压缩

因为MyISAM表压缩不符合要求，所以以下仅做简单介绍，并没有进行实际操作，无法给出更具体的说明，仅供参考~

应用场景： 类似数据仓库
缺点： 表压缩后只有只读权限；不支持分区表
使用工具： myisampack、myisamchk
压缩率： 40%~70%

#进行压缩时，需确保表是在非使用状态下进行，避免数据丢失等风险
#打开指定库对应的目录下进行操作
shell> ls -l station.*
-rw-rw-r--   1 jones    my         994128 Apr 17 19:00 station.MYD
-rw-rw-r--   1 jones    my          53248 Apr 17 19:00 station.MYI
-rw-rw-r--   1 jones    my           5767 Apr 17 19:00 station.frm

shell> myisamchk -dvv station  #显示表详细信息

MyISAM file:     station
Isam-version:  2
Creation time: 1996-03-13 10:08:58
Recover time:  1997-02-02  3:06:43
Data records:              1192  Deleted blocks:              0
Datafile parts:            1192  Deleted data:                0
Datafile pointer (bytes):     2  Keyfile pointer (bytes):     2
Max datafile length:   54657023  Max keyfile length:   33554431
Recordlength:               834
Record format: Fixed length

table description:
Key Start Len Index   Type                 Root  Blocksize    Rec/key
1   2     4   unique  unsigned long        1024       1024          1
2   32    30  multip. text                10240       1024          1

............

shell> myisampack station.MYI	#开始压缩表
Compressing station.MYI: (1192 records)
- Calculating statistics

normal:     20  empty-space:   16  empty-zero:     12  empty-fill:  11
pre-space:   0  end-space:     12  table-lookups:   5  zero:         7
Original trees:  57  After join: 17
- Compressing file
87.14%
Remember to run myisamchk -rq on compressed tables

shell> myisamchk -rq station	#重建表索引
- check record delete-chain
- recovering (with sort) MyISAM-table 'station'
Data records: 1192
- Fixing index 1
- Fixing index 2

shell> mysqladmin -uroot flush-tables	#刷新表

shell> ls -l station.*
-rw-rw-r--   1 jones    my         127874 Apr 17 19:00 station.MYD
-rw-rw-r--   1 jones    my          55296 Apr 17 19:04 station.MYI
-rw-rw-r--   1 jones    my           5767 Apr 17 19:00 station.frm

shell> myisamchk -dvv station

MyISAM file:     station
Isam-version:  2
Creation time: 1996-03-13 10:08:58
Recover time:  1997-04-17 19:04:26
Data records:               1192  Deleted blocks:              0
Datafile parts:             1192  Deleted data:                0
Datafile pointer (bytes):      3  Keyfile pointer (bytes):     1
Max datafile length:    16777215  Max keyfile length:     131071
Recordlength:                834
Record format: Compressed

table description:
Key Start Len Index   Type                 Root  Blocksize    Rec/key
1   2     4   unique  unsigned long       10240       1024          1
2   32    30  multip. text                54272       1024          1

..........

InnoDB表压缩

InnoDB表，压缩后仍然可以进行增删改查操作，即操作同未压缩表一样。当然，还是存在一些缺点的，下面我们就来看一下InnoDB表压缩吧~

应用场景

内存较大，硬盘是SSD设备：压缩表可以实现较小的数据库大小，减少的I/O和改进的吞吐量。
服务器空间不足，数据必须存在，但很少操作：使用压缩表时将占用更多的CPU、内存和操作时间。

重点： InnoDB表压缩对字符串类型更有效（BLOB， VARCHAR，TEXT）；如果是数字类型或二进制类型的，就不用压缩啦（当然我木有实验过哈，有时间大家可以测试下）

前提

限制

InnoDB表压缩仅支持存储在自己单独的表空间中的表和索引，因此不支持系统表压缩；且5.1之前的版本无法处理压缩表，所以需要考虑文件格式的兼容性问题。
表进行压缩或者解压时，是需要时间的，而时间取决于表数据类型和大小。一个简单的参考时间：压缩前149G的ibd文件，压缩时间为597m16.410s（约10个小时，天哪），压缩后大小变成62G，压缩比率大约为41.61%（还是很可观的）。
表在压缩过程中，磁盘空间是会变大的，大概是原表的一倍多一点，所以如果是空间相当不足的情况下，为避免压缩失败，建议先从小表开始压缩。

参数配置

#根据限制要求，在进行压缩之前请检查并更正以下两个参数
#mysql无需重启，可动态修改，记得同步到 my.cnf 配置表中哟 ~
mysql> SET GLOBAL innodb_file_per_table=1;	#开启每表一个表空间文件
mysql> SET GLOBAL innodb_file_format=Barracuda; #支持压缩的文件格式

压缩命令特征

KEY_BLOCK_SIZE用于指定页面大小，默认为 innodb_page_size 值的一半；
如果指定ROW_FORMAT=COMPRESSED，则可以忽略KEY_BLOCK_SIZE，采用默认值；
如果指定KEY_BLOCK_SIZE，则可以省略 ROW_FORMAT=COMPRESSED，压缩会自动启用。
InnoDB表默认页面大小为16KB，因此如果KEY_BLOCK_SIZE=16或者大于16，对压缩来说，通常是没有多大效果的。

查看压缩表效果

#INNODB_CMP表可以简单查看压缩表的效果，compress_ops表示压缩总量，compress_ops_ok表示压缩成功总量
#查看compress_ops_ok /compress_ops 比率，即可查看压缩效果，如果压缩比率较小，则表示压缩没有达到预期效果，一般失败在1%~2%是可以接受的。
mysql> SELECT * FROM INFORMATION_SCHEMA.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 |            0 |               0 |             0 |              0 |               0 |
|      8192 |     15639582 |        15637354 |          2730 |         120421 |              10 |
|     16384 |            0 |               0 |             0 |              0 |               0 |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
5 rows in set (0.05 sec)

#！！！INNODB_CMP_PER_INDEX应在测试开发环境中进行，因其统计信息所付出的代价较为昂贵
#INNODB_CMP_PER_INDEX表统计需开启innodb_cmp_per_index_enabled功能
#在下没有采用该统计方式，所以无法给出该表统计到底需要耗费多少服务器性能哈，欢迎尝试后告知 ~
mysql> SHOW VARIABLES LIKE 'innodb_cmp_per_index_enabled';
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX;

#其它可查看压缩效果的系统表
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM;
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP_RESET;
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM_RESET;

压缩与解压

InnoDB表压缩可以在创建时指定或者对已有表进行更正：

#进行压缩、解压时，需确保表是在非使用状态下进行，避免数据丢失等风险
#压缩
CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
#解压，即更正为原表的格式，如COMPACT
ALTER TABLE t1 ROW_FORMAT=COMPACT;

根据MySQL官方手册原文，建议采用8KB作为KEY_BLOCK_SIZE的赋值。

Typically, you set the compressed page size to 8K or 4K bytes. Given that the maximum row size for an InnoDB table is around 8K, KEY_BLOCK_SIZE=8 is usually a safe choice.

影响参数

针对OLTP（On-Line Transaction Processing联机事务处理过程）工作负载的压缩表进行优化操作，以下五个参数将会影响其写密集型操作的性能和可伸缩性。

mysql> SHOW VARIABLES LIKE '%innodb%compress%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| innodb_compression_failure_threshold_pct | 5     |
| innodb_compression_level                 | 6     |
| innodb_compression_pad_pct_max           | 50    |
| innodb_log_compressed_pages              | ON    |
+------------------------------------------+-------+
4 rows in set (0.03 sec)

mysql> SHOW VARIABLES LIKE '%innodb_buffer_pool_size%';
+-------------------------+-------------+
| Variable_name           | Value       |
+-------------------------+-------------+
| innodb_buffer_pool_size | 12582912000 |
+-------------------------+-------------+
1 row in set (0.09 sec)

名词解析

innodb_compression_failure_threshold_pct：可接受的压缩失败比率

innodb_compression_level：压缩水平

innodb_compression_pad_pct_max：指定可以在每个压缩页面内保留为可用空间的最大百分比，仅在压缩失败时，会用到可用空间，因为拆分压缩页也是很昂贵的操作。

innodb_log_compressed_pages：指定是否将重新压缩的页面图像写入redo日志，开启则在对压缩数据进行更改时，可能会发生重新压缩。默认是开启的。这是为了避免 zlib 在恢复过程中使用不同版本的压缩算法时可能发生的损坏。如果可以确定 zlib 版本不会变动，则可禁用该参数，以减少工作负载。

innodb_buffer_pool_size：缓冲池大小调整，也对工作负载有影响，一般在专用数据库的环境下该值设置为物理内存的80%。

压缩过程的影响

因为没有特地记录压缩过程的CPU变化，内存变化，磁盘空间变化等，所以只能简单的说明下，在下比较懒，估计不会再进行测试了，所以仅供参考。
压缩过程中：

磁盘空间会变大，大概是压缩表原来大小的一倍，需要提前评估预留时间；
CPU负载明显变高，不过在可控范围内（服务器的CPU内核：8核）
内存变化不大，但也有波动，在可空范围之内（服务器的物理内存：16G）

以上经过了MySQL官方手册指导和实操，遗憾没有进行更科学的测试阶段，但是也够用啦~
记录一下过程，方便自己，方便大家。

糯玉米珍珠奶茶

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL InnoDB表压缩过程

MySQL InnoDB表压缩过程起因简单介绍下MyISAM表压缩InnoDB表压缩应用场景前提压缩与解压影响参数压缩过程的影响起因服务器磁盘空间不足，且数据库处于冷表状态，即及其少量的数据变动，和较多的查询过程。因此考虑压缩表来释放空间。其中，MySQL版本为5.6，还是比较常用的老版本了。简单介绍下MyISAM表压缩因为MyISAM表压缩不符合要求，所以以下仅做简单介绍，并没有进行实际操作，无法给出更具体的说明，仅供参考~应用场景：类似数据仓库缺点：表压缩后只有只读权限；不支持分区表
复制链接

扫一扫