fozzo Posted July 23, 2014 Share Posted July 23, 2014 Hi guys I need some help with slow connection to remote mysql database. I've been after this for a couple of days. I've been reading around but I didn't find any solution which helped me. I have this scenario: Server Ae-commerce runningmysql database running Server Be-commerce runningremote connection to server A database The two e-commerce are identical. On server A it runs very quick, on server B the connection to mysql database it's about 16 times slower. Some complex queries and mixes queries don't get resolved and let the page go in timeout. I tried a test php script where I connect to mysql and I run a simple query (some classes are required before the query). On server A execution time is 0.107 seconds while on server B it is 1.788 seconds. I tried to have the same test with both firewall (server A and B) turned off but the results is exactly the same. I added in my.cnf of server A skip-name-resolve and restarted mysql but this didn't really change anything I am logging slow queries and none from server B are recorded in the file Running the same query from remote ssh connection from server B to server A's mysql is very fast Connection is set to persistent on server B's e-commerce Both server have centos 6.2 installed with WHM/Cpanel, configurations are basically the same. Do I miss something obvious? Quote Link to comment https://forums.phpfreaks.com/topic/290072-remote-connection-to-mysql-very-slow/ Share on other sites More sharing options...
phpPeter Posted August 5, 2014 Share Posted August 5, 2014 A remote database connection is always significant slower than a local connection. Typically, the network communication will generate much more overhead, so that the time will be reduced up to a relevant factor. It is caused by the -compared to memory speed- slow network connection, and the latency. A remote ssh is fast, because the communication between DB client and database happen locally. If there is the possibility of connection pooling, you should use that option. Establishing connections will produce signifcant delays. If you are lucky, this will reduce the query time very much, if multiple requests are issued against the database. A single request is not represantive in this case. Please run your test scripte multiple times, or create a test script, which will issue the request multiple times itself (I assume the most expensive operation is establishing the connection) Quote Link to comment https://forums.phpfreaks.com/topic/290072-remote-connection-to-mysql-very-slow/#findComment-1486929 Share on other sites More sharing options...
trq Posted August 5, 2014 Share Posted August 5, 2014 A remote ssh is fast, because the communication between DB client and database happen locally. That makes little sense. SSH is a network communication protocol. Quote Link to comment https://forums.phpfreaks.com/topic/290072-remote-connection-to-mysql-very-slow/#findComment-1486942 Share on other sites More sharing options...
phpPeter Posted August 10, 2014 Share Posted August 10, 2014 He tried to you log via ssh on on his database server. Then, of course, will the queries executed locally. The issue is the latency of remote database connections. Quote Link to comment https://forums.phpfreaks.com/topic/290072-remote-connection-to-mysql-very-slow/#findComment-1487347 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.