Partitioning Tables on Zabbix 1.8

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:

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:

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:

Maintaining the Partitions Manually

Adding new partitions:

Dropping partitions (Housekeeping):

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:

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



44 Responses to “ “Partitioning Tables on Zabbix 1.8”

  1. 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.

  2. Twirrim says:

    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

  3. 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

  4. StefanS says:

    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.

      • StefanS says:

        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

  5. sersad says:

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

  6. Ben Chavet says:

    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);

  7. Timp says:

    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)?

  8. Angela says:

    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

  9. Luke says:

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

  10. 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

  11. Gabriele says:

    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

  12. Gabriele says:

    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

  13. I just published the script, here is the link:

    http://linuxnotes.us/archives/503

    Thanks for a great set of instructions and explanations

    • Hannu Ylitalo says:

      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

      • Gabriele says:

        just a question, why you didn’t include trends and trends_uint into the monthly partitioned tables?

    • Gabriele says:

      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”.

  14. krishna says:

    May i create partition tables on existing production zabbix database

  15. keshengjie says:

    @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.)

  16. keshengjie says:

    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.

  17. mcmyst says:

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

  18. keshengjie says:

    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

  19. T0MA says:

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

  20. 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.

  21. Doug Goldenberg says:

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

  22. jcho says:

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

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

  24. hamid says:

    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

  25. Daniel says:

    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?

  26. Luis says:

    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..

    • Daniel says:

      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…

  27. Andy says:

    Any news on Zabbix 2.0 + ????

Leave a Reply

*