41.9 Partitions

The support for the table partitioning was added in MySQL^TM v.5.1.

Partitioning allows you to distribute portions of individual tables across a filesystem according to rules which you can set largely as needed. In effect, different portions of a table are stored as separate tables in different locations. The user-selected rule by which the division of data is accomplished is known as a partitioning function, which in MySQL^TM can be the modulus, simple matching against a set of ranges or value lists, an internal hashing function, or a linear hashing function. The partitioning is a horizontal partitioning that is, different rows of a table may be assigned to different physical partitions. MySQL^TM 5.1 does not support vertical partitioning, in which different columns of a table are assigned to different physical partitions.

For further information about partitioning please refer to the MySQL^TM Reference Manual.

HoneyMonitor provides you a simple and complete GUI for manage the partitioning.
You can use the Partitions Tab of the Table Editor to manage your partitions (sec. 41.4.5): from here you can partition a table, add a partition to a partitioned table, drop a partition, re-partition a table.

Creating a Partition
To add a Partition to a Partitioned Table:

1. open the ''Server Object List'' (chap. 16);
2. expand the ''Tables'' node relating the database which contains the table you want to create the partition in;
3. double click on the table name;
4. click on the ''Partitions'' Tab of the ''Table Editor'' (sec. 41.4);
5. click on the ''Add Partition'' button (or right click and select the''Add Partition'' item from the popup menu);
6. use the ''Add Partition Window'' (sec. 41.9.1).

Dropping a Partition
To drop a RANGE or LIST Partition from a Partitioned Table :

1. open the ''Server Object List'' (chap. 16);
2. expand the ''Tables'' node relating the database which contains the table you want to drop the partition from;
3. double click on the table name;
4. click on the ''Partitions'' Tab of the ''Table Editor'' (sec. 41.4);
5. in the partitions' grid select the row relating the partition you want to drop;
6. click on the ''Drop selected Partition'' button (or right click and select the ''Drop Partition'' item from the popup menu);
7. confirm the requested operation.

Attention: All data belong to the partition will be dropped.

Please, see ''Aggregating Partitions'' topic for more information on dropping HASH or KEY partitions.

Aggregating Partitions
To drop one or more HASH or KEY partitions from a Partitioned Table by aggregating them in the other partitions:

1. open the ''Server Object List'' (chap. 16);
2. expand the ''Tables'' node relating the database which contains the table which contains the partitions you want to aggregate;
3. double click on the table name;
4. click on the ''Partitions'' Tab of the ''Table Editor'' (sec. 41.4);
5. click on the ''Aggregate Partition'' button.

Please, see ''Dropping a Partition'' topic for more information on dropping RANGE or LIST partitions.

Multi-Drop of Partitions (Range or List)
To drop a set of RANGE or LIST Partitions of a table:

1. open the ''Server Object List'' (chap. 16);
2. expand the table node relating the database which contains the table you want to drop the partitions from;
3. right click on the ''Partitions'' node and select the ''Multi-Drop of Partitions'' item from the popup menu;
4. use the ''Multi-Drop Wizard'' (chap. 26).

Attention: All data belongs to the dropped partitions will be dropped.

Partitioning a Table
To partition a Table:

1. open the ''Server Object List'' (chap. 16);
2. expand the table node relating the database which contains the table you want to partition;
3. double click on the table name;
4. click on the ''Partitions'' Tab of the ''Table Editor'' (sec. 41.4);
5. click on the ''Add Partitioning'' button;
6. use the ''Partition Editor'' (sec. 41.9.2).

Reorganizeing Partitions
To edit one or more Partitions of a Partitioned Table:

1. open the ''Server Object List'' (chap. 16);
2. expand the ''Tables'' node relating the database which contains the table which contains the partitions you want to reorganize;
3. double click on the table name;
4. click on the ''Partitions'' Tab of the ''Table Editor'' (sec. 41.4);
5. click on the ''Reorganize Partition'' button;
6. use the ''Reorganize Partitions Window'' (sec. 41.9.3).

