一台服务器上的 MySQL 比另一台服务器慢 10 倍

我有一个实时服务器和我的开发服务器，我发现我的 LIVE(不是开发)服务器上的查询运行速度慢了 10 倍，即使实时服务器更强大，而且它们都是运行可比负载.这不是数据库结构的事情，因为我将备份从实时服务器加载到我的开发服务器中.

有人对我可以在哪里查找差异有任何想法吗?它可能是一个 MySQL 配置的东西吗?我应该从哪里开始寻找?

实时服务器:

<前>mysql> SELECT count(`Transaction`.`id`) 作为count, sum(`Transaction`.`amount`) 作为sum, sum(Transaction.citiq_margin+rounding + Transaction.citiq_margin_vat) 作为收入来自`transactions` AS `Transaction` LEFT JOIN `meters` AS `Meter` ON (`Transaction`.`meter_id` = `Meter`.`id`) LEFT JOIN `units` AS `Unit` ON (`Meter`.`unit_id` = `Unit`.`id`) WHERE (NOT (`Unit`.`building_id` IN ('1', '85')) AND NOT (`Transaction`.`state` >= 90)) AND DAY(`Transaction`.`created`) = DAY(NOW()) AND YEAR(`Transaction`.`created`) = YEAR(NOW()) AND (MONTH(`Transaction`.`created`)) = MONTH(NOW());+-------+---------+---------+|计数 |总和 |收入|+-------+---------+---------+|第413话3638550 |409210 |+-------+---------+---------+1 排(2.62 秒)[root@mises ~]# 正常运行时间17:11:57 55 天，1 分钟，1 个用户，平均负载:0.45、0.56、0.60

开发服务器(由于备份有轻微的时间延迟，结果计数不同):

<前>mysql> SELECT count(`Transaction`.`id`) 作为count, sum(`Transaction`.`amount`) 作为sum, sum(Transaction.citiq_margin+rounding + Transaction.citiq_margin_vat) 作为收入来自`transactions` AS `Transaction` LEFT JOIN `meters` AS `Meter` ON (`Transaction`.`meter_id` = `Meter`.`id`) LEFT JOIN `units` AS `Unit` ON (`Meter`.`unit_id` = `Unit`.`id`) WHERE (NOT (`Unit`.`building_id` IN ('1', '85')) AND NOT (`Transaction`.`state` >= 90)) AND DAY(`Transaction`.`created`) = DAY(NOW()) AND YEAR(`Transaction`.`created`) = YEAR(NOW()) AND (MONTH(`Transaction`.`created`)) = MONTH(NOW());+-------+---------+---------+|计数 |总和 |收入|+-------+---------+---------+|第357话3005550 |338306 |+-------+---------+---------+1 排(0.22 秒)[www@smith 测试]$ 正常运行时间18:11:53 up 12 days, 1:57, 4 users, load average: 0.91, 0.75, 0.62

实时服务器(2 个至强四核):

<前>处理器:7vendor_id : 正版英特尔CPU系列:6型号 : 44型号名称 : Intel(R) Xeon(R) CPU E5620 @ 2.40GHz踏步:2CPU 兆赫:2395.000缓存大小:12288 KB物理 ID:0兄弟姐妹:8核心编号:10CPU核心:4

开发服务器(1 个四核)

<前>处理器:3vendor_id : 正版英特尔CPU系列:6型号 : 23型号名称 : Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz步数:10微码:0xa07CPU 兆赫:1998.000缓存大小:2048 KB物理 ID:0兄弟姐妹:4核心编号:3CPU核心:4

实时服务器:

1. CentOS 5.7
2. MySQL 5.0.95 版

开发服务器:

1. ArchLinux
2. MySQL 5.5.25a 版

解决方案

因此，我在运行 Centos、1 个 CPU 和 512MB 内存的虚拟机上运行相同的数据库和查询:它在 0.3 秒内提供了该查询的答案;系统负载为 0.4 :/

唯一真正的区别似乎是我在该服务器上运行 Mysql 5.5.在我的案例中，从 Mysql 5.0 到 Mysql 5.5，似乎真的性能提高了 10 倍.

我只有在将实时服务器从 Mysql 5.0 迁移到 Mysql 5.5 后才能确定，一旦完成，我将确认结果.

