Zabbix: Partitioning MySQL / MariaDB database Tables
In this tutorial we will learn, step by step, how to partition Zabbix database (history and events tables) on MySQL or MariaDB.
Zabbix gathers data from hosts and stores them in database using history and trends tables. Zabbix history keeps raw data (each value that Zabbix has collected) and trends stores consolidated hourly data that is averaged as min,avg, and max.
Zabbix’s housekeeping process is responsible for deleting old trend and history data. Removing old data from the database using SQL delete query can negatively impact database performance. Many of us have received that annoying alarm “Zabbix housekeeper processes more than 75% busy” because of that.
That problem can be easily solved with the database partitioning. Partitioning creates tables for each hour or day and drops them when they are not needed anymore. SQL DROP is way more efficient than the DELETE statement.
You can use this tutorial for any Zabbix version after 3.0 (3.2, 3.4, 4.0, 4.2, 4.4 etc).
Before we continue please make a backup of the Zabbix database, but if the installation is new than there is no need for backup.
Contents
Step 1: Download SQL script for partitioning
Step 2: Create partition procedures with SQL script
Step 3: Create crontab job for partitioning
Step 4: Configure Housekeeping on Zabbix frontend
Step 1: Download SQL script for partitioning
Download and uncompress SQL script “zbx_db_partitiong.sql” on your database server:
wget https://bestmonitoringtools.com/wp-content/uploads/2019/10/zbx_db_partitiong.tar.gz tar -zxvf zbx_db_partitiong.tar.gz
Script “zbx_db_partitiong.sql” is configured to keep 7 days of history data and 365 days of trend data – move to step 2 if those settings are acceptable to you.
However, if you wont to change days for trends or history then open file “zbx_db_partitiong.sql“, change settings as shown in the picture below and save file.
