如何构建高性能MySQL系统!

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

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

wKioL1ldmGryXYxkAAenwapr22E548.jpg-wh_50

wKiom1ldmG_Awwp7AAiQZZMnBSk430.jpg-wh_50




































0?tp=webp&wxfrom=5&wx_lazy=1硬件0?tp=webp&wxfrom=5&wx_lazy=1


(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

0?tp=webp&wxfrom=5&wx_lazy=1操作系统

(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

          内存最大性能模式

0?tp=webp&wxfrom=5&wx_lazy=1文件系统0?tp=webp&wxfrom=5&wx_lazy=1

           确保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

0?tp=webp&wxfrom=5&wx_lazy=1MySQL0?tp=webp&wxfrom=5&wx_lazy=1

        (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越高设置可以设置的越大

       推荐文章 www.cnblogs.com/conanwang/p/5849437.html

内存分配

  • 策略:

        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(*),表示字段不重复的比例,比例越大我们扫描的记录数越少


推荐文章:

  • MySQL索引原理及慢查询优化

    http://tech.meituan.com/mysql-index.html

  • MySQL索引实践

    http://blog.coderland.net/mysql/2015/08/26/MySQL%E7%B4%A2%E5%BC%95%E5%AE%9E%E8%B7%B5/

  • 由浅入深探究 MySQL索引结构原理、性能分析与优化

    http://blog.jobbole.com/87107/

SQL开发优化

  • 不使用存储过程、触发器,自定义函数

  • 不使用全文索引

  • 不使用分区表

  • 针对OTLP业务尽量避免使用多表join和子查询

  • 不使用*,SELECT使用具体的列名:在发生列的增/删时,发生列名修改时,最大限度避免程序逻辑中没有修改导致的BUG,IN的元素个数300-500

  • 避免使用大事务,使用短小的事务:减少锁等待和竞争

  • 禁止使用%前缀模糊查询 where like ‘%xxx’

  • 禁止使用子查询,遇到使用子查询的情况,尽量使用join代替

  • 遇到分页查询,使用延迟关联解决:分页如果有大offset,可以先取Id,然后用主键id关联表会提高效率

  • 禁止并发执行count(*),并发导致CPU飙高

  • 禁止使order by rand()

  • 不使用负向查询,如 not in/like,使用in反向代替

  • 不要一次更新大量(大于30000条)数据,批量更新/删除

  • SQL中使用到OR的改写为用 IN() (or的效率没有in的效率高)

0?tp=webp&wxfrom=5&wx_lazy=1数据库架构0?tp=webp&wxfrom=5&wx_lazy=1

  • 单实例无法解决空间和性能需求时考虑拆分

  • 垂直拆分

  • 水平拆分

  • 引入缓存系统


转自:前阿里数据库团队资深DBA------->杨奇龙



本文转自 boy461205160 51CTO博客,原文链接:http://blog.51cto.com/461205160/1944905

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1天前
|
存储 SQL 关系型数据库
MySQL 系统文件你了解吗
MySQL 系统文件你了解吗
14 0
|
14天前
|
关系型数据库 MySQL 数据库
MySQL技能完整学习列表——1、数据库基础概念——2、数据库管理系统(DBMS)——3、数据模型(Data Model)
MySQL技能完整学习列表——1、数据库基础概念——2、数据库管理系统(DBMS)——3、数据模型(Data Model)
14 0
|
15天前
|
关系型数据库 MySQL 数据安全/隐私保护
MySQL8.0绿色版本——最纯净的安装体验(免去卸载不掉只能重做系统的‘囧‘境)
MySQL8.0绿色版本——最纯净的安装体验(免去卸载不掉只能重做系统的‘囧‘境)
50 0
|
16天前
|
关系型数据库 MySQL Java
MySQL - 系统是如何跟MySQL打交道的
MySQL - 系统是如何跟MySQL打交道的
|
16天前
|
关系型数据库 MySQL Linux
docker构建mysql以及多实例
docker构建mysql以及多实例
11 0
|
20天前
|
存储 关系型数据库 MySQL
《高性能Mysql》学习笔记(二)
《高性能Mysql》学习笔记(二)
115 0
|
20天前
|
存储 SQL 关系型数据库
《高性能Mysql》学习笔记(一)
《高性能Mysql》学习笔记(一)
83 0
|
1月前
|
消息中间件 NoSQL 关系型数据库
在秒杀系统中redis的数据和mysql不一致了,要怎么检查出来了(概述)
在秒杀系统中redis的数据和mysql不一致了,要怎么检查出来了(概述)
17 0
|
1月前
|
分布式计算 DataWorks 关系型数据库
在云数据仓库AnalyticDB MySQL版中,LIMIT的大小是由系统参数max_limit控制的
【1月更文挑战第7天】【1月更文挑战第31篇】在云数据仓库AnalyticDB MySQL版中,LIMIT的大小是由系统参数max_limit控制的
18 1
|
1月前
|
关系型数据库 MySQL 数据安全/隐私保护
使用Docker构建本地Mysql容器及配置
使用Docker构建本地Mysql容器及配置
28 1