MySQL数据库性能优化-硬件和参数配置提升I/O（二）

蔚落 2022-06-03 05:53 286阅读 0赞

总结之前的内容，MySQL数据库性能优化的途径有以下几个方式

1.  **提高硬件方面的配置，比如SSD固态硬盘等。**
2.  **选择合适的存储引擎，一般使用Innodb（支持事务处理等优势）。**

来学一下一下第二点知识：MySQL关于硬件和参数配置提升I/O性能。

## 3-3、突破I/O性能 ##

为了解决上一节中提到的I/O性能问题，本文这里基于之前介绍的块存储方案的知识，列出这个问题的几种解决方案。除了根据I/O吞吐量要求对MySQL数据库特别是InnoDB引擎的配置参数进行更改以外，本文提到的硬件层解决方法所需要花费的资金和能够得到的I/O性能和扩展能力基本上成正比。

### 3-3-1、对MySQL中的I/O相关参数进行调整 ###

上一节我们已经对InnoDB数据库引擎（以下简称InnoDB引擎）进行事务操作时的I/O过程进行了简单说明，主要介绍了Log flush和Pages flush两个过程。如果我们需要挖掘正式生产环境上MySQL数据库服务的性能潜力，那么对MySQL数据库服务中的默认参数进行更改就是必须要做的事情。在进行配置修改之前我们先来看看如何查看当前MySQL数据库特别是InnoDB引擎的工作状态：

# 通过执行以下命令，我们可以查看当前InnoDB引擎的工作状态
    show engine innodb status;
     
      
       1
       2

执行后可以得到类似如下的执行结果（已省去一部分与本文没有涉及到的知识点所相关的状态描述信息）：

...... # master thread是InnoDB引擎中优先级最高的线程 # 这个线程存在的主要作用是控制InnoDB中各种I/O操作的策略，驱动其它read/write thread # 并根据当前的InnoDB的InnoDB Log Buffer、InnoDB Buffer Pool区域状态和配置参数决定处理逻辑 -----------------
    BACKGROUND THREAD -----------------
    srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 79085 srv_idle srv_master_thread log flush and writes: 79085 ...... # 这些是负责进行磁盘读写的I/O线程，日志和数据Page的读写操作最终都是靠它们完成
    # 它们的数量可以通过innodb_write_io_threads参数和innodb_read_io_threads参数进行设置 --------
    FILE I/O --------
    I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
    I/O thread 1 state: waiting for completed aio requests (log thread)
    I/O thread 2 state: waiting for completed aio requests (read thread)
    ...... I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (write thread) ......
    I/O thread 13 state: waiting for completed aio requests (write thread)
    Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
     ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    Pending flushes (fsync) log: 0; buffer pool: 0
    524 OS file reads, 5 OS file writes, 5 OS fsyncs
    0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
    ...... # 这些信息反馈了InnoDB引擎中的日志标识 --- LOG --- # 当前生成的最大日志LSN值 Log sequence number 9902833 # 当前已完成日志信息向磁盘同步的最大日志LSN值 Log flushed up to 9902833 # 当前已完成数据信息向磁盘同步的最大日志LSN值 Pages flushed up to 9902833 # 当前已检查点同步的最大日志LSN值。小于和等于这是LSN值的日志在异常重启后都无需进行“重做”。 Last checkpoint at 9902833 # 当前InnoDB中待写的日志操作、待写的检查点操作 0 pending log writes, 0 pending chkp writes 8 log i/o's done, 0.00 log i/o's/second # 这些信息反馈了InnoDB引擎中Buffer Pool内存空间的状态 ----------------------
    BUFFER POOL AND MEMORY ----------------------
    Total memory allocated 4395630592; in additional pool allocated 0
    Dictionary memory allocated 99235
    # 当前Buffer Pool的大小
    Buffer pool size   262136
    # 当前Buffer Pool的可用大小
    Free buffers       261619
    # Page数量
    Database pages     506
    Old database pages 0
    Modified db pages  0
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 0, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 506, created 0, written 1
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    No buffer pool page gets since the last printout
    # 状态信息的这行反馈了Page的预读信息，以及预读信息的未命中剔除情况
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 506, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ......
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15
       16
       17
       18
       19
       20
       21
       22
       23
       24
       25
       26
       27
       28
       29
       30
       31
       32
       33
       34
       35
       36
       37
       38
       39
       40
       41
       42
       43
       44
       45
       46
       47
       48
       49
       50
       51
       52
       53
       54
       55
       56
       57
       58
       59
       60
       61
       62
       63
       64
       65
       66
       67
       68
       69
       70
       71
       72
       73
       74

