无惧双十二Or 黑五,这些 MySQL 性能调优技巧看过来

2024-06-03 10:08

本文主要是介绍无惧双十二Or 黑五,这些 MySQL 性能调优技巧看过来,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

2017-12-10 jihong10102006 ITPUB ITPUB
ITPUB

itpuber

ITPUB官方账户,分享社区技术干货内容,了解社区最新动态,参与社区精彩活动。

点击蓝色字关注 "ITPUB" 一起玩耍哦~
摘要: 针对购物旺季网站流量会对数据库造成的压力,作者给出了 MySQL 性能调优的一些技巧,这些技巧极具参考价值,通过这些调优,可以有效避免因为流量过大造成服务器宕机,从而给企业造成经济损失。以下是译文


万圣节已经过去很久了,该是把注意力集中在即将到来的假日季节的时候了。首先是感恩节,接着就是黑色星期五和网络星期一,最终在圣诞节 / 节礼周(从 12 月 26 日的节礼日开始,到 12 月 31 日的除夕结束为期六天或更长时间。这个词是由零售业在 2000 年代中期左右发明的,试图延长他们的节礼日销售)达到购物高潮。对于企业主来说,一年的这个时候标志着人们期待已久的年底获利了结。对于一些 DBA 来说,它会带来恐惧,不安,甚至是不眠之夜,他们要努力使系统重新上线。


值得庆幸是,情况并非如此。通过对 MySQL 性能变量做一些主动调整,可以使数据库服务器免受购物旺季带来的需求增加的冲击。

技巧 #1:确定 MySQL 的最大连接数


对于 MySQL 的最大连接数,一次最好是发送 5 个请求到 Web 服务器。对 Web 服务器的 5 个请求中的一部分将用于 CSS 样式表,图像和脚本等资源。由于诸如浏览器缓存等原因,要获得准确的 MySQL 到 Web 服务器的请求比率可能很困难; 要想得到一个确切的数字,就需要分析 Web 服务器的日志文件。例如,可以手动访问 Apache 的 “access_log” 日志文件,也可以通过 Analog 或 Webalizer 等实用程序访问日志文件。


一旦有了对特定使用情况的准确估计,请将该比率乘以 Web 服务器的最大连接数。例如,如果 Web 服务器配置为最多为 256 个客户端提供服务,MySQL 请求与 Web 请求的比率为 1/8,则最好将最大数据库连接数设置为 32。还要考虑留有安全余量,把这个数乘以 2,得到最终的数量。只有在基础设施支持的情况下,才能尝试将数据库连接数的最大数量与 Web 服务器的客户端限制相匹配。在大多数情况下,最好保持接近 32。


 在 Monyog 中查看 MySQL 连接  


在 MySQL 数据库中,MySQL 的最大并发连接数是存储在全局变量 max_connections 中的。Monyog 报告变量 “max_connections” 作为当前连接监控组中的 “最大允许” 指标。它还将该数字除以打开的连接数,以生成连接使用百分比:


还有一个连接历史记录监控,可以帮助计算最佳的最大并发连接数。它包括尝试,拒绝和成功连接的数量。此外,允许达到的最大指标的百分比显示为一个进度条,可以让你快速评估服务器在过去达到的最大并发连接数:

 

技巧 #2:为临时表分配足够的内存


在某些情况下,服务器在处理语句时会创建内部临时表。临时表用于内部操作如 GROUP BY 和 distinct,还有一些 ORDER BY 查询以及 UNION 和 FROM 子句(派生表)中的子查询。这些都是在内存中创建的内存表。内存中临时表的最大大小由 tmp_table_size 和 max_heap_table_size 中较小的值确定。如果临时表的大小超过这个阈值,则将其转换为磁盘上的 InnoDB 或 MyISAM 表。此外,如果查询涉及 BLOB 或 TEXT 列,而这些列不能存储在内存表中,临时表总是直接指向磁盘。


