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.  

1.Azure portal menu dashboard .png

Select New Dashboard 

2.New dashboard.png

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

3.Done Customizing.png

 

 

 

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.  

4.Share Dashboard.png

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

5.Publish Dashboard.png2. Configure the Databases for Data Collection 

  • 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 

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 

6.Diagonistics Settings.png

Select  Diagnostics setting and then select Add diagnostic setting 

7. Add Diag settinsg.png

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  

8.Select Diag settings.png4. Select the objects which you would like to see in Dashboard. 

Add the Metrics selected. 

   

Go to the Dashboard again from the portal  

1.Azure portal menu dashboard .png

Select the custom Dashboard we created initially and click on Edit  

9. Edit dashboard.png

Click on Add Metrics Chart 

10.Metrics Chart.png

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

11.Edit Metrics.png

Select  Add Metric  

12.Add Metric.png

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.

13.Save to Dashboard.png

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. 

14.All metric chart.png

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  

15.Log Analytics.png

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.

15.Query Log Analytics.png 

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. 

16.Pin to Dashboard.png

 

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.

17.Final.png

Hope this helps  

Sudheesh Narayanaswamy 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.