Repartitioning a Table
To completely repartition a Partitioned Table:

1. open the ''Server Object List'' (chap. 16);
2. expand the table node relating the database which contains the table you want to repartition;
3. double click on the table name;
4. click on the ''Partitions'' Tab of the ''Table Editor'' (sec. 41.4);
5. click on the ''Edit Partitioning'' button.
6. use the ''Partition Editor'' (sec. 41.9.2).

Removing the Partitioning from a Table
To drop the partitioning from a table:

1. open the ''Server Object List'' (chap. 16);
2. expand the ''Tables'' node relating the database which contains the table you want to drop the partitioning from;
3. double click on the table name;
4. click on the ''Partitions'' Tab of the ''Table Editor'' (sec. 41.4);
5. click on the ''Drop Partitioning'' button;
6. confirm the requested operation.

Note: All partitions will be dropped but the data are preserved.

41.9.1 Add Partition Window

The Add Partition Window allows you to add a partition to a partitioned table.
The window (Fig. 41.62) can be opened from the ''Partitions'' Tab of the Table Editor (see the introduction of the sec. 41.9).

See Also:

•  41.4.5: Table Editor, Partitions Tab
•  41.9.2: Partition Editor
•  41.9.3: Reorganize Partitions Window
•  41.9.4: Partition's Maintenance

To add a partition you must set its name (at least). For RANGE or LIST partitions you have to set the partition value (e.g. ''Less than (1982)'', or ''Less than MAXVALUE'') or the value's list (e.g. ''Values IN (2,3)''). The other parameters are optional.

Please, refer to the MySQL^TM Reference Manual for a detailed description of partition options.

Note: Data Directory and Index Directory must be absolute path.

Example:

    DATA DIRECTORY = '/var/appdata/95/data'
    INDEX DIRECTORY = '/var/appdata/95/idx'

On Windows^TM, you must use the / (slash) character and not the \ (backslash) character when specifying paths for these directories.

Example:

    DATA DIRECTORY='d:/s1/data/'
    INDEX DIRECTORY = 'd:/s1/idx/'

You may execute the query

    SHOW VARIABLES LIKE 'have_symlink';

to see if your systems supports symbolic links.

To add one or more subpartitions click on the ''Subpartitions'' Tab (sec. 41.9.1), otherwise click on the ''Ok'' button.

41.9.1.1 Subpartitions

The ''Subpartitions'' Tab allows you to add one or more subpartitions to the partition being created (Fig. 41.63).

You must set the subpartition's name at least; please, refer to the MySQL^TM Reference Manual for a detailed description of the subpartition options.

Note: Data Directory and Index Directory must be absolute path.

Example:

    DATA DIRECTORY = '/var/appdata/95/data'
    INDEX DIRECTORY = '/var/appdata/95/idx'

On Windows^TM, you must use the / (slash) character and not the \ (backslash) character when specifying paths for these directories.

Example:

    DATA DIRECTORY='d:/s1/data/'
    INDEX DIRECTORY = 'd:/s1/idx/'

You may execute the query

    SHOW VARIABLES LIKE 'have_symlink';

to see if your systems supports symbolic links.

The subpartitions grid at the bottom of the window lists the subpartitions being created in the current partition. You may drop a subpartition or move it up or down using the subpartition toolbar.

41.9.2 Partition Editor

The Partition Editor allows you to create, edit and do the partition's maintenance.

The graphical interface of this Editor changes slightly according the involved action (adding partitioning, Fig. 41.64, or editing partitioning, Fig. 41.65).

Note: The Partition Editor allows you to do a complete repartition of a table. To do a simple reorganize of partitions (the reorganize syntax is ''ALTER TABLE table_name REORGANIZE PARTITION partition_names INTO (partition_definitions)) you can use the ''Reorganize Partitions Window'' (sec. 41.9.3).

