innodb_temp_data_file_path 参数查看 innodb_log_file_in_group

转载

mob64ca1405d568 2024-04-19 13:29:10

文章标签 数据库操作系统 python MySQL ci 文章分类 机器学习人工智能

如果我们需要挖掘正式生产环境上MySQL数据库服务的性能潜力，那么对MySQL数据库服务中的默认参数进行更改就是必须要做的事情。

在进行配置修改之前，我们可以先看看当前MySQL数据库特别是InnoDB引擎的工作状态：

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

执行后可以得到类似如下的执行结果：

innodb_temp_data_file_path 参数查看 innodb_log_file_in_group_数据库

innodb_temp_data_file_path 参数查看 innodb_log_file_in_group_MySQL_02

MySQL主要性能参数

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完成一个事务日志操作后，向磁盘进行持久化的写入策略。建议设置为2。

innodb_flush_log_at_trx_commit = 0时，InnoDB将按照1秒钟为单位向磁盘写入这个阶段所有已完成的事务日志信息。这时innodb_log_buffer_size的值就不能过小，因为在一个同步周期内如果待刷新的日志超过了innodb_log_buffer_size设置的大小，InnoDB就会强制执行同步操作。这里的写入成功并不是说写入到Linux 操作系统的Page Cache中就算成功，而是需要等待操作系统真正写到了物理磁盘上的通知。这意味着即使InnoDB Buffer Pool中的数据操作是成功的，但是一旦数据库系统异常崩溃，那么业务系统将会丢失前1秒内写入的数据：因为没有磁盘介质上的日志就无法在异常重启后恢复数据信息。
innodb_flush_log_at_trx_commit = 1时，InnoDB按照完成一个日志操作就向磁盘写入日志信息的方式来工作（执行一个事务就写入一个事务日志）。同样，这里的写入成功同样是要等待操作系统返回真正写入了物理磁盘的通知，可以打比方理解为BIO。
innodb_flush_log_at_trx_commit = 2时，InnoDB按照完成一个日志操作就向磁盘写入日志信息的方式来工作。但是，这种工作模式下InnoDB不会等待操作系统返回物理磁盘上写入成功的通知，就会继续工作，可以理解为NIO或MMF。实际上这个时候，数据一般还存在于Linux操作系统的cache memory区块中，所以这种模式下最好使用带有日志功能的文件系统，并且确认开启了文件系统的日志功能。

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：该参数一定要打开，on，保证脏页的同步周期由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拥堵的问题，反而可能使问题更严重。

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不同的是，binlog_cache_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次二进制日志操作后，就将内存中的二进制日志同步到磁盘中。