要做优化工作首先要了解 mysql 的架构设计，针对每个功能点做优化

一、整体架构优化

1、连接管理器

参数概览

mysql> show variables like '%conn%';
+-----------------------------------------------+-----------------+
| Variable_name                                 | Value           |
+-----------------------------------------------+-----------------+
| character_set_connection                      | utf8            |
| collation_connection                          | utf8_general_ci |
| connect_timeout                               | 10              |
| disconnect_on_expired_password                | ON              |
| init_connect                                  |                 |
| max_connect_errors                            | 1000000         |
| max_connections                               | 2000            |
| max_user_connections                          | 0               |
| performance_schema_session_connect_attrs_size | -1              |
+-----------------------------------------------+-----------------+
9 rows in set (0.00 sec)

open_files_limit=1000000 # 文件描述符数量限制
character_set_connection=utf8mb4 # 连接使用的编码
collation_connection=utf8mb4_general_ci # 字符集
connect_timeout=10
init_connect=
max_connect_errors=100 # 连续失败的连接超过max_connect_errors会阻止这台主机后续的所有请求
max_connections=151 # MySql的最大连接数
max_user_connections=0 # 指每个数据库用户的最大连接, 默认值为：0不受限制
back_log=2000 # 当MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log

open_files_limit=1000000

在/etc/my.cnf加入open_files_limit=8192后重启MySQL后查看不起作用(重点在于操作系统的文件打开数是否够)

如果my.cnf里配置了open_files_limit，open_files_limit最后取值为 /etc/my.cnf 中 open_files_limit，max_connections * 5， wanted_files=10+max_connections+table_cache_size*2 三者中的最大值。

如果my.cnf里如果没配置了open_files_limit，open_files_limit最后取值为max_connections * 5，10+max_connections+table_cache_size*2，ulimit -n中的最大者

max_connections=2000

max_connections是指MySql的最大连接数。

如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量。
当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。

可以过’conn%’通配符查看当前状态的连接数量，以定夺该值的大小。

MySQL服务器允许的最大连接数16384；

查看系统当前最大连接数：

show variables like ‘max_connections’;

max_user_connections=0

max_user_connections是指每个数据库用户的最大连接

针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。
简单说是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。

目前默认值为：0不受限制。

查看max_user_connections值

show variables like ‘max_user_connections’;

max_connect_errors=100

如果MySQL服务器连续接收到了来自于同一个主机的请求，而且这些连续的请求全部都没有成功的建立连接就被中断了，当这些连续的请求的累计值大于max_connect_errors的设定值时，MySQL服务器就会阻止这台主机后续的所有请求。相信一开始你看到这些资料，也会被“many successive connection requests from a host are interrupted without a successful connection”给弄懵，其实这个就是因为由于网络异常而中止数据库连接

back_log=2000

back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。

当MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log。
如果等待连接的数量超过back_log，将不被授予连接资源。
将会报：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时。

back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效，查看当前系统的TCP/IP连接的侦听队列的大小命令：
cat /proc/sys/net/ipv4/tcp_max_syn_backlog目前系统为1024。

查看mysql 当前系统默认back_log值，命令：
show variables like ‘back_log’; 查看当前数量

thread_cache_size

默认的thread_cache_size=8

根据物理内存设置规则如下：
1G —> 8
2G —> 16
3G —> 32 >3G —> 64

mysql> show status like ‘thread%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 0 | <—当前被缓存的空闲线程的数量
| Threads_connected | 1 | <—正在使用（处于连接状态）的线程
| Threads_created | 1498 | <—服务启动以来，创建了多少个线程
| Threads_running | 1 | <—正在忙的线程（正在查询数据，传输数据等等操作）
+——————-+——-+

查看开机起来数据库被连接了多少次？

mysql> show status like ‘%connection%’;
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| Connections | 1504 | –>服务启动以来，历史连接数
| Max_used_connections | 2 |
+———————-+——-+

通过连接线程池的命中率来判断设置值是否合适？命中率超过90%以上,设定合理。

(Connections - Threads_created) / Connections * 100 %

2、查询缓存

查询缓存是把查询 sql 作为 key，查询结果作为 value 缓存；因为 sql 多变，而且查询结果一般比较大；所以缓存的意义不大，一般关掉查询缓存

之所以查询缓存并没有能起到提升性能的做用，客观上有如下两点原因：
1、把SQL语句的hash值作为键，SQL语句的结果集作为值；这样就引起了一个问题如 select user from mysql.user 和cSELECT user FROM mysql.user 这两个将会被当成不同的SQL语句，这个时候就算结果集已经有了，但是一然用不到；

2、当查询所基于的低层表有改动时与这个表有关的查询缓存都会作废、如果对于并发度比较大的系统这个开销是可观的；对于作废结果集这个操作也是要用并发访问控制的，就是说也会有锁。并发大的时候就会有Waiting for query cache lock 产生；

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.01 sec)

have_query_cache YES # 表示这个mysql版本是否支持查询缓存
query_cache_limit 1048576 # 表示单个结果集所被允许缓存的最大值
query_cache_min_res_unit 4096 # 每个被缓存的结果集要占用的最小内存
query_cache_size 1048576 # 设置query缓冲区的大小
query_cache_type OFF # 是否开启查询缓存
query_cache_wlock_invalidate OFF # 如果某个数据表被其他的连接锁住，是否仍然从查询缓存中返回结果

query_cache_type

query_cache_type=0（OFF）关闭

query_cache_type=1（ON）缓存所有结果，除非select语句使用SQL_NO_CACHE禁用查询缓存

query_cache_type=2(DEMAND)，只缓存select语句中通过SQL_CACHE指定需要缓存的查询

修改方法：

vi /etc/my.cnf,加入如下行:

query_cache_type =2

query_cache_size

设置query缓冲区的大小，一般关掉

query_cache_size: 主要用来缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集。

Query Cache也有一个致命的缺陷，那就是当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失

Query Cache的使用需要多个参数配合，其中最为关键的是query_cache_size和query_cache_type，
前者设置用于缓存 ResultSet的内存大小，后者设置在何场景下使用Query Cache

可以通过命令：
show status like ‘Qcache_%’;查看目前系统Query catch使用大小

计算缓存命中率

mysql> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 1031352  |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 94462672 |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+

Qcache_free_blocks: 表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。
Qcache_free_memory: 查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整。
Qcache_hits: 表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
Qcache_inserts: 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
Qcache_lowmem_prunes: 该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。
Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。
Qcache_queries_in_cache: 当前缓存中缓存的查询数量。
Qcache_total_blocks: 当前缓存的block数量。

内存碎片的产生。当一块分配的内存没有完全使用时，MySQL会把这块内存Trim掉，把没有使用的那部分归还以重复利用。比如，第一次分配4KB,只用了3KB，剩1KB，第二次连续操作，分配4KB，用了2KB，剩2KB，这两次连续操作共剩下的 1KB+2KB=3KB，不足以做个一个内存单元分配，这时候，内存碎片便产生了。使用flush query cache，可以消除碎片

下面是命中率和内存使用率的一些算法

