“这是我参与8月更文挑战的第14天，活动详情查看：8月更文挑战”

1. 优化哲学

1.1 优化有风险

1.2 优化的范围

存储、主机和操作系统：
- 主机架构稳定性
- I/O 规划及配置
- Swap
- OS 内核参数
- 网络问题
应用程序：Index、lock、session
- 应用程序稳定性和性能
- SQL 语句性能
- 串行访问资源
- 性能欠佳会话管理
数据库优化：内存、数据库设计、参数
- 内存
- 数据库结构（物理 & 逻辑）
- 实例配置

1.3 优化效果评估

2. 优化工具介绍

2.1 系统层

CPU
- 计算（主）
- 调度（次）
IO
- 输入
- 输出
MEM
- 缓存
- 缓冲

2.1.1 top 命令

Cpu(s):  0.1%us,  0.1%sy,  0.0%ni, 99.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st

id：空闲的 CPU 时间片占比
us：用户程序工作所占用的时间片占比
sy：内核工作花费的 CPU 时间片占比
- 过高的原因
  - 内核本身的 bug
  - 并发很高
  - 锁
wa：CPU 用来等待的时间片占比

按 1 查看每个 CPU 运行情况，查看是否均匀

Cpu0  :  0.3%us,  0.3%sy,  0.0%ni, 99.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu1  :  2.7%us,  0.7%sy,  0.0%ni, 96.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu2  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu3  :  1.0%us,  1.3%sy,  0.0%ni, 97.6%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu4  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu5  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu6  :  0.7%us,  0.7%sy,  0.0%ni, 98.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu7  :  0.7%us,  0.0%sy,  0.0%ni, 99.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu8  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu9  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu10 :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st

2.1.2 iostat 命令

iostat -dk 1

2.1.3 vmstat 命令

vmstat 1

3. 优化思路

3.1 主机、存储、网络

3.1.1 主机

真实的硬件（PC Server）: DELL R系列，华为，浪潮，HP，联想
云产品：ECS、数据库 RDS、DRDS
IBM 小型机 P6 570 595 P7 720 750 780 P8

3.1.2 CPU 根据数据库类型

OLTP
OLAP
IO密集型：线上系统，OLTP 主要是 IO 密集型的业务，高并发
CPU密集型：数据分析数据处理，OLAP，cpu 密集型的，需要 CPU 高计算能力（i 系列，IBM power 系列）
CPU密集型： I 系列的，主频很高，核心少
IO密集型： E 系列（至强），主频相对低，核心数量多

3.1.3 内存

建议 2- 3倍 cpu 核心数量（ECC）

3.1.4 磁盘选择

SATA-III SAS Fc SSD（sata） pci-e ssd Flash 主机 RAID 卡的 BBU(Battery Backup Unit) 关闭

3.1.5 存储

根据存储数据种类的不同，选择不同的存储设备配置合理的 RAID 级别(raid5、raid10、热备盘)

r0 :条带化，性能高
r1 :镜像，安全
r5 :校验 + 条带化，安全较高+性能较高（读），写性能较低（适合于读多写少）
r10：安全 + 性能都很高，最少四块盘，浪费一半的空间（高 IO 要求）

3.1.6 网络

1、硬件买好的（单卡单口） 2、网卡绑定（bonding），交换机堆叠

以上问题，提前规避掉。

3.2 操作系统优化

3.2.1 Swap 调整

echo 0 >/proc/sys/vm/swappiness 的内容改成 0（临时），

/etc/sysctl.conf 上添加 vm.swappiness=0（永久）

sysctl -p

这个参数决定了 Linux 是倾向于使用 swap，还是倾向于释放文件系统 cache。在内存紧张的情况下，数值越低越倾向于释放文件系统 cache。

当然，这个参数只能减少使用 swap 的概率，并不能避免 Linux 使用 swap。

