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
See more information about history tables.
Monthly – other huge tables
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:
1 2 3 4 5 6 7 8 9 |
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:
1 2 3 4 5 6 7 8 9 10 |
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:
1 2 3 4 5 6 7 8 9 10 |
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:
1 2 3 |
ALTER TABLE `history_uint` ADD PARTITION ( PARTITION p20110523 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-24 00:00:00")) ); |
Dropping partitions (Housekeeping):
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:
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
/************************************************************** 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 ; |
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.
Hi Laurent,
It was tested on this environment.
I could only reach this stage with partitioning.
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
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.
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
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
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
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.
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
How to create partition on mysql 5.1?
It’s posible?
Of course. Today I use 5.5, but I started at 5.1
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);It’s better to create partitions for one or two days/months in future, I Think.
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.
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.
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)?
Technically it will be possible, but we need to wait until 2.0 release to define exactly the procedure.
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
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.
Bah! This got me today, forgot to make my 2012 monthly partitions. Zabbix didn’t like that.
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
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
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
I just published the script, here is the link:
http://linuxnotes.us/archives/503
Thanks for a great set of instructions and explanations
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
just a question, why you didn’t include trends and trends_uint into the monthly partitioned tables?
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”.
The errors you are referring to, are they on the original script from linuxnotes.us, or on the modified scripts?
JBB
the errors are on both the original linuxnotes.us script and on the modified one
May i create partition tables on existing production zabbix database
@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.)
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.
Does this script works for the new version 2.0 ? As I know, the SQL schema has changed a bit.
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
I got the upgrade script working for 2.0 by reverting the monthly partition tables to no partitioning (dropping them and recreating them)!
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.
Does anyone know if there a way to back out of the partitioning to apply the Zabbix 2.0 SQL patch?
Someone found a solution to this problem with 2.0.x version ?
Does PostGreSQL ou Oracle support foreign keys in partitionning ?
Thanks
[…] and immediately implemented a slightly altered version of the plan he laid out. Check out his post, it’s […]
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
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?
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..
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…
Any news on Zabbix 2.0 + ????
Check my reply to Luis 😉