ZabbixServerMySQL分区分表

本教程只是大概讲解zabbix MySQL分区分表操作，若读者想了解更多关于表分区的详细知识，可以访问以下地址：

MySQL官网说明：https://dev.mysql.com/doc/refman/5.7/en/partitioning.html
Zabbix 官网说明：https://www.zabbix.org/wiki/Docs/howto/mysql_partition


Zabbix关于性能优化有很多种方法，例如调整性能参数（包括zabbix server性能参数，MySQL数据库性能参数等），增加服务器性能（CPU/内存/硬盘更换SSD固态盘/千兆网络换成万兆光纤网络等），MySQL数据库优化（包括读写分离/集群/分区分表等），本教程主要针对MySQL数据库的分区分表优化进行讲解，MySQL分区分表不是适合所有MySQL数据库使用。

本教程是对zabbix监控服务器的MySQL进行分区分表进行优化，是参考zabbix官网说明操作，如果监控设备达到1000台以上或者监控数据量非常大就适合使用zabbix官网推荐的分区分表优化MySQL数据库。MySQL分区分表作用减少数据库表在容量、提高数据库查询性能。

zabbix 采用架构（Linux + Tomcat + MySQL + Zabbix ）,使用zabbix官网Yum安装的环境，以下是已安装软件版本:

[[email protected] ~]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
[[email protected] ~]# uname -r
3.10.0-862.el7.x86_64
[[email protected] ~]# php -v
PHP 5.4.16 (cli) (built: Apr 1 2020 04:07:17)
Copyright (c) 1997-2013 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies
[[email protected] ~]# apachectl -v
Server version: Apache/2.4.6 (CentOS)
Server built: Apr 2 2020 13:13:23
[[email protected] ~]# zabbix_server -V
zabbix_server (Zabbix) 4.0.25
[[email protected] ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using EditLine wrapper


分区分表预备知识：

1、建议了解一下MySQL分区分表概念，数据库空间碎片概念，Innodb数据库引擎。（不了解也可以继续下面操作）

2、MySQL要开启独立表空间（innodb_file_per_table，MySQL5.6包含MySQL 5.6以下的版本默认是没有开启）。

3、MySQL需要开启默认使用Innodb引擎。

4、针对zabbix数据库表要进行分区分表，如果表数据量大执行分区分表会非常慢，建议先清空history、trends表中的数据（注意：历史记录会全部被清空）,再执行分区分表。

5、zabbix 数据库主要针对以下表（zabbix历史记录表和zabbix存储趋势表）进行分区分表：

history，history_uint，history_str，history_test，history_log，trends，trends_uint

6、zabbix 数据库在没有使用分区分表功能，默认使用Housekeeping（管家功能）进行删除历史数据和趋势历史记录，如果zabbix数据库使用了分区分表功能需要把Housekeeping（管理功能）关闭。Housekeeping功能监控数据量少可以使用，但监控数据量多每次执行删除会降低MySQL数据库性能，并且还会产生很多空间碎片。（zabbix_server.conf配置文件两个参数进行历史记录数据删除：间隔多久删除一次，默认单位小时HousekeepingFrequency=1，一次删除多少数据，默认单位行MaxHousekeeperDelete=5000）

一、查看开启独立表空间和、默认使用Innodb引擎、最大连接数

[[email protected] ~]# mysql -u root -p
Enter password:
mysql> show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)
[[email protected] ~]# echo "max_cOnnections=1000" >> /etc/my.cnf


二、清空zabbix 数据库历史数据

