Partitioning Tables on Zabbix 1.8

Posted by Ricardo Santos in database, mysql, performance, server, zabbix on May 18th, 2011 | 44 comments

Many performance problems on Zabbix are related to the database and it became visible only when we have thousands of items. On another side, we have to handle enormous tables and it makes impossible operational routines like housekeeping and backup.

Use all information and scripts at your own risk. Make backups!

My suggestion is disable housekeeping and to partition some tables based on clock, an unix timestamp field. If you wanna know more about partitioning access the MySQL Reference Manual – Partitioning Overview.

The Concept

An easy way to handle partitioning is splitting the tables into two groups based on their growth:

Daily – basically the largest tables

history
history_log
history_str
history_text
history_uint

See more information about history tables.

Monthly – other huge tables

acknowledges
alerts
auditlog
events
service_alarms
trends
trends_unit

Step 1 – Preparing the Tables

To partition a table using the clock, the field needs belong to the primary key or not have one. So we need make some key changes on this tables:

ALTER TABLE `acknowledges` DROP PRIMARY KEY, ADD KEY `acknowledgedid` (`acknowledgeid`);
ALTER TABLE `alerts` DROP PRIMARY KEY, ADD KEY `alertid` (`alertid`);
ALTER TABLE `auditlog` DROP PRIMARY KEY, ADD KEY `auditid` (`auditid`);
ALTER TABLE `events` DROP PRIMARY KEY, ADD KEY `eventid` (`eventid`);
ALTER TABLE `service_alarms` DROP PRIMARY KEY, ADD KEY `servicealarmid` (`servicealarmid`);
ALTER TABLE `history_log` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_log` DROP KEY `history_log_2`;
ALTER TABLE `history_text` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_text` DROP KEY `history_text_2`;

ALTER TABLE `acknowledges` DROP PRIMARY KEY, ADD KEY `acknowledgedid` (`acknowledgeid`);

ALTER TABLE `alerts` DROP PRIMARY KEY, ADD KEY `alertid` (`alertid`);

ALTER TABLE `auditlog` DROP PRIMARY KEY, ADD KEY `auditid` (`auditid`);

ALTER TABLE `events` DROP PRIMARY KEY, ADD KEY `eventid` (`eventid`);

ALTER TABLE `service_alarms` DROP PRIMARY KEY, ADD KEY `servicealarmid` (`servicealarmid`);

ALTER TABLE `history_log` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);

ALTER TABLE `history_log` DROP KEY `history_log_2`;

ALTER TABLE `history_text` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);

ALTER TABLE `history_text` DROP KEY `history_text_2`;

Step 2 – Setup Monthly Partitions

Repeat this step with each table (see above) with appropriate months.

In this example, we’ll create the monthly partitions to events table since May 2011 until December 2011:

ALTER TABLE `events` PARTITION BY RANGE( clock ) (
PARTITION p201105 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-01 00:00:00")),
PARTITION p201106 VALUES LESS THAN (UNIX_TIMESTAMP("2011-07-01 00:00:00")),
PARTITION p201107 VALUES LESS THAN (UNIX_TIMESTAMP("2011-08-01 00:00:00")),
PARTITION p201108 VALUES LESS THAN (UNIX_TIMESTAMP("2011-09-01 00:00:00")),
PARTITION p201109 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-01 00:00:00")),
PARTITION p201110 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-01 00:00:00")),
PARTITION p201111 VALUES LESS THAN (UNIX_TIMESTAMP("2011-12-01 00:00:00")),
PARTITION p201112 VALUES LESS THAN (UNIX_TIMESTAMP("2012-01-01 00:00:00"))
);

