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
  • Axis Name
  • Series
• 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:

1. HoneyMonitor Audit Pro Edition
2. 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 different 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 different 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:

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

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:

• Performance Monitoring, Tuning & Auditing in MySQL® 5.1 - A GUI Approach - PART 1
• HoneyMonitor v.1.0.14 released!
• HoneyMonitor v.1.0.14-alpha - New Features Preview

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.

This is a short tutorial on the features implemented in HoneyMonitor for the use and administration of Partitioning in MySQL™ 5.1. It could be useful for Users who are new to the product and want to get start with it or to whom want to learn the basis of Table Partitioning in MySQL™ 5.1.

Contents

1. Introduction
2. Partitioning a Table
2.1 Creating a Partitioned Table using the Create Table Wizard and the Partition Editor - A Range Partitioning Example
2.2 Partitioning a Table that already exits - An Hash Partitioning Example
3. Creating and Dropping Partitions
3.1 Adding Partitions
3.2 Dropping Range (or List) Partitions
3.2.1 Multi-Dropping of Partitions
3.3 Aggregating Hash (or Key) Partitions
4. Reorganizing Partitions
5. Repartitioning a Table
6. Removing Partitioning
7. Retrieving Information about Partitions
7.1 Partitions’ Metadata
7.2 Partition Editor’s Charts
8. Partition’s Maintenance
9. Other Resources

1. Introduction

The support for table partitioning has been added in MySQL™ v.5.1. Partitioning allows you to distribute a portion of table’s rows across a filesystem according to specific rules that you can set (the so called partitioning function).

This article can’t be exhaustive about the Partitioning Feature of MySQL™ 5.1. We will have just a GUI approach. For more information about Table Partitioning, please visit http://dev.mysql.com/doc/refman/5.1/en/partitioning.html.

HoneyMonitor provides you with a useful GUI for managing table partitioning. In this short article we will see how to add / edit / drop partitioning, how to add / drop (or coalesce) partitions / subpartitions, how to retrieve information about partitions and the basis of partition’s maintenance.

More detail on HoneyMonitor on its Reference Manual, at http://www.honeysoftware.com/honeymonitor/doc.

2. Partitioning a Table

You can add partitioning to a table in 2 ways:

1. If you want to create a partitioned table (and the table has not been created yet) you can use the Create Table Wizard.
2. If the table has been already created, you may open it the the Table Editor and then click on the Partitions Tab.

2.1 Creating a Partitioned Table using the Create Table Wizard and the Partition Editor - A Range Partitioning Example

The Create Table Wizard allows you creating tables in a simple and fast way (Fig. 1).

Fig. 1: The Create Table Wizard

One step of the Wizard is dedicated to Table Partitioning (Fig. 2).

Fig. 2: Partitioning Step of the Create Table Wizard

Figures below explain the creation process of a Range Partitioning.

 

2.2 Partitioning a Table that already exits - An Hash Partitioning Example

If a table is not partitioned, and you open it in the Table Editor, you can add partitioning by clicking on the button ”Add Partitioning” available from the Partitions Tab (Fig. 3)

Fig. 3: Table Editor - Partitions Tab (no partitioning)

The Partition Editor will be opened and you can follow the same steps as in the example above (if you want to create a Range-Partitioned Table).

For our tutorial we will create a Hash partitioned table (Fig. 4 and 5). For this kind of partitioning, you can set the partition number and then just click ”OK” without the need of creating partition by partition.

Fig. 4: Using the Partition Editor to create a Hash Partitioned Table

Fig. 5: Table Editor - Partitions Tab (list of the hash partitions)

3. Creating and Dropping Partitions

Suppose now that you have a partitioned table and you want to add or drop a partition. You can do this in a simple way using the popup menu of the Table Editor (Fig. 6):

Fig. 6: Table Editor - Popup Menu of the Partition Tab

3.1 Adding Partitions

To add a partition you can use the popup menu of the Partitions Tab of the Table Editor (and then use the ”Add Partition Window”) but you can also add a partition without the need to open that Editor, just using the popup menu of the Server Objects List (Fig. 7).

Fig. 7: Server Objects List - Popup Menu for Partitions’ Administration

3.2 Dropping Range (or List) Partitions

To drop a partition just select it in the grid of the Table Editor and use the popup menu (Fig. 6).

Please be aware of the fact that when dropping a (Range or List) partition all its subpartitions will be removed and the data contained in that partition will be deleted as well (deleting a partition is a fast way for deleting a large data set).

3.2.1 Multi-Dropping of Partitions

To drop more than one partition at once, you can use the Multi-Drop Wizard (Fig. 7).

Fig. 7: Multi-Drop of Range Partitions using the Multi-Drop Wizard

3.3 Aggregating Hash (or Key) Partitions

To reduce the number of partitions in our Hash partitioned Table we can’t ”drop” a partition but we have to ”aggregate” two or more partitions (Fig. 8, 9).

Fig. 8: Aggregating 3 Hash Partitions

Fig. 9: Table Editor - Partition List after the aggregation

4. Reorganizing Partitions

The Reorganize Partitions Window (Fig. 10) allows you to reorganize some partitions of a partitioned table.

Fig. 10: Reorganize Partitions Window: Choosing Partitions to be reorganize

Note: if you want to do a complete repartition of the table, editing the partition function as well, please use the Partition Editor (see below).

After choosing a set of partitions, you can write the SQL to reorganize them or you can click on the button ”…” to use the Partition Editor to reorganize them in a graphical way (Fig. 11).

Fig. 11: Reorganize Partitions Window: Ready to execute the query

5. Repartitioning a Table

If you want to do a complete repartition of a partitioned table you can use the ”Repartitioning” Feature of the Partition Editor (Fig. 12):

1. Open a partitioned table in the Table Editor and click on the ”Partitions” Tab.
2. Click on the button ”Edit Partitioning” -> the Partition Editor will be opened.
3. Click on the button ”Table Repartitioning” and use the Partition Editor in a normal way (as when you add partitioning for the first time).

