Ad Widget

Collapse

MySQL Partitioning recommendations request

Collapse
X
Collapse
 
  • Page of 4
  • Time
  • Show
Clear All
new posts
Previous 1 2 3 4 template Next
  • steeladept
    Member
    • Sep 2018
    • 69
    #31
    Originally posted by ingus.vilnis
    Code:
    my $amount_partitions = 10;
    This is the control which specifies how many partitions ahead will be created. This just gives you some space to store the data in the unlikely event of the script failing or whatnot.
    Thank you. That $amount_partitions part is what I missed. It seems to be working exactly as you described then. Creating 10 partitions ahead and not deleting any older partitions until they are at least x days/months old. And the timezone is exactly as you said, so we are good there.

      Comment

      • ingus.vilnis
        Senior Member
        Zabbix Certified Trainer
        Zabbix Certified SpecialistZabbix Certified Professional
        • Mar 2014
        • 908
        #32
        Awesome.
        Don't forget to verify now if the script works nightly as scheduled in cron.

        If you ever run into failures, the partitions are not created and you run out of pre-created partitions then just run the script as soon as you detect the problems and you should be good. But the best is to set up a Zabbix log item for monitoring of the failures in the log.

        So I wish you good luck!

          Comment

          • rbeglinger
            Junior Member
            • Jan 2019
            • 10
            #33
            First, I apologize for reviving an older thread, but this is exactly the situation I'm running into.

            I'm performing a new installation of Zabbix 4.0.3 on MySQL 8.0.13, Centos 7 and I'm trying to get the external partitioning script working. I've gotten past the initial issue of it reporting that "MySQL does not support partitioning", but now I get the following message in /var/log/messages for all tables when I run the script from the command line:

            mysql_zbx_part[123048]: Partitioning for "history" is not found! The table might be not partitioned.
            mysql_zbx_part[123048]: Partitioning for "history_log" is not found! The table might be not partitioned.
            mysql_zbx_part[123048]: Partitioning for "history_str" is not found! The table might be not partitioned.
            mysql_zbx_part[123048]: Partitioning for "history_text" is not found! The table might be not partitioned.
            mysql_zbx_part[123048]: Partitioning for "history_uint" is not found! The table might be not partitioned.
            mysql_zbx_part[123048]: Partitioning for "trends" is not found! The table might be not partitioned.
            mysql_zbx_part[123048]: Partitioning for "trends_uint" is not found! The table might be not partitioned.

            The history* and trends* tables are partitioned:
            Code:
            # ll /var/lib/mysql/zabbix/*2019*
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_log#P#p2019_01_04.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_log#P#p2019_01_05.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_log#P#p2019_01_06.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_log#P#p2019_01_07.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_log#P#p2019_01_08.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_log#P#p2019_01_09.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_log#P#p2019_01_10.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_log#P#p2019_01_11.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_log#P#p2019_01_12.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_log#P#p2019_01_13.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_log#P#p2019_01_14.ibd
-rw-r-----. 1 mysql mysql 10485760 Jan 14 08:08 /var/lib/mysql/zabbix/history#P#p2019_01_04.ibd
-rw-r-----. 1 mysql mysql 27262976 Jan 14 08:08 /var/lib/mysql/zabbix/history#P#p2019_01_05.ibd
-rw-r-----. 1 mysql mysql 28311552 Jan 14 08:08 /var/lib/mysql/zabbix/history#P#p2019_01_06.ibd
-rw-r-----. 1 mysql mysql 29360128 Jan 14 08:08 /var/lib/mysql/zabbix/history#P#p2019_01_07.ibd
-rw-r-----. 1 mysql mysql 31457280 Jan 14 08:08 /var/lib/mysql/zabbix/history#P#p2019_01_08.ibd
-rw-r-----. 1 mysql mysql 32505856 Jan 14 08:08 /var/lib/mysql/zabbix/history#P#p2019_01_09.ibd
-rw-r-----. 1 mysql mysql 32505856 Jan 14 08:08 /var/lib/mysql/zabbix/history#P#p2019_01_10.ibd
-rw-r-----. 1 mysql mysql 32505856 Jan 14 08:08 /var/lib/mysql/zabbix/history#P#p2019_01_11.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:08 /var/lib/mysql/zabbix/history#P#p2019_01_12.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:08 /var/lib/mysql/zabbix/history#P#p2019_01_13.ibd
-rw-r-----. 1 mysql mysql 12582912 Jan 14 11:19 /var/lib/mysql/zabbix/history#P#p2019_01_14.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_str#P#p2019_01_04.ibd
-rw-r-----. 1 mysql mysql   180224 Jan 14 08:07 /var/lib/mysql/zabbix/history_str#P#p2019_01_05.ibd
-rw-r-----. 1 mysql mysql   180224 Jan 14 08:07 /var/lib/mysql/zabbix/history_str#P#p2019_01_06.ibd
-rw-r-----. 1 mysql mysql   212992 Jan 14 08:07 /var/lib/mysql/zabbix/history_str#P#p2019_01_07.ibd
-rw-r-----. 1 mysql mysql   212992 Jan 14 08:07 /var/lib/mysql/zabbix/history_str#P#p2019_01_08.ibd
-rw-r-----. 1 mysql mysql   229376 Jan 14 08:07 /var/lib/mysql/zabbix/history_str#P#p2019_01_09.ibd
-rw-r-----. 1 mysql mysql   229376 Jan 14 08:07 /var/lib/mysql/zabbix/history_str#P#p2019_01_10.ibd
-rw-r-----. 1 mysql mysql   229376 Jan 14 08:07 /var/lib/mysql/zabbix/history_str#P#p2019_01_11.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_str#P#p2019_01_12.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_str#P#p2019_01_13.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 11:07 /var/lib/mysql/zabbix/history_str#P#p2019_01_14.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_text#P#p2019_01_04.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_text#P#p2019_01_05.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_text#P#p2019_01_06.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_text#P#p2019_01_07.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_text#P#p2019_01_08.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_text#P#p2019_01_09.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_text#P#p2019_01_10.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_text#P#p2019_01_11.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_text#P#p2019_01_12.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_text#P#p2019_01_13.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_text#P#p2019_01_14.ibd
-rw-r-----. 1 mysql mysql  7340032 Jan 14 08:07 /var/lib/mysql/zabbix/history_uint#P#p2019_01_04.ibd
-rw-r-----. 1 mysql mysql 19922944 Jan 14 08:07 /var/lib/mysql/zabbix/history_uint#P#p2019_01_05.ibd
-rw-r-----. 1 mysql mysql 19922944 Jan 14 08:07 /var/lib/mysql/zabbix/history_uint#P#p2019_01_06.ibd
-rw-r-----. 1 mysql mysql 24117248 Jan 14 08:07 /var/lib/mysql/zabbix/history_uint#P#p2019_01_07.ibd
-rw-r-----. 1 mysql mysql 37748736 Jan 14 08:07 /var/lib/mysql/zabbix/history_uint#P#p2019_01_08.ibd
-rw-r-----. 1 mysql mysql 41943040 Jan 14 08:07 /var/lib/mysql/zabbix/history_uint#P#p2019_01_09.ibd
-rw-r-----. 1 mysql mysql 41943040 Jan 14 08:07 /var/lib/mysql/zabbix/history_uint#P#p2019_01_10.ibd
-rw-r-----. 1 mysql mysql 41943040 Jan 14 08:07 /var/lib/mysql/zabbix/history_uint#P#p2019_01_11.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_uint#P#p2019_01_12.ibd
-rw-r-----. 1 mysql mysql   131072 Jan 14 08:07 /var/lib/mysql/zabbix/history_uint#P#p2019_01_13.ibd
-rw-r-----. 1 mysql mysql 13631488 Jan 14 11:19 /var/lib/mysql/zabbix/history_uint#P#p2019_01_14.ibd
-rw-r-----. 1 mysql mysql 14680064 Jan 14 11:02 /var/lib/mysql/zabbix/trends#P#p2019_01.ibd
-rw-r-----. 1 mysql mysql 15728640 Jan 14 11:10 /var/lib/mysql/zabbix/trends_uint#P#p2019_01.ibd
            So

            Originally posted by steeladept
            That helped a lot. So I removed some sections of the script and it seems to have worked. However, it didn't do what I expected and so I am wondering if I am understanding what it should do correctly. What it did was create the next 10 partitions into the future. I expected it would just create today's partitions and delete any older than I set in the time section (30 days, or 3 months or whatever). Did I misunderstand and it is working correctly, or did I edit it wrong somehow? Either way, it seems to be working, just surprised me a bit on it's behavior.
            I'm curious what sections were removed from the script. I commented out the sub check_have_partition { ...} and the call to the check_have_partition section.

            Code:
            #!/usr/bin/perl

