17

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.

Reado
  • 1,412
  • 5
  • 21
  • 51
  • try mysql_pconnect() http://www.php.net/manual/en/function.mysql-pconnect.php mayb it will help – YamahaSY Nov 22 '11 at 08:58
  • Using persistent connections doesn't help. The query still takes the same time to complete. – Reado Nov 22 '11 at 09:02
  • 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. – Oroboros102 Nov 22 '11 at 09:08
  • If I use MySQL Workbench on my local PC to connect to the remote database over the same connection, it's fine. – Reado Nov 22 '11 at 09:09
  • 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? – Lawrence Cherone Nov 22 '11 at 09:10
  • what does your network setup look like? Running through a hub or older switches can be a real bitch on throughput. – regality Nov 22 '11 at 09:20
  • please paste the connect line from PHP and also please let me know if the systems are using selinux – Narcis Radu Nov 22 '11 at 09:22
  • 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. – CodeCaster Nov 22 '11 at 09:39

2 Answers2

16

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.

Muhammad Hassaan
  • 7,296
  • 6
  • 30
  • 50
Narcis Radu
  • 2,519
  • 22
  • 33
  • 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. – Reado Nov 22 '11 at 09:52
  • 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. – Narcis Radu Nov 22 '11 at 09:59
  • 6
    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. – Craig Hooghiem Mar 23 '13 at 15:29
  • 4
    And, the line "skip-name-resolve" should be under [mysqld] (or at least in that block) – Rob May 24 '13 at 15:09
  • I meet the same issue. Try to see /etc/resolve.conf, and ping the configured DNS server. Maybe cannot access to it. – Does Dec 25 '19 at 08:07
5

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.

Paul
  • 51
  • 1
  • 1