PHP on Azure App Service slow performance when connected to Azure Database for MySQL

Question

I am hosting a PHP 7.2 website written on CodeIgniter 3 framework on Linux Azure App Service (plan P1v2). The database is MySQL 5.7 running on Azure Database for MySQL (General Purpose, 2vCores, 5GB storage) with SSL disabled.

When I browse my website, it literally takes about 8-9 seconds to load where browser would stay the white page and display everything at once after finished loading. (most of website contents are pulled from database)

I have been monitoring both App Service and MySQL DB and the average %CPU have never reached 50% of their allocated resource, so the specs does not seem to be the problem.

My outsourced developer team has their own testing environment (not on Azure) and they said it loads almost instantly on their environment, so they are blaming Azure.

I then noticed that Azure Advisor was telling me two things about this database where the impact is High

Improve MySQL connection latency Our internal telemetry indicates that your application connecting to MySQL server may not be managing connections efficiently. This may result in higher application latency. To improve connection latency, we recommend that you enable connection redirection. This can be done by enabling the connection redirection feature of the PHP driver.

Improve MySQL connection management Our internal telemetry indicates that your application connecting to MySQL server may not be managing connections efficiently. This may result in unnecessary resource consumption and overall higher application latency. To improve connection management, we recommend that you reduce the number of short-lived connections and eliminate unnecessary idle connections. This can be done by configuring a server side connection-pooler, such as ProxySQL.

I am now working on enabling the connection redirection to reduce latency but still not successful as I am not familiar with CodeIgniter so I need to wait for the developers to fix it.

My question is: what is/are the cause(s) of this performance issue? Are those two recommendations really the causes of this problem?

Both App Service and database are in the same region. Do they communicate locally like some kind of LAN connection? I hope the connection from App Service would not go out through the internet then back to Azure DB, causing slow performance.

Thanks.

Answer 1

I have fixed this issue, turns out the Azure Advisor was right! I have enabled connection redirection and now my website finishes loading in 3 seconds. Here are the what I did.

TL;DR Configure your app to use SSL when connecting to database and also put the mysqlnd_azure extension file in your deployed app, set Azure Web App's App Settings to read the ini file that make it loads the extension. Then set Azure MySQL DB server parameter redirect_enabled to ON and enable Enforce SSL with TLS 1.2. Restart the app.

Beware: Make sure that setting Enforce SSL is the last thing you do, otherwise it will reject all non-SSL connections and your app wouldn't work at all.

---

Update Jan 2021

I have just found out that it seems like Microsoft has already built-in the required mysqlnd_azure extension to Azure App Service. The majority of steps below are not required anymore.

You should first test if the extension has already been loaded by following step 7. If mysqlnd_azure extension is on the list then you can continue to step 8-10. You still need to follow step 1. to configure SSL certificate for your database though.

Afterwards, confirm if the database connection redirection is in use by following the sample code link in the reference at the end of this answer. You should see the text mysqlnd_azure.enableRedirect: preferred, followed by text in this format: [random text and numbers].[random text and numbers again].[your azure region].worker.database.windows.net.

If the text still shows your MySQL hostname you used, e.g. mydatabase.mysql.database.azure.com, then connection redirection is not in use. Check again.

Original answer below this line.

---

1. Configure your app to use SSL when connecting to database. In CodeIgniter, it's the database.php file in /application/config/. Make sure all MySQL drivers that you use have SSL enabled. Download the SSL certificate from https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem and store it in your app's directory e.g. /cert/BaltimoreCyberTrustRoot.crt.pem

    /*Snippet from database.php*/
    $db['production'] = array(
        'dsn'   => '',
        'hostname' => getenv("DB_HOST"),
        'username' => getenv("DB_USER"),
        'password' => getenv("DB_PWD"),
        'database' => getenv("DB_NAME"),
        'dbdriver' => 'mysqli',
        'dbprefix' => '',
        'pconnect' => FALSE,
        'db_debug' => FALSE,
        'cache_on' => FALSE,
        'cachedir' => '',
        'char_set' => 'utf8',
        'dbcollat' => 'utf8_general_ci',
        'swap_pre' => '',
        'encrypt' => [
            'ssl_key'    => NULL,
            'ssl_cert'   => NULL,
            'ssl_ca'     => '/home/site/wwwroot/cert/BaltimoreCyberTrustRoot.crt.pem',
            'ssl_capath' => NULL,
            'ssl_cipher' => NULL,
            'ssl_verify' => FALSE
        ],
        'compress' => FALSE,
        'stricton' => FALSE,
        'failover' => array(),
        'save_queries' => TRUE
    );
   
    /*Snippet for PHP (PDO)*/
    <?php
    define('CONN_HOST', getenv("DB_HOST"));
    define('CONN_DATABASE', getenv("DB_NAME"));
    define('CONN_USER', getenv("DB_USER"));
    define('CONN_PASSWORD', getenv("DB_PWD"));
    define('CONN_OPTION', array(
        PDO::MYSQL_ATTR_SSL_CA          =>     '/home/site/wwwroot/cert/BaltimoreCyberTrustRoot.crt.pem',
        PDO::MYSQL_ATTR_INIT_COMMAND    => "SET NAMES utf8"
    ));
   

2. Install mysqlnd_azure extension by following this link https://azureossd.github.io/2019/01/29/azure-app-service-linux-adding-php-extensions/ or if you just want the .so extension right away, download it from http://www.mediafire.com/file/g6mzeld0wnqedw0/mysqlnd_azure.so/file otherwise build it yourself from https://github.com/microsoft/mysqlnd_azure

3. Put mysqlnd_azure.so in your app's directory. E.g. /bin/mysqlnd_azure.so Then deploy your code to Azure

4. SSH into your App Service, create a directory in /home/site called ini. Create an .ini file with any name e.g. settings.ini

5. Use vi or nano to edit that ini file and paste these lines

   extension=/home/site/wwwroot/bin/mysqlnd_azure.so
   mysqlnd_azure.enableRedirect = on
   

6. Tell your app to load the mysqlnd_azure extension by adding an application settings called PHP_INI_SCAN_DIR with value /usr/local/etc/php/conf.d:/home/site/ini

7. Azure will force restart your app after saving the app settings. After that you can check if the extension has been loaded by SSH again and run php -m to see all loaded extensions. If you see mysqlnd_azure in the list, you're on the right path! If not, this command should tell why it couldn't load that extension

8. On Azure Portal, navigate to your Azure MySQL DB and set server parameter redirect_enabled to ON

9. Under Connection Security, enable Enforce SSL connection and choose TLS 1.2

10. Restart the app again.

If you use sslmode=verify-ca or sslmode=verify-full, you need to switch to the new SSL certificate on October 26, 2020. Refer to https://learn.microsoft.com/en-us/azure/mysql/concepts-certificate-rotation Update: Microsoft has extended the deadline for root certificate deprecation till February 15, 2021.

If you have any deployment slots, make sure to do step 4-7 for every slot.

References:

• Sample code to confirm if connection redirection is working https://learn.microsoft.com/en-us/azure/mysql/howto-redirection#confirm-redirection
• App Service Linux - Update PHP Settings https://azureossd.github.io/2019/01/29/azure-app-service-linux-update-php-settings/
• Configure SSL connectivity in your application to securely connect to Azure Database for MySQL https://learn.microsoft.com/en-us/azure/mysql/howto-configure-ssl