对于上一小节提到的重要参数信息，可以采用如下的设定规则：

innodb\_log\_file\_size：单个日志文件的大小不宜过小，例如设置为500MB。由于InnoBD引擎对日志文件采用顺序写的操作方式，所以不必担心日志文件的操作消耗比数据文件操作更多的性能。

innodb\_log\_files\_in\_group：该参数控制了文件组中日志文件的总数。设置为2-5的范围都不会有太大影响。更重要的是读者应该清楚innodb\_log\_file\_size \* innodb\_log\_files\_in\_group就是InnoDB引擎在磁盘上可用日志空间的总大小。

innodb\_log\_buffer\_size：这个参数决定了InnoDB引擎可使用的日志内存空间。只要没有类似插入blob类型数据的操作（也不建议有这样的操作），这个内存空间都不需要设置得太大。5MB-10MB是一个推荐的设置值，不过这个参数还是要和innodb\_flush\_log\_at\_trx\_commit参数配合使用。

innodb\_flush\_log\_at\_trx\_commit：该参数可以说是InnoDB引擎日志操作策略部分最重要的设置参数之一。在上一篇文章（《[架构设计：系统存储（5）——MySQL数据库性能优化（1）][5_MySQL_1]》）的3-1节中，我们已经介绍过该参数的三个值和它们代表的策略意义。如果您将innodb\_flush\_log\_at\_trx\_commit设置为0，代表着InnoDB引擎将会按照1秒钟的周期进行日志从内存到磁盘的同步。这时innodb\_log\_buffer\_size的值就不能过小，因为在一个同步周期内如果待刷新的日志超过了innodb\_log\_buffer\_size设置的大小，InnoDB就会强制执行同步操作。如果您的Linux操作系统使用的是带有日志功能的文件系统并且日志功能是开启的，那么还是建议将该参数设置为2。

innodb\_buffer\_pool\_size：这个参数调整分配给InnoDB引擎使用的可用数据内存区域的大小。实际上这个数据区域不止包括了本文中一直强调的Page Cache区域，它还有很多数据区域。例如InnoDB中用来进行查询排序的Sort Buffer区域。建议的设置大小是MySQL数据库服务所在物理服务器上总内存的60%——80%（文件系统的Cache Memory/Buffer Memory等其它程序还要使用）。8GB的物理服务器可设置6GB的InnoDB Buffer Pool可用内存区域。注意，当MySQL数据库启动时并不是立刻就会占据所有数据区域。

innodb\_buffer\_pool\_instances：本小节和本文中多处位置都提及到innodb\_buffer\_pool\_size参数以及它的含义。这个参数值在生产环境下一般设置得都比较大（例如4GB、8GB、12GB、24GB等等）。但是由于脏数据刷盘的周期性，在I/O性能强劲的物理机器上可能就会存在I/O间歇性低谷。为了将I/O操作一直保持在一定的工作效能上，也为了发挥CPU的计算性能，InnoDB引擎允许将innodb\_buffer\_pool划分为多个独立的运行实例，当InnoDB需要读取新的Page时，它们会按照一定的算法被分配到某个独立运行的buffer pool instance中。这些buffer pool instance有各自独立的LRU算法队列、独立计算脏页比例，并且独立进行脏页刷新。innodb\_buffer\_pool\_instances参数在具有较高I/O性能并且具有较大innodb\_buffer\_pool\_size设定值的物理设备上能够对I/O性能产生非常明显的影响。如果您采用的是固态磁盘或者磁盘阵列作为MySQL服务器的硬件层存储介质，那么建议1-2GB的innodb\_buffer\_pool就分配一个独立的运行实例（这样算下来12GB的buffer pool可以设置6-12个运行实例，注意进行生产环境下的实测调整哦^-^）。但如果您只是使用的机械磁盘又或者innodb\_buffer\_pool\_size的值并不大，那么将innodb\_buffer\_pool\_instances参数设置为1就可以了。

