Zabbix partitioning not cleaning up old tables

[pyrodex]

Hello!

We have a fairly large environment and after hitting the wall with some issues with history and trends I decided to go the MySQL partition route using https://zabbix.org/wiki/Docs/howto/mysql_partition as a guide.

Partitioning is working just fine but I am noticing the clean up aspect of partition_maintenance isn't cleaning up older tables.

Here is how I created my tables:

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

Since we have a large amount of data I opted for hourly history* tables and trends are fine daily.

I do run the following command in RHEL under cron.daily and the new tables are being created but the older ones are not dropping.

/bin/mysql -h localhost -u zabbix -pPASSWD zabbix -e "CALL partition_maintenance_all('zabbix');"

Any thoughts on where to begin to troubleshoot? I am no mysql guru but can help with commands.

[kloczek]

https://www.zabbix.com/forum/zabbix-help/357756-the-last-mile-how-to-automate-zabbix-db-partition-creation-and-drop

[pyrodex]

https://www.zabbix.com/forum/zabbix-...ation-and-drop

I understand this but I have a cron script working properly to call partition_maintenance per the initial URL but it isn't dropping tables, it completely creates them without issues but doesn't drop them. This URL you provided embeds the cron script in mysql basically using native events but I don't need that or am I missing something else?

[kloczek]

It is one very good reason why not to us for such maintenance scripts started over cron.
SQL event can be defined that it can be executed on master or slave instance.
Sooner or later OS beneath SQL DB engine needs to be rebuild/reinstall or upgraded so you must have platform which will allow you to quickly (in matter of 2-5s) promore current slave database as new master -> do maintenance on prev master -> snapshot new master -> replicate snapshot data to rebuild new slave.
In such scenario on promote slave as new master you must remember that crontab must be deactivated on prev master and moved to new host.
With SQL event it will be done automatically and you will not trash your current smale by executing partitioning maintenance on slave.
The same SQL even but defined as executed only on slave can be used to start off-site backup generated always on slave.
Simple doesn't matter where slave and master are SQL event will guarantee that exact maintenance procedure like partitions rotations or making backups will be started on right host.
Just KISS principle ..
https://dev.mysql.com/doc/refman/5.7...ate-event.html

[pyrodex]

It is one very good reason why not to us for such maintenance scripts started over cron.
SQL event can be defined that it can be executed on master or slave instance.
Sooner or later OS beneath SQL DB engine needs to be rebuild/reinstall or upgraded so you must have platform which will allow you to quickly (in matter of 2-5s) promore current slave database as new master -> do maintenance on prev master -> snapshot new master -> replicate snapshot data to rebuild new slave.
In such scenario on promote slave as new master you must remember that crontab must be deactivated on prev master and moved to new host.
With SQL event it will be done automatically and you will not trash your current smale by executing partitioning maintenance on slave.
The same SQL even but defined as executed only on slave can be used to start off-site backup generated always on slave.
Simple doesn't matter where slave and master are SQL event will guarantee that exact maintenance procedure like partitions rotations or making backups will be started on right host.
Just KISS principle ..
https://dev.mysql.com/doc/refman/5.7...ate-event.html

Understood 100% without a doubt, however when I even disable my cron and run these commands in SQL events the OLD tables are still not getting removed. How do I troubleshoot why my old tables are not getting removed with a maintenance command but when I run partition_drop with a date of the previous day it cleans them up properly?

[kloczek]

Understood 100% without a doubt, however when I even disable my cron and run these commands in SQL events the OLD tables are still not getting removed. How do I troubleshoot why my old tables are not getting removed with a maintenance command but when I run partition_drop with a date of the previous day it cleans them up properly?

User from which partitions maintenance is executed needs to have granted ALER TABLES permission.
I\m running all events from root (nit from zabbix SQL db user) and I'm not leaving ALER TABLES permission during normal zabbix exploitation.
In such scenario I mustonly remember that during zabbix binaries upgrade temporary ALER TABLES permission to be granted temporary because during zabbix database schema upgrade zabbix server/proxy processes are using ALER TABLES queries.