Unreasonable ammount of queries and connections to Azure Database for MySQL (flexible server)

Question

Unreasonable ammount of queries and connections to Azure Database for MySQL (flexible server)

Elias Bonnici 1

For some strange reason my Azure MySQL server seems to be receiving a huge ammount of queries and I don't know why.

The only thing I've done is connecting my NodeJS express app to the MySQL server without sending any queries to it. Doing that I see (in azure portal) that the MySQL server has around 30 established connections and that number conitinues to grow for every minute (the same holds for queries).

The number of established connections and queries also seems to be growing even if I shut down the NodeJS express app. For instance, when I start the Azure MySQL server and let it run for a couple of minutes (with the NodeJS app being shut down) I have about 500 queries.

Does anybody know what could possibly be the reason for this problem? I have tried to create logs but can't see anything weird there!

Oury Ba-MSFT 20,926 Reputation points Microsoft Employee Moderator

2021-08-17T14:46:07.143+00:00

Hi @Elias Bonnici Thank you for posting your question. What error are you getting with too many MySQL queries.?

There are two ways to end a connection. Terminating a connection gracefully is done by calling the end() method:

connection.end(function(err) {
// The connection is terminated now
});
This will make sure all previously enqueued queries are still before sending a COM_QUIT packet to the MySQL server. If a fatal error occurs before the COM_QUIT packet can be sent, an err argument will be provided to the callback, but the connection will be terminated regardless of that.

An alternative way to end the connection is to call the destroy() method. This will cause an immediate termination of the underlying socket. Additionally destroy() guarantees that no more events or callbacks will be triggered for the connection.

connection.destroy();
https://github.com/mysqljs/mysql#terminating-connections

Regards,
Oury
Elias Bonnici 1 Reputation point

2021-08-18T07:09:44.107+00:00

Hi Oury!

I am actually not receiving any errors. The problem I have is that Azure Portal shows that I have a lot of active connections and queries but when connecting to the Azure Mysql server from the command-line client it shows the opposite.

Using commands such as "show processlist;" to get all active connections it all looks fine. I do get the numbers that I am expecting. But when I am in Azure Portal and looking at the number of active connections, it is totally different.

I am using a connection pool that automatically drops connections after a given time so I am not sure if the problem has to do with terminating connections :(

Regards,
Elias

1 answer

Your answer

Oury Ba-MSFT 20,926 Reputation points Microsoft Employee Moderator

2021-08-17T14:46:07.143+00:00

Hi @Elias Bonnici Thank you for posting your question. What error are you getting with too many MySQL queries.?

There are two ways to end a connection. Terminating a connection gracefully is done by calling the end() method:

connection.end(function(err) {
// The connection is terminated now
});
This will make sure all previously enqueued queries are still before sending a COM_QUIT packet to the MySQL server. If a fatal error occurs before the COM_QUIT packet can be sent, an err argument will be provided to the callback, but the connection will be terminated regardless of that.

An alternative way to end the connection is to call the destroy() method. This will cause an immediate termination of the underlying socket. Additionally destroy() guarantees that no more events or callbacks will be triggered for the connection.

connection.destroy();
https://github.com/mysqljs/mysql#terminating-connections

Regards,
Oury
Elias Bonnici 1 Reputation point

2021-08-18T07:09:44.107+00:00

Hi Oury!

I am actually not receiving any errors. The problem I have is that Azure Portal shows that I have a lot of active connections and queries but when connecting to the Azure Mysql server from the command-line client it shows the opposite.

Using commands such as "show processlist;" to get all active connections it all looks fine. I do get the numbers that I am expecting. But when I am in Azure Portal and looking at the number of active connections, it is totally different.

I am using a connection pool that automatically drops connections after a given time so I am not sure if the problem has to do with terminating connections :(

Regards,
Elias

Answer 1

KalyanChanumolu-MSFT 8,351

@Elias Bonnici Our apologies for the inconvenience caused to you.

Please Configure audit logging to get more details on each connection, its origin, query text etc.
This should help you understand which other clients are connecting to your database.

You can then query Log Analytics using the below query to fetch the required information

AzureDiagnostics  
| where Category == ‘MySqlAuditLogs’  
| order by TimeGenerated desc  
| project TimeGenerated, event_class_s, event_subclass_s, user_s, external_user_s, connection_id_d, thread_id_d, event_time_t, sql_text_s, db_s, table_s, error_code_d

Share via

Unreasonable ammount of queries and connections to Azure Database for MySQL (flexible server)

1 answer

Your answer