阅读更多

0顶
0踩

数据库

原创新闻 MySQL性能调优技巧

2017-12-06 09:34 by 副主编 jihong10102006 评论(0) 有14162人浏览

原文:MySQL Performance Tuning Tips for the Shopping Season
作者:Shree Nair
翻译:无阻我飞扬

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

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

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

 

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

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

一旦有了对特定使用情况的准确估计,请将该比率乘以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)并收获回报吧!

0
0
评论 共 0 条 请登录后发表评论

发表评论

您还没有登录,请您登录后再发表评论

相关推荐

  • MySQL性能调优技巧及工具

    作者:禅与计算机程序设计艺术 1.简介 随着互联网web应用日益复杂化,数据库系统也面临越来越多的压力,特别是在高并发环境下。...而MySQL数据库的性能调优就是为了提升数据库系统的处理能力,降低数据库系统的

  • MySQL性能调优与架构设计.mobi

    本书以 MySQL 数据库的基础及维护为切入点,重点介绍了 MySQL 数据库应用系统的性能调优,以及高可用可扩展的架构设计。 全书共分3篇,基础篇介绍了MySQL软件的基础知识、架构组成、存储引擎、安全管理及基本的备份...

  • MySQL 性能调优技巧

    技巧#1:确定MySQL的最大连接数 对于MySQL的最大连接数,一次最好是发送5个请求到Web服务器。对Web服务器的5个请求中的一部分将用于CSS样式表,图像和脚本等资源。由于诸如浏览器缓存等原因,要获得准确的MySQL到Web...

  • 技术更新!10个MySQL性能调优技巧

    同样,对于innodb_log_file_size和innodb_log_files_in_group 来说大多数情况下,从2GB的文件开始,...在最近的MySQL版本中,就有明显变化,如新的数据字典,已经改善了性能,即使有很多表,也不会对性能产生大的影响。

  • MySQL 性能调优和优化技巧

    如果您的查询不断备份,则更好的过程是使用。...日志文件建议是列表中的第一个,但如果您滚动到底部,您可以看到提高 MySQL 性能的一般建议。如果您需要这些数据,那很好,但是包含不需要的数据会浪费性能。...

  • MySQL性能调优的这些技巧,你都知道么?

    本文给大家介绍一些常用的MySQL性能调优实战技巧,可以在实际开发中提升数据库查询性能,并提高我们的工作效率。

  • 极具参考价值的MySQL性能调优技巧

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

  • MySQL性能调优技巧:提高数据库性能的方法

    MySQL是一个常用的关系型数据库管理系统,为了保证应用程序的性能,我们需要对MySQL进行性能调优。4.2 使用慢查询日志:慢查询日志记录执行时间超过指定阈值的查询语句,帮助我们找出性能较差的查询语句。1.3 减少...

  • MySQL性能调优与架构设计.pdf

    性能优化篇从影响 MySQL 数据库应用系统性能的因素开始,针对性地对各个影响因素进行调优分析。如 MySQL Schema 设计的技巧,Query 语句的性能优化方式方法及MySQL Server中SQL层和存储引擎层的优化思路。同时还分析...

  • 二十种实战调优MySQL性能优化的经验

    希望下面的这些优化技巧对你有用。大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中...

  • (5) MySQL性能调优技巧及工具 新浪开源团队

    3.4 MySQL性能调优工具和命令 MySQL性能调优工具和命令有很多,这里仅介绍一些常用的命令: show status:显示当前数据库状态,包括运行时信息、连接信息等。 show processlist:显示当前所有连接的进程,包括...

  • mysql linux 性能提高_针对MySQL的Linux性能调优技巧

    原文来自Percona工程师:Linux performance tuning tips for MySQL为了方面阅读,我没依照原文按行逐句的进行翻译。另外,我自己的扩充了一下基础知识点,很多知识点也是我第一次去学习,翻阅了一些资料。原文中对...

  • MySQL性能调优与架构设计(PDF)

    本书以 MySQL 数据库的基础及维护为切入点,重点介绍了 MySQL 数据库应用系统的性能调优,以及高可用可扩展的架构设计。 全书共分3 篇,基础篇介绍了MySQL软件的基础知识、架构组成、存储引擎、安全管理及基本的备份...

  • 解读MySQL性能调优“金字塔”

    计算机是一种实验的科学,性能优化是实战的艺术 蒸汽机的改进不是一蹴而就的,MySQL性能的改进也是贯穿整个...本文选自《千金良方:MySQL性能优化金字塔法则》一书,将从整体上介绍性能调优的几个方面,并借用“...

  • MySQL应用实战与性能调优

    MySQL是当下流行的关系型数据库管理系统,使用C和C++语言编写而成,因而具有源码级的可移植性。MySQL可以在Linux、macOS、NovellNetWare、OpenBSD、Solaris、Windows等多种操作系统中运行。在Web应用方面,MySQL是...

  • 阿里巴巴Java性能调优实战(2021-2022华山版)+Java架构核心宝典+性能优化手册100技巧.rar

    阿里巴巴Java性能调优华山版是一套系统性能调优教程,!通过这份笔记的学习,你将会有...MySQL性能优化的 50+ 小技巧 Redis 性能优化的 13 个小技巧 . . . 每篇文章图文并茂,附有源码 + 性能评测结果 + 优化方案。

  • 江西师范大学科学技术学院在四川2020-2024各专业最低录取分数及位次表.pdf

    那些年,与你同分同位次的同学都去了哪里?全国各大学在四川2020-2024年各专业最低录取分数及录取位次数据,高考志愿必备参考数据

  • 麒麟win10双系统重新安装win10后麒麟启动菜单看不到解决方法

    麒麟win10双系统重新安装win10后麒麟启动菜单看不到解决方法

  • 多邻国Duolingo v6.0.3 高级版.apk

    多邻国Duolingo v6.0.3 高级版.apk

Global site tag (gtag.js) - Google Analytics