调优参数详情
| 参数选项 | 释义 | 默认参数 | 是否支持热更新(dynamic) | 策略 | 注意事项 | 推荐组合 |
|---|---|---|---|---|---|---|
| innodb_buffer_pool_size | 数据缓存，索引缓存，更改数据的缓冲，存储内部结构 | 128M | Yes | MEM*70% | ||
| innodb_flush_log_at_trx_commit | InnoDB依靠重做日志来保证数据能在丢失后进行恢复，该参数控制重做日志写入磁盘的过程 | 1 | Yes | 0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。 1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去。 2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作 |
设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。 设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。 设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。 |
innodb_flush_log_at_trx_commit=2 + sync_binlog=100或者0 |
| sync_binlog | 控制数据库的binlog刷到磁盘上 | 1 | YES | 为0，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的。 为1，每次事务提交，MySQL都会把binlog刷下去。 大于1，每($sync_binlog)次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去 (牺牲一定的一致性，可以获得更高的并发和性能) |
为0，风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。 为1，性能损耗最大，一旦系统crash，系统才有可能丢失1个事务的数据。 大于1，刷新的频率过高对IO的影响也非常大 |
|
| wait_timeout | 设置睡眠连接超时秒数，如果某个连接超时，会被mysql自然终止 | 28800(秒) | Yes | 查询show processlist; 以大多数sleep进程TIME为标准设值 |
过大有弊端，导致MySQL里大量的SLEEP进程无法及时释放，拖累系统性能 不能设置过小，导致“MySQL has gone away”之类的问题 |
|
|
innodb_log_file_size innodb_log_files_in_group |
InnoDB 存储引擎使用一个指定大小的Redo log空间，Redo log的空间通过innodb_log_file_size和innodb_log_files_in_group来调节。将这俩参数相乘即可得到总的可用Redo log 空间 | 48M 2 |
No No |
配置的Redo空间越大，InnoDB就能更好的优化写操作。 | 当出现崩溃或掉电等意外时，增大Redo空间也意味着更长的恢复时间。 | |
| innodb_log_buffer_size | 数据写入到内存中的日志缓存中，由于InnoDB在事务提交前，并不将改变的日志写入到磁盘中，因此在大事务中，可以减轻磁盘I/O的压力。 | 16M | No | 通常情况下，4MB-8MB已经足够 | 当mysql服务挂掉时，数值越大，则恢复数据需要越久 | 对于比较小innodb_buffer_pool_size，建议是设置为一样大 innodb_buffer_pool_size + innodb_log_buffer_size |
| innodb_file_per_table | 默认开启InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间 | 1 | Yes | 默认开启，单表在不同的数据库中移动，空间可回收，而且不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。 不开启，会降低文件系统操作的性能开销。适合于将整个磁盘都用于存储mysql数据的情况。 |
默认开启时，应避免单表增加过大。 不开启时，应避免在空间受限的系统表空间里导入大量临时数据。 |
默认开启 |
| innodb_lock_wait_timeout | 事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败 | 50 | Yes | 最小可设置为1s，最大可设置1073741824秒以上再大就会被截断了 | 如果事务开始前部分有其他操作而中途遇到锁等待超时则mysql端还需要回滚，如果频繁出现，会增加DB消耗 | 根据业务量级，对于数值进行适当的降低或者升高。 |
| innodb_purge_threads | 将purge线程从master线程分离出来，提高cpu使用率提升存储引擎性能，最大值为32 | 4 | No | 可以指定多个innodb_purge_threads来进一步加快和提高undo（还原段）回收速度 | ||
|
innodb_thread_concurrency innodb_commit_concurrency |
默认是0，则表示没有并发线程数限制，所有请求都会直接请求线程执行。 同样控制了多线程并发提交的数量。 |
0 0 |
Yes Yes |
如果数据库没有出现性能问题，且当并发用户线程数量小于64，建议设置使用默认值就好。 如果负载不稳定，时而低，时而高到峰值，建议先设置为128，并通过不断的降低这个参数，直到发现能够提供最佳性能的线程数 |
两者的修改相辅相成，单纯调高innodb_thread_concurrency的数值，会造成大量线程阻塞。 设置过高值，可能会因为系统资源内部争夺导致性能下降； 定期监控和分析DB，因为随着数据库负载的变化，业务的增加，参数需要动态的调整。 |
在大多数情况下，最佳的值是小于并接近虚拟CPU的个数 + innodb_concurrency_tickets（5000） + innodb_commit_concurrency |
|
innodb_write_io_threads innodb_read_io_threads |
IO Thread 的主要工作是要负责这些IO请求的回调（call back）处理 | 4 4 |
No No |
根据CPU核数来更改相应的参数值，更有效的利用cpu性能。 允许值的范围是1～64 根据业务量级的不同，读与写的参数设置可以有微小差距。 |
||
| innodb_io_capacity | 缓冲池冲洗页和合并从所述插入缓冲数据执行的I / O活动的上限 | 200 | Yes | 服务器的磁盘是5400 RPM ~7200 RPM，属于比较低级的磁盘，根据MySQL 官方的建议，应该将innodb_io_capacity降低到100. 如果具有大量IOPS的快速驱动器，可是适当提高该值 |
设置过大，则会造成MySQL高估了磁盘的能力，导致脏页堆积。 设置过低，则会出现MySQL低估了磁盘的能力，使得数据库能够单位时间内提交的事务数(tps)降低 |
采用SSD云盘,确认支持的IOPS数量 |
| innodb_buffer_pool_instances | 允许多个缓冲池实例，每页根据哈希平均分配到不同缓冲池实例中，减少数据库内部资源竞争，增加数据库并发处理能力 | 1 | No | 在buffer_pool不大时，该值越大（低于10）性能越优。使用大的 buffer_pool 时，为1时 的表现最棒 | 适用于多核处理器，依业务量进行调整。 | |
| innodb_change_buffer_max_size | change buffer在buffer pool中的最大占比，默认25%，最大可设置为50% | 25 | Yes | 如果系统中有严重的insert、update并且还有活跃的delete时，就增大。 针对不更改数据的纯报表系统，可以减小该参数值 |
性能测试脚本实例
#!/usr/bin/env python
# -*- coding: UTF-8 -*-
import pymysql
import time
create_table_sql = """
CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE,
nickname VARCHAR(255) NOT NULL
)
"""
insert_table_sql = """
INSERT INTO users(username, nickname)
VALUES(%s,%s)
"""
conn = pymysql.connect(host='127.0.0.1', user='root',
passwd='123456', db='sbtest', port=4408, charset='utf8')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS users')
cur.execute(create_table_sql)
conn.commit()
print('开始时间: '+ time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()))
start = time.time()
failed = 0 # 插入失败条数
count = 10000 # 插入1W条数据
for i in range(1, count + 1):
username = 'username_' + str(i)
nickname = 'nickname_' + str(i)
try:
cur.execute(insert_table_sql, (username, nickname))
conn.commit()
except:
print('failed')
end = time.time()
print('结束时间: '+ time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()))
print('消耗秒数: ' + str(int(end - start)))
print('失败条数: ' + str(failed)
