How to manually partition the UIM Database for MySQL

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

The following document describes a manual process for partitioning tables on a UIM 8.31 or higher Database hosted on MySQL 5.6 or above.  Prior versions of UIM/MySQL do not support this functionality.

When enabling partitioning in an environment with a very large database, it may be advisable to manually partition the largest tables before allowing the data_engine to partition the smaller ones; the data_engine may otherwise time out during maintenance periods if it is allowed to attempt partitioning the tables on its own.

Generally speaking, we always recommend that partitioning should be enabled where possible; even on a smaller database, it will improve the performance of data maintenance.  On a larger database (over 10GB in size) this can be especially important, as data maintenance on an unpartitioned database can take many hours to complete.

While it is difficult to pinpoint an exact number of rows which constitutes a "large" table that is a candidate for manual partitioning, a rule of thumb is to manually partition any table with >50 million rows. 

Resolution

Below is the full process and query for manually partitioning the tables associated with a QoS definition.

 ************IMPORTANT WARNING************

Please review this and all queries with your DBA before using this Article. This information is provided on a "use at your own risk" basis.

We also highly recommend that these queries and their results be tested in a Test/DEV environment before running in Production.

Note that once you begin this process, you have 14 days to complete it. As soon as you begin the next step here there's no turning back. If the process is not completed within 14 days, then you risk losing 1 days’ worth of data each day until the process is completed.

It is also important to note that in MySQL the partitioning is an OFFLINE operation; this means that while you are partitioning a table, data_engine won't be able to insert to that table as it will be locked.  This could lead to alarms from data_engine regarding the insert failures, and queuing of QoS data, but the blocked data will not be lost - it will be saved offline and automatically re-inserted in bulk when the process is completed.

1. using Raw Configure on the data_engine probe, set the following two configuration keys as follows:

data_management_active = no

data_management_partition = yes

Restart data_engine after setting these. This will prevent data_engine from trying to do any automatic maintenance and interrupt our manual process, while also configuring the maintenance stored procedures to enable partitioning.

 

2. Execute the following script on the MySQL Server: 

set @RCR = 0;

set @HCR = 0;

set @RSR = 0;

set @HSR = 0;

set @RC = 0;

set @EMSG = NULL;

call spn_de_DataMaint(1,'all',null,5,@RCR,@HCR,@RSR,@HSR,@EMSG,@RC);  --- change the '1' in the first parameter to denote which table you want to partition

select @RCR,@HCR,@RSR,@HSR,@EMSG,@RC;

 

The first parameter in the script correlates with a qos_def_id (and subsequently the RN/HN/etc tables, e.g. RN_QOS_DATA_0001).  So, if you want to manually partition table RN_QOS_DATA_0020, you would use: 

call spn_de_DataMaint(20,'all',null,5,@RCR,@HCR,@RSR,@HSR,@EMSG,@RC);

This procedure will automatically partition the table and perform up-to-date data maintenance on the table at the same time.

Note: Depending on the table size, it may take a long time (many hours) to partition each table.   We recommend starting with the largest table and using this as a benchmark to determine how long the other tables might take.

You can verify partitioning is successful with the following query (substitute your database name and table name as appropriate)

 SELECT count(*)

    FROM  INFORMATION_SCHEMA.PARTITIONS

    WHERE TABLE_SCHEMA = 'CAUIM'

    AND   TABLE_NAME   = 'RN_QOS_DATA_0001'; 

The number returned should be equal to the sum of the values in data_engine.cfg for the config keys "delete_raw_samples" and "raw_data_extra."  (e.g. if you have delete_raw_samples set to 180, and raw_data_extra set to 15, the above query should return a count of 195.)

Once you have completed the manual partitioning, set "data_management_active" back to "yes" in the data_engine.cfg and restart data_engine one more time to ensure that maintenance is re-enabled.