《MySQL排错指南》——4.1 物理硬件限制

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

本节书摘来自异步社区出版社《MySQL排错指南》一书中的第4章,第4.1节,作者:【美】Sveta Smirnova(斯维特 斯米尔诺娃),更多章节内容可以访问云栖社区“异步社区”公众号查看。

4.1 物理硬件限制

对性能有不切实际的期望是通常会犯的错。我们要求MySQL服务器在忽略硬件组件延迟的情况下进行优化。因此,理解什么导致了延迟很重要。

下面列出了影响MySQL服务器的硬件资源:

内存
CPU
内核数量
磁盘I/O
网络带宽
我们依次讨论每个细节。

4.1.1 内存

内存对于MySQL是非常宝贵的资源。服务器在没有磁盘交换的情况下运行很快。理想情况是数据驻留在内存中。因此,在物理内存的限制内合理配置缓冲区是非常重要的。关于这一点,1.6.4小节和3.9.3小节提供了更多详细的介绍和指导。

提示 提示

在Linux/UNIX上可以通过vmstat,或者Windows上的任务管理器来查看mysqld是否有大量磁盘交换。

这里列出Linux上一个磁盘交换的例子。重要的部分加粗显示。对于没有磁盘交换的服务器,这些值应该为0:
image

相关章节讨论了影响内存使用的配置变量。首先计算出MySQL服务器需要用到的最大内存量,基本原则是要保证它小于你的物理内存。当缓冲区大于实际内存时会增加MySQL服务器因为“内存不足”错误而崩溃的风险。

前面的观点可以换一种陈述方式:如果你需要更大的缓冲区,则需要购买更多内存。扩展应用程序时这始终是一个很好的做法。
使用的内存模块支持扩展错误校正(EEC),这样即使内存有一些损坏,MySQL服务器也不会因此崩溃。
关于内存使用的更多内容,可以参考MySQL参考手册中的“MySQL如何使用内存”章节。我就不在此处重复,因为它不涉及任何新排错技巧。

很重要的一点是,当查询的行包含BLOB字段时,内部缓冲区会增长到能存储该值的点,并且存储引擎在查询结束后不会释放内存。需要执行FLUSH TABLE来释放内存。

另一个要点涉及32位和64位架构的不同。虽然32位使用较小的指针大小可以节约内存,但由于操作系统的寻址受限,它在缓冲区大小设置上存在固有的约束。理论上,每个进程在32位系统上的最大可用内存为4GB,实际上在很多系统上这个数值很小。所以如果你想使用的缓冲区在32位系统上超过了这个值,考虑转换到64位架构下。

4.1.2 处理器与内核

MySQL的性能不会随着CPU的速度线性增长。这并不意味着你不能使用一块快的CPU,但不要期望提高CPU速度能像增加内存那样性能会有大幅提升。

然而,当设置一些影响内部线程并发的选项时,内核的数量很重要。如果你没有足够的内核数增加这些值是没有任何意义的。通过使用一款名叫sysbench[1]的基准工具能够轻松说明问题。表4-1显示了在四核机器上的一个测试结果。我使用具有16个线程的OLTP sysbench测试。

表4-1 不同innodb_thread_concurrency值下执行事件消耗的时间(s)

image

如你所见,在启动8个线程之前测试越运行越快,但随着数值的增加停止了增长。

4.1.3 磁盘I/O

快速磁盘对MySQL性能很重要。磁盘越快,I/O性能越好。

关于磁盘,你需要关注磁盘读延迟(每次读访问需要多长时间)和fsync延迟(每个fsync耗时多少)。

近来固态硬盘(SSD)的性能很好,但不要指望在上面出现奇迹,因为大多数存储引擎是针对硬盘读写优化的。

同样网络存储也是。可以将数据和日志文件放在网络文件系统或者存储上,但这些装置可能会慢于本地磁盘。你需要检查存储是否快速并且可靠,否则,如果由于网络故障引起数据丢失请不要感到意外。

在Linux/UNIX上使用iostat来判断磁盘I/O是否过载。向设备发起的请求平均队列长度正常情况下不会很高。出于同样的目的,你可以在Windows平台上使用perfmon。下面给出iostat的一个输出例子:

image

以上输出取自mysqld空闲并随后启动一个活跃的I/O作业时。你可以看到avgqu-sz是如何增长的。虽然还不至于带来问题,但我决定在这里通过这个例子来说明当mysqld正在处理它的作业时磁盘I/O活动是如何发生变化的。

除了速度之外,要牢记的是,存储也可能会丢失数据,可能出现只写入一部分页的情况。如果使用InnoDB,为了数据安全通过设置innodb_double_write使用双写缓冲区。同样,为磁盘准备备用电池是很重要的,它可能防止因为电源故障导致的数据丢失。