use strict;
use Data::Dumper;
use DBI;
use Sys::Syslog qw(:standard :macros);
use DateTime;
use POSIX qw(strftime);

openlog("mysql_zbx_part", "ndelay,pid", LOG_LOCAL0);

my $db_schema = 'zabbix';
my $dsn = 'DBI:mysql:'.$db_schema.':mysql_socket=/var/lib/mysql/mysql.sock';
my $db_user_name = 'zbx_part';
my $db_password = '<Zabbix partitioning DB user password>';
my $tables = {  'history' => { 'period' => 'day', 'keep_history' => '30'},
        'history_log' => { 'period' => 'day', 'keep_history' => '30'},
        'history_str' => { 'period' => 'day', 'keep_history' => '30'},
        'history_text' => { 'period' => 'day', 'keep_history' => '30'},
        'history_uint' => { 'period' => 'day', 'keep_history' => '30'},
        'trends' => { 'period' => 'month', 'keep_history' => '12'},
        'trends_uint' => { 'period' => 'month', 'keep_history' => '12'},
        };
my $amount_partitions = 10;

my $curr_tz = 'Europe/Riga';

my $part_tables;

my $dbh = DBI->connect($dsn, $db_user_name, $db_password);

#unless ( check_have_partition() ) {
    #print "Your installation of MySQL does not support table partitioning.\n";
    #syslog(LOG_CRIT, 'Your installation of MySQL does not support table partitioning.');
    #exit 1;