mysql> select table_name,(data_length+index_length)/1024/1024 as total_mb,table_rows from information_schema.tables where table_schema='zabbix';
+----------------------------+------------+------------+
| history | 0.85937500 | 9443 |
| history_log | 0.03125000 | 0 |
| history_str | 0.03125000 | 19 |
| history_text | 0.03125000 | 0 |
| history_uint | 0.37500000 | 4216 |
| trends | 0.01562500 | 244 |
| trends_uint | 0.01562500 | 137 |
mysql> use zabbix
mysql>truncate table history;
mysql>optimize table history;
mysql>truncate table history_str;
mysql>optimize table history_str;
mysql>truncate table history_log;
mysql>optimize table history_log;
mysql>truncate table history_text;
mysql>optimize table history_text;
mysql>truncate table history_uint;
mysql>optimize table history_uint;
mysql>truncate table trends;
mysql>optimize table trends_uint;


三、zabbix 数据库导入存储过程

###先编辑一个SQL脚本执行导入四个存储过程，此脚本复制zabbix官网提供的SQL脚本
[[email protected] ~]# vim /tmp/history_stored.sql
DELIMITER $$
CREATE PROCEDURE `partition_create`(SCHEMANAME varchar(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 AND partition_description >= CLOCK;
IF RETROWS = 0 THEN
/*
1. Print a message indicating that a partition was created.
2. Create the SQL to create the partition.
3. Execute the SQL from #2.
*/
SELECT CONCAT( "partition_create(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;
SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );
PREPARE STMT FROM @sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `partition_drop`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT)
BEGIN
/*
SCHEMANAME = The DB schema in which to make changes
TABLENAME = The table with partitions to potentially delete
DELETE_BELOW_PARTITION_DATE = Delete any partitions with names that are dates 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 TO get rid of that character.
*/
DECLARE myCursor CURSOR FOR
SELECT partition_name
FROM information_schema.partitions
WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND CAST(SUBSTRING(partition_name FROM 2) AS UNSIGNED) 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 all the necessary partitions.
2. Run the SQL to drop the partitions.
3. Print out the table partitions 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 being deleted, 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
CALL partition_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(), INTERVAL KEEP_DATA_DAYS DAY), '%Y%m%d0000');
CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE);
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `partition_verify`(SCHEMANAME VARCHAR(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 AND partition_name IS NULL;
/*
* If partitions do not exist, go ahead and partition the table
*/
IF RETROWS = 1 THEN
/*
* Take the current date at 00:00:00 and add HOURLYINTERVAL to it. This is the timestamp below which we will store values.
* We begin partitioning based on the beginning of a day. This is because we don't want to generate a random 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" when all 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 ;
###执行SQL脚本导入存储过程
[[email protected] ~]# mysql -u root -p
Enter password:
mysql> use zabbix
mysql> source /tmp/history_stored.sql;
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)


四、zabbix 数据库使用存储过程

###创建一个SQL脚本使用存储过程
[[email protected] ~]# vim /tmp/history_stored_used.sql
DELIMITER $$
CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGIN
CALL partition_maintenance(SCHEMA_NAME, 'history', 28, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_log', 28, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_str', 28, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_text', 28, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 28, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'trends', 730, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 730, 24, 14);
END$$
DELIMITER ;
[[email protected] ~]# mysql -u root -p
Enter password:
mysql> use zabbix
mysql> source /tmp/history_stored_used.sql;
mysql> call partition_maintenance_all('zabbix');
说明：
CALL partition_maintenance('', '', , , )
zabbix_db_name：库名
table_name：表名
days_to_keep_data：保存多少天的数据
hourly_interval：每隔多久生成一个分区
num_future_intervals_to_create：本次一共生成多少个分区
例如：
CALL partition_maintenance(SCHEMA_NAME, 'history', 28, 24, 14);
这个例子表示history表最多保存28天的数据，每隔24小时生成一个分区，这次一共生成14个分区


五、定时调用存储过程

提示：因为存储过程会老化，比如上面所说history表最多保存28天的数据，每隔24小时生成一个分区，这次一共生成14个分区，当生成第15个分区时存储过程就已经老化了，后面的监控数据就存储不了，会存在丢失监控数据，所以，需要定时执行调用存储过程。设置定时任务时间是通过每24小时会产生一个分区，这次一共生成14个分区，也就是说第15天就存储过程就会老化，那我们就设定每个星期六调用存储过程创建一次（只要在14天以内的时间都允许）。

###通过crontab定时任务调用存储过程
[[email protected] ~]# crontab –e
###everyweek staurday MySQL create partition at 5:00 AM
00 5 * * 6 /usr/bin/mysql -u zabbix -p'12345' zabbix -e "call partition_maintenance_all('zabbix');" > /dev/null 2>&1


六、关闭zabbix 管家功能（housekeeping）

七、检查创建的分区分表情况

###检查分区情况
[[email protected] ~]# ls -l /var/lib/mysql/zabbix/ | grep -e history#P#
-rw-r----- 1 mysql mysql 196608 Oct 20 16:39 history#P#p202010200000.ibd
-rw-r----- 1 mysql mysql 114688 Oct 20 16:07 history#P#p202010210000.ibd
-rw-r----- 1 mysql mysql 114688 Oct 20 16:07 history#P#p202010220000.ibd
-rw-r----- 1 mysql mysql 114688 Oct 20 16:07 history#P#p202010230000.ibd
-rw-r----- 1 mysql mysql 114688 Oct 20 16:07 history#P#p202010240000.ibd
-rw-r----- 1 mysql mysql 114688 Oct 20 16:07 history#P#p202010250000.ibd
-rw-r----- 1 mysql mysql 114688 Oct 20 16:07 history#P#p202010260000.ibd
-rw-r----- 1 mysql mysql 114688 Oct 20 16:07 history#P#p202010270000.ibd
-rw-r----- 1 mysql mysql 114688 Oct 20 16:07 history#P#p202010280000.ibd
-rw-r----- 1 mysql mysql 114688 Oct 20 16:07 history#P#p202010290000.ibd
-rw-r----- 1 mysql mysql 114688 Oct 20 16:07 history#P#p202010300000.ibd
-rw-r----- 1 mysql mysql 114688 Oct 20 16:07 history#P#p202010310000.ibd
-rw-r----- 1 mysql mysql 114688 Oct 20 16:07 history#P#p202011010000.ibd
-rw-r----- 1 mysql mysql 114688 Oct 20 16:07 history#P#p202011020000.ibd
###检查分表情况
[[email protected] ~]# mysql -u root -p
Enter password:
mysql> use zabbix
mysql> show create table history\G;
*************************** 1. row ***************************
Table: history
Create Table: 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 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (`clock`)
(PARTITION p202010200000 VALUES LESS THAN (1603209600) ENGINE = InnoDB,
PARTITION p202010210000 VALUES LESS THAN (1603296000) ENGINE = InnoDB,
PARTITION p202010220000 VALUES LESS THAN (1603382400) ENGINE = InnoDB,
PARTITION p202010230000 VALUES LESS THAN (1603468800) ENGINE = InnoDB,
PARTITION p202010240000 VALUES LESS THAN (1603555200) ENGINE = InnoDB,
PARTITION p202010250000 VALUES LESS THAN (1603641600) ENGINE = InnoDB,
PARTITION p202010260000 VALUES LESS THAN (1603728000) ENGINE = InnoDB,
PARTITION p202010270000 VALUES LESS THAN (1603814400) ENGINE = InnoDB,
PARTITION p202010280000 VALUES LESS THAN (1603900800) ENGINE = InnoDB,
PARTITION p202010290000 VALUES LESS THAN (1603987200) ENGINE = InnoDB,
PARTITION p202010300000 VALUES LESS THAN (1604073600) ENGINE = InnoDB,
PARTITION p202010310000 VALUES LESS THAN (1604160000) ENGINE = InnoDB,
PARTITION p202011010000 VALUES LESS THAN (1604246400) ENGINE = InnoDB,
PARTITION p202011020000 VALUES LESS THAN (1604332800) ENGINE = InnoDB) */
1 row in set (0.00 sec)
ERROR:
No query specified