I have a live server and my dev server, and I am finding that queries on my LIVE (not dev) server run 10x slower, even though the live server is more powerful and they are both running comparable load. It's not a database structure thing because I load the backup from the live server into my dev server.

Does anybody have any ideas on where I could look for the discrepancy? Could it be a MySQL config thing? Where should I start looking?

Live Server:

mysql> SELECT count(`Transaction`.`id`) as count, sum(`Transaction`.`amount`) as sum, sum(Transaction.citiq_margin+rounding + Transaction.citiq_margin_vat) as revenue FROM `transactions` AS `Transaction` LEFT JOIN `meters` AS `Meter` ON (`Transaction`.`meter_id` = `Meter`.`id`) LEFT JOIN `units` AS `Unit` ON (`Meter`.`unit_id` = `Unit`.`id`) WHERE (NOT (`Unit`.`building_id` IN ('1', '85')) AND NOT (`Transaction`.`state` >= 90)) AND DAY(`Transaction`.`created`) = DAY(NOW()) AND YEAR(`Transaction`.`created`) = YEAR(NOW()) AND (MONTH(`Transaction`.`created`)) = MONTH(NOW());
+-------+---------+---------+
| count | sum     | revenue |
+-------+---------+---------+
|   413 | 3638550 |  409210 |
+-------+---------+---------+
1 row in set (2.62 sec)

[root@mises ~]# uptime
 17:11:57 up 55 days, 1 min,  1 user,  load average: 0.45, 0.56, 0.60

Dev Server (result count is different because of slight time delay from backup):

mysql>  SELECT count(`Transaction`.`id`) as count, sum(`Transaction`.`amount`) as sum, sum(Transaction.citiq_margin+rounding + Transaction.citiq_margin_vat) as revenue FROM `transactions` AS `Transaction` LEFT JOIN `meters` AS `Meter` ON (`Transaction`.`meter_id` = `Meter`.`id`) LEFT JOIN `units` AS `Unit` ON (`Meter`.`unit_id` = `Unit`.`id`) WHERE (NOT (`Unit`.`building_id` IN ('1', '85')) AND NOT (`Transaction`.`state` >= 90)) AND DAY(`Transaction`.`created`) = DAY(NOW()) AND YEAR(`Transaction`.`created`) = YEAR(NOW()) AND (MONTH(`Transaction`.`created`)) = MONTH(NOW());
+-------+---------+---------+
| count | sum     | revenue |
+-------+---------+---------+
|   357 | 3005550 |  338306 |
+-------+---------+---------+
1 row in set (0.22 sec)

[www@smith test]$ uptime
 18:11:53 up 12 days,  1:57,  4 users,  load average: 0.91, 0.75, 0.62

Live Server (2 x Xeon Quadcore):

processor       : 7
vendor_id       : GenuineIntel
cpu family      : 6
model           : 44
model name      : Intel(R) Xeon(R) CPU           E5620  @ 2.40GHz
stepping        : 2
cpu MHz         : 2395.000
cache size      : 12288 KB
physical id     : 0
siblings        : 8
core id         : 10
cpu cores       : 4

Dev Server (1 x Quadcore)

processor       : 3
vendor_id       : GenuineIntel
cpu family      : 6
model           : 23
model name      : Intel(R) Core(TM)2 Quad CPU    Q8300  @ 2.50GHz
stepping        : 10
microcode       : 0xa07
cpu MHz         : 1998.000
cache size      : 2048 KB
physical id     : 0
siblings        : 4
core id         : 3
cpu cores       : 4

Live Server:

1. CentOS 5.7
2. MySQL ver 5.0.95

Dev Server:

1. ArchLinux
2. MySQL ver 5.5.25a

解决方案

So, I ran the same database and queries on a Virtual Machine running Centos, 1 CPU and 512MB of memory: it provides the answer to that query in 0.3 seconds; system load is 0.4 :/

The only real difference seems to be that I am running Mysql 5.5 on that server. And it seems that there really is a 10x performance improvement in my case from Mysql 5.0 to Mysql 5.5.

I will only know for sure once I have migrated my live servers from Mysql 5.0 to Mysql 5.5, I will confirm the results once I have done that.