#}

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

while (my $row =  $sth->fetchrow_hashref()) {
    $part_tables->{$row->{'table_name'}}->{$row->{'partition_name'}} = $row;
}

$sth->finish();

foreach my $key (sort keys %{$tables}) {
    unless (defined($part_tables->{$key})) {
        syslog(LOG_ERR, 'Partitioning for "'.$key.'" is not found! The table might be not partitioned.');
        next;
    }

    create_next_partition($key, $part_tables->{$key}, $tables->{$key}->{'period'});
    remove_old_partitions($key, $part_tables->{$key}, $tables->{$key}->{'period'}, $tables->{$key}->{'keep_history'})
}

delete_old_data();

$dbh->disconnect();

#sub check_have_partition {
    #my $result = 0;
#
    #my $sth = $dbh->prepare(qq{SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = 'partition'});
#
    #$sth->execute();
#
    #my $row = $sth->fetchrow_array();
#
    #$sth->finish();
#
    #return 1 if $row eq 'ACTIVE';
#}

sub create_next_partition {
    my $table_name = shift;
    my $table_part = shift;
    my $period = shift;

    for (my $curr_part = 0; $curr_part < $amount_partitions; $curr_part++) {
        my $next_name = name_next_part($tables->{$table_name}->{'period'}, $curr_part);
        my $found = 0;

        foreach my $partition (sort keys %{$table_part}) {
            if ($next_name eq $partition) {
                syslog(LOG_INFO, "Next partition for $table_name table has already been created. It is $next_name");
                $found = 1;
            }
        }

        if ( $found == 0 ) {
            syslog(LOG_INFO, "Creating a partition for $table_name table ($next_name)");
            my $query = 'ALTER TABLE '."$db_schema.$table_name".' ADD PARTITION (PARTITION '.$next_name.
                        ' VALUES less than (UNIX_TIMESTAMP("'.date_next_part($tables->{$table_name}->{'period'}, $curr_part).'") div 1))';
            syslog(LOG_DEBUG, $query);
            $dbh->do($query);
        }
    }
}

sub remove_old_partitions {
    my $table_name = shift;
    my $table_part = shift;
    my $period = shift;
    my $keep_history = shift;

    my $curr_date = DateTime->now;
    $curr_date->set_time_zone( $curr_tz );

    if ( $period eq 'day' ) {
        $curr_date->add(days => -$keep_history);
        $curr_date->add(hours => -$curr_date->strftime('%H'));
        $curr_date->add(minutes => -$curr_date->strftime('%M'));
        $curr_date->add(seconds => -$curr_date->strftime('%S'));
    }
    elsif ( $period eq 'week' ) {
    }
    elsif ( $period eq 'month' ) {
        $curr_date->add(months => -$keep_history);

        $curr_date->add(days => -$curr_date->strftime('%d')+1);
        $curr_date->add(hours => -$curr_date->strftime('%H'));
        $curr_date->add(minutes => -$curr_date->strftime('%M'));
        $curr_date->add(seconds => -$curr_date->strftime('%S'));
    }

    foreach my $partition (sort keys %{$table_part}) {
        if ($table_part->{$partition}->{'partition_description'} <= $curr_date->epoch) {
            syslog(LOG_INFO, "Removing old $partition partition from $table_name table");

            my $query = "ALTER TABLE $db_schema.$table_name DROP PARTITION $partition";

            syslog(LOG_DEBUG, $query);
            $dbh->do($query);
        }
    }
}

