InnoDB性能优化
transaction_isolation
解读：事务隔离级别，Oracle, SQL Server等商业知名数据库默认级别为READ-COMMITTED，而MySQL默认为REPEATABLE-READ，它利用自身独有的Gap Lock解决了"幻读"。但也因为Gap Lock的缘故，相比于READ-COMMITTED级别的Record Lock，REPEATABLE-READ的事务并发插入性能受到很大的限制。
设置：隔离级别的选择取决于实际的业务需求（安全与性能的权衡），如果不是金融、电信等事务级别要求很高的业务，完全可以设置成transaction_isolation=READ-COMMITTED。
innodb_buffer_pool_size
解读：InnoDB缓冲池大小，它决定了MySQL可以在内存中缓存多少数据和索引，而不是每次都从磁盘上读取。我们都知道Redis的读写很快，其最重要的原因是它的所有数据都缓存在内存中。试想一下如果innodb_buffer_pool_size足够大，MySQL所有表数据和索引都能被缓存，那将是一种什么体验？
设置：如果是专用的MySQL服务器，一般设置为操作系统内存的75%左右，但至少保留2G内存用于操作系统维护和MySQL异常事件处理。
innodb_buffer_pool_instances
解读：InnoDB缓冲池实例个数，InnoDB缓冲池是通过一整个链表的方式来管理页面（段、簇、页）的，由于互斥锁的存在（保护链表中的页面），高并发事务下，页面的读取和写入就需要锁的竞争和等待。通过设置innodb_buffer_pool_instances，将一整个链表划分为多个，每个缓冲池实例管理自己的页面和互斥，从而提高效率。
设置：如果缓冲池比较大（8G以上），可以按照innodb_buffer_pool_size / innodb_buffer_pool_instances = 1G进行设置，但如果缓冲池特别大（32G以上），可以按照每个实例2~3G进行划分，实例数不是越多越好，多实例代表多线程，线程的开销（CPU、MEM）也得考虑。
innodb_log_file_size
解读：InnoDB日志文件大小（Redo Log），它将事务对InnoDB表的修改记录保存在ib_logfile0、ib_logfile1中。innodb_log_file_size越大，缓冲池中的脏数据需要检查点（checkpoint）进行刷盘的频率就越少，从而减少磁盘IO来降低高并发负载造成的峰值。但日志文件也不是越大约好，由于内存中脏数据刷盘的频率减少，一旦数据库发生异常崩溃，数据库重启时从innodb_log_file中读取数据进行恢复的时间越长。
设置：一般选取业务高峰期一个小时的日志量作为标准，计算过程如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
# 命令
$ mysql -uuser -p -e 'show engine innodb status\G'|grep 'Log sequence number' \
&& sleep 60 \
&& mysql -uuser -p -e 'show engine innodb status\G'|grep 'Log sequence number'
# 输出
Log sequence number 149949388055
Log sequence number 149959622102
# 计算
( 149959622102 - 149949388055 ) / 1024 / 1024 = 10M
10 / 60 * 3600 = 600M
600 / 2 = 300M
# 解释
Log sequence number代表InnoDB运行至今写入日志的总字节数，两次打印之间线程休眠60秒
得到一分钟之内事务日志记录的总量10M，再转换成一个小时的总量600M
因为`ib_logfile0、ib_logfile1`两个文件循环写入，一个文件为300M
最终，innodb_log_file_size=300M
|
innodb_flush_log_at_trx_commit
解读：InnoDB事务日志刷盘时机
- 当0时，事务提交到日志缓冲区，后台Write线程每隔一秒将缓冲区的日志写入系统缓冲区，实际写入物理日志文件的时机取决于操作系统。
- 当1时，事务提交到日志缓冲区，Master线程同步将缓冲区的日志直接写入物理日志文件，这完全符合InnoDB ACID事务标准，数据不会丢失。
- 当2时，事务提交到系统缓冲区，Master线程每隔一秒将系统缓冲区的日志写入物理日志文件。
设置：安全1 > 2 > 0，速度0 > 2 > 1，根据实际业务需求（安全与速度权衡）选择合理的刷盘时机。
sync_binlog
解读：二进制事务日志刷盘时机，需要配合log-bin选项才能记录二进制日志。区别于InnoDB事务日志，二进制事务日志是针对整个MySQL Server的，而InnoDB事务日志只针对InnoDB存储引擎。二进制事务日志的作用一是用于主从复制，二是用于数据恢复。区别于InnoDB事务日志恢复，二进制事务日志是用于误操作的数据恢复，而InnoDB事务日志是用于InnoDB存储引擎的崩溃恢复。
-
当0时，将由操作系统控制binlog_cache的刷盘时机。
-
当1时，所有事务开始、提交阶段，都会同步写入磁盘，这是最安全的方式。如果设置innodb_flush_log_at_trx_commit = 1, sync_binlog = 1，这是使用InnoDB事务最安全可靠的方式。
-
当N时，事务每提交N次，同步写入一次二进制日志。设置：如果MySQL是单机，可以考虑sync_binlog=0；如果是主从，且每秒事务并发量低，考虑sync_binlog=1；事务并发量很高，考虑sync_binlog=N，N的选取可以通过统计业务正常时期的OPS。
innodb_file_per_table
解读：InnoDB独立表空间，innodb_file_per_table = ON表示每张表在独立的物理文件中（.ibd）存储数据和索引，innodb_file_per_table = OFF表示所有表都共享表空间即一个物理文件（ibdata1）。如果通过drop/truncate table操作，独立表空间的物理存储会立即被回收（删除/初始化），而共享表空间不会被回收且只会一直增大。
设置：innodb_file_per_table = ON，但需要注意的是，独立表空间只存储数据和索引，如回滚日志缓冲（Undo Log）、插入索引缓冲（Insert Buffer）、二次写缓冲（Doublewrite Buffer）等还是放在共享表空间。
query_cache_size
解读：查询缓存大小，它是为了在追踪表的数据未发生变化时，本次查询命中之前的查询语句，从而跳过解析、优化、执行阶段，直接返回缓冲池中的数据。但实际在OLTP系统中，极少能命中查询缓存（前提是数据库中的数据变化频率很小），因为一旦数据有变则缓存失效。且因为查询缓存会跟踪所有表的变化，它也会成为整个数据库的瓶颈（资源竞争点）。
设置：query_cache_size = 0，同时配合设置query_cache_type = 0，MySQL5.7.20以上、MySQL8.0会直接弃用所有查询缓存配置项。
max_connections
解读：最大连接数，当max_connections设置太小时（默认151），MySQL可能会报错Too many connections。当max_connections设置太大时（1000以上），操作系统可能忙于线程间的切换而失去响应。
设置：每个连接都会消耗一定内存，计算过程如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
# 计算每个线程平均消耗的内存
SELECT CONCAT(ROUND(SUM(VARIABLE_VALUE)/(1024*1024)),'M') AS 'per_connection'
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN ('read_buffer_size', 'read_rnd_buffer_size',
'sort_buffer_size', 'join_buffer_size', 'thread_stack',
'max_allowed_packet', 'net_buffer_length');
# 当前连接数量
mysql> SHOW STATUS LIKE 'threads_connected';
# 内存
$ top -c|grep mysqld
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
24411 mysql 20 0 8327348 6.458g 7680 S 36.7 85.4 831:56.14 /usr/sbin/mysqld
|
thread_cache_size
解读：线程缓存大小，客户端连接时，MySQL会为每个连接分配一个线程，通过设置thread_cache_size = N，MySQL可以重复利用保存在缓存中的N个线程，从而改善频繁的线程创建销毁的开销。同时，应用如果有连接池复用设置，那也会改善MySQL Server的线程开销。
设置：可以通过监控SHOW STATUS LIKE ‘threads_connected’的数量，确定每天的平均连接数。
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
|
mysql> show status like '%thread%';
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| Delayed_insert_threads | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 2 |
| Threads_connected | 216 |
| Threads_created | 2076 |
| Threads_running | 3 |
+------------------------------------------+-------+
# 如果Threads_created很大，可以考虑加大thread_cache_size值
mysql> show variables like '%thread%';
+-----------------------------------------+---------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------+
| innodb_purge_threads | 1 |
| innodb_read_io_threads | 4 |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_write_io_threads | 4 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| myisam_repair_threads | 1 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | -1 |
| pseudo_thread_id | 17534 |
| thread_cache_size | 13 |
| thread_concurrency | 10 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 |
+-----------------------------------------+---------------------------+
|
my.cnf参考配置
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
|
# /etc/my.cnf for mysql 5.6.x
# add by cd.net on 2022-01-11
# ---------------------------------
[mysqld]
datadir = /home/dbcenter/mysql-server
# 最好加上这个，否则连接会巨慢无比，大量超时待认证的连接
skip_name_resolve
skip_host_cache
# 连接数
max_connections = 500
max_connect_errors = 500
# 线程缓存池大小
thread_cache_size = 100
# 数据+索引 缓存大小
innodb_buffer_pool_size = 32G
join_buffer_size = 6G
sort_buffer_size = 4G
read_rnd_buffer_size = 4G
# 允许接收的数据包最大值，比如这么大的sql语句
max_allowed_packet = 128M
# 查询缓存，都关闭
query_cache_size = 0
query_cache_type = 0
# query slow
long_query_time = 1
slow-query-log = 1
slow-query-log-file = /home/dbcenter/mysql-server/query-slow.log
# replication +++++++++++++++++++++++++++++++++++++
# 不同服务器的server-id一定不能一样
server-id = 1158
# 开启binlog日志
log_bin = mysql-bin
binlog_format = mixed
# 这里的参数根据需要调节，控制MySQL高可用安全级别，根据业务特定在性能和数据一致性方面做决策
# InnoDB在复制中最大的持久性和一致性，[每次commit都 write cache & flush disk]
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
# 事务日志文件大小
innodb_log_file_size = 512M
# 忽略几个mysql自带的DB变动日志
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
# 同时也不复制这些表
replicate_wild_ignore_table = mysql.%
replicate_wild_ignore_table = information_schema.%
replicate_wild_ignore_table = performance_schema.%
# mysql 8.x 以上不能有下面这句
innodb_file_format = Barracuda
innodb_file_per_table = ON
performance_schema = OFF
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log = /var/log/mysqld_multi.log
!includedir /etc/my.cnf.d
|
（完）