The ''Editor'' can be opened from the Create Table Wizard or from the Table Editor (see the introduction of the sec. 41.9).

See Also:

•  41.1.7: Create Table Wizard, Partitions Step
•  41.4.5: Table Editor, Partition Tab
•  41.9.1: Add Partition Window
•  41.9.3: Reorganize Partition Window
•  41.5.1: Field Editor
•  41.6.1: Index Editor
•  41.7.1: Foreign Keys Editor
•  41.8.1: Trigger Editor

Toolbar
Using the toolbar you may (please, note that toolbar is not visible while adding partitioning):

• refresh the ''Editor'';
• save your changes;
• undo the repartitioning (if you choose to repartition the table, you can undo that action clicking this button);
• repartition the table (a complete repartition);
• view the help;
• close the ''Editor''.

41.9.2.1 Advanced

This Tab (Fig. 41.66) allows you to view and set the main properties of the partitioning:

• the partition type: RANGE, LIST, HASH, KEY for partitions, HASH and KEY for subpartitions;
• the partitioning and subpartitioning functions;
• the partitions and subpartitions number (optional).

Note: If your table is already partitioned, these options are not-editable; to edit them, click on the ''Table Repartitioning'' button.

41.9.2.2 Partitions

This Tab changes slightly according the involved action (adding partitioning, or editing partitioning).

The partitions/subpartitions scheme is visible in the tree at the left.

If your table is already partitioned and you click on a partition from the tree, information on that partition, included some statistical-charts, will appear (Fig. 41.67).

More information are showed if you click on a chart.

Toolbar
Using the toolbar of this Tab you may:

• add a partition;
• drop a partition;
• rebuild a partition;
• optimize a partition;
• analyze a partition;
• repair a partition;
• check a partition.

Further information about partition's maintenance can be found in sec. 41.9.4.

41.9.2.3 SQL

This Tab (Fig. 41.68) is enabled only when you are adding the partitioning or you are repartitioning a table. It contains the partitioning SQL query; you may edit the query, but if you manually edit the query, you'll cannot use the ''Advanced'' and ''Partitions'' Tabs until you save (or undo) your changes.

41.9.3 Reorganize Partitions Window

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

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

The Reorganize Partitions Window can be opened from the ''Partitions'' Tab of the Table Editor (see the introduction to the sec. 41.9).

See Also:

•  41.4.5: Table Editor, Partitions Tab
•  41.9.2: Partition Editor
•  41.9.1: Add Partition Window
•  41.9.4: Partition's Maintenance

41.9.3.1 Origin

Select a partition from the drop-down combo then click on the ''Add partition in the list...'' button (Fig. 41.70). When done click on the ''Target'' Tab.

41.9.3.2 Target

Write the reorganize partition query or click on the ''Use Partition Editor...'' to create it graphically (Fig. 41.71).

41.9.4 Partition's Maintenance

HoneyMonitor gives you the necessary tools to do the maintenance of your table's partitions. For the partitioned tables, these tool replaces the table's maintenance tools (sec. 41.10) that cannot be used for that tables.

41.9.4.1 Rebuild Partitions Wizard

The Rebuild Partitions Wizard allows you to defragment one or more partitions (this has the same effect as dropping all records stored in the partition, then reinserting them). It implements the SQL syntax REBUILD PARTITION.

To open the Wizard click on the ''Database Objects / Tables / Table's Maintenance / Partitions'' menu.

See Also:

•  41.9.4: Optimize Partitions Wizard
•  41.9.4: Analyze Partitions Wizard
•  41.9.4: Repair Partitions Wizard
•  41.9.4: Check Partitions Wizard

41.9.4.1.1 Choosing database and table

Please, select a connection, a database and a partitioned table to be rebuild (Fig. 41.72) then click on the ''Next'' button.