query_cache_min_res_unit的估计值：(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

查询缓存命中率 ≈ (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

查询缓存内存使用率 ≈ (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

二、存储引擎 InnoDB 优化

Mysql5.6

InnoDB 的存储引擎总体分为内存结构和磁盘结构

1、内存结构配置

1.1、Buffer Pool

功能介绍

Buffer Pool是主内存中的一个区域，在InnoDB访问表和索引数据时会在其中进行缓存。Buffer Pool允许直接从内存中直接处理经常使用的数据，从而加快了处理速度。在专用服务器上，通常将多达80％的物理内存分配给缓冲池。

为了提高大容量读取操作的效率，Buffer Pool被分为多个页面，这些页面可能包含多个行。为了提高缓存管理的效率，Buffer Pool被实现为页面的链接列表。使用LRU算法的变体将很少使用的数据从缓存中老化掉

图14.2缓冲池列表

该算法将大量页面保留在新的子列表中。旧的子列表包含较少使用的页面。这些页面是驱逐的候选对象。

默认情况下，该算法的运行方式如下：

3/8的缓冲池专用于旧的子列表。
列表的中点是新子列表的尾部与旧子列表的头相交的边界。
当InnoDB将页面读入缓冲池时，它首先将其插入中点（旧子列表的头部）。可以读取页面，因为它是用户启动的操作（例如SQL查询）所必需的，或作为的自动执行的预读操作的一部分 InnoDB。
访问旧子列表中的页面使其变为“ 年轻 ”，将其移至新子列表的头部。如果由于用户启动的操作而需要读取页面，则将立即进行首次访问，并使页面年轻。如果由于预读操作而读取了该页面，则第一次访问不会立即发生，并且在退出该页面之前可能根本不会发生。
随着数据库的运行，通过移至列表的尾部，缓冲池中未被访问的页面将“ 老化 ”。新的和旧的子列表中的页面都会随着其他页面的更新而老化。随着将页面插入中点，旧子列表中的页面也会老化。最终，未使用的页面到达旧子列表的尾部并被逐出。

默认情况下，查询读取的页面会立即移入新的子列表，这意味着它们在缓冲池中的停留时间更长。例如，针对mysqldump操作或SELECT不带WHERE子句的语句执行的表扫描可以将大量数据带入缓冲池，并驱逐同等数量的旧数据，即使不再使用新数据也是如此。同样，由预读后台线程加载且仅访问一次的页面将移到新列表的开头。这些情况可能会将常用页面推送到旧的子列表，在此它们会被逐出。有关优化此行为的信息，请参见第14.8.3.2节“使缓冲池扫描具有抵抗力”和第14.8.3.3节“配置InnoDB缓冲池预取（预读）”。

InnoDB标准监视器输出在BUFFER POOL AND MEMORY有关缓冲池LRU算法操作的部分中包含几个字段。有关详细信息，请参阅使用InnoDB Standard Monitor监视缓冲池。

参数配置

mysql> show variables like '%innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 1688207360     |
+-------------------------------------+----------------+
8 rows in set (0.00 sec)

参数说明：

innodb_buffer_pool_dump_at_shutdown：在 mysqld 关闭时把热数据dump到本地磁盘；

innodb_buffer_pool_load_at_startup：在 mysqld 启动时把热数据加载到内存；

innodb_buffer_pool_dump_now：在 mysqld 关闭时把热数据dump到本地磁盘；

innodb_buffer_pool_load_now：采用手工方式把热数据加载到内存；

innodb_buffer_pool_filename：停止MySQL服务时，MySQL将InnoDB缓冲池中的热数据保存到数据库根目录中，默认文件名为ib_buffer_pool

innodb_buffer_pool_instances：表示InnoDB缓存池被划分到一个区域。适当地增加该参数（例如将该参数值设置为2），此时InnoDB被划分成为两个区域，可以提升InnoDB的并发性能。如果InnoDB缓存池被划分成多个区域，建议每个区域不小于1GB的空间；

innodb_buffer_pool_load_abort：终止Buffer Pool恢复，可以指定负载运行；

innodb_buffer_pool_size：缓存池大小

innodb_buffer_pool_size

只使用InnoDB引擎的MySQL服务器中，根据经验，推荐设置 innodb_buffer_pool_size 为服务器总可用内存的80%；
通过(innodb_buffer_pool_read_requests – innodb_buffer_pool_reads) / innodb_buffer_pool_read_requests * 100% 计算缓存命中率。并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化，命中率越高越好；

设置方法

SET GLOBAL innodb_buffer_pool_size=size_in_bytes;
# 并且
$ vi my.cnf
innodb_buffer_pool_size=size_in_bytes
innodb_buffer_pool_instances=8

效果监控

方法一

mysql> show status like 'innodb_%_read%';           
+---------------------------------------+----------------+
| Variable_name                         | Value          |
+---------------------------------------+----------------+
| Innodb_buffer_pool_read_ahead_rnd     | 0              |
| Innodb_buffer_pool_read_ahead         | 1125395433     |
| Innodb_buffer_pool_read_ahead_evicted | 4003043        |
| Innodb_buffer_pool_read_requests      | 26830062706    |
| Innodb_buffer_pool_reads              | 606103167      |
| Innodb_data_pending_reads             | 0              |
| Innodb_data_read                      | 28370717659136 |
| Innodb_data_reads                     | 1731613719     |
| Innodb_pages_read                     | 1731611205     |
| Innodb_rows_read                      | 170090056704   |
+---------------------------------------+----------------+
10 rows in set (0.01 sec)

参数说明：

Innodb_buffer_pool_reads: 表示从物理磁盘读取页的次数

Innodb_buffer_pool_read_ahead: 预读的次数

Innodb_buffer_pool_read_ahead_evicted: 预读的页，但是没有读取就从缓冲池中被替换的页的数量，一般用来判断预读的效率

Innodb_buffer_pool_read_requests: 从缓冲池中读取页的次数

Innodb_data_read: 总共读入的字节数

Innodb_data_reads: 发起读取请求的次数，每次读取可能需要读取多个页

通过(innodb_buffer_pool_read_requests – innodb_buffer_pool_reads) / innodb_buffer_pool_read_requests * 100% 计算缓存命中率。并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化，命中率越高越好；

方法二

1	mysql> SHOW ENGINE INNODB STATUS\G

InnoDB可以使用访问的标准监视器输出， SHOW ENGINE INNODB STATUS提供有关缓冲池操作的度量。缓冲池度量标准位于BUFFER POOL AND MEMORY“ InnoDB标准监视器”输出中的部分，其外观类似于以下内容：

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2197815296; in additional pool allocated 0
Dictionary memory allocated 155455
Buffer pool size   131071
Free buffers       92158
Database pages     38770
Old database pages 14271
Modified db pages  619
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 322, created 38448, written 42083
0.00 reads/s, 222.30 creates/s, 159.47 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead
0.00/s
LRU len: 38770, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

1.2、Change Buffer

功能介绍

Change Buffer是一种特殊的数据结构，当二级索引页不在缓冲池(Buffer Pool)中时，它们会缓存这些更改。当页面通过其他读取操作加载到缓冲池中时，可能由INSERT， UPDATE或 DELETE操作（DML）导致的缓冲更改将在以后合并。

图14.3Change Buffer

与聚簇索引不同，二级索引通常是非唯一的，并且二级索引中的插入以相对随机的顺序发生。同样，删除和更新可能会影响索引树中不相邻的二级索引页。当稍后通过其他操作将受影响的页读入缓冲池时，合并缓存的更改将避免从磁盘将辅助索引页读入缓冲池所需的大量随机访问I / O。

参数配置

mysql> show variables like '%change_buffer%';      
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
| innodb_change_buffering       | all   |
+-------------------------------+-------+
2 rows in set (0.00 sec)

参数说明：

innodb_change_buffering：

all: 默认值：缓冲区插入，删除标记操作和清除。

none: 不要缓冲任何操作。

inserts: 缓冲区插入操作。

deletes: 缓冲区删除标记操作。

changes: 缓冲插入和删除标记操作。

purges: 缓冲在后台发生的物理删除操作。

innodb_change_buffer_max_size：Change Buffer的最大大小配置为Buffer Pool总大小的百分比。默认情况下， innodb_change_buffer_max_size设置为25。最大设置为50

效果监控

方法一

1	mysql> SHOW ENGINE INNODB STATUS\G

Change Buffer状态信息位于INSERT BUFFER AND ADAPTIVE HASH INDEX 标题下，并显示类似以下内容：

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 3340871, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

方法二

mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages, 
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
       (SELECT ((change_buffer_pages/total_pages)*100)) 
       AS change_buffer_page_percentage;
+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
|                  25 |        8192 |                        0.3052 |
+---------------------+-------------+-------------------------------+

1.3、Log Buffer 配置

日志缓冲区是存储区域，用于保存要写入磁盘上的日志文件的数据。日志缓冲区大小由innodb_log_buffer_size变量定义。默认大小为16MB。日志缓冲区的内容会定期刷新到磁盘。大型的日志缓冲区使大型事务能够运行，而无需在事务提交之前将Redo Log数据写入磁盘。因此，如果您有更新，插入或删除许多行的事务，则增加日志缓冲区的大小可以节省磁盘I / O。

该 innodb_flush_log_at_trx_commit 变量控制如何将日志缓冲区的内容写入并刷新到磁盘。该 innodb_flush_log_at_timeout 变量控制日志刷新频率。

mysql> show variables like '%log_buffer%';      
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| innodb_log_buffer_size | 8388608 |  此时为8M = 8 * 1024 * 1024
+------------------------+---------+
1 row in set (0.01 sec)

mysql> show variables like '%innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |  
+--------------------------------+-------+
1 row in set (0.00 sec)

innodb_flush_log_at_trx_commit 参数解释：

0（延迟写）： log_buff –每隔1秒–> log_file —实时—> disk

1（实时写，实时刷）： log_buff —实时—> log_file —实时—> disk

2（实时写，延迟刷）： log_buff —实时—> log_file –每隔1秒–> disk

0：最快减少mysql写的等待 1：最大安全性,不会丢失数据 2：折中，减少操作系统文件写入等待时间

1.4、Adaptive Hash Index

功能介绍

InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应（adaptive）的。自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

根据InnoDB的官方文档显示，启用自适应哈希索引后，读取和写入速度可以提高2倍；对于辅助索引的连接操作，性能可以提高5倍。在我看来，自适应哈希索引是非常好的优化模式，其设计思想是数据库自优化（self-tuning），即无需DBA对数据库进行调整。

Adaptive Hash Index是针对B+树Search Path的优化，因此所有会涉及到Search Path的操作，均可使用此Hash索引进行优化，这些可优化的操作包括：Unique Scan/Range Scan(Locate First Key Page)/Insert/Delete/Purge等等，几乎涵盖InnoDB所有的操作类型。

通过参数innodb_adaptive_hash_index来禁用或启动此特性，默认为开启

参数配置

mysql> show variables like '%adaptive_hash%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

2、操作系统缓存

##　3、磁盘结构配置

Mysql5.6

3.1、System Tablespace

功能介绍

系统表空间是 InnoDB数据字典，Doublewrite Buffer，Change Buffer和 Undo Log的存储区。如果在系统表空间中创建表，而不是在每个表文件中创建表，则它也可能包含表和索引数据。

系统表空间可以具有一个或多个数据文件。默认情况下，ibdata1在数据目录中创建一个名为的系统表空间数据文件。系统表空间数据文件的大小和数量由innodb_data_file_path启动选项定义。有关配置信息，请参阅《系统表空间数据文件配置》。

参数配置

mysql> show variables like '%innodb_data%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir  |                        |
+-----------------------+------------------------+
2 rows in set (0.00 sec)

mysql> show variables like '%innodb_autoextend_increment%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 8    |
+-----------------------------+-------+
1 row in set (0.00 sec)

ibdata1:12M:autoextend 含义：

innodb_data_home_dir 为空，表示在默认目录 /var/lib/mysql 下创建一个文件 ibdata1，初始大小为12MB，如果快达到12MB时，以innodb_autoextend_increment设置的增量 8MB 根据需要增加空间。

1、增大系统表空间

假设 /var/lib/mysql 所在的磁盘将满，需要扩展系统空间，方法如下：

停止 mysql 服务；
vi /etc/my.cnf

1 2	[mysqld] innodb_data_file_path=/var/lib/mysql/ibdata1:76M;/disk2/ibdata2:50M:autoextend

/disk2/ 为新磁盘；autoextend 只能跟在最后一个文件后面；

启动 mysql 服务；

2、减小系统表空间

您不能从系统表空间中删除数据文件。要减小系统表空间大小，请使用以下过程：

使用mysqldump转储所有 InnoDB表，包括模式中的InnoDB表 mysql。使用以下查询标识模式中的InnoDB表 mysql：

mysql> SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';
+----------------------+
| table_name           |
+----------------------+
| innodb_index_stats   |
| innodb_table_stats   |
| slave_master_info    |
| slave_relay_log_info |
| slave_worker_info    |
+----------------------+
5 rows in set (0.00 sec)

停止服务器。
删除所有现有的表空间文件（*.ibd），包括 ibdata和ib_log 文件。不要忘记删除架构*.ibd 中表的文件mysql。
删除表的所有.frm文件 InnoDB。
为新系统表空间配置数据文件。请参阅系统表空间数据文件配置。
重新启动服务器。
导入转储文件。

3、在l Linux 和 Unix上使用原始磁盘分区

修改 /etc/my.cnf ；

1
2
3

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

重启 mysql；

InnoDB 的 newraw关键字将初始化新分区。重启以后，不要创建任何新表。否则下次重启将再次初始化分区，丢失所有数据；

再次修改 /etc/my.cnf ；

1
2
3

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw

重启 mysql 服务器。现在允许创建表。

3.2、File-Per-Table-Tablespaces

功能介绍

每表文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统中自己的数据文件中。

.frm 文件：表结构信息、字典信息；
.idb 文件：数据信息、索引信息；

参数配置

mysql> show variables like '%innodb_file%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
| innodb_file_per_table    | ON       |
+--------------------------+----------+
4 rows in set (0.00 sec)

innodb_file_per_table： ON-启动独立表空间，OFF-启用共享表空间

# 临时表更
mysql> SET GLOBAL innodb_file_per_table=ON;
# 永久变更
vi /etc/my.cnf
innodb_file_per_table=1

3.3、Undo Tablespaces

功能介绍

回滚段

InnoDB采用回滚段的方式来维护undo log的并发写入和持久化。回滚段实际上是一种 Undo 文件组织方式，每个回滚段又有多个undo log slot。

一共128个回滚段，每个回滚段维护了一个段头页，在该page中又划分了1024个slot (TRX_RSEG_N_SLOTS)，每个slot又对应到一个undo log对象，因此理论上InnoDB最多支持 96 * 1024个普通事务。

rseg0预留在系统表空间ibdata中;
rseg 1~rseg 32这32个回滚段存放于临时表的系统表空间中;
rseg33~ 则根据配置存放到独立undo表空间中（如果没有打开独立Undo表空间，则存放于ibdata中）

Undo Log

undo Log是与单个读写事务关联的 undo Log 记录的集合。undo Log 记录包含如何撤消事务对聚簇索引记录的最新更改的信息。如果另一个事务读取原始数据，就需要在 undo Log记录读取。

undo Log存在于 undo log segment。默认情况下，undo log segment 实际上是 system tablespace 的一部分，但它们也可以驻留在 undo log tablespace 中。有关更多信息，请参见第14.6.3.3节“撤消表空间”。

InnoDB支持128个回滚段，通过参数 innodb_rollback_segments 定义；
一个 rollback segment 支持的事务数取决（ rollback segment 中 undo slots 的数量）和（每个事务所需的 undo log 数）；
一个rollback segment 中 undo slots 的数量又会因为 innodb_page_size 不同而不同；

InnoDB Page Size	rollback segment 中的 undo slots 数（innodb_page_size/ 16）
`4096 (4KB)`	`256`
`8192 (8KB)`	`512`
`16384 (16KB)`	`1024`

以下每种操作类型，一个事务最多可以分配两个 undo Log：

INSERT 操作在事务提交前只对当前事务可见，因此产生的Undo日志可以在事务提交后直接删除；
UPDATE和 DELETE 操作产生的Undo日志被归成一类，即update_undo；

undo Log根据需要分配。例如，一个事务执行INSERT， UPDATE 和 DELETE 操作被分配了两个undo Log，仅执行INSERT操作的事务被分配有一个 undo Log；

分配给事务的Undo Log在其持续时间内始终与事务相关；

给定上述因素，可以使用以下公式来估计InnoDB能够支持的并发读写事务数。

注意：在达到 InnoDB 能够支持的并发读写事务数之前，事务可能会遇到并发事务限制错误。当分配给事务的 rollback segment 用完 undo slots 时，就会发生这种情况。在这种情况下，请尝试重新运行事务

如果每个事务执行任一个 insert 或 update 或 delete 操作，InnoDB支持的并发读-写事务的数目：
1
(innodb_page_size / 16) * innodb_rollback_segments
如果每个事务执行任一个 insert 和（ update 或 delete）操作，InnoDB支持的并发读-写事务的数目
1
(innodb_page_size / 16 / 2) * innodb_rollback_segments

在 mysql 5.6 中开始支持把 undo log 分离到独立的表空间，并放到单独的文件目录下；这给我们部署不同IO类型的文件位置带来便利，对于并发写入型负载，我们可以把undo文件部署到单独的高速存储设备上；

undo log 的 i/o 模式决定最好使用 SSD 来存储；

innodb事务日志包括redo log和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。

undo log不是redo log的逆向过程，其实它们都算是用来恢复的日志：

redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

参数配置

mysql> show variables like '%undo%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_undo_directory   | .     |
| innodb_undo_logs        | 128   |
| innodb_undo_tablespaces | 0     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show variables like '%innodb_rollback_segments%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_rollback_segments | 128   |
+--------------------------+-------+
1 row in set (0.00 sec)

innodb_undo_tablespaces

用于设定创建的undo表空间的个数，仅在初始化 mysql 实例时才能配置此选项，此后无法更改；

默认值为0，表示 不独立设置undo的 tablespace，默认记录到 ibdata 中；否则，则在undo目录下创建这么多个undo文件，例如假定设置该值为16，那么就会创建命名为 undo001 ~ undo016的 undo tablespace 文件，每个文件的默认大小为 10M

修改该值可能会导致Innodb无法完成初始化；

innodb_undo_logs

用于表示回滚段的个数（早期版本的命名为innodb_rollback_segments ），该变量可以动态调整，但是物理上的回滚段不会减少，只是会控制用到的回滚段的个数;

默认为128个回滚段

innodb_undo_directory

当开启独立undo表空间时，指定undo文件存放的目录

如果我们想转移undo文件的位置，只需要修改下该配置，并将undo文件拷贝过去就可以了。

3.4、Redo Log

https://dev.mysql.com/doc/refman/5.6/en/innodb-redo-log.html

功能介绍

redo log 是一种基于磁盘的，在 mysql 崩溃恢复时用来纠正未完成事务的数据结构。

默认情况下，redo log 在磁盘上表现为两个文件 ib_logfile0 和 ib_logfile1。mysql 把受影响的数据编码后以循环方式写这两文件。

优化建议：确保 redo log 日志足够大，甚至和 buffer pool 一样大。当 InnoDB 把 redo log 文件写满时，它必须把 buffer pool 中的数据写入磁盘 checkpoint。如果 redo log 太小会导致很多没必要的磁盘写入。尽管 redo log 太大会导致 mysql 在启动恢复时耗时更长，不过现在的优化以后它的速度还是挺快的，可以放心使用；

参数配置

1、确保 innodb_fast_shutdown 不是2

1	mysql> SET GLOBAL innodb_fast_shutdown=1;

2、停止 mysql

1	systemctl stop mysqld

3、将 ib_logfile0 和 ib_logfile1 复制到其他安全的空间，防止被写入脏数据；

4、删除掉 ib_logfile0 和 ib_logfile1；

5、修改 /etc/my.cnf，改变 redo log 文件的数量或者大小；

vi /etc/my.cnf

[mysqld]
# redo log 文件大小
innodb_log_file_size=536870912
# redo log 文件个数
innodb_log_files_in_group=2

6、启动 mysql，mysqld 将会创建新的ib_logfile0 和 ib_logfile1；

和其他AICD的数据库搜索引擎一样，InnoDB会在提交一个事务之前 flush redo log。InnoDB 使用 group commit 功能将多个事务的提交请求一起flush，而避免每个提交只有一个flush，从而提高吞吐量。

效果监控

mysql> show variables like '%innodb_log_file%';
+---------------------------+-----------+
| Variable_name             | Value     |
+---------------------------+-----------+
| innodb_log_file_size      | 536870912 |
| innodb_log_files_in_group | 2         |
+---------------------------+-----------+
2 rows in set (0.00 sec)

3.5、InnoDB Data Dictionary

InnoDB的数据字典有内部系统表组成，这些表包含 tables，indexs，和 table columns。元数据在物理上位于 system tablespace。由于历史原因，数据字典元数据在一定程度上与 InnoDB 表的元数据文件（.frm 文件）中存储的信息重叠。

3.6、Doublewrite Buffer

功能介绍

doublewrite buffer 是 InnoDB 的 system tablespace 中的一个区域。在把 buffer pool 中的内容写入对应的 data file 之前，先要把数据写入 doublewirte buffer 中；

如果 storage 或者 mysqld 在写 page的过程中进程崩溃，InnoDB 可以在 doublewrite buffer 中找到完成的 page 的拷贝用来恢复；

尽管数据总是写两次，但 doublewrite buffer 不需要两倍的 I/O开销或两倍的 I/O 操作。数据作为一个大的连续块写入 doublwrite buffer，只需对操作系统执行一次 fsync() 调用；

doublewirte buffer 默认启用，如果想关掉它，设置 innodb_doublewrite=0

效果监控

mysql> show global status like '%dblwr%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Innodb_dblwr_pages_written | 91756 |
| Innodb_dblwr_writes        | 18741 |
+----------------------------+-------+
2 rows in set (0.00 sec)

关注点：Innodb_dblwr_pages_written / Innodb_dblwr_writes

　　开启doublewrite后，每次脏页刷新必须要先写doublewrite，而doublewrite存在于磁盘上的是两个连续的区，每个区由连续的页组成，一般情况下一个区最多有64个页，所以一次IO写入应该可以最多写64个页。

　　而根据以上系统Innodb_dblwr_pages_written与Innodb_dblwr_writes的比例来看，大概在3左右，远远还没到64(如果约等于64，那么说明系统的写压力非常大，有大量的脏页要往磁盘上写)，所以从这个角度也可以看出，系统写入压力并不高。

三、常见参数介绍

back_log

含义：

MySQL可以具有的未完成连接请求数。也受操作系统限制，默认值-1表示自动调整大小。默认值是这么计算的(50 + (max_connections / 5))，范围(1-65535) ;

back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。将会报：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时.

back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效，查看当前系统的TCP/IP连接的侦听队列的大小命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog，目前系统为1024。对于Linux系统推荐设置为大于512的整数;

修改方式：

# 修改 TCP/IP连接的侦听队列的大小
cat /proc/sys/net/ipv4/tcp_max_syn_backlog
vi /etc/sysctl.conf
# 添加: 
net.ipv4.tcp_max_syn_backlog = 2048
# 使生效
sysctl -p
cat /proc/sys/net/ipv4/tcp_max_syn_backlog

vi /etc/my.cnf
[mysqld]
back_log=2000
在mysql中查看
show variables like 'back_log';

open_files_limit

含义

在/etc/my.cnf加入open_files_limit=8192后重启MySQL后查看不起作用(重点在于操作系统的文件打开数是否够)

my.cnf里如果配置了open_files_limit

open_files_limit最后取值为配置文件 open_files_limit，max_connections5， wanted_files= 10+max_connections+table_cache_size2 三者中的最大值。

如果my.cnf里如果没配置了open_files_limit

open_files_limit最后取值为max_connections5，10+max_connections+table_cache_size2，ulimit -n中的最大者

修改方案：

# 永久更改最大打开文件描述符数
# 第一步
# vi /etc/security/limits.conf
*  soft nofile 1000000
*  hard nofile 1000000       #星号表示对所有用户生效

# 第二步
vi /etc/my.cnf
[mysqld_safe]
open_files_limit=1000000

# 第三步
vi /etc/systemd/system/mysql.service
[Service]
LimitNOFILE=1000000

# 第四步
systemctl daemon-reload
# service mysqld restart
systemctl restart mysqld.service
reboot       #重启系统

# 第五步（查看是否生效）
ulimit -n
# sysctl fs.file-max
#PID是应用的进程ID，在输出结果中查看"Max open files"的显示值
ps aux | grep mysql
cat /proc/${mysql-pid}/limits

cat /proc/${mysql-pid}/limits

直接设置生效

character_set_server=utf8
collation_server=utf8_general_ci
enforce_gtid_consistency=ON
expire_logs_days=3
innodb_open_files=2000
open_files_limit=1000000
performance_schema_digests_size=10000
performance_schema_events_stages_history_long_size=10000
performance_schema_events_statements_history_long_size=10000
performance_schema_events_waits_history_long_size=10000
performance_schema_max_cond_instances=3504
performance_schema_max_file_instances=7693
performance_schema_max_mutex_instances=15906
performance_schema_max_rwlock_instances=9102
performance_schema_max_socket_instances=322
performance_schema_max_table_handles=4000
performance_schema_max_table_instances=12500
performance_schema_max_thread_instances=402
system_time_zone=CST
table_definition_cache=1400
table_open_cache=2000

host_cache_size

含义

–skip-host-cache

为加快主机名到IP解析禁用使用内部主机缓存。在这种情况，每次客户连接，服务器执行DNS查找。见 Section 8.11.5.2, “DNS Lookup Optimization and the Host Cache”。使用 –skip-host-cache类似设置系统变量host_cache_size的值为0，但host_cache_size更加灵活，因为在运行时它也可以调整大小、启用或禁用主机缓存,不只是在服务器启动事。如果使用 –skip-host-cache启动服务，它不能阻止host_cache_size的改变，但这些改变不生效和缓存是不可用，尽管host_cache_size设置大于0。

修改

1
2
3

vi /etc/my.cnf
[mysqld]
host_cache_size=703

innodb_buffer_pool_size

https://www.cnblogs.com/wanbin/p/9530833.html

MyISAM使用操作系统缓存来缓存数据。InnoDB需要innodb buffer pool中处理缓存。所以非常需要有足够的InnoDB buffer pool空间

MySQL InnoDB buffer pool 里包含什么？
数据缓存
InnoDB数据页面

索引缓存
索引数据

缓冲数据
脏页（在内存中修改尚未刷新(写入)到磁盘的数据）
内部结构
如自适应哈希索引，行锁等。

配置的innodb_buffer_pool_size是否合适？
当前配置的innodb_buffer_pool_size是否合适，可以通过分析InnoDB缓冲池的性能来验证。

可以使用以下公式计算InnoDB缓冲池性能：
1
2
>  Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100
>

innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。
innodb_buffer_pool_read_requests：表示从内存中读取逻辑的请求数。

>  root@localhost [(none)] 15:35:31>show status like 'innodb_buffer_pool_read%';
>  +---------------------------------------+-------------+
>  | Variable_name                         | Value       |
>  +---------------------------------------+-------------+
>  | Innodb_buffer_pool_read_ahead_rnd     | 0           |
>  | Innodb_buffer_pool_read_ahead         | 0           |
>  | Innodb_buffer_pool_read_ahead_evicted | 0           |
>  | Innodb_buffer_pool_read_requests      | 4029033624  |
>  | Innodb_buffer_pool_reads              | 91661       |
>  +---------------------------------------+-------------+
>  5 rows in set (0.00 sec)
>  
>  
>  Performance = 91661 / 4029033624 * 100 = 0.0022750120389663
>

意味着InnoDB可以满足缓冲池本身的大部分请求。从磁盘完成读取的百分比非常小。因此无需增加innodb_buffer_pool_size值。

修改

1
2
3

vi /etc/my.cnf
[mysqld]
innodb_buffer_pool_size=9688842240

innodb_data_file_path

innodb_data_file_path用来指定innodb tablespace文件，如果我们不在My.cnf文件中指定innodb_data_home_dir和innodb_data_file_path那么默认会在datadir目录下创建ibdata1 作为innodb tablespace

show variables like ‘innodb_data%’;
1
2
3
> innodb_data_file_path	ibdata1:12M:autoextend
> innodb_data_home_dir	
>

修改

1
2
3

vi /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:100M:autoextend

可能引起的问题

2019-11-15 10:58:33 18273 [ERROR] InnoDB: auto-extending data file ./ibdata1 is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 6400 pages, max 0 (relevant if non-zero) pages!
2019-11-15 10:58:33 18273 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2019-11-15 10:58:33 18273 [ERROR] Plugin 'InnoDB' init function returned error.
2019-11-15 10:58:33 18273 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2019-11-15 10:58:33 18273 [ERROR] Unknown/unsupported storage engine: InnoDB
2019-11-15 10:58:33 18273 [ERROR] Aborting

https://blog.csdn.net/u010735147/article/details/82415868
问题分析：从Error看,表空间ibdata1跑在旧的my.cnf下大小65536/64M(64个page=1M,1个page=16k)比现在my.cnf文件中配置的
ibdata1值要小,导致数据文件无法打开，同时InnoDB存储引擎加载失败
解决方式:
1.删除现在ibdata1数据文件,引起另外麻烦,mysqld启动了，但是所有InnoDB表报废，select时提示该表不存在—慎用；
2.注释该设置的参数(InnoDB_data_file_path),MySQLd启用默认设置ibdata1值大小;
3.ibdata1大小扩展方式,网上搜索一把,查询是ibdata1如何瘦身,涉及参数:InnoDB_data_file_path;

innodb_numa_interleave

大家都知道，在运行mysql服务的服务器上，linux系统的内存numa特性是强烈建议关闭的。因为这种特性很容易引起内存泄漏的情况：即发现物理内存还有剩余，但是系统已经开始使用swap内存。

numa内存特性：比如一台机器是有2个处理器，有4个内存块。我们将1个处理器和两个内存块合起来，称为一个NUMA node，这样这个机器就会有两个NUMA node。在物理分布上，NUMA node的处理器和内存块的物理距离更小，因此访问也更快。比如这台机器会分左右两个处理器（cpu1, cpu2），在每个处理器两边放两个内存块(memory1.1, memory1.2, memory2.1,memory2.2)，这样NUMA node1的cpu1访问memory1.1和memory1.2就比访问memory2.1和memory2.2更快。所以使用NUMA的模式如果能尽量保证本node内的CPU只访问本node内的内存块，那这样的效率就是最高的。

其实由于mysql数据库服务器一般只会部署mysql一种服务在运行，而不会部署若干应用在运行。所以一般是希望mysql是独占整个服务器的资源（剔除留给操作系统运行的资源）。所以根据这种业务特性，mysql服务器上是不建议开启numa内存特性。

修改

# 建议关闭
【关闭NUMA的方案】
1、 在mysqld_safe脚本中加上“numactl –interleave all”来启动mysqld
2、 Linux Kernel启动参数中加上numa=off，需要重启服务器
3、 在BIOS层面关闭NUMA
4、 MySQL 5.6.27/5.7.9开始引用innodb_numa_interleave选项

innodb_flush_log_at_trx_commit

一、参数解释
0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。
2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

二、参数修改
找到mysql配置文件mysql.ini，修改成合适的值，然后重启mysql。

三、注意事项
当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。。
当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

四、其他相关
查找资料时候看到其他文章说innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数，当两个参数都设置为1的时候写入性能最差，推荐做法是innodb_flush_log_at_trx_commit=2，sync_binlog=500 或1000

修改

1
2
3

vi /etc/my.cnf
[mysqld]
innodb_flush_log_at_trx_commit=2

innodb_flush_method

https://blog.csdn.net/smooth00/article/details/72725941

fdatasync模式：写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。
O_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成
O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲

修改

1
2
3

vi /etc/my.cnf
[mysqld]
innodb_flush_method=O_DIRECT

此模式，稳定性最高，直接读写磁盘，不经过buffer

innodb_io_capacity

可以提高数据库 tps

innodb_io_capacity 和 innodb_io_capacity_max

这是一个更加高级的调优，只有当你在频繁写操作的时候才有意义（它不适用于读操作，例如 SELECTs）。若你真的需要对它进行调整，最好的方法是要了解系统可以支持多大的 IOPS。譬如，假设服务器有一块 SSD 硬盘，我们可以设置 innodb_io_capacity_max=6000 和 innodb_io_capacity=3000（最大值的一半）。运行 sysbench 或者任何其他基准工具来对磁盘吞吐量来进行基准测试是一个好方法。

然而，我们需要去担心这个选项吗？看下面这张缓冲池的”脏页“

在这种情况下，脏页的总量很大，而且看起来 InnoDB 刷新脏页的速度跟不上脏页的速度。如果我们有一个高速的磁盘子系统（例如：SSD），可以增加 innodb_io_capacity 和 innodb_io_capacity_max 来得到更好的性能。

修改

vi /etc/my.cnf
[mysqld]
innodb_io_capacity=2000
innodb_io_capacity_max=4000

innodb_log_file_size

延伸阅读： https://blog.csdn.net/kai404/article/details/80242262

跟其他数据库管理系统一样，MySQL通过日志来实现数据的持久性（在使用InnoDB存储引擎的前提下）。这确保了当一个事务提交后，其相关数据在崩溃或者服务器掉电的情况下不会丢失。

MySQL的InnoDB 存储引擎使用一个指定大小的Redo log空间（一个环形的数据结构）。Redo log的空间通过innodb_log_file_size和innodb_log_files_in_group（默认2）参数来调节。将这俩参数相乘即可得到总的可用Redo log 空间。尽管技术上并不关心你是通过innodb_log_file_size还是innodb_log_files_in_group来调整Redo log空间，不过多数情况下还是通过innodb_log_file_size 来调节。

为InnoDB引擎设置合适的Redo log空间对于写敏感的工作负载来说是非常重要的。然而，这项工作是要做出权衡的。你配置的Redo空间越大，InnoDB就能更好的优化写操作；然而，增大Redo空间也意味着更长的恢复时间当出现崩溃或掉电等意外时。

恢复时间 : 每1GB的Redo log的恢复时间大约在5分钟左右来估算 ;

关于Redo 空间的使用情况，如果没有安装PMM的话，也可以通过下面的命令来观察每小时的写入量（MB）：
1
2
> a=$(mysql -uuser -p'passwd' -e "show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}'); sleep 60; b=$(mysql -uuser -p'passwd' -e "show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}'); let "res=($b-$a)*60/1024/1024";echo $res
>

修改

1
2
3

vi /etc/my.cnf
[mysqld]
innodb_log_file_size=536870912

innodb_max_dirty_pages_pct

mysql检查点事件受两个因素的制约：一个是amount,另外一个是age.amount主要由innodb_max_dirty_pages_pct参数控制;至于age,主要是由日志文件大小有关 ;

innodb_io_capacity, innodb_max_dirty_pages_pct, innodb_adaptive_flushing这三个参数是为了解决SSD等大容量存储设备的出现而导致innoDB不能很好利用这类设备性能的困境而出现的，本文会结合mysql-5.5.34的源代码进行这三个参数的介绍;

innodb_max_dirty_pages_pct：参数会让InnoDB Buffer Pool刷新数据而不让脏数据的百分比超过这个值；

innodb_max_dirty_pages_pct_lwm：InnoDB会自动维护后台作业自动从Buffer Pool当中清除脏数据，当Buffer Pool中的脏页占用比达到innodb_max_dirty_pages_pct_lwm的设定值的时候，就会自动将脏页清出Buffer Pool；

修改

1	innodb_max_dirty_pages_pct=50

innodb_open_files

限制Innodb能同时打开的表的数量，默认为300，数据库里的表特别多的情况，可以适当增大为1000。innodb_open_files的大小对InnoDB效率的影响比较小。但是在InnoDBcrash的情况下，innodb_open_files设置过小会影响recovery的效率。所以用InnoDB的时候还是把innodb_open_files放大一些比较合适。

修改

1	innodb_open_files=1024

key_buffer_size

key_buffer_size这个参数是用来设置索引块（index blocks）缓存的大小，它被所有线程共享，严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。那我们怎么才能知道key_buffer_size的设置是否合理呢，一般可以检查状态值Key_read_requests和Key_reads，比例key_reads / key_read_requests应该尽可能的低，比如1:100，1:1000 ，1:10000。其值可以用以i下命令查得：
1
2
3
4
5
6
7
8
9
10
11
12
13
> mysql> show status like 'key_read%';
> +-------------------+------------+
> | Variable_name     | Value      |
> +-------------------+------------+
> | Key_read_requests | 3916880184 | 
> | Key_reads         | 1014261    | 
> +-------------------+------------+
> 2 rows in set (0.00 sec)
> 3916880184/1024/1024=?M    //单位为兆
> 我的key_buffer_size值为：
> key_buffer_size=536870912/1024/1024=512M,
> key_reads / key_read_requests=1014261: 3916880184≈1:4000，照上面来看，健康状况还行。
>

修改

1	key_buffer_size=33554432

long_query_time

慢查询阈值

1	long_query_time=3.000000

max_connections

数据库最大连接数，系统支持的最大连接

 mysql> show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 58    |
| Threads_connected | 57    |   ###这个数值指的是打开的连接数
| Threads_created   | 3676  |
| Threads_running   | 4     |   ###这个数值指的是激活的连接数，这个数值一般远低于connected数值
+-------------------+-------+
 
Threads_connected 跟show processlist结果相同，表示当前连接数。准确的来说，Threads_running是代表当前并发数
 
这是是查询数据库当前设置的最大连接数
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+
 
可以在/etc/my.cnf里面设置数据库的最大连接数
[mysqld]
max_connections = 1000

设置

1	max_connections=2000

max_user_connections

用户能最大连接进来的数量

1	max_user_connection=600

max_connect_errors

阅读延伸： https://www.cnblogs.com/kerrycode/p/8405862.html

If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. You can unblock blocked hosts by flushing the host cache. To do so, issue a FLUSH HOSTS statement or execute a mysqladmin flush-hosts command. If a connection is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero. However, once a host is blocked, flushing the host cache is the only way to unblock it. The default is 100.

如上所示，翻译出来的话，大致如下：如果MySQL服务器连续接收到了来自于同一个主机的请求，而且这些连续的请求全部都没有成功的建立连接就被中断了，当这些连续的请求的累计值大于max_connect_errors的设定值时，MySQL服务器就会阻止这台主机后续的所有请求。相信一开始你看到这些资料，也会被“many successive connection requests from a host are interrupted without a successful connection”给弄懵，其实这个就是因为由于网络异常而中止数据库连接

设置

1	max_connect_errors=2000

max_allowed_packet

mysql根据配置文件会限制server接受的数据包大小。

有时候大的插入和更新会受max_allowed_packet 参数限制，导致写入或者更新失败。
1
2
3
4
5
6
7
8
> mysql> show variables like 'max_allowed_packet';
> +--------------------+---------+
> | Variable_name      | Value   |
> +--------------------+---------+
> | max_allowed_packet | 4194304 |
> +--------------------+---------+
> 1 row in set (0.00 sec)
>

设置

1	max_allowed_packet=16777216

max_digest_length ？？？

net_buffer_length

net_buffer_length选项对数据备份及恢复影响
net-buffer-length 可能对mysqldump导出及恢复有影响，对此测试了一下，发现影响很小，基本可以忽略不计
每个客户端连接时。用于维持连接缓冲，初始分配预设值，在有需要时，则会自动扩大到max_allowed_packet大小，然后在回收预设的net_buffer_length大小最小1k 最大1m默认16k

设置

1	net_buffer_length=8192

query_cache_size

延伸阅读：https://blog.csdn.net/u014044812/article/details/78924315

1、原理
MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存，会立刻返回结果，跳过了解析，优化和执行阶段。
查询缓存会跟踪查询中涉及的每个表，如果这写表发生变化，那么和这个表相关的所有缓存都将失效。
但是随着服务器功能的强大，查询缓存也可能成为整个服务器的资源竞争单点。
2、初步设置
默认这个开关是关闭的，就是禁止使用query_cache，查询是否使用语句如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
> mysql> show variables like 'have_query_cache'; 
> +------------------+-------+
> | Variable_name    | Value |
> +------------------+-------+
> | have_query_cache | YES   |
> +------------------+-------+
> 1 row in set (0.00 sec)
> 
> mysql> show variables like 'query_cache%';
> +------------------------------+---------+
> | Variable_name                | Value   |
> +------------------------------+---------+
> | query_cache_limit            | 1048576 |
> | query_cache_min_res_unit     | 4096    |
> | query_cache_size             | 1048576 |
> | query_cache_type             | OFF     |
> | query_cache_wlock_invalidate | OFF     |
> +------------------------------+---------+
> 5 rows in set (0.00 sec)
>

注意这个只是显示，支持query_cache功能而已，默认是关闭的

3、MYSQL如何分配query_cache_size
MySQL用于查询的缓存的内存被分成一个个变长数据块，用来存储类型，大小，数据等信息。
当服务器启动的时候，会初始化缓存需要的内存，是一个完整的空闲块。当查询结果需要缓存的时候，先从空闲块中申请一个数据块大于参数query_cache_min_res_unit的配置，即使缓存数据很小，申请数据块也是这个，因为查询开始返回结果的时候就分配空间，此时无法预知结果多大。
分配内存块需要先锁住空间块，所以操作很慢，MySQL会尽量避免这个操作，选择尽可能小的内存块，如果不够，继续申请，如果存储完时有空余则释放多余的。
4、如何判断是否命中
缓存存放在一个引用表中，通过一个哈希值引用，这个哈希值包括查询本身，数据库，客户端协议的版本等，任何字符上的不同，例如空格，注释都会导致缓存不命中。
当查询中有一些不确定的数据时，是不会缓存的，比方说now(),current_date(),自定义函数，存储函数，用户变量，字查询等。所以这样的查询也就不会命中缓存，但是还会去检测缓存的，因为查询缓存在解析SQL之前，所以MySQL并不知道查询中是否包含该类函数，只是不缓存，自然不会命中。

打开Qcache对读和写都会带来额外的消耗：
a、读查询开始之前必须检查是否命中缓存。
b、如果读查询可以缓存，那么执行完之后会写入缓存。
c、当向某个表写入数据的时候，必须将这个表所有的缓存设置为失效，如果缓存空间很大，则消耗也会很大，可能使系统僵死一段时间，因为这个操作是靠全局锁操作来保护的。
对InnoDB表，当修改一个表时，设置了缓存失效，但是多版本特性会暂时将这修改对其他事务屏蔽，在这个事务提交之前，所有查询都无法使用缓存，直到这个事务被提交，所以长时间的事务，会大大降低查询缓存的命中
一个表可以被许多类型的语句更改，例如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE。
对于InnoDB而言，事物的一些特性还会限制查询缓存的使用。当在事物A中修改了B表时，因为在事物提交之前，对B表的修改对其他的事物而言是不可见的。为了保证缓存结果的正确性，InnoDB采取的措施让所有涉及到该B表的查询在事物A提交之前是不可缓存的。如果A事物长时间运行，会严重影响查询缓存的命中率
查询缓存的空间不要设置的太大。
因为查询缓存是靠一个全局锁操作保护的，如果查询缓存配置的内存比较大且里面存放了大量的查询结果，当查询缓存失效的时候，会长时间的持有这个全局锁。因为查询缓存的命中检测操作以及缓存失效检测也都依赖这个全局锁，所以可能会导致系统僵死的情况

5、
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
> mysql> show status like 'Qcache%'; 
> +-------------------------+---------+
> | Variable_name           | Value   |
> +-------------------------+---------+
> | Qcache_free_blocks      | 1       |
> | Qcache_free_memory      | 1031352 |
> | Qcache_hits             | 0       |
> | Qcache_inserts          | 0       |
> | Qcache_lowmem_prunes    | 0       |
> | Qcache_not_cached       | 7       |
> | Qcache_queries_in_cache | 0       |
> | Qcache_total_blocks     | 1       |
> +-------------------------+---------+
> 8 rows in set (0.00 sec)
>

解析：
Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。
减少碎片：
合适的query_cache_min_res_unit可以减少碎片，这个参数最合适的大小和应用程序查询结果的平均大小直接相关，可以通过内存实际消耗（query_cache_size - Qcache_free_memory）除以Qcache_queries_in_cache计算平均缓存大小。
可以通过Qcache_free_blocks来观察碎片，这个值反应了剩余的空闲块，如果这个值很多，但是
Qcache_lowmem_prunes却不断增加，则说明碎片太多了。可以使用flush query cache整理碎片，重新排序，但不会清空，清空命令是reset query cache。整理碎片期间，查询缓存无法被访问，可能导致服务器僵死一段时间，所以查询缓存不宜太大。
Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整。
Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
Qcache_inserts: 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。
Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。
Qcache_queries_in_cache:当前缓存中缓存的查询数量。
Qcache_total_blocks:当前缓存的block数量。

设置

1 2	query_cache_size=0 query_cache_type=OFF

提高查询缓存的使用率：
如果碎片不是问题，命中率却非常低，可能是内存不足，可以通过 Qcache_free_memory 参数来查看没有使用的内存。
如果2者都没有问题，命中率依然很低，那么说明缓存不适合你的当前系统。可以通过设置
query_cache_size = 0或者query_cache_type 来关闭查询缓存。

read_buffer_size

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 458624 K

read_buffer_size：是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。

read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

read_rnd_buffer_size

MySQL手册里关于read_rnd_buffer_size的解释如下： [ mrr order by ]

sort后，得到的是行数据指针，通过key-value的形式存在，对于MyISAM是数据的偏移量，对于innodb是主键或存储重新查询的全量数据（对于小片的数据是有益的）。

假设sort后的数据使用的是行指针，并且行中的字段能够被转换成固定的大小（除了BLOB/TEXT字段外），MySQL能够使用read_rnd_buffer_size优化数据读取。

因为sort后的数据是以key-value的形式存在的，使用这些行指针去读取数据，将是以指针数据物理的顺序去读取，很大程度上是随机的方式读取数据的。MySQL从　　　 sort_buffer中读取这些行指针数据，然后通过指针排序后存入read_rnd_buffer中，之后再通过指针读取数据时，基本上都是顺序读取了。

read_rnd_buffer_size是很重要的参数，尤其工作在如下场景：

sort_buffer中存的是行指针而不是要查询的数据。

查询的字段中包含Blob/Text字段。

sort后有大量的数据行（limit 10并不能帮助你，因为MySQL是通过指针获取行数据的）

如果你取出很少字段的数据（小于max_length_for_sort_data）,行数据将会全部存储在sort buffer里，因此将不需要read_rnd_buffer_size这个参数。

而如果你查询的字段数据很长（这些字段很可能含有Text/Blob字段），比max_length_for_sort_data还长，read_rnd_buffer_size这个参数将派上用场。

table_open_cache

table_open_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。
通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_open_cache的值。
如果你发现open_tables等于table_open_cache，并且opened_tables在不断增长，那么你就需要增加table_open_cache的值了(上述状态值可通过SHOW GLOBAL STATUS LIKE ‘Open%tables’获得)。
注意，不能盲目地把table_open_cache设置成很大的值，设置太大超过了shell的文件描述符(通过ulimit -n查看)，造成文件描述符不足，从而造成性能不稳定或者连接失败。

修改方式：

1
2
3

vi /etc/my.cnf
[mysqld]
table_open_cache=2000

table_definition_cache

延伸阅读： http://ju.outofmemory.cn/entry/332959

来理解一下 table_open_cache到底是来干嘛的，文档里或者网上的文章，通通解释是“用于控制MySQL Server能同时打开表的最大个数”。如果继续问这个个数怎么算呢？

我来尝试解答一下。MySQL是多线程的，多个会话上有可能会同时访问同一个表，mysql是允许这些会话各自独立的打开这个表，而表最终都是磁盘上的数据文件。(默认假设innodb_file_per_table=1)，打开文件需要获取文件描述符(File Descriptor)，为了加快这个open table的速度，MySQL在Server层设计了这个cache：

The idea behind this cache is that most statements don’t need to go to a central table definition cache to get a TABLE object and therefore don’t need to lock LOCK_open mutex. Instead they only need to go to one Table_cache instance (the specific instance is determined by thread id) and only lock the mutex protecting this cache. DDL statements that need to remove all TABLE objects from all caches need to lock mutexes for all Table_cache instances, but they are rare.

table_cache 减少了表级别 LOCK_open 这个互斥量的获取，改用获取表对象缓存实例列表的mutex。简化成如下过程：

假设当前并发200个连接，table_open_cache=200，其中有50连接都在访问同一张表

mysql内部维护了一个 unused_table_list，在a表上的请求结束后，会把这个thread刚才用过的 table object 放入unused_table_list

每个表有个key，可以通过hash快速定位到表a的所有可用object，如果后面一下子100个连接上来访问表a，内部会先从 unused_table_list 去找这个表已经缓存过的对象(get_table)，比如前50个可以直接拿来用(unlink_unused_table)

后50个则需要调用系统内核，拿到文件描述符。

用完之后会，放回到unused_table_list，并将这个表的key放到hash表的前面。

如果缓存的对象个数超过了 table_open_cache，则会通过LRU算法，把认为不用的表对象逐出。

从上面的过程应该很容易理解 table_open_cache 与 table_definition_cache 的区别。

table_defintion_cache也是一个key/value形式的hash表，但每个表只有一个值，值/对象的内容就是表的元数据信息(Data Dictionay，frm文件里面的信息)，如表结构、字段、索引，它是一个全局的结构，并且不占用文件描述符。

而 table_open_cache的key/value的值是一个列表，表示这个表的多个 Table_cache_element，他们共用这个表的 definition (代码层定义为TABLE_SHARE对象)。

(注：我们在row格式的binlog里面看到的 table_map_id 就是在 TABLE_SHARE 里面定义的，表结构变更、缓存被逐出，都会导致 table_map_id 递增。)

thread_cache_size

thread_cache_size：当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)

即可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能。

thread_cache_size大小的设置：

如果是短连接，适当设置大一点，因为短连接往往需要不停创建，不停销毁，如果大一点，连接线程都处于取用状态，不需要重新创建和销毁，所以对性能肯定是比较大的提升。
对于长连接，不能保证连接的稳定性，所以设置这参数还是有一定必要，可能连接池的问题，会导致连接数据库的不稳定性，也会出现频繁的创建和销毁，但这个情况比较少，如果是长连接，可以设置成小一点，一般在50-100左右。

设置

1	thread_cache_size=50

show variables like ‘thread_cache_size’;

performance_schema

mysql> show variables like 'performance_schema%'; 
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema                                     | ON    |
| performance_schema_accounts_size                       | 100   |
| performance_schema_digests_size                        | 10000 |
| performance_schema_events_stages_history_long_size     | 10000 |
| performance_schema_events_stages_history_size          | 10    |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_statements_history_size      | 10    |
| performance_schema_events_waits_history_long_size      | 10000 |
| performance_schema_events_waits_history_size           | 10    |
| performance_schema_hosts_size                          | 100   |
| performance_schema_max_cond_classes                    | 80    |
| performance_schema_max_cond_instances                  | 3504  |
| performance_schema_max_digest_length                   | 1024  |
| performance_schema_max_file_classes                    | 50    |
| performance_schema_max_file_handles                    | 32768 |
| performance_schema_max_file_instances                  | 7693  |
| performance_schema_max_mutex_classes                   | 200   |
| performance_schema_max_mutex_instances                 | 15906 |
| performance_schema_max_rwlock_classes                  | 40    |
| performance_schema_max_rwlock_instances                | 9102  |
| performance_schema_max_socket_classes                  | 10    |
| performance_schema_max_socket_instances                | 322   |
| performance_schema_max_stage_classes                   | 150   |
| performance_schema_max_statement_classes               | 168   |
| performance_schema_max_table_handles                   | 4000  |
| performance_schema_max_table_instances                 | 12500 |
| performance_schema_max_thread_classes                  | 50    |
| performance_schema_max_thread_instances                | 402   |
| performance_schema_session_connect_attrs_size          | 512   |
| performance_schema_setup_actors_size                   | 100   |
| performance_schema_setup_objects_size                  | 100   |
| performance_schema_users_size                          | 100   |
+--------------------------------------------------------+-------+
32 rows in set (0.00 sec)

修改

1 2	performance_schema=OFF performance_schema_max_table_instances=200

按硬件优化

一、connection相关

二、timeout相关

1、参数概览

show variables like ‘%timeout%’

connect_timeout=5  # 连接超时
wait_timeout=28800  # 关闭闲置连接前等待的秒数
interactive_timeout=28800 # 关闭交互式连接前等待的秒数
net_read_timeout=30 # 读数据的时的等待超时
net_write_timeout=60 # 等待将一个block发送给客户端的超时

delayed_insert_timeout=300 # 这是为MyISAM INSERT DELAY设计的超时参数，在INSERT DELAY中止前等待INSERT语句的时间。
innodb_flush_log_at_timeout=1 # 这个是5.6中才出现的，是InnoDB特有的参数，每次日志刷新时间。
innodb_lock_wait_timeout=50 # 没有死锁的情况下一个事务持有另一个事务需要的锁资源，被回滚的肯定是请求锁的那个Query
innodb_rollback_on_timeout=OFF # 这个参数关闭或不存在的话遇到超时只回滚事务最后一个Query，打开的话事务遇到超时就回滚整个事务
lock_wait_timeout=31536000
rpl_stop_slave_timeout=31536000 # 控制stop slave 的执行时间，在重放一个大的事务的时候,突然执行stop slave,命令 stop slave会执行很久,这个时候可能产生死锁或阻塞,严重影响性能，mysql 5.6可以通过rpl_stop_slave_timeout参数控制stop slave 的执行时间
slave_net_timeout=3600 # 这是Slave判断主机是否挂掉的超时设置，在设定时间内依然没有获取到Master的回应就认为Master挂掉了

参考：http://www.ttlsa.com/mysql/research-and-measurement-of-timeout-mysql/

2、connect_timeout=5

这个比较好理解，字面上看意思是连接超时。”The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake”。

MySQL连接一次连接需求经过6次“握手”方可成功，任意一次“握手”失败都有可能导致连接失败，如下图所示。

前三次握手可以简单理解为TCP建立连接所必须的三次握手，MySQL无法控制，更多的受制于不TCP协议的不同实现，后面的三次握手过程超时与connect_timeout有关。简单的测试方法：
1
2
3
4
5
6
7
8
9
10
11
12
> e telnet mysql_ip_addr port
> $ time telnet 127.0.0.1 5051
> Trying 127.0.0.1...
> Connected to 127.0.0.1.
> Escape character is '^]'.
> ?
> Connection closed by foreign
> host.
> real    0m5.005s #这里的5秒即mysql默认的连接超时
> user    0m0.000s
> sys 0m0.000s
>

Telnet未退出前通过show processlist查看各线程状态可见，当前该连接处于授权认证阶段，此时的用户为“unauthenticated user”。细心的你懂得，千万不要干坏事。

3、wait_timeout=1800

MySQL客户端的数据库连接闲置最大时间值。当MySQL连接闲置超过一定时间后将会被强行关闭。

MySQL默认的wait-timeout 值为8个小时，可以通过命令show variables like ‘wait_timeout’查看结果值。

interactive_timeout：服务器关闭交互式连接前等待活动的秒数。

wait_timeout:服务器关闭非交互连接之前等待活动的秒数。

这两个参数必须配合使用。否则单独设置wait_timeout无效。

等待超时，那mysql等什么呢？确切的说是mysql在等用户的请求(query)，如果发现一个线程已经sleep的时间超过wait_timeout了那么这个线程将被清理掉，无论是交换模式或者是非交换模式都以此值为准。

注意：wait_timeout是session级别的变量哦，至于session和global变量的区别是什么我不说您也知道。手册上不是明明说wait_timeout为not interactive模式下的超时么？为什么你说无论是交换模式或者非交换模式都以此值为准呢？简单的测试例子如下

那为什么手册上说在交互模式下使用的是interactive_timeout呢，原因如下：

check_connection函数在建立连接初期，如果为交互模式则将interactive_timeout值赋给wait_timeout，骗您误以为交互模式下等待超时为interactive_timeout 代码如下:
1
2
3
4
> if (thd->client_capabilities & CLIENT_INTERACTIVE) {
>      thd->variables.net_wait_timeout=thd->variables.net_interactive_timeout;
> }
>

4、interactive_timeout

上面说了那么多，这里就不再多做解释了。我理解mysql之所以多提供一个目的是提供给用户更灵活的设置空间。

5、net_write_timeout=60

看到这儿如果您看累了，那下面您得提提神了，接下来的两个参数才是我们遇到的最大的难题。 “The number of seconds to wait for a block to be written to a connection before aborting the write.” 等待将一个block发送给客户端的超时，一般在网络条件比较差的时，或者客户端处理每个block耗时比较长时，由于net_write_timeout导致的连接中断很容易发生。下面是一个模拟的例子：

6、net_read_timeout=30

“The number of seconds to wait fprintfor more data from a connection before aborting the read.”。Mysql读数据的时的等待超时，可能的原因可能为网络异常或客户端or服务器端忙无法及时发送或接收处理包。这里我用的是iptables来模拟网络异常，生成一个较大的数据以便于给我充足的时间在load data的过程中去配置iptables规则。

执行完iptables命令后show processlist可以看到load data的连接已经被中断掉了

7、net_retry_count

“超时”的孪生兄弟“重试”，时间原因这个我没有进行实际的测试，手册如是说，估且先信它一回。

If a read or write on a communication port is interrupted, retry this many times before giving up. This value should be set quite high on FreeBSD because internal interrupts are sent to all threads.

On Linux, the “NO_ALARM” build flag (-DNO_ALARM) modifies how the binary treats both net_read_timeout and net_write_timeout. With this flag enabled, neither timer cancels the current statement until after the failing connection has been waited on an additional net_retry_count times. This means that the effective timeout value becomes” (timeout setting) × (net_retry_count+1)”.

FreeBSD中有效，Linux中只有在build的时候指定NO_ALARM参数时net_retry_count才会起作用。

说明：目前线上使用的版本都未指定NO_ALARM。

8、innodb_lock_wait_timeout

The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the –innodb_rollback_on_timeout option, available as of MySQL 5.1.15. See also Section 13.6.12, “InnoDB Error Handling”.)

innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.

InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.

For the built-in InnoDB, this variable can be set only at server startup. For InnoDB Plugin, it can be set at startup or changed at runtime, and has both global and session values.

解释：描述很长，简而言之，就是事务遇到锁等待时的Query超时时间。跟死锁不一样，InnoDB一旦检测到死锁立刻就会回滚代价小的那个事务，锁等待是没有死锁的情况下一个事务持有另一个事务需要的锁资源，被回滚的肯定是请求锁的那个Query。

9、innodb_rollback_on_timeout

In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1). This variable was added in MySQL 5.1.15.

