Go Premium for a chance to win a PS4. Enter to Win


PHP PDO Connection very slow when connecting to Mysql remotely

Posted on 2016-07-26
Medium Priority
Last Modified: 2016-08-10
Local Computer running apache, PHP 7.1. Connecting using PDO
Remote computer running MySQL 5.7

Connecting to MySQL server remotely using PHP PDO connection returns data very slowly. I have tried adding skip_name_resolve in the MySQL my.ini file but does not make a significant difference. The remote computer is connected via OpenVPN but I have also tried on the same network and I get the same speed results.
How can I resolve this issue?
Question by:ido90
  • 3
  • 2
  • 2
LVL 35

Expert Comment

ID: 41730520
You said it "returns data" slowly. The skip_name_resolve will only provide a speed benefit during the initial connection and only if there's a problem doing a reverse lookup on your IP address.

1. Provide some specifics (e.g. how many seconds is "very slowly")

2. Describe if it's slow to connect, or slow to query. A slow initial connection will have a very different cause than a slow query.

3. Make sure the slow query log is enabled on the server (you need to restart the MySQL service if you made the changes to the config file) and set to a threshold like 2 seconds or so. Then see if your queries are showing up in that log.

4. If you try connecting using the MySQLi extension, is it any faster?
LVL 111

Accepted Solution

Ray Paseur earned 2000 total points
ID: 41730958
The remote computer is connected via OpenVPN but I have also tried on the same network and I get the same speed results.
This suggests to me that the observed slow speed results are not caused by the VPN.  You can further eliminate the VPN and other communications pipes as a potential cause by testing FTP or some similar protocol.  If the bandwidth and throughput look good, then the problem is most likely something on the MySQL computer.  Or it may be that too much data is being returned by the queries.

Would we be on firm ground in understanding that the MySQL computer is dedicated to your work and is not also running work from other requests?

Have you investigated the speed of the MySQL queries?  This is where the slow query log can be helpful.  Some ideas...
  • Never use SELECT *; choose columns by name instead
  • Use LIMIT clauses to avoid a table scan
  • Add an index on any column used in WHERE, ORDER, HAVING, GROUP, JOIN, etc
  • Use EXPLAIN SELECT on any query that touches more than one table

You might also consider caching the SELECT query responses.

Author Comment

ID: 41731153
I guess this is a limitation of the connection, For one query through the VPN I get 2.9s and on the local PC get 0.052s. I don't see anything special in the logs. It would have been nice to work this way but I guess try to serve through HTTP instead.
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Author Comment

ID: 41731382
Is PDO slower then mysqli?

Do you think there is a faster way then using this:
function __construct()
         try {
               $this->dbh = new PDO('mysql:host=' . MYSQLHOST . ';dbname=' . MYSQLDB, MYSQLUSER, MYSQLPASS, array( PDO::ATTR_PERSISTENT => true ));
         catch (PDOException $e) {
                     echo $e->getMessage();
LVL 35

Expert Comment

ID: 41731388
I wouldn't jump to that conclusion just yet. There are usually 3 entities involved in a query. It USUALLY goes like this for a PHP script that accesses a database:

1. Visitor asks the web server for a PHP page.
RESULT: Network connection from visitor's PC to web server.

2. Web server reads the request, finds the right file for that request, and sends the file to the PHP engine.
RESULT: PHP engine starts and begins processing the file.

3. PHP reaches the line of code that connects to the database and performs the connection request (using the MySQL libraries it has, like MySQLi).
PHP: mysqli_connect("host","user","password","database");
RESULT: Network connection from web/PHP server to the MySQL server.

4. MySQL receives the connection and authenticates the credentials, optionally invoking SSL and/or reverse DNS lookups based on the MySQL config.
RESULT: Connection is established and kept open if everything checks out.

5. PHP continues until it finds the line of code that runs a query and then sends the query to MySQL.
PHP: mysqli_query("SELECT fields FROM table WHERE criteria");
RESULT: Network message sent to the MySQL server.

6. MySQL receives the query, optimizes it, and runs it.
RESULT: Result set built in memory.

7. MySQL sends the finished result set back to PHP.
PHP: The final stage of the mysqli_query() call from step 5.
RESULT: Results are downloaded through the established network connection.

8. PHP receives the results, stores them in memory, and allows code to loop through the results.

Now, a query will take about the same time to run (step 6) regardless of what connection you are on. However, MySQL caches the results so if a query takes 3 seconds to run on the server, then it might take a few milliseconds the next time because it will pull the results from the cache instead of executing the query again. So when you're testing connections, make sure you add "SQL_NO_CACHE" after the "SELECT" portion to bypass the query cache for more accurate testing.

SELECT SQL_NO_CACHE fields FROM table WHERE criteria;

Usually, performance issues are either on:
Steps 3,4 - Connection problems.
Step 6 - Query problems.

Unless the VPN is just EXTREMELY slow for every protocol and every download, step 7 should not be the issue, since it's downloading of data over an established connection.
LVL 35

Expert Comment

ID: 41731407
Regarding PDO vs mysqli, I'm not necessarily concerned about the code itself causing issues, but rather any potential issues with the extension itself. The extension is what actually does all the heavy lifting when connecting to the MySQL server and sending the queries and receiving/managing the results. So if there are any issues with the extensions, it can lead to performance issues that you cannot solve.

So I was just asking you to execute a basic test using both PDO and then MySQLi, to see if there were any differences. Use microtime(true) to trace how long each line takes:

$start = microtime(true);
$db = mysqli_connect("server","user","pw","database");
$end = microtime(true);
$connection_time = ($end - $start);

$start = microtime(true);
$rs = mysqli_query($db, "SELECT SQL_NO_CACHE fields FROM table");
$end = microtime(true);
$query_time = ($end - $start);

echo "Connection Time: {$connection_time}s<br />\n";
echo "Query Time: {$query_time}s<br />\n";

Open in new window

LVL 111

Expert Comment

by:Ray Paseur
ID: 41731735
Is PDO slower than mysqli?
Not in my experience.  The differences are sub-second on a query-by-query basis, in other words, so small as to not be noticeable until there is a huge load on the web site.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question