ALTER TABLE `events` PARTITION BY RANGE( clock ) (

PARTITION p201105 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-01 00:00:00")),

PARTITION p201106 VALUES LESS THAN (UNIX_TIMESTAMP("2011-07-01 00:00:00")),

PARTITION p201107 VALUES LESS THAN (UNIX_TIMESTAMP("2011-08-01 00:00:00")),

PARTITION p201108 VALUES LESS THAN (UNIX_TIMESTAMP("2011-09-01 00:00:00")),

PARTITION p201109 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-01 00:00:00")),

PARTITION p201110 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-01 00:00:00")),

PARTITION p201111 VALUES LESS THAN (UNIX_TIMESTAMP("2011-12-01 00:00:00")),

PARTITION p201112 VALUES LESS THAN (UNIX_TIMESTAMP("2012-01-01 00:00:00"))

);

Step 3 – Setup Daily Partitions

Repeat this step with each table (see above) with appropriate days.

In this example, we’ll create the daily partitions to history_uint table since 15th May until 22th May:

ALTER TABLE `history_uint` PARTITION BY RANGE( clock ) (
PARTITION p20110515 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-16 00:00:00")),
PARTITION p20110516 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-17 00:00:00")),
PARTITION p20110517 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-18 00:00:00")),
PARTITION p20110518 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-19 00:00:00")),
PARTITION p20110519 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-20 00:00:00")),
PARTITION p20110520 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-21 00:00:00")),
PARTITION p20110521 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-22 00:00:00")),
PARTITION p20110522 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-23 00:00:00"))
);

ALTER TABLE `history_uint` PARTITION BY RANGE( clock ) (

PARTITION p20110515 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-16 00:00:00")),

PARTITION p20110516 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-17 00:00:00")),

PARTITION p20110517 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-18 00:00:00")),

PARTITION p20110518 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-19 00:00:00")),

PARTITION p20110519 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-20 00:00:00")),

PARTITION p20110520 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-21 00:00:00")),

PARTITION p20110521 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-22 00:00:00")),

PARTITION p20110522 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-23 00:00:00"))

);

Maintaining the Partitions Manually

Adding new partitions:

ALTER TABLE `history_uint` ADD PARTITION (
PARTITION p20110523 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-24 00:00:00"))
);

ALTER TABLE `history_uint` ADD PARTITION (

PARTITION p20110523 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-24 00:00:00"))

);

Dropping partitions (Housekeeping):

ALTER TABLE `history_uint` DROP PARTITION p20110515;

1	ALTER TABLE `history_uint` DROP PARTITION p20110515;

Step 4 – Auto Daily Partitioning

Make sure that you created the partitioning correctly on the history tables (Step 3)

This script drops and creates automatically the daily partitioning.

The default is keep only last 3 days, if you wanna more days change @mindays variable.

Don’t forget to put it on cron:

mysql -B -h localhost -u zabbix -pPASSWORD zabbix -e "CALL create_zabbix_partitions();"

1	mysql -B -h localhost -u zabbix -pPASSWORD zabbix -e "CALL create_zabbix_partitions();"

Procedures
https://github.com/xsbr/zabbixzone/blob/master/zabbix-mysql-autopartitioning.sql

