Reading this article you will learn how to create a Customized Performance Report for one of your MySQL™ Servers using HoneyMonitor, a GUI for MySQL™ Server Administration, Monitoring & Performance Tuning.
Contents
- Introduction
- Step 1 - Choosing a File Name and Opening the Report Designer
- Step 2 - Editing the SQL Queries used by the Report
- Step 3 - Editing the Charts contained in the Report
- Step 4 - Other minor changes
- Step 5 - Previewing our Customized Report
- Few Considerations and Useful Tips
- Actual Date Interval
- Reopening the Customized Report
- Getting Ready for the Creation of a new Customized Report: Editing the Customized Report Template
- Other Resources
- Conclusion
- About the Author
Introduction
HoneyMonitor includes about 10 ready-to-use Performance Reports (Temporary Tables, Query Cache, InnoDB, …) which can help you monitoring and tuning your MySQL™ Server.
The total number of Performance Charts included in the Reports is about 90.
With a Customized Report you can choose to monitor a specific variable not included in the standard reports and create a Report that better fits your need. To allow you creating a Customized Performance Report, HoneyMonitor integrates a powerful Report Designer, FastReport™.
In this short Tutorial, we will see how to create a simple Customized Report, which contains one chart on which we can see how a specific performance ratio is changing over time.
In general, you can add many series on the same chart, or many charts in the same report. Procedures explained in this article can easily be extended to more complex cases.
Information included in the article apply to:
- HoneyMonitor Audit Pro Edition
- HoneyMonitor Network Edition
Before going on with the reading, please make sure that the version of your MySQL™ Server is 5.1 or higher. For the complete list of MySQL™ versions supported by the Audit System included in HoneyMonitor, please refer to the HoneyMonitor’s Reference Manual.
To simplify the creation of your first customized Report, we have created a Report Template and we have included it in the HoneyMonitor installation. Each time you create a customized Report, the Template will be your starting point; it already includes some standard queries and one chart ready to be modified by you.
After the creation of your first customized Report, in order to speed-up the creation of your next Reports, you can edit the Report Template. We will came back to this point, later, in the last subsection of this article.
Step 1 - Choosing a File Name and Opening the Report Designer
To create a Customized Performance Report, please click on the menu Auditing / Reports / Custom Report (Fig. 1).
Fig. 1: Creating a Customized Performance Report - Step 1 - Image 1.
You will be prompted by a Windows™ Dialog to choose the name and the path of your new Report. Please choose a Filename and click on the “Ok” button of the dialog (Fig. 2).
Fig. 2: Creating a Customized Performance Report - Step 1 - Image 2.
FastReport™ will be opened (Fig. 3).
Fig. 3: Creating a Customized Performance Report - Step 1 - Image 3.
Step 2 - Editing the SQL Queries used by the Report
In the second Step of this guide we would like to edit the SQL Queries which will be used by the Report Designer to generate the (prepared) Report.
Please, click on the “Data” Tab of the Report Designer, and then double-click on the icon “SQL_Query1″. A dialog containing a SQL Query will be opened (Fig. 4).
Fig. 4: Creating a Custom Performance Report - Step 2 - Image 1.
We will edit this query to select the data we would like to include in the Report.
The basic idea is the following: we have an audit table which contains the values of many system and status variables as well as the values of many derived variables (ratios). This audit table is populated by the Audit System included in HoneyMonitor. In our Customized Report, we would like to select one of these variables - or create a new customized ratio - in order to see how it is changing over time.
Let’s do the simpler case and suppose we want monitoring the variable “Inserts_per_Second” (for a list of available columns of the audit table, please goto
http://www.honeysoftware.com/products/honeymonitor/auditpro/available_variables.html).
We can proceed replacing the line
### `hs_audit_schema`.`status_system_dpm_table`.`your_variable_name`,
with the line
` hs_audit_schema`.`status_system_dpm_table`.`Inserts_per_Second`,
(change `Inserts per Second` with the variable your prefer; you can also select other variables if you want to add more series in the chart or you want to add more charts in the Report).
Now we would like to choose a time interval in order to retrieve only the data of a specic period.
Let’s assume we want monitoring the period from ‘2008-10-10 00:00:00′ to ‘2008-11-10 00:00:00′.
Hence we replace the line
WHERE
`hs_audit_schema`.`status_system_dpm_table`.`g_timestamp` > :min_limit
AND
`hs_audit_schema`.`status_system_dpm_table`.`g_timestamp` < :max_limit
with the line
WHERE
`hs_audit_schema`.`status_system_dpm_table`.`g_timestamp`
> '2008-10-10 00:00:00'
AND
`hs_audit_schema`.`status_system_dpm_table`.`g_timestamp`
< '2008-11-10 00:00:00'
The result would be similar to that shown in Fig. 5
Fig. 5: Creating a Customized Performance Report - Step 2 - Image 2.
Let’s now edit the WHERE clause of “SQL_Query2″ as we did for “SQL_Query1″. The result would be similar to that shown in Fig. 6.
Fig. 6: Creating a Customized Performance Report - Step 2 - Image 3.
In general, you don’t need to edit “SQL_Query3″. You can use this query, for example, to retrieve additional information about the server.
The query you will find pre-loaded if you double-click on the “SQL_Query3″ icon is shown in Fig. 7.
Fig. 7: Creating a Customized Performance Report - Step 2 - Image 4.
Step 3 - Editing the Charts contained in the Report
The Template of the Customized Report includes one chart. You can add more charts in the Report selecting the chart you would duplicate and using the standard Copy (CTR+C) and Paste (CTR+V) commands.
In this Step we will see how to include in the chart the variable “Inserts_per_Second” retrieved by the query edited in the last step of this Guide.
Please, click on the “ReportPage” Tab, select the chart and click on the right button of your mouse. A popup menu will be displayed; if you click on the “Edit” entry (Fig. 8) the “Chart Editor” will be opened.
Fig 8: Creating a Customized Performance Report - Step 3 - Image 1.
Axis Name
Firstly, you would change the name of the chart.
To change this property, as well as other properties, you can use the “Property Grid” of the “Chart Editor” (Fig. 9).
Fig. 9: Creating a Customized Performance Report - Step 3 - Image 2.
Please expand the “BottomAxis” item and then scroll down until you see the entry “Title”. Select it and edit the “Caption” Property as shown in Fig. 10 and 11.
Fig. 10: Creating a Customized Performance Report - Step 3 - Image 3.
Fig. 11: Creating a Customized Performance Report - Step 3 - Image 4.
The result would be like that shown in Fig. 12.
Fig. 12: Creating a Customized Performance Report - Step 3 - Image 5.
After editing the chart’s name, make sure to click on another row of the Properties Grid, so that the change will be stored.
Series
Let’s now add a series on the chart.
Please click on the “Fast Line - Series2″ item of the “Chart” Tree and then select the “Insert_per_Second” field from the “Y” combo-box contained in the “Values” frame (Fig. 13).
Fig. 13: Creating a Customized Performance Report - Step 3 - Image 6.
Hence click on the “Ok” button of the “Chart Editor” and save your Report by clicking on the File / Save menu.
Step 4 - Other minor changes
We are almost ready to preview our Customized Report. You probably would edit some labels or do other minor changes in the Report Template.
This is the right moment for such activities (Fig. 14).
Fig. 14: Creating a Customized Performance Report - Step 4 - Image 1.
Step 5 - Previewing our Customized Report
You can now click on the “Preview” button of the Designer toolbar to open the prepared report. If the date interval you have chosen returns records, you will see a line representing the trend of the “Inserts_per_Second” variable in the chosen time interval.
In the example created for this tutorial, that variable has the trend shown in Fig. 15.
Fig. 15: Creating a Customized Performance Report - Step 5 - Image 1.
Once the (prepared) Report has been created, you can print it or export it in PDF.
Note: data used for this tutorial have been created, on a local server, using the following method. I have created a simple application which performed a certain number of Insert queries each second, and then, on alternate days, I switched on and switched off another similar application in order to reproduce a simple stair chart.
Few Considerations and Useful Tips
Actual Date Interval
In our example, we have chosen the interval [’2008-10-10 00:00:00′, ‘2008-11-10 00:00:00′].
In general, we can’t be sure that the audit table contains at least one row for the initial date and one row for the final date. That’s why, in all our Performance Reports, we have included additional information about the date of the first and the last records used by the chart, which in general can be di fferent from those specified in our SQL queries, as you can see from Fig. 16.
Fig. 16: Creating a Customized Performance Report - Date Issue.
Reopening the Customized Report
The next time you want open your Report, to edit it or just to recreate the (prepared) Report for a diff erent period of time, you can use the menu Utility / Edit Report (Designer), Fig. 17.
Fig. 17: Reopening the Report - Image 1.
You can also add a link to the “Server Object List” to have a more elegant way to reopening the Report just created (Fig. 18).
Fig. 18: Reopening the Report - Image 2.
Getting Ready for the Creation of a new Customized Report: Editing the Customized Report Template
The Customized Report Template is your starting point for your Customized Report.
You can edit it, if you want, to speed-up the creation of your next Performance Reports.
To do this, click on the menu Utility / Edit Report (Designer), select the file honeymonitor-
installation-path\audit\reports\custom_report_template.fr3 and edit it as you want (do a backup copy of the original file before start editing).
Other Resources
If you want to learn more on HoneyMonitor please visit the Project Home Page, at http://www.honeysoftware.com/honeymonitor, or check out one of the following resources:
Conclusion
In this article, we have seen how to create a Customized Report for monitoring the performance of a MySQL™ Server using the Audit System and a powerful Report Designer integrated in HoneyMonitor, a GUI for MySQL™ Server Administration, Monitoring & Performance Tuning.
Here’s a list of other posts you might be interested in:
About the Author
Santo Leto is a two years experience MySQL™ DBA and Developer.
Leader and main programmer of the HoneyMonitor Project, he graduated in physics from Trieste Univeristy, and he lives in Italy, where he works from home.
