![2、分析问题：奥贬贰搁贰 – MySQL
mysql slow query
show [global status]、show
processlist、show engine innodb
status
profiling(mysql 5.0+)
mysql explain
其他，如 mysqlreport](https://image.slidesharecdn.com/mysql20090828-090914130407-phpapp02/85/MySql-20090828-10-320.jpg)
![2、分析问题：奥贬贰搁贰 – show
status/processlist
show [global] status
重点关注key hit ratio, qcache hit ratio, lock, slow
query, innodb buffer hit ratio, innodb data/log
write/read
show processlist
重点关注一些异常状态的查询，或者耗时很长的查询
show engine innodb status
重点关注log flush状态，锁状态，事务状态，内存分配消耗](https://image.slidesharecdn.com/mysql20090828-090914130407-phpapp02/85/MySql-20090828-16-320.jpg)
叶金荣：MySql优化 20090828 系统架构师大会
- 1. Linux 下的 MySQL 调优 叶金荣 http://www.chinaunix.net http://imysql.cn
- 2. Linux下的MySQL调优 1 需求来源：WHY 2 分析问题：WHERE 3 解决问题：HOW 4 总结
- 3. 1、需求来源：奥贬驰
- 6. 1、需求来源：奥贬驰 – 不要赶在老板后面 老板很郁闷 后果很严重！！
- 7. Linux下的MySQL调优 1 需求来源：WHY 2 分析问题：WHERE 3 解决问题：HOW 4 总结
- 8. 2、分析问题：奥贬贰搁贰
- 10. 2、分析问题：奥贬贰搁贰 – MySQL mysql slow query show [global status]、show processlist、show engine innodb status profiling(mysql 5.0+) mysql explain 其他，如 mysqlreport
- 13. 2、分析问题：奥贬贰搁贰 - microslow 打上microslow补丁，支持最小微秒单位，同时还能显示执行计划 主要选项：long_query_time, log_slow_filter， log_slow_verbosity 详细解释查看 http://www.percona.com/docs/wiki/patches:microsl ow_innodb
- 14. 2、分析问题：奥贬贰搁贰 – 分析 slowlog 几个slow query log分析工具 mysqlsla – 重点推荐 mysqldumpslow mysql-explain-slow-log mysql-log-filter myprofi
- 16. 2、分析问题：奥贬贰搁贰 – show status/processlist show [global] status 重点关注key hit ratio, qcache hit ratio, lock, slow query, innodb buffer hit ratio, innodb data/log write/read show processlist 重点关注一些异常状态的查询，或者耗时很长的查询 show engine innodb status 重点关注log flush状态，锁状态，事务状态，内存分配消耗
- 17. 2、分析问题：奥贬贰搁贰 - explain 表的读取顺序 每个表都是如何读取的 可能用到哪些索引，实际使用了哪些索引 表是如何引用的 查询优化器从每个表中预计读取的记录数 其他额外信息，尽量避免Using filesort / Using temporary的查询产生
- 18. 2、分析问题：奥贬贰搁贰 - profiling mysql profiling +------------------------------------------+--------------+ | Status | Duration | +------------------------------------------+--------------+ | (initialization) | 0.000009 | | checking query cache for query | 0.000071 | | Opening tables | 0.000018 | | System lock | 0.00001 | | Table lock | 0.0000660 | | init | 0.000034 | | optimizing | 0.00001 |
- 19. 2、分析问题：奥贬贰搁贰 - profiling | statistics | 0.000027 | | preparing | 0.000027 | | executing | 0.000006 | | Sending data | 0.000252 | | end | 0.000035 | | query end | 0.000015 | | storing result in query cache | 0.000014 | | freeing items | 0.000346 | | closing tables | 0.000015 | | logging slow query | 0.000007 | +-------------------------------------------+------------+
- 20. 2、分析问题：奥贬贰搁贰 – 其他 定期检查多余的索引以及没有使用索引的慢查询 利用 mysqlreport 产生可读性更强的报告 利用 Profiling 剖析一次查询瓶颈所在 其他工具，包括监控工具，linux自带工具等
- 21. Linux下的MySQL调优 1 需求来源：WHY 2 分析问题：WHERE 3 解决问题：HOW 4 总结
- 22. 3、解决问题：HOW 硬件 软件 MySQL自身 程序，架构
- 24. 3、解决问题：HOW – 升级硬、软件 不同RAID级别 & 不同磁盘数 的IO性能对比 140 130 120 100 100 89 80 68 60 40 20 0 raid 5(4) raid 5(6) raid 1+0(4) raid 1+0(6)
- 25. 3、解决问题：HOW – 升级硬、软件 FW 升级前后 8G内存拷贝38G大文件耗时对比 88 86.493 86 84 82 80 78 76 76.0777 74 72 70 FW升级前 FW升级后
- 26. 3、解决问题：HOW – 升级硬、软件 232 XFS 72 33 ReiserFS Ext3 1750 83 41 文件系统性能测试对比 553 23 8 Create Read Delete
- 27. 3、解决问题：HOW – 升级硬、软件 250000 200000 150000 write cache 25% write cache 50% write cache 75% 100000 50000 0 8 16 32 64 128 256 512 1024 2048
- 28. 3、解决问题：HOW – MySQL设置 参数名 说明 Key Buffer MyISAM索引缓冲 Query Cache 查询结果缓存 Sort/read/jion Buffer 排序/全表扫描/表连接缓冲 Slow Query 设置慢查询,打上msl补丁 Tmp Table 内存表，还需要注意max_heap_table_size Innodb Buffer InnoDB最重要的设置,包括日志缓冲
- 30. 3、解决问题：HOW – 引擎 MyISAM InnoDB TukoDB XtraDB Memcached for MySQL Waffle Grid Project
- 31. 3、解决问题：HOW – Innodb Plugin
- 32. 3、解决问题：HOW – 补丁 Performance Improving ? IO Pathces: multiple threads,Adaptive checkpoint,IO Control of Insert buffer,multiple pages, ? Extra rollback segments ? Enhanced read_write locks ? Split buffer_pool mutex even more ? InnoDB IO patches Usability ? Microslow ? Limit data dictionary ? IO access pattern ? Show buffer pool content ? Show memory usage ? Show locks held ? Extra undo slots
- 33. 3、解决问题：HOW – 程序、架构 选择合适的应用类型 OLTP / OLAP ? 业务量估计 读写比？峰值？预计使用年限？ 缓存利用 是否都需要实时查询？ 保持线上表尽可能瘦小 速度快、好维护、并发高 分表 可扩展、速度快
- 35. 3、解决问题：HOW – 其他 垂直/水平切分服务器/数据库、表 开启MySQL复制，实现读、写分离 在复制的基础上，增加负载均衡 采用集群+复制(MySQL 6.0+) 频繁更新的表，可以分离成父表和子表(内存表) 用统计表保存定时统计结果，而不是在大表上直接统计 编写存储过程/函数来代替大量的外部应用程序交互 确保索引合理利用，尽量使用联合索引 适当加大查询缓存(query cache) 尽量减少交互次数 尽量使用固定格式的SQL语句，查询语句中少用运算或函数 缩短每个事务 使用适当的字段类型；适当的长度，有需要的时候再扩充
- 36. 3、解决问题：HOW – 其他 分解复杂查询为多个小查询 字符型字段采用前缀索引 不直接执行 COUNT(*) – innodb 多个操作放在一起提交，但要注意事务不能太大 日志文件并非越大越好，需要考虑恢复和检查点 左连接时把数据量小的表放在前面 innodb_flush_log_at_trx_commit 可以尝试设置为 2，甚至是 0 导入数据时关闭 AUTOCOMMIT 以及 UNIQUE_CHECKS、 FOREIGN_KEY_CHECKS 复杂的查询总是先用EXPLAIN来分析一下 定期执行OPTIMIZE TABLE整理碎片 用char来代替varchar，MyISAM是这样，InnoDB则相反 关闭swappiness，避免发生swappy
- 38. 3、解决问题：HOW – 升级硬、软件 优化调试过程 Do See Adjust
- 39. Linux下的MySQL调优 1 需求来源：WHY 2 分析问题：WHERE 3 解决问题：HOW 4 总结
- 40. 善用网络资源
- 42. Q&A 谢谢大家！