/**************************************************************
MySQL Auto Partitioning Procedure for Zabbix 1.8
http://zabbixzone.com/zabbix/partitioning-tables/

Author: Ricardo Santos (rsantos at gmail.com)
Version: 20110518
**************************************************************/
DELIMITER //
DROP PROCEDURE IF EXISTS `zabbix`.`create_zabbix_partitions` //
CREATE PROCEDURE `zabbix`.`create_zabbix_partitions` ()
BEGIN
CALL zabbix.create_next_partitions("zabbix","history");
CALL zabbix.create_next_partitions("zabbix","history_log");
CALL zabbix.create_next_partitions("zabbix","history_str");
CALL zabbix.create_next_partitions("zabbix","history_text");
CALL zabbix.create_next_partitions("zabbix","history_uint");
CALL zabbix.drop_old_partitions("zabbix","history");
CALL zabbix.drop_old_partitions("zabbix","history_log");
CALL zabbix.drop_old_partitions("zabbix","history_str");
CALL zabbix.drop_old_partitions("zabbix","history_text");
CALL zabbix.drop_old_partitions("zabbix","history_uint");
END //
DROP PROCEDURE IF EXISTS `zabbix`.`create_next_partitions` //
CREATE PROCEDURE `zabbix`.`create_next_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE NEXTCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @totaldays = 7;
SET @i = 1;
createloop: LOOP
SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i DAY);
SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m%d' );
SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 DAY),'%Y-%m-%d 00:00:00'));
CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
SET @i=@i+1;
IF @i &gt; @totaldays THEN
LEAVE createloop;
END IF;
END LOOP;
END //
DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_partitions` //
CREATE PROCEDURE `zabbix`.`drop_old_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE OLDCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @mindays = 3;
SET @maxdays = @mindays+4;
SET @i = @maxdays;
droploop: LOOP
SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i DAY);
SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m%d' );
CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
SET @i=@i-1;
IF @i &lt;= @mindays THEN
LEAVE droploop;
END IF;
END LOOP;
END //
DROP PROCEDURE IF EXISTS `zabbix`.`create_partition` //
CREATE PROCEDURE `zabbix`.`create_partition` (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)
BEGIN
DECLARE RETROWS int;
SELECT COUNT(1) INTO RETROWS
FROM `information_schema`.`partitions`
WHERE `table_schema` = SCHEMANAME AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;

IF RETROWS = 0 THEN
SELECT CONCAT( "create_partition(", 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 //
DROP PROCEDURE IF EXISTS `zabbix`.`drop_partition` //
CREATE PROCEDURE `zabbix`.`drop_partition` (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64))
BEGIN
DECLARE RETROWS int;
SELECT COUNT(1) INTO RETROWS
FROM `information_schema`.`partitions`
WHERE `table_schema` = SCHEMANAME AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;

IF RETROWS = 1 THEN
SELECT CONCAT( "drop_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg;
SET @sql = CONCAT( 'ALTER TABLE `', SCHEMANAME, '`.`', TABLENAME, '`',
' DROP PARTITION ', PARTITIONNAME, ';' );
PREPARE STMT FROM @sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;
END //
DELIMITER ;

/**************************************************************

MySQL Auto Partitioning Procedure for Zabbix 1.8

http://zabbixzone.com/zabbix/partitioning-tables/

Author: Ricardo Santos (rsantos at gmail.com)

Version: 20110518

**************************************************************/

DELIMITER //

DROP PROCEDURE IF EXISTS `zabbix`.`create_zabbix_partitions` //

CREATE PROCEDURE `zabbix`.`create_zabbix_partitions` ()

BEGIN

CALL zabbix.create_next_partitions("zabbix","history");

CALL zabbix.create_next_partitions("zabbix","history_log");

CALL zabbix.create_next_partitions("zabbix","history_str");

CALL zabbix.create_next_partitions("zabbix","history_text");

CALL zabbix.create_next_partitions("zabbix","history_uint");

CALL zabbix.drop_old_partitions("zabbix","history");

CALL zabbix.drop_old_partitions("zabbix","history_log");

CALL zabbix.drop_old_partitions("zabbix","history_str");

CALL zabbix.drop_old_partitions("zabbix","history_text");

CALL zabbix.drop_old_partitions("zabbix","history_uint");

END //

DROP PROCEDURE IF EXISTS `zabbix`.`create_next_partitions` //

CREATE PROCEDURE `zabbix`.`create_next_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))

BEGIN

DECLARE NEXTCLOCK timestamp;

DECLARE PARTITIONNAME varchar(16);

DECLARE CLOCK int;

SET @totaldays = 7;

SET @i = 1;

createloop: LOOP

SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i DAY);

SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m%d' );

SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 DAY),'%Y-%m-%d 00:00:00'));

CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );

SET @i=@i+1;

IF @i > @totaldays THEN

LEAVE createloop;

END IF;

END LOOP;

END //

DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_partitions` //

CREATE PROCEDURE `zabbix`.`drop_old_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))

BEGIN

DECLARE OLDCLOCK timestamp;

DECLARE PARTITIONNAME varchar(16);

DECLARE CLOCK int;

SET @mindays = 3;

SET @maxdays = @mindays+4;

SET @i = @maxdays;

droploop: LOOP

SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i DAY);

SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m%d' );

CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );

SET @i=@i-1;

IF @i <= @mindays THEN

LEAVE droploop;

END IF;

END LOOP;

END //

DROP PROCEDURE IF EXISTS `zabbix`.`create_partition` //

CREATE PROCEDURE `zabbix`.`create_partition` (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)

BEGIN

DECLARE RETROWS int;

SELECT COUNT(1) INTO RETROWS

FROM `information_schema`.`partitions`

WHERE `table_schema` = SCHEMANAME AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;

IF RETROWS = 0 THEN

SELECT CONCAT( "create_partition(", 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 //

DROP PROCEDURE IF EXISTS `zabbix`.`drop_partition` //

CREATE PROCEDURE `zabbix`.`drop_partition` (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64))

BEGIN

DECLARE RETROWS int;

SELECT COUNT(1) INTO RETROWS

FROM `information_schema`.`partitions`

WHERE `table_schema` = SCHEMANAME AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;

IF RETROWS = 1 THEN

SELECT CONCAT( "drop_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg;

SET @sql = CONCAT( 'ALTER TABLE `', SCHEMANAME, '`.`', TABLENAME, '`',

' DROP PARTITION ', PARTITIONNAME, ';' );

PREPARE STMT FROM @sql;

EXECUTE STMT;

DEALLOCATE PREPARE STMT;

END IF;

END //

DELIMITER ;

44 Responses to “ “Partitioning Tables on Zabbix 1.8”

Laurent Letourmy says:

May 19, 2011 at 6:18 am

Hi,

Very interesting post. We would like to test this solution on our Zabbix installation.

What kind of performance improvement did you noticed on large installation ?

Laurent.

Reply
- Ricardo Santos says:
  
  May 19, 2011 at 8:28 am
  
  Hi Laurent,
  
  It was tested on this environment.
  
  I could only reach this stage with partitioning.
  
  Reply
Twirrim says:

May 19, 2011 at 3:11 pm

The function you’ve created runs partitioning against the history table, but at no stage in the blog did you actually partition the history table, so it fails 🙂

mysql> CALL create_zabbix_partitions();
+——————————————————-+
| msg |
+——————————————————-+
| create_partition(zabbix,history,p20110520,1305972000) |
+——————————————————-+
1 row in set (0.32 sec)

ERROR 1505 (HY000): Partition management on a not partitioned table is not possible

Reply
- Ricardo Santos says:
  
  May 19, 2011 at 9:41 pm
  
  Twirrim,
  
  You’re right. I forgot to cite that first partitioning must be do manually on history tables (see Step 3).
  
  Thanks for your feedback.
  
  Reply
Daniel Santos says:

May 19, 2011 at 3:51 pm

Hi Ricardo, would you have the same procedure above but for environments using PostgreSQL?? I’m using PostgreSQL 9 and I would like to implement partitioning on our PgSQL database the same way you did for MySQL. Would you have some documentation talking about that?!

Tks,

Daniel Santos

Reply
- Ricardo Santos says:
  
  May 19, 2011 at 9:49 pm
  
  Daniel,
  
  I haven’t any skills on PgSQL to help you, but looking at pg documentation, seems to be more complicated.
  
  Keep watching this post on Zabbix Forum:
  http://www.zabbix.com/forum/showthread.php?t=14208
  
  Reply
StefanS says:

May 25, 2011 at 8:54 am

Ricardo,
thanks for this good article.
I looked for that for a long time.
I would have two questions still in addition.

1) Do i have to provide a new zabbix DB (dump, retsore), or do work this with existing zabbix DB?