sub name_next_part {
    my $period = shift;
    my $curr_part = shift;

    my $name_template;

    my $curr_date = DateTime->now;
    $curr_date->set_time_zone( $curr_tz );

    if ( $period eq 'day' ) {
        my $curr_date = $curr_date->truncate( to => 'day' );
        $curr_date->add(days => 1 + $curr_part);

        $name_template = $curr_date->strftime('p%Y_%m_%d');
    }
    elsif ($period eq 'week') {
        my $curr_date = $curr_date->truncate( to => 'week' );
        $curr_date->add(days => 7 * $curr_part);

        $name_template = $curr_date->strftime('p%Y_%m_w%W');
    }
    elsif ($period eq 'month') {
        my $curr_date = $curr_date->truncate( to => 'month' );
        $curr_date->add(months => 1 + $curr_part);

        $name_template = $curr_date->strftime('p%Y_%m');
    }

    return $name_template;
}

sub date_next_part {
    my $period = shift;
    my $curr_part = shift;

    my $period_date;

    my $curr_date = DateTime->now;
    $curr_date->set_time_zone( $curr_tz );

    if ( $period eq 'day' ) {
        my $curr_date = $curr_date->truncate( to => 'day' );
        $curr_date->add(days => 2 + $curr_part);
        $period_date = $curr_date->strftime('%Y-%m-%d');
    }
    elsif ($period eq 'week') {
        my $curr_date = $curr_date->truncate( to => 'week' );
        $curr_date->add(days => 7 * $curr_part + 1);
        $period_date = $curr_date->strftime('%Y-%m-%d');
    }
    elsif ($period eq 'month') {
        my $curr_date = $curr_date->truncate( to => 'month' );
        $curr_date->add(months => 2 + $curr_part);

        $period_date = $curr_date->strftime('%Y-%m-%d');
    }

    return $period_date;
}

sub delete_old_data {
    $dbh->do("DELETE FROM sessions WHERE lastaccess < UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH)");
    $dbh->do("TRUNCATE housekeeper");
    $dbh->do("DELETE FROM auditlog_details WHERE NOT EXISTS (SELECT NULL FROM auditlog WHERE auditlog.auditid = auditlog_details.auditid)");
}

              Comment

              • steeladept
                #33.1
                steeladept commented
                14-01-2019, 22:26
                Editing a comment
                Those were the two sections I commented out.
              • rbeglinger
                Junior Member
                • Jan 2019
                • 10
                #34
                I did a little more digging and I found another section to remove/comment out.

                Code:
                foreach my $key (sort keys %{$tables}) {
    #unless (defined($part_tables->{$key})) {
        #syslog(LOG_ERR, 'Partitioning for "'.$key.'" is not found! The table might be not partitioned.');
        #next;
    #}

    create_next_partition($key, $part_tables->{$key}, $tables->{$key}->{'period'});
    remove_old_partitions($key, $part_tables->{$key}, $tables->{$key}->{'period'}, $tables->{$key}->{'keep_history'})
}
                This allowed the partitions to be created. I sure there is good reason to have that in for error checking, but it is broken for MySQL 8.

                Here is my "working" script file.

                Code:
                #!/usr/bin/perl

use strict;
use Data::Dumper;
use DBI;
use Sys::Syslog qw(:standard :macros);
use DateTime;
use POSIX qw(strftime);

openlog("mysql_zbx_part", "ndelay,pid", LOG_LOCAL0);

my $db_schema = 'zabbix';
my $dsn = 'DBI:mysql:'.$db_schema.':mysql_socket=/var/lib/mysql/mysql.sock';
my $db_user_name = 'zbx_part';
my $db_password = '<Zabbix partitioning DB user password>';
my $tables = { 'history' => { 'period' => 'day', 'keep_history' => '30'},
'history_log' => { 'period' => 'day', 'keep_history' => '30'},
'history_str' => { 'period' => 'day', 'keep_history' => '30'},
'history_text' => { 'period' => 'day', 'keep_history' => '30'},
'history_uint' => { 'period' => 'day', 'keep_history' => '30'},
'trends' => { 'period' => 'month', 'keep_history' => '12'},
'trends_uint' => { 'period' => 'month', 'keep_history' => '12'},
};
my $amount_partitions = 10;

