控制MySQL:抑制日常性能下降的现实建议


本文来自实际项目经验,其中访问日志文件与日常应用程序事务在同一个数据库中,这导致了不可持续的日常性能下降。日志聚合也是在生产数据库上完成的—在晚上。当项目变得全球化,不再只处理来自DACH地区的请求时,麻烦就在眼前。

警告:你的数据库让你夜不能寐。您已经为每个对您的查询有意义的表建立了索引。如何解决这个问题?

你的应用或网站性能处于历史最低点。问题通常是糟糕的数据库设计和软件架构。无论修复它是否在您的工作范围内,您都需要一个非常快速的解决方案,以便数据库不会停止您的整个应用程序。在Unix服务器上,您不需要昂贵的工具或应用程序中新的微服务来解决这个问题——在性能关键时期运行的终端和脚本能够在短时间内处理您的情况,直到您想出更好的全面解决方案。

下面你会发现两个权宜之计:第一个是监控缓慢的查询并给你发电子邮件,第二个是在你能正确解决问题之前放入一个快速解决方案。这些解决方案只需要几个工具:编辑器、数据库凭证、crontab访问和事务电子邮件提供者(在本例中是mailgun)。

第一:监控

如上所述,解决问题的第一步是找到性能关键的查询。为此,您需要设置一个监视器,当服务器出现问题时,它会向您发送电子邮件。大多数这类指南会建议您使用控制台mail命令,这需要专家设置,并且由于反垃圾邮件解决方案,通常会遇到巨大的交付问题。相反,我们将使用mailgun和一个curl命令。

Mailgun允许每天发送多达300封电子邮件给“授权收件人”,无需注册或设置域名。默认情况下,设置的电子邮件帐户是一个“授权收件人”,但您可以添加更多,他们只需验证他们想要的电子邮件。在您设置了mailgun帐户后,您可以继续监控脚本。

注意:如果你每天生成超过300封电子邮件,你可能想更主动地处理本文后半部分讨论的问题。

让我们创建一个名为monitorqueries.sh并用以下内容填充它:

#!/bin/bash 

TOOLONG=60
USERNAME=root


HOST=127.0.0.1
PASSWORD=password




MAILGUN_DOMAIN=https://api.mailgun.net/v3/sandboxf79e512e2fca44b898cac747b734feef.mailgun.org
MAILGUN_APIKEY=key-a7ab252022dadc298b37e4595fafe2a79
TO_EMAIL="Jack Approved <jack_is_approved@gmail.com>" 
FROM_EMAIL="Jill Validated <jill_is_validated@gmail.com>"

pl=$(mysql -u$USERNAME -h$HOST -p$PASSWORD -e "SHOW FULL PROCESSLIST")
trimmedpl=$(echo "$pl" | sed '1d')

echo "$trimmedpl" | while read -r line; do
     typerunning=$(echo "$line" | cut -d$'\t' -f 5)
     if [ "$typerunning" == 'Query' ]
     then
        timerunning=$(echo "$line" | cut -d$'\t' -f 6)
        if [ $timerunning -ge $TOOLONG ]
        then
            id=$(echo "$line" | cut -d$'\t' -f 1)
            cmdinfo=$(echo "$line" | cut -d$'\t' -f 8)
            echo "Mailing slow query information."
            curl -s --user "api:$MAILGUN_APIKEY" "$MAILGUN_DOMAIN/messages" -F from="$FROM_EMAIL" -F to="$TO_EMAIL" -F subject="Slow Query Warning" -F text="pid: $id is running too long $timerunning >= $TOOLONG on the query: $cmdinfo"
        else
            echo "No queries took too long."
        fi
     fi
done

现在,您只需通过crontab尽可能频繁地运行该脚本。对于每分钟重复执行,您可以键入crontab -e在终端中,通过向crontab添加以下行,将脚本添加到您定期执行的作业中:

* * * * * pathtoyourfile/monitorqueries.sh

保存并关闭crontab。您现在已经有了查询监控脚本,应该开始接收您定义为太慢的查询的电子邮件。

第二:快速解决方案

