MySQL 10x在一个服务器上比另一个慢 [英] MySQL 10x slower on one server compared to another
问题描述
我有一个活动服务器和我的dev服务器，我发现我的 LIVE （不是dev）服务器上的查询运行速度慢10倍，即使活动服务器更强大，运行相当的负载。它不是一个数据库结构的东西，因为我把实时服务器的备份加载到我的dev服务器。
有没有人有什么想法，我可以在哪里寻找差异？它可以是一个MySQL配置的东西吗？我应该从哪里开始寻找？
Live Server：
mysql> SELECT count（`Transaction`.` id`）作为计数，sum（`Transaction`.`amount`）as sum，sum（Transaction.citiq_margin + rounding + Transaction.citiq_margin_vat）as revenue from`transactions` AS`Transaction` LEFT JOIN` 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 AND YEAR（`Transaction`.`created`）= YEAR（NOW（））AND MONTH（`Transaction`.`created`））= MONTH（NOW（））;
+ ------- + --------- + --------- +
|计数| sum |收入|
+ ------- + --------- + --------- +
| 413 | 3638550 | 409210 |
+ ------- + --------- + --------- +
集合中的一行（2.62秒）
[root @ mises〜]＃uptime
17:11:57 up 55 days，1 min，1 user，load average：0.45，0.56，0.60
Dev 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`.`
+ ------- + --------- + --------- +
|计数| sum |收入|
+ ------- + --------- + --------- +
| 357 | 3005550 | 338306 |
+ ------- + --------- + --------- +
集合中的一行（0.22秒）
[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）：
处理器：7
vendor_id：GenuineIntel
cpu family：6
型号：44
型号名称：Intel（R）Xeon（R）CPU E5620 @ 2.40GHz
步进：2
cpu MHz：2395.000
缓存大小：12288 KB
物理ID：0
siblings：8
核心ID：10
cpu核心：4
Dev Server（1 x Quadcore）
处理器：3
vendor_id：GenuineIntel
cpu family：6
model：23
型号名称：Intel（R）Core（TM）2四核CPU Q8300 @ 2.50GHz
步进： 10
microcode：0xa07
cpu MHz：1998.000
缓存大小：2048 KB
物理ID：0
兄弟姐妹：4
核心ID：3
cpu核心：4
Live服务器：
- CentOS 5.7
- MySQL ver 5.0.95
Dev Server：
- ArchLinux
- MySQL ver 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:
- CentOS 5.7
- MySQL ver 5.0.95
Dev Server:
- ArchLinux
- 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.
这篇关于MySQL 10x在一个服务器上比另一个慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
