Mysql优化之Zabbix分区优化

2023-09-20 08:23:05 162

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

原理

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

操作详细步骤

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

第一步

登录zabbixserver的数据库，统一MySQL的配置

cat>/etc/my.cnf<
注意：一定要修改innodb_buffer_pool_size=物理内存的1/3
第二步
先确认zabbix的版本，本操作zabbix的版本一定要大于3.2.0。小于3.2的版本不能安装此操作，线上默认是zabbix-3.2.6。
a、导入存储过程
#catpartition.sql
DELIMITER$$
CREATEPROCEDURE`partition_create`(SCHEMANAMEvarchar(64),TABLENAMEvarchar(64),PARTITIONNAMEvarchar(64),CLOCKint)
BEGIN
/*
SCHEMANAME=TheDBschemainwhichtomakechanges
TABLENAME=Thetablewithpartitionstopotentiallydelete
PARTITIONNAME=Thenameofthepartitiontocreate
*/
/*
Verifythatthepartitiondoesnotalreadyexist
*/

DECLARERETROWSINT;
SELECTCOUNT(1)INTORETROWS
FROMinformation_schema.partitions
WHEREtable_schema=SCHEMANAMEANDtable_name=TABLENAMEANDpartition_description>=CLOCK;

IFRETROWS=0THEN
/*
1.Printamessageindicatingthatapartitionwascreated.
2.CreatetheSQLtocreatethepartition.
3.ExecutetheSQLfrom#2.
*/
SELECTCONCAT("partition_create(",SCHEMANAME,",",TABLENAME,",",PARTITIONNAME,",",CLOCK,")")ASmsg;
SET@sql=CONCAT('ALTERTABLE',SCHEMANAME,'.',TABLENAME,'ADDPARTITION(PARTITION',PARTITIONNAME,'VALUESLESSTHAN(',CLOCK,'));');
PREPARESTMTFROM@sql;
EXECUTESTMT;
DEALLOCATEPREPARESTMT;
ENDIF;
END$$
DELIMITER;

DELIMITER$$
CREATEPROCEDURE`partition_drop`(SCHEMANAMEVARCHAR(64),TABLENAMEVARCHAR(64),DELETE_BELOW_PARTITION_DATEBIGINT)
BEGIN
/*
SCHEMANAME=TheDBschemainwhichtomakechanges
TABLENAME=Thetablewithpartitionstopotentiallydelete
DELETE_BELOW_PARTITION_DATE=Deleteanypartitionswithnamesthataredatesolderthanthisone(yyyy-mm-dd)
*/
DECLAREdoneINTDEFAULTFALSE;
DECLAREdrop_part_nameVARCHAR(16);

/*
Getalistofallthepartitionsthatareolderthanthedate
inDELETE_BELOW_PARTITION_DATE.Allpartitionsareprefixedwith
a"p",souseSUBSTRINGTOgetridofthatcharacter.
*/
DECLAREmyCursorCURSORFOR
SELECTpartition_name
FROMinformation_schema.partitions
WHEREtable_schema=SCHEMANAMEANDtable_name=TABLENAMEANDCAST(SUBSTRING(partition_nameFROM2)ASUNSIGNED)CREATE_NEXT_INTERVALSTHEN
LEAVEcreate_loop;
ENDIF;

SETLESS_THAN_TIMESTAMP=CUR_TIME+(HOURLY_INTERVAL*@__interval*3600);
SETPARTITION_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);
ENDIF;
SET@__interval=@__interval+1;
SETOLD_PARTITION_NAME=PARTITION_NAME;
ENDLOOP;

SETOLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(),INTERVALKEEP_DATA_DAYSDAY),'%Y%m%d0000');
CALLpartition_drop(SCHEMA_NAME,TABLE_NAME,OLDER_THAN_PARTITION_DATE);

END$$
DELIMITER;

DELIMITER$$
CREATEPROCEDURE`partition_verify`(SCHEMANAMEVARCHAR(64),TABLENAMEVARCHAR(64),HOURLYINTERVALINT(11))
BEGIN
DECLAREPARTITION_NAMEVARCHAR(16);
DECLARERETROWSINT(11);
DECLAREFUTURE_TIMESTAMPTIMESTAMP;

/*
*CheckifanypartitionsexistforthegivenSCHEMANAME.TABLENAME.
*/
SELECTCOUNT(1)INTORETROWS
FROMinformation_schema.partitions
WHEREtable_schema=SCHEMANAMEANDtable_name=TABLENAMEANDpartition_nameISNULL;

