Ad Widget

Collapse

MySQL Partitioning recommendations request

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

    #46
    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.

    Code:
    # 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:

    Code:
    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:
    Code:
    # /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?

    Comment


    • steeladept
      steeladept commented
      Editing a comment
      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.

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

    Comment


      #48
      Originally posted by rbeglinger View Post
      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.

      Comment

      Announcement

      Collapse
      No announcement yet.
      Working...
      X