4.1.4 网络带宽

客户端几乎总是通过网络连接MySQL到服务器的,所以MySQL服务器运行在一个快速的网络环境下是很重要的。

除了网络带宽之外,往返时间(RTT)和往返次数也很重要。RTT指的是客户端发送网络包到服务端返回应答所消耗的时间。主机间隔的距离越长,RTT越高。

也正是因为存在网络带宽和RTT这些原因,如果可以,推荐将客户端和服务器放在同一个本地网络中。

复制也推荐在本地网络中进行。连接到从服务器时可以使用因特网来替代本地内联网,但预期会出现延迟,甚至中继日志数据损坏带来的报错。上面的错误应该在bug #26489 bug修复之后会自动修复,前提是你在5.5版本中配置了relay-log-recovery选项,或者从5.6版本开始使用二进制日志校验和。但由于网络故障主从服务器之间仍然会在重发包上消耗时间。

4.1.5 延迟效应的例子

为了结束本章内容,我用一个小例子来说明硬件延迟是如何影响一条普通的UPDATE查询的。我们使用InnoDB表并将自动提交打开,同时打开二进制日志文件。

image

这个简单的查询会在下面的情况下遭遇延迟:

  客户端发送命令到服务端历经半个RTT。

  执行UPDATE的WHERE子句,mysqld读磁盘。

  由于自动提交开启mysqld会对此事务做一个fsync调用。

  为写入二进制日志文件Mysqld做一次fsync调用。

  为提交改变mysqld做一次fsync调用。

  客户端收到来自服务器的结果,这是RTT的另一半。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
24天前
|
监控 关系型数据库 MySQL
MySQL技能完整学习列表12、性能优化——1、性能指标和监控——2、优化查询和数据库结构——3、硬件和配置优化
MySQL技能完整学习列表12、性能优化——1、性能指标和监控——2、优化查询和数据库结构——3、硬件和配置优化
77 0
|
3月前
|
存储 SQL 关系型数据库
MySQL之深入InnoDB存储引擎——物理文件
一、参数文件 当 MySQL 实例启动时,数据库会先去读一个配置参数文件,用来寻找数据库的各种文件所在位置以及指定某些初始化参数。在默认情况下,MySQL 实例会按照一定的顺序在指定的位置读取,没有参数文件也可以运行,这时所有的参数值取决于编译 MySQL 时指定的默认值和源代码中指定参数的默认值。 但是如果在默认的数据库目录下找不到 mysql 架构,则启动同样失败,mysql 架构中记录了访问该实例的权限。
|
5月前
|
关系型数据库 MySQL 数据库
企业实战(15)阿里云数据库RDS MySQL 物理全备文件数据恢复至自建数据库Mysql 5.7中
企业实战(15)阿里云数据库RDS MySQL 物理全备文件数据恢复至自建数据库Mysql 5.7中
265 0
|
5月前
|
关系型数据库 MySQL 数据库
阿里云Mysql数据库物理全备文件恢复到自建数据库Mysql报错:InnoDB: Log file ./...xtrabacku
阿里云Mysql数据库物理全备文件恢复到自建数据库Mysql报错:InnoDB: Log file ./...xtrabacku
|
10月前
|
存储 关系型数据库 MySQL
MySQL聚簇索引物理结构及主键查询过程
数据页分裂的过程,在你不停往表里灌入数据时,会搞出来一个个数据页,若你的主键非自增,他可能会有一个数据行挪动过程,保证你下一个数据页的主键值都大于上一个数据页的主键值。
72 0
|
11月前
|
关系型数据库 MySQL 测试技术
MySQL 排错-解决MySQL非聚合列未包含在GROUP BY子句报错问题
MySQL 排错-解决MySQL非聚合列未包含在GROUP BY子句报错问题
116 0
|
存储 SQL 缓存
MySQL性能优化(硬件,系统配置,表结构,SQL语句)
MySQL性能优化(硬件,系统配置,表结构,SQL语句)
120 0
MySQL性能优化(硬件,系统配置,表结构,SQL语句)
|
存储 SQL Oracle
干货!RDS MySQL高频物理备份,看这篇就够了
MySQL的物理备份包含了全量备份、增量备份、逻辑事务日志备份。
干货!RDS MySQL高频物理备份,看这篇就够了
|
SQL 关系型数据库 MySQL
MYSQL中LIMIT语句(限制语句)
记录下MYSQL中LIMIT语句的基本使用
128 0
MYSQL中LIMIT语句(限制语句)
|
关系型数据库 MySQL
MySQL练习4——查询结果限制返回行数
MySQL练习4——查询结果限制返回行数!