这种转换的代价很大,所以考虑增加 max_heap_table_size 和 tmp_table_size 变量的大小来帮助减少在磁盘上创建临时表的数量。请记住,这将需要大量内存,因为内存中临时表的大小是基于 “最坏情况” 的。例如,内存表总是使用固定长度的列,所以字符列使用 VARCHAR(255)。这可以使内存中的临时表比想象的要大得多—事实上,这比查询表的总大小要大很多倍!当增加 max_heap_table_size 和 tmp_table_sizevariables 的大小时,一定要监视服务器的内存使用情况,因为内存中的临时表可能会增加达到服务器内存容量的风险。


一般来说,32M 到 64M 是建议值,从这两个变量开始并根据需要进行调优。

 在 Monyog 中的临时表监测 


临时表的监测是许多预定义的 Monyog 监测之一。它提供了一些临时表使用的指标,包括:

 

  • 允许的最大值显示 tmp_table_size 服务器变量的值,它定义了在内存中创建的临时表的最大大小。与 max_heap_table_size 一起,这个值定义了可以在内存中创建的临时表的最大大小。如果内存临时表大于此大小,则将其存储在磁盘上。

  • 内存表的最大大小显示 max_heap_table_size 服务器变量的值,该值定义了显式创建的 MEMORY 存储引擎表的最大大小。

  • 创建的临时表总数显示 created_tmp_tables 服务器变量的值,它定义了在内存中创建的临时表的数量。

  • 在磁盘上创建的临时表显示 created_tmp_disk_tables 服务器变量的值,该变量定义了在磁盘上创建的临时表的数量。如果这个值很高,则应该考虑增加 tmp_table_size 和 max_heap_table_size 的值,以便增加创建内存临时表的数量,从而减少在磁盘上创建临时表的数量。

  • 磁盘:总比率基于 created_tmp_disk_tables 除以 created_tmp_tables 的计算值。由于 tmp_table_size 或 max_heap_table_size 不足而在磁盘上创建的临时表的百分比。Monyog 将这个数字显示为一个进度条和百分比,以便快速确定有多少磁盘用于临时表,而不是内存。


趋势图可用于创建的总表,磁盘上创建的表和磁盘的总比值。这些让我们看到了它们随着时间的演变:

 

技巧 #3:增加线程缓存大小


连接管理器线程处理服务器监听的网络接口上的客户端连接请求。连接管理器线程将每个客户端连接与专用于它的线程关联,该线程负责处理该连接的身份验证和所有请求处理。因此,线程和当前连接的客户端之间是一对一的比例。确保线程缓存足够大以容纳所有传入请求是非常重要的。


MySQL 提供了许多与连接线程相关的服务器变量:


线程缓存大小由 thread_cache_size 系统变量决定。默认值为 0(无缓存),这将导致为每个新连接设置一个线程,并在连接终止时需要处理该线程。如果希望服务器每秒接收数百个连接请求,那么应该将 thread_cache_size 设置的足够高,以便大多数新连接可以使用缓存线程。可以在服务器启动或运行时设置 max_connections 的值。


还应该监视缓存中的线程数(Threads_cached)以及创建了多少个线程,因为无法从缓存中获取线程(Threads_created)。关于后者,如果 Threads_created 继续以每分钟多于几个线程的增加,请考虑增加 thread_cache_size 的值。


使用 MySQL show status 命令显示 MySQL 的变量和状态信息。这里有几个例子:


SHOW GLOBAL STATUS LIKE '%Threads_connected%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Threads_connected | 2     |+-------------------+-------+
SHOW GLOBAL STATUS LIKE '%Threads_running%';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| Threads_running | 1     |+-----------------+-------+

 

  Monyog 线程缓存监测  


Monyog 提供了一个监控线程缓存的屏幕,名为 “线程”。与 MySQL 线程相关的服务器变量映射到以下 Monyog 指标:


  • thread_cache_size:可以缓存的线程数。

  • Threads_cached:缓存中的线程数。

  • Threads_created:创建用于处理连接的线程。


Monyog 线程屏幕还包括 “线程缓存命中率” 指标。这是一个提示线程缓存命中率的指标。如果值较低,则应该考虑增加线程缓存。在状态栏以百分比形式显示该值;它的值越接近 100%越好。


如果这些指标的值等于或超过指定值,则可以将每一个指标配置为发出警告和 / 或严重警报。 

其他相关的服务器变量

