Migration from AWS EC2 MySQL DB to a Azure MySQL Database resulted in a very poor slow performance

Question

We had a very small MySQL DB of 150MB, which was running very fast with AWS t2.large database as Self Hosted.

H/W spec: Azure: 2vCPU, 10GB memory, AWS: 2 vCPU, 8GB Memory.

However, the company decided to move the AWS DB to the Azure Managed MySQL, and keep the PHP application in the EC2 for now.

H/W both servers equal in fact Azure had 2GB extra memory, with both have vCPU = 2,

Once moved to the Azure we saw massive performance degradation,

Example: In AWS ajax request took 15 sec, In Azure same ajax request takes 2.5 minutes.

Azure MySQL DB - the average %CPU and % Memory have never reached 50% of their allocated resource, so the specs do not seem to be the problem.

As per the question below PHP on Azure App Service slow performance when connected to Azure Database for MySQL

We did turn the Azure MySQL DB server parameter redirect_enabled to ON and enabled Enforce SSL with TLS 1.2.

And installed the "mysqlnd_azure" extension as well, usinge

sudo pecl install mysqlnd_azure

As per this below link we check this has any effect on the connection but it seems hostname is the same with no change

https://learn.microsoft.com/en-us/azure/mysql/howto-redirection#confirm-redirection

In the below "Test code" if redirection is set to ON it gives me a error "Connect error (2002):" If redirection set to off in the ini file, Test code goes to the else with same HOST name, which suggest that redirection is not working, However can see mysqlnd_azure extension is loaded and table names are shown correctly in the resultset

<?php
$host = '<yourservername>.mysql.database.azure.com';
$username = '<yourusername>@<yourservername>';
$password = '<yourpassword>';
$db_name = 'testdb';
 echo "mysqlnd_azure.enableRedirect: ", ini_get("mysqlnd_azure.enableRedirect"), "\n";
 $db = mysqli_init();
 //The connection must be configured with SSL for redirection test
 $link = mysqli_real_connect ($db, $host, $username, $password, $db_name, 3306, NULL, MYSQLI_CLIENT_SSL);
 if (!$link) {
    die ('Connect error (' . mysqli_connect_errno() . '): ' . mysqli_connect_error() . "\n");
 }
 else {
   echo $db->host_info, "\n"; //if redirection succeeds, the host_info will differ from the hostname you used used to connect
   $res = $db->query('SHOW TABLES;'); //test query with the connection
   print_r ($res);
   $db->close();
 }
?>

Application is done with Code-igniter and PEAR.

Does anyone have a similar experience when running Code Igniter with Azure MySQL DB with AWS EC2? and Any suggestions to fix this issue?

Answer 1

Your most urgent problem is that you do not know.

So the very first thing to do is to know -- to investigate where exactly things go awry.

For example (but you'll need to do more than this):

function after($msg) {
    global $tsts;
    if (isset($tsts)) {
        $time = floor((microtime(true)-$tsts)*1000);
    } else {
        $time = 0;
    }
    print "{$msg}: {$time} ms\n";
    $tsts = microtime(true);
}
    
after("Start");
$db = mysqli_init();
after("mysqli_init");
after("FINISH");

$link = mysqli_real_connect ($db, $host, $username, $password, $db_name, 3306, NULL, MYSQLI_CLIENT_SSL);
after("mysqli_real_connect");

$res = $db->query('SHOW TABLES;'); //test query with the connection
after("query");
// MUCH BETTER: a call to *execute* the query, and a different call
// to *retrieve* results, and you time *both* operations! See below

$db->close();
after("close");

This will tell you exactly where the time goes during the call. You can for example modify the after() function so that it immediately returns unless the REMOTE_ADDR is your exact workstation, otherwise it will silently save its data in an array, and when $msg is "FINISH" it will dump the array to a file. This way, seeding after() calls everywhere in your code, you will be able to see where the time costs are.

(This is a very poor man's profiling; xdebug and kcachegrind would be much better and precise choices, but have more taxing requirements and we just want to have a quick look at things).

This will tell you for example:

• is the connection slow?
• is the query slow?
• is the result retrieval slow?
• is formatting the results slow? (unlikely but, hey, it happens: different JSON libraries and lo!, you're stuck)
• is sending results slow? (very unlikely, but Azure can be (mis)configured almost every which way. I have a machine that is intentionally slowed this way to allow debugging specific UI issues).
• etc.

If the problem is in the connection, you'll study protocols, firewalls, possible alternative connections, possibly moving the whole app into Azure and leave only a redirection stub in EC2, etc etc.

If the problem is the query you'll need to check memory, indexes, possible thrashing, possible MySQL misconfigurations.

If it is the transfer, experiment with protocol compression, or investigate connection bottlenecks, or negotiate a better connection policy with either Amazon or Azure. Nothing can avail you if you have a 57600 bps connection, you need to take the trouble to whoever supplies you the bandwidth.

Without detailed timing information, you're stumbling in the dark.

Small steps

• Begin with connecting to a simple bespoke service set up on the Azure machine. You might turn off the database completely, and instead set up a web server on MySQL's port 3306. Then from EC2 you would ask for "https://azure.host/index.html" and time that. If the whole slowness appears there, then you know the problem can only be in the network.

• Then fire up a test database with a single table of one record. Again, if the slowness appears there, it can only be the MySQL network configuration.

• Add a large table and run a query that will perform a full table scan, and return nothing. This stresses the buffer pool and disk I/O.

• Return the large table, an increasing number of records. This will test weird networking and firewalling problems (I had a MTU mismatch partially "remediated" by a borked Mikrotik once, boy was that a mess to investigate) as well as low bandwidth problems. Here you can test with compression both on and off.

• Finally run the test with a complex query. A slowness here might indicate problems either in the mysqld.conf (compare it with the one on EC2) or even some silly gotcha like forgetting to transfer indexes or even triggers (don't laugh, I did it once. Still smarts).