innodb\_io\_capacity：该参数控制着InnoDB Buffer Pool数据内存区域进行磁盘同步时每次可以同步的脏页数量。在磁盘I/O性能不足时，如果innodb\_io\_capacity参数值过大就会造成I/O阻塞，并且造成InnoDB引擎性能较大的降低。但如果您使用的是固态硬盘或者RAID磁盘阵列,就可以将innodb\_io\_capacity参数默认的200设置大一些，例如设置成500——800）。

innodb\_adaptive\_flushing：该参数一定要打开，保证脏页的同步周期由InnoDB引擎根据实时I/O性能情况自行控制同步频率（实际上只有两种频率：1秒或者10秒）。

innodb\_max\_dirty\_pages\_pct：该参数默认为75，一般情况下无需更改。另外innodb\_io\_capacity\_max参数表示当脏页数量在InnoDB Buffer Pool内存中的比例超过了innodb\_max\_dirty\_pages\_pct参数设置的上限后，就按照innodb\_io\_capacity\_max设置的脏页数量强制进行脏页的刷新（建议采用默认值即可）。但是设想一下这个问题：什么情况下最可能使脏页在内存中的占比超过上限呢？当然是InnoDB引擎的事务不断快速执行，并且I/O性能又不足以快速完成同步。这时InnoDB引擎将停止事务的执行，并且进行强制刷新。所以，当问题真正发生时innodb\_io\_capacity\_max参数设置得再大也不可能解决I/O拥堵的问题，反而可能使问题更严重。

### 3-3-2、对MySQL中的其它参数进行调整 ###

*  innodb\_page\_size：该参数决定了InnoDB引擎中每一页的大小。每一个page包含多条row数据，更大的page size意味着内存中存储的每页信息有更多的数据条数。由于文件系统和底层硬件设置的结构，所以该值都为4KB的整数倍（默认值为16KB，可选值为4KB、8KB、16KB）。注意如果您需要更改这个参数值，那么就必须在MySQL数据库初始化启动时，就加入到my.cnf配置文件中。否则一旦创建了用户数据表，再对这个参数进行修改，MySQL数据库就会报错。
 *  innodb\_read\_io\_threads：该参数设置InnoDB数据库中的负责从磁盘上读取数据的线程数量，另外这些线程还负责在预读选项开启时承担起预读的工作任务。innodb\_read\_io\_threads的建议值为CPU的内核数量。
 *  innodb\_write\_io\_threads：该参数设置InnoDB数据库中负责将脏页同步到磁盘上的线程数量。innodb\_write\_io\_threads的建议值为CPU的内核数量。
 *  innodb\_read\_ahead\_threshold：该参数表示InnoDB引擎中的顺序预读阀值。在buffer pool中的page也有一个组织结构：64个page组成一个extent结构。当InnoDB发现在一个extent结构中\*\*已经连续读取\*\*N个page，那么InnoDB会接着将另外64 - N个后续的page读入到buffer pool中。顺序预读在“连续读”性能较高的硬件设备上，对性能的影响非常小。所以如果读者使用了I/O性能比较强劲的固态磁盘环境或者磁盘阵列环境，则建议直接关闭该功能（设置为0即可）。
 *  innodb\_random\_read\_ahead：该参数表示是否开启随机预读，默认是关闭的。
 *  innodb\_flush\_neighbors：既然InnoDB引擎提供Page的预读功能，当然就提供预写功能。该参数表示当Buffer Pool中的脏页被同步到磁盘时，是否一起刷新和这个脏页临近的页信息。该参数在I/O性能比较强劲的固态磁盘环境或者磁盘阵列环境下，对性能提升并不明显。所以建议在这样的情况下直接关闭这个功能（设置为0即可）。
 *  sort\_buffer\_size：后文介绍数据库查询优化时会讨论到这个参数。该参数对数据库引擎的查询性能，特别是有对结果进行排序要求的查询性能影响非常大。
 *  join\_buffer\_size：后文介绍数据库查询优化时会讨论到这个参数。该参数对数据库引擎的查询性能，特别是有各种join连接要求的查询性能影响非常大。
 *  binlog\_cache\_size：在MySQL数据库中处理InnoDB层存在“重做日志”以外，在数据库管理层还有一个独立工作的二进制日志模块。这个日志模块的工作方式和“重做日志”的工作方式相似，它们采用的办法都是在内存中进行日志数据变更，然后再按照一定的策略周期性/直接同步到磁盘上。binlog\_cache\_size参数设置的就是可供二进制日志在内存中进行暂存的空间大小。需要注意的是：binlog\_cache\_size和innodb\_buffer\_pool\_size不同的是，前者的大小以MySQL数据库的客户端连接为单位。也就是说MySQL数据库会为两个独立的数据库客户端连接分别分配独立运行的binlog cache空间。正式环境的数据库中为每一个数据库连接设置的binlog cache空间不需要太大，当然这还要考虑实际的客户端请求频度和数据类型，还要考虑下面将介绍的sync\_binlog参数设定。该参数建议的几个设置值为：32768（32KB为默认值，没有特别的要求可以保留该设置）、65536（64KB）、131072（128KB）、262144（KB）、524288（512KB）、1048576（1MB）以内。
 *  sync\_binlog：在MySQL数据库中除了InnoDB的“重做日志”需要同步以外，二进制日志也需要进行同步。这个参数是指MySQL数据库在内存中进行X次二进制日志操作后，就将内存中的二进制日志同步到磁盘中。