这种快速修复适用于PHP风格的部署,在这种部署中,每个超文本传输协议查询只有一个进程,杀死一个几乎不相关的运行中的MySQL查询对整体的应用程序性能来说不是什么坏事。也就是说,请记住,如果您决定随机终止生产数据库中正在运行的查询,您需要优雅地处理接下来发生的事情——主要是用户界面反馈和随后的数据库更改。请记住,您可能正在停止一个事务,该事务是其他事务的后续,可能会中断数据链接并导致进一步的问题。叶被警告了!

你会:

  • 在远程服务器上建立与数据库的连接。
  • 执行一个查询,杀死所有超过一分钟的查询,并将它们记录到一个文件中。

这个剧本有两个目的。一方面,如果您不知道哪些查询会导致性能下降,那么您可以在方便的时候将它们放在一个文件中进行检查。另一方面,您会自动终止耗时过长的查询,以免影响应用程序性能。

警告:您需要一个管理员用户来查看所有用户的进程,并能够关闭它们。如果您在生产中使用这样的脚本,您应该通过排除或包括用户来限制查询,无论哪一个对您的设置更有意义。在示例脚本中,我们排除了通过用户运行的所有查询admin

上面显示的监控脚本使用命令SHOW FULL PROCESSLIST,它从MySQL内部表中获取信息INFORMATION_SCHEMA.PROCESSLIST。一种更细粒度的终止和记录慢速查询的方法是查询该表。这样,就可以挑出特定的用户,这对于确保像备份这样的自动任务正常工作,并且不会因为上面的脚本每分钟都在运行而被杀死是很重要的。相反,最好查询负责从应用程序传入的所有用户请求的特定用户,或者排除您为自动数据库任务创建的那些用户。如果您取消了对应用程序用户的查询,请确保向用户显示的视觉反馈反映了失败的数据事务。

注意:你应该根据自己的需要调整时间周期;如果一个大约需要20秒的查询对于您的目的来说太长,那么您将需要至少每10秒运行一次脚本,并终止超过10秒的查询。还要注意,每次执行这个脚本时都会创建一个数据库连接,因此在一分钟以下的短时间间隔内,您可能希望用套接字保持数据库连接的活动状态,但是这超出了我们的bash脚本的范围。但是,您可以轻松地在脚本中运行无限循环。下面是一个脚本示例,该脚本每10秒运行一次,持续无限长的时间,检查是否有超过30秒的查询不是来自用户admin,随后杀死它们,并将SQL信息收集到日志文件中。

#!/bin/bash
LOGFILE=/tmp/logexample
KILLQUERIES=/tmp/killqueries.sql
CHECKKILLS=/tmp/checkkills
USERNAME=root
HOST=127.0.0.1
PASSWORD=password
TOOLONG=30
CHECKDELAY=10

while :
do
  mysql -u$USERNAME -h$HOST -p$PASSWORD -e "SELECT INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND = 'Query' AND USER != 'admin' AND TIME > 10;" >> $LOGFILE
  mysql -u$USERNAME -h$HOST -p$PASSWORD -e "SELECT CONCAT('KILL ',ID,';') FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND = 'Query' AND USER != 'admin' AND TIME > $TOOLONG;" > $KILLQUERIES
  mysql -u$USERNAME -h$HOST -p$PASSWORD < $KILLQUERIES > $CHECKKILLS
  sleep $CHECKDELAY
done

MySQL已经有了一个日志功能slow query logs,默认情况下是禁用的,因为它会给数据库带来额外的开销。理想情况下,如果数据库性能良好,您不需要默认启用它。您可以使用上面的脚本来确定哪些部分需要修复,但是您不应该在生产数据库中不断积累这些信息,而没有人负责修复导致查询速度慢的原因。

  • 您可以编写一个脚本,将当前文件移动到一个版本化文件,这样您就不会得到一个几GB大的日志文件。
  • 如果在慢速查询中被频繁访问的表还没有被索引,那么这是一个逻辑上的下一步,只需要很少的努力。按相关因素(在大多数情况下,是日期,但这取决于您查询最多的是什么)对数据进行分区将是减轻数据库负担的下一个后续步骤。
  • 利用查询耗时过长的知识作为架构改进的基础,包括软件和数据库方面。