MySQL优化核心理论与实践!

  • 时间:
  • 浏览:0

找到Socket Interleave选项,选取Non-NUMA

目标三:全面提升IOPS性能,让磁盘I/O不再拖后退

1.资金充裕时,采购SSD甚至PCIe-SSD

6.子查询处置时性能低,建议改使用JOIN改写SQL

8.定期使用pt-table-checksum、pt-table-sync来检查并修复mysql主从克隆qq的数据差异

10.多用复合索引,少用多个独立索引,尤其是基数太小的列则不建议创建索引

6.设置innodb_data_file_path = ibdata1:1G:autoextend,在高并发事务时获得良好性能

6.能这麼 使用pt-kill杀掉超长时间的SQL请求,Percona版本富含个选项 innodb_kill_idle_transaction可不还还可以 实现该功能

目标一:选对版本,让MySQL起跑底气十足

1.优先推荐Oracle MySQL,不不 的新上系统在拥抱官方5.7.x版本

11.使用分页功能的SQL时,选把关键字与主键做符合索引,再来执行,时延会高可是

硬件层的优化

新采购的服务器默认跑在节能模式下,在并发访问量很大的业务场景,会原因分析分析数据库性能跟不上,造成几瓶延迟,最终将拖垮业务系统。与此一齐,磁盘选取与阵列卡设置不当也会使数据库性能成为整个业务系统的瓶颈。

核心纲领:在上线事先,变更任何另另另有有一个参数,全是做压力测试,处置漏网之鱼原因分析分析MySQL出显各种CRASH。

背景描述:人们 单位OA系统前不久完成升级大改造,后端用的MySQL存储数据,上线跑了个把月,抱怨电话结束接二连三打来,全是这里打不开,可是 那里无响应,人们虚实结合 升级后变成老爷车,这麼 慢,问題迫在眉睫,可不还还可以 马上想对策呀。机会部署采用了规范文档,上线前也做了各种测试,于是乎,在线排查,未果,翻出实施文档,逐条阅读,未果,于是想起曾经 另另另有有一个业务系统,也碰到之类于状况,已经 通过各种优化得以缓解,遂有下文,《MySQL优化核心理论与实践》。

说明:本文理论次要来源叶老师的博文,实践次要来源工作积累和众多热爱MySQL技术分享的日本外国网友,分发初衷是为了更加深入地理解MySQL优化,掌握更多MySQL优化方面的技术,提升我本人,回馈热爱技术分享的所有日本外国网友。

7.能这麼 使用pt-online-schema-change来完成大表的ONLINE DDL需求

3.设置合适的innodb_buffer_pool_size大小,单实例多数是innodb表,建议设置物理内存的30%-70%

关闭QC可不还还可以 在启动MySQL前配置

query_cache_type = 0

query_cache_size = 0

目标三:Schema设计和SQL编写根据参考规范设定,有有助于于提高MySQL时延

1.所有innodb表都设计另另另有有一个无业务用途的自增列做主键

11.设置key_buffer_size = 32M,关闭query cache功能

3.单实例运行中硬件资源还是比较紧张时,不不跑多实例

2.不出显OOM KILL和几瓶使用SWAP,不不担心MySQL任务管理器占用不不 内存

5.定期分发slow query log,用pt-query-digest工具进行分析,再结合Anemometer等系统进行slow query管理,以便于分析和优化

3.尽量不不TEXT和BLOB字段类型,一定可不还还可以 时拆分至子表

机械盘配deadline,执行命令echo deadline >/sys/block/sda/queue/scheduler

固态盘配noop,执行命令echo noop >/sys/block/sda/queue/scheduler

注意sda是数据文件所在分区

2.文件系统尽量使用XFS,假如还在使用ext4,希望可是 过度阶段

8.多表连接查询时,把过滤后的结果集小的表作为驱动表

7.设置innodb_log_file_size=256M,innodb_log_files_in_group=2

2.字段类型在满足够用时,尽量选长度小的;字段属性尽量都加进NOT NULL约束

4.设置合适的innodb_flush_log_at_trx_commit和sync_binlog值

9.适当调大max_connection,建议设置max_connection_error为10万以上,设置open_files_limit、innodb_open_files、table_open_cache、table_definition_cache约10倍于max_connection

5.一般varchar(n)列建索引是,取前30%长度即可

SSD和PCIe-SSD带来的不可是 惊喜,更有踏实,从此磁盘I/O不再是恶魔

2.机械盘搭配阵列卡,Cache策略,BBU电池,RAID-10,15KRPM

