Method 2 for dealing with Zabbix historical database-partitioning with MySQL table
{$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'} 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% masking% 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% masking% 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% Yee% 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 ('% Ymuri% mmi% 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 ('% Ymuri% mmi% 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 ('% Ymuri% mmi% 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)");}
An error may be reported during execution
Can't locate DateTime.pm in @ INC (@ INC contains: / usr/local/lib64/perl5 / usr/local/share/perl5 / usr/lib64/perl5/vendor_perl / usr/share/perl5/vendor_perl / usr/lib64/perl5/ usr/share/perl5.) At zabbix_mysql_partition.pl line 7.BEGIN failed--compilation aborted at zabbix_mysql_partition.pl line 7.
Solution:
Yum-y install perl-DateTime
Execute perl zabbix_mysql_partition.pl
This script writes the log to syslog, which can be viewed by / var/log/messages
And then put it into crontab to execute.
0 23 * / usr/bin/perl / opt/script/zabbix_mysql_partition.pl
Four summaries
Use Partition consideration
When creating a new partition, make sure that the partition range is not out of bounds, otherwise an error will be returned
An MySQL table is either completely partitioned or not partitioned at all.
Increase the value of open_files_limit when trying to partition a large number of tables
None of the partitioned tables supports foreign keys. You need to delete foreign keys before partitioning.
Partitioned tables do not support query caching
Use zoning recommendations
Use MySQL5.5 or later. These versions optimize table partitioning and run more stably.
Consider using XtraDB instead of pure InnoDB.XtraDB included in MariaDB and Percona
TokuDB is not suitable for Zabbix and does not seem to run well when executing query tables
Optimize, re-optimize, adjust the configuration parameters
Reference documentation:
Https://www.zabbix.org/wiki/Docs/howto/mysql_partitioning
Https://www.zabbix.org/wiki/Docs/howto/mysql_partition
Http://dev.mysql.com/doc/refman/5.6/en/symbolic-links-to-tables.html
Http://dev.mysql.com/doc/refman/5.6/en/blackhole-storage-engine.html
