mysql性能优化总结(一)
一.什么决定双11大促的成败?
场景:原数据库架构为1M15S,M服务器性能很好,64核512G内存,IO采用fushion IO,比一般磁盘的读写能力高很多.
缺点:
1.只有一个主库,没有主从切换中间件,每次如果主库挂了,需要运维人员手动进行切换,然后同步给其他从库,耗时高(超过半小时不可用)
2.双十一凌晨2点半数据库磁盘IO达到峰值,造成恐慌,担心性能为急剧下降.经检查,发现是数据库远程备份定时任务在跑,总结:最好不要在主库上进行数据库备份,大型活动前取消这类计划.
对数据库性能影响比较大的因素:
(1)sql性能
(2)网卡流量
(3)服务器硬件
(4)磁盘IO
(5)大表
(6)大事务
数据库面临的风险:
(1)超高的qps和tps
根据经验,数据库性能80%都是由慢查询导致的.大多数据数据库问题都可以通过sql性能优化来解决.
当前的mysql不支持单sql语句多cpu并发计算,一条sql只能由一个cpu去执行.10ms的sql和100ms的sql性能直接相差10倍.
(2)大量的并发和超高的cpu使用率
大量并发:数据库连接被占满.当并发数大于最大连接数时,出现500错误,对用户体验造成影响.
PS:并发数和连接数的区别:并发是指同时请求mysql的数量,连接数是指mysql的连接数,通常连接数远大于并发数,因为大部分的连接都是出于sleep状态.类似于http长连接.
数据库最大连接数:max_connections,默认值为100.所以需要把这个参数改的大一些.
超高的cpu使用率:可能导致cpu资源耗尽而宕机
(3)磁盘
1.IO性能突然下降
常发生在热数据量远远大于服务器可用内存的情况下(使用更快的磁盘设备来解决.比如fashion io,SSD,更好的raid卡)
2.其他大量损耗磁盘性能的计划任务(调整计划任务,做好磁盘维护)
(4)网卡流量(1000Mb/8≈100MB)
流量高峰期,千兆网卡多次被跑满,其他请求会出现无法连接数据库.
解决办法:
1.减少从数据库的数量
2.进行分级缓存,避免大量的请求突然对数据库形成冲击
3.避免使用SELECT * 进行查询,查询出没有必要的列会浪费网络流量
4.分离业务网络和服务器网络,避免主从同步或者备份等作业影响网络性能.
什么是大表:
1.记录行数巨大,单表达到千万行
根据场景灵活应用,比如日志表,只有insert操作和少量的select操作,几乎没有update操作和delete操作,这样的表就算超过了千万行,也不会对业务产生什么影响.实际工作中有遇到过,日志表的行数已经超过10个亿了,一直很稳定.但是在增加列的时候,会非常痛苦(为什 么?)
2.数据文件巨大,表数据文件超过10G
大表对查询的影响:
1.慢查询:大表意味着很难在一定的时间内,过滤出所需要的的数据
案例:此超过10E行的日志表,之前一直无人问津,直到有一天,运营人员提出需要查询表中订单来源(order_from,1公司网站 2京东 3淘宝 4微信)的字段,在大促中此条慢sql直接把数据库搞死了.
2.对DDL操作的影响
(1)建立索引需要花费很长的时间
MYSQL版本<5.5,建立索引会锁表
MYSQL版本>=5.5,不会锁表,但是会引起主从延迟
(2)修改表结构需要长时间锁表
风险:
1.会造成长时间的主从延迟.假设主库修改需要480s,mysql5.6之前主从同步使用的是单线程,主库执行完之后才会同步到从库执行相同的操作,所以从库也至少需要480s来完成同步,也就造成了480s的主从延迟,这对于大多数业务来说都是不可接受的.
5.6之后,支持多线程复制,但是也有一定的限制.
2.影响正常的数据库操作(锁表,其他操作被阻塞),导致数据库连接数猛增.超过最大连接数后,前台出现500错误
如何解决大表问题:
1.分库分表
2.进行历史数据归档
难点:
(1)归档是时间点的选择
(2)如何进行归档操作(大表中删除大量数据,需要注意方式方法,否则轻则造成主从延迟,重则影响用户正常操作)
大事务对性能的影响
定义:运行时间较长,操作的数据比较多的事务
风险:
锁定太多的数据,造成大量的阻塞和锁超时.
回滚时所需时间比较长(有可能比执行事务所花费的时间更长)
执行时间长,容易造成主从延迟
解决方法:
1.避免一次性操作太多的数据
2.移出不必要在事务中的SELECT操作
影响性能的几个方面:
1.计算机硬件,如cpu,内存,磁盘io
2.操作系统,windows xp系统默认的并发数只有10个.服务器性能调优
3.mysql的存储引擎
MyISAM:不支持事务,表级锁
InnoDB:事务级存储引擎,完美支持行级锁,事务ACID特性.
4.数据库参数配置
有些参数对数据库性能的影响是决定性的.
前面三个方面的影响加起来 < 数据库参数配置的影响
5.数据库表结构设计和sql语句执行效率
慢查询是很多数据库性能低下的罪魁祸首.
很多慢查询是由于数据库表结构设计不合理导致的,这类问题一旦项目上线就很难再修改了.
重点是前期的优化设计及后期sql优化.
如何选择CPU?
1.高的频率,更多的核心内核数.2者不可兼得时,WEB应用来说,内核数>频率.CPU密集型应用来说,由于mysql不支持多核并发计算同一条sql,所以频率也很重要.
2.MYSQl版本也有影响,5.0之前的版本对于多核CPU的支持是很弱的,5.6,5.7之后做了很好的支持.
3.注意不要出现64位的cpu使用32位的操作系统,大多由于误装,对性能限制很大.
如何选择内存?
1.数据库自带缓存策略,内存的大小直接影响数据库性能,特别是当热数据大小远远大于可用内存时.
MyIsam
索引->内存
数据->OS
InnoDB
索引,数据->内存
2.内存并不是越大越好,当内存大小大于所有数据量大小,即所有的数据都已经被缓存在内存中,再增加内存就是没有意义的了.
3.内存的大小不仅对查询有效,对插入也一样有效.可以做到延缓插入,先缓存在内存中,然后批量插入,从而减少io操作次数,提高性能.
4.频率越高越好,选择主板能够支持的频率最高的内存条,然后单个内存条尽量的大,所有内存条型号保持完全一致.
磁盘的配置和选择
1.使用传统机器硬盘
最常见,使用最多,价格低,存储空间大,读写速度慢
如何选择?
1.存储容量
2.传输速度
3.访问时间
4.主轴转速(7500,15000)
5.物理尺寸(越小越快)
2.使用RAID增强传统机器硬盘的性能
定义:RAID是磁盘冗余队列的简称,吧多个容量较小的磁盘组成一组容量更大的磁盘,并提供数据冗余来保证数据完整性的技术
常见RAID组别:
(1)RAID 0:数据条带,串联,成本低,可以提高磁盘性能和吞吐量,没有提供冗余和错误修复.损坏率比单块磁盘更高,其中任何一块磁盘损坏都会造成数丢失.
(2)RAID 1:磁盘镜像,最大限度的保障可靠性和可修复性,磁盘利用率低(50%)
(3)RAID 5:分布式奇偶校验磁盘阵列,读快,写慢,磁盘利用率高,只需要额外一块磁盘.同时损坏两块以上则不可修复.
(4)RAID 10:先做RAID 1,在做RAID 0,读写性能好,恢复较RAID 5更简单,速度也更快.
3.使用固态存储SSD和PCIe卡
更好的随机读写性能
更好的支持并发
缺点:更容易损坏
SSD:使用SATA接口,同样可以使用RAID技术,由于带宽的限制,无法完全发挥闪存技术的性能
PCIe卡:无法使用SATA接口,需要特殊的驱动和配置,价格比SSD更贵,性能更好.Fusion-IO,会占用内存资源.
如果只有一块ssd,应该放在从服务器上,而不是主服务器上.
4.使用网络存储NAS和SAN
SAN和NAS是两种外部文件存储设备加载到服务器上的方法