MySQL

MySQL部署规范

Linux · 12月22日 · 2020年

知识体系

硬件体系结构性能

硬件相关

了解PC Server ,掌握DELL、HP、IBM 、华为、浪潮、联想、曙光等常用PC server特点(日常管理、稳定性&可靠性、特殊优化设置、远程设置)

掌握阵列基础知识(阵列级别、WB/WT、BBU、CACHE Policy)及如何优化

WB, Write Back

WT, write Through

正常,使用机械盘时,WB模式下至少比WT时的IOPS高一倍以上

了解服务器硬件健康状况监控,常见故障处理方案

了解硬件关键技术指标:

  • CPU(主频、多核、超线程、L1-L3 cache、时延)
  • 内存(容量、带宽、时延)
  • Disk i/o(转速、IOPS、吞吐,时延)
  • 网络(速率、时延)
  • SSD相关 擦写次数、总写入字节数、写放大、平均无故障时长、MLC/SLC)
  • 如何估算IOPS指标
  • 顺序读写,随机读写

MySQL roadmap发展历程

MySQL特点

  • 不要存储文本、图片、附件等大量对象
  • 不要只想复杂查询、复杂运算、或全文检索
  • 不支持bitmap索引,不支持函数索引、表达式索引(5.7后用虚拟列变相解决)
  • MySQL8.0以前,没有统计直方图
  • innoDB行锁机制处理不像Oracle
  • 只支持罗技复制,还不支持物理复制
  • 性能诊断工具没有Oracle那么丰富
  • 分区功能没有Oracle完善、高效
  • DBA管理工具没有Oracle丰富
  • 小事为主,适时提交
  • 用于数据持久存储,频繁更新的状态利用MQ或NOSQL缓存
  • 用于高并发事务控制,比如秒杀,订单控制。

CPU资源利用特点

  • 5.1之前,多核支持差,几乎就是不支持多核并行计算
  • 5.1,最高可用4个核;5.5,最高可用24个核;5.6,最高可用64个核。
  • 5.7,再也不用担心不能好好利用CPU多核特性了
  • 不过,每个query对应一个线程,只能用到一个逻辑core;每个连接对应一个线程,只能用到一个逻辑core
  • 使用/优化建议
  • 尽可能使用新版本,抛弃太旧版本
  • 物理CPU主频越高越好,核数越多越好
  • 每次请求尽可能快速结束,少用复杂SQL,事务及时提交/回滚

内存资源利用特点

  • innodb buffer pool 、key buffer、query cache类似Oracle中的SGA
  • sort buffer、join buffer、tmp table类似Oracle中的PGA
  • 早期官方版本中内存并发竟争锁比较严重,MariaDB、Percona对其做了优化
  • 使用/优化建议
  • 高并发时,更多内存可减少物理I/O,提高TPS
  • 关闭query cache(QCACHE,或QC)
  • key_buffer_size设置大概8-32MB
  • 通常专用单实例的innodb buffer pool设置物理内存的50%~70%
  • 临时状态数据、KV对象数据取在redis、memcached,最终需要持久化的数据才存储在MySQL中

磁盘I/O资源利用特点

  • undo log的I/O特征:顺序写,随机写;
  • redo log、binglog、relay log的I/O顺序写,顺序读;
  • 数据文件的I/O特征:随机读,随机写;
  • InnoDB是索引组织表(IOT)
  • 建议
  • 加大物理内存
  • 使用更高速I/O设备

MySQL优点

  • 跨平台,尤其借助Linux平台在互联网广为传播应用
  • 开源、免费,可根据实际需求改源码、打patch、增加plugin
  • 官方团队给力,版本迭代更频繁,修复BUG更及时,功能越来越强大
  • 大量的社区资源提供支持,帮助
  • 特别适合互联网短平快的应用开发模式
  • 权限控制灵活:主机/域名、用户、资源控制,8.0起增加role功能
  • SQL语法灵活,支持多种数据类型,自动隐式转化,可设置SQL_MODE调整兼容性
  • MySQL很早实现复制特性使得基于MySQL的架构设计很轻松实现架构快速扩展
  • 简单上手快,易用好批量安装部署、管理,特别适合互联网爆发增长特点

