MySQL Partitioning recommendations request

[kloczek]

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.

On the proxy it is necessary to partition only one table (much less than on server).
Current maintenance is done every hour so it is obvious that partitioning should on hour base.
It is possible to even implement semi-partitioning in case sqlite db backend by create new file with proxy_history table content for each hour data.
Currently used number of hours data to be kept on the proxy can be number of partitions which needs to be kept.
All what is needed currently to add partitioning on the proxy is add call one alter table per hour with create new partition and another one with drop oldest partition. Syntax of such queries is not part of the SQL92 spec so those operations needs to be in db backed type dependent code. For each db backend it should be not more than 10 additional lines of code to use partitioning instead delete queries.
Ergo: additional complexity is close to null.

[steeladept]

For our environment, our 5 proxies are mainly for remote data collection in case of down network far more than they are for performance enhancements. So unless there is a good reason to do so now; I will not bother with the proxy partitioning unless/until I find a need for it. However, it is good to know that some performance gains can be achieved using that if there is a bottleneck at the proxy for any reason. Is the partitioning of that table similar to the main SQL database (with appropriate date changes, of course, and assuming it is MySQL as well) -

#ALTER TABLE `proxy_history` PARTITION BY RANGE ( clock) (PARTITION p2018_10_24 VALUES LESS THAN (UNIX_TIMESTAMP("2018-10-25 00:00:00") div 1) ENGINE = InnoDB);

And edit the script to account for that table on an hourly change basis? Something like so -

##!/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 = {  'proxy_history' => { 'period' => 'hour', 'keep_history' => '48'}, };  
my $amount_partitions = 10;

my $curr_tz = 'Etc/UTC';

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

[kloczek]

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.

To complex .. because?
Look I have one argument: proxy is using only one table maintained on hourly base and what exactly needs to be added/modified to have this single table used on proxy maintenance done over partitioning this table.
On you side is only empty claim "it is to complex". 1.2knvps for proxy it is NOTHING.
If in your stack 1.2knvps proxy it is the problem we can talk about this but such subject will be completely not related to on what we are discussing.
Try to measure real storage IO bandwidth created by such traffic flowing over proxy. Show me your db backend cfg ..

Discussion it is king of chivalry where both sides are using facts.
Please use facts and not your or someone else opinion.

[steeladept]

I am a little confused, here kloczek. Ingus hasn't responded to the Too Complex question, but I did ask questions and your answer is to explain that it isn't complex without answering any of the questions on if I am correct in understanding how simple it is. You are looking for facts as to why he considers it complex, I am just trying to learn what it takes to implement it, in case I or another wants to do so now or in the future.

As for the complexity question, It is most likely as simple as unnecessary in my use case. It may also be easy enough to implement anyway, as you imply. Either way, I need to understand the correct way to do this or it will just be a waste of time to discuss the merits as it will still not occur.

[kloczek]

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.

On the proxy service_alarms, events, alerts, acknowledges and auditlogs tables are not used at all.
On adding data ti proxy_history table nothing more in the DB needs to be locked. DB backend speed generally depends on storage IO latency. Delete oldest data on every hour delete oldest data locks this table.
Partitioning and dropping oldest hourly partition allows shorten period of time when any other locks on proxy_history table will be active.

[steeladept]

Let me know if this should go into a different thread, but since it is related, I will start it here.

The script pointed to is not working - it ALWAYS is throwing the error: Your installation of MySQL does not support table partitioning.

I believe this is a minor change due to the perl script being written against MySQL 5.x and I am trying to run it against MySQL 8.x. However, I do not know Perl well enough to troubleshoot and resolve this. So while I am going to try to learn, on the fly, I am also posting this here for any who might be in a better position to help resolve this.

Thanks again

[kloczek]

Just stop use an external script.Whole partitioning can be done in pure SQL + SQL stored procedures, and scheduling using SQ events.

https://zabbix.org/wiki/Docs/howto/mysql_partition

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

[ingus.vilnis]

This is somewhat uncommon to have the "partition" plugin missing for MySQL although it is clearly documented that for 8.0 partitioning is available for Innodb engine. https://dev.mysql.com/doc/refman/8.0...titioning.html
You may check what "SHOW ENGINES" and "SHOW PLUGINS" commands return. As I said before there might be some differences in these outputs for MySQL 8.0 compared to 5.7 which have not tested. I wonder how you were able to do the "ALTER TABLE..." commands without errors. Do you see the partitions created as files for history* and trends* tables in your MySQL data directory?

[steeladept]

That is what I thought too. The tables are the same, so the ALTER TABLE commands worked fine. I did them directly for the first time. Show Engines shows it to default to InnoDB with all other engines available (except the Federated engine) and the Show PLUGINS command returns a huge list of stuff - all active, except for the Federated Storage Engine. As for the partitions, yes, they are created. The only issue seems to be when the script tries to do so. I set the script to run as root, so I don't think it is a permissions issue. The account used in the script is valid and works. It is just when I run the script that it throws the error.

