zabbix数据库优化脚本

32324

分享一个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()