mysql配置优化

发表于 2019-11-14 | 分类于 mysql

要做优化工作首先要了解 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

undo_log

redo_log

bin_log

Java 实现CPU亲和性_未完

发表于 2019-11-13 | 分类于 java

log4j2源码——架构设计

发表于 2019-11-12

从github导入idea

下载地址： https://github.com/apache/logging-log4j2.git

kafka Q&A

发表于 2019-11-11

kafka多个partition 之间怎么保证消息全局顺序性

flink checkpoint 分类

发表于 2019-10-29 | 分类于 flink

Keyed State

Operator State

增量 checkpoint

持久化方式

flink 从mysql抽取写入kafka和文本文件

发表于 2019-10-29 | 分类于 flink

一、需求说明

登录日志放在mysql里 ==> 现在需要将 mysql中的数据转换格式以后写入kafka ==> 数据中心从 kafka里取数据进行日志分析和监管审查；

首先考虑有flume读取数据，但是在做格式转换时，需要重写 source 和 interceptor，得不偿失，还不如用flink。正好阿里发布了全新的 flink 1.9，尝鲜使用；

二、环境配置

flink-1.9.0（The only requirements are working Maven 3.0.4 (or higher) and Java 8.x installations.）
mysql-5.6
kafka_2.11-0.11

1、安装 jdk 1.8+

2、下载启动flink-1.9

下载地址： https://flink.apache.org/zh/downloads.html

说明文档： https://ci.apache.org/projects/flink/flink-docs-release-1.9/getting-started/tutorials/local_setup.html

wget http://mirrors.tuna.tsinghua.edu.cn/apache/flink/flink-1.9.1/flink-1.9.1-bin-scala_2.11.tgz
tar zxf flink-1.9.1-bin-scala_2.11.tgz
cd flink-1.9.1
# 启动flink(Standalone形式)
/data/flink/flink-1.9.1/bin/start-cluster.sh
# 停止flink
/data/flink/flink-1.9.1/bin/stop-cluster.sh
开启端口
firewall-cmd --permanent --add-port=8081/tcp
firewall-cmd --reload
firewall-cmd --query-port 8081/tcp

验证启动成功：

jps -l

netstat -nltp | grep 8081

访问管理页面： http://172.18.1.51:8081

三、创建 Flink工程

官网文档：
https://ci.apache.org/projects/flink/flink-docs-release-1.9/dev/projectsetup/java_api_quickstart.html#requirements

1 2	curl https://flink.apache.org/q/quickstart.sh \| bash -s 1.9.0 mv quickstart/ flink-log-process

改造后工程结构如下：

1、定义 Mysql Datasource

1.1、在上述工程的 pom.xml 中添加 mysql 依赖

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.34</version>
</dependency>

1.2、定义 CCustLoginHisSource 读取 mysql

package com.jzsec.tzdslog;


import org.apache.commons.collections.IteratorUtils;
import org.apache.flink.api.common.state.ListState;
import org.apache.flink.api.common.state.ListStateDescriptor;
import org.apache.flink.api.common.typeinfo.TypeInformation;
import org.apache.flink.configuration.Configuration;
import org.apache.flink.runtime.state.FunctionInitializationContext;
import org.apache.flink.runtime.state.FunctionSnapshotContext;
import org.apache.flink.streaming.api.checkpoint.CheckpointedFunction;
import org.apache.flink.streaming.api.functions.source.RichSourceFunction;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

public class CCustLoginHisSource extends RichSourceFunction<CCustLoginHisEntity> implements CheckpointedFunction {
    private PreparedStatement ps;
    private Connection connection;
    private String url, username, password;
    private int year;
    private Date beginDate, endDate;

    public CCustLoginHisSource(String url, String username, String password, LocalDate localDate) {
        this.url = url;
        this.username = username;
        this.password = password;

        year = localDate.getYear();
        beginDate = new java.sql.Date(localDate.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant().toEpochMilli());
        endDate = new java.sql.Date(localDate.plusDays(1).atStartOfDay().atZone(ZoneId.systemDefault()).toInstant().toEpochMilli());
    }