解释：这个参数关闭或不存在的话遇到超时只回滚事务最后一个Query，打开的话事务遇到超时就回滚整个事务。

10、innodb_flush_log_at_timeout

master线程刷写日志的频率。可以增大此参数设置来减少刷写次数，避免对binlog group commit带来影响。默认值是1

四、buffer相关

参数概览

show variables like ‘%buffer%’

bulk_insert_buffer_size=8388608 # MyISAM，用来缓存批量插入数据的时候临时缓存写入数据

innodb_buffer_pool_dump_at_shutdown=OFF
innodb_buffer_pool_dump_now=OFF
innodb_buffer_pool_filename=ib_buffer_pool
innodb_buffer_pool_instances=8
innodb_buffer_pool_load_abort=OFF
innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_load_now=OFF
innodb_buffer_pool_size=134217728

innodb_change_buffer_max_size=25
innodb_change_buffering=all
innodb_log_buffer_size=8388608
innodb_sort_buffer_size=1048576
join_buffer_size=262144 # 每个线程使用连接缓存区大小，最好是添加适当的索引而不是纯粹加大 join_buffer_size
key_buffer_size=8388608 # MyISAM，索引块的缓冲区大小
myisam_sort_buffer_size=8388608 # MyISAM，排序的缓冲区大小
net_buffer_length=16384 # net-buffer-length 可能对mysqldump导出及恢复有影响
preload_buffer_size=32768 # 预加载索引时分配的缓冲区大小
read_buffer_size=131072 # 对表进行顺序扫描的请求将分配一个读入缓冲区
read_rnd_buffer_size=262144 # 当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区
sort_buffer_size=262144 # 每个线程排序缓存区的大小
sql_buffer_result=OFF # 通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表, 释放表锁

