一步到位实现 MySQL 优化 ---- 从硬件系统配置设计等多角度优化 MySQL 叶金荣 QQ 群 : 微信公众号 :MySQL 中文网

Size: px

Start display at page:

Download "一步到位实现 MySQL 优化 ---- 从硬件系统配置设计等多角度优化 MySQL 叶金荣 QQ 群 : 微信公众号 :MySQL 中文网"

右冀
5 years ago
Views:

1 一步到位实现 MySQL 优化 ---- 从硬件系统配置设计等多角度优化 MySQL 叶金荣 QQ 群 : weibo:@yejinrong, 微信公众号 :MySQL 中文网

2 About Me 叶金荣, 网络常用 ID:yejr Oracle ACE(MySQL) 2006 年创办国内首个 MySQL 专业技术网站精通 MySQL 数据库,10 年 +MySQL 相关工作经验, 擅长 MySQL 优化架构设计故障处理

3 About Me MySQL 经历 MySQl ~ now(5.7) 2000 ~ now 2012, ORACLE ACE(MySQL) Linux 经历蓝点 Linux 2.0 TurboLinux FreeBSD Debian Ubuntu Fedora CentOS RHEL

4 About Me 联系方式国内首个 MySQL 技术博客站,From 2006) QQ 群 : QQ: 叶金荣微信公众号 :MySQL 中文网 imysql@gmail.com

5 MySQL 的特点 Not ORACLE,Not SQL Server,Not PostgreSQL Not Excel, Not Access Not File Storage Not Calculator Not Seach Engin Not... MySQL is MySQL

6 MySQL 的特点 CPU 的利用特点 <5.1, 多核心支持较弱 5.1, 可利用 4 个核 5.5, 可利用 24 个核 5.6, 可利用 64 个核每个连接对应一个线程, 每个并发 query 只能使用到一个核

7 MySQL 的特点内存利用特点类似 ORACLE 的 SGA PGA 模式, 注意 PGA 不宜分配过大内存管理简单有效在高 TPS 高并发环境下, 可增加物理内存以减少物理 IO, 提高并发性能官方分支锁并发竞争比较严重,MariaDB Percona 进行优化有类似 ORACLE library cache 的 query cache, 但效果不佳, 建议关闭执行计划没有缓存 ( 类似 ORACLE 的 library cache) 通常内存建议按热点数据总量的 15%-20% 来规划, 专用单实例则可以分配物理内存的 50~70% 左右类似 K-V 简单数据, 采用 memcached Redis 等 NOSQL 来缓存

8 MySQL 的特点对磁盘的利用特点 binlog redo log undo log 主要顺序 IO datafile 是随机 IO 和顺序 IO 都有 OLTP 业务以随机 IO 为主, 建议加大内存, 尽量合并随机 IO 为顺序 IO OLAP 业务以顺序 IO 为主, 极大内存的同时增加硬盘数量提高顺序 IO 性能 MyISAM 是堆组织表 (HOT),InnoDB 是索引组织表 (IOT) InnoDB 相比 MyISAM 更消耗磁盘空间

9 优化思路确认问题确认瓶颈制定方案测试方案实施方案回顾反馈

10 确认瓶颈 top vmstat sar iotop dstat oprofile

11 确认瓶颈 slow log show global status show processlist show engine innodb status pt-ioprofile

12 硬件优化 BIOS 设置优化 System Profile( 系统配置 ) 选择 Performance Per Watt Optimized(DAPC), 发挥最大功耗性能 Memory Frequency( 内存频率 ) 选择 Maximum Performance( 最佳性能 ) C1E, 允许在处理器处于闲置状态时启用或禁用处理器切换至最低性能状态, 建议关闭 ( 默认启用 ) C States(C 状态 ), 允许启用或禁用处理器在所有可用电源状态下运行, 建议关闭 ( 默认启用 )

13 硬件优化 IO 子系统优化阵列卡配备 CACHE 及 BBU 模块, 提高 IOPS 设置写策略为 WB, 或者 FORCE WB, 禁用 WT 策略关闭预读, 没必要预读, 那点宝贵的 CACHE 用来做写缓存阵列级别使用 RAID 1+0, 而不是 RAID 5 关闭物理磁盘 cache 策略, 防止丢数据使用高转速硬盘, 不使用低转速盘使用 SSD 或者 PCIe-SSD 盘

14 系统优化 vm.swappiness /sys/block/sdx/queue/scheduler 文件系统首选 xfs, 其次 ext4,zfs 也很不错, 但在 linux 下不是那么可靠

15 系统优化

16 系统优化

17 配置优化全局参数 interactive_timeout wait_timeout open_files_limit max_connections thread_pool

18 配置优化全局参数 thread_pool

19 配置优化内存相关内存相关参数

20 配置优化 InnoDB 相关 innodb_buffer_pool_size innodb_data_file_path innodb_flush_log_at_trx_commit innodb_log_buffer_size & innodb_log_file_size transaction_isolation

21 配置优化 InnoDB 相关

22 配置优化 InnoDB 相关

23 配置优化其他 general_log log_bin sync_binlog long_query_time log_slow_query

