• 硬件方面

  • 操作系统

  • MySQL版本

  • MySQL配置

  • 开发方面

  • 运维事项

  • 安全

  • 总结

硬件方面

  • BIOS

    • 关闭NUMA

    • 关闭节能模式

    • Raid卡强制Force WriteBack （WB）

  • CPU

    • 高主频VS多核

  • MEM

    • 内存越大越好

  • HDD VS SSD

    • 15000转的SAS 145个IOPS

    • PCI-E SSD 10万IOPS起步

BIOS

  • CPU设置最大性能模式，关闭C1E，C-stats

  • 内存设置最大性能模式

  • 关闭NUMA，5.6.27以上的版本，新增选项innodb_numa_interleave(innodb_numa_interleave在5.7以后需要源码编译，才会有这个选项)

RAID配置优化

  • RAID-10

  • CACHE & BBU

  • WB & FORCE WB

  • 使用PCIe-SSD等高速I/O设备

CPU

  • sysstat工具集

    • sar、pidstat、mpstat、iostat、vmstat

  • perf top -p $pid

    • 实时显示系统/进程的性能统计信息

  • pt-ioprofile

    • mysql中的iotop

  • pstack

    • 当mysql里有线程hang住时，利用pstack排查由于哪些函数调用存在问题

系统（推荐centos ubuntu）

  • 系统限制

    • 文件句柄

    • 进程数

    • 禁selinux

  • 文件系统

    • 文件分区格式

    • IO调度

    • 目录结构

  • 网络配置

    • 禁ipv6

    • iptables

    • 网络参数

  • 时区&字符集

    • 时区

    • 终端字符集

系统限制

  • 默认：1024 open files,4096 max user processes

  • /etc/securiry/limits.conf

增大这些值

  • 禁止selinux

    • /etc/sysconfig/selinux

    • SELINUX=disabled

文件系统

  • io scheduler

    • deadline/noop

  • filesystems

    • xfs

    • ext4

  • 别用NFS放置datadir

  • kernel

    • vm.swappiness = 1~5（rhel7以后，设置0要慎重）

    • vm.dirty_background_ratio = 5

    • vm.dirty_ratio = 10

经过测试，deadline/noop性能是默认的cfq的50%~100%

网络配置

对于短连接应用

  • /etc/sysctl.conf

net.ipv4.tcp_max_syn_backlog = 819200
net.core.netdev_max_backlog = 40000
net.core.somaxconn = 4096
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=0

  • hostname

/etc/hosts

时区&字符集

  • MySQL使用系统的timezone设置产生时间

  • 主从系统时区必须一致

  • 建议更改mysql的配置：time_zone=’+8:00’

  • 系统字符集一致

    • locale

MySQL版本

  • 目录结构

  • 版本选择

  • 单实例VS多实例

目录结构

  • basedir:/usr/local/mysql

  • 其他目录：/data/mysql/mysql$port/{data,tmp,logs,my$port.cnf}

  • datadir:/data/mysql/mysql$port/data

  • 标准化

  • 方便自动化

  • /data挂载点独立分区

版本选择

  • CentOS/Redhat:

    • yum install mysql-server

    • yum install mysql-client

  • 前列推荐

    • 下载二进制统一安装，统一版本

    • 构建自己的yum源，统一版本

  • MySQL/MariaDB/Percona

  • 选择新版本

    • changelog阅读

    • 新特性引入

  • MySQL5.7特性图

【MySQL】MySQL最佳实践32条 - 图1

单实例VS多实例

  • 私有业务&云平台RDS化业务

  • 虚拟化和非虚拟化

  • 资源分配对齐

MySQL配置

  • 对于配置优化是不是有作用，最好的办法就是压力测试测一下，当然调优的基础也需要理论来支撑。

Innodb buffer Pool

  • 推荐50~80%的内存

  • Innodb Buffer Pool中含：data+index

  • 可以通过增大buffer pool减少磁盘的IO

  • show engine innodb status\G

  • Buffer pool hit rate:999/1000

  • innodb_buffer_pool_size=50%~80%内存

  • innodb_buffer_pool_instance=4-8

连接数

  • max_connections

  • max_user_connections

  • MySQL默认给Super权限的账号保留一个连接，支持的最大连接数：max_connections+1

  • gdb -p pidof mysqld -ex “set max_connections=2048” —batch

    • 提示：

      • wait_timeout default:28800

      • interactive_timeout default:28800

放弃MyISAM

  • 只能使用一个Core CPU

  • 内存最大支持4G，只能缓存索引

  • 不支持同事并发读和更新

  • 无事物支持，表容易Crash，可修复，丢数据

  • 并发能力弱

复制类型

复制类型 说明 适用
传统复制
binlog row+position		 版本MySQL 5.5以下 有点老了，可以考虑退出
GTID复制
binlog row+GTID		 版本MySQL 5.6以上 跨IDC，对复制要求不严格环境
半同步
（GTID）		 MySQL 5.5不支持GTID
MySQL 5.6支持GTID		 不推荐，这两个版本存在性能问题
MySQL 5.7可用
增强半同步
(GTID)		 MySQL 5.7 推荐，金融环境，同IDC，同城
多源复制
（GTID）		 MySQL 5.7 OLAP节点，延迟复制

开发方面

  • 索引/主键选择

  • Many Tables

  • 容量规划

  • Bad Query

索引&主键选择

  • Innodb以主键排序存储

  • 普通索引包含主键存储

  • UUID目前不是顺序增长，作为主键写入导致随机IO严重，8.0可以使用UUID做主键，8.0以后UUID是顺序增长

  • 联合索引性能优于独立索引

  • 区别度低的字段，在联合索引中效果不错

  • 聚集索引选择顺序

    • 显示主键

    • 第一个非NULL唯一索引

    • 6字节 rowid

Many Tables

  • 每个DB是一个数据库目录

  • Innodb每个表包含两个文件（.frm+.ibd）

  • 某论坛将近200张表，共享系统安装1000个，将近200000表

  • 访问information_schema相当于打开了一次所有的表

  • 物理文件系统访问非常慢

  • 推荐：

    • 每个实例最多1万张表

    • 最佳实践单实例小于500张表

容量规划

  • 资源对齐分配

  • 单实例最大1T

  • 单机多实例，规范管理

  • 不是DB放不下，只是数据库大了，对于核心库的维护备份恢复上太难处理，开发的同学，也要想想怎么维护

Bad Query

  • 用不到索引

  • 一次读取过多记录

  • like “%zst%”

  • join列无索引

  • 监控慢查询

    • pt-query-digest

    • Anemometer

  • 干掉垃圾查询

    • pt-kill

    • 优化

  • Cache

    • MySQL +NoSQL

运维注意事项

  • alter table

  • MySQL大表

  • 复制延迟

  • 变量动态修改

  • 其他

Alter table

  • 直接alter table容易锁表

  • 高并发系统直接alter table易出现拿不到锁，导致server Crash

  • MySQL5.6支持在线online ddl，但容易造成复制延迟

  • 对于alter table操作，请查看每个版本，确认只是更改字典信息无copy可以在线操作

  • 拿不准情况推荐：pt-osc

MySQL大表

  • delete,update大表，分批段进行

  • 更改Session会话binlog_format=’statement’

  • 大表删除可以用硬链接的方式处理

复制延迟

  • 复制中sql_thread单线程工作

  • 大的delete/update/alter会让复制延迟比较长

  • MySQL5.6支持基于库级别的并行复制

  • MySQL5.7支持基于事务级的并行复制（group commit）

  • show slave status\G;

  • mysqlbinlog -v —base64-output=decode-rows —start-position=exec_master_log_po relay_master_log_file

变量动态修改

  • set global innodb_buffer_pool_size=N;

  • set join_buffer_size=410241024；

  • set …

重启，更改丢失
会话变量，连接不Close会影响其他会话

其他注意-过多的Slave

  • Master在Binlog中记录所有的变更

  • 所有的Slave需要拉取全量的Binlog

  • 过多的Slave对主的网络及IO压力较大

  • 如：每分100M写入，20个Slave

  • 20*100 ->2GB/分 -> 33.33M/S

MySQL安全

  • 用户名&密码

  • 权限分配

  • 网络监听

  • load data

  • 备份&恢复

用户名和密码

  • 低于MySQL 5.7的版本删除匿名账号和泛授权

    • delete from mysql.user where user=’’ or host!=’localhost’;

    • drop database test;truncate table mysql.db;flush privileges;

  • 不要使用root做业务账号

  • 不要使用无密码账号做业务账号

  • 合理控制权限，不要把用户名和密码写到程序中

  • 创建用户名 长度8-16个字符，密码8-32个字符

    • 避免使用关键词做用户名和密码

权限分配

  • DB系统包含：

    • 业务账号

      • delete,update,insert,select,excute

    • 监控账号

      • replication client,show

    • 业务平台更新账号

      • create,drop,含业务账户

    • 管理账号(with grant option)

      • 创建用户，权限管理相关

网络监听

  • 禁ipv6,iptables

  • 只监听内网

load data

  • MySQL5.6以下版本
  1. mysql>LOAD DATA LOCAL INFILE '/tmp/shadow' INTO TABLE `text`(data);
  • MySQL 5.7及以上版本
  1. mysql>LOAD DATA INFILE '/tmp/shadow' INTO TABLE `text`(data);

备份恢复

  • 逻辑备份

    • mysqldump

    • mydumper

  • 在线备份

    • xtrabackup

  • Point-to-Point备份恢复

    • binlog + 复制

  • 快速单表恢复

  • 快速增量应用

脑图
【MySQL】MySQL最佳实践32条 - 图2