by ·1 Comment

从MySQL 5.7版本开始，MySQL不仅支持原有的压缩表格式(Table Compression)，还支持一种称为透明页压缩的特性(Transparent Page Compression)。通过阅资料和源码，我对这个特性有了一定的了解。以下我将从它的使用方法、实现原理等方面对它进行简单分析，并同压缩表格式进行一些对比。 1. 开启方法 官方文档对于透明页压缩的特性的说明仅仅一页，主要说明了它的使用方法，我也对这页官方文档进行过翻译，详见：InnoDB Page Compression MySQL文档翻译：InnoDB透明页压缩 对于透明页压缩的使用方法，和压缩表格式相同的是，都是通过CREATE TABLE或者ALTER TABLE语法对于一个表使用的。不同点是压缩表格式使用ROW_FORMAT=COMPRESSED这个字段，而透明页压缩使用COMPRESSION=”zlib”、COMPRESSION=”lz4″或者COMPRESSION=”None”这种字段。分别用两种压缩形式创建一个表的例子： ## 创建一个表，启用压缩表格式，块的大小为8K CREATE TABLE t1(c1 INT) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; # 创建一个表，启用透明页压缩，压缩算法为LZ4 CREATE TABLE t1(c1 INT) COMPRESSION=”zlib”(“lz4”…); 另外要注意：开启透明页压缩需要文件系统和操作系统支持 Sparse File 和 Hole Punching 特性，并且需要开启InnoDB的file-per-table选项。更详细的使用方法见上边的那篇翻译。 2. 原理简述 2.1. 先说说以前压缩表格式

by ·0 Comments

原文： [MySQL 5.7 Manual 15.9.1.6 Compression for OLTP Workloads] https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-compression-oltp.html 传统上，InnoDB压缩功能主要用于只读或大部分读取的工作负载，例如在数据仓库配置中。快速但相对较小和昂贵的SSD存储设备的兴起使得压缩对于OLTP工作负载也越发具有吸引力的：高流量的交互式网站可以通过使用压缩表同时使用具有执行频繁INSERT，UPDATE和DELETE操作的应用程序来减少其存储需求和每秒I/O操作数(IOPS)。 MySQL 5.6中引入的配置选项可让您调节压缩对特定MySQL实例的工作参数，其对于写入密集型操作的性能和可扩展性是很重要的： innodb_compression_level： 允许你向上或向下调整压缩程度，更高的值可以让你节省更多空间存储更多数据，同时在压缩时牺牲更多CPU周期，比较低的值可以让你在存储空间不重要时或者在数据不太适合被压缩时减小CPU开销。 innodb_compression_failure_threshold_pct： 指定了一个进行页压缩失败的百分比阈值，当它被超过时，MySQL开始在每个新的压缩页中增加一些额外的空闲的保留空间(padding)，动态的调整这个空余空间的大小，padding的上限是由innodb_compression_pad_pct_max指定的，它是页面大小的一个百分比。 innodb_compression_pad_pct_max：允许你调整每个页面中用于直接保存更改和插入数据的空闲保留空间(padding)的最大值(这样就无需每次更改和插入都再次压缩整个页面)。 值越高，就可以记录更多的更改(???)，而无需重新压缩页面。MySQL运行时,只有当需要昂贵分割操作的”压缩失败”达到上一个参数所指定的百分比时，才会为每个压缩表中的页面增加一个大小可变的空余空间(padding)。 innodb_log_compressed_pages：默认情况下，此选项被启用，以防止在恢复期间使用不同版本的zlib压缩算法时可能会发生损坏。 如果你确定zlib版本不会更改，请禁用innodb_log_compressed_pages以减少为修改压缩数据而生成的重做日志。 由于开启压缩时，内存中可能同时保留存在数据的压缩版本和未压缩版本，所以在OLTP工作负载下进行压缩时，innodb_buffer_pool_size配置选项的值可能会需要增加。

by ·0 Comments