### 3-3-3、调整后的参数情况 ###

以下是一组可以在配置有固态硬盘和磁盘阵列的正式MySQL数据库环境下使用的配置项参考，主要是为读者总结InnoDB引擎中和I/O性能相关的重要参数（只和I/O性能有关，因为后续的文章中还会介绍其他参数）。读者在进行参数配置是还是需要按照自己团队的生产环境情况，对配置项进行调整（这些参数信息都在MySQL数据库的my.cnf主配置文件中进行设置）：

# 设置单个日志文件的大小为500MB
    innodb_log_file_size = 524288000
    # 设置日志文件组中有两个日志文件
    innodb_log_files_in_group = 2
    # 设置日志内存区域为10MB
    innodb_log_buffer_size = 10485760
    # 设置日志数据同步策略为“2”
    innodb_flush_log_at_trx_commit = 2
    # 设置buffer pool的大小为8GB
    innodb_buffer_pool_size = 8G
    # 设置正常情况下每一次脏页到磁盘的同步数量为800个
    # （当然读者要确定磁盘I/O性能够用，否则改大这个值有害无益）
    innodb_io_capacity = 800
    # 打开InnoDB提供的自监控频率
    innodb_adaptive_flushing = on
    # 已经介绍过三次了，不再赘述
    innodb_max_dirty_pages_pct = 75
    innodb_io_capacity_max = 2000
    
    # 设置InnoDB的buffer pool区域一共有8个独立运行的实例
    innodb_buffer_pool_instances = 8
    # 设置每一个数据页“page”的大小为16KB。为4KB的整数倍
    innodb_page_size = 16384
    # 设置每次二进制日志操作都提交到文件系统的Cache中
    sync_binlog = 0
    # 也可设置二进制日志在内存区域每操作1000次后，就进行磁盘同步
    #sync_binlog = 1000
    # 关闭顺序预读
    innodb_read_ahead_threshold = 0
    # 关闭随机读
    innodb_random_read_ahead = off
    # 关闭临近写
    innodb_flush_neighbors = 0
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15
       16
       17
       18
       19
       20
       21
       22
       23
       24
       25
       26
       27
       28
       29
       30
       31
       32
       33

配置完成后可以通过以下命令查看当前MySQL数据库和InnoDB引擎中相关的配置参数（为节约篇幅，已省去一部分查询结果）：

