Revision: 8 - Last Update: September 03 2008

This is the first part of a series of short articles with a how-to approach about MySQL® Performance Monitoring, Tuning & Auditing. We will see the question from a GUI prospective. In particular we will describe which monitoring-oriented features HoneyMonitor, a GUI for MySQL® currently in alpha development, implements.

I will explain how HoneyMonitor let you

1. install an audit database on your server, without the need of using 3th Party Agents nor using remote repository databases
2. enable the auditing and start monitoring your server
3. tuning your server changing a few suggested list of variables to get better performance.

We will use only the 5.1.x series of the Server as we use some Scheduled Events and the Event Scheduler has been added only in the 5.1 branch. In particular we will use MySQL® 5.1.24-rc. We will also do some tests on the 5.1.23-alpha-maria preview and MySQL® 6.0.4-alpha.

As regard HoneyMonitor, we will use the latest alpha version, i.e. 1.0.13-alpha.

Requirements:

1. a MySQL® Server v. 5.1. - no matter of the platform it is running on - with a user with basic privileges (like create database or create and execute routines)
2. a Windows® XP® or Vista® client - where we can install HoneyMonitor.

In this article I will not explain the meaning of the performance variables and metrics. There are several Guides available on the web to get more information about the Derived Performance Metrics. Of course, the first step is to browse the MySQL® Documentation. By the way, you can read all the Ratio’s formulas, as well the description of the MySQL® system and status variables, directly from HoneyMonitor.

You can read more on HoneyMonitor, at the project’s page - www.honeysoftware.com/products/honeymonitor - and for any question you can write me directly at sales@honeysoftware.com.

Contents:

• Downloading & Installing HoneyMonitor
• First start of HoneyMonitor: Edition Choosing, Software Registration, CEIP, Connection Creation
• Introduction to the Performance Audit System
• Installing the Audit Database & Enabling the Auditing
• Audit Options & Audit Statistics
• Conclusion
• What we will see in the next article
• Other Resources
• About the Author

1. Downloading & Installing HoneyMonitor

You can download HoneyMonitor at http://www.honeysoftware.com/download. After the download just unzip the file in a temporary folder and run the Installation Wizard.

Honeymonitor uses direct connection to the Server but for some features it also needs the MyODBC driver. If you haven’t that driver installed on your system, you can install it using the HoneyMonitor’s Installation Wizard.

2. First start of HoneyMonitor: Edition Choosing, Software Registration, CEIP, Connection Creation

After installation the icon ‘HoneyMonitor - Welcome’ is created on your Desktop. As the Installation Wizard installs all the available HoneyMonitor’s Editions (Lite Free, Standard, Enterprise, Network, Pro, Audit Pro), the HoneyMonitor - Welcome Window (Fig. 1) allows you to choose the Edition you want to start.

Let’s click on the ‘HoneyMonitor - Welcome’ icon and choose the Audit Pro Edition. For more information about the HoneyMonitor Editions’ Logic, please refer to the page Editions & Features Matrix of the HoneySoftware Web Site.

HoneyMonitor requires a free web registration. The first time you run the software, a Registration Window will appear (Fig. 2).

To register your copy you can follow the instructions reported in that Window and when you get your Web Key, just past it in the Registration Window and click on the ‘Ok’ button.

After Registration, the Trial Period Information Window should appear (Fig. 3). This is just to inform that you are using a 30-days Trial software. You can click on the ‘Next’ button.

If this is the first time you are running HoneyMonitor, the Customer Experience Improvement Program Window should appear (Fig. 4). You can decide to join the Program and anonymously partecipate to help us improve our software. Wheter decision you take, click on the ‘Ok’ button to go on.

You are now ready to create a connection to the MySQL® Server (Fig. 5). HoneyMonitor can save your connection’s parameters so that the next time you will use the software you will be able to open that connection just double-clicking on its name from the Server Object List or selecting it using the ‘Stored Connection’ combo-list of the Connection Window.

 Fig. 5: New MySQL® Connection

To create a new connection, insert the Server Hostname (or IP Address), the Port the server is listening on, your Username and your Password. Then do a check clicking on the ‘Test Connection’ button. If the connection was succesfully established, just click on the ‘Connect’ button. The Connection Window will be closed and you are ready to use the connection just created.

Of course, you can create as many connections as you need. For more information about connections creation in HoneyMonitor - included instructions on how to create SSH Tunneling Connections or SSL Connections - please refer to the HoneyMonitor’s Getting Starting Guide.

