MySQL参数优化

（3）修改方式举例
vim /etc/my.cnf
Max_connections=1024

补充:
1.开启数据库时,我们可以临时设置一个比较大的测试值
2.观察show status like ‘Max_used_connections’;变化
3.如果max_used_connections跟max_connections相同,
那么就是max_connections设置过低或者超过服务器的负载上限了，
低于10%则设置过大.

back_log
（1）简介
mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它
就会起作用，如果mysql的连接数据达到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

wait_timeout和interactive_timeout
（1）简介
wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数
interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动的断开，默认的是28800，可调优为7200。
wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用
（2）设置建议
如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接，一般希望wait_timeout尽可能低
（3）修改方式举例
wait_timeout=60
interactive_timeout=1200
长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。
一般我们会将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不需要调整。
另外还可以使用类外的参数弥补。

key_buffer_size
（1）简介
key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度
《1》此参数与myisam表的索引有关
《2》临时表的创建有关（多表链接、子查询中、union）
在有以上查询语句出现的时候，需要创建临时表，用完之后会被丢弃
临时表有两种创建方式：
内存中------->key_buffer_size
磁盘上------->ibdata1(5.6)
ibtmp1 (5.7）
（2）设置依据
通过key_read_requests和key_reads可以直到key_buffer_size设置是否合理。
mysql> show variables like “key_buffer_size%”;
±----------------±--------+
| Variable_name | Value |
±----------------±--------+
| key_buffer_size | 8388608 |
±----------------±--------+
1 row in set (0.00 sec)

mysql>
一共有10个索引读取请求，有2个请求在内存中没有找到直接从硬盘中读取索引
控制在 5%以内。
注：key_buffer_size只对myisam表起作用，即使不使用myisam表，但是内部的临时磁盘表是myisam表，也要使用该值。
可以使用检查状态值created_tmp_disk_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

max_connect_errors
max_connect_errors是一个mysql中与安全有关的计数器值，
它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，
mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令
清空此host的相关信息 max_connect_errors的值与性能并无太大关系。
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容
max_connect_errors=2000

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

max_allowed_packet
（1）简介：
mysql根据配置文件会限制，server接受的数据包大小。
（2）配置依据：
有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，
更大值是1GB，必须设置1024的倍数
（3）配置方法：
max_allowed_packet=32M

join_buffer_size
select a.name,b.name from a join b on a.id=b.id where xxxx
用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接
独享。
尽量在SQL与方面进行优化，效果较为明显。
优化的方法：在on条件列加索引，至少应当是有MUL索引

thread_cache_size
(1)简介
服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,
那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁
(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存
中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加
这个值可以改善系统性能.
（2）配置依据
通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。
设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。
服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)

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

innodb_buffer_pool_size
（1）简介
对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。
（2）配置依据：
InnoDB使用该参数指定大小的内存来缓冲数据和索引。
对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%,一般我们建议不要超过物理内存的70%。
（3）配置方法
innodb_buffer_pool_size=2048M

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

innodb_thread_concurrency
（1）简介
此参数用来设置innodb线程的并发数量，默认值为0表示不限制。
(2)配置依据
如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，
并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，

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

设定依据：
1、大事务：存储过程调用 CALL
2、多事务

innodb_log_file_size = 100M
设置 ib_logfile0 ib_logfile1
此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.
innodb_log_file_size = 100M

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

read_buffer_size = 1M
MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为
它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描
进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。
和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享

read_rnd_buffer_size = 1M
MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序
)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免
磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个
客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。

bulk_insert_buffer_size = 8M
批量插入数据缓存大小，可以有效提高插入效率，默认为8M

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标准(基于安全的控制)：
sync_binlog=1 什么时候刷新binlog到磁盘，每次事务commit
innodb_flush_log_at_trx_commit=1
set sql_log_bin=0;
show status like ‘com_%’;

安全参数
Innodb_flush_method=(O_DIRECT, fsync)
1、fsync ：
（1）在数据页需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
2、 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

（2）锁监控及处理

（1）查看当前有无锁等待
SHOW STATUS LIKE ‘innodb_row_lock%’;

（2）查看哪个事务在等待(被阻塞了)
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state=‘LOCK WAIT’\G;
trx_id : 事务ID号
trx_state : 当前事务的状态
trx_mysql_thread_id:连接层的,连接线程ID(SHOW PROCESSLIST ===>Id或trx_id )
trx_query : 当前被阻塞的操作(一般是要丢给开发的)

（3）查看锁源,谁锁的我!
SELECT * FROM sys.innodb_lock_waits\G;
locked_table : 哪张表出现的等待
waiting_trx_id: 等待的事务(与上个视图trx_id 对应)
waiting_pid : 等待的线程号(与上个视图trx_mysql_thread_id)
blocking_trx_id : 锁源的事务ID
blocking_pid : 锁源的线程号

（4）找到锁源的thread_id
SELECT * FROM performance_schema.threads WHERE processlist_id=15;
====> 41

（5）找到锁源的SQL语句
– 当前在执行的语句
SELECT * FROM performance_schema.events_statements_current WHERE thread_id=41;
– 执行语句的历史
SELECT * FROM performance_schema.events_statements_history WHERE thread_id=41;