修改 MySQL 的配置参数 innodb_flush_method，开启 O_DIRECT 模式，这种情况下，InnoDB的 buffer pool 会直接绕过文件系统 cache 来访问磁盘，但是 redo log 依旧会使用文件系统 cache。值得注意的是，Redo log 是覆写模式的，即使使用了文件系统的 cache，也不会占用太多。

3.2.2 IO 调度策略

centos 7 默认是 deadline

cat /sys/block/sda/queue/scheduler

临时修改为 deadline(centos6)：echo deadline >/sys/block/sda/queue/scheduler vi /boot/grub/grub.conf

更改到如下内容:

kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet

IO：
- raid
- no lvm
- ext4 或 xfs
- ssd
- IO 调度策略

提前规划好以上所有问题，减轻 MySQL 优化的难度。

4. 优化细节

4.1 参数优化

4.1.1 Max_connections

1. 简介

Mysql 的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql 会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。

2. 判断依据

show variables like 'max_connections';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | max_connections | 151   |
    +-----------------+-------+
show status like 'Max_used_connections';
    +----------------------+-------+
    | Variable_name        | Value |
    +----------------------+-------+
    | Max_used_connections | 101   |
    +----------------------+-------+

3. 修改方式举例

vim /etc/my.cnf
Max_connections=1024

补充：

开启数据库时,我们可以临时设置一个比较大的测试值
观察 show status like 'Max_used_connections'; 变化
如果 max_used_connections 跟 max_connections 相同，那么就是max_connections 设置过低或者超过服务器的负载上限了。

4.1.2 back_log

1. 简介

mysql 能暂存的连接数量，当主要 mysql 线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果 mysq l的连接数据达到 max_connections 时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及 back_log，如果等待连接的数量超过 back_log，将不被授予连接资源。

back_log 值指出在 mysql 暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它。

2. 判断依据

show full processlist

发现大量的待连接进程时，就需要加大 back_log 或者加大 max_connections 的值

3. 修改方式举例

vim /etc/my.cnf
back_log=1024

4.1.3 wait_timeout 和 interactive_timeout

1. 简介

wait_timeout：
- 指的是 mysql 在关闭一个非交互的连接之前所要等待的秒数
- 如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用
interactive_timeout：
- 指的是 mysql 在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行 mysql 管理，使用的即时交互的连接，这时候，如果没有操作的时间超过了 interactive_time 设置的时间就会自动的断开，默认的是 28800，可调优为 7200。

2. 设置建议

如果设置太大，容易造成连接打开时间过长，在 show processlist 时候，能看到很多的连接，一般希望 wait_timeout 尽可能低

3. 修改方式举例

wait_timeout=60
interactive_timeout=1200

长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。一般我们会将 wait_timeout 设定比较小，interactive_timeout 要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不需要调整。另外还可以使用类外的参数弥补。

4.1.4 key_buffer_size

1. 简介

key_buffer_size 指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度

此参数与 myisam 表的索引有关
临时表的创建有关（多表链接、子查询中、union）。在有以上查询语句出现的时候，需要创建临时表，用完之后会被丢弃
临时表有两种创建方式：
1. 内存中------->key_buffer_size
2. 磁盘上------->ibdata1(5.6)、ibtmp1 (5.7）

2. 设置依据

通过 key_read_requests 和 key_reads 可以直到 key_baffer_size 设置是否合理。

mysql> show variables like "key_buffer_size%";
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.00 sec)

mysql>
mysql> show status like "key_read%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 10    |
| Key_reads         | 2     |
+-------------------+-------+
2 rows in set (0.00 sec)

一共有 10 个索引读取请求，有 2 个请求在内存中没有找到直接从硬盘中读取索引控制在 5% 以内。注：key_buffer_size 只对 myisam 表起作用，即使不使用 myisam 表，但是内部的临时磁盘表是 myisam 表，也要使用该值。

可以使用检查状态值 created_tmp_disk_tables 得知：

