Solved

PHP PDO Connection very slow when connecting to Mysql remotely

Posted on 2016-07-26
7
701 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

626 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