Zabbix-mysql Table Partitioning

mysql> Alter table History_text Drop primary key, add index (ID), DROP index history_text_2, add index history_text_2 ( Itemid, id);

mysql> Alter table History_log Drop primary key, add index (ID), DROP index history_log_2, add index history_log_2 (ite Mid, id);

#1 >

DELIMITER $$

CREATE PROCEDURE ' partition_create ' (SCHEMANAME varchar), TABLENAME varchar (+), PartitionName varchar (+), CLOCK int )

BEGIN

/*

SCHEMANAME = The DB schema in which to make changes

TABLENAME = The table with partitions to potentially delete

PartitionName = The name of the partition to create

*/

/*

Verify that the partition does not already exist

*/

DECLARE retrows INT;

SELECT COUNT (1) into retrows

From Information_schema.partitions

WHERE Table_schema = SCHEMANAME and table_name = TABLENAME and partition_description >= CLOCK;

IF retrows = 0 Then

/*

1. Print a message indicating that a partition is created.

2. Create the SQL to create the partition.

3. Execute the SQL from #2.

*/

Select CONCAT ("Partition_create (", SCHEMANAME, ",", TABLENAME, ",", PartitionName, ",", CLOCK, ")") as MSG;

SET @sql = CONCAT (' ALTER TABLE ', SCHEMANAME, '. ', TABLENAME, ' ADD PARTITION (PARTITION ', partitionname, ' VALUES less THAN (', CLOCK, '));

PREPARE STMT from @sql;

EXECUTE STMT;

Deallocate PREPARE STMT;

END IF;

end$$

DELIMITER;

#2 >

DELIMITER $$

CREATE PROCEDURE ' partition_drop ' (SCHEMANAME varchar, TABLENAME varchar, delete_below_partition_date BIGINT)

BEGIN

/*

SCHEMANAME = The DB schema in which to make changes

TABLENAME = The table with partitions to potentially delete

Delete_below_partition_date = Delete Any partitions with names that is dates older than this one (YYYY-MM-DD)

*/

DECLARE done INT DEFAULT FALSE;

DECLARE drop_part_name VARCHAR (16);

/*

Get a list of all the partitions that is older than the date

In Delete_below_partition_date. All partitions is prefixed with

A "P", so use SUBSTRING to get rid of the that character.

*/

DECLARE MyCursor CURSOR for

SELECT Partition_name

From Information_schema.partitions

WHERE Table_schema = SCHEMANAME and table_name = TABLENAME and CAST (SUBSTRING (Partition_name from 2) as UNSIGNED) < DEL Ete_below_partition_date;

DECLARE CONTINUE HANDLER for don't FOUND SET done = TRUE;

/*

Create the basics for if we need to drop the partition. Also, create

@drop_partitions to hold a comma-delimited list of all partitions that

should be deleted.

*/

SET @alter_header = CONCAT ("Alter TABLE", SCHEMANAME, ".", TABLENAME, "DROP PARTITION");

SET @drop_partitions = "";

/*

Start looping through all the partitions is too old.

*/

OPEN MyCursor;

Read_loop:loop

FETCH mycursor into Drop_part_name;

IF do Then

LEAVE Read_loop;

END IF;

SET @drop_partitions = IF (@drop_partitions = "", Drop_part_name, CONCAT (@drop_partitions, ",", Drop_part_name));

END LOOP;

IF @drop_partitions! = "" Then

/*

1. Build the SQL to drop all the necessary partitions.

2. Run the SQL to drop the partitions.

3. Print out of the table partitions that were deleted.

*/

SET @full_sql = CONCAT (@alter_header, @drop_partitions, ";");

PREPARE STMT from @full_sql;

EXECUTE STMT;

Deallocate PREPARE STMT;

Select CONCAT (SCHEMANAME, ".", TABLENAME) as ' table ', @drop_partitions as ' partitions_deleted ';

ELSE

/*

No partitions is being deleted, so print out "N/a" (not applicable) to indicate

That no changes were made.

*/

Select CONCAT (SCHEMANAME, ".", TABLENAME) as ' table ', ' N/a ' as ' partitions_deleted ';

END IF;

end$$

DELIMITER;

#3 >

DELIMITER $$

CREATE PROCEDURE ' partition_maintenance ' (schema_name varchar), table_name varchar (+), Keep_data_days INT, Hourly_ INTERVAL int, create_next_intervals int)

