Shulou(Shulou.com)06/01 Report--

{$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"

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.