Using Partitioning on Zabbix DB tables to improve performance

By jbayer - Last updated: Monday, March 5, 2012 - Save & Share - 17 Comments

This script will partition your zabbix database to improve the efficiency. It will also create stored procedures to do the necessary housekeeping, and create a cronjob to do this on a daily basis. This script was inspired by the following:

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

While the basic SQL is from the above page, this script both creates the necessary SQL for the desired tables, and can create new partitions as the time goes on assuming that the cronjob has been properly entered.

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

 

  partitiontables.zip (4.0 KiB, 1,097 hits)

 

Usage:

./partitiontables.sh [-h host][-u user][-p password][-d min_days][-y startyear]
 -h host database host
 -u user db user
 -p password user password
 -d min_days Minimum number of days of history to keep
 -m min_months Minimum number of months to keep trends
 -y startyear First year to set up with partitions

After running this script, don’t forget to disable housekeeping if you didn’t have the script disable it, and add the following cronjob:

Change the file /etc/zabbix/zabbix_server.conf as follows to disable the housekeeping:

### Option: DisableHousekeeping
 # If set to 1, disables housekeeping.
 #
 # Mandatory: no
 # Range: 0-1
 ################### Uncomment and change the following line to 1 in
 ################### Then restart the zabbix server
 DisableHousekeeping=1

Cron job

0 0 * * * /etc/zabbix/cron.d/housekeeping.sh

 

Posted in Database, Zabbix • • Top Of Page
6,343 views

17 Responses to “Using Partitioning on Zabbix DB tables to improve performance”

Comment from keshengjie
Time May 4, 2012 at 3:01 pm

Thanks for this script. It was very useful!

Comment from Alessandro
Time June 21, 2012 at 8:58 am

Hi, has this been updated to work with zabbix 2.0?

Comment from jbayer
Time June 21, 2012 at 3:05 pm

Correction: No, it is not supported, there are issues with foreign keys which MySql doesn’t support with partitioning

Comment from Craig
Time August 2, 2012 at 11:18 am

love the script however, I may have messed something up…hope you can help. :)

ran the above script and everything was working for a few weeks (all of July) then on Aug 1 the email that was sent only contained the date….no info on the partitions it created/dropped….and me not being a dba have no idea why or even how to check it. :) googling it now. :)

do you have any ideas or can you point me in the right direction? email me or post here if you can help out or need any additional info.

thanks

Comment from jbayer
Time August 3, 2012 at 3:39 pm

Sounds like it didn’t do Aug.

When did you download the script?

Comment from Craig
Time August 7, 2012 at 12:56 pm

i believe i downloaded it on july 23

Comment from Craig
Time August 7, 2012 at 1:00 pm

Actually, it was probably back in March or April when I downloaded it….I ran it on July 23. :)

Comment from Craig
Time August 8, 2012 at 8:57 am

quick update…turns out i may have made a typo in the main script…oops. and doh!

Comment from Paulo Raponi
Time October 16, 2012 at 12:07 pm

Hi,

Have some idea if this will be migrate to Zabbix 2?

Congrats for your and Ricardo work!

Comment from jbayer
Time October 16, 2012 at 7:17 pm

Thank you.

Unfortunately, the Zabbix 2.0 database is using relational integrity, which doesn’t work with partitions. So the choice is to remove the relations, or not use partitioning.

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

Comment from Cícero
Time August 25, 2013 at 10:00 pm

Hi, this script resolve!

https://github.com/cdand/zabbixdbpartitioning

Comment from Cícero
Time August 25, 2013 at 10:01 pm

Hi, partitioning for zabbix 2.x

https://github.com/cdand/zabbixdbpartitioning

Comment from jbayer
Time September 18, 2013 at 6:48 am

Thank you. I took a quick look,and it looks nice.

Comment from wilfred
Time December 18, 2013 at 1:27 am

can anyone send the updated script forzabbix 2.2.1 Database partitioning. i am using postgreSQL instead of mysql.

Thanks for your response

Comment from jbayer
Time December 19, 2013 at 4:59 pm

There is no script for Zabbix 2.2, or for Postgresql. I know that someone did modify this for 2.0, but don’t know where it is.

Comment from Ricardo Lopes
Time March 24, 2014 at 9:59 am

Anyone could mail me this script to 2.2.2? Or is the 2.x working?

I could see some of tables are not partitioned, like events, etc

Comment from jbayer
Time May 3, 2014 at 9:55 am

Sorry, it doesn’t work for 2.0 or later.

Write a comment

asd