innodb简单优化

innodb_flush_log_at_trx_commit, sync_binlog

双1标准
innodb_flush_log_at_trx_commit=1；sync_binlog=1
innodb_flush_log_at_trx_commit
值为0 : 提交事务的时候，不立即把 redo log buffer 里的数据刷入磁盘文件的，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。
值为1 : 提交事务的时候，就必须把 redo log 从内存刷入到磁盘文件里去，只要事务提交成功，那么 redo log 就必然在磁盘里了。注意，因为操作系统的“延迟写”特性，此时的刷入只是写到了操作系统的缓冲区中，因此执行同步操作才能保证一定持久化到了硬盘中。
值为2 : 提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件，可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。
可以看到，只有1才能真正地保证事务的持久性，但是由于刷新操作 fsync() 是阻塞的，直到完成后才返回，我们知道写磁盘的速度是很慢的，因此 MySQL 的性能会明显地下降。如果不在乎事务丢失，0和2能获得更高的性能。
Mysql官方建议，在允许少量数据丢失的情况下，把该值设置成0或者2

select @@innodb_flush_log_at_trx_commit;

sync_binlog

该参数控制着二进制日志写入磁盘的过程。
该参数的有效值为0 、1、N：
0：默认值。事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作（fsync()），此时只是写入了操作系统缓冲，若操作系统宕机则会丢失部分二进制日志。
1：事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。
N：每写N次操作系统缓冲就执行一次刷新操作。
将这个参数设为1以上的数值会提高数据库的性能，但同时会伴随数据丢失的风险。
二进制日志文件涉及到数据的恢复，以及想在主从之间获得最大的一致性，那么应该将该参数设置为1，但同时也会造成一定的性能损耗。

innodb_thread_concurrency

innodb_thread_concurrency是动态参数可以随时修改

• 64个活跃连接以内直接配0
• 高压场景需要从高到低测试，找到最优值
• 高压场景下较低的值可以明显提高写入QPS的占比（高频率的读被限制了）

innodb_thread_sleep_delay（微秒）

• 定义在开始排队前，等多久加入队列

innodb_adaptive_max_sleep_delay（微秒）

• 配置innodb_thread_sleep_delay允许的最大值，配了之后innodb会自动调整innodb_thread_sleep_delay的值到一个合适的范围内（自适应算法）

innodb_concurrency_tickets（默认5000）

• 使用小的值时小事务可以和大事务竞争，缺点是大事务要多次才能跑完
• 使用大的值时大事务有优势，缺点是可能让小事务一直得不到运行
• 调整这个值可以参考队列长度，长度从SHOW ENGINE INNODB STATUS来看（ ROW OPERATIONS section of SHOW ENGINE INNODB STATUS output），也可以从INFORMATION_SCHEMA.INNODB_TRX的TRX_CONCURRENCY_TICKETS来看。

InnoDB使用操作系统线程来处理用户的事务请求。（在事务提交或回滚之前可能给InnoDB引擎带来很多的请求）。在现代化操作系统和多核处理器的服务器上，上下文切换是非常高效的，大多数工作负载运行没有任何并发线程数量的限制。在MySQL 5.5及以上版本中，MySQL做了可伸缩性的改进，它减少了这种在InnoDB内部限制并发执行线程数量的需要。

它有助于在最小化的情况下进行线程之间的上下文切换，InnoDB可以使用各种技术来限制操作系统并发执行线程的数量（因此大批量的请求可以在任何一个时间得到处理）。当InnoDB从用户会话收到一个新的请求，如果线程并发执行的数量达到预定义的限制，那么新的请求会先睡眠一段时间后再次尝试。在睡眠后不能按计划执行的请求会被放入先入/先出队列，并最终处理。但那些等待获取锁的线程则不会被计入到并发执行线程的数量中。 我们可以通过设置配置参数innodb_thread_concurrency来限制并发线程的数量，一旦执行线程的数量达到这个限制，额外的线程在被放置到对队列中之前，会睡眠数微秒，可以通过设定参数innodb_thread_sleep_delay来配置睡眠时间。

mysql> show status like ‘threads_%’;
基于安全：
Sync_binlog=1/innodb_flush_log_at_trx_commit=1/set sql_log_bin=0/show status like ‘com_%’;
安全参数：innodb_flush_method=o_direct,fsync

1. fsync:在数据页需要持久化时，首先将数据写入到os buffer中，然后再由os决定什么时候写入磁盘，但是如果innodb_flush_log_at_commit=1的话，日志还是每次commit直接写入磁盘
2. 最安全模式：innodb_flush_log_at_trx_commit=1/innodb_flush_method=0_DIRECT
3. 最高性能：innodb_flush_log_at_trx_commit=0/innodb_flusb_method=0_direct

监控锁状态

1）查看有没有锁等待：mysql> show status like ‘innodb_row_lock%’;
2）查看哪个事务在等待（被阻塞了）：mysql> select * from information_schema.innodb_trx where trx_state=‘LOCK WAIT’;
Trx_id:事务号、
trx_stat:当前事务状态
trx_mysql_thread_id:连接线程id
trx_query:被阻塞的操作
3）查看锁源，谁锁的我：mysql> select * from sys.innodb_lock_waits;
Locked_table:哪张表出现等待

优化顺序
1） 通过top排查，查找mysql进程占比
2） 如果大量cpu用于sys和wait，us处于正常，则排查mysql锁、sql语句
3） 查看slowlog及锁等待情况
4） Pt-query-diagest查看慢日志