key_buffer_size

用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，对MyISAM表性能影响最大的一个参数。

如果太大，系统将开始换页并且真的变慢了。

严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。

对于内存在4GB左右的服务器该参数可设置为256M或384M。

如何判断key_buffer_size的设置是否合理？
检查状态值Key_read_requests和Key_reads，key_reads / key_read_requests应该尽可能的低，比如1:100，1:1000 ，1:10000。其值可以用以下命令查得：show status like ‘key_read%’;

read_buffer_size

read_buffer_size 是控制 MySql读入缓冲区大小。磁盘 -> 内存的缓冲区。

对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。

如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能.

read_rnd_buffer_size

read_rnd_buffer_size 是MySql的随机读缓冲区大小。

当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。

进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。

但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

sort_buffer_size

sort_buffer_size是MySql执行排序使用的缓冲大小。

如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。

如果不能，可以尝试增加sort_buffer_size变量的大小

tmp_table_size

tmp_table_size是MySql的heap （堆积）表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。
如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。
还可以通过设置tmp_table_size选项来增加临时表的大小。
也就是说，如果调高该值，MySql同时将增加heap表的大小，可达到提高联接查询 join 速度的效果。

join_buffer_size

应用经常会出现一些两表（或多表）join的操作需求，MySQL在完成某些join需求的时候（all row join/all index /scan join）为了减少参与join的“被驱动表”的读取次数以提高性能，需要使用到join buffer来协助完成join操作当join buffer 太小，MySQL不会将该buffer存入磁盘文件而是先将join buffer中的结果与需求join的表进行操作，然后清空join buffer中的数据，继续将剩余的结果集写入次buffer中，如此往复，这势必会造成被驱动表需要被多次读取，成倍增加IO访问，降低效率（执行计划中如果现实using join buffer）

