PHP: Remote MySQL connections very slow

php mysql performance cpanel
Reado picture Reado · Nov 22, 2011 · Viewed 20.2k times · Source

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.

Answer

Narcis Radu picture Narcis Radu · Nov 22, 2011

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.

Related questions

Doing calculations in MySQL vs PHP

Context: We have a PHP/MySQL application. Some portions of the calculations are done in SQL directly. eg: All users created in the last 24 hours would be returned via an SQL query ( NOW() – 1 day) There's a debate going on between …

Read More
mysql_connect (localhost / 127.0.0.1) slow on Windows platform

I am using Windows 7, Apache 2, PHP 5, MySQL 5, all are on the same machine. I have found an interesting issue, I have the following code: $sql = "select * from user1"; $conn = mysql_connect("localhost", "root", "xxxxxxxx"); mysql_select_db("test1"); mysql_query("…

Read More
Most efficient approach for multilingual PHP website

I am working on a large multilingual website and I am considering different approaches for making it multilingual. The possible alternatives I can think of are: The Gettext functions with generation of .po files One MySQL table with the translations …

Read More