No changes will be ”committed” before you click on the ”Post Changes” button. If you are not satisfied with the new partition’s structure just created, just don’t save and click on the ”Undo” button. The old partition’s structure will be re-loaded in the Partition Editor.

Note: repartitioning preserve the table’s data. No data will be lost.

Fig. 12: Repartitioning a Partitioned Table

6. Removing Partitioning

You can remove the partitioning by clicking on the button ”Remove Partitioning” of the ”Partitions” Tab of the Table Editor. All Partitions will be removed but the data are preserved.

7. Retrieving Information about Partitions

HoneyMonitor allows you retrieving partition metadata in (at least) two ways. You can use the Metadata Catalog of the Server Objects List or the Partition Editor.

When searching partition’s metadata, please be aware of bug #33287.

7.1 Partitions’ Metadata

Please use the drill-down feature of the Metadata Catalog to find the metadata you need (Fig. 15, 16, 17).

Fig. 15	Fig. 16	Fig. 17

7.2 Partition Editor’s Charts

If you open the Partition Editor, and you click on a partition name in the Partition Tree, useful information about that partition will be shown. This include also 3 charts: ”Rows #”, ”Data Length” and ”Index Length”. You can use those charts to check the portion of table’s rows included in a specific partition, as well as the used Data and Index MB (Fig. 19).

Fig. 19: Partition Editor - Partition Statistics

8. Partition’s Maintenance

As regards maintenance of partitions, again you can use (at least) 2 methods:

1. A single-partition one-click method from the Partition Editor (Fig. 20).
2. A more general multi-partitions wizard-method (Fig. 21).

Fig. 20:Using the Partition Editor for Partition’s Maintenance

Fig. 21:  Using the top-level HoneyMonitor’s Menu for Partition’s Maintenance

9. 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

