Optimizing Zabbix with MySQL: Version Tips, Permissions, and Partitioning Strategies

MySQL Version Recommendation

Use MySQL 5.7 or newer to benefit from online DDL for fast Zabbix upgrades. Connect Zabbix server, proxy, and MySQL using domain names to simplify failover.

Zabbix Database Grants

grant all privileges on zabbix.* to 'zabbix'@'1.1.1.1' identified by 'zabbix';

grant SELECT on zabbix.* to 'zabbix_ro'@'1.1.1.1' identified by 'zabbixro';

Important MySQL Configuration Parameters

innodb_log_files_in_group = 16

innodb_log_file_size = 1G

innodb_file_per_table = 1

max_allowed_packet = 64M

back_log = 1024

max_connections = 2000

sync_binlog = 0

innodb_flush_log_at_trx_commit = 0

binlog_format = row

expire_logs_days = 3

Using SQL for Zabbix Maintenance

update zabbix.items set delay=5 where key='icmpping';

update zabbix.items t, zabbix.functions f set f.parameter='30' where f.itemid=t.itemid and t.key='agent.ping';

Read/Write Splitting and Load Balancing

For secondary development, use a dedicated read‑only replica to avoid impacting the production server. Exclude auditlog, sessions, and ids tables from replication, and grant write permission on these tables. Use IP‑hash for Zabbix web API load balancing to preserve session affinity.

replicate-wild-ignore-table = zabbix.auditlog

replicate-wild-ignore-table = zabbix.sessions

replicate-wild-ignore-table = zabbix.ids

Zabbix Upgrade Considerations

Create a new database cluster, sync data, then switch the Zabbix server and database domain to the new cluster to minimize monitoring disruption.

Database Backup and Data Cleanup

Back up only essential information; exclude monitoring data, audit logs, and events due to size. Delete old history and trend data using partitioning, and regularly clean the events table, keeping roughly five days of records and performing fragment cleanup.

Zabbix Server History and Trend Table Partitioning

Download partition SQL scripts (for Zabbix 2.0/3.0) from https://www.zabbix.org/wiki/Docs/howto/mysql_partition . Add indexes on the clock column for faster time‑based queries.

ALTER TABLE history ADD INDEX clock (clock);

ALTER TABLE history_log ADD INDEX clock (clock);

ALTER TABLE history_str ADD INDEX clock (clock);

ALTER TABLE history_str_sync ADD INDEX clock (clock);

ALTER TABLE history_sync ADD INDEX clock (clock);

ALTER TABLE history_text ADD INDEX clock (clock);

ALTER TABLE history_uint ADD INDEX clock (clock);

ALTER TABLE history_uint_sync ADD INDEX clock (clock);

ALTER TABLE trends ADD INDEX clock (clock);

ALTER TABLE trends_uint ADD INDEX clock (clock);

ALTER TABLE EVENTS ADD INDEX objectid (objectid, VALUE, clock);

Stored Procedure Cleanup

DROP PROCEDURE IF EXISTS partition_create;

DROP PROCEDURE IF EXISTS partition_drop;

DROP PROCEDURE IF EXISTS partition_maintenance;

DROP PROCEDURE IF EXISTS partition_maintenance_all;

DROP PROCEDURE IF EXISTS partition_verify;

Partition Maintenance Procedures

Procedure partition_create creates a new partition if it does not already exist.

DELIMITER $$
CREATE PROCEDURE partition_create(
  SCHEMANAME VARCHAR(64),
  TABLENAME VARCHAR(64),
  PARTITIONNAME VARCHAR(64),
  CLOCK INT
) BEGIN
  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
    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 ;

Procedure partition_drop removes partitions older than a specified date.

DELIMITER $$
CREATE PROCEDURE partition_drop(
  SCHEMANAME VARCHAR(64),
  TABLENAME VARCHAR(64),
  DELETE_BELOW_PARTITION_DATE BIGINT
) BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE drop_part_name VARCHAR(16);
  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;
  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
    SET @full_sql = CONCAT('ALTER TABLE ', SCHEMANAME, '.', TABLENAME,
      ' DROP PARTITION ', @drop_partitions, ';');
    PREPARE STMT FROM @full_sql;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT;
    SELECT CONCAT(SCHEMANAME, '.', TABLENAME) AS `table`, @drop_partitions AS `partitions_deleted`;
  ELSE
    SELECT CONCAT(SCHEMANAME, '.', TABLENAME) AS `table`, 'N/A' AS `partitions_deleted`;
  END IF;
END$$
DELIMITER ;

Procedure partition_maintenance creates future partitions and drops old ones based on retention settings.

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 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');
    CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);
    SET @__interval = @__interval + 1;
  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 ;

Procedure partition_verify checks if a table already has partitions and creates an initial one if needed.

DELIMITER $$
CREATE PROCEDURE partition_verify(
  SCHEMANAME VARCHAR(64),
  TABLENAME VARCHAR(64),
  HOURLYINTERVAL INT
) BEGIN
  DECLARE RETROWS INT;
  SELECT COUNT(1) INTO RETROWS FROM information_schema.partitions
    WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND partition_name IS NULL;
  IF RETROWS = 1 THEN
    SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, HOURLYINTERVAL,
      CONCAT(CURDATE(), ' 00:00:00'));
    SET PARTITION_NAME = DATE_FORMAT(CURDATE(), 'p%Y%m%d%H00');
    SET @__PARTITION_SQL = CONCAT('ALTER TABLE ', SCHEMANAME, '.', TABLENAME,
      ' PARTITION BY RANGE(`clock`) (PARTITION ', PARTITION_NAME,
      ' VALUES LESS THAN (', UNIX_TIMESTAMP(FUTURE_TIMESTAMP), '));');
    PREPARE STMT FROM @__PARTITION_SQL;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT;
  END IF;
END$$
DELIMITER ;

Procedure partition_maintenance_all runs partition_maintenance for all Zabbix history and trend tables.

DELIMITER $$
CREATE PROCEDURE partition_maintenance_all(SCHEMA_NAME VARCHAR(32))
BEGIN
  CALL partition_maintenance(SCHEMA_NAME, 'history', 28, 24, 14);
  CALL partition_maintenance(SCHEMA_NAME, 'history_log', 28, 24, 14);
  CALL partition_maintenance(SCHEMA_NAME, 'history_str', 28, 24, 14);
  CALL partition_maintenance(SCHEMA_NAME, 'history_text', 28, 24, 14);
  CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 28, 24, 14);
  CALL partition_maintenance(SCHEMA_NAME, 'trends', 730, 24, 14);
  CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 730, 24, 14);
END$$
DELIMITER ;

Original source: https://blog.51cto.com/wangwei007/2458114

Author: JD DBA Wang Wei