【ACDU翻译】MySQL 15.9.1.3 调整 InnoDB 表的压缩

原创由迪 2022-05-19

389

15.9.1.3 调整 InnoDB 表的压缩

大多数情况下， InnoDB 数据存储和压缩中描述的内部优化可确保系统在压缩数据时运行良好。但是，由于压缩效率取决于数据的性质，因此您可以做出影响压缩表性能的决策：

要压缩哪些表。
使用什么压缩页面大小。
是否根据运行时性能特征调整缓冲池的大小，例如系统压缩和解压缩数据所花费的时间。工作负载更像是数据仓库（主要是查询）还是 OLTP系统（查询和DML的混合）。
如果系统对压缩表执行 DML 操作，并且数据的分布方式会导致运行时出现代价高昂的压缩失败，您可能需要调整其他高级配置选项。

使用本节中的指南来帮助做出这些架构和配置选择。当您准备好进行长期测试并将压缩表投入生产时，请参阅第 15.9.1.4 节，“在运行时监控 InnoDB 表压缩”以了解在实际条件下验证这些选择的有效性的方法。

何时使用压缩

通常，压缩在包含合理数量的字符串列并且数据读取频率远高于写入频率的表上效果最佳。因为没有可靠的方法来预测压缩是否有益于特定情况，所以始终使用在代表性配置上运行的特定工作负载和数据集进行测试。在决定要压缩哪些表时，请考虑以下因素。

数据特征和压缩

压缩效率在减小数据文件大小方面的一个关键决定因素是数据本身的性质。回想一下，压缩是通过识别数据块中重复的字节串来工作的。完全随机的数据是最坏的情况。典型数据通常具有重复值，因此可以有效压缩。字符串通常可以很好地压缩，无论是在CHAR、或列VARCHAR中定义的。另一方面，主要包含二进制数据（整数或浮点数）或先前压缩的数据（例如JPEG或PNG 图像）的表通常可能无法很好地、显着地或根本无法压缩。 TEXT``BLOB

您选择是否为每个 InnoDB 表打开压缩。一个表和它的所有索引都使用相同的（压缩的）页面大小。可能是包含表所有列的数据的主键（聚集）索引比二级索引更有效地压缩。对于那些有长行的情况，使用压缩可能会导致长列值被 “离页”存储，如 DYNAMIC Row Format中所述. 那些溢出页面可能会很好地压缩。考虑到这些因素，对于许多应用程序，某些表的压缩效率比其他表高，您可能会发现您的工作负载仅在压缩表的子集时表现最佳。

要确定是否压缩特定表，请进行实验。通过对未压缩表的.ibd 文件gzip副本使用实现 LZ77 压缩（例如 WinZip）的实用程序，您可以粗略估计压缩数据的效率。与基于文件的压缩工具相比，MySQL 压缩表的压缩率可能更低，因为 MySQL 会根据页面大小以块的形式压缩数据, 默认为 16KB。除了用户数据之外，页面格式还包括一些未压缩的内部系统数据。基于文件的压缩实用程序可以检查更大的数据块，因此在一个大文件中找到的重复字符串可能比 MySQL 在单个页面中找到的要多。

在特定表上测试压缩的另一种方法是将未压缩表中的一些数据复制到file-per-table 表空间中类似的压缩表（具有所有相同的索引），并查看生成 .ibd文件的大小。例如：

USE test;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL autocommit=0;

-- Create an uncompressed table with a million or two rows.
CREATE TABLE big_table AS SELECT * FROM information_schema.columns;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
COMMIT;
ALTER TABLE big_table ADD id int unsigned NOT NULL PRIMARY KEY auto_increment;

SHOW CREATE TABLE big_table\G

select count(id) from big_table;

-- Check how much space is needed for the uncompressed table.
\! ls -l data/test/big_table.ibd

CREATE TABLE key_block_size_4 LIKE big_table;
ALTER TABLE key_block_size_4 key_block_size=4 row_format=compressed;

INSERT INTO key_block_size_4 SELECT * FROM big_table;
commit;

-- Check how much space is needed for a compressed table
-- with particular compression settings.
\! ls -l data/test/key_block_size_4.ibd
复制

该实验产生了以下数字，当然，根据您的表结构和数据，这些数字可能会有很大差异：

-rw-rw----  1 cirrus  staff  310378496 Jan  9 13:44 data/test/big_table.ibd
-rw-rw----  1 cirrus  staff  83886080 Jan  9 15:10 data/test/key_block_size_4.ibd
复制

要查看压缩对于您的特定工作负载是否有效：