    private Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            System.out.println("-----------mysql getConnection() has exception , msg = " + e.getMessage());
        }
        return conn;
    }

    @Override
    public void open(Configuration parameters) throws Exception {
        // 增量抽取数据
        super.open(parameters);
        connection = getConnection();
        String sql = "select * from db.tablename_" + year + " where create_time between ? and ?;";
        ps = this.connection.prepareStatement(sql);
        ps.setDate(1, beginDate);
        ps.setDate(2, endDate);
    }

    @Override
    public void close() throws Exception {
        super.close();
        if (null != connection) {
            connection.close();
        }
        if (null != ps) {
            ps.close();
        }
    }

    @Override
    public void cancel() {}

    @Override
    public void run(SourceContext<CCustLoginHisEntity> sourceContext) throws Exception {
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()) {
            CCustLoginHisEntity entity = new CCustLoginHisEntity();
            entity.setId(resultSet.getInt("id"));
            entity.setEnv_type(resultSet.getString("env_type"));
            entity.setApp_type(resultSet.getString("app_type"));
            entity.setUser_ip(resultSet.getString("user_ip"));
            entity.setUser_agent(resultSet.getString("user_agent"));
            entity.setIp_addr(resultSet.getString("ip_addr"));
            entity.setMac(resultSet.getString("mac"));
            entity.setPhone(resultSet.getString("phone"));
            entity.setImei(resultSet.getString("imei"));
            entity.setRequest_time(resultSet.getDate("request_time"));
            entity.setCreate_time(resultSet.getDate("create_time"));

            lastIndex = entity.getId();
            sourceContext.collect(entity);
        }
    }
}

1.3、写入kafka

import org.apache.flink.configuration.Configuration;
import org.apache.flink.streaming.api.functions.sink.RichSinkFunction;
import org.apache.kafka.clients.producer.KafkaProducer;
import org.apache.kafka.clients.producer.ProducerRecord;

import java.util.Properties;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.atomic.AtomicInteger;

public class SinkToKafka011 extends RichSinkFunction<String> {

    private Properties props;
    private String topic;
    private KafkaProducer<Integer, byte[]> producerB;
    private AtomicInteger messageNo = new AtomicInteger(0);

    public SinkToKafka011(Properties props, String topic) {
        this.props = props;
        this.topic = topic;
    }
    @Override
    public void open(Configuration parameters) throws Exception {
        super.open(parameters);
        producerB = new KafkaProducer(props);
    }

