Today, I worked on a very interesting service request that our customer noticed some milliseconds of difference connecting a MySQL local environment and Azure Database for MySQL using PHP.
The first point that we need to know that these milliseconds of difference is expected because when you are connecting to Azure Database for MySQL all connections will be made against a proxy server as you could see in this URL.
For this reason, we have some best practices and recomendations that my colleague Bashar reported
In this article just wanted to share the two examples that how to show you measure the results:
In Summary
- I did several tests using mysqlcli and PDO drivers using a sample PHP application.
- All test have been done in NorthEurope.
- I installed PHP version - php-7.3.13-Win32-VC15-x64
- I created a virtual machine and installed a MySQL server v.8.0.18, with a model that allows me to select Accelerated networking.
- Computer Size: Standard D4s v3 (4 vcpus, 16 GiB memory) and accelerated networking enabled
- I created an Azure Database for MySQL:
- MySQL Version: 5.6, Memory Optimized, 8 vCore(s), 1018 GB
- SSL Enforce status: DISABLED
- Initial configuration without any parameter modification.
Results running on Azure
Test using PDO:
- I developed this small application, using persistent connection, running 1000 connections opening/closing and SELECT 1 and the results of the connections are more faster.
<?php
for ($i = 1; $i <= 1000; $i++)
{
$starttime = microtime(true);
$conn = new pdo("mysql:host=servername.mysql.database.azure.com;dbname=databasename", "username@servername","password", array(
PDO::ATTR_PERSISTENT => true));
$tsql= "SELECT 1 as X";
$getResults= $conn->query($tsql);
echo ("Reading data from table" . PHP_EOL);
if ($getResults == FALSE)
echo (sqlsrv_errors());
while ($row = $getResults->fetch(PDO::FETCH_ASSOC)) {
echo ($row['X'] . PHP_EOL);
}
$getResults=null;
$conn=null;
$endtime = microtime(true);
$timediff = $endtime - $starttime;
echo 'Time'.$i.'---'.secondsToTime($timediff).'.--'.($timediff).PHP_EOL;
}
function secondsToTime($s)
{
$h = floor($s / 3600);
$s -= $h * 3600;
$m = floor($s / 60);
$s -= $m * 60;
return $h.':'.sprintf('%02d', $m).':'.sprintf('%02d', $s);
}
?>
- I developed this small application, using non-persistent connection, running 1000 connections opening/closing and SELECT 1. You could find a slightly delay in the results.
<?php
for ($i = 1; $i <= 1000; $i++)
{
$starttime = microtime(true);
$conn = new pdo("mysql:host=servername.mysql.database.azure.com;dbname=databasename", "username@servername","password", array(
PDO::ATTR_PERSISTENT => false));
$tsql= "SELECT 1 as X";
$getResults= $conn->query($tsql);
echo ("Reading data from table" . PHP_EOL);
if ($getResults == FALSE)
echo (sqlsrv_errors());
while ($row = $getResults->fetch(PDO::FETCH_ASSOC)) {
echo ($row['X'] . PHP_EOL);
}
$getResults=null;
$conn=null;
$endtime = microtime(true);
$timediff = $endtime - $starttime;
echo 'Time'.$i.'---'.secondsToTime($timediff).'.--'.($timediff).PHP_EOL;
}
function secondsToTime($s)
{
$h = floor($s / 3600);
$s -= $h * 3600;
$m = floor($s / 60);
$s -= $m * 60;
return $h.':'.sprintf('%02d', $m).':'.sprintf('%02d', $s);
}
?>
Test using mySQLcli I got the same results using connection pooling/persistent connections:
- I developed this small application, using persistent connection, running 1000 connections opening/closing and SELECT 1
<?php
for ($i = 1; $i <= 1000; $i++)
{
$starttime = microtime(true);
//Establishes the connection
$conn = new mysqli("p:servername.mysql.database.azure.com", "username@servername","password","databasename");
$tsql= "SELECT 1 as X";
$getResults= $conn->query($tsql);
echo ("Reading data from table" . PHP_EOL);
if ($getResults == FALSE)
echo (sqlsrv_errors());
while ($row = $getResults->fetch_assoc()) {
echo ($row['X'] . PHP_EOL);
}
$getResults->free();
$conn->close();
$endtime = microtime(true);
$timediff = $endtime - $starttime;
echo 'Time'.$i.'---'.secondsToTime($timediff).'.--'.($timediff).PHP_EOL;
}
function secondsToTime($s)
{
$h = floor($s / 3600);
$s -= $h * 3600;
$m = floor($s / 60);
$s -= $m * 60;
return $h.':'.sprintf('%02d', $m).':'.sprintf('%02d', $s);
}
?>
- I developed this small application, without using persistent connection, running 1000 connections opening/closing and SELECT 1
<?php
for ($i = 1; $i <= 1000; $i++)
{
$starttime = microtime(true);
//Establishes the connection
$conn = new mysqli("servername.mysql.database.azure.com", "username@servername","password","databasename");
$tsql= "SELECT 1 as X";
$getResults= $conn->query($tsql);
echo ("Reading data from table" . PHP_EOL);
if ($getResults == FALSE)
echo (sqlsrv_errors());
while ($row = $getResults->fetch_assoc()) {
echo ($row['X'] . PHP_EOL);
}
$getResults->free();
$conn->close();
$endtime = microtime(true);
$timediff = $endtime - $starttime;
echo 'Time'.$i.'---'.secondsToTime($timediff).'.--'.($timediff).PHP_EOL;
}
function secondsToTime($s)
{
$h = floor($s / 3600);
$s -= $h * 3600;
$m = floor($s / 60);
$s -= $m * 60;
return $h.':'.sprintf('%02d', $m).':'.sprintf('%02d', $s);
}
?>
Results running on Local Environment
Test using PDO:
- I modified the application connecting to my local environment and I found that without using persistent connections the performance in terms of connection the time spent is the same using persistent connections in Azure.
<?php
for ($i = 1; $i <= 1000; $i++)
{
$starttime = microtime(true);
$conn = new pdo("mysql:host=localhost;dbname=databasename", "username","password", array(
PDO::ATTR_PERSISTENT => false));
$tsql= "SELECT 1 as X";
$getResults= $conn->query($tsql);
echo ("Reading data from table" . PHP_EOL);
if ($getResults == FALSE)
echo (sqlsrv_errors());
while ($row = $getResults->fetch(PDO::FETCH_ASSOC)) {
echo ($row['X'] . PHP_EOL);
}
$getResults=null;
$conn=null;
$endtime = microtime(true);
$timediff = $endtime - $starttime;
echo 'Time'.$i.'---'.secondsToTime($timediff).'.--'.($timediff).PHP_EOL;
}
function secondsToTime($s)
{
$h = floor($s / 3600);
$s -= $h * 3600;
$m = floor($s / 60);
$s -= $m * 60;
return $h.':'.sprintf('%02d', $m).':'.sprintf('%02d', $s);
}
?>
- I modified the application connecting to my local environment and enabling the persistent connections, and the performance in terms of connection, is better than using persistent connections in Azure.
<?php
for ($i = 1; $i <= 1000; $i++)
{
$starttime = microtime(true);
$conn = new pdo("mysql:host=localhost;dbname=databasename", "username","password", array(
PDO::ATTR_PERSISTENT => true));
$tsql= "SELECT 1 as X";
$getResults= $conn->query($tsql);
echo ("Reading data from table" . PHP_EOL);
if ($getResults == FALSE)
echo (sqlsrv_errors());
while ($row = $getResults->fetch(PDO::FETCH_ASSOC)) {
echo ($row['X'] . PHP_EOL);
}
$getResults=null;
$conn=null;
$endtime = microtime(true);
$timediff = $endtime - $starttime;
echo 'Time'.$i.'---'.secondsToTime($timediff).'.--'.($timediff).PHP_EOL;
}
function secondsToTime($s)
{
$h = floor($s / 3600);
$s -= $h * 3600;
$m = floor($s / 60);
$s -= $m * 60;
return $h.':'.sprintf('%02d', $m).':'.sprintf('%02d', $s);
}
?>
If you need more information you could visit this URL and script to have more details about other tests done.
Enjoy!
Microsoft
