We are pleased to announce the release 1.0.16 of HoneyMonitor, our GUI for MySQL™ administration and monitoring.

In this release, available for immediate download, we have fixed many bugs and included several improvements.

We are working to release a RC version as soon as possible.

The following is the list of changes:

• New Features:
  • new Tab “Defaults Folders” in the “HoneyMonitor Options” Window to set the default folders to be used when storing Audit Reports, Standard Reports, HTML Reports, Queries, Backups, Scripts, Exported Data.
• Improvements:
  • during the installation of the Audit System, it is now possible to choose the engine of the Audit Table. InnoDB is used by default.
  • when connecting to a server through Network or Audit Pro Editions, now a message alerts users if Event Scheduler is stopped.
  • minor improvements to the Filter Editor: an info-icon has been included to help users when setting a filter condition.
  • a different structure of directories is now used to store users’ files created through HoneyMonitor (queries, reports, backups). All the files are now stored as default in the folder Documents\HoneySoftware\HoneyMonitor.
  • minor improvements in the Binary and Relay Log Editor.
  • improved stability of Connections to MySQL™: both for direct and SSH Tunneling connections.
  • several improvements in the “Connection Window”:
    • it is now possible to specify a SSH Key for Tunneling Connections;
    • it is not mandatory anymore to set a local port number for SSH Tunneling Connections: you can set it or leave the default 0 value. If 0 is chosed a random (free) port will be choosed by HoneyMonitor;
    • it is now possible to store a connection even if it fails (a new “Save and Close” button has been added in the “Connection Window”, when the Window is in “creation mode”). Prior to this, it was possible to edit a connection but it was not possible to create a stored connection if that connection was failing.
    • it is now possible to edit a stored connection at software startup (before, it was possible only to select a stored connection or create a new one, and it was possible to edit a stored connection only after “entering” in the software - e.g. after “Server Objects List” was loaded.
  • several improvements to the “Unable to Connect” Window: added more information and tips to better indetify and solve occurred connection errors.
  • minor improvements in the Performance Tuning Monitor: now the Report Preview is opened in a new Window, outside the Monitor, for a better usability.
  • several other minor improvements.
• Bugs Fix:
  • some Editors were closed even if not able to save, after choosing “yes” on the message “Would you like to save your changes?”.
  • due to a wrong Edition limitation, in the Audit Pro Edition of HoneyMonitor, it was not possible to open the Report Preview using the top menu included in the software. Now fixed.
  • Plugin Editor: Editor was opening a different plugin (the first) than that selected in the “Server Objects Lists”.
  • fixed several inconsistencies between the active node of the Server Objects List, the software captionbar and some top level menus: now, if a connection is closed, many menus are disabled so users can’t open some Windows or Editors until they connect to a MySQL™ Server using the Server Objects List or the “Connect” menu entry.
  • many Wizards now remember the last used directory for storing files (backups, HTML reports, reports, …).
  • if any error occurs when executing FLUSH or RESET queries (feature called from the top menu of the software), now the message that is shown is formatted in a better way.
  • changed some red lines with blu and orange ones. Red color is typically associated to “alert status” so we have changed it for better intuitiveness. Green and red colors are now used as symptom of “no attention needed” and “attention needed”. Blue and orange lines are just graphical elements and are not associated with alert status at all.
  • better error handling when connecting on a server using a wrong STMT statement.
  • better error messages when pinging and shutdowning the server (if any error is returned).
  • several bugs fix in the Trigger Editor.
  • several bugs fix in User’s Privileges Window.
  • several bugs fix in User Editor.
  • several bugs fix when saving a script file from the Script Editor.
  • several bugs fix in the actions performed through the popup menu of the Plugins Tab of the Server Properties Window.
  • several bugs have been fixed in the Connection Window:
    • sometimes, when testing SSH Tunnels, Connection Window was saying that local port for SSH Tunnel was in use even if it was not.
    • when editing a stored connection, changes made in the local port number used for SSH Tunnel were not saved, if other options were not changed at the same time.
  • several bug fix in HoneyMonitor Options Window (when using this window with no MySQL™ connections opened, some errors were occurring).
  • several bug fix in Editor Options Window.
  • several bugs fix when attempting to reconnect: there where some bugs when, after software lost connection to the server, it was automatically trying to re-establish the connection. Now software should reconnect silentely (in a transparent way from a user point a view).
  • several bugs fix in the Federated Server Editor:
    • even if set, socket option was not included in the query when creating a Federated Server (it was working fine when the Editor was in “editing mode”);
  • several bugs fix in the HTML Report Wizard.
  • several bugs fix in LogFile Group Editor.
  • several bugs fix in TableSpace Editor.
  • several bugs fix in the Routine Editor:
    • some errors where occurring while opening the Editor with limited-privileges users;
    • parameters’ popup menus were not localized in English.
  • several bugs fix in the UDF Editor: under some conditions, a wrong DROP FUNCTION query was executed while adding a new UDF function.
  • several bugs fix in the Server Properties Window:
    • bugs fix when changing values of the variables “general_log_file” and “slow_log_file” on Windows™.
  • several bugs fix in the Metadata Catalog:
    • bugs fix when connected over MySQL™ version 6.x (MySQL™ Bug #33106).
  • minor bugs fix in the localization of the Replication Monitor.
  • minor bugs fix in Server Object List.
  • minor bugs fix in Log Management Window.
  • minor bugs fix in the Performance Tuning Monitor.
  • minor bugs fix in the Field Editor when used for Timestamp fields in MySQL™ 6.
  • minor bugs fix in the Table Editor.
  • minor bugs fix in the string “username@hostname:port” shown in the captionbar of the software: now the output of the query “SELECT CURRENT_USER();” is used.
  • minor bugs fix in the Backup Wizard.
  • minor bugs fix in the style of the Calendar controls used in some Windows (minor compatibility issues with Systems with English local Settings).
  • minor bugs fix in the Audit Reports: a better font size is now used for report head.
  • several other minor bugs fix.
• Other:
  • compatibility tests with MySQL™ ver. 5.1.31, 5.1.32, 6.0.9-alpha and 6.0.10-alpha.

Beginning with version 1.0.16, we have started some tests to ensure the compatibility of our software with Windows™ 7 - the new Microsoft™ Operating System, now in beta.

Here’s some screenshots on how HoneyMonitor looks in Windows™ 7:

Playing with MySQL™ Cluster Replication	Performance Tuning Monitor	Audit Report Preview
View Editor - Query Builder	Playing with MySQL™ Logs and Server Properties	Show Monitor and some Editors

For more information about HoneyMonitor please visit the product page at http://www.honeysoftware.com/honeymonitor.

Your questions and bug reports are welcome at support at honeysoftware dot com. You can also contact me directly for any requests you might have.

The Beta Program for the HoneyMonitor Project is running and your feedback is much appreciated! Feel free to send us also your enhancement requests and your comments about the features you would like to see in next versions of our software!

We are pleased to announce the release 1.0.15 of HoneyMonitor, our GUI for MySQL™ administration and monitoring.

In this release, available for immediate download, we have fixed some bugs without adding many new features.

We are working to release a RC version as soon as possible.

The following is the list of changes:

- New Features:

1. new menu entry Auditing / Reports / Edit Report’s Template / Custom Report.

- Improvements:

1. minor bugs fix and improvements in the Report Designer.

- Bugs Fix:

1. on Vista™, the Report Designer was losing the connection after opening the report preview. Now fixed.
2. InnoDB Performance Report: an incorrect % simbol was on the chart Innodb_rows_xxx_per_second. Now removed.

3. other minor bugs fix.

- Other:

1. compatibility tests with MySQL™ ver. 5.1.29-rc, 5.1.30, 6.0.7-alpha.

Version 1.0.16 will be released within few weeks.

Recently we also have updated the Reference Manual:

1. English Edition: v.1.0.15 Rev.226
2. Italian Edition:v.1.0.15 Rev.1043

For more information about HoneyMonitor you can visit the product page at http://www.honeysoftware.com/honeymonitor.

Your questions and bug reports are welcomed at support at honeysoftware dot com. You can also contact me directly for any requests you might have.

The Beta Program for the HoneyMonitor Project is running! Feel free to send us your enhancement requests and your comments about the features you would like to see in next versions of our software!

Other posts  you might be interested in:

• Step by Step Guide on How to Create a Customized Performance Report using HoneyMonitor

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.

We are pleased to announce the release 1.0.14 of HoneyMonitor, our GUI for MySQL™ administration and monitoring.

With this release, available for immediate download, all HoneyMonitor’s Editions:

• Lite Free
• Standard
• Enterprise
• Network
• Pro
• Audit Pro

reach the beta stage. The following is the list of changes:

• New Features:
  • new Virtual Data Grid for a better management of data in HoneyMonitor (insert / edit / delete of records) [it is linked to the Table Editor, Queries Editor, View Editor, Server Objects List].
  • new menu entry Database Objects / Tables / Open Table’s Data (Quick Way).
  • new menu entry Auditing / Reports / Custom Report. Possibility to create a customized Report using the Audit Table (useful to see how a numeric variable (raw or calculated) is changing over time).
  • new menu entry Auditing / Reports / Edit Report’s Template to edit the Templates of the Performance Reports.
  • new menu entry Utility / Edit Report (Designer).
• Improvements:
  • minor improvements in the Metadata Catalog.
  • minor improvements in the log output created by the Data Transfer.
  • several improvements and bugs fix in the Log Management Window.
  • Server Objects List:
    • support for servers’ groups;
    • minor improvements in the popup menu;
    • new menu entries “Import Link” and “Drop Link” in the popup menu of the Queries, Reports, Backups nodes;
    • new toolbar button to refresh all opened connections.
  • minor improvements to Audit Statistic Window:
    • a “Refresh” button has been added;
    • 3 new fields now show the date of the first and last audit record included in the audit table and the date difference.
  • you can now open more than one instance of the User’s Account Manager.
  • improvements in the administration of the FALCON variables.
  • minor improvements in the Report Connection Choice Window.
  • new NetStat Window to see if a local port is already used before opening a SSH tunneling connection.
  • Connection Window: new STMT option (very useful, for example to set SET SQL_LOG_BIN = 0).
• Bugs Fix:
  • bugs fix in the LogFile Group Editor.
  • Field Editor (TIMESTAMP default value).
  • minor bugs fix in the Routine Editor (parameters).
  • minor bugs fix when changing software language.
  • minor bugs fix in the Performance Tuning Monitor.
  • minor bugs fix in the Change Master Wizard.
  • bugs fix when installing the Audit Database in a Master / Slave environment: SET SQL_LOG_BIN = 0 is now used, so you don’t need to filter replication anymore.
  • minor bugs fix in the Replication Monitor.
  • bugs fix when establishing a SSH tunneling connection (Table Editor, View Editor, Queries Window).
• Other:
  • audit system: compatibility with MySQL™ 6.0.6-alpha:
    • support for the Maria Engine in v.6.x;
    • status variables Com_show_column_types and Innodb_buffer_pool_pages_latched have been deprecated (MySQL™ Bugs #36793 and #5299).

One of the most important feature included in this version is the new Virtual Data Grid that allows you to retrieve and edit your data (insert / update / delete).

Thank to the vitual system, you can open very big recordsets.

Below you find some screenshots. We will try to update the documentation and add more video tutorials as soon as possible.

For more information about HoneyMonitor you can visit the product page at http://www.honeysoftware.com/honeymonitor.

Your questions and bug reports are welcomed at support at honeysoftware dot com. You can also contact me directly.

Other posts  you might be interested in:

• HoneyMonitor v.1.0.14-alpha - New Features Preview

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

The second public alpha version (1.0.14-alpha) of HoneyMonitor Audit Pro - the Edition of the HoneySoftware’s GUI for MySQL™ mainly oriented to Server Administration, Monitoring and Tuning - will be released soon.

In this article I’ll try to describe the new features implemented and the most important bugs fix of this version.

You will find the complete list of bugs fix and improvements in a next post - when we will release v.1.0.14-alpha - and in the Release Notes File included in the build.

Your questions, enhancement requests and comments are welcome.

Contents:

A. New Features

1. Audit System
1.0. Introduction
1.1. Replication
1.1.1. STMT - New connection Option
1.2. Support for the Maria Engine
1.3. Other bugs fix

2. Performance Reports
2.0. Introduction
2.1. Custom Reports
2.2. Report Templates

3. Other
3.1. Audit Statistics
3.2. Server Objects List
3.3. FALCON

B. Other Bugs Fix

C. Other

A. New Features

1. Audit System

1.0. Introduction

As you (probably) already know, the Audit Pro Edition of HoneyMonitor uses a database (hs_audit_schema) to collect the “status data” of the server and store, manipulate and use them to create performance reports thus let you monitoring your servers.

The monitoring system that HoneyMonitor let you install and use is a “SQL-based monitoring” system - this means that no Agents are required to run on your Servers and we don’t need a Central Repository Server. All data are collected by a Scheduled Event and stored on your server.

You use a Windows™ Client (HoneyMonitor) installed for example on your laptop, to connect to your MySQL™ Server (in a direct way or through a SSH Tunneling Connection) and open the Performance Tuning Monitor to view the most common derived performance metrics and create reports to see how the ratios are changing over time.

No automated alerts are included - so far - in the HoneyMonitor Audit Pro Edition. The SQL monitoring system does not alert you - for example with an email - if a slave is down or you are reaching the maximum number of allowed connections (so far).

The Performance Tuning Monitor as well as the Replication Monitor and other tools included in HoneyMonitor are developed to help your every-days DBA activities - this is our aim - and they do not (and can’t) replace your (human) supervision of the server activities.

1.1. Replication

The most important bug fix in v.1.0.14-alpha is related to replication. Our SQL scripts now use SET SQL_LOG_BIN = 0 so no errors occur if you start the installation of the audit system (using the Audit Installation Wizard) on a Master. Audit data of the master are not replicated on slaves of course. Previously (v.1.0.13-alpha) we were asking you to filter replication by using “-replicate-ignore-table=db_name.tbl_name” and similar options but beginning with the new version 1.0.14-alpha, you don’t need to filter replication anymore (just because now we use SET SQL_LOG_BIN = 0).

1.1.1. STMT - New connection Option

Please note that now you can set a STMT (statement) option when you create a connection in HoneyMonitor. This improvement has been added in all Editions of our tool (from the Lite Free to the Network Edition). The STMT option is a SQL statement that will be executed every time the software connects to the server. A typical STMT is “SET NAMES…” (already included in early versions of the software) or “SET SQL_LOG_BIN = 0″ (if you want to connect to a master and you want that tasks performed using HoneyMonitor do not affect replication).

Fig. 1: Connection Window - STMT Option

1.2. Support for the Maria Engine

We are developing the tools you need to have a better experience with the new engine Maria (as well as Falcon). In particular, in next versions of HoneyMonitor (Standard, Enterprise and Network Editions) we plan to improve our “Create Table Wizard” to support the new Maria options.

As per Audit Pro Edition, we have tested the monitoring scripts on v.5.1.26-rc and 6.0.6-alpha. Minor bugs fix of next version 1.0.14-alpha are related to the fact that now the new Maria Engine has been put in the 6.x branch of the server (previously it was included in v.5.1.23-maria-alpha-preview. Please note that we don’t support that version anymore. If you want to test the Maria engine, please use v.6.0.6-alpha).

 

Fig. 2: MySQL™ 6.0.6-alpha - Support for Maria

1.3. Other bugs fix

In the newer version of the MySQL™ Server, status variables “Com_show_column_types” and “Innodb_buffer_pool_pages_latched” have been deprecated so we don’t use them in our audit table anymore [MySQL™ Bugs #36793 and #5299].

2. Performance Reports

2.0. Introduction

Two important improvements are included in the new version 1.0.14-alpha.

2.1. Custom Reports

You can now create your own reports (a Custom Report Template is included and you can use this as the basis of your own reports). HoneyMonitor Audit Pro includes about 10 Reports (Falcon, InnoDB, MyISAM, Temporay Tables, …) for about 90 charts. With a custom report you can choose to monitor a specific variable not included in the standard reports. We will try to create a video tutorial about this new feature soon.

Fig. 3: New entries in the “Auditing” Menu

 

Fig. 4: Creating a Custom Performance Report - Designer

 

Fig. 5: Creating a Custom Performance Report - Choosing Variables to add (SQL query)

2.2. Report Templates

You can now edit the Report Templates thus every time you use the Performance Tuning Monitor to create a report your templates will be used (it’s the same when you use the “Audit Report Generation Window ” to create a report in a fast way, by clicking on the menu Auditing / Reports / …). We suggest you backup our templates before editing them so you can restore them if you need (just copy the files included in the directory /audit/reports).

 

Fig. 6: Customizing Template of the “Query Cache” Report

3. Other

3.1. Audit Statistics

Minor improvements are included in the Audit Statistics Window. A “Refresh” button has been added; 3 new fields now show the date of the first and last audit record included in the audit table and the date difference.

Fig. 7: Audit Statistics Window

3.2. Server Objects List

Minor improvements in the Server Objects List. When you create a (stored) connection you can now choose a “Server Group”

Fig. 8: Connection Window - Server Group

Fig. 9:  Server Objects List - Server Groups

3.3. FALCON

Minor improvements for FALCON variables: Description, Type, Handle, …, are now included (like for other “older” system variables).

Fig. 10: Performance Tuning Monitor - Falcon Variables

B. Other Bugs Fix

For a bug of version 1.0.13-alpha, some errors occur when opening the Table Editor, View Editor and Queries Window if you are connected through SSH. Now fixed.

C. Other

Similar Posts you could be interested on:

• Performance Monitoring, Tuning & Auditing in MySQL 5.1 – A GUI Approach - Part-1

Let us know what you would like us to add in the next versions of our software and, if you would be informed when we will release the new v.1.0.14-alpha, you are welcome to join our newsletter.

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