my $curr_tz = 'Europe/Riga';

my $part_tables;

my $dbh = DBI->connect($dsn, $db_user_name, $db_password);

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

while (my $row = $sth->fetchrow_hashref()) {
$part_tables->{$row->{'table_name'}}->{$row->{'partition_name'}} = $row;
}

$sth->finish();

foreach my $key (sort keys %{$tables}) {
create_next_partition($key, $part_tables->{$key}, $tables->{$key}->{'period'});
remove_old_partitions($key, $part_tables->{$key}, $tables->{$key}->{'period'}, $tables->{$key}->{'keep_history'})
}

delete_old_data();

$dbh->disconnect();

sub create_next_partition {
my $table_name = shift;
my $table_part = shift;
my $period = shift;

for (my $curr_part = 0; $curr_part < $amount_partitions; $curr_part++) {
my $next_name = name_next_part($tables->{$table_name}->{'period'}, $curr_part);
my $found = 0;

foreach my $partition (sort keys %{$table_part}) {
if ($next_name eq $partition) {
syslog(LOG_INFO, "Next partition for $table_name table has already been created. It is $next_name");
$found = 1;
}
}

if ( $found == 0 ) {
syslog(LOG_INFO, "Creating a partition for $table_name table ($next_name)");
my $query = 'ALTER TABLE '."$db_schema.$table_name".' ADD PARTITION (PARTITION '.$next_name.
' VALUES less than (UNIX_TIMESTAMP("'.date_next_part($tables->{$table_name}->{'period'}, $curr_part).'") div 1))';
syslog(LOG_DEBUG, $query);
$dbh->do($query);
}
}
}

sub remove_old_partitions {
my $table_name = shift;
my $table_part = shift;
my $period = shift;
my $keep_history = shift;

my $curr_date = DateTime->now;
$curr_date->set_time_zone( $curr_tz );

if ( $period eq 'day' ) {
$curr_date->add(days => -$keep_history);
$curr_date->add(hours => -$curr_date->strftime('%H'));
$curr_date->add(minutes => -$curr_date->strftime('%M'));
$curr_date->add(seconds => -$curr_date->strftime('%S'));
}
elsif ( $period eq 'week' ) {
}
elsif ( $period eq 'month' ) {
$curr_date->add(months => -$keep_history);

$curr_date->add(days => -$curr_date->strftime('%d')+1);
$curr_date->add(hours => -$curr_date->strftime('%H'));
$curr_date->add(minutes => -$curr_date->strftime('%M'));
$curr_date->add(seconds => -$curr_date->strftime('%S'));
}

foreach my $partition (sort keys %{$table_part}) {
if ($table_part->{$partition}->{'partition_description'} <= $curr_date->epoch) {
syslog(LOG_INFO, "Removing old $partition partition from $table_name table");

my $query = "ALTER TABLE $db_schema.$table_name DROP PARTITION $partition";

syslog(LOG_DEBUG, $query);
$dbh->do($query);
}
}
}

sub name_next_part {
my $period = shift;
my $curr_part = shift;

my $name_template;

my $curr_date = DateTime->now;
$curr_date->set_time_zone( $curr_tz );

if ( $period eq 'day' ) {
my $curr_date = $curr_date->truncate( to => 'day' );
$curr_date->add(days => 1 + $curr_part);

$name_template = $curr_date->strftime('p%Y_%m_%d');
}
elsif ($period eq 'week') {
my $curr_date = $curr_date->truncate( to => 'week' );
$curr_date->add(days => 7 * $curr_part);

$name_template = $curr_date->strftime('p%Y_%m_w%W');
}
elsif ($period eq 'month') {
my $curr_date = $curr_date->truncate( to => 'month' );
$curr_date->add(months => 1 + $curr_part);

$name_template = $curr_date->strftime('p%Y_%m');
}

return $name_template;
}