原文： [MySQL 5.7 Manual 15.9.1.5 How Compression Works for InnoDB Tables] https://dev.mysql.com/doc/refman/5.7/en/innodb-compression-internals.html 本节介绍关于InnoDB表的压缩的一些内部实现细节。 这里介绍的信息可能有助于性能调优，但对压缩功能的基本使用并不必要。 压缩算法 某些操作系统在文件系统级别实现压缩。文件通常被分为固定大小的块，这些块被压缩后大小不固定，很容易导致碎片化。当块内的内容被修改时，在被写入磁盘之前，整个块都要被重新压缩。这些特性使得这种压缩技术不适合在update密集型当数据库系统中使用。 MySQL利用了广为人知的zlib库(基于LZ77压缩算法)来实现压缩功能。这种压缩算法在CPU利用率和减小数据大小方面都是成熟，稳健和高效的。该算法是“无损”的，从而可以始终从压缩形式重构原始的未压缩数据。 LZ77压缩通过查找要压缩的数据中重复的数据序列来实现压缩的目的。数据的具体形式决定了它的压缩程度，典型的用户数据通常会压缩50％或更多。 与一个应用程序执行的压缩或某些其他数据库管理系统的压缩功能不同，InnoDB压缩既适用于用户数据又适用于索引数据。在许多情况下，索引可以构成总数据库大小的40-50％以上，所以这带来的差异是显著的。当压缩对于某个数据集工作良好时，InnoDB数据文件（file-per-table的表空间或一般的表空间.idb文件）的大小是未压缩大小的25％到50％或更小。根据工作负载，压缩后的较小的数据库反过来很可能导致I/O数的减少，并以适度增加CPU占用率为成本增大了吞吐量。你可以通过修改innodb_compression_level配置选项来调整压缩级别和CPU开销之间的平衡。 InnoDB数据存储和压缩 InnoDB表中的所有用户数据都存储在包含B树索引（聚簇索引, clustered index）的页面中。 在其他一些数据库系统中，这种类型的索引称为“索引组织表”。 索引节点中的每一行都包含（用户指定的或系统生成的）主键和表的所有其他列的值。 InnoDB表中的辅助索引(secondary indexes)也是B树，包含键值对：索引键和指向聚簇索引中的某个行的指针。 指针实际上是表的主键的值，在需要除索引键和主键之外的列时，则需要查找到该值用来访问聚簇索引，进而访问到需要的列。 辅助索引记录必须始终可以放在单个B树页面。

by ·0 Comments

原文： [MySQL 5.7 Reference Manual 15.9.2 InnoDB Page Compression] https://dev.mysql.com/doc/refman/5.7/en/innodb-page-compression.html InnoDB支持file-per-table表空间中的表的页级压缩。 此功能称为透明页压缩。 通过使用CREATE TABLE或ALTER TABLE指定”COMPRESSION”这个表属性来启用页面压缩。 支持的压缩算法包括Zlib和LZ4。 支持的平台 页面压缩需要稀疏文件(sparse file)和打洞(hole punching)支持。 页面压缩在使用NTFS的Windows上被支持，并且在以下MySQL支持的Linux平台发行版的内核级别提供了打孔支持： RHEL 7 and derived distributions that use kernel version 3.10.0-123 or higher OEL 5.10 (UEK2) kernel version 2.6.39 or higher OEL 6.5 (UEK3) kernel version 3.8.13 or higher OEL 7.0 kernel version 3.8.13 or […]

by ·0 Comments

原文 [MySQL Internals Manual 22.2.1 High-Altitude View] https://dev.mysql.com/doc/internals/en/innodb-page-overview.html 0. 概述 InnoDB的页有7个部分： +—————————-+ | Fil Header | +—————————-+ | Page Header | +—————————-+ | Infimum + Supremum Records | +—————————-+ | User Records | +—————————-+ | Free Space | +—————————-+ | Page Directory | +—————————-+ | Fil Trailer | +—————————-+ 你可以发现，每个页有头尾(header/trailer)对。靠内的头尾对(Page Header和Page Directory)主要是由page程序组的所关注的，而外部的头尾对(Fil Header和Fil Trailer)主要是fil程序组的关注 。 […]

by ·0 Comments

