更新时间:2022-10-15 22:54:56
因此,我在运行 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:
Dev Server:
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.