24 配置优化 InnoDB 相关

25 配置优化 InnoDB 相关

26 设计优化先入为主默认使用 InnoDB 引擎, 可适用 99% 以上业务场景并发没人愿意所有的请求都被串行的执行完成数据一致性交易类业务要求数据高一致性, 确保数据完整性 Crash Recovery 故障自动修复, 修复相比 MyISAM 速度更快更高存取效率行锁减低锁粒度, 更高内存利用率提高数据索引存取效率

27 设计优化 Schema 设计不管 InnoDB 与否, 都设计自增列主键日期时间 IPV4 适用 INT UNSIGNED 存储性别是否等枚举类型, 使用 ENUM/TINYINT, 而非 CHAR/VARCHAR 杜绝 TEXT/BLOB, 可以做垂直拆分, 或者转成 MyISAM 表 USERNAME:VARCHAR(255) VS VARCHAR(30) vs CHAR(30) 所有字段显式定义 NOT NULL

28 设计优化 Schema 设计基数 ( Cardinality ) 很低的字段不创建索引 (MySQL 还不支持 bitmap 索引 ) 采用第三方系统实现 text/blob 全文检索常用排序 (ORDER BY) 分组 (GROUP BY) 取唯一 (DISTINCT) 字段上创建索引索引数量不要太多, 有负作用多使用联合索引, 少用单独索引字符型列需要索引时, 创建前缀索引

29 设计优化无法使用索引的场景通过索引扫描的记录数超过 30%, 变成全表扫描联合索引中, 第一个索引列使用范围查询 -- 只能用到部分索引联合索引中, 第一个查询条件不是最左索引列模糊查询条件列最左以通配符 % 开始内存表 (HEAP 表 ) 使用 HASH 索引时, 使用范围检索或者 ORDER BY 两个独立索引, 其中一个用于检索, 一个用于排序 -- 只能用到其中一个索引,5.6 以上有 ICP 特性表关联字段类型不一样 ( 也包括长度不一样 ) 索引字段条件上使用函数

30 设计优化常见杀手级 SQL SELECT * vs SELECT col1, col2 ORDER BY RAND() LIMIT huge_num, offset SELECT COUNT(*) on InnoDB table WHERE func(key_col) =? -- 无法使用索引 WHERE key_part2 =? AND key_part3 =? -- 无法使用索引 WHERE key_part1 >? AND key_part2 =? -- 只能用到部分索引 SELECT WHERE key_col +? =? -- 无法使用索引

31 设计优化常见杀手级 SQL SELECT a.x... FROM a ORDER BY a.y LIMIT , 20; 采用子查询进行优化 => SELECT a.x... FROM a WHERE a.pkid > (SELECT pkid FROM a WHERE pkid >= ORDER BY a.y) LIMIT 20;

32 设计优化 - 架构设计减少物理 I/O, 让 MySQL 闲下来转变随机 I/O 为顺序 I/O 减小活跃数据分库分表读写分离 OLTP OLAP 分离

33 优化工具 pt-ioprofile mysqldumpslow pt-query-digest + Box Anemometer/Query-Digest-UI

34 常见优化误区分配内存越多越好, 可能导致 OS Swap session 级内存分配过大, 导致 OOM 索引越多越好, 可能导致更多 IO Qcache 设置过大, 实际效果差认为 MyISAM 的只读效率远高于 InnoDB 人云亦云, 不自己动手实践过度优化, 反而带来成本的上升

35 谢谢关注! 若本次分享对您有帮助, 请转发扩散, 谢谢! 在中获取更多内容

从上面这个表格中我们可以很明显看到巨大的差异当数据全部缓存到内存中内存大小会影响所有操作不管是 SELECT 还是 INSERT/UPDATE/DELETE 操作 INSERT 当往一个随机排序的索引中插入数据的时候会造成随机的读/写 UPDATE/DELETE 当更改数据的时候会导致磁盘的读/

从上面这个表格中我们可以很明显看到巨大的差异当数据全部缓存到内存中内存大小会影响所有操作不管是 SELECT 还是 INSERT/UPDATE/DELETE 操作 INSERT 当往一个随机排序的索引中插入数据的时候会造成随机的读/写 UPDATE/DELETE 当更改数据的时候会导致磁盘的读/ MySQL 服务器的 linux 性能优化和扩展技巧作者 Yoshinori Matsunbu 作者现在是 DeNA 公司的数据库和基础设施架构师之前在 SUN 公司工作他也是 HandlerSocket 的作者这个是 MySQL 的 NoSQL 插件本文是根据他的 PPT 整理而成的如有不正确敬请指教本文主要的内容有如下 1. 内存和 SWAP 空间管理 2. 同步 I/O 文件系统和

一步到位实现 MySQL 优化 ---- 从硬件 系统 配置 设计等多角度优化 MySQL 叶金荣 QQ 群 : 微信公众号 :MySQL 中文网

一步到位实现 MySQL 优化 ---- 从硬件系统配置设计等多角度优化 MySQL 叶金荣 QQ 群 : 微信公众号 :MySQL 中文网