/*
*Ifpartitionsdonotexist,goaheadandpartitionthetable
*/
IFRETROWS=1THEN
/*
*Takethecurrentdateat00:00:00andaddHOURLYINTERVALtoit.Thisisthetimestampbelowwhichwewillstorevalues.
*Webeginpartitioningbasedonthebeginningofaday.Thisisbecausewedon'twanttogeneratearandompartition
*thatwon'tnecessarilyfallinlinewiththedesiredpartitionnaming(ie:ifthehourintervalis24hours,wecould
*endupcreatingapartitionnownamed"p201403270600"whenallotherpartitionswillbelike"p201403280000").
*/
SETFUTURE_TIMESTAMP=TIMESTAMPADD(HOUR,HOURLYINTERVAL,CONCAT(CURDATE(),"",'00:00:00'));
SETPARTITION_NAME=DATE_FORMAT(CURDATE(),'p%Y%m%d%H00');

--Createthepartitioningquery
SET@__PARTITION_SQL=CONCAT("ALTERTABLE",SCHEMANAME,".",TABLENAME,"PARTITIONBYRANGE(`clock`)");
SET@__PARTITION_SQL=CONCAT(@__PARTITION_SQL,"(PARTITION",PARTITION_NAME,"VALUESLESSTHAN(",UNIX_TIMESTAMP(FUTURE_TIMESTAMP),"));");

--Runthepartitioningquery
PREPARESTMTFROM@__PARTITION_SQL;
EXECUTESTMT;
DEALLOCATEPREPARESTMT;
ENDIF;
END$$
DELIMITER;

DELIMITER$$
CREATEPROCEDURE`partition_maintenance_all`(SCHEMA_NAMEVARCHAR(32))
BEGIN
CALLpartition_maintenance(SCHEMA_NAME,'history',90,24,14);
CALLpartition_maintenance(SCHEMA_NAME,'history_log',90,24,14);
CALLpartition_maintenance(SCHEMA_NAME,'history_str',90,24,14);
CALLpartition_maintenance(SCHEMA_NAME,'history_text',90,24,14);
CALLpartition_maintenance(SCHEMA_NAME,'history_uint',90,24,14);
CALLpartition_maintenance(SCHEMA_NAME,'trends',730,24,14);
CALLpartition_maintenance(SCHEMA_NAME,'trends_uint',730,24,14);
END$$
DELIMITER;
上面内容包含了创建分区的存储过程，将上面内容复制到partition.sql中,然后执行如下：
mysql-uzabbix-pzabbixzabbix
b、添加crontable,每天执行01点01分执行，如下：
crontab-l>crontab.txt
cat>>crontab.txt</dev/null
EOF
catcrontab.txt|crontab
注意：mysql的zabbix用户的密码部分按照实际环境配置
c、首先执行一次（由于首次执行的时间较长，请使用nohup执行），如下：
nohupmysql-uzabbix-pzabbixzabbix-e"CALLpartition_maintenance_all('zabbix')"&>/root/partition.log&
注意：观察/root/partition.log的输出
d、查看结果
登录mysql,查看history等表,如下：
MariaDB[zabbix]>showcreatetablehistory
|history|CREATETABLE`history`(
`itemid`bigint(20)unsignedNOTNULL,
`clock`int(11)NOTNULLDEFAULT'0',
`value`double(16,4)NOTNULLDEFAULT'0.0000',
`ns`int(11)NOTNULLDEFAULT'0',
KEY`history_1`(`itemid`,`clock`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8
/*!50100PARTITIONBYRANGE(`clock`)
(PARTITIONp201708280000VALUESLESSTHAN(1503936000)ENGINE=InnoDB,
PARTITIONp201708290000VALUESLESSTHAN(1504022400)ENGINE=InnoDB,
PARTITIONp201708300000VALUESLESSTHAN(1504108800)ENGINE=InnoDB,
PARTITIONp201708310000VALUESLESSTHAN(1504195200)ENGINE=InnoDB,
PARTITIONp201709010000VALUESLESSTHAN(1504281600)ENGINE=InnoDB,
PARTITIONp201709020000VALUESLESSTHAN(1504368000)ENGINE=InnoDB,
PARTITIONp201709030000VALUESLESSTHAN(1504454400)ENGINE=InnoDB,
PARTITIONp201709040000VALUESLESSTHAN(1504540800)ENGINE=InnoDB,
PARTITIONp201709050000VALUESLESSTHAN(1504627200)ENGINE=InnoDB,
PARTITIONp201709060000VALUESLESSTHAN(1504713600)ENGINE=InnoDB,
PARTITIONp201709070000VALUESLESSTHAN(1504800000)ENGINE=InnoDB,
PARTITIONp201709080000VALUESLESSTHAN(1504886400)ENGINE=InnoDB,
PARTITIONp201709090000VALUESLESSTHAN(1504972800)ENGINE=InnoDB,
PARTITIONp201709100000VALUESLESSTHAN(1505059200)ENGINE=InnoDB,
PARTITIONp201709110000VALUESLESSTHAN(1505145600)ENGINE=InnoDB)*/|
发现了大量PARTITION字段，说明配置正确。注意观察Mysql的SlowQuery,一般到执行操作的第二天，SlowQuery几乎就会有了，此时Zabbix的Dashboard响应速度应该非常流畅了。

Mysql优化之Zabbix分区优化

热门推荐

随机推荐