mysql> show status like "created_tmp%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

通常地，我们习惯以 Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables) 、 Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)

或者已各自的一个时段内的差额计算，来判断基于内存的临时表利用率。所以，我们会比较关注 Created_tmp_disk_tables 是否过多，从而认定当前服务器运行状况的优劣。 Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 控制在 5%-10% 以内

看以下例子：

在调用 mysqldump 备份数据时，大概执行步骤如下：

180322 17:39:33       7 Connect     root@localhost on
7 Query       /*!40100 SET @@SQL_MODE='' */
7 Init DB     guo
7 Query       SHOW TABLES LIKE 'guo'
7 Query       LOCK TABLES `guo` READ /*!32311 LOCAL */
7 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=1
7 Query       show create table `guo`
7 Query       show fields from `guo`
7 Query       show table status like 'guo'
7 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `guo`
7 Query       UNLOCK TABLES
7 Quit

其中，有一步是：show fields from guo。从 slow query 记录的执行计划中，可以知道它也产生了 ·Tmp_table_on_disk·。

所以说，以上公式并不能真正反映到 mysql 里临时表的利用率，有些情况下产生的 Tmp_table_on_disk。我们完全不用担心，因此没必要过分关注 ·Created_tmp_disk_tables·，但如果它的值大的离谱的话，那就好好查一下，你的服务器到底都在执行什么查询了。

3. 配置方法

key_buffer_size=64M

4.1.5 max_connect_errors

max_connect_errors 是一个 mysql 中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql 服务器将禁止 host 的连接请求，直到mysql 服务器重启或通过 flush hosts 命令清空此 host 的相关信息。

max_connect_errors 的值与性能并无太大关系。

修改 /etc/my.cnf文件，在 [mysqld] 下面添加如下内容

max_connect_errors=2000

4.1.6 sort_buffer_size

1. 简介：

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速

ORDER BY
GROUP BY
distinct
union

2. 配置依据

Sort_Buffer_Size 并不是越大越好，由于是 connection 级的参数，过大的设置+高并发可能会耗尽系统内存资源。例如：500 个连接将会消耗 500*sort_buffer_size（2M）=1G 内存

3. 配置方法

修改 /etc/my.cnf 文件，在 [mysqld] 下面添加如下：sort_buffer_size=1M

4.1.7 max_allowed_packet

1. 简介：

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

2. 配置依据：

有时候大的插入和更新会受 max_allowed_packet 参数限制，导致写入或者更新失败，更大值是 1GB，必须设置 1024 的倍数

3. 配置方法：

max_allowed_packet=32M

4.1.8 thread_cache_size

1. 简介

服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁（前提是缓存数未达上限），如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能。

2. 配置依据

通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。设置规则如下：1GB 内存配置为 8，2GB 配置为 16，3GB 配置为 32，4GB 或更高内存，可配置更大。

服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）

试图连接到 MySQL （不管是否连接成功）的连接数

mysql>  show status like 'threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 8     |
| Threads_connected | 2     |
| Threads_created   | 4783  |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

Threads_cached ：代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected：代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created：代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created 值过大的话，表明 MySQL 服务器一直在创建线程，这也是比较耗 cpu SYS资源，可以适当增加配置文件中 thread_cache_size 值。
Threads_running ：代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于 sleep 状态。

3. 配置方法：

thread_cache_size=32

整理

Threads_created：一般在架构设计阶段，会设置一个测试值，做压力测试。

结合 zabbix 监控，看一段时间内此状态的变化。

如果在一段时间内，Threads_created 趋于平稳，说明对应参数设定是 OK。

如果一直陡峭的增长，或者出现大量峰值，那么继续增加此值的大小，在系统资源够用的情况下（内存）

4.1.9 innodb_buffer_pool_size（非常重要）

1. 简介

对于 InnoDB 表来说，innodb_buffer_pool_size 的作用就相当于 key_buffer_size 对于 MyISAM 表的作用一样。