    @Override
    public void invoke(String value, Context context) {
        try {
            producerB.send(new ProducerRecord(
                    topic,
                    messageNo.getAndIncrement(),
                    value.getBytes()))
                    .get();
        } catch (InterruptedException e) {
            e.printStackTrace();
        } catch (ExecutionException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void close() throws Exception {
        if (null != producerB) producerB.close();
        super.close();
    }
}

1.4、写入文本文件

import org.apache.flink.configuration.Configuration;
import org.apache.flink.streaming.api.functions.sink.RichSinkFunction;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;

public class SinkToTextFile extends RichSinkFunction<String> {
    private String filepath;
    private String filename;
    private FileOutputStream fos;
    private OutputStreamWriter osw;
    private BufferedWriter bufferedWriter;

    public SinkToTextFile(String filepath, String filename) {
        this.filepath = filepath;
        this.filename = filename;
        File dir = new File(filepath);
        if (!dir.isDirectory()) {
            dir.mkdir();
        }
    }

    @Override
    public void open(Configuration parameters) throws Exception {
        super.open(parameters);
        File file = new File(filepath);
        if (!file.isDirectory()) {
            file.mkdir();
        }
        fos = new FileOutputStream(new File(filepath + "/" + filename), true);
        osw = new OutputStreamWriter(fos);
        bufferedWriter = new BufferedWriter(osw);
    }

    @Override
    public void close() throws Exception {
        if (bufferedWriter != null) bufferedWriter.close();
        if (osw != null) osw.close();
        if (fos != null) fos.close();
        super.close();
    }

    @Override
    public void invoke(String value, Context context) throws Exception {
        bufferedWriter.write(value);
    }
}

1.5、定义Job

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.flink.api.java.utils.ParameterTool;
import org.apache.flink.shaded.jackson2.com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.flink.streaming.api.datastream.DataStreamSource;
import org.apache.flink.streaming.api.datastream.SingleOutputStreamOperator;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.kafka.clients.producer.ProducerConfig;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class TzdsLoginLogProcessJob {

    private static ObjectMapper mapper = new ObjectMapper();

    public static void main(String[] args) throws Exception {

        final ParameterTool argsParamTool = ParameterTool.fromArgs(args);
        // --logdate 2019-10-15
        String logdate = argsParamTool.get("logdate", "2019-10-15"); 
        final String propertiesFile = argsParamTool.get("conf", "conf.properties");
        final ParameterTool propParamTool = ParameterTool.fromPropertiesFile(propertiesFile);

        // 创建上下文
        final StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.getConfig().setGlobalJobParameters(propParamTool);

        DataStreamSource<CCustLoginHisEntity> dataStreamSource = env.addSource(
                new CCustLoginHisSource(
                        propParamTool.get("mysql.jdbc.url"),
                        propParamTool.get("mysql.jdbc.username"),
                        propParamTool.get("mysql.jdbc.password"),
                        LocalDate.parse(logdate))
        );
        SingleOutputStreamOperator<String> logStream = dataStreamSource.map(entity -> {
            try {
                LogModel log = new LogModel();
                log.setAppType("accessLog");
                log.setCid("0x000000000001");
                log.setMid("0x000000000002");
                log.setName("tagname");
                log.setIp(entity.getUser_ip());
                log.setIndicator(entity2Map(entity));
                log.setTime_stamp(Long.toString(entity.getRequest_time().getTime()));
                return JSONObject.toJSONString(log) + "\n";
            } catch (Exception e) {
                return "";
            }
        });
        SingleOutputStreamOperator<String> unifyLogStream = dataStreamSource.map(entity -> {
            try {
                Map unifyLog = new HashMap();
                initLoginLog(unifyLog);
                procLoginLog(unifyLog, entity2Map(entity), Long.toString(entity.getRequest_time().getTime()));
                return JSONObject.toJSONString(unifyLog) + "\n";
            } catch (Exception e) {
                return "";
            }
        });
        // 写入日志
        logStream.addSink(new SinkToTextFile(
                propParamTool.get("log.filepath") + "/" + logdate,
                "login_log_" + logdate.replace("-", "") + ".log"
                ));
        // 写入kafka
        Properties kafkaProps = new Properties();
        kafkaProps.put(ProducerConfig.BOOTSTRAP_SERVERS_CONFIG, propParamTool.get("kafka.bootstrap.servers"));
        kafkaProps.put("client.id", propParamTool.get("kafka.client.id"));
        kafkaProps.put(ProducerConfig.KEY_SERIALIZER_CLASS_CONFIG, "org.apache.kafka.common.serialization.IntegerSerializer");
        kafkaProps.put(ProducerConfig.VALUE_SERIALIZER_CLASS_CONFIG, "org.apache.kafka.common.serialization.ByteArraySerializer");
        String topic = propParamTool.get("kafka.topic.login-log");
        unifyLogStream.addSink(new SinkToKafka011(kafkaProps, topic)).name("sink-to-kafka").setParallelism(1);

        env.execute();
    }
}

2、编译

mvn clean package

flume自定义拦截器 + mysql增量同步到kafka

发表于 2019-10-25

一、搭建可运行的flume环境

0、下载 apache-flume-1.9.0-bin

1、下载 mysql 依赖包

1、下载 mysql-connector-java-5.1.18.jar 放在 apache-flume-1.9.0-bin\lib 下

2、下载 flume-ng-sql-source

https://github.com/keedio/flume-ng-sql-source/releases

mvn package

把生成的 flume-ng-sql-source-1.5.2.jar 放在 apache-flume-1.9.0-bin\lib 下

2、创建kafka topic

1	./kafka-topics.sh --create --zookeeper zookeeper:2181 --replication-factor 1 --partitions 1 --topic tzds-login

3、配置 flume

新建 conf/mysql2kafka.conf

a1.channels=ch-1
a1.sources=src-1
a1.sinks=k1
###########sql source#################
# For each one of the sources, the type is defined
a1.sources.src-1.type=org.keedio.flume.source.SQLSource
a1.sources.src-1.hibernate.connection.url=jdbc:mysql://10.10.209.27:3306/crm

# Hibernate Database connection properties
a1.sources.src-1.hibernate.connection.user=crm
a1.sources.src-1.hibernate.connection.password=crm@2015
a1.sources.src-1.hibernate.connection.autocommit=true
a1.sources.src-1.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
a1.sources.src-1.hibernate.connection.driver_class=com.mysql.jdbc.Driver
a1.sources.src-1.run.query.delay=10000
a1.sources.src-1.status.file.path=./
a1.sources.src-1.status.file.name=sqlsource.status
#sqlsource.status文件中记录了增量字段的值 $@$

# Custom query="你的SQL语句"
a1.sources.src-1.start.from=0
a1.sources.src-1.custom.query=select * from crm.c_cust_login_his_2019 where id > $@$ order by id asc
a1.sources.src-1.batch.size=1000
a1.sources.src-1.max.rows=1000

a1.sources.src-1.hibernate.connection.provider_class=org.hibernate.connection.C3P0ConnectionProvider
a1.sources.src-1.hibernate.c3p0.min_size=1
a1.sources.src-1.hibernate.c3p0.max_size=10

a1.sources.src-1.interceptors=i1
a1.sources.src-1.interceptors.i1.type=com.jzsec.flume.interceptor.TzdsLoginBuilder

##############################
a1.channels.ch-1.type=memory
a1.channels.ch-1.capacity=10000
a1.channels.ch-1.transactionCapacity=10000
a1.channels.ch-1.byteCapacityBufferPercentage=20
a1.channels.ch-1.byteCapacity=800000

#Kafka sink配置 a1.sinks.k1.topic=你的topic名字
a1.sinks.k1.type=org.apache.flume.sink.kafka.KafkaSink
a1.sinks.k1.topic=tzds-login
a1.sinks.k1.brokerList=172.18.1.51:9092
a1.sinks.k1.requiredAcks=1
a1.sinks.k1.batchSize=20

# combination  a1.sources.src-1.channels不要漏掉s
a1.sinks.k1.channel=ch-1
a1.sources.src-1.channels=ch-1

4、启动flume agent

linux:

1	./bin/flume-ng agent -n a1 -c conf -f conf/mysql2kafka.conf -Dflume.root.logger=INFO,console

windows：

1	bin\flume-ng agent -n a1 -c conf -f conf\mysql2kafka.conf

5、查看 kafka topic

docker exec -it b6276c6648c2 /bin/bash
cd /opt/kafka/bin/
./kafka-console-consumer.sh --bootstrap-server 172.18.1.51:9092  --topic tzds-login --from-beginning
./kafka-console-consumer.sh --bootstrap-server 172.18.1.51:9092  --topic tzds-login

二、自定义Interceptor流程

搭建flume开发环境（巧妇难为无米之炊，你没开发环境怎么玩，程序都不知道你写的类是个啥）
新建一个类，实现Interceptor接口，重写intercept(Event event)方法
新建一个类，实现Interceptor.Builder接口，重写configure(Context context)和build()方法
打成jar包放到flume的lib目录下
编写相应的flume.conf文件，将type值使用类的全限定名指定我们的拦截器。如果有自定义属性，需要配置该自定义属性。

三、搭建开发环境

1、新建一个maven工程，在 pom.xml 中引入如下依赖：

<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xmlns="http://maven.apache.org/POM/4.0.0"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.jzsec</groupId>
    <artifactId>flume-custom-interceptor</artifactId>
    <version>1.0</version>
    <packaging>jar</packaging>

    <dependencies>
        <dependency>
            <groupId>org.apache.flume</groupId>
            <artifactId>flume-ng-sdk</artifactId>
            <version>1.9.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.flume</groupId>
            <artifactId>flume-ng-core</artifactId>
            <version>1.9.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.58</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.1</version>
                <configuration>
                    <source>1.7</source>
                    <target>1.7</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <version>2.3</version>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.18.1</version>
            </plugin>

        </plugins>
    </build>

</project>

2、自定义 Interceptor 和 Builder

public class TzdsLoginInterceptor implements Interceptor {
    
	public void initialize() {}

	public Event intercept(Event event) {
		//获得body的内容
		Map<String, String> eventHeader = event.getHeaders();
		String eventBody = new String(event.getBody(), Charsets.UTF_8);
		String fmt = "%s, %s";
		try {
			eventBody = procLog(eventBody);
		} catch (ParseException e) {
			eventBody = "";
			e.printStackTrace();
		}
		event.setBody(String.format(fmt, JSONObject.toJSONString(eventHeader), eventBody).getBytes());
		return event;
	}

	public List<Event> intercept(List<Event> list) {
		for (Event event : list) {
			intercept(event);
		}
		return list;
	}

	public void close() {}
}

import org.apache.flume.Context;
import org.apache.flume.interceptor.Interceptor;

public class TzdsLoginBuilder implements Interceptor.Builder {

	public void configure(Context context) {}

	public Interceptor build() {
		return new TzdsLoginInterceptor();
	}
}

3、打包部署

mvn clean package

将 flume-custom-interceptor-1.0.jar 部署到 apache-flume-1.9.0-bin\lib 下

4、在 mysql2kafka.conf 中配置 interceptors

1 2	a1.sources.src-1.interceptors=i1 a1.sources.src-1.interceptors.i1.type=com.jzsec.flume.interceptor.TzdsLoginInterceptor

5、启动flume agent

linux:

1	./bin/flume-ng agent -n a1 -c conf -f conf/mysql2kafka.conf -Dflume.root.logger=INFO,console

windows：

1	bin\flume-ng agent -n a1 -c conf -f conf\mysql2kafka.conf

6、查看 kafka topic

微服务、SpringCloud、k8s、Istio杂谈

发表于 2019-10-24 | 分类于 micro-service

一、微服务与SOA

“微服务”是一个名词，没有这个名词之前也有“微服务”，一个朗朗上口的名词能让大家产生一个认知共识，这对推动一个事务的发展挺重要的，不然你叫微服务他叫小服务的大家很难集中到一个点上。

业界对微服务与SOA的区别争论比较多大多都是在微观上对比他们的区别什么微服务粒度更细啊、微服务没有ESB啊、微服务通讯相比SOA采用更轻量级的协议啊等等，但是从微观谈区别本身就有悖论，

这些区别只是微服务的一种”最佳实践“而已。我个人理解微服务与SOA灵魂上的不同是

微服务是互联网时代的产物而SOA是系统集成的产物，微服务是对系统的打散而SOA是对系统的整合。

二、微服务与SpringCloud

因为SpringCloud的流行很多人就把SpringCloud等同于微服务，这也没有错共识的人多了就是对的。准确点说SpringCloud是适合实现微服务的一套基础框架，SpringCloud有助于讯速的落地微服务架构。SpringCloud是以Java库的形式工作所以它的工作层面是在应用层（研发层）。

SpringCloud通过提供一篮子解决方案来应对微服务中的各种需求和通点，通过Eureka提供服务注册与发现，Ribbon实现客户端的负载均衡，Feign牛逼的将REST变成强类型的接口调用，Config提供方便但不灵活的配置中心，Hystrix提供熔断方案，Zuul提供网关方案等。

优点：

1、提供较全的微服务治理全套解决方案

2、对开发人员友好（对代码侵入强）

缺点：

1、只能java平台技术栈使用，当然提供了SideCar用于集成异构技术但是限制比较大

2、对开发人员友好（对代码侵入强）

三、Kubernetes(k8s)

k8s并不是因为微服务而生而是因为docker而生只是天时地利人和正好赶上了微服务流行的时代，docker的特性正好特别适用于微服务，而k8s进一步对docker方便的编排。

从基础设施方向来讲k8s可以比作是IDC机房和机房工作人员，对物理服务器（docker）的存放与管理，上机架、装系统、接网络等等。

从微服务的角度来讲，k8s通过基础设施的方式通过逻辑抽象出service等概念提供了对微服务的另一种实现，就好比用N台电脑联网提供了FTP服务。

优点：

1、在基础层提供了抽象，对代码无侵入

缺点：

1、对微服务治理比较弱，如熔断限流等，当然这也不应该是k8s做的。

四、Istio

Istio的理论概念是Service Mesh（服务网络），我们不必纠结于概念实际也是微服务的一种落地形式有点类似上面的SideCar模式，它的主要思想是关注点分离，即不像SpringCloud一样交给研发来做，也不集成到k8s中产生职责混乱，Istio是通过为服务配 Agent代理来提供服务发现、负截均衡、限流、链路跟踪、鉴权等微服务治理手段。

Istio开始就是与k8s结合设计的，Istio结合k8s可以牛逼的落地微服务架构。

优点：

1、关注点分离，对代码无侵入

2、服务治理相关较全面

缺点：

1、老子学不动了

五、我理想中的微服务架构

Java灵魂拷问

发表于 2019-10-24

Java集合

ArrayList 和 Vector 的区别。
说说 ArrayList,Vector, LinkedList 的存储性能和特性。
快速失败 (fail-fast) 和安全失败 (fail-safe) 的区别是什么？
hashmap 的数据结构。
HashMap 的工作原理是什么?
Hashmap 什么时候进行扩容呢？
List、Map、Set 三个接口，存取元素时，各有什么特点？
Set 里的元素是不能重复的，那么用什么方法来区分重复与否呢? 是用 == 还是 equals()? 它们有何区别?
两个对象值相同 (x.equals(y) == true)，但却可有不同的 hash code，这句话对不对?
heap 和 stack 有什么区别。
Java 集合类框架的基本接口有哪些？
HashSet 和 TreeSet 有什么区别？
HashSet 的底层实现是什么?
LinkedHashMap 的实现原理?
为什么集合类没有实现 Cloneable 和 Serializable 接口？
什么是迭代器 (Iterator)？
Iterator 和 ListIterator 的区别是什么？
数组 (Array) 和列表 (ArrayList) 有什么区别？什么时候应该使用 Array 而不是 ArrayList？
Java 集合类框架的最佳实践有哪些？
Set 里的元素是不能重复的，那么用什么方法来区分重复与否呢？是用 == 还是 equals()？它们有何区别？
Comparable 和 Comparator 接口是干什么的？列出它们的区别
Collection 和 Collections 的区别。

JVM与调优

Java 类加载过程？
描述一下 JVM 加载 Class 文件的原理机制?
Java 内存分配。
GC 是什么? 为什么要有 GC？
简述 Java 垃圾回收机制
如何判断一个对象是否存活？（或者 GC 对象的判定方法）
垃圾回收的优点和原理。并考虑 2 种回收机制
垃圾回收器的基本原理是什么？垃圾回收器可以马上回收内存吗？有什么办法主动通知虚拟机进行垃圾回收？
Java 中会存在内存泄漏吗，请简单描述
深拷贝和浅拷贝。
System.gc() 和 Runtime.gc() 会做什么事情？
finalize() 方法什么时候被调用？析构函数 (finalization) 的目的是什么？
如果对象的引用被置为 null，垃圾收集器是否会立即释放对象占用的内存？
什么是分布式垃圾回收（DGC）？它是如何工作的？
串行（serial）收集器和吞吐量（throughput）收集器的区别是什么？
在 Java 中，对象什么时候可以被垃圾回收？
简述 Java 内存分配与回收策率以及 Minor GC 和 Major GC。
JVM 的永久代中会发生垃圾回收么？
Java 中垃圾收集的方法有哪些？
什么是类加载器，类加载器有哪些？
类加载器双亲委派模型机制？

并发编程

Synchronized 用过吗，其原理是什么？
你刚才提到获取对象的锁，这个“锁”到底是什么？如何确定对象的锁？
什么是可重入性，为什么说 Synchronized 是可重入锁？
JVM 对 Java 的原生锁做了哪些优化？48
为什么说 Synchronized 是非公平锁？49
什么是锁消除和锁粗化？49
为什么说 Synchronized 是一个悲观锁？乐观锁的实现原理又是什么？什么是 CAS，它有什么特性？
乐观锁一定就是好的吗？
跟 Synchronized 相比，可重入锁 ReentrantLock 其实现原理有什么不同？
那么请谈谈 AQS 框架是怎么回事儿？
请尽可能详尽地对比下 Synchronized 和 ReentrantLock 的异同。
ReentrantLock 是如何实现可重入性的？
除了 ReetrantLock，你还接触过 JUC 中的哪些并发工具？
请谈谈 ReadWriteLock 和 StampedLock。
如何让 Java 的线程彼此同步？你了解过哪些同步器？请分别介绍下。
CyclicBarrier 和 CountDownLatch 看起来很相似，请对比下呢？
Java 线程池相关问题
Java 中的线程池是如何实现的？
创建线程池的几个核心构造参数？
线程池中的线程是怎么创建的？是一开始就随着线程池的启动创建好的吗？
既然提到可以通过配置不同参数创建出不同的线程池，那么 Java 中默认实现好的线程池又有哪些呢？请比较它们的异同
如何在 Java 线程池中提交线程？
什么是 Java 的内存模型，Java 中各个线程是怎么彼此看到对方的变量的？
请谈谈 volatile 有什么特点，为什么它能保证变量对所有线程的可见性？
既然 volatile 能够保证线程间的变量可见性，是不是就意味着基于 volatile 变量的运算就是并发安全的？
请对比下 volatile 对比 Synchronized 的异同。
请谈谈 ThreadLocal 是怎么解决并发安全的？
很多人都说要慎用 ThreadLocal，谈谈你的理解，使用 ThreadLocal 需要注意些什么？

spring

什么是 Spring 框架？Spring 框架有哪些主要模块？
使用 Spring 框架能带来哪些好处？
什么是控制反转(IOC)？什么是依赖注入？
请解释下 Spring 框架中的 IoC？
BeanFactory 和 ApplicationContext 有什么区别？
Spring 有几种配置方式？
如何用基于 XML 配置的方式配置 Spring？
如何用基于 Java 配置的方式配置 Spring？
怎样用注解的方式配置 Spring？
请解释 Spring Bean 的生命周期？
Spring Bean 的作用域之间有什么区别？
什么是 Spring inner beans？
Spring 框架中的单例 Beans 是线程安全的么？
请举例说明如何在 Spring 中注入一个 Java Collection？
如何向 Spring Bean 中注入一个 Java.util.Properties？
请解释 Spring Bean 的自动装配？
请解释自动装配模式的区别？
如何开启基于注解的自动装配？
请举例解释@Required 注解？
请举例解释@Autowired 注解？
请举例说明@Qualifier 注解？
构造方法注入和设值注入有什么区别？
Spring 框架中有哪些不同类型的事件？
FileSystemResource 和 ClassPathResource 有何区别？
Spring 框架中都用到了哪些设计模式？

设计模式

请列举出在 JDK 中几个常用的设计模式？
什么是设计模式？你是否在你的代码里面使用过任何设计模式？
Java 中什么叫单例设计模式？请用 Java 写出线程安全的单例模式
在 Java 中，什么叫观察者设计模式（observer design pattern）？
使用工厂模式最主要的好处是什么？在哪里使用？
举一个用 Java 实现的装饰模式(decorator design pattern)？它是作用于对象层次还是类层次？
在 Java 中，为什么不允许从静态方法中访问非静态变量？
设计一个 ATM 机，请说出你的设计思路？
在 Java 中，什么时候用重载，什么时候用重写？
举例说明什么情况下会更倾向于使用抽象类而不是接口

springboot

什么是 Spring Boot？
Spring Boot 有哪些优点？
什么是 JavaConfig？
如何重新加载 Spring Boot 上的更改，而无需重新启动服务器？
Spring Boot 中的监视器是什么？
如何在 Spring Boot 中禁用 Actuator 端点安全性？
如何在自定义端口上运行 Spring Boot 应用程序？
什么是 YAML？
如何实现 Spring Boot 应用程序的安全性？
如何集成 Spring Boot 和 ActiveMQ？
如何使用 Spring Boot 实现分页和排序？
什么是 Swagger？你用 Spring Boot 实现了它吗？
什么是 Spring Profiles？
什么是 Spring Batch？
什么是 FreeMarker 模板？
如何使用 Spring Boot 实现异常处理？
您使用了哪些 starter maven 依赖项？
什么是 CSRF 攻击？
什么是 WebSockets？
什么是 AOP？
什么是 Apache Kafka？
我们如何监视所有 Spring Boot 微服务？

Netty

BIO、NIO和AIO的区别？
NIO的组成？
Netty的特点？
Netty的线程模型？
TCP 粘包/拆包的原因及解决方法？
了解哪几种序列化协议？
如何选择序列化协议？
Netty的零拷贝实现？
Netty的高性能表现在哪些方面？
NIOEventLoopGroup源码？

Redis

什么是redis?
Reids的特点
Redis支持的数据类型
Redis是单进程单线程的
虚拟内存
Redis锁
读写分离模型
数据分片模型
Redis的回收策略
使用Redis有哪些好处？
redis相比memcached有哪些优势？4
redis常见性能问题和解决方案
MySQL里有2000w数据，redis中只存20w的数据，如何保证redis中的数据都是热点数据245
Memcache与Redis的区别都有哪些？
Redis 常见的性能问题都有哪些？如何解决？
Redis 最适合的场景

退避算法

发表于 2019-09-06

CSMA/CD采用二进制指数退避算法，又称为二元指数后退算法。退避算法是以冲突窗口大小为基准的，每个节点有一个冲突计数器C。退避的时间与冲突次数具有指数关系，冲突次数越多，退避的时间就可能越长，若达到限定的冲突次数，该节点就停止发送数据。

定义

二进制退避技术（Binary Exponential Back off）. 指在遇到重复的冲突时，站点将重复传输，但在每一次冲突之后，随着时延的平均值将加倍。二进制指数退避算法提供了一个处理重负荷的方法。尝试传输的重复失败导致更长的退避时间，这将有助于负荷的平滑。如果没有这样的退避，以下状况可能发生：两个或多站点同时尝试传输，这将导致冲突，之后这些站点又立即尝试重传，导致一个新冲突。

应用

在CSMA/CD协议中，一旦检测到冲突，为降低再冲突的概率，需要等待一个随机时间，然后再使用CSMA方法试图传输。为了保证这种退避维持稳定，采用了二进制指数退避算法的技术，其算法过程如下：

确定基本退避时间，一般为端到端的往返时间为2t，2t也称为冲突窗口或争用期。
定义参数k，k与冲突次数有关，规定k不能超过10，k=Min[冲突次数，10]。在冲突次数大于10，小于16时，k不再增大，一直取值为10。
从离散的整数集合[0,1,2，……，(2^k-1)]中随机的取出一个数r，等待的时延为r倍的基本退避时间，等于r x 2t。r的取值范围与冲突次数k有关，r可选的随机取值为2^k个、这也是称为二进制退避算法的起因。
当冲突次数大于10以后，都是从0—2^10-1个2t中随机选择一个作为等待时间。
当冲突次数超过16次后，发送失败，丢弃传输的帧，发送错误报告。

举例

如果第二次发生碰撞：

n = 2

k = MIN(2,10) = 2

R = {0, 1, 2, 3)

延迟时间 = R * 512 * Bit-time

其中：Bit-time = 1 / Debit

例如：

对于传输速率Debit = 10 Mbit/s,

那么Bit-time = 0.1 us

延迟时间={0, 51.2 us, 102.4 us, 153.6 us} 其中任取一