---
title: 数据库参数调优
description: "通过调整数据库的参数配置，可以有效提升服务器性能。"
url: https://www.hikunpeng.com/document/detail/zh/kunpengdbs/ecosystemEnable/MariaDB/kunpengdbstune_05_0020.html
sourcePath: /source/zh/kunpengdbs/ecosystemEnable/MariaDB/kunpengdbstune_05_0020.html
indexId: 4b22c279d4ecca4e6000505ede0c5cfad3ee5502d9205fc62d4c8eeba8968b5373
---
# 数据库参数调优

#### 目的

通过调整数据库的参数配置，可以有效提升服务器性能。


#### 方法

使用如下配置文件参数启动数据库，默认配置文件路径为“/etc/my.cnf”，其中MariaDB软件安装以及数据存放路径根据实际情况修改。

```
[mysqld_safe]
log-error=/data/mariadb/log/mariadb.log
pid-file=/data/mariadb/run/mysqld.pid
[client]
socket=/data/mariadb/run/mysql.sock
default-character-set=utf8
[mysqld]
basedir=/usr/local/mariadb
tmpdir=/data/mariadb/tmp
datadir=/data/mariadb/data
socket=/data/mariadb/run/mysql.sock
port=3306
user=root
default_authentication_plugin=mysql_native_password
ssl=0 #关闭ssl
max_connections=2000  #设置最大连接数
back_log=2048  #设置会话请求缓存个数
performance_schema=OFF #关闭性能模式
max_prepared_stmt_count=128000
transaction_isolation=READ-COMMITTED #设置数据库事务隔离级别
#file
innodb_file_per_table=on #设置每个表一个文件
innodb_log_file_size=1500M #设置logfile大小
innodb_log_files_in_group=32 #设置logfile组个数
innodb_open_files=50000 #设置最大打开表个数
#buffers
innodb_buffer_pool_size=230G #设置buffer pool size,一般为服务器内存60%
innodb_buffer_pool_instances=16 #设置buffer pool instance个数，提高并发能力
innodb_log_buffer_size=10240M #设置log buffer size大小
#tune
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_flush_method=O_DIRECT #设置innodb数据文件及redo log的打开、刷写模式
innodb_io_capacity=20000 # 设置innodb 后台线程每秒最大iops上限
innodb_io_capacity_max=40000 #设置压力下innodb 后台线程每秒最大iops上限
innodb_lru_scan_depth=9000 #设置page cleaner线程每次刷脏页的数量
innodb_page_cleaners=16  #设置将脏数据写入到磁盘的线程数
#perf special
innodb_flush_neighbors=0 #检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新，SSD关闭该功能
innodb_write_io_threads=16 #设置写线程数
innodb_read_io_threads=16 #设置读线程数
innodb_purge_threads=32  #设置回收已经使用并分配的undo页线程数
thread_handling=pool-of-threads #使用线程池
thread_pool_size=100 #控制线程组的大小
innodb_adaptive_hash_index=0
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES
```


**表1 数据库调优参数**

| 参数名称 | 参数含义 | 优化建议 |  |  |
| --- | --- | --- | --- | --- |
| innodb\_thread\_concurrency | InnoDB使用操作系统线程来处理用户的事务请求。 | 建议取默认值为0，它表示默认情况下不限制线程并发执行的数量。 |  |  |
| innodb\_read\_io\_threads | 执行请求队列中的读请求操作的线程数。 | 根据CPU核数及读写比例进一步更改来提高性能。 |  |  |
| innodb\_write\_io\_threads | 执行请求队列中的写请求操作的线程数。 | 根据CPU核数及读写比例进一步更改来提高性能。 |  |  |
| query\_cache\_size | 设置Query Cache（查询缓存）大小。 | Query Cache（查询缓存）是一个众所周知的瓶颈位，即使在并发量不高的时候也会出现。最好的选择是从一开始就禁用它。通过设置query\_cache\_size = 0，建议禁用查询缓存。 |  |  |
| innodb\_buffer\_pool\_instances | 开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写。 | 建议设置8~16。 |  |  |
| innodb\_open\_files | 在innodb\_file\_per\_table模式下，限制Innodb能打开的文件数量。 | 建议此值调大一些，尤其是表特别多的情况。 |  |  |
| innodb\_buffer\_pool\_size | 缓存数据和索引的地方。 | 通常建议内存的70%左右。 |  |  |
| innodb\_log\_buffer\_size | 缓存重做日志。 | 默认值是64M，建议通过查看innodb\_log\_wait，调整innodb\_log\_buffer\_size大小。 |  |  |
| innodb\_io\_capacity | innodb后台线程每秒最大iops上限。 | 建议为IO QPS总能力的75%。 |  |  |
| innodb\_log\_files\_in\_group | 重做日志组的个数。 | 默认值是2，建议设为32。 |  |  |
| innodb\_log\_file\_size | 重做日志文件大小。 | 如果存在大量写操作，建议增加日志文件大小，但日志文件过大，硬性数据恢复时间，非生产环境，测试极限性能尽量调大，商用场景需要考虑数据恢复时间。 |  |  |
| innodb\_flush\_method | Log和数据刷新磁盘的方法： datasync模式：写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush，并且文件的元数据信息也都需要更新到磁盘。 O\_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成。 O\_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步，日志还是要经过OS缓冲。 | 建议O\_DIRECT模式。 |  |  |
| innodb\_lru\_scan\_depth | LRU列表的可用页数量。 | 默认值是1024，非生产环境，测试极限性能可以适当调大，减少checkpoint次数。 |  |  |
| innodb\_page\_cleaners | 刷新脏数据的线程数。 | 建议与innodb\_buffer\_pool\_instances相等。 |  |  |
| innodb\_purge\_threads | 回收undo的线程数。 | 默认值是4，建议设为32。 |  |  |
| innodb\_flush\_log\_at\_trx\_commit | 0：不管有没有提交，每秒钟都写到binlog日志里。 1：每次提交事务，都会把log buffer的内容写到磁盘里去，对日志文件做到磁盘刷新，安全性最好。 2：每次提交事务，都写到操作系统缓存，由OS刷新到磁盘，性能最好。 | 非生产环境，测试极限性能，可以设置为0。 |  |  |
| innodb\_doublewrite | 是否开启二次写。 | 非生产环境，测试极限性能，可以设置为0，关闭二次写。 |  |  |
| ssl | 该参数用于开启或关闭安全连接。 | 安全连接对性能影响较大，非生产环境，测试极限性能，可以设置为0；商用场景，根据客户需求调整。 |  |  |
| skip\_log\_bin | 该参数用于开启或关闭binlog。 | 在非生产环境中测试极限性能时，在参数文件中增加此参数，关闭binlog选项。 具体的方法为将如下信息添加至配置文件中： 1 2 skip\_log\_bin \#log\-bin=mysql\-bin | 1 2 | skip\_log\_bin \#log\-bin=mysql\-bin |
| 1 2 | skip\_log\_bin \#log\-bin=mysql\-bin |  |  |  |
| performance\_schema | 是否开启性能监测视图。 | 非生产环境，测试极限性能设置为OFF，关闭性能监测视图。 |  |  |