2) Your Auto Partitioning Procedure is only for history table, which i make with trends tbales etc.?

Stefan

Reply
- Ricardo Santos says:
  
  May 25, 2011 at 10:29 am
  
  1) Do i have to provide a new zabbix DB (dump, retsore), or do work this with existing zabbix DB?
  
  I suggest to do it on a clean DB, but you can do a simple process on existents tables:
  – create a new empty table (eg: new_history_uint)
  – create the new indexes and do the partitioning (on empty table)
  – copy the rows (eg: INSERT INTO new_history_uint SELECT * FROM history_uint)
  – swap the tables (eg: RENAME TABLE history_uint TO old_history_uint, new_history_uint TO history_uint)
  
  2) Your Auto Partitioning Procedure is only for history table, which i make with trends tables etc.?
  
  In the future, I’ll create a procedure to create monthly partitioning.
  Now, you can create manually the partitioning for a long time (12 or 24 months) described on step 2.
  
  Reply
  - StefanS says:
    
    May 25, 2011 at 12:26 pm
    
    Ricardo,
    thanks for your fast answer 🙂
    i think it is not a Problem to run your “Partitioning Procedure”on mysql event (not cron) every day (midnight).
    About “copy rows” with 40GB history Table =8-(
    maby i make a complete new zabbix DB.
    
    thanks again
    
    Reply
sersad says:

June 2, 2011 at 9:17 am

How to create partition on mysql 5.1?
It’s posible?

Reply
- Ricardo Santos says:
  
  October 19, 2011 at 7:59 am
  
  Of course. Today I use 5.5, but I started at 5.1
  
  Reply
Ben Chavet says:

July 11, 2011 at 10:30 pm

I would like to suggest adding a “catchall” partition at the end, so that if a new partition does not exist (script didn’t run, forgot to manually create it, whatever), you still have functionallity

ALTER TABLE history ADD PARTITION (PARTITION overflow VALUES LESS THAN MAXVALUE);

Then, to add a new partition between the last “real” partition and this overflow

ALTER TABLE history REORGANIZE PARTITION overflow INTO (PARTITION p201201 VALUES LESS THAN (UNIX_TIMESTAMP(“2012-02-01 00:00:00”)), PARTITION overflow VALUES LESS THAN MAXVALUE);

Reply
- DeDA says:
  
  October 18, 2011 at 11:46 am
  
  It’s better to create partitions for one or two days/months in future, I Think.
  
  Reply
  - Ricardo Santos says:
    
    October 19, 2011 at 8:02 am
    
    The PROCEDURE from my script http://pastebin.com/ijyKkxLh creates partitions for the next 7 days.
    
    For monthly partitions, I suggest to create at least until December from the next year.
    
    Reply
  - Ben Chavet says:
    
    November 17, 2011 at 1:18 pm
    
    I agree completely. My suggestion was simply to add a catchall in case something happened where the new partitions did not get created for some reason.
    
    Reply
Timp says:

August 30, 2011 at 5:03 am

Thanks for your solution!

And can you say, in future, is it possible to update this db to new zabbix release (2.0 for xample)?

Reply
- Ricardo Santos says:
  
  October 19, 2011 at 8:04 am
  
  Technically it will be possible, but we need to wait until 2.0 release to define exactly the procedure.
  
  Reply
Angela says:

September 20, 2011 at 12:53 pm

How do you recommended dropping partitions that are no longer needed? I expect to start dropping partitions after 6 months. Is there an order in which they should be dropped – any concerns about foreign key dependencies on other tables, etc?

Angela

Reply
- Ricardo Santos says:
  
  October 19, 2011 at 8:10 am
  
  It depends on your business, but 12 months is a good number for almost every business.
  
  There’s no order to drop and foreign keys are not used on 1.8 version.
  
  Reply
Luke says:

January 5, 2012 at 4:16 pm

Bah! This got me today, forgot to make my 2012 monthly partitions. Zabbix didn’t like that.

Reply
Jonathan Bayer says:

February 14, 2012 at 6:04 pm

I’ve written a script which will automatically set up a system to do this. It creates the partitions, edits the conf file and creates a cronjob. Once set up, you won’t have to remember to create any new partitions.

It will be available at linuxnotes.us in a few days, once I’m done testing it.

JBB

Reply
Gabriele says:

February 28, 2012 at 9:47 am

I wrote, using ricardo’s script as reference, the three missing procedures to automatically handle monthly partitions:

DELIMITER //
DROP PROCEDURE IF EXISTS zabbix.create_zabbix_monthly_partitions //
CREATE PROCEDURE zabbix.create_zabbix_monthly_partitions ()
BEGIN
CALL zabbix.create_next_monthly_partitions(“zabbix”,”acknowledges”);
CALL zabbix.create_next_monthly_partitions(“zabbix”,”alerts”);
CALL zabbix.create_next_monthly_partitions(“zabbix”,”auditlog”);
CALL zabbix.create_next_monthly_partitions(“zabbix”,”events”);
CALL zabbix.create_next_monthly_partitions(“zabbix”,”service_alarms”);
CALL zabbix.create_next_monthly_partitions(“zabbix”,”trends”);
CALL zabbix.create_next_monthly_partitions(“zabbix”,”trends_uint”);
CALL zabbix.drop_old_monthly_partitions(“zabbix”,”acknowledges”);
CALL zabbix.drop_old_monthly_partitions(“zabbix”,”alerts”);
CALL zabbix.drop_old_monthly_partitions(“zabbix”,”auditlog”);
CALL zabbix.drop_old_monthly_partitions(“zabbix”,”events”);
CALL zabbix.drop_old_monthly_partitions(“zabbix”,”service_alarms”);
CALL zabbix.drop_old_monthly_partitions(“zabbix”,”trends”);
CALL zabbix.drop_old_monthly_partitions(“zabbix”,”trends_uint”);
END //
DROP PROCEDURE IF EXISTS zabbix.create_next_monthly_partitions //
CREATE PROCEDURE zabbix.create_next_monthly_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE NEXTCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @totalmonths = 3;
SET @i = 1;
createloop: LOOP
SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i MONTH);
SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, ‘p%Y%m’ );
SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 MONTH),’%Y-%m-01 00:00:00′));
CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
SET @i=@i+1;
IF @i > @totalmonths THEN
LEAVE createloop;
END IF;
END LOOP;
END //
DROP PROCEDURE IF EXISTS zabbix.drop_old_monthly_partitions //
CREATE PROCEDURE zabbix.drop_old_monthly_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE OLDCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @minmonths = 12;
SET @maxmonths = @minmonths+3;
SET @i = @maxmonths;
droploop: LOOP
SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i MONTH);
SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, ‘p%Y%m’ );
CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
SET @i=@i-1;
IF @i /opt/zabbix/monthly_partition_management_results.log 2>&1

