MySQL Partitioning recommendations request

[rbeglinger]

I can unfortunately confirm my suspicion that the deletion process is not working with CentOS7, and MySQL 8.0.15-1. I have my retention set at 45 days history and 13 months for trends, but today when I logged in , there are 59 history, history_log, history_str, history_text and history_uint partitions.

# ll history#P#p2019_0* |wc -l
59
# ll history_uint#P#p2019_0* |wc -l
59
ll history_text#P#p2019_0* |wc -l
59

The output in /var/log messages:

Mar  7 00:01:02 server mysql_zbx_part[49910]: Creating a partition for history table (p2019_03_08)
Mar  7 00:01:02 server mysql_zbx_part[49910]: Creating a partition for history table (p2019_03_09)
Mar  7 00:01:02 server mysql_zbx_part[49910]: Creating a partition for history table (p2019_03_10)
Mar  7 00:01:02 server mysql_zbx_part[49910]: Creating a partition for history table (p2019_03_11)
Mar  7 00:01:02 server mysql_zbx_part[49910]: Creating a partition for history table (p2019_03_12)
Mar  7 00:01:02 server mysql_zbx_part[49910]: Creating a partition for history table (p2019_03_13)
Mar  7 00:01:02 server mysql_zbx_part[49910]: Creating a partition for history table (p2019_03_14)
Mar  7 00:01:02 server mysql_zbx_part[49910]: Creating a partition for history table (p2019_03_15)
Mar  7 00:01:02 server mysql_zbx_part[49910]: Creating a partition for history table (p2019_03_16)
Mar  7 00:01:02 server mysql_zbx_part[49910]: Creating a partition for history table (p2019_03_17)
.....
Mar  7 00:01:04 server mysql_zbx_part[49910]: Creating a partition for trends_uint table (p2019_04)
Mar  7 00:01:04 server mysql_zbx_part[49910]: Creating a partition for trends_uint table (p2019_05)
Mar  7 00:01:04 server mysql_zbx_part[49910]: Creating a partition for trends_uint table (p2019_06)
Mar  7 00:01:04 server mysql_zbx_part[49910]: Creating a partition for trends_uint table (p2019_07)
Mar  7 00:01:04 server mysql_zbx_part[49910]: Creating a partition for trends_uint table (p2019_08)
Mar  7 00:01:04 server mysql_zbx_part[49910]: Creating a partition for trends_uint table (p2019_09)
Mar  7 00:01:04 server mysql_zbx_part[49910]: Creating a partition for trends_uint table (p2019_10)
Mar  7 00:01:04 server mysql_zbx_part[49910]: Creating a partition for trends_uint table (p2019_11)
Mar  7 00:01:04 server mysql_zbx_part[49910]: Creating a partition for trends_uint table (p2019_12)
Mar  7 00:01:04 server mysql_zbx_part[49910]: Creating a partition for trends_uint table (p2020_01)

When I run the script from the command line I get the following output:

# /usr/local/bin/zabbix-mysql-partitioning.pl
DBD::mysql::db do failed: Duplicate partition name p2019_03_08 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_03_09 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_03_10 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_03_11 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_03_12 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_03_13 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_03_14 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_03_15 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_03_16 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_03_17 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
.....
DBD::mysql::db do failed: Duplicate partition name p2019_04 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_05 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_06 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_07 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_08 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_09 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_10 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_11 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2019_12 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.
DBD::mysql::db do failed: Duplicate partition name p2020_01 at /usr/local/bin/zabbix-mysql-partitioning.pl line 77.

I would expect no more than 55 partitions for the history tables, 45 day retention + 10 day pre-creation of partitions. I am able to manually go into the database and ALTER TABLE history DROP PARTITION....; successfully. It does create the partitions for future dates, so I'm confident that it is not a permissions issue.

Anybody else having this issue?

[steeladept]

When you first posted about this I thought you were just talking about the file system, but over the last month I have been having issue after issue and found this was the root cause - It was causing the database to fill up because the partitions were not being deleted as expected. Once I cleared the database partitions that didn't belong, it all started humming along smoothly again.

[alexthefifth]

May want to consider MariaDB. No issues using the partitioning script with the latest MariaDB 10.3