sub date_next_part {
my $period = shift;
my $curr_part = shift;

my $period_date;

my $curr_date = DateTime->now;
$curr_date->set_time_zone( $curr_tz );

if ( $period eq 'day' ) {
my $curr_date = $curr_date->truncate( to => 'day' );
$curr_date->add(days => 2 + $curr_part);
$period_date = $curr_date->strftime('%Y-%m-%d');
}
elsif ($period eq 'week') {
my $curr_date = $curr_date->truncate( to => 'week' );
$curr_date->add(days => 7 * $curr_part + 1);
$period_date = $curr_date->strftime('%Y-%m-%d');
}
elsif ($period eq 'month') {
my $curr_date = $curr_date->truncate( to => 'month' );
$curr_date->add(months => 2 + $curr_part);

$period_date = $curr_date->strftime('%Y-%m-%d');
}

return $period_date;
}

sub delete_old_data {
$dbh->do("DELETE FROM sessions WHERE lastaccess < UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH)");
$dbh->do("TRUNCATE housekeeper");
$dbh->do("DELETE FROM auditlog_details WHERE NOT EXISTS (SELECT NULL FROM auditlog WHERE auditlog.auditid = auditlog_details.auditid)");
}

                  Comment

                  • steeladept
                    #34.1
                    steeladept commented
                    14-01-2019, 22:27
                    Editing a comment
                    It worked for me without the Key section you mention above, but that may have something to do with the minor changes between versions for all I know. Either way, glad it is working for you. The sub check_have_partition { ...} and the call to the check_have_partition section were the two major points it hung up on for me.
                  • rbeglinger
                    Junior Member
                    • Jan 2019
                    • 10
                    #35
                    It looks like the "working" was very loosely used. It does create the additional partitions, but the check to see if the partition already exists, doesn't seem to work properly.

                    Code:
                    sub create_next_partition {
    my $table_name = shift;
    my $table_part = shift;
    my $period = shift;

    for (my $curr_part = 0; $curr_part < $amount_partitions; $curr_part++) {
        my $next_name = name_next_part($tables->{$table_name}->{'period'}, $curr_part);
        my $found = 0;

        foreach my $partition (sort keys %{$table_part}) {
            if ($next_name eq $partition) {
                syslog(LOG_INFO, "Next partition for $table_name table has already been created. It is $next_name");
                $found = 1;
            }
        }

        if ( $found == 0 ) {
            syslog(LOG_INFO, "Creating a partition for $table_name table ($next_name)");
            my $query = 'ALTER TABLE '."$db_schema.$table_name".' ADD PARTITION (PARTITION '.$next_name.
                        ' VALUES less than (UNIX_TIMESTAMP("'.date_next_part($tables->{$table_name}->{'period'}, $curr_part).'") div 1))';
            syslog(LOG_DEBUG, $query);
            $dbh->do($query);
        }
    }
}
                    Expected behavior is that if the partition is already created, it would output the info to the syslog. Currently, it is not recognizing that the exist. I ran the script twice, both times it claimed it created the partitions. The second time, the partitions already existed.

                    Code:
                    Jan 14 14:12:42 mysql_zbx_part[6106]: Creating a partition for trends table (p2019_02)
Jan 14 14:12:42 mysql_zbx_part[6106]: Creating a partition for trends table (p2019_03)
Jan 14 14:12:42 mysql_zbx_part[6106]: Creating a partition for trends table (p2019_04)
Jan 14 14:12:42 mysql_zbx_part[6106]: Creating a partition for trends table (p2019_05)
Jan 14 14:12:42 mysql_zbx_part[6106]: Creating a partition for trends table (p2019_06)
Jan 14 14:12:42 mysql_zbx_part[6106]: Creating a partition for trends table (p2019_07)
Jan 14 14:12:42 mysql_zbx_part[6106]: Creating a partition for trends table (p2019_08)
Jan 14 14:12:42 mysql_zbx_part[6106]: Creating a partition for trends table (p2019_09)
Jan 14 14:12:42 mysql_zbx_part[6106]: Creating a partition for trends table (p2019_10)
Jan 14 14:12:42 mysql_zbx_part[6106]: Creating a partition for trends table (p2019_11)
.........
Jan 14 14:57:01 mysql_zbx_part[9022]: Creating a partition for trends table (p2019_02)
Jan 14 14:57:01 mysql_zbx_part[9022]: Creating a partition for trends table (p2019_03)
Jan 14 14:57:01 mysql_zbx_part[9022]: Creating a partition for trends table (p2019_04)
Jan 14 14:57:01 mysql_zbx_part[9022]: Creating a partition for trends table (p2019_05)
Jan 14 14:57:01 mysql_zbx_part[9022]: Creating a partition for trends table (p2019_06)
Jan 14 14:57:01 mysql_zbx_part[9022]: Creating a partition for trends table (p2019_07)
Jan 14 14:57:01 mysql_zbx_part[9022]: Creating a partition for trends table (p2019_08)
Jan 14 14:57:01 mysql_zbx_part[9022]: Creating a partition for trends table (p2019_09)
Jan 14 14:57:01 mysql_zbx_part[9022]: Creating a partition for trends table (p2019_10)
Jan 14 14:57:01 mysql_zbx_part[9022]: Creating a partition for trends table (p2019_11)
                    I suspect this will lead to old partitions not being removed as the script won't see they exist.

                    Hopefully someone with strong Perl and MySQL skills will be able to help with this.

                      Comment

                      • steeladept
                        #35.1
                        steeladept commented
                        14-01-2019, 23:38
                        Editing a comment
                        Can you verify multiple partitions are actually there? IIRC, I found that it said that, but just skipped over it for me. That is from a while ago though, so I may be mistaken.
                      • rbeglinger
                        Junior Member
                        • Jan 2019
                        • 10
                        #36
                        The partitions show up in the file system:

                        Code:
                        # ll /var/lib/mysql/zabbix/trends#P*
