1. 首页
  2. IT资讯

MySQL 性能优化之骨灰级,高阶神技

“u003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002FRf8n3jdBqecfy8″ img_width=”1080″ img_height=”345″ alt=”MySQL 性能优化之骨灰级,高阶神技” inline=”0″u003Eu003Cpu003E作者 | 惨绿少年 u003Cu002Fpu003Eu003Cpu003E链接 | https:u002Fu002Fclsn.iou002Fclsnu002Flx287.htmlu003Cu002Fpu003Eu003Cpu003Eu003Cstrong toutiao-origin=”span” class=”highlight-text”u003E一、前言u003Cu002Fstrongu003Eu003Cu002Fpu003Eu003Cpu003EMySQL调优对于很多程序员而言,都是一个非常棘手的问题,多数情况都是因为对数据库出现问题的情况和处理思路不清晰。在进行MySQL的优化之前必须要了解的就是MySQL的查询过程,很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。u003Cu002Fpu003Eu003Cpu003E今天给大家讲解MySQL的优化实战,助你高薪之路顺畅!u003Cu002Fpu003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002FRfR8ZC6GIaVBz” img_width=”700″ img_height=”410″ alt=”MySQL 性能优化之骨灰级,高阶神技” inline=”0″u003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch1 toutiao-origin=”h2″u003E二、优化的哲学u003Cu002Fh1u003Eu003Cpu003E注意:优化有风险,涉足需谨慎!u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E1、优化可能带来的问题u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003E1) 优化不总是对一个单纯的环境进行,还很可能是一个复杂的已投产的系统。u003Cu002Fpu003Eu003Cpu003E2) 优化手段本来就有很大的风险,只不过你没能力意识到和预见到!u003Cu002Fpu003Eu003Cpu003E3) 任何的技术可以解决一个问题,但必然存在带来一个问题的风险!u003Cu002Fpu003Eu003Cpu003E4) 对于优化来说解决问题而带来的问题,控制在可接受的范围内才是有成果。u003Cu002Fpu003Eu003Cpu003E5) 保持现状或出现更差的情况都是失败!u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E2、优化的需求u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003E1) 稳定性和业务可持续性,通常比性能更重要!u003Cu002Fpu003Eu003Cpu003E2) 优化不可避免涉及到变更,变更就有风险!u003Cu002Fpu003Eu003Cpu003E3) 优化使性能变好,维持和变差是等概率事件!u003Cu002Fpu003Eu003Cpu003E4) 切记优化,应该是各部门协同,共同参与的工作,任何单一部门都不能对数据库进行优化!u003Cu002Fpu003Eu003Cpu003E5) 所以优化工作,是由业务需要驱使的!!!u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E3、优化由谁参与u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003E在进行数据库优化时,应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等,业务相关人员共同参与。u003Cu002Fpu003Eu003Cpu003Etips:大家可以u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-6″u003E关注u003Cu002Fiu003Eu003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-4″u003E微信u003Cu002Fiu003Eu003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-3″u003E公众号u003Cu002Fiu003E:Java后端,获取更多优秀博文推送。u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch1 toutiao-origin=”h2″u003E三、优化思路u003Cu002Fh1u003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E1、优化什么u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003E在数据库优化上有两个主要方面:即安全与性能。u003Cu002Fpu003Eu003Cpu003E1) 安全 —> 数据可持续性u003Cu002Fpu003Eu003Cpu003E2) 性能 —> 数据的高性能访问u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E2、优化的范围有哪些u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003E存储、主机和操作系统方面:u003Cu002Fpu003Eu003Cpu003E1) 主机架构稳定性u003Cu002Fpu003Eu003Cpu003E2) Iu002FO规划及配置 u003Cu002Fpu003Eu003Cpu003E3) Swap交换分区u003Cu002Fpu003Eu003Cpu003E4) OS内核参数和网络问题u003Cu002Fpu003Eu003Cpu003E应用程序方面:u003Cu002Fpu003Eu003Cpu003E1) 应用程序稳定性 u003Cu002Fpu003Eu003Cpu003E2) SQL语句性能 u003Cu002Fpu003Eu003Cpu003E3) 串行访问资源 u003Cu002Fpu003Eu003Cpu003E4) 性能欠佳会话管理u003Cu002Fpu003Eu003Cpu003E5) 这个应用适不适合用MySQLu003Cu002Fpu003Eu003Cpu003E数据库优化方面:u003Cu002Fpu003Eu003Cpu003E1) 内存u003Cu002Fpu003Eu003Cpu003E2) 数据库结构(物理&逻辑) u003Cu002Fpu003Eu003Cpu003E3) 实例配置u003Cu002Fpu003Eu003Cpu003E说明:不管是在,设计系统,定位问题还是优化,都可以按照这个顺序执行。u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E3、优化维度u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003E数据库优化维度有四个:u003Cu002Fpu003Eu003Cpu003E硬件、系统配置、数据库表结构、SQL及索引。u003Cu002Fpu003Eu003Cpu003E优化选择:u003Cu002Fpu003Eu003Cpu003E1) 优化成本: u003Ccodeu003Eu003Cstrong toutiao-origin=”span”u003E硬件>系统配置>数据库表结构>SQL及索引u003Cu002Fstrongu003Eu003Cu002Fcodeu003Eu003Cu002Fpu003Eu003Cpu003E2) 优化效果: u003Ccodeu003Eu003Cstrong toutiao-origin=”span”u003E硬件<系统配置<数据库表结构<SQL及索引u003Cu002Fstrongu003Eu003Cu002Fcodeu003Eu003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch1 toutiao-origin=”h2″u003E四、优化工具有啥?u003Cu002Fh1u003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E1、数据库层面u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003E检查问题常用工具:u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Emysqlu003Cu002Fcodeu003Eu003Ccodeu003Emsyqladmin mysqlu003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-2″u003E客户端u003Cu002Fiu003E,可进行管理操作u003Cu002Fcodeu003Eu003Ccodeu003Emysqlshow 功能强大的查看shell命令u003Cu002Fcodeu003Eu003Ccodeu003Eshow [SESSION | GLOBAL] variables 查看数据库参数信息u003Cu002Fcodeu003Eu003Ccodeu003ESHOW [SESSION | GLOBAL] STATUS 查看数据库的状态信息u003Cu002Fcodeu003Eu003Ccodeu003Einformation_schema 获取元数据的方法u003Cu002Fcodeu003Eu003Ccodeu003ESHOW ENGINE INNODB STATUS Innodb引擎的所有状态u003Cu002Fcodeu003Eu003Ccodeu003ESHOW PROCESSLIST 查看当前所有连接session状态u003Cu002Fcodeu003Eu003Ccodeu003Eexplain 获取查询语句的执行计划u003Cu002Fcodeu003Eu003Ccodeu003Eshow index 查看表的索引信息u003Cu002Fcodeu003Eu003Ccodeu003Eslow-log 记录慢查询语句u003Cu002Fcodeu003Eu003Ccodeu003Emysqldumpslow 分析slowlog文件的u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E不常用但好用的工具:u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Ezabbix 监控主机、系统、数据库(部署zabbix监控平台)u003Cu002Fcodeu003Eu003Ccodeu003Ept-query-digest 分析慢日志u003Cu002Fcodeu003Eu003Ccodeu003Emysqlslap 分析慢日志u003Cu002Fcodeu003Eu003Ccodeu003Esysbench 压力测试工具u003Cu002Fcodeu003Eu003Ccodeu003Emysql profiling 统计数据库整体状态工具 u003Cu002Fcodeu003Eu003Ccodeu003EPerformance Schema mysql性能状态统计的数据u003Cu002Fcodeu003Eu003Ccodeu003Eworkbench 管理、备份、监控、分析、优化工具(比较费资源)u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E2、数据库层面问题解决思路u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003E一般应急调优的思路:u003Cu002Fpu003Eu003Cpu003E针对突然的业务办理卡顿,无法进行正常的业务处理!需要立马解决的场景!u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003E1、show processlistu003Cu002Fcodeu003Eu003Cbru003Eu003Ccodeu003E2、explain select id ,name from stu where name=’clsn’; # ALL id name age sexu003Cu002Fcodeu003Eu003Cbru003Eu003Ccodeu003E select id,name from stu where id=2-1 函数 结果集>u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-4″u003E30u003Cu002Fiu003E;u003Cu002Fcodeu003Eu003Cbru003Eu003Ccodeu003E show index from table;u003Cu002Fcodeu003Eu003Cbru003Eu003Ccodeu003E3、通过执行计划判断,索引问题(有没有、合不合理)或者语句本身问题u003Cu002Fcodeu003Eu003Cbru003Eu003Ccodeu003E4、show status like ‘%lock%’; # 查询锁状态u003Cu002Fcodeu003Eu003Cbru003Eu003Ccodeu003E kill SESSION_ID; # 杀掉有问题的sessionu003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E常规调优思路:u003Cu002Fpu003Eu003Cpu003E针对业务周期性的卡顿,例如在每天10-11点业务特别慢,但是还能够使用,过了这段时间就好了。u003Cu002Fpu003Eu003Cpu003E1) 查看slowlog,分析slowlog,分析出查询慢的语句。u003Cu002Fpu003Eu003Cpu003E2) 按照一定优先级,进行一个一个的排查所有慢语句。u003Cu002Fpu003Eu003Cpu003E3) 分析top sql,进行explain调试,查看语句执行时间。u003Cu002Fpu003Eu003Cpu003E4) 调整索引或语句本身。u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E3、系统层面u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003Ecpu方面:u003Cu002Fpu003Eu003Cpu003Evmstat、sar top、htop、nmon、mpstatu003Cu002Fpu003Eu003Cpu003E内存:u003Cu002Fpu003Eu003Cpu003Efree 、ps -aux 、u003Cu002Fpu003Eu003Cpu003EIO设备(磁盘、网络):u003Cu002Fpu003Eu003Cpu003Eiostat 、 ss 、 netstat 、 iptraf、iftop、lsof、u003Cu002Fpu003Eu003Cpu003Evmstat 命令说明:u003Cu002Fpu003Eu003Cpu003EProcs:r显示有多少进程正在等待CPU时间。b显示处于不可中断的休眠的进程数量。在等待Iu002FOMemory:swpd显示被交换到磁盘的数据块的数量。未被使用的数据块,用户缓冲数据块,用于操作系统的数据块的数量Swap:操作系统每秒从磁盘上交换到内存和从内存交换到磁盘的数据块的数量。s1和s0最好是0Io:每秒从设备中读入b1的写入到设备b0的数据块的数量。反映了磁盘Iu002FOSystem:显示了每秒发生中断的数量(in)和上下文交换(cs)的数量Cpu:显示用于运行用户代码,系统代码,空闲,等待Iu002FO的CPU时间u003Cu002Fpu003Eu003Cpu003Eiostat命令说明u003Cu002Fpu003Eu003Cpu003E实例命令:iostat -dk 1 5u003Cu002Fpu003Eu003Cpu003Eiostat -d -k -x 5 (查看设备使用率(%util)和响应时间(await))u003Cu002Fpu003Eu003Cpu003E1) tps:该设备每秒的传输次数。“一次传输”意思是“一次Iu002FO请求”。多个逻辑请求可能会被合并为“一次Iu002FO请求”。u003Cu002Fpu003Eu003Cpu003E2) iops :硬件出厂的时候,厂家定义的一个每秒最大的IO次数,”一次传输”请求的大小是未知的。u003Cu002Fpu003Eu003Cpu003E3) kBreadu002Fs:每秒从设备(drive expressed)读取的数据量;u003Cu002Fpu003Eu003Cpu003E4) KBwrtnu002Fs:每秒向设备(drive expressed)写入的数据量;u003Cu002Fpu003Eu003Cpu003E5) kBread:读取的总数据量;7、kBwrtn:写入的总数量数据量;这些单位都为Kilobytes。u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E4、系统层面问题解决办法u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003E你认为到底负载高好,还是低好呢?u003Cu002Fpu003Eu003Cpu003E在实际的生产中,一般认为 cpu只要不超过90%都没什么问题 。u003Cu002Fpu003Eu003Cpu003E当然不排除下面这些特殊情况:u003Cu002Fpu003Eu003Cpu003E问题一:cpu负载高,IO负载低u003Cu002Fpu003Eu003Cpu003E1、内存不够 2、磁盘性能差 3、SQL问题 ——>去数据库层,进一步排查sql问题 4、IO出问题了(磁盘到临界了、raid设计不好、raid降级、锁、在单位时间内tps过高) 5、tps过高: 大量的小数据IO、大量的全表扫描u003Cu002Fpu003Eu003Cpu003E问题二:IO负载高,cpu负载低u003Cu002Fpu003Eu003Cpu003E1、大量小的IO 写操作:2、autocommit ,产生大量小IO 3、IOu002FPS,磁盘的一个定值,硬件出厂的时候,厂家定义的一个每秒最大的IO次数。4、大量大的IO 写操作 5、SQL问题的几率比较大u003Cu002Fpu003Eu003Cpu003E问题三:IO和cpu负载都很高u003Cu002Fpu003Eu003Cpu003E硬件不够了或sql存在问题u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch1 toutiao-origin=”h2″u003E五、基础优化u003Cu002Fh1u003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E1、优化思路u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003E定位问题点:u003Cu002Fpu003Eu003Cpu003E硬件 –> 系统 –> 应用 –> 数据库 –> 架构(高可用、读写分离、分库分表)u003Cu002Fpu003Eu003Cpu003E处理方向:u003Cu002Fpu003Eu003Cpu003E明确优化目标、性能和安全的折中、防患未然u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E2、硬件优化u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003E主机方面:u003Cu002Fpu003Eu003Cpu003E1) 根据数据库类型,主机CPU选择、内存容量选择、磁盘选择 u003Cu002Fpu003Eu003Cpu003E2) 平衡内存和磁盘资源 u003Cu002Fpu003Eu003Cpu003E3) 随机的Iu002FO和顺序的Iu002FO u003Cu002Fpu003Eu003Cpu003E4) 主机 RAID卡的BBU(Battery Backup Unit)关闭u003Cu002Fpu003Eu003Cpu003Ecpu的选择:u003Cu002Fpu003Eu003Cpu003E1) cpu的两个关键因素:核数、主频 u003Cu002Fpu003Eu003Cpu003E2) 根据不同的业务类型进行选择u003Cu002Fpu003Eu003Cpu003E3) cpu密集型:计算比较多,OLTP 主频很高的cpu、核数还要多 u003Cu002Fpu003Eu003Cpu003E4) IO密集型:查询比较,OLAP 核数要多,主频不一定高的u003Cu002Fpu003Eu003Cpu003E内存的选择:u003Cu002Fpu003Eu003Cpu003E1) OLAP类型数据库,需要更多内存,和数据获取量级有关。u003Cu002Fpu003Eu003Cpu003E2) OLTP类型数据一般内存是cpu核心数量的2倍到4倍,没有最佳实践。u003Cu002Fpu003Eu003Cpu003E存储方面:u003Cu002Fpu003Eu003Cpu003E1) 根据存储数据种类的不同,选择不同的存储设备 u003Cu002Fpu003Eu003Cpu003E2) 配置合理的RAID级别(raid5、raid10、热备盘) u003Cu002Fpu003Eu003Cpu003E3) 对与操作系统来讲,不需要太特殊的选择,最好做好冗余(raid1)(ssd、sas 、sata)u003Cu002Fpu003Eu003Cpu003Eraid卡:主机raid卡选择:u003Cu002Fpu003Eu003Cpu003E1) 实现操作系统磁盘的冗余(raid1)u003Cu002Fpu003Eu003Cpu003E2) 平衡内存和磁盘资源 u003Cu002Fpu003Eu003Cpu003E3) 随机的Iu002FO和顺序的Iu002FO u003Cu002Fpu003Eu003Cpu003E4) 主机 RAID卡的BBU(Battery Backup Unit)要关闭。u003Cu002Fpu003Eu003Cpu003E网络设备方面:u003Cu002Fpu003Eu003Cpu003E使用流量支持更高的网络设备(交换机、路由器、网线、网卡、HBA卡)u003Cu002Fpu003Eu003Cpu003E注意:以上这些规划应该在初始设计系统时就应该考虑好。u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E3、服务器硬件优化u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003E1) 物理状态灯:u003Cu002Fpu003Eu003Cpu003E2) 自带管理设备:远程控制卡(FENCE设备:ipmi ilo idarc),开关机、硬件监控。u003Cu002Fpu003Eu003Cpu003E3) 第三方的监控软件、设备(snmp、agent)对物理设施进行监控u003Cu002Fpu003Eu003Cpu003E4) 存储设备:自带的监控平台。EMC2(hp收购了), 日立(hds),IBM低端OEM hds,高端存储是自己技术,华为存储u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E4、系统优化u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003ECpu:u003Cu002Fpu003Eu003Cpu003E基本不需要调整,在硬件选择方面下功夫即可。u003Cu002Fpu003Eu003Cpu003E内存:u003Cu002Fpu003Eu003Cpu003E基本不需要调整,在硬件选择方面下功夫即可。u003Cu002Fpu003Eu003Cpu003ESWAP:u003Cu002Fpu003Eu003Cpu003EMySQL尽量避免使用swap。阿里云的服务器中默认swap为0u003Cu002Fpu003Eu003Cpu003EIO :u003Cu002Fpu003Eu003Cpu003E1) raid、no lvm、 ext4或xfs、ssd、IO调度策略 u003Cu002Fpu003Eu003Cpu003E2) Swap调整(不使用swap分区)u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Eu002Fprocu002Fsysu002Fvmu002Fswu003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-1″u003Eappu003Cu002Fiu003Einess的内容改成0(临时),u002Fetcu002Fsysctl.conf上添加vm.swu003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-1″u003Eappu003Cu002Fiu003Einess=0(永久)u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E这个参数决定了Linux是倾向于使用swap,还是倾向于释放文件系统cache。在内存紧张的情况下,数值越低越倾向于释放文件系统cache。当然,这个参数只能减少使用swap的概率,并不能避免Linux使用swap。修改MySQL的配置参数innodbflushmethod,开启O_DIRECT模式。这种情况下,InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘,但是redo log依旧会使用文件系统cache。值得注意的是,Redo log是覆写模式的,即使使用了文件系统的cache,也不会占用太多。u003Cu002Fpu003Eu003Cpu003EIO调度策略:u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Evi u002Fbootu002Fgrubu002Fgrub.confu003Cu002Fcodeu003Eu003Ccodeu003E更改到如下内容:u003Cu002Fcodeu003Eu003Ccodeu003Ekernel u002Fbootu002Fvmlinuz-2.6.18-8.el5 ro root=LABEL=u002F elevator=deadline rhgb quietu003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E5、系统参数调整u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003ELinux系统内核参数优化:u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Evim u002Fetcu002Fsysctl.confu003Cu002Fcodeu003Eu003Ccodeu003E net.ipv4.ip_local_port_range = 1024 65535 # 用户端口范围u003Cu002Fcodeu003Eu003Ccodeu003E net.ipv4.tcp_max_syn_backlog = 4096 u003Cu002Fcodeu003Eu003Ccodeu003E net.ipv4.tcp_fin_timeout = u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-4″u003E30u003Cu002Fiu003E u003Cu002Fcodeu003Eu003Ccodeu003E fs.file-max=65535 # 系统最大文件句柄,控制的是能打开文件最大数量 u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E用户限制参数(mysql可以不设置以下配置):u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Evim u002Fetcu002Fsecurityu002Flimits.conf u003Cu002Fcodeu003Eu003Ccodeu003E * soft nproc 65535u003Cu002Fcodeu003Eu003Ccodeu003E * hard nproc 65535u003Cu002Fcodeu003Eu003Ccodeu003E * soft nofile 65535u003Cu002Fcodeu003Eu003Ccodeu003E * hard nofile 65535u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E6、应用优化u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003E业务应用和数据库应用独立,防火墙:iptables、selinux等其他无用服务:u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Echkconfig –level 23456 acpid offu003Cu002Fcodeu003Eu003Ccodeu003Echkconfig –level 23456 anacron offu003Cu002Fcodeu003Eu003Ccodeu003Echkconfig –level 23456 autofs offu003Cu002Fcodeu003Eu003Ccodeu003Echkconfig –level 23456 avahi-daemon offu003Cu002Fcodeu003Eu003Ccodeu003Echkconfig –level 23456 bluetooth offu003Cu002Fcodeu003Eu003Ccodeu003Echkconfig –level 23456 cups offu003Cu002Fcodeu003Eu003Ccodeu003Echkconfig –level 23456 firstboot offu003Cu002Fcodeu003Eu003Ccodeu003Echkconfig –level 23456 haldaemon offu003Cu002Fcodeu003Eu003Ccodeu003Echkconfig –level 23456 hplip offu003Cu002Fcodeu003Eu003Ccodeu003Echkconfig –level 23456 ip6tables offu003Cu002Fcodeu003Eu003Ccodeu003Echkconfig –level 23456 iptables offu003Cu002Fcodeu003Eu003Ccodeu003Echkconfig –level 23456 isdn offu003Cu002Fcodeu003Eu003Ccodeu003Echkconfig –level 23456 pcscd offu003Cu002Fcodeu003Eu003Ccodeu003Echkconfig –level 23456 sendmail offu003Cu002Fcodeu003Eu003Ccodeu003Echkconfig –level 23456 yum-updatesd offu003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E安装图形界面的服务器不要启动图形界面 runlevel 3,另外,思考将来我们的业务是否真的需要MySQL,还是使用其他种类的数据库。用数据库的最高境界就是不用数据库。u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch1 toutiao-origin=”h2″u003E六、数据库优化u003Cu002Fh1u003Eu003Cpu003ESQL优化方向:u003Cu002Fpu003Eu003Cpu003E执行计划、索引、SQL改写u003Cu002Fpu003Eu003Cpu003E架构优化方向:u003Cu002Fpu003Eu003Cpu003E高可用架构、高性能架构、分库分表u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E1、数据库参数优化u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpu003E调整:u003Cu002Fpu003Eu003Cpu003E实例整体(高级优化,扩展)u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Ethread_concurrency # 并发线程数量个数u003Cu002Fcodeu003Eu003Ccodeu003Esort_buffer_size # 排序缓存u003Cu002Fcodeu003Eu003Ccodeu003Eread_buffer_size # 顺序读取缓存u003Cu002Fcodeu003Eu003Ccodeu003Eread_rnd_buffer_size # 随机读取缓存u003Cu002Fcodeu003Eu003Ccodeu003Ekey_buffer_size # 索引缓存u003Cu002Fcodeu003Eu003Ccodeu003Ethread_cache_size # (1G—>8, 2G—>16, 3G—>32, >3G—>64)u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E连接层(基础优化)u003Cu002Fpu003Eu003Cpu003E设置合理的连接客户和连接方式u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Emax_connections # 最大连接数,看交易笔数设置 u003Cu002Fcodeu003Eu003Ccodeu003Emax_connect_errors # 最大错误连接数,能大则大u003Cu002Fcodeu003Eu003Ccodeu003Econnect_timeout # 连接超时u003Cu002Fcodeu003Eu003Ccodeu003Emax_user_connections # 最大用户连接数u003Cu002Fcodeu003Eu003Ccodeu003Eskip-name-resolve # 跳过域名解析u003Cu002Fcodeu003Eu003Ccodeu003Ewait_timeout # 等待超时u003Cu002Fcodeu003Eu003Ccodeu003Eback_log # 可以在堆栈中的连接数量u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003ESQL层(基础优化)u003Cu002Fpu003Eu003Cpu003Equerycachesize:查询缓存–>>>OLAP类型数据库,需要重点加大此内存缓存.u003Cu002Fpu003Eu003Cpu003E1) 但是一般不会超过GB.u003Cu002Fpu003Eu003Cpu003E2) 对于经常被修改的数据,缓存会立马失效。u003Cu002Fpu003Eu003Cpu003E3) 我们可以实用内存数据库(redis、memecache),替代他的功能。u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch2 toutiao-origin=”h3″u003Eu003Cstrongu003E2、存储引擎层(innodb基础优化参数)u003Cu002Fstrongu003Eu003Cu002Fh2u003Eu003Cpreu003Eu003Ccodeu003Edefault-storage-engineu003Cu002Fcodeu003Eu003Ccodeu003Einnodb_buffer_pool_size # 没有固定大小,50%测试值,看看情况再微调。但是尽量设置不要超过物理内存70%u003Cu002Fcodeu003Eu003Ccodeu003Einnodb_file_per_table=(1,0)u003Cu002Fcodeu003Eu003Ccodeu003Einnodb_flush_log_at_trx_commit=(0,1,2) # 1是最安全的,0是性能最高,2折中u003Cu002Fcodeu003Eu003Ccodeu003Ebinlog_syncu003Cu002Fcodeu003Eu003Ccodeu003EInnodb_flush_method=(O_DIRECT, fdatasync)u003Cu002Fcodeu003Eu003Ccodeu003Einnodb_log_buffer_size # 100M以下u003Cu002Fcodeu003Eu003Ccodeu003Einnodb_log_file_size # 100M 以下u003Cu002Fcodeu003Eu003Ccodeu003Einnodb_log_files_in_group # 5个成员以下,一般2-3个够用(iblogfile0-N)u003Cu002Fcodeu003Eu003Ccodeu003Einnodb_max_dirty_pages_pct # 达到百分之75的时候刷写 内存脏页到磁盘。u003Cu002Fcodeu003Eu003Ccodeu003Elog_binu003Cu002Fcodeu003Eu003Ccodeu003Emax_binlog_cache_size # 可以不设置u003Cu002Fcodeu003Eu003Ccodeu003Emax_binlog_size # 可以不设置u003Cu002Fcodeu003Eu003Ccodeu003Einnodb_additional_mem_pool_size #小于2G内存的机器,推荐值是20M。32G内存以上100Mu003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003Etips:大家可以u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-6″u003E关注u003Cu002Fiu003Eu003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-4″u003E微信u003Cu002Fiu003Eu003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-3″u003E公众号u003Cu002Fiu003E:Java后端,获取更多优秀博文推送。u003Cu002Fpu003Eu003Cp class=”pgc-end-source”u003E-END-u003Cu002Fpu003E”

原文始发于:MySQL 性能优化之骨灰级,高阶神技

主题测试文章,只做测试使用。发布者:℅傍ㄖ免沦陷dε鬼,转转请注明出处:http://www.cxybcw.com/17908.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code