原文: MySQL 5.7 Reference Manual — 9.12.2 Optimizing Disk I/O 本节将介绍当你决定更多更快的存储硬件设备投入到数据库服务器时，应该如何配置它们。 软件配置上优化InnoDB以提高I / O性能的信息，请参见第9.5.8节“优化InnoDB磁盘I/O”。 磁盘寻道是一个巨大的性能瓶颈，当数据量剧增到使得缓存命中率减小时，这个问题会更为明显。对于或多或少会进行随机访问的大型的数据库，可以确定对于读至少需要一次磁盘寻道，而对于写需要好几次磁盘寻道。要最小化这个问题，应该用磁盘寻道时间较小的磁盘。 增加可以用磁盘数，这样就可以通过符号链接将不同文件存储到不同磁盘，或者可以通过磁盘分条，来降低寻道开销。 使用符号链接: 这意味着，对于MyISAM表，可以将索引文件和数据文件从它们在数据目录中的通常位置符号链接到另一个磁盘（也可能是条带化的）。假设磁盘不用于其他通途，那么这会使得查找和读取时间减小。详情请参见第9.12.3节“使用符号链接”。 条带化: 分条意味着有多个磁盘使用的情况下，将第一个数据块放在第一个磁盘上，第二个块在第二个磁盘上，第N个块在（N对磁盘数取模）磁盘上，这意味着如果一般的数据大小小于条带大小（或者完全对齐），那么你将获得更好的性能。分条的性能非常依赖于操作系统和条带大小，因此应该设置不同的条带大小分别进行基准测试。详情请参见第9.13.2节“使用自己的基准”。 条带化的速度差异十分依赖于参数配置。根据如何设置条带化参数和硬盘数量，你可能会得到数量级的差异。对于优化随机访问还是顺序访问，你必须做出选择。 为了可靠性，你可能需要使用RAID 0+1（条带加镜像），但在这种情况下，需要2×N个磁盘来N个磁盘的数据。如果你有足够的钱，这可能是最好的选择，而且，你可能还需要额外投资一些卷管理软件来有效地管理它们。 一个好的选择是根据数据类型的关键程度来改变RAID级别。 例如，可以再生的一般重要数据可以放在RAID 0上，但将真正重要的数据（如主机信息和日志）存储在RAID 0+1或 RAID N磁盘上。 如果写入较多，则由于更新奇偶校验位需要时间，RAID N可能不太合适。 你也可以设置数据库所涉及的文件系统的参数：如果您不需要知道上次访问文件的时间（这在数据库服务器上通常用途不大），则可以使用-o noatime选项来挂载文件系统。 这会跳过对文件系统上inode的最后访问时间的更新，从而避免某些磁盘寻道。在许多操作系统上，您可以通过使用-o async选项挂载文件系统以将其设为异步更新。 如果您的计算机相当稳定，这应该给你更好的性能，而不会牺牲太多的可靠性。 （在Linux上，此标志默认是打开的） 对于使用NFS的MySQL，应该注意的潜在问题如下： 放在NFS卷上的MySQL数据和日志文件变成锁定状态并且无法使用：锁定问题可能发生在多个MySQL实例访问相同数据目录或MySQL由于断电等而不正常关闭的情况下。 NFS版本4解决了咨询或租赁导致的锁定问题。 但是，MySQL实例之间共享数据目录是不被推荐的。 由于收到无序的消息或丢失的网络流量，引入了数据不一致： 为了避免此问题，请在使用hard和intr挂载选项的情况下使用TCP。 最大文件大小限制： NFS版本2客户端能访问的最大文件只有2GB（带符号的32位偏移量）。 NFS V3客户端支持更大的文件（最多64位偏移量）。 最大支持的文件大小还取决于NFS服务器的本地文件系统。 在专业SAN环境或其他存储系统中使用NFS比在这种环境之外使用NFS更可靠。 但是，SAN环境中的NFS可能比直接连接或总线连接的非旋转存储更慢。 如果使用NFS，建议用NFS V4或更高版本。在部署到生产环境之前，也应彻底测试NFS设置。

by ·1 Comment

原文：8.5.8 Optimizing InnoDB Disk I/O 这页感觉比较重要，翻译一下。。 如果你遵循了数据库设计和SQL操作调优的最佳实践，但是数据库仍然由于磁盘I/O负载过重而运行慢，请考虑这些磁盘I/O优化方法。如果Unix top工具或Windows任务管理器显示你的工作负载的CPU使用率百分比小于70％，那么你的数据库系统瓶颈在于磁盘。 增加缓冲池(buffer pool)的大小 当表中数据被缓存到InnoDB的buffer pool中时，对它的查询请求就不会引起任何磁盘I/O请求。使用innodb_buffer_pool_size选项就可以指定buffer pool的大小。buffer pool这块内存很重要，所以通常建议将innodb_buffer_pool_size配置为系统内存的50％到75％。 有关更多信息，请参见第9.12.4.1节“MySQL如何使用内存”。 调整刷新方法 在某些版本的Linux或Unix中，使用Unix fsync()调用(InnoDB默认用它)或其他类似调用将文件刷入磁盘的速度是非常慢的。如果数据库的写入性能有问题，请在执行基准测试时将innodb_flush_method参数设置为O_DSYNC。 noop和deadline两种I/O调度程序的选择 InnoDB在Linux上使用异步I/O子系统(naive AIO)来执行数据文件页的预读操作和写请求操作。此行为由innodb_use_native_aio选项进行控制，默认情况下是启用的。 对于naive AIO来说，I/O调度程序(I/O Scheduler)的类型对I/O性能有很大的影响。一般来说，建议使用noop和deadline这两种类型的I/O调度程序。这里就应该进行测试以决定当前的环境和工作负载适合使用哪一个I/O调度器。有关更多信息，请参见第15.6.8节“在Linux上使用异步I / O”。 x86_64架构的Solaris 10上使用direct I/O