2. 配置依据：

InnoDB 使用该参数指定大小的内存来缓冲数据和索引。

对于单独的 MySQL 数据库服务器，最大可以把该值设置成物理内存的 80%,一般我们建议不要超过物理内存的70%。

如何查看是否够用：

mysql> show engine innodb status\G
Total large memory allocated 137428992
Dictionary memory allocated 160964
Buffer pool size   8192
Free buffers       7747
Database pages     445
Old database pages 0
Modified db pages  0
Pending reads      0

Buffer pool size 指的是内存页的个数，Database pages 指的是已经使用的页的个数。Free buffers 指的是剩余的页的个数，如果非常少的话，就需要调高了该值。但是如果该值已经超过了物理内存的 80%，就需要加内存条了。

3. 配置方法

innodb_buffer_pool_size=2048M

4.1.10 innodb_flush_log_at_trx_commit

1. 简介

主要控制了 innodb 将 log buffer 中的数据写入日志文件并 flush 磁盘的时间点，取值分别为 0、1、2 三个。

0：表示当事务提交时，不做日志写入操作，而是每秒钟将 log buffer 中的数据写入日志文件并 flush 磁盘一次；
1：每次事务的提交都会引起 redo 日志文件写入、flush 磁盘的操作，确保了事务的 ACID；
2：每次事务提交引起写入日志文件的动作,但每秒钟完成一次 flush 磁盘操作。

2. 配置依据

实际测试发现，该值对插入数据的速度影响非常大，设置为 2 时插入 10000 条记录只需要 2 秒，设置为 0 时只需要 1 秒，而设置为1时则需要 229 秒。因此，MySQL 手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。

根据 MySQL 官方文档，在允许丢失最近部分事务的危险的前提下，可以把该值设为 0 或 2。

3. 配置方法

innodb_flush_log_at_trx_commit=1

双 1 标准中的一个 1

4.1.11 innodb_thread_concurrency

1. 简介

此参数用来设置 innodb 线程的并发数量，默认值为 0 表示不限制。

2. 配置依据

在官方 doc 上，对于 innodb_thread_concurrency 的使用，也给出了一些建议，如下：

如果一个工作负载中，并发用户线程的数量小于 64，建议设置innodb_thread_concurrency=0；
如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，并通过不断的降低这个参数，96, 80, 64 等等，直到发现能够提供最佳性能的线程数，例如，假设系统通常有 40 到 50 个用户，但定期的数量增加至 60，70，甚至 200。你会发现，性能在 80 个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，建议设置 innodb_thread_concurrency 参数为 80，以避免影响性能。
如果你不希望 InnoDB 使用的虚拟 CPU 数量比用户线程使用的虚拟 CPU 更多（比如 20 个虚拟 CPU），建议通过设置 innodb_thread_concurrency 参数为这个值（也可能更低，这取决于性能体现），如果你的目标是将 MySQL 与其他应用隔离，你可以考虑绑定 mysqld 进程到专有的虚拟 CPU。但是需要注意的是，这种绑定，在 myslqd 进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，你可能会设置 mysqld 进程绑定的虚拟 CPU，允许其他应用程序使用虚拟 CPU 的一部分或全部。
在某些情况下，最佳的 innodb_thread_concurrency参数设置可以比虚拟 CPU 的数量小。
定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency 参数的设置进行调整。

128 -----> top cpu

设置标准：

当前系统 cpu 使用情况，均不均匀

top

当前的连接数，有没有达到顶峰

show status like 'threads_%';
show processlist;

3. 配置方法：

innodb_thread_concurrency=8

方法：

看 top ,观察每个 cpu 的各自的负载情况
发现不平均,先设置参数为 cpu 个数，然后不断增加(一倍)这个数值
一直观察 top 状态,直到达到比较均匀时，说明已经到位了。