对于简单的测试，使用没有其他压缩表的 MySQL 实例并针对该 INFORMATION_SCHEMA.INNODB_CMP 表运行查询。
对于涉及具有多个压缩表的工作负载的更详细的测试，请针对该 INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX 表运行查询。由于 INNODB_CMP_PER_INDEX收集表中的统计信息的成本很高，因此您必须 innodb_cmp_per_index_enabled 在查询该表之前启用配置选项，并且您可能会将此类测试限制在开发服务器或非关键副本服务器上。
针对您正在测试的压缩表运行一些典型的 SQL 语句。
INFORMATION_SCHEMA.INNODB_CMP 通过查询or INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX 表并与比较COMPRESS_OPS来检查成功的压缩操作与整体压缩操作的比率 COMPRESS_OPS_OK。
如果压缩操作成功完成的百分比很高，则该表可能是一个很好的压缩候选者。
如果压缩失败的比例很高，您可以按照第 15.9.1.6 节“OLTP 工作负载的压缩”innodb_compression_level中所述调整、 innodb_compression_failure_threshold_pct和 innodb_compression_pad_pct_max 选项，并尝试进一步测试。

数据库压缩与应用程序压缩

决定是在应用程序中还是在表中压缩数据；不要对同一数据使用两种类型的压缩。当您在应用程序中压缩数据并将结果存储在压缩表中时，极不可能节省额外的空间，双重压缩只会浪费 CPU 周期。

在数据库中压缩

启用后，MySQL 表压缩是自动的，并适用于所有列和索引值。这些列仍然可以使用诸如的运算符进行测试，LIKE即使索引值被压缩，排序操作仍然可以使用索引。由于索引通常占数据库总大小的很大一部分，因此压缩可以显着节省存储、I/O 或处理器时间。压缩和解压缩操作发生在数据库服务器上，这可能是一个强大的系统，其大小可以处理预期的负载。

在应用程序中压缩

如果您在应用程序中压缩诸如文本之类的数据，则在将其插入数据库之前，您可能会通过压缩某些列而不是其他列来节省无法很好压缩的数据的开销。这种方法在客户机而不是数据库服务器上使用 CPU 周期进行压缩和解压缩，这可能适用于具有许多客户机的分布式应用程序，或者客户机具有空闲 CPU 周期的情况。

混合方法

当然，可以将这些方法结合起来。对于某些应用程序，使用一些压缩表和一些未压缩表可能是合适的。最好从外部压缩一些数据（并将其存储在未压缩的表中）并允许 MySQL 压缩（部分）应用程序中的其他表。与往常一样，前期设计和实际测试对于做出正确决策很有价值。

工作负载特征和压缩

除了选择要压缩的表（和页面大小）之外，工作负载是性能的另一个关键决定因素。如果应用程序以读取而不是更新为主，那么在索引页用完MySQL 为压缩数据维护的每页“修改日志”空间后，需要重新组织和重新压缩的页面就会更少。如果更新主要更改非索引列或包含BLOBs 或大字符串的列，这些列恰好存储在“页外”，则压缩的开销可能是可以接受的。如果对表的唯一更改是INSERT使用单调递增的主键，二级索引很少，几乎不需要重新组织和重新压缩索引页。由于 MySQL 可以通过修改未压缩的数据来“删除标记”并“就地”删除压缩页面上的行，DELETE因此对表的操作相对高效。

对于某些环境，加载数据所需的时间可能与运行时检索一样重要。特别是在数据仓库环境中，许多表可能是只读的或只读的。在这些情况下，为增加加载时间而付出压缩的代价可能是可接受的，也可能是不可接受的，除非在减少磁盘读取或存储成本方面的节省是显着的。

从根本上说，当 CPU 时间可用于压缩和解压缩数据时，压缩效果最好。因此，如果您的工作负载受 I/O 限制，而不是 CPU 限制，您可能会发现压缩可以提高整体性能。当您使用不同的压缩配置测试应用程序性能时，请在类似于生产系统的计划配置的平台上进行测试。

配置特征和压缩

从磁盘读取和写入数据库页面是系统性能最慢的方面。压缩尝试通过使用 CPU 时间来压缩和解压缩数据来减少 I/O，并且在 I/O 与处理器周期相比是相对稀缺的资源时最有效。

在具有快速多核 CPU 的多用户环境中运行时尤其如此。当压缩表的页面在内存中时，MySQL 经常在缓冲池中使用额外的内存，通常为 16KB，用于页面的未压缩副本。自适应 LRU 算法尝试平衡压缩和未压缩页面之间的内存使用，以考虑工作负载是以 I/O 密集型还是 CPU 密集型方式运行。尽管如此，在使用压缩表时，具有更多专用于缓冲池的内存的配置往往比内存受到高度限制的配置运行得更好。