- 一篇全面的 MySQL 高性能优化实战总结!
- 来源:高效运维
优化效果:硬件<系统配置<数据库表结构4 优化工具有啥?4.1 数据库层面检查问题常用工具mysql
msyqladmin mysql客户端,可进行管理操作
mysqlshow 功能强大的查看shell命令
show [SESSION | GLOBAL] variables 查看数据库参数信息
SHOW [SESSION | GLOBAL] STATUS 查看数据库的状态信息
information_schema 获取元数据的方法
SHOW ENGINE INNODB STATUS Innodb引擎的所有状态
SHOW PROCESSLIST 查看当前所有连接session状态
explain 获取查询语句的执行计划
show index 查看表的索引信息
slow-log 记录慢查询语句
mysqldumpslow 分析slowlog文件的不常用但好用的工具zabbix 监控主机、系统、数据库(部署zabbix监控平台)
pt-query-digest 分析慢日志
mysqlslap 分析慢日志
sysbench 压力测试工具
mysql profiling 统计数据库整体状态工具
Performance Schema mysql性能状态统计的数据
workbench 管理、备份、监控、分析、优化工具(比较费资源)关于zabbix参考:http://www.cnblogs.com/clsn/p/7885990.html4.2 数据库层面问题解决思路一般应急调优的思路:针对突然的业务办理卡顿,无法进行正常的业务处理!需要立马解决的场景!1、show processlist2、explain select id ,name from stu where name=’clsn’; # ALL id name age sexselect id,name from stu where id=2-1 函数 结果集>30; show index from table;3、通过执行计划判断,索引问题(有没有、合不合理)或者语句本身问题4、show status like ‘%lock%’; # 查询锁状态 kill SESSION_ID; # 杀掉有问题的session
常规调优思路:针对业务周期性的卡顿,例如在每天10-11点业务特别慢,但是还能够使用,过了这段时间就好了。1、查看slowlog,分析slowlog,分析出查询慢的语句。2、按照一定优先级,进行一个一个的排查所有慢语句。3、分析top sql,进行explain调试,查看语句执行时间。4、调整索引或语句本身。4.3 系统层面cpu方面vmstat、sar top、htop、nmon、mpstat内存free 、ps -auxIO设备(磁盘、网络)iostat 、 ss 、 netstat 、 iptraf、iftop、lsofvmstat 命令说明:Procs:r显示有多少进程正在等待CPU时间。b显示处于不可中断的休眠的进程数量。在等待I/O
Memory:swpd显示被交换到磁盘的数据块的数量。未被使用的数据块,用户缓冲数据块,用于操作系统的数据块的数量
Swap:操作系统每秒从磁盘上交换到内存和从内存交换到磁盘的数据块的数量。s1和s0最好是0
Io:每秒从设备中读入b1的写入到设备b0的数据块的数量。反映了磁盘I/O
System:显示了每秒发生中断的数量(in)和上下文交换(cs)的数量
Cpu:显示用于运行用户代码,系统代码,空闲,等待I/O的CPU时间iostat命令说明实例命令:iostat -dk 1 5
iostat -d -k -x 5 (查看设备使用率(%util)和响应时间(await))
tps:该设备每秒的传输次数。“一次传输”意思是“一次I/O请求”。多个逻辑请求可能会被合并为“一次I/O请求”。iops :硬件出厂的时候,厂家定义的一个每秒最大的IO次数
"一次传输"请求的大小是未知的。kB_read/s:每秒从设备(drive expressed)读取的数据量;KB_wrtn/s:每秒向设备(drive expressed)写入的数据量;kB_read:读取的总数据量;kB_wrtn:写入的总数量数据量;这些单位都为Kilobytes。4.4 系统层面问题解决办法你认为到底负载高好,还是低好呢?在实际的生产中,一般认为 cpu只要不超过90%都没什么问题 。当然不排除下面这些特殊情况:问题一:cpu负载高,IO负载低内存不够磁盘性能差SQL问题 →去数据库层,进一步排查sql问题IO出问题了(磁盘到临界了、raid设计不好、raid降级、锁、在单位时间内tps过高)tps过高: 大量的小数据IO、大量的全表扫描
问题二:IO负载高,cpu负载低大量小的IO 写操作:autocommit ,产生大量小IOIO/PS,磁盘的一个定值,硬件出厂的时候,厂家定义的一个每秒最大的IO次数。大量大的IO 写操作SQL问题的几率比较大问题三:IO和cpu负载都很高硬件不够了或sql存在问题5 基础优化5.1 优化思路定位问题点吮吸硬件 → 系统 → 应用 → 数据库 → 架构(高可用、读写分离、分库分表)处理方向明确优化目标、性能和安全的折中、防患未然5.2 硬件优化主机方面:根据数据库类型,主机CPU选择、内存容量选择、磁盘选择平衡内存和磁盘资源随机的I/O和顺序的I/O主机 RAID卡的BBU(Battery Backup Unit)关闭cpu的选择:cpu的两个关键因素:核数、主频根据不同的业务类型进行选择:cpu密集型:计算比较多,OLTP 主频很高的cpu、核数还要多IO密集型:查询比较,OLAP 核数要多,主频不一定高的内存的选择:OLAP类型数据库,需要更多内存,和数据获取量级有关。OLTP类型数据一般内存是cpu核心数量的2倍到4倍,没有最佳实践。存储方面:根据存储数据种类的不同,选择不同的存储设备配置合理的RAID级别(raid5、raid10、热备盘)对与操作系统来讲,不需要太特殊的选择,最好做好冗余(raid1)(ssd、sas 、sata)raid卡:主机raid卡选择: 实现操作系统磁盘的冗余(raid1) 平衡内存和磁盘资源 随机的I/O和顺序的I/O 主机 RAID卡的BBU(Battery Backup Unit)要关闭。网络设备方面:使用流量支持更高的网络设备(交换机、路由器、网线、网卡、HBA卡)注意:以上这些规划应该在初始设计系统时就应该考虑好。5.3 服务器硬件优化1、物理状态灯:2、自带管理设备:远程控制卡(FENCE设备:ipmi ilo idarc),开关机、硬件监控。3、第三方的监控软件、设备(snmp、agent)对物理设施进行监控4、存储设备:自带的监控平台。EMC2(hp收购了), 日立(hds),IBM低端OEM hds,高端存储是自己技术,华为存储5.4 系统优化CPU:基本不需要调整,在硬件选择方面下功夫即可。内存:基本不需要调整,在硬件选择方面下功夫即可。SWAP:MySQL尽量避免使用swap。阿里云的服务器中默认swap为0IO :raid、no lvm、 ext4或xfs、ssd、IO调度策略Swap调整(不使用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调度策略#echo deadline>/sys/block/sda/queue/scheduler 临时修改为deadline永久修改vi /boot/grub/grub.conf
更改到如下内容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet5.5 系统参数调整Linux系统内核参数优化vim /etc/sysctl.conf
net.ipv4.ip_local_port_range = 1024 65535 # 用户端口范围
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_fin_timeout = 30
fs.file-max=65535 # 系统最大文件句柄,控制的是能打开文件最大数量用户限制参数(mysql可以不设置以下配置)vim /etc/security/limits.conf
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 655355.6 应用优化业务应用和数据库应用独立,防火墙:iptables、selinux等其他无用服务(关闭): chkconfig --level 23456 acpid off
chkconfig --level 23456 anacron off
chkconfig --level 23456 autofs off
chkconfig --level 23456 avahi-daemon off
chkconfig --level 23456 bluetooth off
chkconfig --level 23456 cups off
chkconfig --level 23456 firstboot off
chkconfig --level 23456 haldaemon off
chkconfig --level 23456 hplip off
chkconfig --level 23456 ip6tables off
chkconfig --level 23456 iptables off
chkconfig --level 23456 isdn off
chkconfig --level 23456 pcscd off
chkconfig --level 23456 sendmail off
chkconfig --level 23456 yum-updatesd off安装图形界面的服务器不要启动图形界面 runlevel 3另外,思考将来我们的业务是否真的需要MySQL,还是使用其他种类的数据库。用数据库的最高境界就是不用数据库。6 数据库优化SQL优化方向:执行计划、索引、SQL改写架构优化方向:高可用架构、高性能架构、分库分表6.1 数据库参数优化调整:实例整体(高级优化,扩展):thread_concurrency # 并发线程数量个数
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:查询缓存 >>> OLAP类型数据库,需要重点加大此内存缓存,但是一般不会超过GB
对于经常被修改的数据,缓存会立马失效。我们可以实用内存数据库(redis、memecache),替代他的功能。6.2 存储引擎层(innodb基础优化参数)default-storage-engine
innodb_buffer_pool_size #没有固定大小,50%测试值,看看情况再微调。但是尽量设置不要超过物理内存70%
innodb_file_per_table=(1,0)
innodb_flush_log_at_trx_commit=(0,1,2) # 1是最安全的,0是性能最高,2折中
binlog_sync
Innodb_flush_method=(O_DIRECT, fdatasync)
innodb_log_buffer_size # 100M以下
innodb_log_file_size # 100M 以下
innodb_log_files_in_group # 5个成员以下,一般2-3个够用(iblogfile0-N)
innodb_max_dirty_pages_pct # 达到百分之75的时候刷写 内存脏页到磁盘。log_bin
max_binlog_cache_size # 可以不设置
max_binlog_size # 可以不设置
innodb_additional_mem_pool_size #小于2G内存的机器,推荐值是20M。32G内存以上100M来源:本文转自公众号“芋道源码”,作者惨绿少年。GOPS全球运维大会 2019 · 上海站,最想听的专场,你来做主
惨绿少年
扫一扫下载订阅号助手,用手机发文章
赞赏
长按二维码向我转账
受苹果公司新规定影响,微信 iOS 版的赞赏功能被关闭,可通过二维码转账支持公众号。
已同步到看一看
取消
发送
我知道了
朋友会在“发现-看一看”看到你“在看”的内容
确定
已同步到看一看写下你的想法
最多200字,当前共字
发送
已发送
朋友将在看一看看到
确定
写下你的想法...
取消
发布到看一看
确定
最多200字,当前共字
发送中
微信扫一扫
使用小程序
取消
允许
取消
允许
知道了
确定
社会
-
-
- 扯白||好莱坞下一任大女主会是她么?
- 最近我最期待的电影莫过于昆汀导演的《好莱坞往事》了! 这部电影在一些地区都已经公映,虽然离国内上映可能还有一段日子,不过有小李子和皮特这对久违的怀旧CP,也值得咱们激动的心颤抖的手! 莱奥纳多和布
- 蓝小姐和黄小姐
-
-
-
- 作证 举报 告密(下):读《隔墙有耳》
- 本篇集中讨论标题里的后两个词。 关于前一个词,可以参考上篇:作证 举报 告密(上):观《闻香识女人》 举报,通常有所谓实名举报和匿名举报之分,也有公开举报和私下举报之分。 公开举报的一个特点在于举报
- ItTalks
-
-
-
- “我要夸我的女朋友,谁也别挡我。”
- 今天的文章 就用一段留言开始好了 “她会做 double cheese 的披萨,会泡制署上我名字的青梅酒,会在冰箱上贴用老式打字机打上 SSH l love you 的便签,会教我新学到的心理学知识
- 我要WhatYouNeed
-
-
-
- 人不会重复拥有童年,手机正在毁了它
- 玛丽·艾肯(Mary Aiken)正在南洛杉矶——一座黑帮肆虐、犯罪率高、贫穷破败的城市,参与一场人口贩子的抓捕行动,她全副武装,混迹在特别行动队里。 她并不是警员,而是来自爱尔兰的一名刑事科学网络心
- 人物
-
-
-
- 婚姻沉默症:不离婚,也不聊天
- 婚姻沉默症:不离婚也不聊天 - 来自十点读书 - / 12:23 文 | 南歌 · 主播 | 林静 十点读书原创 有人说: 有话聊,是两个人在一起的基本标准。 而一直有话聊,是两人能一直走下去的必要
- 十点读书
-
-
-
- 持续观望是持续行动的开始
- 持续力 公众号ID:scalerstalk 个人微信号:escalers (可加Scalers个人微信交流) 关注 ScalersTalk 成长会 2020 年开放招募,参见《持续行动,刻意学习
- ScalersTalk成长持续论
-
-
-
- 一篇全面的 MySQL 高性能优化实战总结!
- 1 前言 在进行 MySQL 的优化之前必须要了解的就是 MySQL 的查询过程,很多的查询优化工作实际上就是遵循一些原则让 MySQL 的优化器能够按照预想的合理方式运行而已。 2 优化的哲学 2.
- 高效运维
-
-
-
- 127个城市争夺15个名额,“中国孟菲斯”之争,谁将脱颖而出
- 文/西部菌 来源/西部城事 (识局微信公共账号zhijuzk) ▼ 又有15个城市被国家选中,并委以重任! 据报道,经过数月申报筛选,第一批15个左右国家物流枢纽名单近日将公布,标志着国家物流枢
- 识局
-
-
-
- 那些阔气的名人,为什么穷起来特别快|大家
- 一 江湖事江湖了,这两年因为四处曝明星隐私出尽风头的黄毅清(女明星黄奕的前夫)因为吸毒而被捕。 黄奕与黄毅清 虹口公安分局的微博通告 公安局发出的公告里有“以贩养吸”这四个字,要知道吸毒最
- 大家
-
-
-
- 人不会重复拥有童年,手机正在毁了它
- 玛丽·艾肯(Mary Aiken)正在南洛杉矶——一座黑帮肆虐、犯罪率高、贫穷破败的城市,参与一场人口贩子的抓捕行动,她全副武装,混迹在特别行动队里。 她并不是警员,而是来自爱尔兰的一名刑事科学网络心
- 人物
-
-
-
- 幸福是持续进化,所以经营有方
- Henri Martin,Sewing in the Garden 近一个月前,有位读者分三则留了长长的言,一直想找机会把全文放出,但是每天都有更急的主题,没有空档,拖到了今天。 这位读者的故事,
- 连岳
-
-
-
- 梁宁:一个产品经理的奥德赛之旅
- 张辉 改变自己主创 辉哥奇谭主创 | 布道师 奥德赛是一个人的漂泊、自我发现,经营人生的旅程,虽说本文的主角是一个产品经理,但她的经历与感悟对每个普通人也很有启发。
- 改变自己
-
-
-
- 持续观望是持续行动的开始
- 持续力 公众号ID:scalerstalk 个人微信号:escalers (可加Scalers个人微信交流) 关注 ScalersTalk 成长会 2020 年开放招募,参见《持续行动,刻意学习
- ScalersTalk成长持续论
-
-
-
- 深挖年轻态市场,芒果TV这部剧如何“小火慢煨”式拓圈?
- 《奋斗吧,少年!》以育青中学网球队参加全国校园网球联赛为故事背景,被观众评为“体育题材最正确的打开方式”。 近日,2019美国网球公开赛正如火如荼地进行,中国金花独苗王蔷挑战前世界第一、新科法网冠军
- 广电独家
-