問(wèn)題描述
我有一個(gè)實(shí)時(shí)服務(wù)器和我的開發(fā)服務(wù)器，我發(fā)現(xiàn)我的 LIVE(不是開發(fā))服務(wù)器上的查詢運(yùn)行速度慢了 10 倍，即使實(shí)時(shí)服務(wù)器更強(qiáng)大，而且它們都是運(yùn)行可比負(fù)載.這不是數(shù)據(jù)庫(kù)結(jié)構(gòu)的事情，因?yàn)槲覍浞輳膶?shí)時(shí)服務(wù)器加載到我的開發(fā)服務(wù)器中.
有人對(duì)我可以在哪里查找差異有任何想法嗎?它可能是一個(gè) MySQL 配置的東西嗎?我應(yīng)該從哪里開始尋找?
實(shí)時(shí)服務(wù)器:
<前>mysql> SELECT count(`Transaction`.`id`) 作為count, sum(`Transaction`.`amount`) 作為sum, sum(Transaction.citiq_margin+rounding + Transaction.citiq_margin_vat) 作為收入來(lái)自`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());+-------+---------+---------+|計(jì)數(shù) |總和 |收入|+-------+---------+---------+|第413話3638550 |409210 |+-------+---------+---------+1 排(2.62 秒)[root@mises ~]# 正常運(yùn)行時(shí)間17:11:57 55 天，1 分鐘，1 個(gè)用戶，平均負(fù)載:0.45、0.56、0.60開發(fā)服務(wù)器(由于備份有輕微的時(shí)間延遲，結(jié)果計(jì)數(shù)不同):
<前>mysql> SELECT count(`Transaction`.`id`) 作為count, sum(`Transaction`.`amount`) 作為sum, sum(Transaction.citiq_margin+rounding + Transaction.citiq_margin_vat) 作為收入來(lái)自`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());+-------+---------+---------+|計(jì)數(shù) |總和 |收入|+-------+---------+---------+|第357話3005550 |338306 |+-------+---------+---------+1 排(0.22 秒)[www@smith 測(cè)試]$ 正常運(yùn)行時(shí)間18:11:53 up 12 days, 1:57, 4 users, load average: 0.91, 0.75, 0.62實(shí)時(shí)服務(wù)器(2 個(gè)至強(qiáng)四核):
<前>處理器:7vendor_id : 正版英特爾CPU系列:6型號(hào) : 44型號(hào)名稱 : Intel(R) Xeon(R) CPU E5620 @ 2.40GHz踏步:2CPU 兆赫:2395.000緩存大小:12288 KB物理 ID:0兄弟姐妹:8核心編號(hào):10CPU核心:4開發(fā)服務(wù)器(1 個(gè)四核)
<前>處理器:3vendor_id : 正版英特爾CPU系列:6型號(hào) : 23型號(hào)名稱 : Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz步數(shù):10微碼:0xa07CPU 兆赫:1998.000緩存大小:2048 KB物理 ID:0兄弟姐妹:4核心編號(hào):3CPU核心:4實(shí)時(shí)服務(wù)器:
- CentOS 5.7
- MySQL 5.0.95 版
開發(fā)服務(wù)器:
- ArchLinux
- MySQL 5.5.25a 版
因此，我在運(yùn)行 Centos、1 個(gè) CPU 和 512MB 內(nèi)存的虛擬機(jī)上運(yùn)行相同的數(shù)據(jù)庫(kù)和查詢:它在 0.3 秒內(nèi)提供了該查詢的答案;系統(tǒng)負(fù)載為 0.4 :/
唯一真正的區(qū)別似乎是我在該服務(wù)器上運(yùn)行 Mysql 5.5.在我的案例中，從 Mysql 5.0 到 Mysql 5.5，似乎真的性能提高了 10 倍.
我只有在將實(shí)時(shí)服務(wù)器從 Mysql 5.0 遷移到 Mysql 5.5 后才能確定，一旦完成，我將確認(rèn)結(jié)果.
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.
這篇關(guān)于一臺(tái)服務(wù)器上的 MySQL 比另一臺(tái)服務(wù)器慢 10 倍的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！