-rw-r-----. 1 mysql mysql 14680064 Jan 14 15:01 /var/lib/mysql/zabbix/trends#P#p2019_01.ibd
-rw-r-----. 1 mysql mysql   114688 Jan 14 14:14 /var/lib/mysql/zabbix/trends#P#p2019_02.ibd
-rw-r-----. 1 mysql mysql   114688 Jan 14 14:14 /var/lib/mysql/zabbix/trends#P#p2019_03.ibd
-rw-r-----. 1 mysql mysql   114688 Jan 14 14:14 /var/lib/mysql/zabbix/trends#P#p2019_04.ibd
-rw-r-----. 1 mysql mysql   114688 Jan 14 14:14 /var/lib/mysql/zabbix/trends#P#p2019_05.ibd
-rw-r-----. 1 mysql mysql   114688 Jan 14 14:14 /var/lib/mysql/zabbix/trends#P#p2019_06.ibd
-rw-r-----. 1 mysql mysql   114688 Jan 14 14:14 /var/lib/mysql/zabbix/trends#P#p2019_07.ibd
-rw-r-----. 1 mysql mysql   114688 Jan 14 14:14 /var/lib/mysql/zabbix/trends#P#p2019_08.ibd
-rw-r-----. 1 mysql mysql   114688 Jan 14 14:14 /var/lib/mysql/zabbix/trends#P#p2019_09.ibd
-rw-r-----. 1 mysql mysql   114688 Jan 14 14:14 /var/lib/mysql/zabbix/trends#P#p2019_10.ibd
-rw-r-----. 1 mysql mysql   114688 Jan 14 14:14 /var/lib/mysql/zabbix/trends#P#p2019_11.ibd
                        And the MySQL DB shows the partitions as well.

                        Code:
                        mysql> SELECT TABLE_NAME, PARTITION_NAME from INFORMATION_SCHEMA.PARTITIONs where (table_name)='trends';
+------------+----------------+
| TABLE_NAME | PARTITION_NAME |
+------------+----------------+
| trends     | p2019_01       |
| trends     | p2019_02       |
| trends     | p2019_03       |
| trends     | p2019_04       |
| trends     | p2019_05       |
| trends     | p2019_06       |
| trends     | p2019_07       |
| trends     | p2019_08       |
| trends     | p2019_09       |
| trends     | p2019_10       |
| trends     | p2019_11       |
+------------+----------------+
11 rows in set (0.00 sec)
                        To me it looks like they exist.

                          Comment

                          • steeladept
                            #36.1
                            steeladept commented
                            14-01-2019, 23:55
                            Editing a comment
                            Yes, but they are not duplicates. It is the next 11 months of partitions. I don't see any p2019_01 multiple times, for example. That is the same way my system works.
                            Last edited by steeladept; 15-01-2019, 00:01.
                          • rbeglinger
                            Junior Member
                            • Jan 2019
                            • 10
                            #37
                            Correct, I suspect that the secondary create is failing because the partition already exists. Instead, it should see that it exists and not even attempt to create it. Thanks for your time looking into this.

                              Comment

                              • moonb0w
                                Junior Member
                                • Feb 2019
                                • 5
                                #38
                                I tried to partitioned already running zabbix server but I stuck on this error "Table has no partition for value 1547596818". I followed the doc on zabbix wiki.
                                Any clue?

                                Code:
                                MariaDB [zabbix]> ALTER TABLE `history` PARTITION BY RANGE (clock) (PARTITION p2019_01_15 VALUES LESS THAN (UNIX_TIMESTAMP("2019-01-16 00:00:00")) ENGINE = InnoDB);

