腾讯云学生服务器10元优惠套餐 腾讯云新客户无门槛领取2860元代金券 腾讯云十周年感恩回馈! 云耀云服务器年中钜惠! 爆款云产品,限时折扣
社区公告:
  • 吾爱程序猿,致力于打造专业优质的IT学习分享社区,请遵守法律法规和论坛规定,文明发帖,礼貌跟帖!
  • 分享学习资源,与社区用户共同提高,满足你的求知欲!学好IT更好地用技术改变世界!
    发新帖

    道森mysql提高数据库能力,数据库工程师15k不是梦

      [复制链接]
    133 25
    吾爱程序猿致力于打造专业优质的IT学习分享社区。站内所发布的一切文章、软件及附件信息全部来源于网络用户分享,仅限用于学习和研究目的,不得将上述内容用于商业或者非法用途。否则,一切后果请用户自负。
    本站仅提供学习分享平台,仅存储用户发布的图文内容和链接,不储存资源文件本身,版权争议与本站无关。如果您下载了未获授权资源,请在24个小时之内从您的电脑中删除,如果您喜欢该程序,请购买注册正版,得到更好的正版服务。
    站内所有言论纯属会员个人意见,与本论坛立场无关。严禁在本站发布政治、色情、暴力等不良信息和违法违规内容。
    吾爱程序猿作为网络平台提供者,对非法转载、盗版行为的发生不具备充分的监控能力。但是当版权拥有者提出侵权指控并出示充分的版权证明材料时,吾爱程序猿负有移除非法转载和盗版内容以及停止继续传播的义务。
    吾爱程序猿为用户免费分享产生,如发现本站内容存在版权问题,请联系客服或管理员,我们将第一时间核实处理。

    马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

    您需要 登录 才可以下载或查看,没有帐号?立即注册

    x
    本帖最后由 longhuanlong 于 2021-1-13 14:09 编辑
    资源简介链接见最下面

    一、前言

    MySQL 对于很多 Linux 从业者而言,是一个非常棘手的问题,多数情况都是因为对数据库出现问题的情况和处理思路不清晰。在进行 MySQL 的优化之前必须要了解的就是 MySQL 的查询过程,很多的查询优化工作实际上就是遵循一些原则让MySQL 的优化器能够按照预想的合理方式运行而已。

                                                              图 - MySQL查询过程

    二、优化的哲学

    注意:优化有风险,涉足需谨慎!

    1. 优化可能带来的问题
    • 优化不总是对一个单纯的环境进行,还很可能是一个复杂的已投产的系统。

    • 优化手段本来就有很大的风险,只不过你没能力意识到和预见到!

    • 任何的技术可以解决一个问题,但必然存在带来一个问题的风险!

    • 对于优化来说解决问题而带来的问题,控制在可接受的范围内才是有成果。

    • 保持现状或出现更差的情况都是失败!


    2. 优化的需求
    • 稳定性和业务可持续性,通常比性能更重要!

    • 优化不可避免涉及到变更,变更就有风险!

    • 优化使性能变好,维持和变差是等概率事件!

    • 切记优化,应该是各部门协同,共同参与的工作,任何单一部门都不能对数据库进行优化!

    • 所以优化工作,是由业务需要驱使的!!!


    3. 优化由谁参与

    在进行数据库优化时,应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等,业务相关人员共同参与。

    三、优化思路1. 优化什么

    在数据库优化上有两个主要方面:即安全与性能。

    • 安全 ---> 数据可持续性

    • 性能 ---> 数据的高性能访问


    2. 优化的范围有哪些

    存储、主机和操作系统方面:

    • 主机架构稳定性

    • I/O 规划及配置

    • Swap 交换分区

    • OS 内核参数和网络问题


    应用程序方面:

    • 应用程序稳定性

    • SQL 语句性能

    • 串行访问资源

    • 性能欠佳会话管理

    • 这个应用适不适合用 MySQL


    数据库优化方面:

    • 内存

    • 数据库结构(物理&逻辑)

    • 实例配置


    说明:不管是在设计系统,定位问题还是优化,都可以按照这个顺序执行。

    3. 优化维度

    数据库优化维度有四个:

    硬件、系统配置、数据库表结构、SQL 及索引。

    优化选择:

    • 优化成本:硬件>系统配置>数据库表结构>SQL 及索引

    • 优化效果:硬件<系统配置<数据库表结构<SQL 及索引


    四、优化工具有啥1. 数据库层面

    检查问题常用工具:

    不常用但好用的工具:

    2. 数据库层面问题解决思路

    一般应急调优的思路:

    针对突然的业务办理卡顿,无法进行正常的业务处理!需要立马解决的场景!

    • 1、show processlist

    • 2、explain select id ,name from stu where name='clsn'; # ALL id name age sex
      select id,name from stu where id=2-1 函数 结果集>30;
      show index from table;

    • 3、通过执行计划判断,索引问题(有没有、合不合理)或者语句本身问题

    • 4、show status like '%lock%'; # 查询锁状态
      kill SESSION_ID; # 杀掉有问题的session


    常规调优思路:

    针对业务周期性的卡顿,例如在每天 10-11 点业务特别慢,但是还能够使用,过了这段时间就好了。

    • 1、查看 slowlog,分析 slowlog,分析出查询慢的语句。

    • 2、按照一定优先级,进行一个一个的排查所有慢语句。

    • 3、分析 top sql,进行 explain 调试,查看语句执行时间。

    • 4、调整索引或语句本身。


    3. 系统层面

    cpu方面:

    vmstat、sar top、htop、nmon、mpstat

    内存:

    free 、ps -aux 、

    IO 设备(磁盘、网络):

    iostat 、 ss 、 netstat 、 iptraf、iftop、lsof、

    vmstat 命令说明:

    • Procs:r 显示有多少进程正在等待 CPU 时间。b 显示处于不可中断的休眠的进程数量。在等待 I/O

    • Memory:swpd 显示被交换到磁盘的数据块的数量。未被使用的数据块,用户缓冲数据块,用于操作系统的数据块的数量

    • Swap:操作系统每秒从磁盘上交换到内存和从内存交换到磁盘的数据块的数量。s1 和 s0 最好是 0

    • Io:每秒从设备中读入 b1 的写入到设备 b0 的数据块的数量。反映了磁盘 I/O

    • System:显示了每秒发生中断的数量(in)和上下文交换(cs)的数量

    • Cpu:显示用于运行用户代码,系统代码,空闲,等待 I/O 的 CPU 时间


    iostat 命令说明

    实例命令: iostat -dk 1 5

    iostat -d -k -x 5 (查看设备使用率(%util)和响应时间(await))

    • tps:该设备每秒的传输次数。“一次传输”意思是“一次 I/O 请求”。多个逻辑请求可能会被合并为“一次 I/O 请求”。

    • iops :硬件出厂的时候,厂家定义的一个每秒最大的 IO 次数,"一次传输"请求的大小是未知的。

    • kB_read/s:每秒从设备(drive expressed)读取的数据量;

    • KB_wrtn/s:每秒向设备(drive expressed)写入的数据量;

    • kB_read:读取的总数据量;

    • kB_wrtn:写入的总数量数据量;这些单位都为 Kilobytes。


    4. 系统层面问题解决办法

    你认为到底负载高好,还是低好呢?

    在实际的生产中,一般认为 cpu 只要不超过 90% 都没什么问题 。

    当然不排除下面这些特殊情况:

    问题一:cpu 负载高,IO 负载低

    • 内存不够

    • 磁盘性能差

    • SQL 问题 ------>去数据库层,进一步排查 sql 问题

    • IO 出问题了(磁盘到临界了、raid 设计不好、raid 降级、锁、在单位时间内 tps 过高)

    • tps 过高: 大量的小数据 IO、大量的全表扫描


    问题二:IO 负载高,cpu 负载低

    • 大量小的 IO 写操作:

    • autocommit ,产生大量小 IO

    • IO/PS 磁盘的一个定值,硬件出厂的时候,厂家定义的一个每秒最大的 IO 次数。

    • 大量大的 IO 写操作

    • SQL 问题的几率比较大


    问题三:IO 和 cpu 负载都很高

    硬件不够了或 SQL 存在问题

    五、基础优化1. 优化思路

    定位问题点:

    硬件 --> 系统 --> 应用 --> 数据库 --> 架构(高可用、读写分离、分库分表)

    处理方向:

    明确优化目标、性能和安全的折中、防患未然

    2. 硬件优化

    主机方面:

    • 根据数据库类型,主机 CPU 选择、内存容量选择、磁盘选择

    • 平衡内存和磁盘资源

    • 随机的 I/O 和顺序的 I/O

    • 主机 RAID 卡的 BBU(Battery Backup Unit)关闭


    cpu 的选择:

    • cpu 的两个关键因素:核数、主频

    • 根据不同的业务类型进行选择:

    • cpu 密集型:计算比较多,OLTP 主频很高的 cpu、核数还要多

    • IO 密集型:查询比较,OLAP 核数要多,主频不一定高的


    内存的选择:

    • OLAP 类型数据库,需要更多内存,和数据获取量级有关。

    • OLTP 类型数据一般内存是 cpu 核心数量的 2 倍到 4 倍,没有最佳实践。


    存储方面:

    • 根据存储数据种类的不同,选择不同的存储设备

    • 配置合理的 RAID 级别(raid 5、raid 10、热备盘)

    • 对与操作系统来讲,不需要太特殊的选择,最好做好冗余(raid1)(ssd、sas 、sata)


    raid 卡:主机 raid 卡选择:

    • 实现操作系统磁盘的冗余(raid1)

    • 平衡内存和磁盘资源

    • 随机的 I/O 和顺序的 I/O

    • 主机 RAID 卡的 BBU(Battery Backup Unit)要关闭


    网络设备方面:

    使用流量支持更高的网络设备(交换机、路由器、网线、网卡、HBA 卡)

    注意:以上这些规划应该在初始设计系统时就应该考虑好。

    3. 服务器硬件优化
    • 1、物理状态灯:

    • 2、自带管理设备:远程控制卡(FENCE 设备:ipmi ilo idarc),开关机、硬件监控。

    • 3、第三方的监控软件、设备(snmp、agent)对物理设施进行监控

    • 4、存储设备:自带的监控平台。EMC2(hp 收购了), 日立(hds),IBM 低端 OEM hds,高端存储是自己技术,华为存储


    4. 系统优化

    Cpu:

    基本不需要调整,在硬件选择方面下功夫即可。

    内存:

    基本不需要调整,在硬件选择方面下功夫即可。

    SWAP:

    MySQL 尽量避免使用 swap。阿里云的服务器中默认 swap 为 0

    IO :

    • raid、no lvm、 ext4 或 xfs、ssd、IO 调度策略

    • Swap 调整(不使用 swap 分区)


    这个参数决定了 Linux 是倾向于使用 swap,还是倾向于释放文件系统 cache。在内存紧张的情况下,数值越低越倾向于释放文件系统 cache。当然,这个参数只能减少使用 swap 的概率,并不能避免 Linux 使用 swap。

    修改 MySQL 的配置参数 innodb_flush_method,开启 O_DIRECT 模式。这种情况下,InnoDB 的 buffer pool 会直接绕过文件系统 cache 来访问磁盘,但是redo log 依旧会使用文件系统 cache。值得注意的是,Redo log 是覆写模式的,即使使用了文件系统的 cache,也不会占用太多

    IO 调度策略:

    5. 系统参数调整

    Linux 系统内核参数优化:

    用户限制参数(MySQL 可以不设置以下配置):

    6. 应用优化

    业务应用和数据库应用独立,防火墙:iptables、selinux 等其他无用服务(关闭):

    安装图形界面的服务器不要启动图形界面 runlevel 3,另外,思考将来我们的业务是否真的需要MySQL,还是使用其他种类的数据库。用数据库的最高境界就是不用数据库。

    六、数据库优化

    SQL 优化方向:

    执行计划、索引、SQL 改写

    架构优化方向:

    高可用架构、高性能架构、分库分表

    1. 数据库参数优化

    调整:

    实例整体(高级优化,扩展)

    连接层(基础优化)

    设置合理的连接客户和连接方式

    SQL 层(基础优化)

    • query_cache_size: 查询缓存

    • OLAP 类型数据库,需要重点加大此内存缓存

    • 但是一般不会超过 GB

    • 对于经常被修改的数据,缓存会立马失效

    • 我们可以实用内存数据库(redis、memecache),替代他的功能


    2.存储引擎层(innodb 基础优化参数)


    转载于:https://my.oschina.net/JKOPERA/blog/2246927

    游客,如果您要查看本帖隐藏内容请 回复




    举报 使用道具

    回复

    精彩评论25

    emlod   发表于 2021-1-13 23:03:04 | 显示全部楼层
    道森mysql提高数据库能力,数据库工程师15k不是梦

    举报 使用道具

    回复 支持 反对
    xiaod   发表于 2021-1-14 00:22:45 | 显示全部楼层
    道森mysql提高数据库能力,数据库工程师15k不是梦

    举报 使用道具

    回复 支持 反对
    996   发表于 2021-1-14 07:26:21 | 显示全部楼层
    的杀杀杀杀杀杀杀杀杀杀杀杀杀杀杀杀杀杀

    举报 使用道具

    回复 支持 反对
    kiveye   发表于 2021-1-14 09:14:23 | 显示全部楼层
    非常感谢楼主,楼主辛苦了,谢谢

    举报 使用道具

    回复 支持 反对
    lkliyou   发表于 2021-1-14 11:13:20 | 显示全部楼层
    看一看 瞧一瞧

    举报 使用道具

    回复 支持 反对
    bawfnl   发表于 2021-1-14 12:06:47 | 显示全部楼层
    RE: 道森mysql提高数据库能力,数据库工程师15k不是梦 [修改]

    举报 使用道具

    回复 支持 反对
    438456737   发表于 2021-1-14 18:26:42 | 显示全部楼层

    举报 使用道具

    回复
    xxydxx123   发表于 2021-1-14 23:01:16 | 显示全部楼层
    道森mysql提高数据库能力,数据库工程师15k不是梦

    举报 使用道具

    回复 支持 反对
    ilove2015   发表于 2021-1-16 02:38:30 | 显示全部楼层
    啥也不说了,吾爱程序猿论坛的教程就是给力!

    举报 使用道具

    回复 支持 反对
    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则



    快速回复 返回顶部 返回列表