Dear Kaj, Giuseppe, Jay, Lenz, Colin,
MySQL™ Community Team,

This is my Report for the MySQL™ 5.1 Use Case Competition.

Before start reporting on my use of the new 5.1 Features, just a note.

I was quite sure on early August 2008 that including similar posts in my Report would be allowed. But during an email conversation, Giuseppe told me that similar Reports won’t qualify for the competition because the Community Team was looking for Reports on ‘Real Word’ usage of MySQL™ 5.1 in production systems rather than a description of software specific features though implemented on MySQL™ 5.1 features.

BTW, I decided to go on with my series of articles as I wrote in this post (I published it only today, before it was just a draft).

Nevertheless, few weeks ago, after reading this post by Lenz I changed my mind once again about the real stuff a UC Report should contain and today I think that my Report could be admitted (if that has been admitted).

So here’s my report ;-)

http://www.honeysoftware.it/sleto/blog/ucc/sleto-MySQL-5.1-Use-Case-Reports.pdf

Username and password has been sent to the Community email address.

Enjoy!

My last post Using Partitioning in MySQL™ 5.1 would be the last post of the Series “Using Feature XXX in MySQL™ 5.1″.

I started write this series of articles on August 2008 when I heard about the MySQL™ 5.1 Use Case Competition and decided to let the Community Team of MySQL™ know about what I’ve developed and included in HoneyMonitor, the HoneySoftware’s GUI for MySQL™.

I’ll comment about the 5.1 UCC later on a new post.

Here’s the links of the articles included in the series:

1. Using Partitioning in MySQL™ 5.1
2. Using Table Log in MySQL™ 5.1
3. Using Cluster Disk Data in MySQL™ 5.1
4. Using Plugins in MySQL™ 5.1
5. Using Federated Servers in MySQL™ 5.1
6. Using Scheduled Events in MySQL™ 5.1

I’m sure this articles won’t be useful for guru or users who, like me, are using v. 5.1 since early versions: probably all of them know pretty well new features of MySQL™ 5.1. On the contrary I hope that this articles could be useful for newbies or for users of the version 5.0 (or 4.1) who were not aware of the amazing new features included in v. 5.1 (or for users who are thinking to upgrade).

Of course, all new features are described in those articles from my prospective (that is a GUI prospective).

If you are not sure about upgrading your Database Server to v.5.1 the following (partial) list of articles could help you taking your decision, too:

• Why you want to switch to MySQL 5.1 (Sheeri Cabral)
• Choosing MySQL 5.1 over 5.0 (Ronald Bradford)

Please, feel free to add a comment with the links to your articles. The above list refers just to the articles I can mind at this moment.

Now that this series is completed, by the end of the year I’ll try to write the second and third parts of the series “Performance Monitoring, Tuning & Auditing in MySQL™ 5.1 - A GUI Approach”.

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