# 查询配置参数
    show variables like 'innodb%';
    # 或者查询全局工作状态也行
    # show global status like 'innodb%';
    
    ......
    innodb_adaptive_flushing                ON
    innodb_adaptive_flushing_lwm            10
    innodb_adaptive_hash_index              ON
    innodb_adaptive_max_sleep_delay         150000
    innodb_additional_mem_pool_size         8388608
    innodb_api_disable_rowlock              OFF
    innodb_api_enable_binlog                OFF
    innodb_api_enable_mdl                   OFF
    innodb_buffer_pool_instances            8
    innodb_buffer_pool_size                 8589934592
    innodb_disable_sort_file_cache          OFF
    innodb_doublewrite                      ON
    innodb_fast_shutdown                    1
    innodb_file_per_table                   ON
    innodb_flush_log_at_timeout             1
    innodb_flush_log_at_trx_commit          2   
    innodb_flush_neighbors                  0
    innodb_flushing_avg_loops               30
    innodb_io_capacity                      800
    innodb_io_capacity_max                  2000
    innodb_lock_wait_timeout                50
    innodb_locks_unsafe_for_binlog          OFF
    innodb_log_buffer_size                  10485760
    innodb_log_compressed_pages             ON
    innodb_log_file_size                    524288000
    innodb_log_files_in_group               2
    innodb_log_group_home_dir               ./
    innodb_lru_scan_depth                   1024
    innodb_max_dirty_pages_pct              75
    innodb_max_dirty_pages_pct_lwm          0
    innodb_mirrored_log_groups              1
    innodb_online_alter_log_max_size        134217728
    innodb_open_files                       2000
    innodb_page_size                        16384
    innodb_random_read_ahead                OFF
    innodb_read_ahead_threshold             0
    innodb_read_io_threads                  8
    innodb_write_io_threads                 4
    innodb_sort_buffer_size                 1048576
    innodb_table_locks                      ON
    innodb_use_native_aio                   ON
    innodb_use_sys_malloc                   ON
    innodb_version                          5.6.22
    ......
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15
       16
       17
       18
       19
       20
       21
       22
       23
       24
       25
       26
       27
       28
       29
       30
       31
       32
       33
       34
       35
       36
       37
       38
       39
       40
       41
       42
       43
       44
       45
       46
       47
       48
       49
       50

### 3-3-4、提供更优异的硬件方案 ###

*  使用磁盘阵列替代单块磁盘

这是最基本的硬件层改造方式，目前大多数厂商提供的PC Server基本上都集成了RAID控制器。所以这样做一般不需要额外增加购买硬件设备的费用。在MySQL官网上并没有明确推荐使用哪一种磁盘阵列模式，但是从搭建磁盘阵列支持MySQL的实际引用情况来看，更多是使用RAID 10阵列模式（另外磁盘阵列的整体性能和阵列控制芯片有很大关系）。RAID 10阵列模式可以在提升了整个系统I/O性能的基础上兼顾了存储的安全性。为了使用RAID 10磁盘阵列模式，读者至少需要为准备4块磁盘。其中2/4的磁盘容量用来存储数据冗余，另外2/4的磁盘容量用来分散存储数据。对于RAID 10磁盘阵列模式的详细工作方式介绍，读者可以参看另一篇文章（《[架构设计：系统存储（2）——块存储方案（2）][2_2]》）

*  使用固态硬盘进一步替代机械磁盘

以上解决方案中，每一个机械磁盘的I/O性能将会成为整个RAID 10磁盘阵列的性能瓶颈（不考虑阵列控制芯片的处理性能）。所以如果技术团队还有多余的资金支持那么下一步要做的就是将构成RAID 10磁盘阵列多个机械磁盘全部替换成固态磁盘。如下图所示：

![这里写图片描述][20160918161437942]

*  进一步使用外置磁盘阵列柜 + 光纤结构

USB3.0接口的理论带宽只有600MB/S，而且PC Server内置的磁盘阵列控制器由于服务器内部空间的限制，也存在磁盘数量扩展困难的问题。如果读者确认生产环境的某个物理服务器将以I/O读写操作为主，且I/O性能将成为其上工作的应用软件的瓶颈。那么这时最好的硬件方案就是直接采用外置企业级磁盘阵列柜 + 光纤接口的方式搭建硬件层支持。

目前主流的光纤线路带宽为16Gb/s，这远远高于USB3.0 6Gb/s的理论带宽、高于SAS 12Gb/s的理论带宽。另外单个企业级磁盘阵列柜可容纳的磁盘数量就已经很高了（例如IBM Storwize V5000 单柜提供24个磁盘位，单柜支持最大72TB存储容量），并且这些企业级盘柜一般支持扩展成多柜。这两种特性有效解决了硬件层面磁盘I/O速度和容量的问题，但代价就是这些IT基础折本的价格一般比较昂贵，技术团队所在企业需要有比较宽裕的项目/产品建设预算。

[5_MySQL_1]: http://blog.csdn.net/yinwenjie/article/details/52461398#t3
[2_2]: http://blog.csdn.net/yinwenjie/article/details/52248862#t6
[20160918161437942]: /images/20220603/8f3457c6921f4a99a55f0759dbeafbee.png