echo 'vm.swappiness = 5' >>/etc/sysctl.conf

/sbin/sysctl -p

2.vm.dirty_background_ratio设为5,vm.dirty_ratio设为10,让脏页持续刷入磁盘,处置磁盘I/O瞬间写产生TIME_WAIT

echo 'net.ipv4.tcp_tw_recycle = 1' >>/etc/sysctl.conf

echo 'net.ipv4.tcp_tw_reuse = 1' >>/etc/sysctl.conf

/sbin/sysctl -p

MySQL层的优化

选对MySQL版本尤为重要,找到适合业务系统的版本,可不还还可以 发挥出更大性能。运行参数亦是这麼 ,可不还还可以 反复斟酌与调校。规范schema设计与sql编写,还有规范上线后的运维管理流程,同样也会带不小的收益。

系统层的优化

操作系统方面也所处多处值得优化的地方,同样能明显提升IOPS性能。另外,SWAP要少用,不但这麼 救命,反而会让业务系统所处崩溃边缘。

3.mount参数增加noatime,nodiratime,nobarrier

强烈推荐两位实力派老师:叶金荣 和 吴炳锡

3.最后是MariaDB分支版本,除了任务管理器和审计插件,在这能这麼 享受免费的黑科技

2.设置default-storage-engine=innodb,innodb能这麼 满足99%以上的业务场景

目标一:全面提升IOPS性能,让数据库不再背锅

1.配置合理的I/O调度器

vi /etc/fstab

/dev/sda1 /data xfs defaults,noatime,nodiratime,nobarrier 0 0

/dev/sda2 / xfs defaults,noatime,nodiratime,nobarrier 0 0

/dev/sda3 swap swap defaults,noatime,nodiratime,nobarrier 0 0

mount -o remount /data

mount

目标二:减少SWAP使用倾向甚至禁掉,稳定磁盘I/O和网络减少等待图片时间,让MySQL表现更加稳定

1.vm.swappiness设为5甚至0,假如不关心所处OOM

设置双1,不丢数据,性能较低

设置2和10,丢失其他数据,性能一般

设置双0,数据不×××全,性能最高

5.设置innodb_file_per_table = 1,使用独立表空间

写在结尾:计划后期再对每个细节进行理论分析和压力测试,首次分发写作,机会有不完善之处,欢迎留言和交流。

4.定期用pt-duplicate-key-checker检查和删除重复索引,定期用pt-index-usage检查和删除不太用的索引

优势:这麼 的数据不不出显,SQL查询范围小,执行时延高

9.多表连接查询可是 有排序时,排序字段可不还还可以 是驱动表里的,可是 排序列不走索引

目标二:调校合适的参数,让MySQL的性能更加稳定

1.机会选取使用Percona或MariaDB分支版本,强烈推荐开启thread pool

2.其次推荐Percona分支版本,在这能这麼 享受免费的thread pool和audit plugin

4.查询时,尽量填写可不还还可以 的列,不不查询所有列,处置严重随机读问題

目标四:管理维护的优化,让运维更高效

1.online DDL代价太高,机器性能足够时,建议单表物理不超过10G,单表行数不超过1亿,行平均长度不超过8KB

8.设置long_query_time = 0.05,记录超过30毫秒的慢SQL

目标一:全面关闭节能模式,让MySQL跑在高性能模式下

1.关闭CPU节能模式

阵列卡从容面对多块机械盘,BBU电池保障高性能模式下的Cache策略不丢数据

Cache策略选取Write Back甚至Always Write Back

阵列预读的Read Policy选项,选取Normal

使用RAID-10,性能高于RAID-5

使用15KRPM高速磁盘,性能高于7.2KRPM磁盘

备注:服务器硬件设置的参数来源于IBM X3630M3

找到Memory Speed选项,选取Max Performance

找到Power C-States选项,选取Disable

找到C1 Enhanced Mode选项,选取Disable

目标二:关闭NUMA,让CPU能始终高效地使用内存

关闭NUMA

10.不宜设置过大的参数tmp_table_size、max_heap_table_size、sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size

echo 'vm.dirty_background_ration = 5' >>/etc/sysctl.conf

echo 'vm.dirty_ratio = 10' >>/etc/sysctl.conf

/sbin/sysctl -p

3.net.ipv4.tcp_tw_recycle和net.ipv4.tcp_tw_reuse设为双1,减少网络等待图片时间,提高时延

找到OPI Link Speed Select选项,选取Max Performance

2.关闭内存节能模式

7.多表连接查询时,关键字类型尽量一致,且全是有索引