4.1.12 innodb_log_buffer_size

此参数确定些日志文件所用的内存大小，以 M 为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。

innodb_log_buffer_size=128M

设定依据：

大事务：存储过程调用 CALL
多事务

查看现在提交的事务：

mysql> show status like '%commit%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Com_commit     | 0     |
| Com_xa_commit  | 0     |
| Handler_commit | 0     |
+----------------+-------+
3 rows in set (0.09 sec)

如果 Com_commit 过大的话，则提高 innodb_log_buffer_size 大小。

还可以查看一下 IO，如果 IO 非常频繁（高）的话，也可以调一下。

4.1.13 innodb_log_file_size = 100M

设置 ib_logfile0、ib_logfile1

此参数确定数据日志文件的大小，以 M 为单位，更大的设置可以提高性能。

innodb_log_file_size = 100M

4.1.14 innodb_log_files_in_group

为提高性能，MySQL 可以以循环方式将日志文件写到多个文件。推荐设置为 3。

4.1.15 binary log

log-bin=/data/mysql-bin

binlog_cache_size = 2M # 为每个 session 分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录 bin-log 的效率。没有什么大事务，dml 也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml 操作也频繁，则可以适当的调大一点。前者建议是 --1M，后者建议是：即 2--4M
max_binlog_cache_size = 8M # 表示的是 binlog 能够使用的最大 cache 内存大小
max_binlog_size= 512M # 指定 binlog 日志文件的大小，如果当前的日志大小达到 max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于 1GB 或小于 4096 字节。默认值是 1GB。在导入大容量的 sql 文件时，建议关闭 sql_log_bin，否则硬盘扛不住，而且建议定期做删除。

expire_logs_days = 7 # 定义了 mysql 清除过期日志的时间。二进制日志自动删除的天数。默认值为 0，表示“没有自动删除”。

log-bin=/data/mysql-bin

binlog_format=row

sync_binlog=1 # 双 1 标准(基于安全的控制)：什么时候刷新 binlog 到磁盘，每次事务 commit

innodb_flush_log_at_trx_commit=1

set sql_log_bin=0;

show status like 'com_%';

4.1.16 安全参数

Innodb_flush_method=(O_DIRECT, fsync)

Innodb_flush_method=fsync：
1. 在数据页需要持久化时，首先将数据写入OS buffer 中，然后由 os 决定什么时候写入磁盘
2. 在 redo buffuer 需要持久化时，首先将数据写入 OS buffer 中，然后由 os 决定什么时候写入磁盘。但，如果 innodb_flush_log_at_trx_commit=1 的话，日志还是直接每次 commit 直接写入磁盘
Innodb_flush_method=O_DIRECT：
1. 在数据页需要持久化时，直接写入磁盘
2. 在 redo buffuer 需要持久化时，首先将数据写入 OS buffer 中，然后由os决定什么时候写入磁盘。但，如果 innodb_flush_log_at_trx_commit=1 的话，日志还是直接每次 commit 直接写入磁盘

最安全模式：

innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT

最高性能模式：

innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync

一般情况下，我们更偏向于安全。 “双一标准”：

innodb_flush_log_at_trx_commit=1
sync_binlog=1
innodb_flush_method=O_DIRECT

5. 参数优化结果