除了上述指标以外,还应该监控以下内容:

 

  1. InnoDB 缓冲池大小 InnoDB 缓冲池大小在使用 InnoDB 的 MySQL 数据库中起着至关重要的作用。缓冲池同时缓存数据和索引。它的值应该尽可能的大,以确保数据库使用内存而不是硬盘驱动器进行读取操作。

  2. 临时表大小 MySQL 使用 max_heap_table_size 和 tmp_table_size 中较小的一个来限制内存中临时表的大小。拥有较大的值可以帮助减少在磁盘上创建临时表的数量,但也会增加服务器内存容量的风险,因为这个指标适用于每个客户端。一般来说,32M 到 64M 是建议的值,从这两个变量开始并根据需要进行调优。

  3. InnoDB 日志缓冲区大小MySQL 每次写入日志文件时,它都会利用可用于处理销售数据的重要系统资源。因此,将 InnoDB 日志缓冲区大小设置为较大值才有意义。这样,服务器在大型事务中写入磁盘的次数就减少了,从而最大限度地减少了这些耗时的操作。64M 是这个变量的一个很好的起点。

结论

虽然即便是最大的公司网站也会因宕机而遭受损失,但这种影响对于处理网上销售的中小型企业尤其关键。根据最近的一份调查报告显示,一分钟的宕机导致企业平均损失约 5000 美元。不要让你的业务成为那种统计数据(因为宕机造成的损失)的一部分。在假日繁忙之前,主动调优 MySQL 数据库服务器(S)并收获回报吧!


转自:ITeye

原文地址:http://www.iteye.com/news/32796


近期热文

Oracle 常用数据字典表、视图的总结,都在这里了

万字干货总结:MySQL优化原理学习,这一篇就够了!

我是如何在 SQLServer 中处理每天四亿三千万记录的?

维密放弃高薪转行当程序员,她可能是史上最拼的超模!

MySQL 和 MongoDB 对比,一文看全~

数据库 10 大常见安全问题盘点~

就鹿晗宣布恋情导致微博宕机事件浅谈大型网站高可用性架构

四大行、城商行等银行都在使用什么数据库?

听说麦当劳改名了,这些数据库们要说:早都想改了。

为什么 SQL 正在击败 NoSQL,这对未来的数据意味着什么?

十一,送你  51 个 Oracle 常用语句~


《百度外卖大数据技术特刊》合集

现已开放下载了!


微信后台回复:BD  即可收到下载链接


摘自:

http://mp.weixin.qq.com/s?__biz=MjM5OTkxOTc0Mw==&mid=2650230107&idx=1&sn=e3b6d2287c8e52fc4fa2e09596be361c&chksm=bf37c44b88404d5df83aea7322b86c2c2d8984da80f12464c42023771f74150b39309f6e4f86&mpshare=1&scene=1&srcid=1210M3IaA2zi5R3OOAxFMZTJ#rd

赞赏

长按二维码向我转账

受苹果公司新规定影响,微信 iOS 版的赞赏功能被关闭,可通过二维码转账支持公众号。

阅读
投诉

这篇关于无惧双十二Or 黑五,这些 MySQL 性能调优技巧看过来的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1026679

相关文章

快速修复一个Panic的Linux内核的技巧

《快速修复一个Panic的Linux内核的技巧》Linux系统中运行了不当的mkinitcpio操作导致内核文件不能正常工作,重启的时候,内核启动中止于Panic状态,该怎么解决这个问题呢?下面我们就... 感谢China编程(www.chinasem.cn)网友 鸢一雨音 的投稿写这篇文章是有原因的。为了配置完

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》:本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J

Python ZIP文件操作技巧详解

《PythonZIP文件操作技巧详解》在数据处理和系统开发中,ZIP文件操作是开发者必须掌握的核心技能,Python标准库提供的zipfile模块以简洁的API和跨平台特性,成为处理ZIP文件的首选... 目录一、ZIP文件操作基础三板斧1.1 创建压缩包1.2 解压操作1.3 文件遍历与信息获取二、进阶技

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》:本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1:以跳过权限验证模式启动 mysql 并重置密码方法 2:使用 my.ini 文件的临时配置在 Wi

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼:明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案:使用异常捕获3.

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例(伪代码)在数据库系统中,r

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》:本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权(GRANT)语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》:本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用