In this tutorial we will learn, step by step, how to partition Zabbix database (history and trends tables) on MySQL or MariaDB using partitioning script.
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. Even though database optimization can be helpful (read more about it in this post), at some point, you will hit a brick wall regarding performance, and you will need to get your hands dirty with partitioning.
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, 5.0, 5.2, 5.4, 6.0 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.
- Step 1: Download SQL script for partitioning
- Step 2: Create partitioning procedures with the SQL script
- Step 3: Run partitioning procedures automatically
- Step 4: Configure Housekeeping on Zabbix frontend
- Step 5: Change partitioning settings (days for history and trends)
- Step 6: Solution for some common errors
- Step 7: Info about Zabbix partitioning script
Step 1: Download SQL script for partitioning
Download and uncompress SQL script “
zbx_db_partitiong.sql” on your database server (use wget or curl tool):
curl -O https://bestmonitoringtools.com/dl/zbx_db_partitiong.tar.gz tar -zxvf zbx_db_partitiong.tar.gz
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 want to change days for trends or history then open file “zbx_db_partitiong.sql“, change settings as shown in the picture below, and save the file.
Step 2: Create partitioning procedures with the SQL script
Syntax for running script is “mysql -u ‘<db_username>’ -p'<db_password>’ <zb_database_name> < zbx_db_partitiong.sql“.
Now, run it with your Zabbix database name, username , and password to create partitioning procedures:
mysql -u 'zabbix' -p'zabbixDBpass' zabbix < zbx_db_partitiong.sql
Script will create MySQL partitioning procedures very quickly on the new Zabbix installation, but on large databases, this may last for hours.
Step 3: Run partitioning procedures automatically
We’ve created partitioning procedures, but they don’t do anything until we run them!
This step is the most important because the partitions must be deleted and created regularly (every day) using partitioning procedures!
Don’t worry, you don’t have to do that manually. We can use two tools for such tasks: MySQL event scheduler or Crontab – choose whatever you prefer.
Be careful when configuring MySQL event scheduler or Crontab. Zabbix will stop collecting data if you misconfigure them! You will notice that by the empty graphs and the error “[Z3005] query failed:  Table has no partition for value ..” in the Zabbix log file.
Option 1: Manage partitions automatically using MySQL event scheduler (recommended)
By default, the MySQL event scheduler is disabled. You need to enable it by setting “event_scheduler=ON” in the MySQL configuration file just after the “[mysqld]” line.
[mysqld] event_scheduler = ON
Don’t know where that file is located? If you used my tutorial for installing and optimizing Zabbix, then the MySQL configuration file (10_my_tweaks.cnf) should be located at “/etc/mysql/mariadb.conf.d/” or “/etc/my.cnf.d/“, otherwise try to search for it with the command:
sudo grep --include=*.cnf -irl / -e "\[mysqld\]"
Once you have made the changes restart your MySQL server for the setting to take effect!
sudo systemctl restart mysql
Nice! MySQL event scheduler should be enabled, let’s check that with the command:
root@dbserver:~ $ mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "SHOW VARIABLES LIKE 'event_scheduler';" +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+
Now we can create an event that will run procedure “partition_maintenance_all” every 12 hours.
mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CREATE EVENT zbx_partitioning ON SCHEDULE EVERY 12 HOUR DO CALL partition_maintenance_all('zabbix');"
After 12 hours, check to see if the event has been executed successfully using the command below.
mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "SELECT * FROM INFORMATION_SCHEMA.events\G" EVENT_CATALOG: def ... CREATED: 2020-10-24 11:01:07 LAST_ALTERED: 2020-10-24 11:01:07 LAST_EXECUTED: 2020-10-24 11:43:07 ...
Option 2: Manage partitions automatically using Crontab
Crontab is a good alternative if you are unable to use the MySQL event scheduler. Open crontab file with the command “sudo crontab -e” and add a job for partitioning Zabbix MySQL database (every day at 03:30 AM) by adding this line anywhere in the file:
30 03 * * * /usr/bin/mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all('zabbix');" > /tmp/CronDBpartitiong.log 2>&1
Save and close file.
Cron will execute patitioning every day (drop old tables and create new ones) and log everything in file “
However, if you are impatient then run command immediately from the terminal:
root@dbserver:~ $ mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all('zabbix');" +-----------------------------------------------------------+ | msg | +-----------------------------------------------------------+ | partition_create(zabbix,history,p201910150000,1571180400) | +-----------------------------------------------------------+ +-----------------------------------------------------------+ ...etc.
and check the partitioning status afterward:
root@dbserver:~ $ mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "show create table history\G" Table: history Create Table: CREATE TABLE history ( itemid bigint(20) unsigned NOT NULL, clock int(11) NOT NULL DEFAULT '0', value double(16,4) NOT NULL DEFAULT '0.0000', ns int(11) NOT NULL DEFAULT '0', KEY history_1 (itemid,clock) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin /*!50100 PARTITION BY RANGE (clock) (PARTITION p201910140000 VALUES LESS THAN (1571094000) ENGINE = InnoDB, PARTITION p201910150000 VALUES LESS THAN (1571180400) ENGINE = InnoDB, PARTITION p201910160000 VALUES LESS THAN (1571266800) ENGINE = InnoDB) */
As you can see in the outputs, we have created 3 partitions for the history table.
Step 4: Configure Housekeeping on Zabbix frontend
Configure housekeeping on Zabbix frontend as shown in the picture below.
If the picture is not self-explanatory, here are the steps for configuring housekeeping on Zabbix frontend:
- navigate to “Housekeeping” section: “Administration” → “General” → “Housekeeping“;
- remove the checkmark from “Enable internal housekeeping” under History and Trends section;
- put a checkmark on “Override item trend period” under History and Trends section;
- define days for “data storage period” for trends and history under the History and Trends section (must be the same as the days configured in database partitioning – that should be 7 days for history and 365 days for trends if you haven’t changed the default settings in the script);
- click the “Update” button.
And you are done! Keep in mind that the partitioning will delete history and trend tables according to what you have configured in the partitioning procedure. For example, if you have configured to keep 7 days of history, partitioning will start deleting history on the 8th day. After that, it will delete one history table every day so that the database always has 7 days of the history data. The same goes for the trend data, if you configured to keep 365 days of the trend data, only after 365 days it will start deleting old trend tables.
You have successfully partitioned MySQL tables on the Zabbix database!
No need to change anything else as other steps are optional.
CONTINUE TO LEARN MORE:
How to change partitioning settings
Read more about partitioning procedures that are used in the script.
Step 5: Change partitioning settings (days for history and trends)
Sometimes it may happen that you initially set too many days for history and trends for the Zabbix database, so the disk space fills up too quickly. Or the opposite happens, you didn’t configure enough days for history or trends. What to do then?
You don’t need to run the script again, just create a new procedure that you will run instead of the old one.
a) Create a new partitioning procedure
Connect to the MySQL/MariaDB server:
mysql -u 'zabbix' -p'zabbixDBpass' zabbix
Create a new procedure but change the number of days for trends and history according to your needs, I will set 30 days for history and 400 days for trends:
DELIMITER $$ CREATE PROCEDURE partition_maintenance_all_30and400(SCHEMA_NAME VARCHAR(32)) BEGIN CALL partition_maintenance(SCHEMA_NAME, 'history', 30, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_log', 30, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_str', 30, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_text', 30, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 30, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'trends', 400, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 400, 24, 3); END$$ DELIMITER ;
b) Update MySQL event scheduler or Crontab
We have created the partitioning procedure in the previous step, but it is not active yet! Now we must replace the old procedure with the new one that will delete and add partitions regularly. Choose one of the two options below depending on what you have configured on your Zabbix instance.
Option 1: Update MySQL event scheduler
If you created the event scheduler following this tutorial, then use this command to replace the old procedure with the new one.
mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "ALTER EVENT zbx_partitioning ON SCHEDULE EVERY 12 HOUR DO CALL partition_maintenance_all_30and400('zabbix');"
Option 2: Update Crontab
For those using Crontab, open crontab file with the command “sudo crontab -e“, comment out the the old procedure job, and add a new one
# old procedure, still exists in the database so it can be used if needed # 30 03 * * * /usr/bin/mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all('zabbix');" > /tmp/CronDBpartitiong.log 2>&1 30 03 * * * /usr/bin/mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all_30and400('zabbix');" > /tmp/CronDBpartitiong.log 2>&1
Save your changes and exit the Crontab.
Step 6: Solution for some common errors
Duplicate partition name
If you have a different time setting on MySQL compared to your Linux OS, you will receive an error regarding duplicate partition names:
ERROR 1517 (HY000) at line 1: Duplicate partition name p202307100000
Check time on MySQL with command “SELECT CURRENT_TIMESTAMP ;” and on OS with command “timedatectl“.
If they are different then set your timezone correctly using this guide, and then restart the MySQL server.
Waiting for table metadata lock (graphs with no data)
The database partitioning commands used in this script modify metadata on the history and trends tables. If another query is holding a metadata lock on those tables, the partitioning script will ‘hang’ and block every other command, including Zabbix queries that insert data into the trend and history tables.
At first, you might think this is a fault of the partitioning script. However, upon closer examination, you will probably find some other application or script that is using Zabbix tables, causing the hang and holding that metadata lock.
My recommendation is to fix that other application or script to release the metadata lock. In the meantime, you can terminate those hanging queries by running this command in the crontab, just before you execute the partitioning script:
/usr/bin/mysql -u'root' -p'MyRootPass2!?' -e "SELECT GROUP_CONCAT(CONCAT('KILL ', id) SEPARATOR ';') AS kill_commands FROM information_schema.processlist WHERE db = 'zabbix' AND user IN ('scriptuser', 'crmapp') AND command = 'Sleep'" | grep -v '^kill_commands$' | /usr/bin/mysql -u'root' -p'MyRootPass2!?'
In my example, the command will terminate any sleeping queries originating from users named ‘scriptuser’ and ‘crmapp’. Alternatively, you can configure it applay to any non-Zabbix users using the following condition: ‘AND user != ‘zabbix’ and removing ” AND user IN (“..” part.
Finding out which user and query are holding the metadata lock is beyond the scope of this discussion. However, the following steps can be taken: Use ‘SHOW FULL PROCESSLIST;’ to check the currently running queries and their statuses. Check for queries with a ‘NULL’ value in the ‘trx_query’ field using the command ‘SELECT * FROM information_schema.INNODB_TRX \G;’. And use the query below to find out details about queries that are in a ‘sleeping’ status:
SELECT performance_schema.threads.PROCESSLIST_ID,performance_schema.threads.THREAD_ID, performance_schema.events_statements_current.SQL_TEXT,information_schema.INNODB_TRX.trx_started FROM performance_schema.threads INNER JOIN information_schema.INNODB_TRX ON performance_schema.threads.PROCESSLIST_ID = information_schema.INNODB_TRX.trx_mysql_thread_id INNER JOIN performance_schema.events_statements_current ON performance_schema.events_statements_current.THREAD_ID = performance_schema.threads.THREAD_ID\G;
Step 7: Info about Zabbix partitioning script
Zabbix partitioning SQL script that is used in this guide contains these partitioning procedures:
DELIMITER $$ CREATE PROCEDURE `partition_create`(SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int) BEGIN /* SCHEMANAME = The DB schema in which to make changes TABLENAME = The table with partitions to potentially delete PARTITIONNAME = The name of the partition to create */ /* Verify that the partition does not already exist */ DECLARE RETROWS INT; SELECT COUNT(1) INTO RETROWS FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_description >= CLOCK; IF RETROWS = 0 THEN /* 1. Print a message indicating that a partition was created. 2. Create the SQL to create the partition. 3. Execute the SQL from #2. */ SELECT CONCAT( "partition_create(", 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$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `partition_drop`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT) BEGIN /* SCHEMANAME = The DB schema in which to make changes TABLENAME = The table with partitions to potentially delete DELETE_BELOW_PARTITION_DATE = Delete any partitions with names that are dates older than this one (yyyy-mm-dd) */ DECLARE done INT DEFAULT FALSE; DECLARE drop_part_name VARCHAR(16); /* Get a list of all the partitions that are older than the date in DELETE_BELOW_PARTITION_DATE. All partitions are prefixed with a "p", so use SUBSTRING TO get rid of that character. */ DECLARE myCursor CURSOR FOR SELECT partition_name FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND CAST(SUBSTRING(partition_name FROM 2) AS UNSIGNED) < DELETE_BELOW_PARTITION_DATE; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; /* Create the basics for when we need to drop the partition. Also, create @drop_partitions to hold a comma-delimited list of all partitions that should be deleted. */ SET @alter_header = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " DROP PARTITION "); SET @drop_partitions = ""; /* Start looping through all the partitions that are too old. */ OPEN myCursor; read_loop: LOOP FETCH myCursor INTO drop_part_name; IF done THEN LEAVE read_loop; END IF; SET @drop_partitions = IF(@drop_partitions = "", drop_part_name, CONCAT(@drop_partitions, ",", drop_part_name)); END LOOP; IF @drop_partitions != "" THEN /* 1. Build the SQL to drop all the necessary partitions. 2. Run the SQL to drop the partitions. 3. Print out the table partitions that were deleted. */ SET @full_sql = CONCAT(@alter_header, @drop_partitions, ";"); PREPARE STMT FROM @full_sql; EXECUTE STMT; DEALLOCATE PREPARE STMT; SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, @drop_partitions AS `partitions_deleted`; ELSE /* No partitions are being deleted, so print out "N/A" (Not applicable) to indicate that no changes were made. */ SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, "N/A" AS `partitions_deleted`; END IF; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `partition_maintenance`(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT) BEGIN DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16); DECLARE PARTITION_NAME VARCHAR(16); DECLARE OLD_PARTITION_NAME VARCHAR(16); DECLARE LESS_THAN_TIMESTAMP INT; DECLARE CUR_TIME INT; CALL partition_verify(SCHEMA_NAME, TABLE_NAME, HOURLY_INTERVAL); SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')); SET @__interval = 1; create_loop: LOOP IF @__interval > CREATE_NEXT_INTERVALS THEN LEAVE create_loop; END IF; SET LESS_THAN_TIMESTAMP = CUR_TIME + (HOURLY_INTERVAL * @__interval * 3600); SET PARTITION_NAME = FROM_UNIXTIME(CUR_TIME + HOURLY_INTERVAL * (@__interval - 1) * 3600, 'p%Y%m%d%H00'); IF(PARTITION_NAME != OLD_PARTITION_NAME) THEN CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP); END IF; SET @__interval=@__interval+1; SET OLD_PARTITION_NAME = PARTITION_NAME; END LOOP; SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVAL KEEP_DATA_DAYS DAY), '%Y%m%d0000'); CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE); END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `partition_verify`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11)) BEGIN DECLARE PARTITION_NAME VARCHAR(16); DECLARE RETROWS INT(11); DECLARE FUTURE_TIMESTAMP TIMESTAMP; /* * Check if any partitions exist for the given SCHEMANAME.TABLENAME. */ SELECT COUNT(1) INTO RETROWS FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name IS NULL; /* * If partitions do not exist, go ahead and partition the table */ IF RETROWS = 1 THEN /* * Take the current date at 00:00:00 and add HOURLYINTERVAL to it. This is the timestamp below which we will store values. * We begin partitioning based on the beginning of a day. This is because we don't want to generate a random partition * that won't necessarily fall in line with the desired partition naming (ie: if the hour interval is 24 hours, we could * end up creating a partition now named "p201403270600" when all other partitions will be like "p201403280000"). */ SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE(), " ", '00:00:00')); SET PARTITION_NAME = DATE_FORMAT(CURDATE(), 'p%Y%m%d%H00'); -- Create the partitioning query SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " PARTITION BY RANGE(`clock`)"); SET @__PARTITION_SQL = CONCAT(@__PARTITION_SQL, "(PARTITION ", PARTITION_NAME, " VALUES LESS THAN (", UNIX_TIMESTAMP(FUTURE_TIMESTAMP), "));"); -- Run the partitioning query PREPARE STMT FROM @__PARTITION_SQL; EXECUTE STMT; DEALLOCATE PREPARE STMT; END IF; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32)) BEGIN CALL partition_maintenance(SCHEMA_NAME, 'history', 7, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_log', 7, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_str', 7, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_text', 7, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 7, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'trends', 365, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 365, 24, 3); END$$ DELIMITER ;
Need more information ? Watch this video about MySQL database partitioning for Zabbix.
Thank you for reading.