PHP: Remote MySQL connections very slow

Question

I have two servers, both running CentOS 5.7 and cPanel-CURRENT. One is x86 and the other is x64. Both are using Apache 2.2.21, PHP 5.3.8 and MySQL 5.1.

If I query the local database on any one of the servers, the results are returned instantly. In this instance a few thousand results are being returned. However running the same query from one server to another and the query takes 10+ seconds to complete.

If I use MySQL Workbench 5.2 to query the remote database from my workstation with the same query, it completes in less than a second, which makes me think there's a problem with PHP or something else server-related.

Has anyone else encountered this issue before and know how to resolve it? Any help would be greatly appreciated.

Purlieu

try mysql_pconnect() php.net/manual/en/function.mysql-pconnect.php mayb it will help – Pestalozzi

Using persistent connections doesn't help. The query still takes the same time to complete. – Purlieu

May be your databases differ a bit? E.g. not all indexes on local machine exist on remote server. Php work good with remote mysql server, there probably some other problem. What about network connection? Ping remote server. – Pyridoxine

If I use MySQL Workbench on my local PC to connect to the remote database over the same connection, it's fine. – Purlieu

Please clarify: Are you using pdo from the php script?, are you entering a hostname or ip address to connect to the mysql server? have you run a query and got the actual completion time of the query or the time to display output? – Flowers

what does your network setup look like? Running through a hub or older switches can be a real bitch on throughput. – Repp

please paste the connect line from PHP and also please let me know if the systems are using selinux – Writer

What's the internet speed of both connections (web server and your machine)? Try fiddler or Wireshark to analyze what's going on on the network. – Tinfoil

Answer 1

First guess:

It might be a DNS issues and you may use --skip-name-resolve option in my.cnf or you may use only IP addresses in the MySQL grant tables.

Second guess:

It might be a problem with the security level and I suggest to temporarily disable selinux or the firewall and run the test again.

Writer

I'm using the IP address to connect to the remote server in both PHP and MySQL Workbench. Also the "--skip-name-resolve" is in the config file and the server has been restarted. Finally selinux is disabled (one of the requirements for running cPanel) and I tried disabling the firewall but to no avail. – Purlieu

and you noticed any improvements? Can you please tell me what amount of time the connect function takes. I wondering what exactly is time consuming which is not actually clear from your question. – Writer

In the config file, it should be "skip-name-resolve" not "--skip-name-resolve" - the preceding dashes are only if it's used as a parameter when you start the mysqld service. – Scythe

And, the line "skip-name-resolve" should be under [mysqld] (or at least in that block) – Bignoniaceous

I meet the same issue. Try to see /etc/resolve.conf, and ping the configured DNS server. Maybe cannot access to it. – Moffett

BINGOOOO!!! skip-name-resolve did the trick for me. I was getting a consistent 10 seconds delay overhead in every connection. – Chesterfieldian

Answer 2

I had this exact issue with a PHP/MySQL application.

Just wanted to share that adding "skip-name-resolve" to my.ini fixed it for me. I was a little confused by this since my application on desktop 1 was accessing desktop 2 (mysql) using IP address. I am going between two Windows 7 desktops.

Offer