本文介绍了如何针对线上Zabbix数据库中history和history_uint表的大量数据进行MySQL表分区，以降低数据库压力。首先，关闭Zabbix的housekeeper功能，然后在MySQL双主环境下停止复制线程，备份原有表并创建新的分区表。通过脚本自动生成分区和插入数据的SQL语句，按指定粒度和数量进行分区，并逐步将旧数据导入新表。分区完成后，设置定时脚本以保持分区的最新状态，有效改善了数据库性能。
一. 线上 zabbix 数据库的总数据量达到了180G，慢查询日志都是由于 history, history_uint 这两个表引起的mysql> selecttable_name,(data_length+index_length)/1024/1024 as total_mb, table_rows frominformation_schema.tables where table_name='history';
+------------+----------------+------------+
| table_name | total_mb | table_rows |
+------------+----------------+------------+
| history | 38281.28125000 | 225689376 |
+------------+----------------+------------+
1 row in set (0.00 sec)
mysql> selecttable_name,(data_length+index_length)/1024/1024 as total_mb, table_rows frominformation_schema.tables where table_name='history_uint';
+--------------+----------------+------------+
| table_name | total_mb | table_rows |
+--------------+----------------+------------+
| history_uint | 43551.34375000 | 189510565 |
+--------------+----------------+------------+
1 row in set (0.05 sec)
决定对history, history_uint 进行表分区，来降低 mysql 的压力
注：在进行 mysql 分区时先在zabbix前端将 housekeeper 的功能关闭，关闭方法：
Administration --> General --> (右侧下拉框) Housekeeping
介绍，不过他们是使用存储过程(函数)来完成的，这里使用脚本来完成。
这里线上环境是的情况是mysql双主，把两个mysql的复制线程都停掉，然后在backup上执行脚本，执行OK之后，让primary去同步backup的数据
脚本实现的功能：将history重命名为history_bak
根据分区粒度，分区的开始时间，分区的数量生成要分区的sql语句，建立新的分区的history表
生成从history_bak里抽取数据导入history表的insert语句，将旧表的数据导入新表，生成的建表语句也是根据分区粒度来进行的
执行建表语句，创建history为分区表
5.执行update_history_1.sh脚本将旧表数据导入新表，这里因数据大小不一致，时间可能不同，脚本如下[root@zabbix_server zabbix_partitions]# cat partition.sh
#!/bin/bash
#
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/bin:/bin:/usr/sbin:/sbin:/root/bin
# day * 24 * 60 * 60
# 这个变量决定的是分区的粒度，3 代表每3天一个分区
part_interval=$[1*24*60*60]
# 这个变量决定的是分区的总数量，比如 part_interval 为3，这里为60，那么时间跨度将是180天
# ，分区数量有上限，别超过1024个
part_count=480
# 这个变量表示你的分区表要从哪一天开始，这个值最好是观察一下，history 里面最小的 clock 值
# ，然后转换为相关的时间
part_begin=20160209
part_times=$(date -d "${part_begin}" +%s)
#yet_date=$(date -d ${part_begin} +%Y-%m-%d)
# 生成分区表的字符串
part_string=$(
for i in `seq 1 ${part_count}`;do
# 根据 part_times 这个时间戳生成一个类似 20160901 的可读性好的日期名
part_name=$(date -d @${part_times} +%Y%m%d)
# 同上，这个是在 UNIX_TIMESTAMP() 函数中要用的名字
yet_date=$(date -d @${part_times} +%Y-%m-%d)
if [ $i == $part_count ];then
echo "partition p${part_name} values less than(UNIX_TIMESTAMP(\"${yet_date} 00:00:00\"))"
else
echo "partition p${part_name} values less than(UNIX_TIMESTAMP(\"${yet_date} 00:00:00\")),"
fi
# 开始的时间 加上 间隔时间，生成新的时间戳
part_times=$[part_times+part_interval]
done
)
# 这里如果你不愿意去查一下你数据里最早的时间，那么就把第一个变量打开，第二个注释掉,这样他自己去查
#history_start=`mysql -Bse 'select clock from zabbix.history order by clock limit 1;'`
history_start=1455003811
#echo $history_start
now_date=$(date +%s)
insert_string=''
state=true
> /tmp/insert_zabbix.txt
while $state;do
history_para=$[history_start+part_interval]
diff_time=$[now_date-history_para]
if [ ${diff_time} -ge ${part_interval} ];then
insert_string="insert into zabbix.history select * from zabbix.history_bak where clock between ${history_start} and ${history_para};"
echo "${insert_string}" >> /tmp/insert_zabbix.txt
else
insert_string="insert into zabbix.history select * from zabbix.history_bak where clock >= ${history_start};"
echo "${insert_string}" >> /tmp/insert_zabbix.txt
state=false
fi
history_start=$[history_para+1]
done
echo "$part_string" > /tmp/part_string.txt
echo "${insert_string}"
echo "backup history to history_bak;"
/usr/local/mysql/bin/mysql zabbix -e 'alter table history rename history_bak;'
echo "create partions table history"
/usr/local/mysql/bin/mysql zabbix -e "CREATE TABLE history (
itemid bigint(20) unsigned NOT NULL,
clock int(11) NOT NULL DEFAULT '0',
value double(16,4) NOT NULL DEFAULT '0.0000',
ns int(11) NOT NULL DEFAULT '0',
KEY history_1 (itemid,clock)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 partition by range (clock)(${part_string});"
脚本执行后，已经将 history 更改为 history_bak, 建立了新的带分区的 history了。而且在 /tmp/下生成了两个文件[root@zabbix_server tmp]# ls -l
total 1572
-rw-r--r-- 1 root root 34541 Dec 29 15:42 insert_zabbix.txt
-rw-r--r-- 1 root root 36959 Dec 29 15:42 part_string.txt
insert_zabbix.txt 是要将数据导入新表的语句[root@zabbix_server tmp]# tail -n 5 insert_zabbix.txt
insert into zabbix.history select * from zabbix.history_bak where clock between 1482479329 and 1482565729;
insert into zabbix.history select * from zabbix.history_bak where clock between 1482565730 and 1482652130;
insert into zabbix.history select * from zabbix.history_bak where clock between 1482652131 and 1482738531;
insert into zabbix.history select * from zabbix.history_bak where clock between 1482738532 and 1482824932;
insert into zabbix.history select * from zabbix.history_bak where clock >= 1482824933;
part_string.txt 是建表时的 partition 的字段：[root@zabbix_server tmp]# tail -n 5 part_string.txt
partition p20170529 values less than(UNIX_TIMESTAMP("2017-05-29 00:00:00")),
partition p20170530 values less than(UNIX_TIMESTAMP("2017-05-30 00:00:00")),
partition p20170531 values less than(UNIX_TIMESTAMP("2017-05-31 00:00:00")),
partition p20170601 values less than(UNIX_TIMESTAMP("2017-06-01 00:00:00")),
partition p20170602 values less than(UNIX_TIMESTAMP("2017-06-02 00:00:00"))
这样作是有个记录，当你不确定时，可以先把脚本现在的 mysql 注释掉，然后运行下脚本，观察下是否OK，再来进行 mysql 的操作。
6.再看将数据导入新表的脚本[root@zabbix_server zabbix_partitions]# cat update_history_1.sh
#!/bin/bash
export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
> /tmp/$0.out
tac /tmp/insert_zabbix.txt | while read line;do
echo `date` >> /tmp/$0.out
mysql -e "$line" &>> /tmp/$0.out
echo "$line" >> /tmp/$0.out
echo "sleeping 60 seconds ......" >> /tmp/$0.out
sleep 60
done
在我这里因为数据量大，如果执行全表导入的话，可能会出现中断，中断之后也很难确定是在哪里断掉的，所以将语句分开执行，从最后一条开始执行的话，还不影响平常前端的观察。sleep 60 秒的作用可以在执行完某一条语句后中断脚本，这样下次再开始脚本，也知道从哪里继续。
如果你的数据量不大，你完全可以自己执行一个全里的语句，导过来就好了。
注： 脚本使用事项：建立自己的my.cnf 配置文件供链接mysql使用，我这里配置在 /root/.my.cnf里：[root@dockertest zabbix_partition]# cat /root/.my.cnf
[client]
user=root
host=172.18.0.3
password=******
你也可以在 mysql 那里添加用户名密码选项。
2. 更改 PATH 环境变量，和自己想要定义的分区选项，比如分区粒度，分区数量等等。最好是在自己的测试环境里先搞搞再说。即使你测试过了，也在生产环境中先备份一下再执行操作，安全第一。
三. 分区效果
我这里在数据导入大部分之后，mysql 的慢查询日志已经没有新的记录了。磁盘的 IO 也有所下降，比之前下降了 20% 左右。毕竟才刚刚导入。还有待后续观察。
分区完成后，还要定时的更新分区。你可以选择手动的到时更新一下。当然这里为了方便，已经写好了脚本：[root@zabbix_server zabbix_partition]# cat update_partition.sh
#!/bin/bash
export PATH=/usr/local/mysql/bin:/usr/lib64/qt-3.3/bin:/bin:/usr/local/sbin:/usr/bin:/bin:/usr/sbin:/sbin:/root/bin
# 保留分区的天数,以此为基准删除180天以前的分区
KeepPartDays=180
KeepPartSeconds=$[KeepPartDays*24*60*60]
# 当前时间
now_seconds=$(date +%s)
# 新分区的时间粒度
part_interval=$[1*24*60*60]
# 以当前时间为基准的未用的新分区的数量
new_parts=90
NewPartSeconds=$[now_seconds+new_parts*part_interval]
# 需要来更新分区的表名
part_table=(history history_uint)
#out_file=/tmp/${0/.sh/.out}
out_file=/tmp/$(basename ${0/.sh/.out})
mysql='mysql --defaults-file=/root/zabbix_partitions/.my.cnf'
part_max() {
${mysql} information_schema -Bse "select MAX(PARTITION_DESCRIPTION) from partitions where table_name='$1' and table_schema='zabbix';"
}
part_min() {
${mysql} information_schema -Bse "select MIN(PARTITION_DESCRIPTION) from partitions where table_name='$1' and table_schema='zabbix';"
}
echo "$(date) ------------------------------" >> ${out_file}
for i in ${part_table[@]};do
### delete old partitions
old_part_desc=$(part_min "${i}")
old_keep_seconds=$[now_seconds-KeepPartSeconds]
old_part_name=$(${mysql} information_schema -Bse "select partition_name from partitions where table_name='${i}' and table_schema='zabbix' and PARTITION_DESCRIPTION
#echo "${i}:${old_part_name}"
if [[ -z ${old_part_name} ]];then
echo "old_part_name is empty;"
exit 10
else
echo ${old_part_name}
for j in ${old_part_name};do
echo "`date +%Y%m%d%H%M%S`:delete $i:$j" >> ${out_file}
${mysql} zabbix -e "alter table ${i} drop partition ${j};" &>> ${out_file}
done
fi
### add new parittions
last_part_desc=$(part_max "${i}")
diff_seconds=$[NewPartSeconds-last_part_desc]
new_part_num=$[diff_seconds/part_interval]
new_part_desc=$[last_part_desc+part_interval]
for ((k=1;k<=${new_part_num};k++));do
new_part_name=p$(date -d @${new_part_desc} +%Y%m%d)
add_statement="partition ${new_part_name} values less than(${new_part_desc})"
echo "`date +%Y%m%d%H%M%S`:${i}:${k} ${new_part_name}:${add_statement}" >> ${out_file}
${mysql} zabbix -e "alter table ${i} add partition (partition ${new_part_name} values less than(${new_part_desc}));" &>> ${out_file}
new_part_desc=$[new_part_desc+part_interval]
done
done
写到计划任务里：# update zabbix table partition
00 02 * * * /bin/bash /root/zabbix_partition/update_partition.sh
好了，分区搞定。zabbix可以放心的存储数据了。当然这个问题还有更好的解决实现方法。只不过这里如此实现了而已。所以记录一下也是好的。
此文章会在我遇到问题时逐步更新。谢谢观看！
扫一扫
349
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
