Mysql优化之Zabbix分区优化

使用zabbix最大的瓶颈在于数据库，维护好zabbix的数据存储，告警，就能很好地应用zabbix去构建监控系统。目前zabbix的数据主要存储在history和trends的2个表中，随着时间的推移，这两个表变得非常大，性能会非常差，影响监控的使用。对mysql进行调优，能够极大的提升zabbix的性能，本文采用对mysql进行分区的方法进行调优。

原理

对zabbix中的history和trends等表进行分区，按日期进行分区，每天一个，共保留90天分区。

操作详细步骤

操作影响： 可以在线操作，mysql的读写变慢，zabbix性能变慢，影响时间根据数据的小而变化，一般在2个小时左右。

第一步

登录zabbix server的数据库，统一mysql的配置

cat > /etc/my.cnf<<eof
[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
default-storage-engine = innodb
collation-server = utf8_general_ci
init-connect = 'set names utf8'
character-set-server = utf8
symbolic-links=0
max_connections=4096
innodb_buffer_pool_size=12g
max_allowed_packet = 32m
join_buffer_size=2m
sort_buffer_size=2m 
query_cache_size = 64m  
query_cache_limit = 4m  
thread_concurrency = 8
table_open_cache=1024
innodb_flush_log_at_trx_commit = 0

long_query_time = 1
log-slow-queries =/data/mysql/mysql-slow.log 

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#[mysql]
#socket=/data/mysql/mysql.sock
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
eof

注意：一定要修改innodb_buffer_pool_size=物理内存的1/3

第二步

先确认zabbix的版本，本操作zabbix的版本一定要大于3.2.0。小于3.2的版本不能安装此操作，线上默认是zabbix-3.2.6。

a、 导入存储过程

#cat partition.sql
delimiter $$
create procedure `partition_create`(schemanamevarchar(64), tablename varchar(64), partitionname varchar(64), clock int)
begin
    /*
     schemaname = the db schema in which to make changes
     tablename = the table with partitions to potentially delete
     partitionname = the name of the partition to create
    */
    /*
     verify that the partition does not already exist
    */

    declare retrows int;
    select count(1) into retrows
    from information_schema.partitions
    where table_schema = schemaname and table_name = tablename andpartition_description >= clock;

    if retrows = 0 then
        /*
          1. print a messageindicating that a partition was created.
          2. create the sql to createthe partition.
          3. execute the sql from #2.
        */
        select concat( "partition_create(", schemaname, ",",tablename, ",", partitionname, ",", clock, ")" )as msg;
        set @sql = concat( 'alter table ', schemaname, '.', tablename, ' addpartition (partition ', partitionname, ' values less than (', clock, '));' );
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;
    end if;
end$$
delimiter ;

delimiter $$
create procedure `partition_drop`(schemanamevarchar(64), tablename varchar(64), delete_below_partition_date bigint)
begin
    /*
      schemaname = the db schema in which tomake changes
     tablename = the table with partitions to potentially delete
     delete_below_partition_date = delete any partitions with names that aredates older than this one (yyyy-mm-dd)
    */
    declare done int default false;
    declare drop_part_name varchar(16);

    /*
     get a list of all the partitions that are older than the date
     in delete_below_partition_date. all partitions are prefixed with
      a "p", so use substring toget rid of that character.
    */
    declare mycursor cursor for
        select partition_name
        from information_schema.partitions
        where table_schema = schemaname and table_name = tablename andcast(substring(partition_name from 2) as unsigned) <delete_below_partition_date;
    declare continue handler for not found set done = true;

    /*
     create the basics for when we need to drop the partition. also, create
     @drop_partitions to hold a comma-delimited list of all partitions that
     should be deleted.
    */
    set @alter_header = concat("alter table ", schemaname,".", tablename, " drop partition ");
    set @drop_partitions = "";

    /*
     start looping through all the partitions that are too old.
    */
    open mycursor;
    read_loop: loop
        fetch mycursor into drop_part_name;
        if done then
            leave read_loop;
        end if;
        set @drop_partitions = if(@drop_partitions = "",drop_part_name, concat(@drop_partitions, ",", drop_part_name));
    end loop;
    if @drop_partitions != "" then
        /*
          1. build the sql to drop allthe necessary partitions.
          2. run the sql to drop thepartitions.
          3. print out the tablepartitions that were deleted.
        */
        set @full_sql = concat(@alter_header, @drop_partitions, ";");
        prepare stmt from @full_sql;
        execute stmt;
        deallocate prepare stmt;

        select concat(schemaname, ".", tablename) as `table`,@drop_partitions as `partitions_deleted`;
    else
        /*
          no partitions are beingdeleted, so print out "n/a" (not applicable) to indicate
          that no changes were made.
        */
        select concat(schemaname, ".", tablename) as `table`,"n/a" as `partitions_deleted`;
    end if;
end$$
delimiter ;


delimiter $$
create procedure`partition_maintenance`(schema_name varchar(32), table_name varchar(32),keep_data_days int, hourly_interval int, create_next_intervals int)
begin
    declare older_than_partition_date varchar(16);
    declare partition_name varchar(16);
    declare old_partition_name varchar(16);
    declare less_than_timestamp int;
    declare cur_time int;

    call partition_verify(schema_name,table_name, hourly_interval);
    set cur_time = unix_timestamp(date_format(now(), '%y-%m-%d 00:00:00'));

    set @__interval = 1;
    create_loop: loop
        if @__interval > create_next_intervals then
            leave create_loop;
        end if;

        set less_than_timestamp = cur_time + (hourly_interval * @__interval *3600);
        set partition_name = from_unixtime(cur_time + hourly_interval *(@__interval - 1) * 3600, 'p%y%m%d%h00');
        if(partition_name != old_partition_name) then
            callpartition_create(schema_name, table_name, partition_name, less_than_timestamp);
        end if;
        set @__interval=@__interval+1;
        set old_partition_name = partition_name;
    end loop;

    set older_than_partition_date=date_format(date_sub(now(), intervalkeep_data_days day), '%y%m%d0000');
    call partition_drop(schema_name, table_name, older_than_partition_date);

end$$
delimiter ;

delimiter $$
create procedure `partition_verify`(schemanamevarchar(64), tablename varchar(64), hourlyinterval int(11))
begin
    declare partition_name varchar(16);
    declare retrows int(11);
    declare future_timestamp timestamp;

    /*
    * check if any partitions exist for the given schemaname.tablename.
    */
    select count(1) into retrows
    from information_schema.partitions
    where table_schema = schemaname and table_name = tablename andpartition_name is null;

    /*
    * if partitions do not exist, go ahead and partition the table
    */
    ifretrows = 1 then
        /*
        * take the current date at 00:00:00 and add hourlyinterval to it. this is the timestamp below which we willstore values.
        * we begin partitioning based on the beginning of a day. this is because we don't want to generate arandom partition
        * that won't necessarily fall in line with the desired partition naming(ie: if the hour interval is 24 hours, we could
        * end up creating a partition now named "p201403270600" whenall other partitions will be like "p201403280000").
        */
        set future_timestamp = timestampadd(hour, hourlyinterval,concat(curdate(), " ", '00:00:00'));
        set partition_name = date_format(curdate(), 'p%y%m%d%h00');

        -- create the partitioning query
        set @__partition_sql = concat("alter table ", schemaname,".", tablename, " partition by range(`clock`)");
        set @__partition_sql = concat(@__partition_sql, "(partition ",partition_name, " values less than (",unix_timestamp(future_timestamp), "));");

        -- run the partitioning query
        prepare stmt from @__partition_sql;
        execute stmt;
        deallocate prepare stmt;
    end if;
end$$
delimiter ;

delimiter $$
create procedure`partition_maintenance_all`(schema_name varchar(32))
begin
        call partition_maintenance(schema_name, 'history', 90, 24, 14);
        call partition_maintenance(schema_name, 'history_log', 90, 24, 14);
        call partition_maintenance(schema_name, 'history_str', 90, 24, 14);
        call partition_maintenance(schema_name, 'history_text', 90, 24, 14);
        callpartition_maintenance(schema_name, 'history_uint', 90, 24, 14);
        call partition_maintenance(schema_name, 'trends', 730, 24, 14);
        call partition_maintenance(schema_name, 'trends_uint', 730, 24, 14);
end$$
delimiter ;

上面内容包含了创建分区的存储过程，将上面内容复制到partition.sql中,然后执行如下：

mysql -uzabbix -pzabbix zabbix < partition.sql

b、 添加crontable,每天执行01点01分执行，如下：

crontab -l > crontab.txt 
cat >> crontab.txt <<eof
#zabbix partition_maintenance
01 01 * * * mysql -uzabbix -pzabbix zabbix -e"call partition_maintenance_all('zabbix')" &>/dev/null
eof
cat crontab.txt |crontab

注意： mysql的zabbix用户的密码部分按照实际环境配置

c、首先执行一次（由于首次执行的时间较长，请使用nohup执行），如下：

nohup  mysql -uzabbix -pzabbix zabbix -e "callpartition_maintenance_all('zabbix')" &> /root/partition.log&

注意：观察/root/partition.log的输出

d、 查看结果

登录mysql,查看history等表, 如下：

mariadb [zabbix]> showcreate table history
| history | create table `history` (
 `itemid` bigint(20) unsigned not null,
 `clock`int(11) not null default '0',
 `value`double(16,4) not null default '0.0000',
 `ns`int(11) not null default '0',
 key`history_1` (`itemid`,`clock`)
) engine=innodb default charset=utf8
/*!50100 partition by range (`clock`)
(partition p201708280000 values less than(1503936000) engine = innodb,
 partition p201708290000 values less than(1504022400) engine = innodb,
 partition p201708300000 values less than(1504108800) engine = innodb,
 partition p201708310000 values less than(1504195200) engine = innodb,
 partition p201709010000 values less than(1504281600) engine = innodb,
 partition p201709020000 values less than(1504368000) engine = innodb,
 partition p201709030000 values less than(1504454400) engine = innodb,
 partition p201709040000 values less than(1504540800) engine = innodb,
 partition p201709050000 values less than(1504627200) engine = innodb,
 partition p201709060000 values less than(1504713600) engine = innodb,
 partition p201709070000 values less than(1504800000) engine = innodb,
 partition p201709080000 values less than(1504886400) engine = innodb,
 partition p201709090000 values less than(1504972800) engine = innodb,
 partition p201709100000 values less than(1505059200) engine = innodb,
 partition p201709110000 values less than(1505145600) engine = innodb) */ |

发现了大量partition字段，说明配置正确。注意观察mysql的slow query,一般到执行操作的第二天，slow query几乎就会有了，此时zabbix的dashboard响应速度应该非常流畅了。