[HoneyMonitor is a GUI for MySQL™. Server Administration, Database and Database Objects Administration, Code Development, Server Monitoring, Performance Monitoring, Performance Auditing. Read more on http://www.honeysoftware.com/]

This is a short tutorial on the features implemented in HoneyMonitor for the use and administration of Table Logs in MySQL™ 5.1 . It could be useful for Users who are new to the product and want to get start with it or to whom want to learn the basis of Table Logging in MySQL™ 5.1.

Contents

1. Introduction
2. Brief Introduction to the Server Logs Management Window
3. The General Log Table
4. General Log Administration
5. General Log Data: Selecting, Sorting, Filtering Log Data
6. Quick Tour of the other features of the Server Logs Management Window
7. Other Resources

1. Introduction

One of the main new features of MySQL™ 5.1 is Table Logging. Beginning with v.5.1.6, in fact, it is possible to choose a new destination for General and Slow Logs: not only log files but also log tables.

In general, if the log output is a table and all log statements are stored in a table rather than in a text file you can use a SELECT syntax to select over the log table thus improving and speed-up the way you can search information on your logs.

In MySQL™ 5.1 there are two log tables: general_log and slow_log. In this article we typically will refer to general_log but many of the considerations we make apply to slow_log as well.

A server’s variable, log_output, takes care of the destination of the output of the general_log and the slow_log.

log_output is a global, dynamic, string variable.

In general, a dynamic variable is a variable that you can change at run-time without the need to stop and restart the server. This is very useful if applied to log: you can switch from log-table to log-file at runtime.

Possibles values for log_output are “FILE”, “TABLE” and “NONE” but you can also use a combination between values. For example if you set “FILE,TABLE” all log statements will be written both in the file-log and in the table-log.

In early versions of MySQL™ 5.1, the default value for log_output was “TABLE” but then the default value has been “restored” to “FILE” (as for MySQL™ 5.0).

“NONE”, if specified, causes the server not to write log even if the log is enabled. Please pay attention at the following difference: setting a log output does not imply enabling log.

To enable general_log you have to set general_log = ON and, in a similar way, to enable slow_log you have to set log_slow_queries = ON. After log is enabled you can choose log_output but, while there are two separate variables for enabling general and slow_log, there is only one variable related to log output. So you can have the general_log enabled and the slow_log disabled but if you choose an output (and both logs are enabled) that output (”FILE“, “TABLE“, or “FILE,TABLE“) applies to both the logs.

If the logs are disabled, no logging occurs even if the value of log_output is not “NONE“.

Another thing you have to consider is that the slow_log is not an absolute, independent feature like general_log.

slow_log is in fact deeply related to the value of the variable long_query_time (if a query takes more than long_query_time to be executed, and slow_log is enabled, then it will be logged as a slow query).

If you start the server with the option –-log, the general_log is enabled by default (but not the slow_log).

Other variables of interest for log management are general_log_file (the location of the general_log if log_output is “FILE“), slow_query_log_file (the location of the slow_log if log_output is “FILE“) and log_error (the location of the error log).

This article can’t be exhaustive about log management in MySQL™ and we will try to cover only some aspects relating to the general and slow logs, leaving out errors log and binary logs from our treatment.

For more information about Server Logs, please visit http://dev.mysql.com/doc/refman/5.1/en/log-files.html.

2. Brief Introduction to the Server Logs Management Window

In HoneyMonitor, our GUI for MySQL™, the Server Logs Management Window allows you to view and manage the server’s logs (general log, slow log, error log, binary log, relay log).

For this tutorial we will use HoneyMonitor v.1.0.14-alpha, MySQL™ v.5.1.28-rc, the latest version of the 5.1 rc series of the server, and MySQL™ v.5.1.26-rc (we use the old version 5.1.26-rc for the sole reason that the v.5.1.28-rc has just been released few hours ago and, in my laptop, the log tables of that version contain only few rows).

The version 1.0.14-alpha of HoneyMonitor includes many improvements in the log management if compared with v.1.0.13-alpha but we also plan to add other two main features in this Window in next versions so stay tuned for a better management of log in future versions of our software.

When opening the Server Logs Management Window, the Status Tab is selected (Fig. 1).

Fig. 1: Server Logs Management

In this Tab some quick information about the status of the logs are shown (for example, the number of records contained in the General and Slow Log Tables).

Furthermore, server’s variables that are related to the logs management are listed. Dynamic variables, in the list, are identified with a different icon and can be edited just double-clicking on their names or using the popup menu of the list.

When clicking on a variable, a useful tooltip with the variable’s description will appear.

As you can see from Fig. 1, you can ”Flush” the logs by clicking on the ”Flush Logs” button.

If we click on the “General Log” Tab we can see that the general log is enabled but the log output isn’t “TABLE” (Fig. 2)

Fig. 2: Server Logs Management Window – General Log Tab

so the first thing we must do in order to continue our tutorial is changing the log_output (Fig. 3).

Fig. 3: Setting log_output

and then checking that the (orange) warning disappear (Fig. 4).

Fig. 4: General Log Entries

3. The General Log Table

The General Log Table, as well as the Slow Log Table is included in the mysql database:

mysql> use mysql;
Database changed
mysql> describe general_log\G
*************************** 1. row ***************************
Field: event_time
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
*************************** 2. row ***************************
Field: user_host
Type: mediumtext
Null: NO
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: thread_id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 4. row ***************************
Field: server_id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 5. row ***************************
Field: command_type
Type: varchar(64)
Null: NO
Key:
Default: NULL
Extra:
*************************** 6. row ***************************
Field: argument
Type: mediumtext
Null: NO
Key:
Default: NULL
Extra:
6 rows in set (0.11 sec)

mysql> describe slow_log\G
*************************** 1. row ***************************
Field: start_time
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
*************************** 2. row ***************************
Field: user_host
Type: mediumtext
Null: NO
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: query_time
Type: time
Null: NO
Key:
Default: NULL
Extra:
*************************** 4. row ***************************
Field: lock_time
Type: time
Null: NO
Key:
Default: NULL
Extra:
*************************** 5. row ***************************
Field: rows_sent
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 6. row ***************************
Field: rows_examined
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 7. row ***************************
Field: db
Type: varchar(512)
Null: NO
Key:
Default: NULL
Extra:
*************************** 8. row ***************************
Field: last_insert_id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 9. row ***************************
Field: insert_id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 10. row ***************************
Field: server_id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 11. row ***************************
Field: sql_text
Type: mediumtext
Null: NO
Key:
Default: NULL
Extra:
11 rows in set (0.00 sec)

mysql>

You can see the table’s DDL as well as the table data from the General Log Tab of the Server Logs Management Window included in HoneyMonitor, too (Fig. 5).

Fig.5: General Log DDL

4. General Log Administration

You can enable / disable the General Log directly through the General Log Tab. Also you can clear all log entries and change the Engine of the Table form CSV to MyIsam and vice versa. Please, be aware of bug #39133 when altering Log Tables on master servers.

5. General Log Data: Selecting, Sorting, Filtering Log Data

The grid included in the General Log Tab allows you to see, sort and filter (general) log data in an easy way. Of course, data can’t be edited.

Pagination helps you manage large log tables (Fig. 6).

Fig. 6: Data Pagination

Simple sorting (i.e. on just one column) or advanced sorting (i.e. on more than one columns simultaneously) can help you too for your administration tasks (Fig. 7 and 8)

Fig. 7: Simple Sorting

Fig. 8: Advanced Sorting

as well as defining specific filters (Fig. 9).

Fig. 9: Filtering Log Data

6. Quick Tour of the other features of the Server Logs Management Window

The following images can help you understanding the other features implemented by the Server Logs Management Window.

More details on the HoneyMonitor Reference Manual at http://www.honeysoftware.com/honeymonitor/doc.

Fig. 10: General (or Slow) Query File

 

Fig. 11: Slow Table Log

 

Fig. 12: Binary and Relay Logs

Fig. 13: Errors Log

 

Fig. 14: Binlog Events

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

[HoneyMonitor is a GUI for MySQL™. Server Administration, Database and Database Objects Administration, Code Development, Server Monitoring, Performance Monitoring, Performance Auditing. Read more on http://www.honeysoftware.com/]

Contents

• Introduction
• Log File Group & UndoFiles
• TableSpaces & DataFiles
• Cluster Disk Objects Metadata
• Creating Cluster Disk Tables
• Dropping Cluster Disk Data Objects
• The Cluster Monitor
• Cluster Disk Data Limitations
• Cluster Disk Data Quiz
• Other Resources

1. Introduction

Cluster Disk Data is a new feature of MySQL™ 5.1. Beginning with MySQL™ 5.1.6, in fact, it is possible to store the non-indexed columns of NDBCLUSTER tables on disk, rather than in RAM  this allowing MySQL™ Cluster to scale upward with fewer RAM requirements than previously.

This is a short tutorial on the features implemented in HoneyMonitor for the use and administration of Cluster Disk Data Objects . It could be useful for Users who are new to the product and want to get start with it or to whom want to learn the basis of Cluster Disk Data in MySQL™ 5.1.

For more information about Cluster Disk Data, please visit http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-disk-data.html, while, if you want to learn more on HoneyMonitor, please visit its Project Home Page at http://www.honeysoftware.com/honeymonitor.

For this tutorial we will suppose you have a MySQL™ Cluster up and running and we will connect to the Linux machine, from HoneyMonitor, through a SSH Tunneling connection.

If you are new to MySQL™ Cluster and want to set up a testbed (all nodes on localhost), you can use this very useful configuration tool (created by Johan Andersson) or, if you want to do a more complicated (but nice) exercise, you can set up the Cluster in 4 Solaris™ Zones (a tutorial can be found here).

For our Tutorial, we will follow the following steps:

• Create a LogFile Group
• Add one more UndoFile to the LogFile Group
• Create a TableSpace that uses the LogFile Group just created
• Add one more DataFile to the TableSpace
• Create a Cluster Disk Table

You can skip steps no. 2 and 4 as they are just to practise.

2. Log File Group & UndoFiles

The LogFile Group Editor (Fig. 1) allows you create / edit / drop a LogFile Group.

Fig. 1: Creating a LogFile Group

It implements the following Data Definitions Statements:

1) [http://dev.mysql.com/doc/refman/5.1/en/create-logfile-group.html]

CREATE LOGFILE GROUP logfile_group
ADD UNDOFILE ‘undo_file’
[INITIAL_SIZE [=] initial_size]
[UNDO_BUFFER_SIZE [=] undo_buffer_size]
[REDO_BUFFER_SIZE [=] redo_buffer_size]
[NODEGROUP [=] nodegroup_id]
[WAIT]
[COMMENT [=] comment_text]
ENGINE [=] engine_name

2) [http://dev.mysql.com/doc/refman/5.1/en/alter-logfile-group.html]

ALTER LOGFILE GROUP logfile_group
ADD UNDOFILE ‘file_name’
[INITIAL_SIZE [=] size]
[WAIT]
ENGINE [=] engine_name

3) [http://dev.mysql.com/doc/refman/5.1/en/drop-logfile-group.html]

DROP LOGFILE GROUP logfile_group
ENGINE [=] engine_name

As you can see from Fig. 1, some options are disabled as they are parsed but not yet implemented by the  MySQL™ Server.

An UndoFile is required for the LogFile Group creation. In our example we will use an UndoFile named ‘undofile1′.

After the creation  of the LogFile Group the Tabs ‘UndoFiles’ and SQL will be enabled (Fig. 2, 3, 4).

Fig. 2: LogFile Group Editor - Advanced Tab

Fig. 3: LogFile Group Editor - UndoFiles Tab

Fig. 4: LogFile Group Editor - SQL Tab

In the UndoFiles Tab you can see UndoFiles’ details or you can create one more UndoFile, using the Add UndoFile Dialog (Fig. 5).

Fig. 5: Add UndoFile Dialog

The SQL Tab contains the SQL statements to be executed for re-creating the LogFile Group (and all its UndoFiles!).

Note: you can’t delete UndoFiles.

Note 2: beginning with MySQL™ 5.1.8, you can have only one LogFile Group per Cluster at any given time (see Bug#16386).

Let’s now create a TableSpace that uses the LogFile Group just created.

3. TableSpaces & DataFiles

The TableSpace Editor (Fig. 6) allows you create / edit / drop TableSpaces.

Fig. 6: Creating a TableSpace

It implements the following Data Definitions Statements:

1) [http://dev.mysql.com/doc/refman/5.1/en/create-tablespace.html]

CREATE TABLESPACE tablespace_name
ADD DATAFILE ‘file_name’
USE LOGFILE GROUP logfile_group
[EXTENT_SIZE [=] extent_size]
[INITIAL_SIZE [=] initial_size]
[AUTOEXTEND_SIZE [=] autoextend_size]
[MAX_SIZE [=] max_size]
[NODEGROUP [=] nodegroup_id]
[WAIT]
[COMMENT [=] comment_text]
ENGINE [=] engine_name

2) [http://dev.mysql.com/doc/refman/5.1/en/alter-tablespace.html]

ALTER TABLESPACE tablespace_name
{ADD|DROP} DATAFILE ‘file_name’
[INITIAL_SIZE [=] size]
[WAIT]
ENGINE [=] engine_name

3) [http://dev.mysql.com/doc/refman/5.1/en/drop-tablespace.html]

DROP TABLESPACE tablespace_name
ENGINE [=] engine_name

Note: The Editor can be used to create both NDBCLUSTER and FALCON TableSpaces. When creating FALCON TableSpaces, the ‘LogFile Group’ option isn’t visible.

As you can see from Fig. 6, some options are disabled as they are parsed but not yet implemented by the  MySQL™ Server.

A DataFile is required for the TableSpace creation. In our example we will use a DataFile named ‘datafile-11′.

After the creation  of the TableSpace the Tabs ‘DataFiles’ and SQL will be enabled (Fig. 7, 8, 9).

Fig. 7: TableSpace Editor - Advanced Tab

Fig. 8: TableSpace Editor - DataFiles Tab

Fig. 9: TableSpace Editor - SQL Tab

In the DataFiles Tab you can see DataFiles’ details or you can create one more DataFile, using the Add DataFile Dialog (Fig. 10). You can also drop a DataFile.

Fig. 10: Add DataFile Dialog

The SQL Tab contains the SQL statements to be executed for re-creating the TableSpace (and all its DataFiles!).

4. Cluster Disk Objects Metadata

The source for Cluster Disk Object Metadata in MySQL™ 5.1 is the  information_schema.files table. I’ll try to post a more detailed article on Disk Object Metadata later on.

In the HoneyMonitor’s Metadata Catalog there is a visualization dedicated to Cluster Disk Objects (Fig. 11).

Fig. 11: Metadata Catalog - Cluster Disk Objects

That visualization is very useful to know, with just a look, the Cluster Disk Objects’  hierarchy. You can use popup menus to create / edit / drop the objects.

5. Creating Cluster Disk Tables

Now that our TableSpace has been created, we can create a Cluster Disk Table. We can use the command line client

CREATE TABLE my_disk_data_table (
….
)
TABLESPACE tablespace1 STORAGE DISK
ENGINE = NDBCLUSTER;

or the Create Table Wizard (Fig. 12).

Fig. 12: Creating a Cluster Disk Table

6. Dropping Cluster Disk Data Objects

Please, note that there are particular rules for dropping Cluster Disk Data Objects. You have to follow a logical order. For more information, please refer to http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-disk-data-objects.html.

7. The Cluster Monitor

The Cluster Monitor (Fig. 13) allows you to monitor the status of your Cluster.

Fig. 13: The Cluster Monitor

At this stage of development it’s a simple (but useful) Window which shows information about the NDB Engine Status (at a glance or category by category) and the status, dynamic and system variables of the Server related to Cluster.

We plan to add new features for Cluster Management and Monitoring in HoneyMonitor, so stay tuned!

8. Cluster Disk Data Limitations

Disk Data Objects are subject to the following maximums:

1. Maximum number of TableSpaces: 2^32 (4294967296)
2. Maximum number of DataFiles per TableSpace: 2^16 (65535)
3. The theoretical maximum number of extents per TableSpace data file is 2^16 (65525); however, for practical purposes, the recommended maximum number of extents per DataFile is 2^8 (32768).
4. Maximum DataFile size: The theoretical limit is 64G; however, in MySQL™ 5.1, the practical upper limit is 32G. This is equivalent to 32768 extents of 1M each.The minimum and maximum possible sizes of extents for TableSpace data files are 32K and 2G, respectively.

For known limitations of MySQL Cluster, please refer to http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations.html.

9. Cluster Disk Data Quiz

Create a LogFile Group, some TableSpaces and some Cluster Disk Tables. Then choose a TableSpace and write a query to find the list of the tables which are using that TableSpace.

10. Other Resources

It’s now time to close this article. 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

Enjoy your MySQL™ Cluster!

[HoneyMonitor is a GUI for MySQL™. Server Administration, Database and Database Objects Administration, Code Development, Server Monitoring, Performance Monitoring, Performance Auditing. Read more on http://www.honeysoftware.com/]

This is a short tutorial on the features implemented in HoneyMonitor for the use and administration of Plugins. It could be useful for Users who are new to the product and want to get start with it or to whom want to learn the basis of Plugins in MySQL™ 5.1

More details on the HoneyMonitor Reference Manual at http://www.honeysoftware.com/honeymonitor/doc.

Contents

• Introduction
• Plugins Metadata
• Retrieving the list of Plugins
• The Plugins Editor
• The Plugins Tab of the Server Properties Window
• An example of use:  a  FULLTEXT parser plugin
• Other Resources

1. Introduction

There are three ways to add new functions to the MySQL™ Server:

• through the User-Defined Function (UDF) interface;
• by creating Stored Functions;
• starting from v.5.1, through the MySQL™ Plugin Interface (the plugin interface is intended as the successor to the older UDF interface; in some respects, the plugin API is similar to the older UDF API that it supersedes, but the plugin API has several advantages over the older interface).

The UDFs are compiled as object files (.dll, .so) and then added to and removed from the server dynamically.
The Stored Functions are written using SQL statements rather than by compiling object code.
The Plugins are compiled as object files (.dll, .so) and then installed to and uninstalled from the server dynamically.

For more information about Plugins, please visit http://dev.mysql.com/doc/refman/5.1/en/plugin-api.html.

2. Plugins Metadata

In the mysql database there is a table -  mysql.plugin – where all the information about your Plugins are stored:

mysql> DESCRIBE mysql.plugin;
+——-+———–+——+—–+———+——-+
| Field | Type      | Null | Key | Default | Extra |
+——-+———–+——+—–+———+——-+
| name  | char(64)  | NO   | PRI |         |       |
| dl    | char(128) | NO   |     |         |       |
+——-+———–+——+—–+———+——-+
2 rows in set (0.01 sec)

There is also a table in the information_schema database:

mysql> DESCRIBE INFORMATION_SCHEMA.PLUGINS;
+————————+————-+——+—–+———+——-+
| Field                  | Type        | Null | Key | Default | Extra |
+————————+————-+——+—–+———+——-+
| PLUGIN_NAME            | varchar(64) | NO   |     |         |       |
| PLUGIN_VERSION         | varchar(20) | NO   |     |         |       |
| PLUGIN_STATUS          | varchar(10) | NO   |     |         |       |
| PLUGIN_TYPE            | varchar(80) | NO   |     |         |       |
| PLUGIN_TYPE_VERSION    | varchar(20) | NO   |     |         |       |
| PLUGIN_LIBRARY         | varchar(64) | YES  |     | NULL    |       |
| PLUGIN_LIBRARY_VERSION | varchar(20) | YES  |     | NULL    |       |
| PLUGIN_AUTHOR          | varchar(64) | YES  |     | NULL    |       |
| PLUGIN_DESCRIPTION     | longtext    | YES  |     | NULL    |       |
| PLUGIN_LICENSE         | varchar(80) | YES  |     | NULL    |       |
+————————+————-+——+—–+———+——-+
10 rows in set (0.01 sec)

3. Retrieving the list of Plugins

Plugins are listed in Plugins node of the Server Objects List (Fig. 1).

Fig. 1: Server Objects List - Plugins

You can install / uninstall a Plugin using node’s popup menu.

4. The Plugins Editor

The Plugins Editor (Fig. 2), allows you to install a Plugin. To successfully install a Plugin, you must copy it in the Plugin Directory of the Server (the Editor helps you because it includes a specific field for the Plugin Directory – not editable, of course).

Fig. 2: The Plugins Editor

Let’s install a plugin using the Plugin Editor.

For this tutorial I will use the “Simple N-Gram (bi-gram) FULLTEXT parser plugin for MySQL 5.1+” - http://mysqlbigram.googlepages.com/.

We will connect to a MySQL™ Server running on a Linux Machine (Ubuntu) through a SSH Connection.

1. open the Plugins Editor and read your Plugin Directory
2. download the plugin library to the Plugin Directory (or ask your system administration to do that) – Fig. 3

Fig. 3: Getting the Plugin

1. insert the “Plugin Name” and the “Library Name” in the Plugin Editor and click on the save button (Fig. 2)

After the installation of the Plugin, the SQL Tab (Fig. 4) – now enabled - will contain the SQL syntax for re-installing the current Plugin.

Fig. 4: The Plugin Editor - SQL Tab

Note: you can open the “Server Properties Window” to read the value of the variable plugin_dir (Fig. 5).

Fig. 5: Server Properties Window - Variables Tab

 

5. The Plugins Tab of the Server Properties Window

In the “Server Properties Window” there is a Tab for Plugins’ management. As you can see from Fig. 6

Fig. 6: Server Properties Window - Plugins Tab

in the Server Objects List are listed only your plugins while in the Plugins’ Tab of the Server Properties Windows the “system” plugins are listed too. The 2 Windows use different queries:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.1.26-rc MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> SELECT `name` FROM `mysql`.`plugin`; #### The Server Objects List
+———+
| name    |
+———+
| bi_gram |
+———+
1 row in set (0.00 sec)

mysql> SHOW PLUGINS; #### The Server Properties Window
+————+——–+—————-+—————+———+
| Name       | Status | Type           | Library       | License |
+————+——–+—————-+—————+———+
| binlog     | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| partition  | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| ARCHIVE    | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| BLACKHOLE  | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| CSV        | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| MEMORY     | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| InnoDB     | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| MyISAM     | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| bi_gram    | ACTIVE | FTPARSER       | bi_gramlib.so | GPL     |
+————+——–+—————-+—————+———+
10 rows in set (0.00 sec)

6. An example of use:  a  FULLTEXT parser plugin

Our plugin has been installed and it’s ready for use. Let’s test the ‘WITH PARSER’ option of the INDEX syntax:

First we create a Table – using the command line client or the Create Table Wizard (Fig. 7)

mysql> USE test;
Database changed
mysql> CREATE TABLE t (c VARCHAR(255));
Query OK, 0 rows affected (0.01 sec)

Fig. 7: Create Table Wizard

And then we add an Index, using the command line or the Index Editor (Fig. 8)

mysql> CREATE FULLTEXT INDEX c ON t(c) WITH PARSER bi_gram;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Fig. 8: Index Editor

Of course we can add the Index while we are creating the table, but for this tutorial I decided to to that in two steps to let you see the CREATE INDEX syntax.

 

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

[HoneyMonitor is a GUI for MySQL™. Server Administration, Database and Database Objects Administration, Code Development, Server Monitoring, Performance Monitoring, Performance Auditing. Read more on http://www.honeysoftware.com]

This is a short tutorial on the features implemented in HoneyMonitor for the use and administration of Federated Servers . It could be useful for Users who are new to the product and want to get start with it or to whom want to learn the basis of Federated Servers in MySQL™ 5.1

More details on the HoneyMonitor Reference Manual at http://www.honeysoftware.com/honeymonitor/doc.

 

Contents

• Introduction
• Federated Server Metadata
• Retrieving the list of Federated Servers
• The Federated Server Editor
• Creating a Federated Table Using the Create Table Wizard
• Other Resources
• About the Author

1. Introduction

Starting from MySQL™ v.5.1.15-beta you can use the CREATE / ALTER / DROP SERVER syntax to manage your Federated Servers.

This can help you creating a FEDERATED table in a fast and more efficient way. In fact if you first create a Federated Server using the CREATE SERVER syntax, then you can create a federated table using the following simple create table option

CONNECTION = ‘federated_server_name’

instead of the longer

CONNECTION = ‘mysql://user_name [:password]@host_name[:port_num] /db_name/tbl_name’

Note that starting from version 5.1.26-rc, the FEDERATED storage engine is disabled by default in binary distributions. The engine is still available and can be enabled by starting the server with the –federated option.

For more information about Federated Server, please visit http://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html.

 

2. Federated Server Metadata

In the database mysql there is a table -  mysql.servers – where all the information about the Federated Servers are stored:

mysql> DESCRIBE mysql.servers;
+————-+———-+——+—–+———+——-+
| Field       | Type     | Null | Key | Default | Extra |
+————-+———-+——+—–+———+——-+
| Server_name | char(64) | NO   | PRI |         |       |
| Host        | char(64) | NO   |     |         |       |
| Db          | char(64) | NO   |     |         |       |
| Username    | char(64) | NO   |     |         |       |
| Password    | char(64) | NO   |     |         |       |
| Port        | int(4)   | NO   |     | 0       |       |
| Socket      | char(64) | NO   |     |         |       |
| Wrapper     | char(64) | NO   |     |         |       |
| Owner       | char(64) | NO   |     |         |       |
+————-+———-+——+—–+———+——-+
9 rows in set (0.01 sec)

mysql> SELECT * FROM mysql.servers LIMIT 1\G
*************************** 1. row ***************************
Server_name: server1
Host: localhost
Db: mysql
Username: root
Password:
Port: 6005
Socket:
Wrapper: mysql
Owner:
1 row in set (0.00 sec)

 

3. Retrieving the list of Federated Servers

Federated Servers are listed in the Federated Servers node of the Server Objects List (Fig. 1).

Fig.1: Server Objects List - Federated Servers

You can create / edit / drop a Federated Server using node’s popup menu.

 

4. The Federated Server Editor

The Federated Server Editor (Fig. 2), allows you to create / edit a Federated Server.

Fig. 2: The Federated Server Editor - Advanced Tab

You have to set the server name and (at least) one option to create a Federated Server.

After you set the Server’s parameters you can click on the button ‘Test Connection’ to test the connection to the MySQL™ Server.

After the creation of the Server, the SQL Tab (Fig. 3) – now enabled - will contain the SQL syntax for recreating the current Server.

Fig. 3: The Federated Server Editor - SQL Tab

 

5. Creating a Federated Table Using the Create Table Wizard

The Create Table Wizard helps you creating tables in a simply and fast way (Fig. 4).

Fig. 4: The Create Table Wizard

The Wizard includes a ‘Connection’ option; when creating a Federated Table you can fill it with the connection string (’mysql://user_name[:password]@ host_name [:port_num]/db_name/tbl_name’) or just with the name of the Federated Server you created with Federated Server Editor.

By clicking on button at the right of the ‘Connection’ field, the Federated Server List will be opened thus helping you choosing an existing Federated Server - without the need of writing its name - or creating one more (Fig. 5).

Fig. 5: The Federated Server List

 

6. 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

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

 

[HoneyMonitor is a GUI for MySQL™. Server Administration, Database and Database Objects Administration, Code Development, Server Monitoring, Performance Monitoring, Performance Auditing. Read more on http://www.honeysoftware.com]

This is a short tutorial on the features implemented in HoneyMonitor for the use and administration of Scheduled Events . It could be useful for Users who are new to the product and want to get start with it or to whom want to learn the basis of Scheduled Events in MySQL™ 5.1

More details on the HoneyMonitor Reference Manual at http://www.honeysoftware.com/honeymonitor/doc.

 

Contents

• Introduction
• The Scheduled Event Editor
  1. Performed Action
  2. Advanced
  3. Comment
  4. SQL
• Other way to create Events in HoneyMonitor
• Editing Events
• Information about the Execution of the Events
• Do you want to know / edit the value of the variable event_scheduler?
• Event Metadata
• Other Resources
• About the Author

1. Introduction

Scheduled Events have been added in MySQL™ v.5.1.6.

An Event is a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Scheduled tasks of this type are also sometimes known as “temporal triggers”, implying that these are objects that are triggered by the passage of time.

An Event can be enabled or disabled (there is also a third state: disabled on slave); if disabled the event is still present in the database but it is not executed.

For more information about Events, please visit http://dev.mysql.com/doc/refman/5.1/en/events.html

In HoneyMonitor, Events of a specific database are listed in the Server Objects List (Fig. 1)

Fig. 1: Server Objects List - Events

For more information about the Server Objects List, please refer to this post.

 

2. The Scheduled Event Editor

HoneyMonitor includes a Scheduled Event Editor (Fig. 2) that allow you to create, alter, rename and drop your Events.

Fig. 2: The Scheduled Event Editor

Let’s see how to use it.

No special privileges are required to use this Editor as HoneyMonitor retrieves the data it need from the information_schema database (it is not necessary that users have privileges on the mysql database). By the way, note that there is a privilege that govern the creation, modification, and deletion of events, the EVENT privilege.

I used some queries similar to the following to create a database and a user to test the Scheduled Event Editor:

CREATE DATABASE `sleto`;
CREATE USER ’sleto’@'localhost’;
FLUSH PRIVILEGES;
GRANT USAGE ON *.* TO ’sleto’@'localhost’ REQUIRE NONE;
FLUSH PRIVILEGES;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,INDEX,ALTER,CREATE TEMPORARY TABLES,LOCK TABLES,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,EXECUTE,EVENT,TRIGGER ON `sleto`.* TO ’sleto’@'localhost’;
FLUSH PRIVILEGES;

Events are executed by a special event scheduler thread; the global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server. If you don’t know the value of this variable – or if you don’t know that such a variable exists – don’t worry: look at the status-bar of the Editor, and click on it to enable the Event Scheduler (Fig. 3).

 

Fig. 3: Event Scheduler - ON | OFF

Try to ask your system administration if you haven’t the right privileges to perform this operation.
Let’s suppose that the Event Scheduler is running (ON) and let’s create our first Event by filling the required information on the Editor’s Tabs and clicking on the ‘Save’ button.

 

2.1 Performed Action

The ”Performed Action” Tab (Fig. 4) allows you to edit the event statement, i.e. the specific action performed by the event.

Fig. 4: Event Editor - Performed Action

 

2.2 Advanced

The ”Advanced” Tab (Fig. 5) allows you to set the event properties.

Fig. 5: Event Editor - Advanced

There are two type of event:

1. one-time event (”at”): a one-time event executes one time only;
2. recurrent event (”every”): a recurrent event repeats its action at a regular interval, and the schedule for a recurring event can be assigned a specific start day and time, end day and time, both, or neither.

The ”Schedule details” frame changes according to the type of the event that is opened in the Events Editor (you can choose the event type using the ”Schedule form” combo).

When creating a new event, you may click on the ”if not exist” check to prevent an error if the event already exists.

 

2.3 Comment

The ”Comment” Tab (Fig. 6) allows you to set the event comment.

Fig. 6: Event Editor - Comment

 

2.4 SQL

The ”SQL” Tab (Fig. 7) contains the SQL syntax for recreating the current event. This tab will be enabled only after the Event creation.

Fig. 7: Event Editor - SQL

 

3. Other ways to create Events in HoneyMonitor

There are other ways to create an Event in HoneyMonitor. For instance, you can open the Script Editor and create the Event writing and executing a SQL query. You can also use the command builder if you don’t remember exactly the syntax (Fig. 8)

Fig. 8: Script Editor - Writing the syntax for Event’s creation

 

4. Editing Events

You can open an Event in the Event Editor to enable / disable it or change some of its properties (and rename it as well). You can see also some metadata like the creation date (Fig 9).

Fig. 9: Event Editor - Editing an Event

 

5. Information about the Execution of the Events

Is there a way to know if an event has been executed successfully?

Sure. You can check the Server Error Log. To do this in HoneyMonitor, open the Server Logs Management Window. You can also click on the button “Print Debug Information” to get more info  - but make sure you have the right privileges to perform this operation -  (Fig. 10).

Fig. 10: Server Logs Management - Error Log

 

6. Do you want to know / edit the value of the variable event_scheduler?

HoneyMonitor allows you to choose your preferred way to perform a particular task: you choose the way you prefer.

Below you find some possible solutions:

1. Open the Server Properties Window and go to the Tab “Variables”;

2. Use the Metadata Catalog (Global Variables visualization);

3. Open the Performance Tuning Monitor and go to the Tab “Variables”;

4. Open the Event Editor and check its status bar;

5. Open the Script Editor and create and execute a one-line script which contains the query “SHOW VARIABLES LIKE ‘event_scheduler’;”

6. Click on the Utility / MySQL Command Line Client menu to open a command line client and execute the query “SHOW VARIABLES LIKE ‘event_scheduler’”;

Do you want to know/edit the value of the variable event_scheduler (or other variable) but you don’t remember the Type, the Handle and the Description of that variable?

Open the Server Properties Window and go to the Tab “Variables” (Fig. 11) to see those information!

Fig. 11: Server Properties Window - Variables’ List

 

7. Event Metadata

The source of metadata for a Scheduled Event is the table information_schema.events (Fig. 12).

Fig. 12: Event Metadata

 

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.

[HoneyMonitor is a GUI for MySQL™. Server Administration, Database and Database Objects Administration, Code Development, Server Monitoring, Performance Monitoring, Performance Auditing. Read more on http://www.honeysoftware.com]

This is a short tutorial on the Server Objects List included in HoneyMonitor. I am writing it because I will reference it in some posts I am going to write in the next weeks. Furthermore, it could be useful for Users who are new to the product and want to get start with it.

More details on the HoneyMonitor Reference Manual at http://www.honeysoftware.com/honeymonitor/doc.

 

Contents:

• Introduction
• Connections Tree
• Metadata Catalog
• Using the Server Objects List
• Other Resources
• About the Author

1. Introduction

The Server Objects List (Fig. 1) is the ‘heart’ of HoneyMonitor. All server objects are listed in this Window using a tree-scheme and you can open most of the HoneyMonitor’s functionalities like the Table Editor, View Editor and Routine Editor using its useful popup menus.

Fig. 1: The Server Objects List.

The Window can be divided into 2 parts:

• an upper part called Connections Tree;

• a lower part called Metadata Catalog.

2. Connections Tree

The Connections Tree allows you to browse among the Server’s objects. The root nodes of the Tree are the Stored Connections to the MySQL™ Servers used by the software.

To connect to a Server, just double click on a connection node.

A connection node is filled with some sub-connection nodes (database objects, users, …) in a hierarchical way:

1. Databases: all databases for which you have the appropriate privileges are listed. Each database node has the following sub-nodes (Fig. 2):

   1. Tables, all database’s table are listed;

   2. Views, all database’s views are listed;

   3. Stored Procedures, all database’s procedures are listed;

   4. Stored Functions, all database’s functions are listed;

   5. Scheduled Events, all database’s events are listed;

   6. Queries, all queries and scripts stored in your local drives and for which you decided to add a link to are listed;

   7. Reports, all reports stored in your local drives and for which you decided to add a link to are listed;

   8. Backups, all backups stored in your local drives and for which you decided to add a link to are listed;

 

Fig. 2: Server Objects List - Database Objects

1. Users: all server’s users are listed (the select privilege on the mysql.user table is required).

2. UDFs: all server’s UDFs are listed (the select privilege on the mysql.func table is required).

3. Plugins: all server’s Plugins are listed (the select privilege on the mysql.plugin table is required).

4. Federated Servers: all the server’s Federated Servers are listed (the select privilege on the mysql.servers tables is required).

5. Cluster Disk Data: this node has not sub-nodes; when you click on it the LogFile Group, the UndoFiles, the TableSpaces and the DataFiles are added in the Cluster Tree (Fig. 3).

 

Fig. 3: Server Objects List - Cluster Objects Tree

1. Falcon TableSpaces: the Falcon TableSpaces are listed. TableSpaces nodes are filled with the Falcon Tables that use that TableSpace.

2. Server Properties: this node has not sub-nodes; when you double-click the Server Properties Window will be opened.

Tables nodes, on their time, are filled with table’s sub-nodes (fields, indexes, foreign keys, triggers and partitions; Fig. 4).

Fig. 4: Server Objects List - Table Objects

 

3. Metadata Catalog

Metadata is data about the data. The Metadata Catalog allows you retrieve additional information about the objects of the Connections Tree. When you click on a tree-node the Catalog automatically will display information about that node. However the Metadata Catalog contains additional information that aren’t related to the Connections Tree, and thanks to its ‘drill-down’ feature, you can use the Catalog apart from the Connections Tree.

The drill-down feature allows you to reach a deeper view in the Catalog and see more details. When you can drill-down the Catalog, the mouse pointer changes in a ‘hammer’.

The Metadata Catalog contains about 50 different visualizations (Fig. 5 and 6). For further details and screenshots, please refer to the HoneyMonitor’s Reference Manual.

Fig. 5: Server Objects List - Federated Server DDL

 

4. Using the Server Objects List

The nodes of the Connections Tree have popup menus that you can use to execute the most common administration actions related to that node.

The visualizations of the Metadata Catalog have popup menus that you can use to choose a particular visualization (for instance, for a table you can choose if you want to see the DDL or the list of fields or partitions, and so on; Fig. 6).

Fig. 6: Server Objects List - User’s details in the Metadata Catalog

Two toolbars, one for the Connections Tree and one for the Metadata Catalog, helps you executing the most common administration actions or navigating through metadata.

 

5. 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

6. 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.

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.

Fig. 1: HoneyMonitor Welcome Window	Fig. 2: HoneyMonitor Registration Window
Fig. 3: Trial Period Information Window	Fig. 4: CEIP Window

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).

Fig. 6: Audit Installation Wizard (1 of 4)	Fig. 7: Audit Installation Wizard (2 of 4)
Fig. 8: Audit Installation Wizard (3 of 4)	Fig. 9: Audit Installation Wizard (4 of 4)

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.

Fig. 11: Audit Options

Fig. 12: Audit Statistics

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.