两个表关联的时候减少参与被驱动表的join操作（没办法有效利用索引的时候）

多表join时，就需要用到join buffer的三种情况

All row join do not user indexes nad thus perform full table scans（没有索引的全表扫描）

All index join plain index scans（普通索引扫描）

Range index scan join=rangeindex scans（范围索引扫描）

最好是添加适当的索引而不是纯粹加大 join_buffer_size

任何来个表间的全表join就会分配一次join_buffer，也就是说，如果3个表join就会分配2次joinbuffer（而不是一个session只分配一次）

sql_buffer_result

SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …

当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了)，并且可以长时间地为客户端提供大记录集。

innodb_buffer_pool_*

MySQL InnoDB buffer pool 里包含什么？

数据缓存：InnoDB数据页面

索引缓存：索引数据

缓冲数据：脏页（在内存中修改尚未刷新(写入)到磁盘的数据）

内部结构：如自适应哈希索引，行锁等。

1>.mysqld重启之后，innodb_buffer_pool几乎是空的，没有任何的缓存数据。随着sql语句的执行，table中的数据以及index 逐渐被填充到buffer pool里面，之后的查询语句只需要在内存中操作（理想状态下），大幅度提升了mysql的性能。这个逐渐填充的过程可能需要1-2个小时，甚至更久也说不准。在此过程中，mysql性能一般，因为需要大量的硬盘读操作