BEGIN

DECLARE older_than_partition_date VARCHAR (16);

DECLARE partition_name VARCHAR (16);

DECLARE old_partition_name VARCHAR (16);

DECLARE Less_than_timestamp INT;

DECLARE Cur_time INT;

Call Partition_verify (schema_name, TABLE_NAME, hourly_interval);

SET cur_time = Unix_timestamp (Date_format (now (), '%y-%m-%d 00:00:00 '));

SET @__interval = 1;

Create_loop:loop

IF @__interval > Create_next_intervals Then

LEAVE Create_loop;

END IF;

SET Less_than_timestamp = cur_time + (hourly_interval * @__interval * 3600);

SET partition_name = from_unixtime (Cur_time + hourly_interval * (@__interval-1) * 3600, ' p%y%m%d%h00 ');

IF (partition_name! = old_partition_name) Then

Call Partition_create (schema_name, TABLE_NAME, partition_name, Less_than_timestamp);

END IF;

SET @[email protected]__interval+1;

SET old_partition_name = partition_name;

END LOOP;

SET Older_than_partition_date=date_format (Date_sub (now (), INTERVAL keep_data_days Day), '%y%m%d0000 ');

Call Partition_drop (schema_name, TABLE_NAME, older_than_partition_date);

end$$

DELIMITER;

#4 >

DELIMITER $$

CREATE PROCEDURE ' partition_verify ' (SCHEMANAME varchar, TABLENAME varchar), Hourlyinterval INT (11))

BEGIN

DECLARE partition_name VARCHAR (16);

DECLARE retrows INT (11);

DECLARE Future_timestamp TIMESTAMP;

/*

* Check If any partitions exist for the given SCHEMANAME. TABLENAME.

*/

SELECT COUNT (1) into retrows

From Information_schema.partitions

WHERE Table_schema = SCHEMANAME and table_name = TABLENAME and Partition_name is NULL;

/*

* IF partitions do not exist, go ahead and partition the table

*/

IF retrows = 1 Then

/*

* Take the current date @ 00:00:00 and add hourlyinterval to it. This is the timestamp below which we'll store values.

* We begin partitioning based on the beginning of a day. This is because we don ' t want to generate a random partition

* That won ' t necessarily fall on line with the desired partition naming (Ie:if the hour interval are hours, we could

* end up creating a partition today named "p201403270600" when all other partitions would be to like "p201403280000").

*/

SET Future_timestamp = Timestampadd (HOUR, Hourlyinterval, CONCAT (Curdate (), "", ' 00:00:00 '));

SET partition_name = Date_format (Curdate (), ' p%y%m%d%h00 ');

--Create The partitioning query

SET @__partition_sql = CONCAT ("ALTER TABLE", SCHEMANAME, ".", TABLENAME, "PARTITION by RANGE (' clock ')");

SET @__partition_sql = CONCAT (@__partition_sql, "(PARTITION", Partition_name, "VALUES less THAN", Unix_timestamp (FUTUR E_timestamp), "));

--Run The partitioning query

PREPARE STMT from @__partition_sql;

EXECUTE STMT;

Deallocate PREPARE STMT;

END IF;

end$$

DELIMITER;

#4 >

DELIMITER $$

CREATE PROCEDURE ' Partition_maintenance_all ' (schema_name VARCHAR (32))

BEGIN

Call Partition_maintenance (schema_name, ' history ', 90, 720, 20);

Call Partition_maintenance (schema_name, ' History_log ', 90, 720, 20);

Call Partition_maintenance (schema_name, ' History_str ', 90, 720, 20);

Call Partition_maintenance (schema_name, ' History_text ', 90, 720, 20);

Call Partition_maintenance (schema_name, ' History_uint ', 90, 720, 20);

Call Partition_maintenance (schema_name, ' trends ', 730, 720, 20);

Call Partition_maintenance (schema_name, ' Trends_uint ', 730, 720, 20);

end$$

DELIMITER;

#5 >

Mysql> call Partition_maintenance_all (' Zabbix ');

Zabbix-mysql Table Partitioning