如何构建高性能MySQL系统！

2022-01-24 08:14:44

构建高性能MySQL系统涵盖从单机、硬件、OS、文件系统、内存到MySQL 本身的配置，以及schema 设计、索引设计，再到数据库架构上的水平和垂直拓展。

硬件

（1）CPU

CPU亲和性：

确保每个io都被其发起的CPU处理

echo 2 > /sys/block/<block device>/queue/rq_affinity

择最大性能模式，避免节能模式导致性能不足

关闭NUMA，降低swap概率

numactl --interleave=all

（2）RAID卡

选择FORCE WB读写策略

选择合适的充放电策略

高IO，推荐RAID10

空间需求大则RAID5

操作系统

（1）IO调度策略

SSD/PCIE SSD推荐noop，其它推荐deadline

echo noop > /sys/block/<block device>/queue/scheduler

（2）禁用块设备轮转模式

echo 0 > /sys/block/<block device>/queue/rotational

（3）内存

vm.swappiness=0

内存最大性能模式

文件系统

确保4K对，如果使用全盘一个分区，例如mkfs.ext4 /dev/dfa也可以使用xfs 构建文件系统。

禁止atime、diratime

mount -o noatime -o nodiratime

开启trim

mount -o discard

关闭barrier

mount -o barrier=0

/dev/sdc1 /data ext4 defaults,noatime,nodiratime,nobarrier 0 0

MySQL

（1）配置优化

IO相关参数

innodb_flush_method = O_DIRECT

innodb_read_io_threads = 16

innodb_write_io_threads = 16

innodb_io_capacity = 3000（PCIE卡建议更高）

innodb_flush_neighbors=0

InnoDB存储引擎在刷新一个脏页时，会检测该页所在区(extent)的所有页，如果是脏页，那么一起刷新。这样做的好处是通过AIO可以将多个IO写操作合并为一个IO操作。对于传统机械硬盘建议使用，而对于固态硬盘可以关闭

innodb_flush_log_at_trx_commit

redo 的刷盘策略

sync_binlog

binlog 的刷盘策略

innodb_log_buffer_size

建议8-16M，有高TPS（比如大于6k）的可以提高到32M，系统tps越高设置可以设置的越大

内存分配

策略：

jemalloc是BSD的提供的内存分配管理

tcmalloc是google的内存分配管理模块

ptmalloc是glibc的内存分配管理

malloc-lib= /usr/lib64/libjemalloc.so.1

系统资源：

malloc-lib= /usr/lib64/libjemalloc.so.1

back_log：大于max_connections

thread_stack=192

并发控制：

使用thread_pool

thread_cache_size

（2）schema优化

索引优化

目标：利用最小的索引成本找到最需要的行记录。

原则：

最左前缀原则：MySQL会一直向右匹配直到遇到范围查询
between、like)就停止匹配，比如a=1 and b=2 and c>3 and d=4 如果建立(a,b,c,d) 顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整
避免重复索引：idx_abc多列索引,相当于创建了(a)单列索引，(a,b)组合索引以及(a,b,c)组合索引。不在索引列使用函数如 max(id)> 10 ,id+1>3 等
尽量选择区分度高的列作为前缀索引：区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少

码农公寓

相关文章