Monitoring the query execution performance of Bold BI with Grafana

Published:

Introduction

Monitoring the performance of dashboards is crucial for maintaining a seamless user experience in business intelligence applications. Bold BI dashboards can be effectively monitored for performance issues, such as longer rendering times, by utilizing the logs stored in the Bold BI meta database server. Grafana, a popular monitoring tool, can be used to track the execution timing of each widget query and set up alerts for when performance falls outside of acceptable thresholds.

Setting Up Grafana for Bold BI Monitoring

Step 1: Connect Grafana to Bold BI Meta Database

To monitor Bold BI dashboard performance with Grafana, a connection between Grafana and the Bold BI meta database server must be established. This requires configuring Grafana with the correct database credentials and settings.

Download and install Grafana for Windows by following the instructions provided in the official Grafana documentation.
Once installed, access the Grafana user interface by navigating to http://localhost:3000 in your web browser.
Log in to Grafana using the default credentials, which are both admin for the username and password.
After logging in, add a data source used by the Bold BI application. In the “Search for data source” field, enter the name of the database you want to use (MySQL, PostgreSQL, or MSSQL)
Enter the required details for the data source and save the configuration. You will receive a message indicating that the database connection is successful.

Step 2: Create a Dashboard in Grafana and Define Metrics and Queries

Once the connection is established, create a new dashboard in Grafana specifically for monitoring Bold BI performance. Add panels to the dashboard that will display the relevant metrics, such as widget query execution times. For each panel, define the metrics that you want to monitor. This will typically involve writing SQL queries that extract performance data from the Bold BI meta database logs. The queries should be designed to retrieve the execution times of widget queries.

Navigate to the Dashboard section, select the import option, and upload the file below based on your database type, such as opsql, mssql, or mysql, in order to integrate the metrics table widget. This widget will display information such as query details and execution times.

BoldBI_QueryMetrices_Mysql.json

BoldBI_QueryMetrics_Mssql.json

BoldBI_QueryMetrics_Psql.json

Note: Once the dashboard is imported, if it shows no data in the widgets, simply edit the widget and rerun the query to automatically display the data.

Step 3: Enable Email Notification and Set Up Alerts

Grafana provides a robust alerting system that can notify users when certain conditions are met. Set up alerts for each panel to notify you when widget query execution times exceed the predefined threshold limits. Configure the alerting rules according to your requirements, specifying the conditions, message, and notification channels. To ensure you are promptly notified of any issues, configure alerts in Grafana by following these steps:

Enabling Email Notifications

To enable Grafana to send email notifications, you’ll need to configure the SMTP settings:

Locate the default.ini file in your Grafana installation directory, typically found at C:\Program Files\GrafanaLabs\Grafana\config\.
Open the default.ini file with a text editor.
Find the [smtp] section in the file.
Enter the SMTP settings for your email server, including the username, and password.

Note : You can find the password for the Gmail app by referring to this Link
Save the changes to the default.ini file.
Go to the services app and right click on Grafana and give restart.

Setting Up Alert Queries and Contact point.

In your Grafana dashboard, navigate to Alert rules.
Enter the alert rule name.
To create an alert based on query execution time, you can use the following SQL query based on your database type, whether it is MySQL, PostgreSQL, or MSSQL in the Define query and alert condition section.

MySQL

In MySQL, you can convert the query execution time to seconds using the TIME_TO_SEC function combined with TIMEDIFF and STR_TO_DATE. Here’s an example query that demonstrates this conversion:
```
SELECT 
    queryexecutiontime,
    TIME_TO_SEC(
        TIMEDIFF(
            STR_TO_DATE(queryexecutiontime, '%H:%i:%s.%f'),
            '00:00:00'
        )
    ) AS total_seconds
FROM 
    boldtc_querymetrics
ORDER BY 
    queryexecutiontime
LIMIT 10;
```
This query will select the execution time and its equivalent in total seconds from the boldtc_querymetrics table, ordering the results by the execution time and limiting the output to 10 rows.

PostgreSQL

In PostgreSQL, the process is slightly different. You can use the EXTRACT function to retrieve the epoch from an interval, which represents the total number of seconds. Here’s how you can write the query:
```
SELECT 
    EXTRACT(EPOCH FROM 
        (substring(queryexecutiontime, 1, 2) || ' hours ' ||
         substring(queryexecutiontime, 4, 2) || ' minutes ' ||
         substring(queryexecutiontime, 7, 2) || ' seconds')::interval
    ) AS total_seconds
FROM 
    boldtc_querymetrics
ORDER BY 
    id DESC;
```
This query extracts the total seconds from the queryexecutiontime field by first constructing an interval string and then casting it to an interval type before extracting the epoch.

MS SQL Server (MSSQL)

For MS SQL Server, you can use the DATEDIFF function to calculate the difference in seconds between the queryexecutiontime and midnight ('00:00:00'). Here’s the MSSQL query:
```
SELECT 
    queryexecutiontime,
    DATEDIFF(SECOND, '00:00:00', CAST(queryexecutiontime AS TIME)) AS total_seconds
FROM 
    boldtc_querymetrics
ORDER BY 
    queryexecutiontime
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
```
This query will return the execution time and the total seconds for each record, ordering the results by the execution time and fetching only the next 10 rows.
After setting up the query, you need to define the threshold for when an alert should be triggered based on the query execution time.

Note: The timing for the image above will be set to 1 minute (60 seconds). You can adjust the timing in seconds according to your needs.
Now, configure the evaluation behavior to determine how the alert rule will be assessed.
In contact point, select View or create contact points.
Click the new contact point to add your email for receiving alerting notifications.
You can also test the notifications by clicking the test icon.
After adding a contact point, return to the Alert rules section, select the created contact point, and save the alert rule.
This rule will send you an email notification if the Bold BI application query execution exceeds the specified threshold value from Step4.

Note: You can refer this document for more details about Grafana Alerting.

Step 4: Test and Adjust

After setting up the monitoring and alerting system, it’s important to test it to ensure that it’s working as expected. Monitor the alerts and adjust the threshold limits and conditions as necessary to fine-tune the performance monitoring.

Conclusion

By leveraging Grafana’s monitoring capabilities, you can keep a close eye on the performance of Bold BI dashboards. Timely alerts will enable you to address any issues promptly, ensuring that your dashboards continue to provide quick and efficient insights to users.

Additional References

Was this article useful?

Dislike

Written by Priya Sunil Kumar

Updated

Comments (0)