[scuba]

Any help would be appreciated to upgrade MariaDB to 10.3

Thanks

[XPystchrisX]

I can unfortunately confirm my suspicion that the deletion process is not working with CentOS7, and MySQL 8.0.15-1. I have my retention set at 45 days history and 13 months for trends, but today when I logged in , there are 59 history, history_log, history_str, history_text and history_uint partitions.

....

I would expect no more than 55 partitions for the history tables, 45 day retention + 10 day pre-creation of partitions. I am able to manually go into the database and ALTER TABLE history DROP PARTITION....; successfully. It does create the partitions for future dates, so I'm confident that it is not a permissions issue.

Anybody else having this issue?

I'm having this issue. I'm using Percona 8 and it's not dropping the partitions like I would expect. Of course I'm a complete neophyte when it comes to MySQL and PERL so I'm a bit in the dark. But I can confirm that I presently have two servers, both of which are Percona 8, and CentOS 7. One is configured to retain 30 days and 12 months, the other after 90 days and 24 months. Neither server is dropping partitions, and the logs are clean other than the dup partition complaints.

[scuba]

Hi,

Could somebody tidy up the case and update the terms related to Zabbix 4.0x and never versions what should be done to make a proper partitioning please ? Hope to get rid of this History Syncer and HouseKeeper processes busy issue.

Thank you

[dfisicaro]

I completed the MYSQL Partitioning and now for some strange reason my email alerts/notifications has stopped, does anyone have any suggestions of why?

[landsverka]

Hello,

I am a colleague of rbeglinger, I looked into the Perl script a bit and was able to determine that there is a case sensitivity issue with the query at the top that is retrieving from 'information_schema.partitions'. The columns, table_name, partition_name and partition_description all come back from MySQL as all upper case; however, the Perl is expecting them to be all lower case when using them as keys in the script. I was able to correct the Perl script by adding column aliases to those fields as follows:

my $sth = $dbh->prepare(qq{SELECT table_name as table_name, partition_name as partition_name, lower(partition_method) as partition_method,
                    rtrim(ltrim(partition_expression)) as partition_expression,
                    partition_description as partition_description, table_rows
                FROM information_schema.partitions
                WHERE partition_name IS NOT NULL AND table_schema = ?});

After adjusting the query in the script, we were able to successfully execute and everything worked.

Thanks!

[cesarsj]

I was excited to use partitioning but reading the whole topic (all 4 pages so far) concludes that I better just keep housekeeper on, there are few items my boss wants to keep for 10 years. So far the bank has 239G, but I activated the housekeeper last week, and is being averaged 600,000 historical per hour. The history table has 1,807,219,247 records, I will see if it will decrease significantly at least once a day.

I want to free up disk space, my question is if only housekeeper releases, or if I have to run the mysql Optimize command.

I don't know why you want to partition, here we have 800 hosts being monitored, we have disabled a lot of items we don't need, 8708 enabled / 5058 disabled, and Zabbix server processing is still good. NVPS is 81.19.

[gofree]

housekeeper will not freeup space in your case ( ibd files per table ) and the optimize command youre looking for is not a good idea in my opinion - see the link - seems it locks the table for some time - propably bigger the table more time I guess and you tables are huge - I guess your collections intervals are short

https://medium.com/cleartax-engineer...e-cb29b35c0086

honestly if youre looking to use zabbix at this setup you'll have to go with partitioning at some time or at some time your boss will complain on zabbix and force you to change the monitoring solution even with partitioning your DB will be big ( short intervals and long history ) but the performance might be better - 10 yeasr of history data would make more sense on a backup/tape - in case you need them you restore them - there is no point to keep them in live zabbix DB

[cesarsj]

I activated the housekeeper, cleaned up old data and then ran optimize on MariaDB 10.0.28, and this version enables online optimization, with an inconspicuous lock; Believe me, 10G of disk space has been released.

[XPystchrisX]

Just wanted to reply with an update using landsverka 's update to the part.pl script. I took a percona backup of my (formerly bloated) mysql db and then ran the new version of the script. I can confirm that after about 5 minutes on a RAID10 SSD configuration I now have the proper number of partitions in the MySQL DB and that things are running great. Thank you!