Create a Monitoring Dashboard for Azure Database for MySQL

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

A dashboard visually summarizes all the important metrics you have selected to track, to give you a quick-and- easy overview of where everything stands. With real-time Dashboard, it’s a live view of exactly how your application information is performing. This helps in better decision making and helps in taking proactive actions. These can help in the downtime.  

  

This blog explain how you can use the Azure Log Analytics to create a custom Dashboard for Azure Database for MySQL 

 

This is a starter guide. You can use these ideas and customize as per your need.  

  

Steps 

1. Create a Custom Azure Dashboard in the portal  
2. Configure the Databases for Data Collection 
3. Send this information to Log Analytics 
4. Select the objects which you would like to see in Dashboard. 
   1. Add the Metrics selected. 
   2. Write Kusto Query to get the required information  
5. Tag it to the Custom Dashboard.    

 

1. Create Custom Azure Dashboard 

Log in to the Azure portal  

Select Dashboard to open your default dashboard.  

Select New Dashboard 

Give the Name for your Dashboard and Say Done Customizing for now 

 

 

 

For more details on how to create a Dashboard you can refer to: 

https://docs.microsoft.com/azure/azure-portal/azure-portal-dashboards 

  

When you create a dashboard, it is private by default, which means you are the only person who can see it. To make it visible to others, use the Share button that appears alongside the other dashboard commands. You are asked to choose a subscription and resource group for your dashboard to be published to.  

Select the Subscription Name where you would like to share and select Publish , below 

2. Configure the Databases for Data Collection 

• Metric are available by default. There is no specific configuration needs to be done. The Metrics available for Azure Database for MYSQL are documented in the article below: https://docs.microsoft.com/azure/mysql/concepts-monitoring   

• In case of extra data like audit logs , slow query logs , query store etc. we would have to configure the settings in the server parameters of the corresponding Azure Database for MySQL/MariaDB 

• Slow query logs : By default the slow query log is disabled. To enable it, set slow_query_log to ON. There are other parameters which you can adjust. Details in the link https://docs.microsoft.com/azure/mysql/concepts-server-logs  
• Audit logs : By default the audit log is disabled. To enable it, set audit_log_enabled to ON. There are other parameters which you can adjust. Details in the link https://docs.microsoft.com/azure/mysql/concepts-audit-logs 

Depending on what needs to be seen in the dashboard,  we can configure the service.  Once we configure the required service parameters, we would need to configure Diagnostic logs. 

 

3.Send this information to Azure Monitor Logs (Log Analytics) 

 

Azure Database for MySQL is integrated with Azure Monitor diagnostic settings. Through this service, logs and metrics can be emitted to Azure Monitor Logs, Event Hubs, or Azure Storage. For our scenario we will send to Azure Monitor Logs (also known as Log Analytics). 

 

 You would see under each Azure Database service under Monitoring session 

Select  Diagnostics setting and then select Add diagnostic setting 

Select the necessary Data you would like to upload to the Azure Monitor Logs .

If you have not created a Log Analytics work space, you will have to create one.  You can refer to: https://docs.microsoft.com/en-us/azure/azure-monitor/learn/quick-create-workspace  

4. Select the objects which you would like to see in Dashboard. 

Add the Metrics selected. 

   

Go to the Dashboard again from the portal  

Select the custom Dashboard we created initially and click on Edit  

Click on Add Metrics Chart 

Click on done Customizing and once you are back on the page go ahead and click "Edit in Metrics" 

Select  Add Metric  

You can add multiple metrics for the same MySQL server in one chart. You could also have multiple MySQL servers listed for the same metric. 

Once the necessary metrics are added, Select Save to Dashboard.

In the above we have added two Azure Database for MySQL server's CPU Avg.  

Following the same steps, you can create multiple Chart of your choice. 

 

For our example I took Avg CPU, Avg Memory, Max Storage, Max Active Connections. 

Now let’s see how we have bring the other data we send to log analytics to the dashboard. I will take here one example to get data from the slow query logs  

 For the same we would have to first go to Monitor from the portal. Select Logs and click on Get Started  

Select the scope. (I selected MySQLserver ) (This is only if you doing for first time). Later it can directly take you to Kusto Query Explorer 

  

Once you have finalized on the query you can run and then once you have result you should be able to pin to dashboard.

 

I have picked this query from Slow query logs link "Queries longer than 10 seconds on a particular server" 

 

 AzureDiagnostics 

| where LogicalServerName_s == '<Server Name>' 

| where Category == 'MySqlSlowLogs' 

| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s  

| where query_time_d > 10 

   

Once Pin to Dashboard is selected, you will be able to see the Dashboard if it is Shared one. 

 

I followed the same procedure for Audit and Query Store to get that information pin to Dashboard.  

  

Slow query distribution 

  

AzureDiagnostics 

| where LogicalServerName_s == '<Server Name>' 

| where Category == 'MySqlSlowLogs' 

| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s  

| summarize count() by LogicalServerName_s, bin(TimeGenerated, 5m) 

| render timechart 

    

Audit for connection to DB 

  

AzureDiagnostics 

| where LogicalServerName_s == '<Server Name>' 

| where Category == 'MySqlAuditLogs' 

| where event_subclass_s in ('CONNECT','DISCONNECT') 

| project TimeGenerated, LogicalServerName_s, event_class_s,event_subclass_s,user_s 

  

Database Execution Error 

  

AzureDiagnostics 

| where LogicalServerName_s == '<Server Name>' 

| where Category == 'MySqlAuditLogs' 

| where event_subclass_s == "ERROR"  

| project TimeGenerated, LogicalServerName_s, event_class_s,event_subclass_s,user_s 

    

Once you pin this to the Dashboard you can go ahead and edit it. You can make the layout look better. 

The Dashboard would like this.

Hope this helps  

 Sudheesh Narayanaswamy