Ad Widget

**ingus.vilnis** · 24-10-2018, 11:48

Hi,
Can you confirm that there is an active database which is not currently partitioned but you are moving to another setup plus upgrade to 4.0 LTS?

The main problem with already big databases to be partitioned is the time needed for the process, especially if you are running live and can't afford downtime. If that is the case then what is the size of the current DB and what version of Zabbix are you on?

If you do a clean install then this is a task for like 10 minutes.

As for the future design - the specs for the DB server might be a bit too shy for 1200 vps. More cores and RAM would be great. And what DB engine and version do you plan to use?

**steeladept** · 24-10-2018, 14:34

Thank you for the response - yes, this is a clean install. I will be setting up a parallel 4.0 LTS instance and moving our existing machines to it, then adding additional systems as needed. And as an added note, we are not migrating any historical data. As I move the machines, they will all be new adds so this can be considered a complete new build.

As for resources, it may be low. It is a VMware VM, so we can hot-add resources as needed, and there are plenty available. That said, if it makes sense, I can add some more resources now. That was just to start so we didn't overprovision the VM and waste resources.

Finally, with regard to your question on the DB Specs - I currently have a default install of MySQL 8.0 using the INNODB engine with the schema added, but no data. Unless there is a very compelling reason to move to PostgreSQL, I would prefer to stick with that simply because, while I don't know it well, I still know it better than PostgreSQL.

**ingus.vilnis** · 24-10-2018, 14:49

Ok, for clean install it is very easy.

Here is a good manual for it. http://zabbix.org/wiki/Docs/howto/mysql_partitioning
It consists of several parts and might be confusing because you have to make decision whether you are going to do the partitioning with MySQL stored procedures or using external Perl script. My personal favorite is the Perl script because it has been flawlessly working for years at numerous locations. Internal procedures are harder to configure and debug. Read through this one and try to understand first what is going on. Once done that, I can point out what exact steps are important for new setups.

What exact flavor and version of MySQL do you plan to use? And why?

**steeladept** · 24-10-2018, 14:58

Thanks, and sorry, I added the MySQL info afterwords, when I realized you asked and I didn't write it in. The reason why is simply familiarity compared to PostgreSQL. I used the Oracle vanilla version of MySQL instead of MariaDB, but there was no particular reason for that, other than the Zabbix page mentioned MySQL 8.0 and I wasn't sure the MariaDB equivalent (and didn't bother looking it up).

**steeladept** · 24-10-2018, 15:00

Used wrong box - Doh! Anyway, if you have a particular suggestion with good reason, please feel free to let me know. If there is good reason to change, now is the time to do so. And thanks again.

**ingus.vilnis** · 24-10-2018, 15:08

I had to make the same choice recently on one setup and finally settled for Percona Server 5.7. while MySQL 8.0 was also an option. The benchmark tests did not show any great performance difference between those two so the decision was made to go with time proven solutions. As far as I know MySQL 8 works good with Zabbix but I have no comments on the nuances of partitioning.

**steeladept** · 24-10-2018, 15:50

So I reviewed the links and think I understand what is going on. It is basically taking the high volume tables, and creating a new table for each day, or each month, or whatever, and deleting or archiving (depending on configuration) the older partition data, largely making the less efficient housekeeping tasks unnecessary for those tables. Do I have it right?

Assuming I do, I have a few questions.

1) Has this been used with MySQL 8.0, or will there be a lot of troubleshooting? I see a lot of things mentioning version 5.6+, but as I understand, 8.0 has a lot built in, partitioning being one of them. So I don't know if it is a problem, when it says: "SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = 'partition'; to confirm partitioning support is enabled." and I get "Empty set".

2) Do I have to run the ALTER TABLE procedures AND create the scripts to run via cron, or is it just run the scripts (since there is no existing data)?

3) Am I effectively turning off all housekeeper activities, or just some of them as listed in the GUI?

**ingus.vilnis** · 24-10-2018, 16:01

Yes, you got it correct. Housekeeping at nvps over ~ 500 can't keep up with deleting the oldest data and is slowing down the whole database.

One important aspect to consider. When you have housekeeper running (partitioning not configured) you can individually control for how long time (days, months) you keep data for each item individually. For some you don't care, delete them after a week and save disk, for some you want to have the history for years. Housekeeper allows that. Partitioning not. The only thing you can control is the retention by data type (numeric, texts, log). Keep that in mind.

