zabbix3.2对某几个大表进行分区操作
添加了将近1000台主机，数据库中的历史数据越来越多了。出现了zabbix自带的housekeeper清理历史数据的时候，造成查数据慢并且触发了很多报警信息（比如Zabbix housekeeper processes more than 75% busy）。housekeeper默认一小时清理一次旧的数据。在server配置文件中这两行可以定义：
HousekeepingFrequency=1 zabbix执行Housekeeping的频率，单位为hours
MaxHousekeeperDelete=500每次最多删除历史数据的行
但是housekeeper清理过中，会导致数据库负载增高，从而影响读写性能。
所以我们对几个历史数据表做分区表。按照时间（每天）为单位，把历史数据存到各个分区表中，这样做能加快查询速度、快速清理过去一时间的历史数据（删除分区表）
1、先查看每个表所占容量和行数，可以看到history的表很大
MariaDB > select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows from information_schema.tables where table_schema='zabbix';
+----------------------------+---------------+------------+
| table_name | total_mb | table_rows |
+----------------------------+---------------+------------+
| acknowledges | 0.06250000 | 0 |
| actions | 0.04687500 | 14 |
| alerts | 5.60937500 | 7316 |
| application_discovery | 0.04687500 | 0 |
| application_prototype | 0.04687500 | 1 |
| application_template | 2.37500000 | 13963 |
| applications | 1.96875000 | 14035 |
| auditlog | 18.54687500 | 100270 |
| auditlog_details | 0.42187500 | 2321 |
| autoreg_host | 0.03125000 | 0 |
| conditions | 0.03125000 | 61 |
| config | 0.04687500 | 1 |
| corr_condition | 0.03125000 | 0 |
| corr_condition_group | 0.03125000 | 0 |
| corr_condition_tag | 0.01562500 | 0 |
| corr_condition_tagpair | 0.01562500 | 0 |
| corr_condition_tagvalue | 0.01562500 | 0 |
| corr_operation | 0.03125000 | 0 |
| correlation | 0.04687500 | 0 |
| dbversion | 0.01562500 | 1 |
| dchecks | 0.03125000 | 1 |
| dhosts | 0.03125000 | 0 |
| drules | 0.04687500 | 1 |
| dservices | 0.04687500 | 0 |
| escalations | 0.03125000 | 169 |
| event_recovery | 14.10937500 | 16990 |
| event_tag | 0.03125000 | 0 |
| events | 41.09375000 | 35770 |
| expressions | 0.03125000 | 6 |
| functions | 10.78125000 | 56230 |
| globalmacro | 0.03125000 | 1 |
| globalvars | 0.01562500 | 0 |
| graph_discovery | 0.03125000 | 88 |
| graph_theme | 0.03125000 | 2 |
| graphs | 6.09375000 | 17348 |
| graphs_items | 12.20312500 | 73151 |
| group_discovery | 0.03125000 | 0 |
| group_prototype | 0.06250000 | 7 |
| groups | 0.03125000 | 19 |
| history | 1873.62500000 | 17753848 |
| history_log | 0.03125000 | 0 |
| history_str | 2.07812500 | 3330 |
| history_text | 0.07812500 | 594 |
| history_uint | 5980.10937500 | 38661029 |
| host_discovery | 0.04687500 | 2 |
| host_inventory | 0.01562500 | 0 |
| hostmacro | 0.03125000 | 0 |
| hosts | 0.48437500 | 725 |
| hosts_groups | 0.15625000 | 715 |
| hosts_templates | 0.15625000 | 859 |
| housekeeper | 0.01562500 | 204 |
| httpstep | 0.03125000 | 3 |
| httpstepitem | 0.04687500 | 9 |
| httptest | 0.07812500 | 3 |
| httptestitem | 0.04687500 | 9 |
| icon_map | 0.04687500 | 0 |
| icon_mapping | 0.04687500 | 0 |
| ids | 0.01562500 | 43 |
| images | 1.53125000 | 191 |
| interface | 0.18750000 | 671 |
| interface_discovery | 0.03125000 | 0 |
| item_application_prototype | 0.04687500 | 1 |
| item_condition | 0.21875000 | 1291 |
| item_discovery | 0.75000000 | 5656 |
| items | 47.15625000 | 98946 |
| items_applications | 17.21875000 | 119577 |
| maintenances | 0.04687500 | 0 |
| maintenances_groups | 0.04687500 | 0 |
| maintenances_hosts | 0.04687500 | 0 |
| maintenances_windows | 0.04687500 | 0 |
| mappings | 0.03125000 | 143 |
| media | 0.04687500 | 15 |
| media_type | 0.03125000 | 6 |
| opcommand | 0.03125000 | 4 |
| opcommand_grp | 0.04687500 | 0 |
| opcommand_hst | 0.04687500 | 4 |
| opconditions | 0.03125000 | 0 |
| operations | 0.03125000 | 29 |
| opgroup | 0.04687500 | 1 |
| opinventory | 0.01562500 | 0 |
| opmessage | 0.03125000 | 23 |
| opmessage_grp | 0.04687500 | 5 |
| opmessage_usr | 0.04687500 | 15 |
| optemplate | 0.04687500 | 1 |
| problem | 4.32812500 | 17405 |
| problem_tag | 0.04687500 | 0 |
| profiles | 0.34375000 | 1641 |
| proxy_autoreg_host | 0.03125000 | 0 |
| proxy_dhistory | 0.03125000 | 0 |
| proxy_history | 0.03125000 | 0 |
| regexps | 0.03125000 | 5 |
| rights | 0.04687500 | 19 |
| screen_user | 0.04687500 | 0 |
| screen_usrgrp | 0.04687500 | 0 |
| screens | 0.04687500 | 14 |
| screens_items | 0.03125000 | 51 |
| scripts | 0.06250000 | 3 |
| service_alarms | 0.04687500 | 0 |
| services | 0.03125000 | 0 |
| services_links | 0.04687500 | 0 |
| services_times | 0.03125000 | 0 |
| sessions | 2.01562500 | 5953 |
| slides | 0.04687500 | 1 |
| slideshow_user | 0.04687500 | 0 |
| slideshow_usrgrp | 0.04687500 | 0 |
| slideshows | 0.04687500 | 1 |
| sysmap_element_url | 0.03125000 | 0 |
| sysmap_url | 0.03125000 | 0 |
| sysmap_user | 0.04687500 | 0 |
| sysmap_usrgrp | 0.04687500 | 0 |
| sysmaps | 0.07812500 | 1 |
| sysmaps_elements | 0.09375000 | 1 |
| sysmaps_link_triggers | 0.04687500 | 0 |
| sysmaps_links | 0.06250000 | 0 |
| task | 0.01562500 | 0 |
| task_close_problem | 0.01562500 | 0 |
| timeperiods | 0.01562500 | 0 |
| trends |133.12500000 | 590089 |
| trends_uint |141.07812500 | 472377 |
| trigger_depends | 0.04687500 | 65 |
| trigger_discovery | 0.03125000 | 106 |
| trigger_tag | 0.03125000 | 0 |
| triggers | 18.31250000 | 46691 |
| users | 0.03125000 | 10 |
| users_groups | 0.04687500 | 11 |
| usrgrp | 0.03125000 | 12 |
| valuemaps | 0.03125000 | 17 |
+----------------------------+---------------+------------+
127 rows in set (1.20 sec)
2、下载分区脚本（适合zabbix 3.2，谢谢网友写的）
# wget https://dl.cactifans.com/zabbix/partitiontables_gt_zbx34.sh 脚本默认详情数据保留30天，趋势数据保留12个月，如需修改，请修改以下内容：
daily_history_min=30
monthly_history_min=12
脚本默认连接数据库信息，更改成你的：
DBHOST=localhost
DBUSER=zabbix
DBPASS=zabbix
赋予执行权限：
# chmod +x partitiontables_gt_zbx34.sh 脚本内容如下：
#!/bin/bash
#
# This script will partition your zabbix database to improve the efficiency.
# It will also create stored procedures to do the necessary housekeeping,
# and create a cronjob to do this on a daily basis
#
# This script inspired by the following:
# http://zabbixzone.com/zabbix/partitioning-tables/
#
# While the basic SQL is from the above page, this script both creates the necessary
# SQL for the desired tables, and can create new partitions as the time goes on
# assuming that the cronjob has been properly entered.
#
#
# Who to email with cron output
#
EMAIL="root@localhost"
#
# How long to keep the daily history
#
daily_history_min=30
#
# How long to keep the monthly history (months)
#
monthly_history_min=12
#
# Years to create the monthly partitions for
#
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 /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';"
# echo "GRANT LOCK TABLES ON zabbix.* TO '${DBUSER}'@'${DBHOST}' IDENTIFIED BY '${DBPASS}';" | mysql -h${DBHOST} -u${DBADMINUSER} --password=${DBADMINPASS}
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"
#"acknowledges alerts auditlog events service_alarms"
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 $pnameVALUES 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$pnameVALUES 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 $SQL $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 $PATHTOCRON/housekeeping.sh >\$tmpfile 2>&1
$PATHTOMAILBIN -s "Zabbix MySql Partition Housekeeping" \$MAILTO /etc/cron.daily/zabbixhousekeeping >$tmpfile $tmpfile
/usr/bin/mysql --skip-column-names -B -h localhost -uzabbix -pzabbix zabbix -e "CALL create_zabbix_partitions();" >>$tmpfile 2>&1
/usr/bin/mail -s "Zabbix MySql Partition Housekeeping" $MAILTOuse zabbix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB > select
-> partition_name part,
-> partition_expression expr,
-> partition_description descr,
-> table_rows
-> from information_schema.partitionswhere
-> table_schema = schema()
-> and table_name='history';
+-----------+--------+------------+------------+
| part | expr | descr | table_rows |
+-----------+--------+------------+------------+
| p20180219 |clock | 1519056000 | 3757 |
| p20180220 |clock | 1519142400 | 6455 |
| p20180221 |clock | 1519228800 | 6887 |
| p20180222 |clock | 1519315200 | 6455 |
| p20180223 |clock | 1519401600 | 6887 |
| p20180224 |clock | 1519488000 | 6574 |
| p20180225 |clock | 1519574400 | 6455 |
| p20180226 |clock | 1519660800 | 6887 |
| p20180227 |clock | 1519747200 | 6455 |
| p20180228 |clock | 1519833600 | 6887 |
| p20180301 |clock | 1519920000 | 6887 |
| p20180302 |clock | 1520006400 | 6574 |
| p20180303 |clock | 1520092800 | 5720 |
| p20180304 |clock | 1520179200 | 6161 |
| p20180305 |clock | 1520265600 | 6574 |
| p20180306 |clock | 1520352000 | 6513 |
| p20180307 |clock | 1520438400 | 6574 |
| p20180308 |clock | 1520524800 | 6469 |
| p20180309 |clock | 1520611200 | 6574 |
| p20180310 |clock | 1520697600 | 6030 |
| p20180311 |clock | 1520784000 | 6161 |
| p20180312 |clock | 1520870400 | 6455 |
| p20180313 |clock | 1520956800 | 6887 |
| p20180314 |clock | 1521043200 | 234759 |
| p20180315 |clock | 1521129600 | 442278 |
| p20180316 |clock | 1521216000 | 441965 |
| p20180317 |clock | 1521302400 | 443530 |
| p20180318 |clock | 1521388800 | 441965 |
| p20180319 |clock | 1521475200 | 441652 |
| p20180320 |clock | 1521561600 | 2345005 |
| p20180321 |clock | 1521648000 | 12088382 |
| p20180322 |clock | 1521734400 | 0 |
| p20180323 |clock | 1521820800 | 0 |
+-----------+--------+------------+------------+
33 rows in set (0.83 sec) 分区表成功，启动zabbix-server服务。
6、取消housekeeper清理
http://s1.运维网.com/images/20180321/1521610666106086.png
http://s1.运维网.com/images/20180321/1521610620806102.png
页:
[1]