[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock

再次压力测试：

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='FGCD'" engine=innodb --number-of-queries=200000 -uroot -p123 -verbose

6. 锁的监控及处理

6.1 锁等待模拟

Record Lock
Next Lock
GAP Lock

6.2 监控锁状态

锁状态监控

6.3 锁排查过程

6.3.1 监控有没有锁等待

show status like 'innodb_row_lock%';

关注点：

Innodb_row_lock_current_waits：当前有多少锁等待
Innodb_row_lock_waits：一共发生过多少锁等待

6.3.2 查看等待的事务

select * from information_schema.INNODB_TRX where trx_state='LOCK WAIT';

6.3.3 查看锁源，谁锁的我

select * from sys.innodb_lock_waits;

locked_table：产生锁等待的表
locked_type：锁的类型（record，gap，nextlock）
waiting_trx_id：等待的事务的 ID
waiting_pid：等待事务的连接线程 ID
waiting_query：等待的事务语句
waiting_lock_mode：等待锁的类型（X，S）
blocking_trx_id：锁源的事务 ID
blocking_pid：锁源的事务连接线程 ID
sql_kill_blocking_connection：处理建议

6.3.4 根据锁源的 pid，赵铎锁源 SQL 的线程 ID

select * from performance_schema.threads where processlist_id=1691;

thread_id：执行 SQL 的线程 ID
name：线程名称
processlist_id：接收返回数据的线程 ID

6.3.5 根据锁源 SQL 线程 ID，找到锁源的 SQL 语句

select * from performance_schema.event_statements_current where thread_id=1716;

thread_id：线程 ID
event_name：操作类型
sql_text：SQL 语句

6.4 死锁监控

show engine innodb status\G
show variables like '%deadlock%';

vim /etc/my.cnf
innodb_print_all_deadlocks = 1

7. 主从优化

## 5.7 从库多线程MTS
基本要求:
5.7以上的版本(忘记小版本)
必须开启GTID
binlog必须是row模式

gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

5.7 :
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=8
cpu核心数作为标准

CHANGE MASTER TO
  MASTER_HOST='10.0.0.128',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_AUTO_POSITION=1;
start slave;

MySQL 学习（15）—— MySQL 优化

1. 优化哲学

1.1 优化有风险

1.2 优化的范围

1.3 优化效果评估

2. 优化工具介绍

2.1 系统层

2.1.1 top 命令

2.1.2 iostat 命令

2.1.3 vmstat 命令

3. 优化思路

3.1 主机、存储、网络

3.1.1 主机

3.1.2 CPU 根据数据库类型

3.1.3 内存

3.1.4 磁盘选择

3.1.5 存储

3.1.6 网络

3.2 操作系统优化

3.2.1 Swap 调整

3.2.2 IO 调度策略

4. 优化细节

4.1 参数优化

4.1.1 Max_connections

1. 简介

2. 判断依据

3. 修改方式举例

4.1.2 back_log

1. 简介

2. 判断依据

3. 修改方式举例

4.1.3 wait_timeout 和 interactive_timeout

1. 简介

2. 设置建议

3. 修改方式举例

4.1.4 key_buffer_size

1. 简介

2. 设置依据

3. 配置方法

4.1.5 max_connect_errors

4.1.6 sort_buffer_size

1. 简介：

2. 配置依据

3. 配置方法

4.1.7 max_allowed_packet

1. 简介：

2. 配置依据：

3. 配置方法：

4.1.8 thread_cache_size

1. 简介

2. 配置依据

3. 配置方法：

整理

4.1.9 innodb_buffer_pool_size（非常重要）

1. 简介

2. 配置依据：

3. 配置方法

4.1.10 innodb_flush_log_at_trx_commit

1. 简介

2. 配置依据

3. 配置方法

4.1.11 innodb_thread_concurrency

1. 简介

2. 配置依据

3. 配置方法：

4.1.12 innodb_log_buffer_size

4.1.13 innodb_log_file_size = 100M

4.1.14 innodb_log_files_in_group

4.1.15 binary log

4.1.16 安全参数

5. 参数优化结果

6. 锁的监控及处理

6.1 锁等待模拟

6.2 监控锁状态

6.3 锁排查过程

6.3.1 监控有没有锁等待

6.3.2 查看等待的事务

6.3.3 查看锁源，谁锁的我

6.3.4 根据锁源的 pid，赵铎锁源 SQL 的线程 ID

6.3.5 根据锁源 SQL 线程 ID，找到锁源的 SQL 语句