To directly answer your questions:
1) I have no idea about MySQL 8.0. Someone else could comment here, sorry. On that one Percona 5.7 I have this available as ACTIVE but I have suspicion that you should be fine also with your setup.

2) You have to both ALTER table to create the partitions first and then create scripts to run this on nightly basis.

3) You turn off Housekeeper for Hisotry and Trends only. Leave all other boxes checked in that GUI page.

Tomorrow I might post you exact commands and tips how to go with partitioning on empty DB.

**steeladept** · 24-10-2018, 16:12

Thanks for the help. That makes sense. I will see what I can get done today and check back tomorrow. At very least, since it is new and easily rebuilt (got to love snapshots!), I will try to partition it and set the script up - see what errors out.

**ingus.vilnis** · 24-10-2018, 16:20

Ok, here is a quick how to.

Create a separate user to run the script. Will be easier to debug and more restricted permissions.

Code:

mysql> GRANT SELECT, DELETE, DROP, ALTER ON zabbix.* TO 'zbx_part'@'localhost' identified by '<Zabbix partitioning DB user password>';

Partition history and trends tables all one by one.

Code:

mysql> use zabbix; Database changed

mysql> ALTER TABLE `history` PARTITION BY RANGE ( clock) (PARTITION p2018_10_24 VALUES LESS THAN (UNIX_TIMESTAMP("2018-10-25 00:00:00") div 1) ENGINE = InnoDB);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `history_log` PARTITION BY RANGE ( clock) (PARTITION p2018_10_24 VALUES LESS THAN (UNIX_TIMESTAMP("2018-10-25 00:00:00") div 1) ENGINE = InnoDB);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `history_str` PARTITION BY RANGE ( clock) (PARTITION p2018_10_24 VALUES LESS THAN (UNIX_TIMESTAMP("2018-10-25 00:00:00") div 1) ENGINE = InnoDB);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `history_text` PARTITION BY RANGE ( clock) (PARTITION p2018_10_24 VALUES LESS THAN (UNIX_TIMESTAMP("2018-10-25 00:00:00") div 1) ENGINE = InnoDB);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `history_uint` PARTITION BY RANGE ( clock) (PARTITION p2018_10_24 VALUES LESS THAN (UNIX_TIMESTAMP("2018-10-25 00:00:00") div 1) ENGINE = InnoDB);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `trends` PARTITION BY RANGE ( clock) (PARTITION p2018_10 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-01 00:00:00") div 1) ENGINE = InnoDB);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `trends_uint` PARTITION BY RANGE ( clock) (PARTITION p2018_10 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-01 00:00:00") div 1) ENGINE = InnoDB);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> quit
Bye

Create the partitioning script.
Pay attention to password, heep_history days and correct timezone.

Code:

# vi /etc/zabbix/zabbix_partitioning.pl

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)");
}

Optionally set permissions for partitioning script to be accessed by root user only.

Code:

# chmod 700 /etc/zabbix/zabbix_partitioning.pl

Install additional packages for running functions used Perl partitioning script.

Code:

yum install perl-CPAN perl-DateTime-TimeZone perl-DBD-MySQL perl-Sys-Syslog

Add cron job and set it run twice a night (in unlikely case it fails the first time)

Code:

# vi /etc/cron.d/zabbix-mysql-partitioning

Code:

# Zabbix daily table partitioning
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
10 23 * * * root perl /etc/zabbix/zabbix_partitioning.pl 1>/var/log/zabbix/zabbix_partitioning.log  2>&1
10 2 * * * root perl /etc/zabbix/zabbix_partitioning.pl 1>/var/log/zabbix/zabbix_partitioning.log  2>&1

Disable Zabbix internal Housekeeper process for History and Trends tables using web interface Administration → General → Housekeeping (in top right dropdown) menu.

And do ls-lah /var/lib/mysql/zabbix/history* or wherever your data files are to see if there are new files for every day/month.

Profit.

I'm out for the day.

**steeladept** · 24-10-2018, 18:02

Thank you, that is fantastic. And proves that I was on the right track. The only major differences between what I thought I should do and what you have is this:

1) From the reading I thought the following tables should also be partitioned, yet you don't. Can you explain why and when I would want to do so, if ever?
service_alarms
events
alerts
acknowledges
auditlogs

2) I would have missed some of the perl dependencies, so that yum line helped prevent that troubleshooting a lot.

3) I would not have though running the cron job twice would be a good idea. Doesn't that create a lot of empty partitions? And if it misses one day (but not the next) would that have a significant effect on the table performance? (Though probably want some sort of table monitoring to ensure they do generally create new partitions somehow - but that is the next step after partitioning is setup and running regularly).

One other question did rise during this process though. Is this something I should consider for the Proxy Databases as well, or are they more or less pass-through storage only, and do not need partitioned? I am guessing they are pass-through storage only, and this only applies to the server, but want to confirm.

**ingus.vilnis** · 24-10-2018, 19:37

Yes, you are definitely going the right direction and your questions are also very logical.

1) Partitioning for these tables was relevant for Zabbix 2.0 but for newer Zabbixes it is not needed, moreover you can create whole lot of new problems with this. Leave cleanup of these tables to the Housekeeper process. You probably noticed that in the GUI settings you left these ones up to housekeeper. Yet this little detail about these tables was not so obvious from that zabbix.org article and I have noticed many people running into problems by executing those commands not thinking what they do and not reading though the whole article before doing.

2) Yes, the perl dependencies are not obvious. You would run into errors when executing the cron script the first time. Saved you some hours googling.

3) I have never seen the script failing really. Running it twice will not create nor drop partitions twice. Run the script manually every 10 seconds and you will see no difference. The script is really needed once a day only. Later you will notice that the data will be actually deleted by the execution which happens first after midnight. Regarding monitoring you might want to put a Zabbix log item for that /var/log/zabbix/zabbix_partitioning.log file and alert if any lines appear there.

Proxies, no, you don't want to partition them, really, you don't. And actually there is only table "proxy_history"table which stores the values for no longer than 1 hour if proxy is not configured to buffer them for longer. Housekeeper on the proxy takes care of that process. Unless you see real performance problems, that is not something you should worry about.

**kloczek** · 25-10-2018, 00:12

Originally posted by ingus.vilnis

Yes, you are definitely going the right direction and your questions are also very logical.

1) Partitioning for these tables was relevant for Zabbix 2.0 but for newer Zabbixes it is not needed, moreover you can create whole lot of new problems with this. Leave cleanup of these tables to the Housekeeper process. You probably noticed that in the GUI settings you left these ones up to housekeeper. Yet this little detail about these tables was not so obvious from that zabbix.org article and I have noticed many people running into problems by executing those commands not thinking what they do and not reading though the whole article before doing.

Between zabbix 2.0.x and 4.0.0 nothing has changed in history*, trends* tables and maintenance those tables contents. Last change (add ns column in history*) has been introduced in 2.0.
Sorry to say this straight but you are completely wrong about claim that partitioning is not needed now.
I case rest of your points you are wrong as well.
Even on proxies it would be better to not waste time on delete queries and do only few IOs to drop oldest proxy_history table partition (on proxy all data are kept in single table as strings). Proxy keeps kind of cyclic buffer data and to maintain content of such buffer partitioning would be the best.

**ingus.vilnis** · 25-10-2018, 08:14

Originally posted by kloczek

Between zabbix 2.0.x and 4.0.0 nothing has changed in history*, trends* tables and maintenance those tables contents. Last change (add ns column in history*) has been introduced in 2.0.
Sorry to say this straight but you are completely wrong about claim that partitioning is not needed now.
I case rest of your points you are wrong as well.
Even on proxies it would be better to not waste time on delete queries and do only few IOs to drop oldest proxy_history table partition (on proxy all data are kept in single table as strings). Proxy keeps kind of cyclic buffer data and to maintain content of such buffer partitioning would be the best.

Please clarify whether with your statement you are referring to the history and trends tables or service_alarms, events, alerts, acknowledges and auditlogs when the quoted reply was specifically for the latter?

Partitioning for proxy - too complex when one is having the first steps in partitioning the DB. Not at 1200 nvps shared among server and 5 proxies. One proxy alone doing 1200 nvps is another story but not in this particular use case.

Ad Widget

MySQL Partitioning recommendations request

MySQL Partitioning recommendations request

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment