mysql优化32条

一、硬件方面

1.1 BIOS

• 关闭NUMA

• 关闭节能模式

• RAID卡强制force write back(WB)

 

 

 

 

 

 

 

 

1.2 CPU

• 高主频VS多核

 

 

 

1.3 MEM

• 内存越大越好

1.4 HDD VS SSD

• 15000转的SAS 145个IOPS

• PCI-E SSD 10万IOPS起步

 

二、操作系统

CENTOS，ubuntu，选择通用性强的，内核高版本，性能好，令外oracle UEK内核也是一个不错的选择。

 

 

 

2.1 系统限制

• 默认：1024 open files, 4096 max user processes ： /etc/security/limits.conf 增大这些值

• 禁止selinux ： /etc/sysconfig/selinux SELINUX=disabled

2.2 文件系统

2.2.1 io scheduler

• deadline/noop

 

 

 

 

 

 

 

2.2.2 filesystems

• xfs

• ext4

• 别用NFS放置datadir

2.2.3 kernel

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

• vm.dirty_background_ratio = 5

• vm.dirty_ratio = 10

2.2.4 网络配置

•/etc/sysctl.conf： net.ipv4.tcp_max_syn_backlog = 819200 net.core.netdev_max_backlog = 400000 net.core.somaxconn = 4096 net.ipv4.tcp_tw_reuse=1 net.ipv4.tcp_tw_recycle=0

•hostname： /etc/hosts

 

2.2.5 时区和字符集

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

• 主从系统时区必须一致。

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

• 系统字符集一致

  

   

   

 

三、mysql版本

线上环最好统一版本， 统一操作系统，减少环境差异带来的不不必要的麻烦。

3.1 目录结构

• basedir : /usr/local/mysql

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

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

• 标准化

• 方便便自动化

• /data挂载点独立分区

 

3.2 版本的选择

3.2.1 强烈烈推荐

• 下载二进制安统安装，统一版本 • 构建自已的yum安装，统一版本

3.2.2 版本选择

MySQL/MariaDB/Percona

 

 

 

3.2.3 单实例VS多实例

 

 

 

四、开发方面

4.1 索引和主键的选择

• Innodb以主键排序存储

• 普通索引包含主键存储

• UUID 目前不不是顺序增⻓长，做为主键写入导致，随机IO严重

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

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

 

 

 

4.2 Many Tables(5.7)

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

• Innodb每个表包含两个文件(.frm+.ibd)

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

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

• 物理理⽂文件系统访问非常慢 推荐： 每个实例例最多1万张表 最佳实践单实例例小于500张表

4.3 容量规划

• 资源对齐分配！！！

• 单实例例最大1T

• 单机多实例例，规范管理

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

4.4 Bad Query

• 监控慢查询： pt-query-digest 、Anemometer

• 干掉垃圾查询 ：pt-kill 、优化

五、运维

5.1 alter table

• 直接alter table容易易锁表

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

• MySQL 5.6 支持在线的online ddl，但容易易造成复制延迟

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

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

5.2 mysql大表

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

• 更更改Session会话binlog_format='statement'

• 大表删除

5.3 复制延迟

 

 

 

5.4 过多的slave

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

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

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

•如： 每分100M写入，20个Slave 20*100 ->2GB/分 -> 33.33M/S

 

5.5 用户名和密码

5.5.1 权限分配

 

 

 

5.5.2 权限分配

 

 

 

5.6 网络监听

• 禁ipv6，iptables

• 只监听内网

5.7 load data

5.7.1 mysql5.6 load data

• MySQL 5.6及以下版本

mysql> LOAD DATA LOCAL INFILE '/etc/shadow' INTO TABLE `text` (data);
Query OK, 27 rows affected (0.00 sec)

• MySQL 5.7及以上版本

load data infile '/etc/passwd' into table t1(t);

5.8 备份恢复

5.8.1 逻辑备份

• mysqldump

• mydumper

5.8.2 在线热备

• xtrabackup

5.8.3 Point-to-Point备恢复

• binlog + 复制

5.8.4 快速单表恢复

5.8.5 快速增量应用