41.9.4.1.2 Choosing Partitions

Select the partitions to be rebuild then click on the ''Execute'' button (Fig. 41.73).

When done an information message about the operation will be showed.

Options Description:

41.9.4.2 Optimize Partitions Wizard

The Optimize Partitions Wizard allows you to reclaim any unused space and to defragment the partition data file. This can be useful if you have deleted a large number of rows from a partition or if you have made many changes to a partitioned table with variable-length rows (that is, having VARCHAR, BLOB, or TEXT columns). It implements the SQL syntax OPTIMIZE PARTITION.

To open the Wizard click on the ''Database Objects / Tables / Table's Maintenance / Partitions'' menu.

See Also:

•  41.9.4: Rebuild Partitions Wizard
•  41.9.4: Analyze Partitions Wizard
•  41.9.4: Repair Partitions Wizard
•  41.9.4: Check Partitions Wizard

41.9.4.2.1 Choosing database and table

Please, select a connection, a database and a partitioned table to be optimized (Fig. 41.74) then click on the ''Next'' button.

41.9.4.2.2 Choosing Partitions

Select the partitions to be optimized then click on the ''Execute'' button (Fig. 41.75).

When done an information message about the operation will be showed.

Options Description:

41.9.4.3 Analyze Partitions Wizard

The Analyze Partitions Wizard allows you to read and store the key distributions for partitions. It implements the SQL syntax ANALYZE PARTITION.

To open the Wizard click on the ''Database Objects / Tables / Table's Maintenance / Partitions'' menu.

See Also:

•  41.9.4: Rebuild Partitions Wizard
•  41.9.4: Optimize Partitions Wizard
•  41.9.4: Repair Partitions Wizard
•  41.9.4: Check Partitions Wizard

41.9.4.3.1 Choosing database and table

Please, select a connection, a database and a partitioned table to be analyzed (Fig. 41.76) then click on the ''Next'' button.

41.9.4.3.2 Choosing Partitions

Select the partitions to be analyzed then click on the ''Execute'' button (Fig. 41.77).

When done an information message about the operation will be showed.

Options Description:

41.9.4.4 Repair Partitions Wizard

The Repair Partitions Wizard allows you to repair corrupted partitions. It implements the SQL syntax REPAIR PARTITION.

To open the Wizard click on the ''Database Objects / Tables / Table's Maintenance / Partitions'' menu.

See Also:

•  41.9.4: Rebuild Partitions Wizard
•  41.9.4: Optimize Partitions Wizard
•  41.9.4: Analyze Partitions Wizard
•  41.9.4: Check Partitions Wizard

41.9.4.4.1 Choosing database and table

Please, select a connection, a database and a partitioned table to be repaired (Fig. 41.78) then click on the ''Next'' button.

41.9.4.4.2 Choosing Partitions

Select the partitions to be repaired then click on the ''Execute'' button' (Fig. 41.79).

When done an information message about the operation will be showed.

Options Description:

41.9.4.5 Check Partitions Wizard

The Check Partitions Wizard allows you to check partitions for errors. It implements the SQL syntax CHECK PARTITION.

To open the Wizard click on the ''Database Objects / Tables / Table's Maintenance / Partitions'' menu.

See Also:

•  41.9.4: Rebuild Partitions Wizard
•  41.9.4: Optimize Partitions Wizard
•  41.9.4: Analyze Partitions Wizard
•  41.9.4: Repair Partitions Wizard

41.9.4.5.1 Choosing database and table

Please, select a connection, a database and a partitioned table to be checked (Fig. 41.80) then click on the ''Next'' button.

41.9.4.5.2 Choosing Partitions

Select the partitions to be checked then click on the ''Execute'' button (Fig. 41.81).

When done an information message about the operation will be showed.

Options Description:

---

Copyright © 2007 - 2009 HoneySoftware - All Rights Reserved

Submit feedback on this topic.