Lesson Learned #243: Enabling ODBC connection pooling in PHP

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Connection Pooling is key for many applications to reduce the latency connecting to Azure SQL Database, in this post, I would like to share my lessons learned using connection pooling in PHP.

The first thing was to write a PHP code to connect to the database using different drivers, we have several to connect to Azure SQL Database, but, I choose ODBC and the driver 18 as a common driver for Windows and Linux/MacOS environments.

Also, based on my code, I choose to enable the attribute PDO::SQLSRV_ATTR_DIRECT_QUERY $conn->setAttribute(constant('PDO::SQLSRV_ATTR_DIRECT_QUERY'), true); because I'm not going to reuse the prepare command in my code just only open the connection, run SELECT 1 and close it.

Below you could find the source code:

<?php #print_r(PDO::getAvailableDrivers()); #phpinfo(); $NumberInteractions=1000; $timeAvgDiff=0; echo 'Connecting to the database..' . PHP_EOL; for ($i = 1; $i <= $NumberInteractions; $i++) { $starttime = microtime(true); #$conn = new pdo("sqlsrv:server=tcp:servername.database.windows.net,1433;Database=DatabaseName;ConnectionPooling=true;LoginTimeout=30;ApplicationIntent=ReadOnly;Encrypt=true;MultipleActiveResultSets=false;APP=Test Latency Connection", "username@servername","Password"); $conn = new pdo("odbc:DRIVER={ODBC Driver 18 for SQL Server};server=tcp:servername.database.windows.net,1433;Database=DatabaseName;ConnectionPooling=true;LoginTimeout=30;ApplicationIntent=ReadOnly;MultipleActiveResultSets=false;APP=Test Latency Connection", "username@servername","Password"); $conn->setAttribute(constant('PDO::SQLSRV_ATTR_DIRECT_QUERY'), true); $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); $getResults= $conn->query("SELECT 1 as X"); echo 'Time '.$i. ' Reading data from table - Value:' ; if ($getResults == FALSE) echo (sqlsrv_errors()); while ($row = $getResults->fetch(PDO::FETCH_ASSOC)) { echo ($row['X'] ); } $getResults=null; $conn=null; $endtime = microtime(true); $timediff = $endtime - $starttime; $timeAvgDiff = $timeAvgDiff+$timediff; echo ' (Time): '.($timediff).PHP_EOL; } echo '---------------------------------------------'.PHP_EOL; echo ' Total Time connection + execution time: '.($timeAvgDiff).PHP_EOL; echo ' Average connection + execution time: '.(($timeAvgDiff/$NumberInteractions)).PHP_EOL; echo ' Total number of connections: '.($NumberInteractions).PHP_EOL; echo '---------------------------------------------'.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); } ?>

Windows

Basically, Using ODBC Data Source Administrator for 64 Bits, I need to modify the setting to enable connection pooling

Once I have this, running the application connecting 1000 times, running a SELECT 1 every time and I got the following time spent:

In Linux (ubuntu 20.04)

First, I need to modify the file odbcinst.ini located on /etc/odbcinst.ini to enable connection pooling.

[ODBC]
Pooling=Yes

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1
UsageCount=1
CPTimeout=120

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.1.1
UsageCount=1
CPTimeout=120

Second, running the same PHP code in Linux environment the time spent was:

So, the time spent on both environments were pretty much the same using connection pooling.

Besides that, disabling the connection pooling, I would like to pay attention that connection pooling will help when we stablish more than a connection. Remember that the first connection always will take more time due to several operations needed to cache it.

Also, if you want to add a retry logic per module in your code, you could find here additional information about it - AzureSQLConnectivityChecker/PHP at master · JMNetwalker/AzureSQLConnectivityChecker · GitHub

References

Enjoy!

Windows

In Linux (ubuntu 20.04)

References

Leave a Reply Cancel reply