10x在一个服务器上比另一个慢

10x在一个服务器上比另一个慢

本文介绍了MySQL 10x在一个服务器上比另一个慢的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个活动服务器和我的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服务器:


  1. CentOS 5.7

  2. MySQL ver 5.0.95

Dev Server:


  1. ArchLinux

  2. 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:

  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.

这篇关于MySQL 10x在一个服务器上比另一个慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-05 17:02