系统优化工具
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机制主要是为了保证主从复制时,主从节点事务一致性