zabbix mysql调优

23ewrw

                      SELECT table_name AS "Tables", round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = 'zabbix' ORDER BY (data_length + index_length) DESC limit 10; (查看 mysql数据库表大小)

一 、转换分区表

1 2 3 4 5 6 7

ALTER TABLE history PARTITION BY range( clock) (PARTITION P20160301 VALUES LESS THAN (UNIX_TIMESTAMP('2016-03-01'))); ALTER TABLE history_log PARTITION BY range( clock) (PARTITION P20160301 VALUES LESS THAN (UNIX_TIMESTAMP('2016-03-01'))); ALTER TABLE history_str PARTITION BY range( clock) (PARTITION P20160301 VALUES LESS THAN (UNIX_TIMESTAMP('2016-03-01'))); ALTER TABLE history_text PARTITION BY range( clock) (PARTITION P20160301 VALUES LESS THAN (UNIX_TIMESTAMP('2016-03-01'))); ALTER TABLE history_uint PARTITION BY range( clock) (PARTITION P20160301 VALUES LESS THAN (UNIX_TIMESTAMP('2016-03-01'))); ALTER TABLE trends PARTITION BY range( clock) (PARTITION P20160301 VALUES LESS THAN (UNIX_TIMESTAMP('2016-03-01'))); ALTER TABLE trends_uint PARTITION BY range( clock) (PARTITION P20160301 VALUES LESS THAN (UNIX_TIMESTAMP('2016-03-01')));

二、执行脚本

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92

DELIMITER // DROP PROCEDURE IF EXISTS `zabbix`.`create_zabbix_partitions` // CREATE PROCEDURE `zabbix`.`create_zabbix_partitions` () BEGIN CALL zabbix.create_next_partitions("zabbix","history"); CALL zabbix.create_next_partitions("zabbix","history_log"); CALL zabbix.create_next_partitions("zabbix","history_str"); CALL zabbix.create_next_partitions("zabbix","history_text"); CALL zabbix.create_next_partitions("zabbix","history_uint"); CALL zabbix.create_next_partitions("zabbix","trends"); CALL zabbix.create_next_partitions("zabbix","trends_uint"); CALL zabbix.drop_old_partitions("zabbix","history"); CALL zabbix.drop_old_partitions("zabbix","history_log"); CALL zabbix.drop_old_partitions("zabbix","history_str"); CALL zabbix.drop_old_partitions("zabbix","history_text"); CALL zabbix.drop_old_partitions("zabbix","history_uint"); CALL zabbix.drop_old_partitions("zabbix","trends"); CALL zabbix.drop_old_partitions("zabbix","trends_uint"); END // DROP PROCEDURE IF EXISTS `zabbix`.`create_next_partitions` // CREATE PROCEDURE `zabbix`.`create_next_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64)) BEGIN DECLARE NEXTCLOCK timestamp; DECLARE PARTITIONNAME varchar(16); DECLARE CLOCK int; SET @totaldays = 7; SET @i = 1; createloop: LOOP SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i DAY); SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m%d' ); SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 DAY),'%Y-%m-%d 00:00:00')); CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK ); SET @i=@i+1; IF @i > @totaldays THEN LEAVE createloop; END IF; END LOOP; END // DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_partitions` // CREATE PROCEDURE `zabbix`.`drop_old_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64)) BEGIN DECLARE OLDCLOCK timestamp; DECLARE PARTITIONNAME varchar(16); DECLARE CLOCK int; SET @mindays = 30; SET @maxdays = @mindays+4; SET @i = @maxdays; droploop: LOOP SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i DAY); SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m%d' ); CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME ); SET @i=@i-1; IF @i <= @mindays THEN LEAVE droploop; END IF; END LOOP; END // DROP PROCEDURE IF EXISTS `zabbix`.`create_partition` // CREATE PROCEDURE `zabbix`.`create_partition` (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int) BEGIN DECLARE RETROWS int; SELECT COUNT(1) INTO RETROWS FROM `information_schema`.`partitions` WHERE `table_schema` = SCHEMANAME AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME; IF RETROWS = 0 THEN SELECT CONCAT( "create_partition(", 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 // DROP PROCEDURE IF EXISTS `zabbix`.`drop_partition` // CREATE PROCEDURE `zabbix`.`drop_partition` (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64)) BEGIN DECLARE RETROWS int; SELECT COUNT(1) INTO RETROWS FROM `information_schema`.`partitions` WHERE `table_schema` = SCHEMANAME AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME; IF RETROWS = 1 THEN SELECT CONCAT( "drop_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg; SET @sql = CONCAT( 'ALTER TABLE `', SCHEMANAME, '`.`', TABLENAME, '`', ' DROP PARTITION ', PARTITIONNAME, ';' ); PREPARE STMT FROM @sql; EXECUTE STMT; DEALLOCATE PREPARE STMT; END IF; END // DELIMITER ;

将上面代码保存partition.sql 文件中
执行

1 2	/apps/mysql/bin/mysql -uroot -pPass@word zabbix < /apps/mysql/partition.sql /apps/mysql/bin/mysql -B zabbix -uroot -pPass@word -e "CALL create_zabbix_partitions();"

三、加入 crontab

1 2	30 23 * * * /apps/mysql/bin/mysql -uroot -pPass@word zabbix < /apps/mysql/partition.sql 59 23 * * * /apps/mysql/bin/mysql -B zabbix -uroot -pPass@word -e "CALL create_zabbix_partitions();"