Partitioning Zabbix tables in MySQL

My Zabbix server was in a world of hurt and Ricardo over at saved the day.  At the time, I was monitoring about 200 devices with a 4GB, 4CPU virtual machine.  This poor box was running everything, Zabbix server, Zabbix UI, and MySQL.  Needless to say, it was struggling. It needed some performance tuning.

I came across Ricardo’s post and immediately implemented a slightly altered version of the plan he laid out.  Check out his post, it’s fantastic.

The basic premise is that there are certain tables in Zabbix that collect a ton of data.  Not only do the tables get big, they slow down the UI due to long query times, etc.  If you utilize MySQL’s ability to partition these tables you not only make your queries quicker, you can easily keep your disk space under control too.  I’m sure there’s more benefits, but those 2 were enough to sell me on it.

MySQL has an optimization trick known as pruning where it will only look in the partitions that have the expected data in them.  This saves a lot of disk IO in the process.

When you have partitioned tables in MySQL you can drop old partitions and only the data from those dropped partitions is deleted.  In the case of the history tables in Zabbix, I have item history set to 7 days.  I have created weekly partitions and delete any partitions older than 2 weeks.

My environment monitors over 400 servers now, on the same modest VM, and my weekly history partitions have about 75m rows each.  Each week I drop a single partition and get back almost 5G of disk space.  Not only does this save disk space, but Zabbix no longer has to look through, potentially, 75m extra rows to get what it needs and the indexes on the tables are a lot smaller too.

Here’s a good example of the pruning by MySQL.

mysql> explain partitions select value from history_uint where itemid=18435 and clock<=1340923260 order by itemid,clock desc limit 4;
| id | select_type | table        | partitions     | type  | possible_keys  | key            | key_len | ref  | rows  | Extra       |
|  1 | SIMPLE      | history_uint | p201207wk01    | range | history_uint_1 | history_uint_1 | 12      | NULL | 21525 | Using where |

This explain plan shows that MySQL only looked in 1 partition, only about 75m rows instead of a possible 225m+.  The index knocks that number down to about 21525. That’s a win as far as I’m concerned.

Now, I’m going to stop babbling and you should go check out Ricardo’s post at Zabbix Zone.

7 Comments on “Partitioning Zabbix tables in MySQL”

  1. David says:

    Did you forego the partitioning when you upgraded to Zabbix 2.0, due to the foreign key issue?

    • David, haven’t partitioned because I’m still in staging on 2.0, but my history and history_uint tables don’t have foreign keys. Am I missing something?

      • David says:

        Oh… Did you only partition your history and trends tables? If so, is it working well? This may be the solution I was looking for.

        I ran into the issue when upgrading to 2.0 because I had followed Ricard’s post and partitioned all the data tables. (For example, the acknowledges table has a foreign key.)

      • I have partitions on history, history_uint, trends, trends_uint. I should probably partition the events table but it hasn’t given me any trouble yet so I’ll hold off. History and history_uint were the biggest problem tables for me. Those 2 tables grow at about 2.5G each per week. I’ve set all items to only keep 7 days of history but 365 days of trends, so I delete history partitions older than 1 week. This keeps my disk space pretty constant and also cuts down on table scans. I didn’t partition any more than that because the tables were so small, I just run an occasional optimize and that keeps things running smooth.

      • David says:

        Also, did you disable housekeeping? If so, how are you cleaning up the other non-partitioned data tables?

      • I have disabled housekeeping and I don’t do anything but drop old partitions from history and history_uint. I also have a monthly history_text partitioned and old partitions will get dropped if space is ever an issue.

  2. […] old data to keep your table sizes manageable.  If you haven’t already seen my post about partitioning your Zabbix MySQL tables, check it […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s