Reply
Gabriele says:

February 28, 2012 at 10:01 am

it seems my message got cut, so I pasted the MySQL script to pastebin.

I wrote, using ricardo’s script as reference, the three missing procedures to automatically handle monthly partitions:

you’ll find it here: http://pastebin.com/nY8WG9Fr

I assume you already have the “delete_partition()” and “create_partition()” procedures defined, they are included inside Ricardo’s first script.

default for this script is to keep monthly partition for 12 months (I set my item to keep 365 days of trends), you can easily change it by modifying the @minmonths variable to suite your needs.

of course you’ll need to add a monthly crontab entry like this:

30 01 1 * * mysql -u zabbix -pzabbix zabbix -e “CALL create_zabbix_monthly_partitions();” >/opt/zabbix/monthly_partition_management_results.log 2>&1

Hope this helps

Reply
Jonathan Bayer says:

March 5, 2012 at 6:08 pm

I just published the script, here is the link:

http://linuxnotes.us/archives/503

Thanks for a great set of instructions and explanations

Reply
- Hannu Ylitalo says:
  
  May 30, 2012 at 9:12 am
  
  This script no longer works with 2.0 due to added foreign key constraint checks. MySQL doesn’t support foreign keys with partitioned tables so they had to be removed.
  
  I did some modifications (mainly remove the constraint checks) and at least the database partitioning could be completed. We are now going to try it in our new setup.
  
  The script has been only run against a blank zabbix database so I’d be cautious on running it with production data.
  
  Edited script: http://pastebin.com/Nw4xqinq
  Diff: http://pastebin.com/uzxV4dKy
  
  Reply
  - Gabriele says:
    
    June 6, 2012 at 6:38 am
    
    just a question, why you didn’t include trends and trends_uint into the monthly partitioned tables?
    
    Reply
