The last mile - How to automate Zabbix DB partition creation and drop

[wyang]

I'd like to set up Zabbix DB partitioning following the steps on the documentation https://zabbix.org/wiki/Docs/howto/mysql_partition.

The comment of the procedure 'partition_create' indicates that it verifies if a partition already exists before creation.

Q1: Does this mean that it is OK to run 'CALL partition_maintenance' daily even if the <num_future_intervals_to_create> is 14 days?

Q2: Running 'CALL partition_maintenance' daily = daily housekeeping on partitioned tables, correct?

Would you please share your scripts (cron or any other methods) to automate Zabbix DB partition creation, verification ?

Any help would be much appreciated.

[kloczek]

partition_maintenance procedure creates and rotates partitions on execution so you need to execute those calls in time steps of you pastitions lengths.
Additionally make sure that your that events scheduler is enabled (checking this should be part of your monitoring).
Example from my laptop:

mysql> SHOW CREATE EVENT zabbix.e_part_history_manage\G
*************************** 1. row ***************************
               Event: e_part_history_manage
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `e_part_history_manage` ON SCHEDULE EVERY 1 DAY STARTS '2017-12-14 00:02:00' ON COMPLETION PRESERVE ENABLE COMMENT 'Zabbix history tables partitioning.' DO BEGIN
        CALL partition_maintenance('zabbix', 'history',        28,    24,    7);
        CALL partition_maintenance('zabbix', 'history_log',    28,    24,    7);
        CALL partition_maintenance('zabbix', 'history_str',    28,    24,    7);
        CALL partition_maintenance('zabbix', 'history_text',    28,    24,    7);
        CALL partition_maintenance('zabbix', 'history_uint',    28,    24,    7);
    END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE EVENT zabbix.e_part_trends_manage\G
*************************** 1. row ***************************
               Event: e_part_trends_manage
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `e_part_trends_manage` ON SCHEDULE EVERY 2 WEEK STARTS '2017-12-14 00:02:00' ON COMPLETION PRESERVE ENABLE COMMENT 'Zabbix trends tables partitioning.' DO BEGIN
        CALL partition_maintenance('zabbix', 'trends',        730,    336,    2);
        CALL partition_maintenance('zabbix', 'trends_uint',    730,    336,    2);
    END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

mysql> select VARIABLE_VALUE from performance_schema.global_variables where VARIABLE_NAME='event_scheduler';
+----------------+
| VARIABLE_VALUE |
+----------------+
| ON             |
+----------------+
1 row in set (0.00 sec)

mysql>

You can find that in my mysql template (https://github.com/kloczek/zabbix-te...ervice%20MySQL) event_scheduler is monitored and has trigger alarming if event_scheduler=OFF.

[wyang]

Thank you so much kloczek ! Greatly appreciated !

[wyang]

I am still confused that how Zabbix knows some tables being partitioned and saves data to proper partition?

[kloczek]

Zabbix sees database over results of the queries.
From such point of view physical representation of the tables doesn't matte/is transparent.
Remember that not so long time ago MySQL had in default settings innodb_file_per_table=off so all tables and databases have been stored in single file.

[wyang]

The event created with reference to your code works perfect in the test environment. Starting from your reply, I got better understanding on table partitioning.

Thank you very much again kloczek for all the help! Greatly appreciated!