如何解释 MySQL 配置文件输出以调整外部 MySQL 服务器

发布于 2024-11-06 18:05:01 字数 1537 浏览 2 评论 0 原文

我最近将 Drupal 站点升级到多 webhead 环境，并尝试使用 InnoDB 引擎调整 MySQL。我注意到 SELECT 查询在生产中比在暂存中更快，但 UPDATE 查询在生产中更慢。

暂存：在具有 LAMP 堆栈的虚拟机上。
生产：带负载平衡器的双网络头。专用 MySQL 服务器和第二个热备用数据库服务器。

我的系统管理员告诉我，延迟是由于 1) 远程数据库连接和 2) 两个数据库服务器之间数据复制的二进制日志记录造成的。

我是 InnoDB 和多服务器环境的新手。我想看看 MySQL 配置文件的输出是否证实了我的服务器设置，或者是否还有进一步优化生产 MySQL 服务器的空间。

这是我从临时数据库和生产数据库中运行的。我修改了输出，将数字列并排进行比较。请注意，在生产环境中，除了状态为“end”的行之外，该查询在表中的每一行上运行得更快。是执行二进制日志记录的“结束”阶段吗？

mysql> SET profiling = 1;
mysql> UPDATE node SET created = created + 1 WHERE nid = 100;
mysql> SHOW profile;
+----------------------+----------+------------+
| Status               | Staging  | Production |
+----------------------+----------+------------+
| starting             | 0.000100 | 0.000037   |
| checking permissions | 0.000014 | 0.000006   |
| Opening tables       | 0.000042 | 0.000017   |
| System lock          | 0.000007 | 0.000004   |
| Table lock           | 0.000009 | 0.000003   |
| init                 | 0.000076 | 0.000030   |
| Updating             | 0.000062 | 0.000022   |
| end                  | 0.000031 | 0.002159   |
| query end            | 0.000006 | 0.000003   |
| freeing items        | 0.000010 | 0.000003   |
| closing tables       | 0.000009 | 0.000002   |
| logging slow query   | 0.000005 | 0.000001   |
| cleaning up          | 0.000004 | 0.000001   |
+----------------------+----------+------------+
| Total                | 0.000385 | 0.002288   |
+----------------------+----------+------------+

原文

I have recently upgraded a Drupal site to multi-webhead environment and am trying to tune MySQL with InnoDB engine. I notice SELECT queries are faster on production than on staging, but UPDATE queries are slower on production.

Staging: On a virtual machine with LAMP stack on it.
Production: Double webheads with load balancer. Dedicated MySQL server and a second hot stand-by DB server.

My system admin tells me that the latency is due to 1) remote DB connection and 2) binary logging for data replication between two DB servers.

I am new to InnoDB and multi-server environment. I'd like to see if the output from MySQL profile confirms my server settings, or if there is any more room for further optimization of production MySQL server.

This is what I ran from staging and production databases. I modified the output with number columns side by side for comparison. Note that the query runs faster on production on every rows in the table except one with status "end". Is "end" phase where binary logging is performed?

mysql> SET profiling = 1;
mysql> UPDATE node SET created = created + 1 WHERE nid = 100;
mysql> SHOW profile;
+----------------------+----------+------------+
| Status               | Staging  | Production |
+----------------------+----------+------------+
| starting             | 0.000100 | 0.000037   |
| checking permissions | 0.000014 | 0.000006   |
| Opening tables       | 0.000042 | 0.000017   |
| System lock          | 0.000007 | 0.000004   |
| Table lock           | 0.000009 | 0.000003   |
| init                 | 0.000076 | 0.000030   |
| Updating             | 0.000062 | 0.000022   |
| end                  | 0.000031 | 0.002159   |
| query end            | 0.000006 | 0.000003   |
| freeing items        | 0.000010 | 0.000003   |
| closing tables       | 0.000009 | 0.000002   |
| logging slow query   | 0.000005 | 0.000001   |
| cleaning up          | 0.000004 | 0.000001   |
+----------------------+----------+------------+
| Total                | 0.000385 | 0.002288   |
+----------------------+----------+------------+

分享到QQ

分享到微博