2>.innodb在内存中维护一个缓冲池用来缓存数据和索引，缓存池管理一个数据块列表，该列表又分为2个字列，一个子列存放new blocks，另一个子列存放old blocks。old blocks默认占整个列大小的3/8（可通过innodb_old_blocks_pct改变默认值，该值范围在5-95之间，这是一个百分比），其余大小为new blocks占用

手工导出的话，可以用这个命令：
1
2
> mysql> SET innodb_buffer_pool_dump_now=ON;
>

然后mysql会在innodb的数据目录中生成一个文件：ib_buffer_pool

关闭mysql的时候，自动导出：
1
2
> mysql> SET innodb_buffer_pool_dump_at_shutdown=ON;
>

在my.cnf中加上 innodb_buffer_pool_load_at_startup=ON 就会在mysqld启动之后，重新加载buffer pool。

3>.innodb_buffer_pool_instances：主要用于将innodb_buffer_pool进行划分，通过划分innodb_buffer_pool为多个实例，可以提高并发能力，并且减少了不同线程读写造成的缓冲页;但是不必设置过大，该值对mysql性能提升不大，有待测试；

innodb_buffer_pool_size

innodb_buffer_pool_size主要针对InnoDB表性能影响最大的一个参数。功能与Key_buffer_size一样。