- Gabriele says:
  
  June 6, 2012 at 12:22 pm
  
  there are two errors on these scripts:
  
  the first one is that the creation of the first partitions (not the mysql procedure but the first partitions of each table created by the script) is wrong, for example, it creates the p201206 monthly partition but it defines that the content of that partition should be all the values with clock less than 2012-06-01, it should be 2012-07-01 instead (June partition should include all the values before the first day of July). Same applies for the daily partitions.
  I fixed the script modifying this for the monthly partition:
  
  ms=date +"%Y-%m-01" -d "$m/01/$y +1 month"
  pname=”p${y}${m}”
  echo -n “PARTITION $pname VALUES LESS THAN (UNIX_TIMESTAMP(\”$ms 00:00:00\”))” >>$SQL
  
  and this for the daily partition:
  
  ds=date +"%Y-%m-%d" -d "$d day +1 day"
  
  the second error is on the create_next_monthly_partitions procedure, it references a “@i” that is not present anymore on that procedure (probably a cut’n’paste error), so either replace it with a “1” or make a more complete procedure that will create more than one monthly partition in advance:
  
  DROP PROCEDURE IF EXISTS zabbix.create_next_monthly_partitions; //
  CREATE PROCEDURE zabbix.create_next_monthly_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64))
  BEGIN
  DECLARE NEXTCLOCK timestamp;
  DECLARE PARTITIONNAME varchar(16);
  DECLARE CLOCK int;
  SET @totalmonths = 3;
  SET @i = 1;
  createloop: LOOP
  SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i MONTH);
  SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, ‘p%Y%m’ );
  SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 MONTH),’%Y-%m-01 00:00:00′));
  CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
  SET @i=@i+1;
  IF @i > @totalmonths THEN
  LEAVE createloop;
  END IF;
  END LOOP;
  END //
  
  there’s actually a third issue, which is the missing “trends” and “trends_uint” in the list of montlhy partitionable tables, I think they should be included unless you want to keep trends “forever”.
  
  Reply
  - Jonathan Bayer says:
    
    June 22, 2012 at 12:16 pm
    
    The errors you are referring to, are they on the original script from linuxnotes.us, or on the modified scripts?
    
    JBB
    
    Reply
    - Gabriele says:
      
      July 2, 2012 at 10:13 am
      
      the errors are on both the original linuxnotes.us script and on the modified one
      
      Reply
krishna says:

March 15, 2012 at 4:34 pm

May i create partition tables on existing production zabbix database

Reply
keshengjie says:

May 11, 2012 at 1:01 pm

@Ricardo, I found this information very helpful.

@Jonathan, the script was invaluable.

Has anyone had memory issues after partitioning? MySQL 5.5.22 (on CentOS 5.8) keeps running out of memory, so I have to restart it. This only started after partitioning the large tables.

I upgraded to MySQL 5.5.24, last night, and am waiting to see if it improves.

I also have a slave server I just switched to Percona 5.5.20 to see if it makes a difference. (The problem occurs on both, just takes longer to happen on the slave.)

Reply
keshengjie says:

May 17, 2012 at 12:14 pm

I figured out the issue. I cloned my database VM while setting up partitioning. When you clone a VM in VMWare, it sets the memory usage reservation to the amount of memory in the original. I had subsequently increased the memory and increased my settings in mysql.conf. It appeared to be a memory leak because VMware wouldn’t allow me to use more memory than the original.

Reply
mcmyst says:

May 22, 2012 at 5:33 pm

Does this script works for the new version 2.0 ? As I know, the SQL schema has changed a bit.

Reply
keshengjie says:

May 31, 2012 at 1:48 pm

2.0 seems to be a problem. I already have my 1.8 install partitioned and when I run the MySQL patch.sql script to upgrade, it has a problem with foreign keys.

The fourth line in patch.sql is “ALTER TABLE acknowledges ADD CONSTRAINT c_acknowledges_1 FOREIGN KEY (userid) REFERENCES users (userid) ON DELETE CASCADE”

This throws:
ERROR 1506 (HY000) at line 6: Foreign key clause is not yet supported in conjunction with partitioning

Reply
T0MA says:

June 13, 2012 at 7:50 pm

I got the upgrade script working for 2.0 by reverting the monthly partition tables to no partitioning (dropping them and recreating them)!

Reply
Jonathan Bayer says:

June 22, 2012 at 12:08 pm

Zabbix isn’t broken. You are trying to upgrade a modified database, which Zabbix doesn’t support out of the box.

The message says it all. The Zabbix team implement foreign keys, which MySql doesn’t support in partitioning.

Reply
Doug Goldenberg says:

July 2, 2012 at 3:55 pm

Does anyone know if there a way to back out of the partitioning to apply the Zabbix 2.0 SQL patch?

Reply
jcho says:

July 11, 2012 at 9:58 am

Someone found a solution to this problem with 2.0.x version ?
Does PostGreSQL ou Oracle support foreign keys in partitionning ?
Thanks

Reply
Partitioning Zabbix tables in MySQL | mypoorbraindump says:

July 11, 2012 at 8:42 pm

[…] and immediately implemented a slightly altered version of the plan he laid out. Check out his post, it’s […]

Reply
hamid says:

August 9, 2012 at 11:18 am

what happens to deleted items which deleted by admin via GUI in the case of disabling housekeeper?
they are not deleted from history and trend tables and will remain in housekeeper table and they look like garbage

Reply
Daniel says:

December 4, 2012 at 8:12 am

We have some Performance issue, but we are running Zabbix 2.0

I would like to know if this script is compatible to this version, does anyone have some experiences?

Reply
Luis says:

December 5, 2012 at 4:50 pm

hi, that’s great….i’d to know if I can use it in Zabbix 2.0.2….
sorry for my bad english and thanks for your answer..

Reply
- Daniel says:
  
  December 14, 2012 at 12:12 pm
  
  It seems that the Daily job will run perfectly, but since Zabbix 2.0, there are some key konstraints on the following tables:
  
  acknowledges
  alerts
  auditlog
  events
  service_alarms
  
  So you can only use it for the other tables.
  
  But on my expereince, this is not a big deal, as these tables are not very huge, but you have to keep in mind to clean them with a normal query…
  
  Reply
Andy says:

December 12, 2012 at 9:53 am

Any news on Zabbix 2.0 + ????

Reply
- Daniel says:
  
  December 14, 2012 at 12:15 pm
  
  Check my reply to Luis 😉
  
  Reply

About Me

Ricardo Santos has over 10 years of experience as Linux Systems Administrator (RedHat and variants). Since 2009 uses Zabbix for Monitoring and Capacity. Recently became a Certified Zabbix Specialist by Zabbix SIA.

Partitioning Tables on Zabbix 1.8

44 Responses to “ “Partitioning Tables on Zabbix 1.8”

Leave a Reply to Ben Chavet

About Me

Categories

Related Links

Communities

Social Networks