MySQL优化

系统优化工具

top

iotop

iostat

vmstat

数据库层面优化工具
基础优化命令工具
mysql
SHOW ENGINE INNODB STATUS
show index
Information Schema

SHOW [SESSION | GLOBAL] STATUS
SHOW PROCESSLIST
explain
mysqldumpslow （pt-query-diagest）

深度优化命令工具（扩展）
sysbench
Performance Schema(5.7默认开启)

---

硬件优化：

主机：

根据数据库类型

（1）主机CPU选择
IO密集型
CPU密集型

（2）内存容量选择
一般是选择cpu核心数量的2倍

（3）IO的选择

    1、磁盘选择
    SATA   SAS  FC  SSD   pci-e SSD  Flash        
    随机的I/O和顺序的I/O
    主机 RAID卡的BBU(Battery Backup Unit)关闭

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

    raid1  raid0  
    raid10  
    raid5

使用合适raid级别，避免过度条带化

网络设备：
使用流量支持更高的网络设备（交换机、路由器、网线、网卡、HBA卡）
网卡绑定：
bonding 0 1
交换机：堆叠

系统层面优化

Swap调整

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

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

这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。
当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。

修改MySQL的配置参数innodb_flush_method，开启O_DIRECT模式

这种情况下，InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘，
但是redo log依旧会使用文件系统cache。值得注意的是，Redo log是覆写模式的，
即使使用了文件系统的cache，也不会占用太多
IO调度策略

[root@db01 ~]# echo deadline >/sys/block/sda/queue/scheduler
[root@db01 ~]# cat /sys/block/sda/queue/scheduler 
更改到如下内容:
kernel /vmlinuz-2.6.32-696.el6.x86_64 ro root=UUID=40c9133f-6007-485c-be19-4082c8361df3 rd_NO_LUKS rd_NO_LVM LANG=en_US.UTF-8
rd_NO_MD SYSFONT=latarcyrheb-sun16 crashkernel=auto  KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM elevator=deadline rhgb quiet

IO ：

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

数据库层面优化

压力测试

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=2000 -uroot -p123 -verbose

参数调整：

    实例整体（高级优化，扩展）：
            thread_concurrency      并发线程数量个数

在官方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参数的设置进行调整。

            sort_buffer_size                排序缓存    
            
            
    设置此参数，要参考show status like '%sort%'; 
    如果发现值很高的时候，说明应用中有大量排序操作
    我可以考虑增加sort_buffer_size,可能可以缓解
    最根本的解决办法，是减少排序出现，比如创建合理索引，优化语句

            read_buffer_size                顺序读取缓存
            read_rnd_buffer_size        随机读取缓存
            key_buffer_size                    索引缓存
            
            thread_cache_size       (1G —> 8, 2G —> 16, 3G —> 32, >3G —> 64)

连接层（基础优化）

                设置合理的连接客户和连接方式
     max_connections               最大连接数      *********
    
                max_connect_errors            最大错误连接数   *******
                connect_timeout                    连接超时         
                max_user_connections        最大用户连接数   *******
                skip-name-resolve            跳过域名解析     *******   
                wait_timeout                    等待超时
                back_log                               可以在堆栈中的连接数量  

SQL层（基础优化）

             query_cache_size           查询缓存        --- Redis    jd      --- taobao      Tair
             log_bin=
             binlog_format=row
             max_binlog_cache_size
             max_binlog_size

存储引擎层（innodb基础优化参数）

                default-storage-engine
                innodb_buffer_pool_size         **********   
innodb_buffer_pool_size=64G
                
                不要超过物理内存的70%，50-65%
                

                innodb_file_per_table=(1,0)      独立表空间
                
                
                innodb_flush_log_at_trx_commit=(0,1,2)    ***************
                
                1、默认模式是1，最高的安全模式，可以保证在每次事务提交时，redo一定写入磁盘。
                2、如果设置为0，是最高性能模式，每秒写入操作系统缓存中（文件系统），并写入磁盘
                3、如果设置为2，安全和性能这种模式，每次事务提交，往文件系统缓存写入一次，并每秒写入磁盘一次。
                
                
                Innodb_flush_method=(O_DIRECT, fdatasync) ***************
                
                1、fdatasync    ：
                （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=fdatasync

            一般情况下，我们更偏向于安全。

“双一标准”

                innodb_flush_log_at_trx_commit=1        
                sync_binlog=1                                    

            控制redo：
            innodb_log_buffer_size
            innodb_log_file_size
            innodb_log_files_in_group

            innodb_max_dirty_pages_pct         ***********
            
            innodb_additional_mem_pool_size (于2G内存的机器，推荐值是20M。32G内存的 100M)

            transaction_isolation            *********

---

MyIASM： 表级锁
优点：申请和释放时，需要更少系统资源，减少死锁产生。
缺点：不利于并发处理，在某个事务在对表进行修改操作时，会锁定整个表，其他事务只能等待完成之后，才能操作。
有非常严重的锁等待。

InnoDB：
支持行级锁，行级锁在索引锁。如果表中没有任何索引，那么我们做表数据处理的时候，依然会表级锁。
GAP锁：主要针对范围数据操作时

死锁的处理过程

1、 
show processlist


2、
show  engine innodb status\G;
LOCK WAIT 2 lock struct(s), heap size 1184, 1 row lock(s)
MySQL thread id 4, OS thread handle 0x7f37d66f0700, query id 44 localhost root Sending data
select * from t1 where id=3 for update
Trx read view will not see trx with id >= 2323, sees < 2323
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 14 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 2322 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0



3、
kill 4;

避免死锁的方法：
（1）将index lock 转换为table lock

set autocommit=0;
lock table t1;
insert to
update
delete
commit
unlock table t1;

（2）将所有事务处理表数据的顺序尽量保证一致。

2PC：二次提交

问一个问题？
redo：存储引擎层面的日志，主要记录内存数据页的变化
binlog：SQL层的日志，记录的SQL或者数据行的变化

思考：一个事务提交，并且开启了“双一标准”，问：以上日志写入磁盘应该谁先写入磁盘？

2PC：
binlog优先于redo落地到磁盘
1、redo papare
2、binlog commit
3、redo commit
mysql保证，只要binlog落地了，就能保证已完成事务的安全
2PC机制主要是为了保证主从复制时，主从节点事务一致性

---