Solved

PHP PDO Connection very slow when connecting to Mysql remotely

Posted on 2016-07-26
7
625 Views
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?
0
Comment
Question by:ido90
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 35

Expert Comment

by:gr8gonzo
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?
1
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 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.
0
 

Author Comment

by:ido90
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.
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:ido90
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();
         }
0
 
LVL 35

Expert Comment

by:gr8gonzo
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.
0
 
LVL 35

Expert Comment

by:gr8gonzo
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

1
 
LVL 110

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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
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. . .
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

729 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