☰

MySQL 执行时间峰值

tag-icon • tag-icon mysql php nginx php-fpm
MySQL 执行时间峰值

我今天突然遇到了 MySQL 问题。

细节：

  • 操作系统：CentOS 版本 5.7
  • 服务器类型：在 mediatemple DV 4.0 包上运行的 Parallels virtuozzo 容器
  • 平均总内存使用量：<500mb
  • 允许的总内存使用量：1gb（共享池的一部分仅用于紧急情况，仅保证用户使用 500mb）
  • 处理器：>1ghz
  • 使用率最高的主数据库大小：275mb 和 107mb
  • 服务器堆栈：nginx 1.0.10、mysql 5.1.54、带有 php-fpm 的 php 5.3.8
  • innodb_buffer_pool_size=100M
  • php-fpm 最大子节点数：5
  • Webapps：基于 php 的自定义网站、magento 和 drupal
  • 慢查询超时设置为 1 秒

我完成的诊断步骤：

  1. 尚无法重新启动容器 - 今晚国内流量下降后我会再尝试。
  2. 启用mysql和php-fpm slowlog。
  3. 发现 php-fpm slowlog 中执行数据库查询的函数有时需要超过 1 秒才能完成
  4. 发现 mysql slowlog 中的一些简单查询需要超过 1 秒才能完成，而这些查询应该只需不到 1 秒的时间。
  5. 最有趣的是 - 执行时间似乎有时会激增。查询几次需要 0.2 秒，然后有一次运行同一查询需要 8 秒。这些结果是通过 mysql 命令行运行原始 SQL 查询来验证的。
  6. 顶部没有透露任何太有趣的东西
  7. 我唯一看到的与资源相关的问题是平均负载远高于正常水平
  8. 到今天为止，mysql 一直很好，自昨天以来数据库没有发生重大变化。
  9. 有时情况非常糟糕，我在执行 60 秒后看到了网关错误。
  10. Innodb 的平均读取速度为每秒 300-1400 次。
  11. Mysql 每秒执行 3-10 个查询
  12. 2 小时正常运行时间内的慢速查询数为 171（慢速超时为 1 秒）
  13. 尝试多次重启 mysql、nginx、php-fpm

例如：

UPDATE  `catalogsearch_query` SET `query_text` = 'EW 90', `num_results` = '7532', `popularity` = '99180', `redirect` = NULL, `synonym_for` = NULL, `store_id` = '1', `display_in_terms` = '1', `is_active` = '1', `is_processed` = '1', `updated_at` =  '2012-05-08 21:38:31' WHERE (query_id='31');

此查询一次耗时 17 秒，其余时间大约耗时 0.079 秒。但时间会有所不同，有时为 1 秒，有时为 0.004 秒。这是在反复运行相同的查询，每次间隔几秒钟。

大多数表都是 innodb，有时我注意到锁定时间占查询执行时间的 90％，但大多数时候锁定时间并不重要。

知道这里发生什么事了吗？

答案1

听起来几乎肯定是一个影响正在发生的事情的边缘问题，并且在像 VPS 这样的受限和竞争环境中运行 Magento 时相当典型。

如果您的配置以前运行良好，并且您没有更改任何代码或安装任何扩展 - 那么它将是一个外部影响，很可能是流量的增加 - 但对于 VPS 来说，它更有可能增加 I/O 活动。

这听起来就像 I/O 等待 - 只需安装像 Munin 这样的图形工具就可以最终证明这一点（显示 MySQL 慢查询与 I/O 活动/等待之间的相关性）。

我将参考其他几篇同样的帖子 - 因为在 VPS 中运行 Magento 总是会产生类似的行为。

  1. https://serverfault.com/a/368649/113375
  2. https://serverfault.com/a/367861/113375
  3. https://stackoverflow.com/a/8216096

答案2

最可能出现的两个问题是：

1）你的查询被另一个查询阻止 - 检查你的慢查询日志，了解启动更新时正在运行什么

2) 您的虚拟机没有被主机调度。设置一个看门狗脚本，将心跳写入日志文件

可能还有其他原因，但除非你能排除上述两个原因，否则讨论它们就没有多大意义。

就解决问题而言，前者只是查询调整的问题，而对于后者，您需要与管理主机的人员交谈。

答案3

是什么意思：

SHOW CREATE TABLE catalogsearch_query\G
SHOW INNODB STATUS;
SELECT * FROM information_schema.PROCESSLIST WHERE State='Locked' ORDER BY Time;

看起来像 ？

我怀疑您遇到了“打开表/关闭表”问题。我不会过多地重启您的数据库，它需要时间来预热，尤其是在进行大量更新时。

为了使其完整，我也希望看到这一点：

SHOW STATUS LIKE '%cache%';
SHOW STATUS LIKE '%table%';

而这一切都是在遇到麻烦时发生的，我敢打赌，当你遇到麻烦并进行“刷新桌子”时，这将花费很长时间。

一些可以加快处理速度的重要配置参数：

innodb_log_buffer_size  = 16M
innodb_file_per_table   = 1
innodb_open_files = 16000
innodb_io_capacity   = 400
innodb_flush_log_at_trx_commit = 2
innodb_flush_method  = O_DIRECT
innodb_thread_concurrency=8
innodb_lock_wait_timeout = 90

请注意，这是来自 MariaDB，因此是强化版的 mysql。（XtraDB）特别是，per_table 设置非常激进，但只会影响新表，如果您希望问题表位于自己的 innodb 中，则需要重新创建它们。这与 O_DIRECT 刷新方法和 trx_commit=2（首先阅读此内容！）一起将大大加快速度，尤其是在进行大量更新/插入时。

希望这可以帮助。

相关内容