另外InnoDB和 MyISAM 存储引擎不同。MyISAM 的 key_buffer_size 只能缓存索引键，而 innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。

show status like ‘Innodb_buffer_pool_read%’;

通过(innodb_buffer_pool_read_requests – innodb_buffer_pool_reads) / innodb_buffer_pool_read_requests * 100% 计算缓存命中率。并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化，命中率越高越好。

物理内存争用可能导致操作系统频繁的paging

InnoDB为缓冲区和control structures保留了额外的内存，因此总分配空间比指定的缓冲池大小大约大10％。

缓冲池的地址空间必须是连续的，这在带有在特定地址加载的DLL的Windows系统上可能是一个问题。

初始化缓冲池的时间大致与其大小成比例。在具有大缓冲池的实例上，初始化时间可能很长。要减少初始化时间，可以在服务器关闭时保存缓冲池状态，并在服务器启动时将其还原。

innodb_buffer_pool_dump_pct：指定每个缓冲池最近使用的页面读取和转储的百分比。范围是1到100。默认值是25。例如，如果有4个缓冲池，每个缓冲池有100个page，并且innodb_buffer_pool_dump_pct设置为25，则dump每个缓冲池中最近使用的25个page。

innodb_buffer_pool_dump_at_shutdown：默认启用。指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面，以便在下次重新启动时缩短预热过程。

innodb_buffer_pool_load_at_startup：默认启用。指定在MySQL服务器启动时，InnoDB缓冲池通过加载之前保存的相同页面自动预热。通常与innodb_buffer_pool_dump_at_shutdown结合使用。

增大或减小缓冲池大小时，将以chunk的形式执行操作。chunk大小由innodb_buffer_pool_chunk_size配置选项定义，默认值为128 MB。

缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。
如果将缓冲池大小更改为不等于或等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值，
则缓冲池大小将自动调整为等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值。

innodb_buffer_pool_size可以动态设置，允许在不重新启动服务器的情况下调整缓冲池的大小。可以通过状态变量Innodb_buffer_pool_resize_status报告在线调整缓冲池大小操作的状态。

五、cache相关

show variables like ‘%cache%’

binlog_cache_size=32768
binlog_stmt_cache_size=32768
have_query_cache=YES
host_cache_size=279
innodb_disable_sort_file_cache=OFF
innodb_ft_cache_size=8000000
innodb_ft_result_cache_limit=2000000000
innodb_ft_total_cache_size=640000000
key_cache_age_threshold=300
key_cache_block_size=1024
key_cache_division_limit=100
max_binlog_cache_size=18446744073709547520
max_binlog_stmt_cache_size=18446744073709547520
metadata_locks_cache_size=1024

stored_program_cache=256
table_definition_cache=1400
table_open_cache=2000
table_open_cache_instances=1
thread_cache_size=9

六、thread相关

show variables like ‘%thread%’

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=402
pseudo_thread_id=5
thread_cache_size=9
thread_concurrency=10 # CPU核数的2倍,对性能影响很大
thread_handling=one-thread-per-connection
thread_stack=262144

thread_concurrency

thread_concurrency的值的正确与否, 对mysql的性能影响很大。
在多个cpu(或多核)的情况下，错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。

thread_concurrency应设为CPU核数的2倍. 比如有一个双核的CPU, 那thread_concurrency 的应该为4; 2个双核的cpu, thread_concurrency的值应为8.

查看系统当前thread_concurrency默认配置命令：

show variables like ‘thread_concurrency’;

七、table相关

show variables like ‘%table%’

big_tables=OFF
innodb_file_per_table=ON
innodb_ft_aux_table=
innodb_ft_server_stopword_table=
innodb_ft_user_stopword_table=
innodb_table_locks=ON
innodb_undo_tablespaces=0
lower_case_table_names=0
max_heap_table_size=16777216
max_tmp_tables=32
old_alter_table=OFF
performance_schema_max_table_handles=4000
performance_schema_max_table_instances=12500
table_definition_cache=1400
table_open_cache=2000
table_open_cache_instances=1
tmp_table_size=16777216
updatable_views_with_limit=YES

十、InnoDB相关

1、参数概览

show variables like ‘%innodb%’

ignore_builtin_innodb=OFF
innodb_adaptive_flushing=ON
innodb_adaptive_flushing_lwm=10
innodb_adaptive_hash_index=ON
innodb_adaptive_max_sleep_delay=150000
innodb_additional_mem_pool_size=8388608
innodb_api_bk_commit_interval=5
innodb_api_disable_rowlock=OFF
innodb_api_enable_binlog=OFF
innodb_api_enable_mdl=OFF
innodb_api_trx_level=0
innodb_autoextend_increment=64
innodb_autoinc_lock_mode=1
innodb_buffer_pool_dump_at_shutdown=OFF
innodb_buffer_pool_dump_now=OFF
innodb_buffer_pool_filename=ib_buffer_pool
innodb_buffer_pool_instances=8
innodb_buffer_pool_load_abort=OFF
innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_load_now=OFF
innodb_buffer_pool_size=134217728
innodb_change_buffer_max_size=25
innodb_change_buffering=all
innodb_checksum_algorithm=innodb
innodb_checksums=ON
innodb_cmp_per_index_enabled=OFF
innodb_commit_concurrency=0
innodb_compression_failure_threshold_pct=5
innodb_compression_level=6
innodb_compression_pad_pct_max=50
innodb_concurrency_tickets=5000
innodb_data_file_path=ibdata1:12M:autoextend # 要注释掉，否则会创建一个新的把原来的替换的 
innodb_data_home_dir=
innodb_disable_sort_file_cache=OFF
innodb_doublewrite=ON
innodb_fast_shutdown=1
innodb_file_format=Antelope
innodb_file_format_check=ON
innodb_file_format_max=Antelope
innodb_file_per_table=ON
innodb_flush_log_at_timeout=1
innodb_flush_log_at_trx_commit=1
innodb_flush_method=
innodb_flush_neighbors=1
innodb_flushing_avg_loops=30
innodb_force_load_corrupted=OFF
innodb_force_recovery=0
innodb_ft_aux_table=
innodb_ft_cache_size=8000000
innodb_ft_enable_diag_print=OFF
innodb_ft_enable_stopword=ON
innodb_ft_max_token_size=84
innodb_ft_min_token_size=3
innodb_ft_num_word_optimize=2000
innodb_ft_result_cache_limit=2000000000
innodb_ft_server_stopword_table=
innodb_ft_sort_pll_degree=2
innodb_ft_total_cache_size=640000000
innodb_ft_user_stopword_table=
innodb_io_capacity=200
innodb_io_capacity_max=2000
innodb_large_prefix=OFF
innodb_lock_wait_timeout=50
innodb_locks_unsafe_for_binlog=OFF
innodb_log_buffer_size=8388608
innodb_log_compressed_pages=ON
innodb_log_file_size=50331648
innodb_log_files_in_group=2
innodb_log_group_home_dir=./
innodb_lru_scan_depth=1024
innodb_max_dirty_pages_pct=75
innodb_max_dirty_pages_pct_lwm=0
innodb_max_purge_lag=0
innodb_max_purge_lag_delay=0
innodb_mirrored_log_groups=1
innodb_monitor_disable=
innodb_monitor_enable=
innodb_monitor_reset=
innodb_monitor_reset_all=
innodb_numa_interleave=OFF
innodb_old_blocks_pct=37
innodb_old_blocks_time=1000
innodb_online_alter_log_max_size=134217728
innodb_open_files=2000
innodb_optimize_fulltext_only=OFF
innodb_page_size=16384
innodb_print_all_deadlocks=OFF
innodb_purge_batch_size=300
innodb_purge_threads=1
innodb_random_read_ahead=OFF
innodb_read_ahead_threshold=56
innodb_read_io_threads=4
innodb_read_only=OFF
innodb_replication_delay=0
innodb_rollback_on_timeout=OFF
innodb_rollback_segments=128
innodb_sort_buffer_size=1048576
innodb_spin_wait_delay=6
innodb_stats_auto_recalc=ON
innodb_stats_include_delete_marked=OFF
innodb_stats_method=nulls_equal
innodb_stats_on_metadata=OFF
innodb_stats_persistent=ON
innodb_stats_persistent_sample_pages=20
innodb_stats_sample_pages=8
innodb_stats_transient_sample_pages=8
innodb_status_output=OFF
innodb_status_output_locks=OFF
innodb_strict_mode=OFF
innodb_support_xa=ON
innodb_sync_array_size=1
innodb_sync_spin_loops=30
innodb_table_locks=ON
innodb_thread_concurrency=0
innodb_thread_sleep_delay=10000
innodb_tmpdir=
innodb_undo_directory=.
innodb_undo_logs=128
innodb_undo_tablespaces=0
innodb_use_native_aio=ON
innodb_use_sys_malloc=ON
innodb_version=5.6.45
innodb_write_io_threads=4