3. Introduction to the Performance Audit System

The connection to the Server has now been established and we are ready to install the performance audit system and start collecting data.

As we will see more in details in other articles of this series, HoneyMonitor includes a Performance Tuning Monitor. When used in the Pro Edition, the Performance Tuning Monitor is just a static monitor: when you open it (or you use its refresh button) it calculates the most common Performance Hit Ratios and Derived Performance Metrics. When used in HoneyMonitor Audit Pro or HoneyMonitor Network Edition, it not also calculates the Ratios but also gives you the possibility to create time-trend charts of those Ratios so that you can see how the performance of your Server are changing over the time. This could be very useful to identify why, for example, your server’s performance is going worse.

The Performance Tuning Monitor, as well the Audit Report Generation Window reads audit data from an audit database - created on your MySQL® Server by the Audit Installation Wizard. Audit data are hence stored on your server rather than on an external repository database server and this gives you full access to the audit data: you can see them or delete them simply when you want.

Audit data are stored in you server by a Scheduled Event. A Scheduled Event simply is a temporal trigger: you define a time interval (when installing the Audit System, or later using the Audit Options Window) and the Event takes care of calculating all the Performance Hit Ratios and Derived Performance Metrics and stores them in the audit database, so that the data can be used for charts’ creation.

At any time you can change the time interval for the data acquisition or pausing the audit Event so that no other data will be stored until you will enabled the audit Event once again.

As a Populating Event takes care of storing audit data, a Pruning Event takes care of deleting old audit data. You can set the interval for data pruning or simply choose not to prune audit data.

HoneyMonitor doesn’t require third party agents running on your Server, nor cron jobs. It simply use SQL. You don’t need to install - and maintain - software on your servers: all the administration tasks, performance monitoring, tuning and report generation is made at client-side!

Once you create a Performance Report, you can save it in the directory you prefer, print it and export it to pdf or other formats.

HoneyMonitor comes with 90+ performance charts, included in 10 Reports (Temporary Tables, Query Cache, InnoDB, …) but you can also create the Reports you prefer using the included Report Designer.

3.1. Replication Issues

If your server is a Master Server, you must skip the replication of the audit database otherwise the performance audit data of the master will be replicated on the slaves.

Option -replicate-ignore-table=db_name.tbl_name should avoid this problem. Please refer to the MySQL® Reference Manual for further information on replication.

You can monitor your master / slaves environment simply installing the audit database on the master and on each slave.

4. Installing the Audit Database & Enabling the Auditing

To start Audit Installation just click on the menu Auditing / Install Audit and follow the few steps of Audit Installation Wizard. A database named hs_audit_schema will be created. Please make sure that you don’t have a database with the same name as, at the moment, there is no possibilities to change the name of the audit database installed by the software. During installation, the Wizard will perform some tests to ensure that all is installed propertly and also allows you to set the events’ intervals and enable data collection (Fig. from 6 to 9).

By the way at any time you can open the Check Audit Installation Window to check the status of your Audit System.

Fig. 10: Check Audit Installation Window

5. Audit Options & Audit Statistics

By default, the Audit Installation Wizard, after installation, enables the auditing. If you have paused it, to enable it once again, just open the Audit Options Window (Fig. 11) and check the Enable Populating Event check-box.

You may be interested knowing how many records the Audit Table contains or how many disk space is being used. If you click on the Auditing / Audit Statistics menu, the Audit Statistics Window (Fig. 12) will be opened and you’ll can see some statistics on your Audit Database.

6. Conclusion

In this first article of the series, we have focused on the first startup of HoneyMonitor and on introducing the Audit System showing you how to get it working.

7. What we will see in the next articles

In the next article of this series, we’ll take a look at the Performance Tuning Monitor and we will see how to create Performance Reports.

The following is a not-complete list of subsections we plan to discuss over the series of articles:

• Quick Tour of the Performance Tuning Monitor
• Quick Tour of the Performance Reports
• The Audit Database in Details
• Limitations: how the system can be improved
• Future Plans: which features we plan to add in the Audit Pro Edition
• A Brief Introduction to other features of HoneyMonitor
  • Quick Tour of the Metadata Catalog
  • Quick Tour of the Replication Monitor
  • Quick Tour of the Cluster Monitor

Contents are subject to change. Let us know what you’d like to see.

8. 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:

• Getting Started Guide
• Video Tutorials
• User’s Guide

9. 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 and he lives in Italy, where he works from home.