MySQL不足

  • 单进程多线程模式(高并发下,类似Oracle这种多线程做法,其性能表现更好)
  • 5.6之前无CBO特征,5.6有一定CBO规则,5.7可调整cost rulo
  • 每个连接/每个query只能使用到一个逻辑CPU
  • 没有连接池,随着连接数的增加性能下降严重,但有Thread Pool
  • 没有sql解析缓存(library cache),还好sql解析比较轻量级
  • online DDL虽不如Oracle强大, 但也在逐渐增强,且可采用pt-osc补充
  • 没有hash join,少用复杂join或无索引join
  • 优化器比较弱,还在持续增强,8.0起支持倒序索引、不可见索引、直方图不错的做法

系统部署规

  • 关闭CPU节能,设定为最大性能模式
  • 关闭numa、C-states、C1E
  • 若有阵列卡,则使用FORCE WB策略,且关闭预读
  • 若使用机械盘,则所有盘组成RAID-10阵列
  • SSD盘可以只有两块组成RAID-1或三块做RAID-5
  • XFS/EXT4+deadine/noop
  • vm.swappiness=5 (可设置为0)
  • vm.dirty_ratio<=20 ,vm.dirty_background_ratio <;= 10

子查询改写join

https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653928676&idx=1&sn=c8d1f5f12f234738bdf6da844ab4994e#rd

MySQL配置文件申城地址

https://imysql.com/my-cnf-wizard.html

MySQL正式环境参数要求

  • root账号运行,不监听公网IP

说明:如有公网IP与内网IP地址,建议监听内网IP设置配置文件my.cnf加入:bind-address = IP

  • basedir/appdir、datadir/tokudbdir/rocksdbdir、backupdir、logdir

说明:备份文件basedir/appdir固定一个路径,剩余各自目录区分。

  • innodb_flush_log_at_trx_commit =1
  • sync_binlog=1

说明:保证数据安全性设置选择一定设置为:innodb_flush_log_at_trx_commit =1 &;& sync_binlog=1,单机环境设置同属一样设置。如半同步复制不需要设置。

  • innodb_file_per_table、innodb_data_file_path、innodb_io_capacity、调大redo表空间,使用独立undo空间

说明:独立表空间模式

  • innodb_thread_concurrency = 0

说明:并发线程池默认值为0,不建议设置。

  • log_query_time

说明:慢查询日志

发环境建议

  • 启用log_queries_not_using_indexes(log_throttle_queries_not_using_indexes)
  • 设置log_query_time为最小值
  • 定期检查分析slow log
  • 授权和生产环境一致
  • 关闭Query Cache
  • 设置较小innoDB Buffer Pool、key buffer size
  • 据量不能少,否则有些性能问题无法提前规避

业务配合

  • 批量导入、导出数据须提前通知DBA,请求协助观察
  • 扩广活动或上线新功能须提前通知DBA,请求压力评估
  • 不使用SUPER权限连接数据库
  • 单表多次alter操作必须合并为一次操作
  • 数据库DDL及重要SQL及早提交DBA评审
  • 重要业务库告知DBA重要等级,数据备份及时要求
  • 不在业务高峰期批量更新、查询数据库
  • 提交线上DDL需求,所有SQL语句须有备注说明

MySQL关键状态监控状态

  • Aborted_connects 连接表
  • Created_tmp_disk_tables 磁盘临时表
  • Created_tmp_tables 磁盘临时表
  • Handler_read_rnd 没有办法顺序读的请求
  • Handler_read_rnd_next 没有办法顺序读的请求
  • innodb_buffer_pool_wait_free
  • innodb_log_waits redolog位事件日志
  • innodb_row_lock_current_waits Innodb 当前行锁状态
  • Open_tables、Opened_tables 打开历表当前次数与打开历史表的次数
  • select_full_join 全表
  • select_scan 全表扫描
  • Sort_merge_passes 一次排序完不成需要多次排序完成
  • Table_locks_waited
  • Threads_cached(缓存多少)、Threads_connected(多少个连接)、Threads_created (创建多少个线程)Threads_running(当前运行多少个线程)

s

0 条回应