死锁监控
show engine innodb status\G
show variables like ‘%deadlock%’;
vim /etc/my.cnf
innodb_print_all_deadlocks = 1

当前运行的所有事务

SELECT * FROM information_schema.INNODB_TRX;

当前出现的锁

SELECT * FROM information_schema.INNODB_LOCKs;

锁等待的对应关系

SELECT * FROM information_schema.INNODB_LOCK_waits;

PROCESSLIST表记录了每个MySql线程的用户，地址以及操作的db等其他信息。

①SELECT * FROM information_schema.PROCESSLIST;
或者
②SHOW PROCESSLIST

一、information_schema.INNODB_TRX表详情

innodb_trx 表的每个字段解释如下：
trx_id：事务ID。
trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
trx_started：事务开始时间。
trx_requested_lock_id：事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。
trx_wait_started：事务开始等待的时间。
trx_weight：事务的权重。
trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN。
trx_query：事务正在执行的 SQL 语句。
trx_operation_state：事务当前操作状态。
trx_tables_in_use：当前事务执行的 SQL 中使用的表的个数。
trx_tables_locked：当前执行 SQL 的行锁数量。
trx_lock_structs：事务保留的锁数量。
trx_lock_memory_bytes：事务锁住的内存大小，单位为 BYTES。
trx_rows_locked：事务锁住的记录数。包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行。
trx_rows_modified：事务更改的行数。
trx_concurrency_tickets：事务并发票数。
trx_isolation_level：当前事务的隔离级别。
trx_unique_checks：是否打开唯一性检查的标识。
trx_foreign_key_checks：是否打开外键检查的标识。
trx_last_foreign_key_error：最后一次的外键错误信息。
trx_adaptive_hash_latched：自适应散列索引是否被当前事务锁住的标识。
trx_adaptive_hash_timeout：是否立刻放弃为自适应散列索引搜索 LATCH 的标识。

二、information_schema.INNODB_LOCKS表详情

innodb_locks 表的每个字段解释如下

lock_id：锁 ID。
lock_trx_id：拥有锁的事务 ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。
lock_mode：锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。
lock_type：锁的类型。RECORD 代表行级锁，TABLE 代表表级锁。
lock_table：被锁定的或者包含锁定记录的表的名称。
lock_index：当 LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL。
lock_space：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。
lock_page：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。
lock_rec：当 LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。
lock_data：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL。

三、information_schema.INNODB_LOCK_waits表详情

innodb_lock_waits 表的每个字段解释如下：

requesting_trx_id：请求事务的 ID。
requested_lock_id：事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。
blocking_trx_id：阻塞事务的 ID。
blocking_lock_id：某一事务的锁的 ID，该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。

四、INFORMATION_SCHEMA.PROCESSLIST表详情

PROCESSLIST 表的每个字段解释如下：

id:标识ID。这与在SHOW PROCESSLIST语句的Id列、Performance Schema threads表的PROCESSLIST_ID列中显示的值类型相同，并由CONNECTION_ID()函数返回
user:发出该语句的mysql用户。
host：发出该语句的客户机的主机名(系统用户除外，没有主机)。
db：默认数据库。
command：线程正在执行的命令的类型。
time：线程处于当前状态的时间(以秒为单位)。
state：指示线程正在执行的操作、事件或状态。
info：线程正在执行的语句，如果没有执行任何语句，则为NULL。

五、事务一直处于RUNNING状态解决办法

1、需要区分的是 Lock wait timeout exceeded 与 Dead Lock 是不一样。

Lock wait timeout exceeded：后提交的事务等待前面处理的事务释放锁，但是在等待的时候超过了mysql的锁等待时间，就会引发这个异常。
Dead Lock：两个事务互相等待对方释放相同资源的锁，从而造成的死循环，就会引发这个异常。

2、还有一个要注意的是 innodb_lock_wait_timeout 与 lock_wait_timeout 也是不一样的。

innodb_lock_wait_timeout：innodb的dml操作的行级锁的等待时间。
lock_wait_timeout：数据结构ddl操作的锁的等待时间。

定位步骤如下：

（1）使用如下语句查看事务，找到状态为RUNNING的记录
SELECT * FROM information_schema.INNODB_TRX;

（2）通过trx_mysql_thread_id去查询information_schema.processlist表，找到执行事务的客户端请求的SQL线程
select * from information_schema.PROCESSLIST WHERE ID = ‘xxx’;

（3）查看到端口和host以后，再到对应的服务器查看相关的应用和日志
netstat -nlatp | grep 23452
ps -eaf | grep 12059

（4）如果无法定位，此时我们需要从performance_schema表寻找特定线程的信息

# 查看事件
select * from performance_schema.events_statements_current

（5）根据我们拿到的线程id去查，可以获取到具体的执行sql
select * from performance_schema.events_statements_current
where THREAD_ID in (select THREAD_ID from performance_schema.threads where PROCESSLIST_ID=15844)

（3）主从优化

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=3306,
MASTER_AUTO_POSITION=1;
start slave;

查询一个长事物
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60