首页 > 代码库 > zabbix数据库优化脚本
zabbix数据库优化脚本
分享一个zabbix数据库的优化脚本,适合2.0版本。
对history,hostory_uint按日分区,trends,trends_uint按月分区;
关闭Houserkeeper:
vim zabbix_server.conf
DisableHousekeeper=1
对history,hostory_uint每日建立第二天的分区并删除45天前的分区
对trends,trends_uint每月20号建立下一个月的分区,并删除12个月前的分区
时间可以自己修改
由于events表的大小对仪表盘的展示速度影响很大,2.0以后的版本又对该表做过修改,具有主键约束,不能以clock做分区表优化,所以我每日都清理该表,只保留了3天的数据(根据自己的环境和需求而定,可以自己修改),很简单的一个定期维护脚本,希望能帮到大家
#!/usr/bin/env python # coding: utf-8 import MySQLdb import datetime now_time = datetime.datetime.now() error_time = now_time.strftime(‘%Y-%m-%d %H:%M:%S‘) theday = (now_time + datetime.timedelta(days=+1)).strftime(‘%Y%m%d‘) next_day = (now_time + datetime.timedelta(days=+2)).strftime(‘%Y-%m-%d‘) themonth = datetime.datetime(now_time.year,(now_time.month+1),now_time.day).strftime(‘%Y%m‘) next_month = datetime.datetime(now_time.year,(now_time.month+2),1).strftime(‘%Y-%m-%d‘) last_time = (now_time - datetime.timedelta(days=30)).strftime(‘%Y%m%d‘) last_time_month = datetime.datetime((now_time.year-1),now_time.month,now_time.day).strftime(‘%Y%m‘) events_time = (now_time - datetime.timedelta(days=1)).strftime(‘%Y-%m-%d‘) history_time = (now_time - datetime.timedelta(days=45)).strftime(‘%Y%m%d‘) trends_time = datetime.datetime((now_time.year-1),now_time.month,now_time.day).strftime(‘%Y%m‘) table_day=[‘history‘, ‘history_uint‘] table_month=[‘trends‘, ‘trends_uint‘] conn=MySQLdb.connect(host=‘localhost‘,user=‘zabbix‘,passwd=‘zabbix‘,db=‘zabbix‘,port=3306) cur=conn.cursor() for name_d in table_day: try: ####新增分区####### cur.execute(‘ALTER TABLE `%s` ADD PARTITION (PARTITION p%s VALUES LESS THAN (UNIX_TIMESTAMP("%s 00:00:00")))‘ % (name_d, theday, next_day)) except MySQLdb.Error,e: print "[%s] Mysql Error %d: %s" % (error_time, e.args[0], e.args[1]) pass for name_m in table_month: try: ####新增分区####### if now_time.day == 20: cur.execute(‘ALTER TABLE `%s` ADD PARTITION (PARTITION p%s VALUES LESS THAN (UNIX_TIMESTAMP("%s 00:00:00")))‘ % (name_m, themonth, next_month)) except MySQLdb.Error,e: print "[%s] Mysql Error %d: %s" % (error_time, e.args[0], e.args[1]) pass ######清除events表1天前的数据###### try: cur.execute(‘DELETE FROM `events` where `clock` < UNIX_TIMESTAMP("%s 00:00:00")‘% events_time) cur.execute(‘optimize table events;‘) except MySQLdb.Error,e: print "[%s] Mysql Error %d: %s" % (error_time, e.args[0], e.args[1]) pass ######清除history,histroy_uint表45天前的数据###### for name_d in table_day: try: cur.execute(‘ALTER TABLE `%s` DROP PARTITION p%s;‘ % (name_d, history_time)) except MySQLdb.Error,e: print "[%s] Mysql Error %d: %s" % (error_time, e.args[0], e.args[1]) pass ######清除trends,trends_uint表一年前的数据###### for name_m in table_month: try: cur.execute(‘ALTER TABLE `%s` DROP PARTITION p%s;‘ % (name_m, trends_time)) except MySQLdb.Error,e: print "[%s] Mysql Error %d: %s" % (error_time, e.args[0], e.args[1]) pass conn.commit() cur.close() conn.close()
本文出自 “坑爹霞老板” 博客,请务必保留此出处http://xiaoking.blog.51cto.com/4024863/1408151
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。