Remote connection to mysql very slow

[fozzo]

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 A
e-commerce running
mysql database running

 

Server B
e-commerce running
remote 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?

 

[phpPeter]

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)

[trq]

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.

[phpPeter]

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.