[ingus.vilnis]

Now few words about why, how I suggest things here, complexity and other stuff.

As a contributor to this community forum I feel responsible for the advice I am giving to people. Although there is no liability tied to the solutions offered, I personally would not feel comfortable if by my advice things get damaged in some setups even if execution of commands on those are pure responsibility of respective admins.

If we look at this particular case then 1000 hosts @1200 nvps is not a home office hobby project anymore. The guy asking for advice supposedly has a manager who expects stuff to just work and will not be very supportive if things go wrong. Moreover if in the original request there is a line that this is the first attempt to do something so fundamental as partitioning. Do this wrong on the day 1 and this installation and company may suffer for the next years for sure. Therefore extra caution must be taken when evaluating the risks associated with the particular setup to first make the experience as smooth as possible and not overwhelm the guy with too many new things. And even then it can go not as expected since not clear if the partitioning really works on that 8.0 or not.

Having said that in the risk assessment goes also the fact that every additional step implemented into this setup adds to the overall complexity of the solution. The complexity of partitioning the proxies for instance. Introduce that and a whole plethora of problems may arise on the proxy. You have to get the partitions right. Have to remember to disable the housekeeper. Have to properly schedule the deletion. May there be any index changes upon next major upgrade then the upgrade will be unsuccessful and another day of downtime wasted trying to troubleshoot that one. Some may say that I am overestimating the possible problems here, that even then dropping the database and creating a new one takes minutes. Yes, that is all true. But will one have the know how to get all of this right when it takes years of advanced work and research of Zabbix to do this whole thing? In the name of few saved IOps? Again this is not something I would ever consider needed at 1200nvps distributed among server and 5 proxies. Oh, and yes, you have to do this un 5 proxies if you are not on some configuration management tool and even then the DB changes would need to be manual in typical cases.

By the way, if some do not know this - partitioning is not officially supported by Zabbix company and developers. Yes, there is no other way around data deletion on larger setups but in terms of database structure and integrity every such modification puts you on your own responsibility where you can't blame Zabbix for broken upgrade experiences because you partitioned some tables. Particularly if you went beyond history and trends then you are completely on your own. There are initiatives to make partitioning safe and supported out of the box but it is by far not simple to properly develop this right across all DB engines and versions.

And to reply on some of the comments in between I can just say that they were purely taken out of context without any relation to the topic on what the answers were given. Partitioning of service_alarms, events, alerts, acknowledges and auditlogs is not recommended and there were changes in between the versions. You can't anymore assure data integrity if you partition these. The partitioning suggestions were made only for history and trends tables. And how was that later linked to the proxies was just misunderstanding of communication in my view.

[DmitryL]

Note:
As of MySQL 5.7.17, the generic partitioning handler in the MySQL server is deprecated and is removed in MySQL 8.0, when the storage engine used for a given table is expected to provide its own (“native”) partitioning handler. Currently, only the InnoDB and NDB storage engines do this.
So as far for a perl script, sub check_have_partition is not needed at all.

[ingus.vilnis]

Interesting. So we remove that sub check_have_partition portion from the script and go from there?

[DmitryL]

Must admit that I was too lazy to test it further on with the script, but works manually.

sub check_have_partition { my $result = 0; # MySQL 5.5 my $sth = $dbh->prepare(qq{SELECT variable_value FROM information_schema.global_variables WHERE variable_name = 'have_partitioning'}); # MySQL 5.6 #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(); # MySQL 5.5 return 1 if $row eq 'YES'; # MySQL 5.6 #return 1 if $row eq 'ACTIVE'; }

Since you won't find partition plugin in 8.0 it will always return 0 and later in the script and later it fails with:

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

P.S. CODE tag is broken in forum...
P.P.S. Quote doesn't help

[ingus.vilnis]

Ok, good to know. The zabbix.org post must be updated then.

[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.

[ingus.vilnis]

There was one function you need to remove from the script, namely

 
 sub check_have_partition { 	my $result = 0; # MySQL 5.5 	my $sth = $dbh->prepare(qq{SELECT variable_value FROM information_schema.global_variables WHERE variable_name = 'have_partitioning'}); # MySQL 5.6 	#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();  # MySQL 5.5 	return 1 if $row eq 'YES'; # MySQL 5.6 	#return 1 if $row eq 'ACTIVE'; }

After that the described behavior is exactly as expected. The script is supposed to create 10 partitions ahead and drop any older than you specified with those "keep_history" variables which are pretty straightforward.

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.

And please check that you have your time zone set correct in order to have the partitions divided exactly at midnight.