@zabbix数据库历史与趋势数据占用优化（mysql存储查询）
文章目录
- 1.数据库大小查询
- 2.zabbix案例
- 1)磁盘占用优化
- 2）优化表的大小
- 3）服务器上查询确认表大小
- 4）数据库分区自动化脚本
1.数据库大小查询
数据库大小查询
进入数据库，切换到information_schema 库（这个库存放了其他的数据库的关联信息）
#切入数据库信息库
mysql> information_schema
#查询所有数据的大小
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;
#查询zabbix库的大小
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='zabbix';
#查询zabbix库hosts表大小
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='zabbix' and table_name='hosts';
2.zabbix案例
1)磁盘占用优化
假设zabbix占用空间大，需要优化
zabbix占用大的表 一般为两个表：
history：历史数据存储表
trends：趋势数据存储表
#查看zabbix库容量
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='zabbix';
+-----------+
| data |
+-----------+
| 1760.81MB |
+-----------+
1 row in set (0.01 sec)
#查询zabbix库表大小，并排序
mysql> SELECT TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as length,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,3), 'MB') as total_size FROM information_schema.TABLES WHERE TABLE_SCHEMA='zabbix' order by length desc;
+----------------------------+-------------+--------------+------------+------------+------------+
| TABLE_NAME | DATA_LENGTH | INDEX_LENGTH | length | TABLE_ROWS | total_size |
+----------------------------+-------------+--------------+------------+------------+------------+
| history_uint | 1125138432 | 503332864 | 1628471296 | 9288216 | 1553.031MB |
| trends_uint | 360185856 | 0 | 360185856 | 4578474 | 343.500MB |
| history | 192610304 | 89915392 | 282525696 | 3206240 | 269.438MB |
| trends | 92454912 | 0 | 92454912 | 1207380 | 88.172MB |
| history_str | 46219264 | 25247744 | 71467008 | 822685 | 68.156MB |
| alerts | 6832128 | 2867200 | 9699328 | 14900 | 9.250MB |
| items | 4669440 | 2359296 | 7028736 | 6470 | 6.703MB |
| events | 3686400 | 3162112 | 6848512 | 26401 | 6.531MB |
| history_text | 3162112 | 16384 | 3178496 | 387 | 3.031MB |
| triggers | 2605056 | 294912 | 2899968 | 2860 | 2.766MB |
| event_recovery | 1589248 | 540672 | 2129920 | 10712 | 2.031MB |
| images | 1589248 | 16384 | 1605632 | 138 | 1.531MB |
| functions | 409600 | 475136 | 884736 | 5965 | 0.844MB |
| items_applications | 360448 | 458752 | 819200 | 5626 | 0.781MB |
| item_preproc | 475136 | 212992 | 688128 | 5571 | 0.656MB |
| item_discovery | 360448 | 294912 | 655360 | 3818 | 0.625MB |
| auditlog | 311296 | 180224 | 491520 | 2709 | 0.469MB |
| graphs_items | 229376 | 196608 | 425984 | 2523 | 0.406MB |
| graphs | 180224 | 114688 | 294912 | 863 | 0.281MB |
| trigger_depends | 114688 | 147456 | 262144 | 1362 | 0.250MB |
| item_rtdata | 196608 | 0 | 196608 | 3021 | 0.188MB |
| profiles | 114688 | 81920 | 196608 | 700 | 0.188MB |
| hosts | 98304 | 98304 | 196608 | 192 | 0.188MB |
| item_application_prototype | 81920 | 114688 | 196608 | 945 | 0.188MB |
| hostmacro | 98304 | 81920 | 180224 | 749 | 0.172MB |
| item_condition | 114688 | 49152 | 163840 | 897 | 0.156MB |
| applications | 81920 | 49152 | 131072 | 894 | 0.125MB |
| mappings | 81920 | 49152 | 131072 | 1139 | 0.125MB |
| widget_field | 16384 | 98304 | 114688 | 134 | 0.109MB |
| media_type | 98304 | 16384 | 114688 | 10 | 0.109MB |
| sysmaps_elements | 16384 | 81920 | 98304 | 0 | 0.094MB |
| auditlog_details | 81920 | 16384 | 98304 | 476 | 0.094MB |
| trigger_discovery | 65536 | 16384 | 81920 | 780 | 0.078MB |
| application_template | 49152 | 32768 | 81920 | 417 | 0.078MB |
| sysmaps | 16384 | 65536 | 81920 | 0 | 0.078MB |
| event_tag | 65536 | 16384 | 81920 | 565 | 0.078MB |
| httptest | 16384 | 65536 | 81920 | 2 | 0.078MB |
| sysmaps_links | 16384 | 49152 | 65536 | 0 | 0.063MB |
| problem | 16384 | 49152 | 65536 | 95 | 0.063MB |
| scripts | 16384 | 49152 | 65536 | 3 | 0.063MB |
| screens_items | 49152 | 16384 | 65536 | 211 | 0.063MB |
| group_prototype | 16384 | 49152 | 65536 | 14 | 0.063MB |
| acknowledges | 16384 | 49152 | 65536 | 17 | 0.063MB |
| event_suppress | 16384 | 49152 | 65536 | 0 | 0.063MB |
| escalations | 16384 | 49152 | 65536 | 9 | 0.063MB |
| host_discovery | 16384 | 32768 | 49152 | 4 | 0.047MB |
| slideshow_user | 16384 | 32768 | 49152 | 0 | 0.047MB |
| correlation | 16384 | 32768 | 49152 | 0 | 0.047MB |
| proxy_dhistory | 16384 | 32768 | 49152 | 0 | 0.047MB |
| slides | 16384 | 32768 | 49152 | 0 | 0.047MB |
| sysmaps_link_triggers | 16384 | 32768 | 49152 | 0 | 0.047MB |
| application_prototype | 16384 | 32768 | 49152 | 141 | 0.047MB |
| icon_mapping | 16384 | 32768 | 49152 | 0 | 0.047MB |
| opcommand_hst | 16384 | 32768 | 49152 | 0 | 0.047MB |
| service_alarms | 16384 | 32768 | 49152 | 0 | 0.047MB |
| sysmap_usrgrp | 16384 | 32768 | 49152 | 0 | 0.047MB |
| application_discovery | 16384 | 32768 | 49152 | 84 | 0.047MB |
| icon_map | 16384 | 32768 | 49152 | 0 | 0.047MB |
| dservices | 16384 | 32768 | 49152 | 35 | 0.047MB |
| opcommand_grp | 16384 | 32768 | 49152 | 0 | 0.047MB |
| media | 16384 | 32768 | 49152 | 3 | 0.047MB |
| sysmap_user | 16384 | 32768 | 49152 | 0 | 0.047MB |
| httptestitem | 16384 | 32768 | 49152 | 6 | 0.047MB |
| services_links | 16384 | 32768 | 49152 | 0 | 0.047MB |
| drules | 16384 | 32768 | 49152 | 0 | 0.047MB |
| optemplate | 16384 | 32768 | 49152 | 0 | 0.047MB |
| hosts_templates | 16384 | 32768 | 49152 | 191 | 0.047MB |
| actions | 16384 | 32768 | 49152 | 7 | 0.047MB |
| config | 16384 | 32768 | 49152 | 0 | 0.047MB |
| maintenances_windows | 16384 | 32768 | 49152 | 0 | 0.047MB |
| screens | 16384 | 32768 | 49152 | 53 | 0.047MB |
| opmessage_usr | 16384 | 32768 | 49152 | 6 | 0.047MB |
| hosts_groups | 16384 | 32768 | 49152 | 196 | 0.047MB |
| users_groups | 16384 | 32768 | 49152 | 4 | 0.047MB |
| maintenances_hosts | 16384 | 32768 | 49152 | 0 | 0.047MB |
| screen_usrgrp | 16384 | 32768 | 49152 | 0 | 0.047MB |
| opmessage_grp | 16384 | 32768 | 49152 | 4 | 0.047MB |
| httpstepitem | 16384 | 32768 | 49152 | 6 | 0.047MB |
| autoreg_host | 16384 | 32768 | 49152 | 0 | 0.047MB |
| maintenances_groups | 16384 | 32768 | 49152 | 0 | 0.047MB |
| screen_user | 16384 | 32768 | 49152 | 0 | 0.047MB |
| sysmap_element_trigger | 16384 | 32768 | 49152 | 0 | 0.047MB |
| dashboard_usrgrp | 16384 | 32768 | 49152 | 0 | 0.047MB |
| maintenances | 16384 | 32768 | 49152 | 0 | 0.047MB |
| rights | 16384 | 32768 | 49152 | 23 | 0.047MB |
| slideshows | 16384 | 32768 | 49152 | 0 | 0.047MB |
| dashboard_user | 16384 | 32768 | 49152 | 1 | 0.047MB |
| task | 16384 | 32768 | 49152 | 0 | 0.047MB |
| slideshow_usrgrp | 16384 | 32768 | 49152 | 0 | 0.047MB |
| opgroup | 16384 | 32768 | 49152 | 1 | 0.047MB |
| interface | 16384 | 32768 | 49152 | 28 | 0.047MB |
| tag_filter | 16384 | 32768 | 49152 | 0 | 0.047MB |
| operations | 16384 | 16384 | 32768 | 16 | 0.031MB |
| lld_macro_path | 16384 | 16384 | 32768 | 58 | 0.031MB |
| proxy_history | 16384 | 16384 | 32768 | 0 | 0.031MB |
| opconditions | 16384 | 16384 | 32768 | 0 | 0.031MB |
| corr_operation | 16384 | 16384 | 32768 | 0 | 0.031MB |
| services | 16384 | 16384 | 32768 | 0 | 0.031MB |
| globalmacro | 16384 | 16384 | 32768 | 0 | 0.031MB |
| proxy_autoreg_host | 16384 | 16384 | 32768 | 0 | 0.031MB |
| sessions | 16384 | 16384 | 32768 | 175 | 0.031MB |
| services_times | 16384 | 16384 | 32768 | 0 | 0.031MB |
| expressions | 16384 | 16384 | 32768 | 10 | 0.031MB |
| corr_condition | 16384 | 16384 | 32768 | 0 | 0.031MB |
| widget | 16384 | 16384 | 32768 | 15 | 0.031MB |
| problem_tag | 16384 | 16384 | 32768 | 10 | 0.031MB |
| opcommand | 16384 | 16384 | 32768 | 0 | 0.031MB |
| history_log | 16384 | 16384 | 32768 | 56 | 0.031MB |
| config_autoreg_tls | 16384 | 16384 | 32768 | 0 | 0.031MB |
| valuemaps | 16384 | 16384 | 32768 | 102 | 0.031MB |
| sysmap_url | 16384 | 16384 | 32768 | 0 | 0.031MB |
| httptest_field | 16384 | 16384 | 32768 | 0 | 0.031MB |
| dhosts | 16384 | 16384 | 32768 | 35 | 0.031MB |
| media_type_param | 16384 | 16384 | 32768 | 123 | 0.031MB |
| corr_condition_group | 16384 | 16384 | 32768 | 0 | 0.031MB |
| usrgrp | 16384 | 16384 | 32768 | 6 | 0.031MB |
| sysmap_shape | 16384 | 16384 | 32768 | 0 | 0.031MB |
| dchecks | 16384 | 16384 | 32768 | 1 | 0.031MB |
| group_discovery | 16384 | 16384 | 32768 | 0 | 0.031MB |
| conditions | 16384 | 16384 | 32768 | 51 | 0.031MB |
| sysmap_element_url | 16384 | 16384 | 32768 | 0 | 0.031MB |
| users | 16384 | 16384 | 32768 | 3 | 0.031MB |
| opmessage | 16384 | 16384 | 32768 | 13 | 0.031MB |
| httpstep_field | 16384 | 16384 | 32768 | 0 | 0.031MB |
| interface_discovery | 16384 | 16384 | 32768 | 0 | 0.031MB |
| host_tag | 16384 | 16384 | 32768 | 5 | 0.031MB |
| httpstep | 16384 | 16384 | 32768 | 2 | 0.031MB |
| graph_theme | 16384 | 16384 | 32768 | 4 | 0.031MB |
| regexps | 16384 | 16384 | 32768 | 5 | 0.031MB |
| trigger_tag | 16384 | 16384 | 32768 | 85 | 0.031MB |
| maintenance_tag | 16384 | 16384 | 32768 | 0 | 0.031MB |
| hstgrp | 16384 | 16384 | 32768 | 25 | 0.031MB |
| dashboard | 16384 | 16384 | 32768 | 2 | 0.031MB |
| graph_discovery | 16384 | 16384 | 32768 | 235 | 0.031MB |
| globalvars | 16384 | 0 | 16384 | 0 | 0.016MB |
| ids | 16384 | 0 | 16384 | 56 | 0.016MB |
| corr_condition_tagvalue | 16384 | 0 | 16384 | 0 | 0.016MB |
| corr_condition_tagpair | 16384 | 0 | 16384 | 0 | 0.016MB |
| timeperiods | 16384 | 0 | 16384 | 0 | 0.016MB |
| housekeeper | 16384 | 0 | 16384 | 0 | 0.016MB |
| task_remote_command_result | 16384 | 0 | 16384 | 0 | 0.016MB |
| corr_condition_tag | 16384 | 0 | 16384 | 0 | 0.016MB |
| task_remote_command | 16384 | 0 | 16384 | 0 | 0.016MB |
| task_close_problem | 16384 | 0 | 16384 | 0 | 0.016MB |
| dbversion | 16384 | 0 | 16384 | 0 | 0.016MB |
| task_check_now | 16384 | 0 | 16384 | 0 | 0.016MB |
| task_acknowledge | 16384 | 0 | 16384 | 0 | 0.016MB |
| opinventory | 16384 | 0 | 16384 | 0 | 0.016MB |
| host_inventory | 16384 | 0 | 16384 | 0 | 0.016MB |
+----------------------------+-------------+--------------+------------+------------+------------+
149 rows in set (0.01 sec)
2）优化表的大小
清理表的村存储数据：
表数据只需要保留一个月，删除某个表超时一个月的数据；
删除表数据，需要获取获取时间戳，删除表数据后，执行optimize table table_name 立刻释放磁盘空间
【时间戳在线换算】
#删除之前的1656864000存储的数据，通过时间戳换算，数据过大，可能很慢
mysql> delete from zabbix.history_uint where clock < 1656864000;
Query OK, 184017 rows affected (1 min 13.99 sec)
#立即释放磁盘占用空间
mysql> optimize table zabbix.history_uint;
+---------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------------+----------+----------+-------------------------------------------------------------------+
| zabbix.history_uint | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| zabbix.history_uint | optimize | status | OK |
+---------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (3 min 7.93 sec)
#继续查看表的大小（确认到表已经减少了）
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='zabbix' and table_name='history_uint';
+----------+
| data |
+----------+
| 542.98MB |
+----------+
1 row in set (0.00 sec)
3）服务器上查询确认表大小
筛选 查看数据库大小
#文件大小查看(查看zabbix库中表的ibd文件大小)
shell> du -s /var/lib/mysql/zabbix/* |sort -rn |head -10
847876 /var/lib/mysql/zabbix/history_uint.ibd
368644 /var/lib/mysql/zabbix/trends_uint.ibd
290820 /var/lib/mysql/zabbix/history.ibd
135172 /var/lib/mysql/zabbix/history_str.ibd
110596 /var/lib/mysql/zabbix/trends.ibd
57348 /var/lib/mysql/zabbix/history_text.ibd
18436 /var/lib/mysql/zabbix/alerts.ibd
14340 /var/lib/mysql/zabbix/events.ibd
14336 /var/lib/mysql/zabbix/items.ibd
10244 /var/lib/mysql/zabbix/event_recovery.ibd
4）数据库分区自动化脚本
【数据库分区脚本】
#数据库分区脚本
shell> wget https://dl.cactifans.com/zabbix/partitiontables_gt_zbx34.sh
#执行脚本即可（脚本配置无问题）
shell> ./partitiontables_gt_zbx34.sh
#查看分区后的表数据（history）
mysql> use zabbix;
mysql> select partition_name ,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='history';
脚本基础应用配置：
#优化脚本（基础配置）
shell> egrep "^[^#]" partitiontables_gt_zbx34.sh
EMAIL="zeny@foxmail.com" #邮箱
daily_history_min=30 #历史数据存储保留时间
monthly_history_min=12 #趋势数据存储时间配置
first_year=`date +"%Y"`
last_year=$first_year
cur_month=`date +"%m"|sed 's/^0*//'`
if [ $cur_month -eq 12 ]; then
last_year=$((first_year+1))
cur_month=1
fi
y=`date +"%Y"`
SQL="/tmp/partition.sql"
PATHTOCRON="/usr/local/zabbix/cron.d"
PATHTOMAILBIN="/usr/bin/mail"
DUMP_FILE=/tmp/zabbix.sql
function usage {
cat <<_EOF_
$0 [-h host][-u user][-p password][-d min_days][-y startyear][-n][-s][-e email_address][-b]
-h host database host
-u user db user
-p password user password
-d min_days Minimum number of days of history to keep (default: $daily_history_min)
-m min_months Minimum number of months to keep trends (default: $monthly_history_min)
-y startyear First year to set up with partitions
-n noninteractive Run without questions - careful, make sure you know what is going to happen. Needs my.cnf with correct permissions.
-b backup Create backup of DB in $DUMP_FILE before alterations (only works with non-interactive mode, -n)
-s simulate Create SQL file that would be executed for examination ($SQL)
-e email Email address to receive partition update report (default: $EMAIL)
After running this script, don't forget to disable housekeeping if
you didn't have the script disable it, and add the following cronjob
### Option: DisableHousekeeping
# If set to 1, disables housekeeping.
#
# Mandatory: no
# Range: 0-1
################### Uncomment and change the following line to 1 in
################### Then restart the zabbix server
DisableHousekeeping=1
Cron job
0 0 * * * $PATHTOCRON/housekeeping.sh
_EOF_
exit
}
DBHOST=localhost #数据库连接地址
DBUSER=zabbix #数据库连接用户
DBPASS=zabbix #数据库登录密码
SIMULATE=0
NONINTERACTIVE=0
BACKUP=0
while getopts "m:nsbe:h:u:p:d:y:?h" flag; do
case $flag in
h) DBHOST=$OPTARG ;;
u) DBUSER=$OPTARG ;;
p) DBPASS=$OPTARG ;;
e) EMAIL=$OPTARG ;;
s) SIMULATE=1 ;;
n) NONINTERACTIVE=1 ;;
b) BACKUP=1 ;;
d) h=$OPTARG
if [ $h -gt 0 ] 2>/dev/null; then
daily_history_min=$h
else
echo "Invalid daily history min, exiting"
exit 1
fi
;;
m) h=$OPTARG
if [ $h -gt 0 ] 2>/dev/null; then
monthly_history_min=$h
else
echo "Invalid monthly history min, exiting"
exit 1
fi
;;
y) yy=$OPTARG
if [ $yy -lt $y -a $yy -gt 2000 ] 2>/dev/null; then
first_year=$yy
else
echo "Invalid year, exiting"
exit 1
fi
;;
?|h) usage ;;
esac
done
shift $((OPTIND-1))
if [ $NONINTERACTIVE != 1 ]; then
echo "Ready to partition tables."
fi
if [ $SIMULATE = 0 ]; then
if [ $NONINTERACTIVE = 1 ]; then
mysql -B -h $DBHOST -e "GRANT CREATE ROUTINE ON zabbix.* TO '$DBUSER'@'localhost';"
mysql -h $DBHOST -e "GRANT LOCK TABLES ON zabbix.* TO '$DBUSER'@'$DBHOST' IDENTIFIED BY '$DBPASS';"
if [ $BACKUP = 1 ]; then
mysqldump --opt -h $DBHOST -u $DBUSER -p$DBPASS zabbix --result-file=$DUMP_FILE
rc=$?
if [ $rc -ne 0 ]; then
echo "Error during mysqldump, exit code: $rc"
fi
fi
else
echo -e "\nReady to update permissions of Zabbix user to create routines\n"
echo -n "Enter root DB user: "
read DBADMINUSER
echo -n "Enter $DBADMINUSER password: "
read DBADMINPASS
mysql -B -h $DBHOST -u $DBADMINUSER -p$DBADMINPASS -e "GRANT CREATE ROUTINE ON zabbix.* TO '$DBUSER'@'localhost';"
echo -e "\n"
echo -ne "\nDo you want to backup the database (recommended) (Y/n): "
read yn
if [ "$yn" != "n" -a "$yn" != "N" ]; then
echo -e "\nEnter output file, press return for default of $DUMP_FILE"
read df
[ "$df" != "" ] && DUMP_FILE=$df
#
# Lock tables is needed for a good mysqldump
#
echo "GRANT LOCK TABLES ON zabbix.* TO '${DBUSER}'@'${DBHOST}' IDENTIFIED BY '${DBPASS}';" | mysql -h${DBHOST} -u${DBADMINUSER} --password=${DBADMINPASS}
mysqldump --opt -h ${DBHOST} -u ${DBUSER} -p${DBPASS} zabbix --result-file=${DUMP_FILE}
rc=$?
if [ $rc -ne 0 ]; then
echo "Error during mysqldump, rc: $rc"
echo "Do you wish to continue (y/N): "
read yn
[ "yn" != "y" -a "$yn" != "Y" ] && exit
else
echo "Mysqldump succeeded!, proceeding with upgrade..."
fi
else
echo "Are you certain you have a backup (y/N): "
read yn
[ "$yn" != 'y' -a "$yn" != "Y" ] && exit
fi
fi
fi
if [ $NONINTERACTIVE = 1 ]; then
yn='y'
else
echo -e "\n\nReady to proceed:"
echo -e "\nStarting yearly partioning at: $first_year"
echo "and ending at: $last_year"
echo "With $daily_history_min days of daily history"
echo -e "\n\nReady to proceed (Y/n): "
read yn
[ "$yn" = 'n' -o "$yn" = "N" ] && exit
fi
DAILY="history history_log history_str history_text history_uint"
DAILY_IDS="itemid id itemid id itemid"
MONTHLY="trends trends_uint"
MONTHLY_IDS=""
TABLES="$DAILY $MONTHLY"
IDS="$DAILY_IDS $MONTHLY_IDS"
if [ $NONINTERACTIVE != 1 ]; then
echo "Use zabbix; SELECT 'Altering tables';" >$SQL
else
echo "Use zabbix;" >$SQL
fi
cnt=0
for i in $TABLES; do
if [ $NONINTERACTIVE != 1 ]; then
echo "Altering table: $i"
echo "SELECT '$i';" >>$SQL
fi
cnt=$((cnt+1))
case $i in
history_log)
#echo "ALTER TABLE $i DROP KEY history_log_2;" >>$SQL
#echo "ALTER TABLE $i ADD KEY history_log_2(itemid, id);" >>$SQL
#echo "ALTER TABLE $i DROP PRIMARY KEY ;" >>$SQL
#id=`echo $IDS | cut -f$cnt -d" "`
#echo "ALTER TABLE $i ADD KEY ${i}id ($id);" >>$SQL
;;
history_text)
#echo "ALTER TABLE $i DROP KEY history_text_2;" >>$SQL
#echo "ALTER TABLE $i ADD KEY history_text_2 (itemid, clock);" >>$SQL
#echo "ALTER TABLE $i DROP PRIMARY KEY ;" >>$SQL
#id=`echo $IDS | cut -f$cnt -d" "`
#echo "ALTER TABLE $i ADD KEY ${i}id ($id);" >>$SQL
;;
esac
done
echo -en "\n" >>$SQL
for i in $MONTHLY; do
if [ $NONINTERACTIVE != 1 ]; then
echo "Creating monthly partitions for table: $i"
echo "SELECT '$i';" >>$SQL
fi
echo "ALTER TABLE $i PARTITION BY RANGE( clock ) (" >>$SQL
for y in `seq $first_year $last_year`; do
last_month=12
[ $y -eq $last_year ] && last_month=$((cur_month+1))
for m in `seq 1 $last_month`; do
[ $m -lt 10 ] && m="0$m"
ms=`date +"%Y-%m-01" -d "$m/01/$y +1 month"`
pname="p${y}${m}"
echo -n "PARTITION $pname VALUES LESS THAN (UNIX_TIMESTAMP(\"$ms 00:00:00\"))" >>$SQL
[ $m -ne $last_month -o $y -ne $last_year ] && echo -n "," >>$SQL
echo -ne "\n" >>$SQL
done
done
echo ");" >>$SQL
done
for i in $DAILY; do
if [ $NONINTERACTIVE != 1 ]; then
echo "Creating daily partitions for table: $i"
echo "SELECT '$i';" >>$SQL
fi
echo "ALTER TABLE $i PARTITION BY RANGE( clock ) (" >>$SQL
for d in `seq -$daily_history_min 2`; do
ds=`date +"%Y-%m-%d" -d "$d day +1 day"`
pname=`date +"%Y%m%d" -d "$d day"`
echo -n "PARTITION p$pname VALUES LESS THAN (UNIX_TIMESTAMP(\"$ds 00:00:00\"))" >>$SQL
[ $d -ne 2 ] && echo -n "," >>$SQL
echo -ne "\n" >>$SQL
done
echo ");" >>$SQL
done
if [ $NONINTERACTIVE != 1 ]; then
cat >>$SQL <<_EOF_
SELECT "Installing procedures";
_EOF_
fi
cat >>$SQL <<_EOF_
/**************************************************************
MySQL Auto Partitioning Procedure for Zabbix 1.8
http://zabbixzone.com/zabbix/partitioning-tables/
Author: Ricardo Santos (rsantos at gmail.com)
Version: 20110518
**************************************************************/
DELIMITER //
DROP PROCEDURE IF EXISTS zabbix.create_zabbix_partitions; //
CREATE PROCEDURE zabbix.create_zabbix_partitions ()
BEGIN
_EOF_
for i in $DAILY; do
echo " CALL zabbix.create_next_partitions(\"zabbix\",\"$i\");" >>$SQL
echo " CALL zabbix.drop_old_partitions(\"zabbix\",\"$i\");" >>$SQL
done
echo -en "\n" >>$SQL
for i in $MONTHLY; do
echo " CALL zabbix.create_next_monthly_partitions(\"zabbix\",\"$i\");" >>$SQL
echo " CALL zabbix.drop_old_monthly_partitions(\"zabbix\",\"$i\");" >>$SQL
done
cat >>$SQL <<_EOF_
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 = $daily_history_min;
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_next_monthly_partitions; //
CREATE PROCEDURE zabbix.create_next_monthly_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE NEXTCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @totalmonths = 3;
SET @i = 1;
createloop: LOOP
SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i MONTH);
SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m' );
SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 MONTH),'%Y-%m-01 00:00:00'));
CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
SET @i=@i+1;
IF @i > @totalmonths THEN
LEAVE createloop;
END IF;
END LOOP;
END //
DROP PROCEDURE IF EXISTS zabbix.drop_old_monthly_partitions; //
CREATE PROCEDURE zabbix.drop_old_monthly_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE OLDCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @minmonths = $monthly_history_min;
SET @maxmonths = @minmonths+24;
SET @i = @maxmonths;
droploop: LOOP
SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i MONTH);
SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m' );
CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
SET @i=@i-1;
IF @i <= @minmonths 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 ;
_EOF_
if [ $SIMULATE = 1 ]; then
exit 0
fi
if [ $NONINTERACTIVE = 1 ]; then
yn='y'
else
echo -e "\n\nReady to apply script to database, this may take a while.(Y/n): "
read yn
fi
if [ "$yn" != "n" -a "$yn" != "N" ]; then
mysql --skip-column-names -h ${DBHOST} -u ${DBUSER} -p${DBPASS} <$SQL
fi
conf=/etc/zabbix/zabbix_server.conf
if [ $NONINTERACTIVE = 1 ]; then
yn='y'
else
echo -e "\nIf Zabbix Version = 2.0 \nDo you want to update the /etc/zabbix/zabbix_server.conf"
echo -n "to disable housekeeping (Y/n): "
read yn
fi
if [ "$yn" != "n" -a "$yn" != "N" ]; then
cp $conf ${conf}.bak
sed -i "s/^# DisableHousekeeping=0/DisableHousekeeping=1/" $conf
sed -i "s/^DisableHousekeeping=0/DisableHousekeeping=1/" $conf
/etc/init.d/zabbix-server stop
sleep 5
/etc/init.d/zabbix-server start 2>&1 > /dev/null
fi
tmpfile=/tmp/cron$$
if [ $NONINTERACTIVE = 1 ]; then
yn='y'
else
echo -ne "\nDo you want to update the crontab (Y/n): "
read yn
fi
if [ "$yn" != "n" -a "$yn" != "N" ]; then
where=
while [ "$where" = "" ]; do
if [ $NONINTERACTIVE = 1 ]; then
where='Y'
else
echo "The crontab entry can be either in /etc/cron.daily, or added"
echo -e "to the crontab for root\n"
echo -n "Do you want to add this to the /etc/cron.daily directory (Y/n): "
read where
fi
[ "$where" = "" -o "$where" = "y" ] && where="Y"
if [ "$where" != "y" -a "$where" != "Y" -a "$where" != "n" -a "$where" != "N" ]; then
where=""
echo "Response not recognized, please try again"
fi
done
if [ $NONINTERACTIVE != 1 ]; then
echo -en "\nEnter email of who should get the daily housekeeping reports: "
read mailto
fi
[ "$mailto" = "" ] && mailto=$EMAIL
mkdir -p $PATHTOCRON
cat >$PATHTOCRON/housekeeping.sh <<_EOF_
MAILTO=$mailto
tmpfile=/tmp/housekeeping\$\$
date >\$tmpfile
/usr/bin/mysql --skip-column-names -B -h localhost -u${DBUSER} -p${DBPASS} zabbix -e "CALL create_zabbix_partitions();" >>\$tmpfile 2>&1
$PATHTOMAILBIN -s "Zabbix MySql Partition Housekeeping" \$MAILTO <\$tmpfile
rm -f \$tmpfile
_EOF_
chmod +x $PATHTOCRON/housekeeping.sh
chown -R zabbix.zabbix /usr/local/zabbix
if [ "$where" = "Y" ]; then
cat >/etc/cron.daily/zabbixhousekeeping <<_EOF_
$PATHTOCRON/housekeeping.sh
_EOF_
chmod +x /etc/cron.daily/zabbixhousekeeping
else
crontab -l >$tmpfile
cat >>$tmpfile <<_EOF_
0 0 * * * $PATHTOCRON/housekeeping.sh
_EOF_
crontab $tmpfile
rm $tmpfile
fi
fi