MySQL性能优化教程之MySQL运维优化

发表于2014-08-29 15:33  |  次阅读  |  0条评论  |   作者:admin

本文摘自51上一期杂志,对DB的运维做了简单全面的总结,可以参考以下,推荐给大家.
----------------------------------------------------------
MySQL性能优化教程之MySQL运维优化

存储引擎类型
l Myisam 速度快,响应快。表级锁是致命问题。
l Innodb 目前主流存储引擎
n 行级锁
u 务必注意影响结果集的定义是什么 行级锁会带来更新的额外开销,但是通常情况下是值得的。
n 事务提交
u 对 i/o 效率提升的考虑
u 对安全性的考虑
l HEAP 内存引擎
n 频繁更新和海量读取情况下仍会存在锁定状况
 
内存使用考量
l 理论上,内存越大,越多数据读取发生在内存,效率越高
l Query cache 的使用
n 如果前端请求重复度不高,或者应用层已经充分缓存重复请求,query cache 不必设置很大,甚至可以不设置.
n 如果前端请求重复度较高,无应用层缓存,query cache 是一个很
好的偷懒选择
u 对于中等以下规模数据库应用,偷懒不是一个坏选择。
u 如果确认使用 query cache,记得定时清理碎片,flush query cache.
l 要考虑到现实的硬件资源和瓶颈分布
l 学会理解热点数据,并将热点数据尽可能内存化
n 所谓热点数据,就是最多被访问的数据。
n 通常数据库访问是不平均的,少数数据被频繁读写,而更多数据 鲜有读写。
n 学会制定不同的热点数据规则,并测算指标。
u 热点数据规模,理论上,热点数据越少越好,这样可以更好的 满足业务的增长趋势。
u 响应满足度,对响应的满足率越高越好。
 
u 比如依据最后更新时间,总访问量,回访次数等指标定义热 点数据,并测算不同定义模式下的热点数据规模
 
性能与安全性考量
l 数据提交方式
n innodb_flush_log_at_trx_commit = 1 每次自动提交,安全性高,i/o 压力大
n innodb_flush_log_at_trx_commit = 2 每秒自动提交,安全性略 有影响,i/o 承载强。
l 日志同步
n Sync-binlog =1 每条自动更新,安全性高,i/o 压力大
n Sync-binlog = 0 根据缓存设置情况自动更新,存在丢失数据 和同步延迟风险,i/o 承载力强。
n 个人建议保存 binlog 日志文件,便于追溯 更新操作和系统恢复。
n 如对日志文件的 i/o 压力有担心,在内存宽裕的情况下,可考虑 将 binlog 写入到诸如 /dev/shm 这样的内存映射分区,并定时 将旧有的 binlog 转移到物理硬盘。
l 性能与安全本身存在相悖的情况,需要在业务诉求层面决定取舍
n 学会区分什么场合侧重性能,什么场合侧重安全
n 学会将不同安全等级的数据库用不同策略管理
存储/写入压优化
l 顺序读写性能远高于随机读写
l 将顺序写数据和随机读写数据分成不同的物理磁盘进行,有助于 i/o 压力的疏解
      •    数据库文件涉及索引等内容,写入是随即写
•    binlog 文件是顺序写
•    淘宝数据库存储优化是这样处理的
l 部分安全要求不高的写入操作可以用 /dev/shm 分区存储,简单变 成内存写。
l 多块物理硬盘做 raid10,可以提升写入能力
l 关键存储设备优化,善于比对不同存储介质的压力测试数据。
•    例如 fusion-io 在新浪和淘宝都有较多使用。
 
l 涉及必须存储较为庞大的数据量时
•    压缩存储,可以通过增加 cpu 开销(压缩算法)减少 i/o 压力。前 提是你确认 cpu 相对空闲而 i/o 压力很大。 新浪微博就是压缩 存储的典范。
•    通过 md5 去重存储,案例是 QQ 的文件共享,以及 dropbox 这样 的共享服务,如果你上传的是一个别人已有的文件,计算 md5 后, 直接通过 md5 定位到原有文件,这样可以极大减少存储量。涉 及文件共享,头像共享,相册等应用,通过这种方法可以减少超过
70% 的存储规模,对硬件资源的节省是相当巨大的。缺点是,删 除文件需要甄别该 md5 是否有其他人使用。 去重存储,用户量 越多,上传文件越多,效率越高!
•    文件尽量不要存储到数据库内。尽量使用独立的文件系统存储, 该话题不展开。
运维监控体系
l 系统监控
   n 服务器资源监控
u Cpu, 内存,硬盘空间,i/o 压力
u 设置阈值报警
n 服务器流量监控
u 外网流量,内网流量
u 设置阈值报警
n 连接状态监控
u Show processlist 设置阈值,每分钟监测,超过阈值记录
l 应用监控
n 慢查询监控
u 慢查询日志
u 如果存在多台数据库服务器,应有汇总查阅机制。
 
n 请求错误监控
u 高频繁应用中,会出现偶发性数据库连接错误或执行错误, 将错误信息记录到日志,查看每日的比例变化。
u 偶发性错误,如果数量极少,可以不用处理,但是需时常监控 其趋势。
u 会存在恶意输入内容,输入边界限定缺乏导致执行出错,需 基于此防止恶意入侵探测行为。
n 微慢查询监控
u 高并发环境里,超过 0.01 秒的查询请求都应该关注一下。
 
n 频繁度监控
u 写操作,基于 binlog,定期分析。
u 读操作,在前端 db 封装代码中增加抽样日志,并输出执行时 间。
u 分析请求频繁度是开发架构 进一步优化的基础
u 最好的优化就是减少请求次数!
l 总结 :
n 监控与数据分析是一切优化的基础。
n 没有运营数据监测就不要妄谈优化!
n 监控要注意不要产生太多额外的负载,不要因监控带来太多额 外系统开销
 
本文摘录自 caoz 的文档《MySQL 性能优化教程》,下载地址 : http://wenku.baidu.com/view/aa43ecc3aa00b52acfc7ca94.html?st=1 推荐阅读 :
MySQL 中创建及优化索引组织结构的思路 http://database.51cto.com/art/201110/296764.htm MySQL 性能优化的 21 个最佳实践 http://database.51cto.com/art/201108/282615.htm 专题 :讲述 MySQL 索引和优化的故事 http://database.51cto.com/art/201107/278040.htm MySQL 技巧 : 结合相关参数 做好 Limit 优化 http://database.51cto.com/art/201103/248219.htm
本站关键字:sunny90 web开发 数据库 移动开发 服务器 Nginx Mysql PHP
Copyright © sunny90版权所有 power by sunny90.com  
湘ICP备14012284号-1,粤公网安备 44030602000307号