Zabbix 表分区

需求：

客户要分析性能的历史数据，小规模测试逻辑的时候没问题，但取正式数据时发现调用查询历史数据的接口超时了，同时客户工程师反映web控制台也出现了卡顿，客户现在就不敢再取数据了，怕影响新的监控数据入库。想一起看下数据返回慢的原因。

分析：

根据经验，zabbix数据结构里，history表如果做分区化改造后，将会对提升性能有很大帮助。
(实际对history、history_uint、trends、trends_uint进行了分区)

落地：

跟客户协商空窗期；最后定晚上7点进行表分区

操作示例：

1、导出需要分区的表history数据  不导出表结构以免数据太大不好编辑

mysqldump -uroot -p --no-create-info zabbix history> history.sql

2、导出数据后更换表名；

RENAME TABLE history TO history_bak;

3、查看建表语句

show create table history;

4、根据history原表结构新建表分区(此处按照时间分30个分区)

CREATE TABLE `history` (
  `itemid` bigint unsigned NOT NULL,
  `clock` int NOT NULL DEFAULT '0',
  `value` double NOT NULL DEFAULT '0',
  `ns` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`itemid`,`clock`,`ns`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin PARTITION BY RANGE (`clock`)
(PARTITION p20230321 VALUES LESS THAN (1679414400) ENGINE = InnoDB,
PARTITION p20230322 VALUES LESS THAN (1679500800) ENGINE = InnoDB,
PARTITION p20230323 VALUES LESS THAN (1679587200) ENGINE = InnoDB,
PARTITION p20230324 VALUES LESS THAN (1679673600) ENGINE = InnoDB,
PARTITION p20230325 VALUES LESS THAN (1679760000) ENGINE = InnoDB,
PARTITION p20230326 VALUES LESS THAN (1679846400) ENGINE = InnoDB,
PARTITION p20230327 VALUES LESS THAN (1679932800) ENGINE = InnoDB,
PARTITION p20230328 VALUES LESS THAN (1680019200) ENGINE = InnoDB,
PARTITION p20230329 VALUES LESS THAN (1680105600) ENGINE = InnoDB,
PARTITION p20230330 VALUES LESS THAN (1680192000) ENGINE = InnoDB,
PARTITION p20230331 VALUES LESS THAN (1680278400) ENGINE = InnoDB,
PARTITION p20230401 VALUES LESS THAN (1680364800) ENGINE = InnoDB,
PARTITION p20230402 VALUES LESS THAN (1680451200) ENGINE = InnoDB,
PARTITION p20230403 VALUES LESS THAN (1680537600) ENGINE = InnoDB,
PARTITION p20230404 VALUES LESS THAN (1680624000) ENGINE = InnoDB,
PARTITION p20230405 VALUES LESS THAN (1680710400) ENGINE = InnoDB,
PARTITION p20230406 VALUES LESS THAN (1680796800) ENGINE = InnoDB,
PARTITION p20230407 VALUES LESS THAN (1680883200) ENGINE = InnoDB,
PARTITION p20230408 VALUES LESS THAN (1680969600) ENGINE = InnoDB,
PARTITION p20230409 VALUES LESS THAN (1681056000) ENGINE = InnoDB,
PARTITION p20230410 VALUES LESS THAN (1681142400) ENGINE = InnoDB,
PARTITION p20230411 VALUES LESS THAN (1681228800) ENGINE = InnoDB,
PARTITION p20230412 VALUES LESS THAN (1681315200) ENGINE = InnoDB,
PARTITION p20230413 VALUES LESS THAN (1681401600) ENGINE = InnoDB,
PARTITION p20230414 VALUES LESS THAN (1681488000) ENGINE = InnoDB,
PARTITION p20230415 VALUES LESS THAN (1681574400) ENGINE = InnoDB,
PARTITION p20230416 VALUES LESS THAN (1681660800) ENGINE = InnoDB,
PARTITION p20230417 VALUES LESS THAN (1681747200) ENGINE = InnoDB,
PARTITION p20230418 VALUES LESS THAN (1681833600) ENGINE = InnoDB,
PARTITION p20230419 VALUES LESS THAN (1681920000) ENGINE = InnoDB);

5、查看分区情况

select partition_name part, partition_expression expr, partition_description descr, table_rows from  INFORMATION_SCHEMA.partitions  where TABLE_SCHEMA="zabbix" AND TABLE_NAME="history";

6、导入数据

mysql -uRoot -p zabbix < history.sql

7、验证没问题可删除旧表

drop table zabbix.history;

8、定时创建删除分区 脚步+计划任务(插入的数据超过分区范围将会报错会导致数据的丢失；所以需要每天来创建新的分区保证数据能插入并删除最旧的分区数据)

#!/bin/bash
LIMIT_DAY=30

add_partition() {
  SEED=$(date -d "+2 days" +%Y%m%d)
  PARTITION_NAME="p${SEED}"
  PARTITION_LIMIT=$(date -d "$SEED +2 days" +%s)

  SQL_MODIFYING_HISTORY="alter table history add partition (partition $PARTITION_NAME values less than ($PARTITION_LIMIT))"
  SQL_MODIFYING_HISTORY_UINT="alter table history_uint add partition (partition $PARTITION_NAME values less than ($PARTITION_LIMIT))"
  SQL_MODIFYING_TRENDS="alter table trends add partition (partition $PARTITION_NAME values less than ($PARTITION_LIMIT))"
  SQL_MODIFYING_TRENDS_UINT="alter table trends_uint add partition (partition $PARTITION_NAME values less than ($PARTITION_LIMIT))"

  echo ${SQL_MODIFYING_HISTORY}
  mysql -uroot -pPassword --database=zabbix --execute="$SQL_MODIFYING_HISTORY"

  echo ${SQL_MODIFYING_TRENDS}
  mysql -uroot -pPassword --database=zabbix --execute="$SQL_MODIFYING_TRENDS"

  echo ${SQL_MODIFYING_HISTORY_UINT}
  mysql -uroot -pPassword --database=zabbix --execute="$SQL_MODIFYING_HISTORY_UINT"

  echo ${SQL_MODIFYING_TRENDS_UINT}
  mysql -uroot -pPassword --database=zabbix --execute="$SQL_MODIFYING_TRENDS_UINT"

}

get_creating_stmt() {
  mysql -uroot -p'Password' zabbix --execute='show create table history\G'
}

get_partition_name_lst() {
  get_creating_stmt|grep -e 'PARTITION.*VALUES'|awk '{print $2}'
}

drop_partition() {
  p_lst=(`get_partition_name_lst`)
  len_p_lst=${#p_lst[*]}

  for((i=0;i<(${len_p_lst}-${LIMIT_DAY}-1);i++));
  do
    SQL_DROPING_HISTORY="alter table history drop partition ${p_lst[i]}"
    SQL_DROPING_HISTORY_UINT="alter table history_uint drop partition ${p_lst[i]}"
    SQL_DROPING_TRENDS="alter table trends drop partition ${p_lst[i]}"
    SQL_DROPING_TRENDS_UINT="alter table trends_uint drop partition ${p_lst[i]}"
    echo ${SQL_DROPING_HISTORY}
    mysql -uroot -pPassword --database=zabbix --execute="$SQL_DROPING_HISTORY"
    echo ${SQL_DROPING_TRENDS}
    mysql -uroot -pPassword --database=zabbix --execute="$SQL_DROPING_TRENDS"
    echo ${SQL_DROPING_HISTORY_UINT}
    mysql -uroot -pPassword --database=zabbix --execute="$SQL_DROPING_HISTORY_UINT"
    echo ${SQL_DROPING_TRENDS_UINT}
    mysql -uroot -pPassword --database=zabbix --execute="$SQL_DROPING_TRENDS_UINT"
  done

}

add_partition
drop_partition

#查看所有表数据大小

SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024),4),'M') AS 'Data Size',CONCAT(ROUND(index_length/(1024*1024),4),'M') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1024),4),'M') AS'Total'FROM information_schema.TABLES  ORDER BY --total DESC;

#查看所有库的大小

SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.tables GROUP BY table_schema;