--- title: 数据库参数调优 description: "MySQL数据库参数调优是提高数据库性能的重要手段之一。由于MySQL数据库是动态管理系统，在实际应用中，有时会因参数调整不当而降低MySQL的性能，因此需要通过调节MySQL参数来优化它的性能。" url: https://www.hikunpeng.com/document/detail/zh/kunpengdbs/ecosystemEnable/MySQL/kunpengdbstune_05_0017.html sourcePath: /source/zh/kunpengdbs/ecosystemEnable/MySQL/kunpengdbstune_05_0017.html indexId: fc0254f4e5f2219ac5e88e48a8aabe0784e3c2772f937d9069c390b45db81cd071 --- # 数据库参数调优 #### 目的 MySQL数据库参数调优是提高数据库性能的重要手段之一。由于MySQL数据库是动态管理系统，在实际应用中，有时会因参数调整不当而降低MySQL的性能，因此需要通过调节MySQL参数来优化它的性能。 #### 方法 使用如下配置文件参数启动数据库，默认配置文件路径为“/etc/my.cnf”，其中MySQL软件安装及数据存放路径根据实际情况修改。 参数配置完成后，需要重启数据库才能生效。 ``` [mysqld_safe] log-error=/data/mysql/log/mysql.log pid-file=/data/mysql/run/mysqld.pid [mysqldump] quick [mysql] no-auto-rehash [client] socket=/data/mysql/run/mysql.sock default-character-set=utf8 [mysqld] server-id=1 basedir=/usr/local/mysql tmpdir=/data/mysql/tmp datadir=/data/mysql/data socket=/data/mysql/run/mysql.sock port=3306 user=root default_authentication_plugin=mysql_native_password ssl=0 #关闭ssl max_connections=2000 #设置最大连接数 back_log=4000 #设置会话请求缓存个数 performance_schema=OFF #关闭性能模式 max_prepared_stmt_count=128000 #file innodb_file_per_table=on #设置每个表一个文件 innodb_log_file_size=2048M #设置logfile大小 innodb_log_files_in_group=32 #设置logfile组个数 innodb_open_files=10000 #设置最大打开表个数 #buffers innodb_buffer_pool_size=230G #设置buffer pool size,一般为服务器内存60% innodb_buffer_pool_instances=64 #设置buffer pool instance个数，提高并发能力 innodb_log_buffer_size=2048M #设置log buffer size大小 #tune default_time_zone=+8:00 #设置默认时区 thread_cache_size=2000 #设置线程缓存的大小 sync_binlog=1 #设置每次sync_binlog事务提交刷盘 innodb_flush_log_at_trx_commit=1 #每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去 innodb_use_native_aio=1 #开启异步IO innodb_spin_wait_delay=20 #设置spin_wait_delay 参数，防止进入系统自旋 innodb_sync_spin_loops=25 #设置spin_loops 循环次数，防止进入系统自旋 innodb_spin_wait_pause_multiplier=5 #该参数用于控制spin_lock轮询间隔随机数 innodb_flush_method=O_DIRECT #设置innodb数据文件及redo log的打开、刷写模式 innodb_io_capacity=30000 # 设置innodb后台线程每秒最大iops上限 innodb_io_capacity_max=40000 #设置压力下innodb后台线程每秒最大iops上限 innodb_lru_scan_depth=9000 #设置page cleaner线程每次刷脏页的数量 innodb_page_cleaners=16 #设置将脏数据写入到磁盘的线程数 table_open_cache_instances=16 #该参数用于控制MySQL缓存table句柄的分区的个数 table_open_cache=30000 #该参数用于控制Mysqld打开表的数量 #perf special innodb_flush_neighbors=0 #检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新，SSD关闭该功能 innodb_write_io_threads=24 #设置写线程数 innodb_read_io_threads=16 #设置读线程数 innodb_purge_threads=32 #设置回收已经使用并分配的undo页线程数 innodb_adaptive_hash_index=0 sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES log-bin=mysql-bin #启用二进制日志功能，并指定二进制日志文件的名称前缀 #skip_log_bin ``` **表1 数据库调优参数** | 参数名称 | 参数含义 | 优化建议 | | --- | --- | --- | | innodb\_thread\_concurrency | 该参数用于控制InnoDB存储引擎在执行查询时使用的并发线程数。 | 高并发场景，可调整该参数，减少InnoDB层的资源争用。 建议取默认值为0，它表示默认情况下不限制线程并发执行的数量。 | | innodb\_read\_io\_threads | 该参数用于控制InnoDB存储引擎在执行读操作时使用的IO线程数。 | 可以根据CPU核数及读写比例设置该参数来提高性能。 | | innodb\_write\_io\_threads | 该参数用于控制InnoDB存储引擎在执行写操作时使用的IO线程数。 | 可以根据CPU核数及读写比例设置该参数来提高性能。 | | innodb\_buffer\_pool\_instances | 该参数用于控制InnoDB缓冲池的实例数量。 | 增加缓冲池实例数可以减少内存热点页面的争用，从而提高内存读取的并发性。 建议设置为8～32。 | | innodb\_open\_files | 在innodb\_file\_per\_table模式下，该参数用于指定InnoDB存储引擎打开的最大文件数。 | 建议此值调大一些，尤其是表特别多的情况。 | | innodb\_buffer\_pool\_size | 该参数指定了InnoDB缓冲池的大小。InnoDB缓冲池是一个内存区域，用于存储InnoDB表的数据和索引。 | 增加该参数，可使更多数据Cache在内存，减少IO操作。 建议设置为物理内存的70%左右。 | | innodb\_log\_buffer\_size | 该参数用于控制InnoDB事务日志缓冲池的大小。 | 如果该参数设置得太小，InnoDB事务日志缓冲池可能会频繁地刷新到磁盘，导致性能下降。 如果该参数设置得太大，会占用过多的内存资源，导致系统的稳定性受到影响。 合理增加日志缓冲池大小，可提升事务提交效率，尤其在大事务和高并发场景，减少日志刷盘的次数。通过查看innodb\_log\_wait值，来调整innodb\_log\_buffer\_size值。以下两种情况可以考虑调大innodb\_log\_buffer\_size参数的值： 如果innodb\_log\_wait值不等于0并且持续增长时，表明InnoDB存储引擎的日志缓冲池（innodb\_log\_buffer）空间不足，导致innodb\_log\_buffer需要等待。 如果innodb\_log\_wait值较高，说明InnoDB存储引擎需要更多的日志缓冲池空间。 默认值是64MB。 | | innodb\_io\_capacity | 该参数定义了InnoDB后台线程每秒可用的IO操作数，即IOPS。 | 建议设置为磁盘IOPS总能力的75%（磁盘IOPS是指innodb\_page\_size大小的数据块时的IOPS）。 | | innodb\_log\_files\_in\_group | 该参数用于指定InnoDB日志文件组中的日志文件数量。 | \- | | innodb\_log\_file\_size | 该参数用于设置InnoDB日志文件的大小。 | 如果存在大量写操作，建议增加日志文件大小，但日志文件过大，会影响数据恢复时间。 如果是非生产环境，测试极限性能时，尽量调大日志文件。 如果是商用场景，需要考虑数据恢复时间，综合折中后设置日志文件大小。 | | innodb\_flush\_method | 该参数用于指定InnoDB存储引擎刷新数据到磁盘的方式。 | 不同的刷新方式会影响到InnoDB存储引擎的性能和数据的安全性。 建议设置为O\_DIRECT模式，直接写磁盘，需要经过OS缓冲池。 | | innodb\_spin\_wait\_delay | 该参数用于控制spin\_lock轮询的时间间隔。 | 可通过热点函数来对该参数进行优化，尤其在整机单实例场景下，建议将对参数优化，小规格单实例场景默认值即可。 建议设置为180。 | | innodb\_sync\_spin\_loops | 该参数用于控制spin\_lock轮询的循环次数。 | 可通过热点函数来对该参数进行优化，尤其在整机单实例场景下，建议将对参数优化，小规格单实例场景默认值即可。 建议设置为25。 | | innodb\_spin\_wait\_pause\_multiplier | 该参数用于控制spin\_lock轮询间隔随机数。 | 可通过热点函数来对该参数进行优化，尤其在整机单实例场景下，建议将对参数优化，小规格单实例场景默认值即可。 默认值50，建议设置为5。 | | innodb\_lru\_scan\_depth | 该参数用于控制InnoDB缓冲池中LRU列表的可用页数量。 | 默认值是1024，非生产环境，测试极限性能可以适当调大，减少checkpoint次数。 | | innodb\_page\_cleaners | 该参数用于控制刷新脏数据的线程数。 | 建议设置为与innodb\_buffer\_pool\_instances的值相同。 | | innodb\_purge\_threads | 该参数用于控制回收undo的线程数。 | \- | | innodb\_flush\_log\_at\_trx\_commit | 该参数用于控制事务提交时日志刷新的行为。 | 非生产环境，测试极限性能，可以设置为0，表示事务提交时不会立即将日志写入磁盘，而是将日志缓存在内存中。 | | innodb\_doublewrite | 该参数用于开启或关闭二次写。 | 在非生产环境中测试极限性能时，可以设置为0，关闭二次写。 | | ssl | 该参数用于开启或关闭安全连接。 | 安全连接对性能影响较大，非生产环境，测试极限性能，可以设置为0；商用场景，根据客户需求调整。 | | table\_open\_cache\_instances | 该参数用于控制MySQL缓存table句柄的分区的个数。 | 建议设置为16～32。 | | table\_open\_cache | 该参数用于控制Mysqld打开表的数量。 | 建议设置为30000。 | | skip\_log\_bin | 该参数用于开启或关闭binlog。 | 在非生产环境中测试极限性能时，在参数文件中增加此参数，关闭binlog选项。 具体的方法为将如下信息添加至配置文件中： skip\_log\_bin \#log\-bin=mysql\-bin | | performance\_schema | 该参数用于开启或关闭性能模式。 | 在非生产环境中测试极限性能时，将该参数设置为OFF，关闭性能模式。 |