2、default-storage-engine=InnoDB

备注：设置完后把以下几个开启：
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir =/usr/local/mysql
#innodb_data_file_path = ibdata1:1024M;ibdata2:10M:autoextend（要注释掉，否则会创建一个新的把原来的替换的。）
innodb_log_group_home_dir = /usr/local/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1000M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 500M
innodb_log_buffer_size = 20M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50

3、innodb_additional_mem_pool_size

innodb_additional_mem_pool_size 设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。

这个参数大小是否足够还是比较容易知道的，因为当过小的时候，MySQL会记录Warning信息到数据库的error log中，这时候就该调整这个参数大小了。

查看当前系统mysql的error日志 cat /var/lib/mysql/机器名.error 发现有很多waring警告。所以要调大为20M。根据MySQL手册，对于2G内存的机器，推荐值是20M。 32G内存的 100M

4、innodb_log_buffer_size

innodb_log_buffer_size 这是InnoDB存储引擎的事务日志所使用的缓冲区。类似于Binlog Buffer，InnoDB在写事务日志的时候，为了提高性能，也是先将信息写入Innofb Log Buffer中，当满足innodb_flush_log_trx_commit 参数所设置的相应条件(或者日志缓冲区写满)之后，才会将日志写到文件 (或者同步到磁盘)中。可以通过innodb_log_buffer_size 参数设置其可以使用的最大内存空间。

InnoDB 将日志写入日志磁盘文件前的缓冲大小。
理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。
因此，如果有大的事务处理，设置大的日志缓冲可以减少磁盘I/O。在 my.cnf中以数字格式设置。

默认是8MB，系的如频繁的系统可适当增大至4MB～8MB。当然如上面介绍所说，这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB

Creating index ‘GEN_CLUST_INDEX’ required more than ‘innodb_online_alter_log_max_size’ bytes of modification log. Please try again.

虽然optimize对innodb表没什么用，但是仍然会抛出该错误

那么查看一下手册，可以得知：

innodb_online_alter_log_max_size控制在用于在Online DDL操作时的一个临时的日志文件的上限值大小。

该临时的日志文件存储了在DDL时间内，dml操作的记录。这个临时的日志文件依照innodb_sort_buffer_size的值做扩展。

如果该日志超过了innodb_online_alter_log_max_size的最大上限，DDL操作则会抛出失败，并且回滚所有未提交的DML操作。

反过来说，该值如果设置更高，则可以允许在做Online DDL时，有更多的DML操作发生。

但因此带来的问题就是，在DDL做完之后，需要更多时间来锁表和应用这些日志。

另外对于某些DDL操作，比如
ADD INDEX/COLUMN，则可以通过调整innodb_sort_buffer_size的大小来加快操作速度。
但是实际上分配的内存为3倍的innodb_sort_buffer_size值。

innodb_online_alter_log_max_size和innodb_sort_buffer_size均为5.6 Online DDL的新参数。

【解决方案】：

知道这个参数控制的是什么东西，就好解决了。
临时调大该值，此处改成了256MB：

mysql> SET GLOBAL innodb_online_alter_log_max_size=25610241024;
Query OK, 0 rows affected (0.03 sec)

该值默认为128MB，还是建议在做完DDL之后再将其改为默认值，也就是134217728。

十一、Master-Slave相关

1、参数概览

gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1
slave_parallel_type=logical_clock
slave_parallel_workers=8
innodb_flush_log_at_trx_commit=1
sync_binlog=1  #每一个transaction commit都会调用一次fsync()，此时能保证数据最安全但是性能影响较大。

十二、net相关

show variables like ‘%net%’

net_buffer_length=16384
net_read_timeout=30
net_retry_count=10
net_write_timeout=60
skip_networking=OFF # 开启该选项可以彻底关闭MySQL的TCP/IP连接方式。
slave_net_timeout=3600

skip-name-resolve

skip-name-resolve：禁止MySQL对外部连接进行DNS解析。

使用这一选项可以消除MySQL进行DNS解析的时间。

但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！

skip-networking

建议被注释掉，不要开启

开启该选项可以彻底关闭MySQL的TCP/IP连接方式。

如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项，否则将无法正常连接。

十三、lock相关

show variables like ‘%lock%’

block_encryption_mode=aes-128-ecb
innodb_api_disable_rowlock=OFF
innodb_autoinc_lock_mode=1
innodb_lock_wait_timeout=50
innodb_locks_unsafe_for_binlog=OFF
innodb_old_blocks_pct=37
innodb_old_blocks_time=1000
innodb_print_all_deadlocks=OFF
innodb_status_output_locks=OFF
innodb_table_locks=ON
key_cache_block_size=1024
lock_wait_timeout=31536000
locked_in_memory=OFF
max_write_lock_count=18446744073709551615
metadata_locks_cache_size=1024
metadata_locks_hash_instances=8
performance_schema_max_rwlock_classes=40
performance_schema_max_rwlock_instances=9102
query_alloc_block_size=8192
query_cache_wlock_invalidate=OFF
range_alloc_block_size=4096
skip_external_locking=ON
transaction_alloc_block_size=8192

十四、transaction 相关

show variables like ‘%transaction%’

binlog_direct_non_transactional_updates=OFF
slave_transaction_retries=10
transaction_alloc_block_size=8192
transaction_allow_batching=OFF
transaction_prealloc_size=4096

show variables like ‘%trx%’

1 2	innodb_api_trx_level=0 innodb_flush_log_at_trx_commit=1

按功能优化

查询优化

sql_cache意思是说，查询的时候使用缓存。

sql_no_cache意思是查询的时候不适用缓存。

sql_buffer_result意思是说，在查询语句中，将查询结果缓存到临时表中。

这三者正好配套使用。sql_buffer_result将尽快释放表锁，这样其他sql就能够尽快执行。

使用 FLUSH QUERY CACHE 命令，你可以整理查询缓存，以更好的利用它的内存。这个命令不会从缓存中移除任何查询。FLUSH TABLES 会转储清除查询缓存。
RESET QUERY CACHE 使命从查询缓存中移除所有的查询结果。

-————————————————-

那么mysql到底是怎么决定到底要不要把查询结果放到查询缓存中呢？

是根据query_cache_type这个变量来决定的。

这个变量有三个取值：0,1,2，分别代表了off、on、demand。

意思是说，如果是0，那么query cache 是关闭的。如果是1，那么查询总是先到查询缓存中查找，除非使用了sql_no_cache。如果是2，那么，只有使用 sql_cache的查询，才会去查询缓存中查找。

三、index索引相关

1、参数概览

show variables like ‘%key%’

delay_key_write=ON
foreign_key_checks=ON
have_rtree_keys=YES
key_buffer_size=8388608
key_cache_age_threshold=300
key_cache_block_size=1024
key_cache_division_limit=100
max_seeks_for_key=18446744073709551615
ssl_key=

八、sort相关

1、参数概览

show variables like ‘%sort%’

innodb_disable_sort_file_cache=OFF
innodb_ft_sort_pll_degree=2
innodb_sort_buffer_size=1048576
max_length_for_sort_data=1024
max_sort_length=1024
myisam_max_sort_file_size=9223372036853727232
myisam_sort_buffer_size=8388608
sort_buffer_size=262144

九、log 相关

1、参数概览

show variables like ‘%log%’

back_log=80
binlog_cache_size=32768
binlog_checksum=CRC32
binlog_direct_non_transactional_updates=OFF
binlog_error_action=IGNORE_ERROR
binlog_format=STATEMENT
binlog_gtid_simple_recovery=OFF
binlog_max_flush_queue_time=0
binlog_order_commits=ON
binlog_row_image=FULL
binlog_rows_query_log_events=OFF
binlog_stmt_cache_size=32768
binlogging_impossible_mode=IGNORE_ERROR
expire_logs_days=0
general_log=OFF
general_log_file=/var/lib/mysql/d27fc289bc0c.log
innodb_api_enable_binlog=OFF
innodb_flush_log_at_timeout=1
innodb_flush_log_at_trx_commit=1
innodb_locks_unsafe_for_binlog=OFF
innodb_log_buffer_size=8388608
innodb_log_compressed_pages=ON
innodb_log_file_size=50331648
innodb_log_files_in_group=2
innodb_log_group_home_dir=./
innodb_mirrored_log_groups=1
innodb_online_alter_log_max_size=134217728
innodb_undo_logs=128
log_bin=OFF
log_bin_basename=
log_bin_index=
log_bin_trust_function_creators=OFF
log_bin_use_v1_row_events=OFF
log_error=
log_output=FILE
log_queries_not_using_indexes=OFF
log_slave_updates=OFF
log_slow_admin_statements=OFF
log_slow_slave_statements=OFF
log_throttle_queries_not_using_indexes=0
log_warnings=1
max_binlog_cache_size=18446744073709547520
max_binlog_size=1073741824
max_binlog_stmt_cache_size=18446744073709547520
max_relay_log_size=0
relay_log=
relay_log_basename=
relay_log_index=
relay_log_info_file=relay-log.info
relay_log_info_repository=FILE
relay_log_purge=ON
relay_log_recovery=OFF
relay_log_space_limit=0
simplified_binlog_gtid_recovery=OFF
slow_query_log=OFF
slow_query_log_file=/var/lib/mysql/d27fc289bc0c-slow.log
sql_log_bin=ON
sql_log_off=OFF
sync_binlog=0
sync_relay_log=10000
sync_relay_log_info=10000