慢的MySQL远程连接 [英] Slow MySQL Remote Connection
问题描述
目前我们的网站是1服务器上运行（Ubuntu的10.04 - Rackspace公司是我们的主机），并且为了，我们目前使用的最高的选项，能够处理流量高峰是Rackspace的优惠（30 GB RAM和8核心CPU）。
Currently our site is running on 1 server (Ubuntu 10.04 - Rackspace is our host), and in order to be able to handle traffic spikes, we are currently using the highest option that Rackspace offers (30 GB RAM and an 8-core CPU).
CPU是我们的瓶颈，所以我想放的MySQL自己的服务器上。我曾尝试这样做，但不幸的是它增加9秒的页面加载时间。 PHP / MySQL的是从一台服务器通过Rackspace的ServiceNet（本地IP地址）连接到另一个。使用ServiceNet IP地址时，页面加载速度是慢得离谱（超过40秒）。
CPU is our bottleneck, so I would like to put MySQL on its own server. I have tried doing this, but unfortunately it's adding 9 seconds to the page load time. PHP / MySQL is connecting from 1 server to the other through Rackspace's ServiceNet (local ip address). Before using the ServiceNet ip address, the page load was ridiculously slow (over 40 seconds).
我已经加入跳过名称解析到my.cnf，这似乎并没有在所有提高性能。
I have added "skip-name-resolve" to my.cnf, and this did not seem to improve performance at all.
我只是想知道哪些选项我不得不减少远程MySQL连接时间。好像必须有我丢失的东西，因为一个额外的9秒是太多。
I am just wondering what options I have to reduce the remote MySQL connection time. It seems like there has to be something I'm missing because an extra 9 seconds is way too much.
克隆服务器与其他服务器运行MySQL： http://173.45.255.52/index.php?action=browse_members
Cloned server running MySQL from the other server:
http://173.45.255.52/index.php?action=browse_members
Live服务器运行MySQL本地：
http://bros4bros.com/index.php?action=browse_members
Live server running MySQL locally: http://bros4bros.com/index.php?action=browse_members
更新：
我做了一个简单的查询，我的网页加载时间是非常快（270毫秒），所以我做了一个更大的查询（SELECT * FROM城市，1 ...这个表有近300万的记录），并响应时间仍然是来自直播现场没有太大的不同： http://173.45.255.52/simple_query.php
I did a simple query, and my page load time was very fast (270 ms), so I did a much larger query (SELECT * FROM cities WHERE 1... this table has almost 3,000,000 records), and the response time is still not much different from the live site: http://173.45.255.52/simple_query.php
我有点难倒。怎么能只是通过远程运行MySQL来增加9秒网站加载时间？
I'm kind of stumped. How can it be adding 9 seconds to the site load time just by running MySQL remotely?
一个瀑布显示它只是在等待9秒。
A waterfall shows it's just waiting for 9 seconds.
活网站的瀑布如下：
更新2：
使用mysql_pconnect而不是mysql_connect的没什么区别。还有一个额外的8 - 等待9秒侦测一个服务器到另一个小于1毫秒。我撕裂我的头发了。
Using mysql_pconnect instead of mysql_connect makes no difference. There is still an extra 8 - 9 seconds of waiting. Pinging one server to the other is less than 1 ms. I'm tearing my hair out now.
暂时禁用防火墙不起作用。
Temporarily disabling the firewall has no effect.
使用mysqli_connect而不是mysql_connect连接成功，但查询返回任何结果... 划痕HEAD 的
Using "mysqli_connect" instead of "mysql_connect" successfully connects but queries return no results... SCRATCHES HEAD
更新3：
已成功更新code用mysqli_connect而不是mysql_connect，也没有性能优势。我不得不改变我的mysql_query报表mysqli_query等。
Successfully updated the code to use "mysqli_connect" instead of "mysql_connect", and there was no performance benefit. I had to change my "mysql_query" statements to "mysqli_query", etc.
更新4：
是啊，这听起来像它必须（根据下文延'评论）的问题。我们显示在页面上60名成员，所以如果我们在做每个成员显示两个小疑问，这是120的查询。我想他们只是加起来。我得走了code找到一个更好的解决方案，以尽量减少查询......有趣的是我摧毁了第二个服务器，再次克隆的第一台服务器，现在我只看到一个额外的2.5秒。如果我制定一个更优雅的解决方案，以尽量减少查询，我必须能够得到那个可以忽略不计。
Yeah, that sounds like it has to be the problem (in response to Jens' comment below). We're displaying 60 members on the page, so if we're doing two small queries per member displayed, that's 120 queries. I guess they're just adding up. Time to hit the code to find a more elegant solution to minimize queries... Interestingly enough I destroyed the second server, cloned the first server again, and now I'm only seeing an extra 2.5 seconds. If I develop a more elegant solution to minimize queries, I've got to be able to get that to negligible.
推荐答案
减少MySQL的查询数！
我在很多小的查询转换成少数较大的查询过程，它工作正常。从另一台服务器上运行MySQL时，该网站现在是非常快的。
I am in the process of converting a lot of small queries into fewer larger queries, and it is working. The site is now very fast when running MySQL from the other server.
这篇关于慢的MySQL远程连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
