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

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.somysqlnd_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://docs.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://docs.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://docs.microsoft.com/en-us/azure/mysql/howto-configure-ssl