ERROR 1526 (HY000): Table has no partition for value 1547596818
                                Code:
                                MariaDB [zabbix]> SELECT FROM_UNIXTIME(MIN(clock)) FROM `history`;
+---------------------------+
| FROM_UNIXTIME(MIN(clock)) |
+---------------------------+
| 2019-01-15 08:00:14       |
+---------------------------+
1 row in set (2.026 sec)

                                  Comment

                                  • ingus.vilnis
                                    Senior Member
                                    Zabbix Certified Trainer
                                    Zabbix Certified SpecialistZabbix Certified Professional
                                    • Mar 2014
                                    • 908
                                    #39
                                    It is because time value 1547596818 equals 2019-01-16T00:00:18 UTC. Therefore you need the command to include that day as well.
                                    Code:
                                     
 ALTER TABLE `history` PARTITION BY RANGE (clock) (PARTITION p2019_01_16 VALUES LESS THAN (UNIX_TIMESTAMP("2019-01-17 00:00:00")) ENGINE = InnoDB);
                                    However, you might want to partition multiple days at once as per the doc, otherwise you will end up having all your current history in one partition and that should not be what you really want.

                                      Comment

                                      • moonb0w
                                        Junior Member
                                        • Feb 2019
                                        • 5
                                        #40
                                        thanks for replying
                                        so I need to do in descending order rather than ascending?

                                          Comment

                                          • ingus.vilnis
                                            Senior Member
                                            Zabbix Certified Trainer
                                            Zabbix Certified SpecialistZabbix Certified Professional
                                            • Mar 2014
                                            • 908
                                            #41
                                            The list goes from oldest date up till today. Ascending that is. You were missing entry for the last day (today).
                                            Zabbix Community Area
                                            https://zabbix.org/wiki/Docs/howto/mysql_partitioning
                                            Join the friendly and open Zabbix community on our forums and social media platforms.

                                              Comment

                                              • moonb0w
                                                Junior Member
                                                • Feb 2019
                                                • 5
                                                #42
                                                Originally posted by ingus.vilnis
                                                The list goes from oldest date up till today. Ascending that is. You were missing entry for the last day (today).
                                                https://zabbix.org/wiki/Docs/howto/mysql_partitioning
                                                I'm using the perl script partitioning, if I have items with :
                                                Code:
                                                history storage period: 30d
trend storage period: 365d
                                                should I edit these lines to match with my items?

                                                Code:
                                                my $tables = {    'history' => { 'period' => 'day', 'keep_history' => '30'},
'history_log' => { 'period' => 'day', 'keep_history' => '30'},
'history_str' => { 'period' => 'day', 'keep_history' => '30'},
'history_text' => { 'period' => 'day', 'keep_history' => '30'},
'history_uint' => { 'period' => 'day', 'keep_history' => '30'},
'trends' => { 'period' => 'month', 'keep_history' => '2'},
'trends_uint' => { 'period' => 'month', 'keep_history' => '2'},
                                                thanks

                                                  Comment

                                                  • ingus.vilnis
                                                    Senior Member
                                                    Zabbix Certified Trainer
                                                    Zabbix Certified SpecialistZabbix Certified Professional
                                                    • Mar 2014
                                                    • 908
                                                    #43
                                                    Yes, set values for trends and trends_uint tables to 12 months before running the script. Otherwise all your trend values older than 2 months will be lost.

                                                      Comment

                                                      • moonb0w
                                                        Junior Member
                                                        • Feb 2019
                                                        • 5
                                                        #44
                                                        thanks
                                                        that info should be added to the wiki site, so new zabbix user won't confused why their data keep disappearing even though the item config is set to 90d

                                                          Comment

                                                          • ingus.vilnis
                                                            Senior Member
                                                            Zabbix Certified Trainer
                                                            Zabbix Certified SpecialistZabbix Certified Professional
                                                            • Mar 2014
                                                            • 908
                                                            #45
                                                            By no means is that manual for new users anyways. Moreover on the live production. You got to understand the whole concept, do it few times on some test system before going into production.

                                                              Comment

                                                              Previous 1 2 3 4 template Next
                                                              Working...