一、查询zabbix数据库大小
mysql> select table_schema, concat(truncate(sum(data_length)/1024/1024,2),' mb') as data_size,concat(truncate(sum(index_length)/1024/1024,2),'mb') as index_size from information_schema.tables group by table_schema order by data_size desc; +--------------------+------------+------------+ | table_schema | data_size | index_size | +--------------------+------------+------------+ | zabbix | 8326.06 mb | 3031.85mb | | jumpserver | 26.57 mb | 8.21mb | | mysql | 2.49 mb | 0.21mb | | information_schema | 0.15 mb | 0.00mb | | sys | 0.01 mb | 0.00mb | | performance_schema | 0.00 mb | 0.00mb | +--------------------+------------+------------+ 6 rows in set (0.40 sec)
发现Zabbix数据占用了8G空间
二、查询Zabbix数据库里所有表大小
mysql> select table_name, (data_length+index_length)/1024/1024 as total_mb,table_rows from information_schema.tables where table_schema='zabbix' order by total_mb desc; +----------------------------+---------------+------------+ | table_name | total_mb | table_rows | +----------------------------+---------------+------------+ | history_uint | 5487.29687500 | 56075683 | | history | 4843.39062500 | 51787332 | | trends_uint | 526.92187500 | 7440029 | | history_str | 251.39062500 | 2393973 | | trends | 215.87500000 | 2978883 | | items | 6.21875000 | 10033 | | events | 4.93750000 | 7922 | | alerts | 4.67187500 | 5030 | | items_applications | 2.35937500 | 11510 | | triggers | 1.89062500 | 4359 | | auditlog | 1.85937500 | 6803 | | images | 1.53125000 | 138 | | functions | 0.67187500 | 4746 | | item_discovery | 0.57812500 | 3921 | | graphs_items | 0.51562500 | 3773 | | graphs | 0.50000000 | 1561 | | auditlog_details | 0.42187500 | 2620 | | event_recovery | 0.32812500 | 2936 | | applications | 0.29687500 | 2345 | | item_preproc | 0.29687500 | 3668 | | application_template | 0.28125000 | 1989 | | sessions | 0.23437500 | 1064 | | hosts | 0.17187500 | 167 | | profiles | 0.15625000 | 564 | | sysmaps_elements | 0.15625000 | 95 | | mappings | 0.12500000 | 820 | | widget_field | 0.10937500 | 170 | | graph_discovery | 0.10937500 | 887 | | problem | 0.09375000 | 104 | | item_condition | 0.07812500 | 446 | | sysmaps | 0.07812500 | 13 | | trigger_depends | 0.07812500 | 401 | | httptest | 0.07812500 | 41 | | event_suppress | 0.06250000 | 0 | | escalations | 0.06250000 | 8 | | sysmaps_links | 0.06250000 | 0 | | scripts | 0.06250000 | 3 | | trigger_discovery | 0.06250000 | 648 | | hostmacro | 0.06250000 | 297 | | group_prototype | 0.06250000 | 7 | | acknowledges | 0.06250000 | 207 | | slides | 0.04687500 | 0 | | rights | 0.04687500 | 4 | | maintenances_groups | 0.04687500 | 0 | | host_discovery | 0.04687500 | 2 | | httpstepitem | 0.04687500 | 123 | | item_application_prototype | 0.04687500 | 23 | | autoreg_host | 0.04687500 | 0 | | task | 0.04687500 | 0 | | maintenances | 0.04687500 | 0 | | dservices | 0.04687500 | 30 | | sysmap_element_trigger | 0.04687500 | 0 | | tag_filter | 0.04687500 | 0 | | drules | 0.04687500 | 8 | | slideshows | 0.04687500 | 0 | | users_groups | 0.04687500 | 5 | | services_links | 0.04687500 | 0 | | opcommand_hst | 0.04687500 | 0 | | optemplate | 0.04687500 | 0 | | interface | 0.04687500 | 82 | | slideshow_usrgrp | 0.04687500 | 0 | | opcommand_grp | 0.04687500 | 0 | | opmessage_usr | 0.04687500 | 22 | | sysmaps_link_triggers | 0.04687500 | 0 | | service_alarms | 0.04687500 | 0 | | opmessage_grp | 0.04687500 | 4 | | hosts_templates | 0.04687500 | 263 | | icon_mapping | 0.04687500 | 0 | | application_prototype | 0.04687500 | 23 | | dashboard_usrgrp | 0.04687500 | 0 | | media | 0.04687500 | 9 | | hosts_groups | 0.04687500 | 211 | | icon_map | 0.04687500 | 0 | | application_discovery | 0.04687500 | 8 | | sysmap_usrgrp | 0.04687500 | 0 | | dashboard_user | 0.04687500 | 0 | | opgroup | 0.04687500 | 0 | | httptestitem | 0.04687500 | 123 | | sysmap_user | 0.04687500 | 0 | | screens | 0.04687500 | 12 | | actions | 0.04687500 | 16 | | screen_usrgrp | 0.04687500 | 0 | | maintenances_windows | 0.04687500 | 0 | | correlation | 0.04687500 | 0 | | slideshow_user | 0.04687500 | 0 | | config | 0.04687500 | 0 | | screen_user | 0.04687500 | 0 | | maintenances_hosts | 0.04687500 | 0 | | group_discovery | 0.03125000 | 0 | | conditions | 0.03125000 | 30 | | sysmap_element_url | 0.03125000 | 0 | | problem_tag | 0.03125000 | 0 | | regexps | 0.03125000 | 5 | | httpstep_field | 0.03125000 | 26 | | interface_discovery | 0.03125000 | 0 | | services_times | 0.03125000 | 0 | | maintenance_tag | 0.03125000 | 0 | | history_text | 0.03125000 | 22 | | graph_theme | 0.03125000 | 4 | | httpstep | 0.03125000 | 41 | | dhosts | 0.03125000 | 31 | | widget | 0.03125000 | 23 | | users | 0.03125000 | 3 | | services | 0.03125000 | 0 | | hstgrp | 0.03125000 | 27 | | history_log | 0.03125000 | 0 | | dchecks | 0.03125000 | 8 | | valuemaps | 0.03125000 | 75 | | opcommand | 0.03125000 | 0 | | usrgrp | 0.03125000 | 8 | | media_type | 0.03125000 | 11 | | opmessage | 0.03125000 | 30 | | globalmacro | 0.03125000 | 0 | | trigger_tag | 0.03125000 | 0 | | screens_items | 0.03125000 | 51 | | proxy_history | 0.03125000 | 0 | | corr_condition_group | 0.03125000 | 0 | | expressions | 0.03125000 | 10 | | dashboard | 0.03125000 | 3 | | proxy_dhistory | 0.03125000 | 0 | | corr_condition | 0.03125000 | 0 | | operations | 0.03125000 | 32 | | httptest_field | 0.03125000 | 0 | | sysmap_url | 0.03125000 | 0 | | proxy_autoreg_host | 0.03125000 | 0 | | sysmap_shape | 0.03125000 | 0 | | opconditions | 0.03125000 | 0 | | event_tag | 0.03125000 | 0 | | corr_operation | 0.03125000 | 0 | | timeperiods | 0.01562500 | 0 | | corr_condition_tagvalue | 0.01562500 | 0 | | task_remote_command_result | 0.01562500 | 0 | | corr_condition_tagpair | 0.01562500 | 0 | | task_remote_command | 0.01562500 | 0 | | corr_condition_tag | 0.01562500 | 0 | | task_close_problem | 0.01562500 | 0 | | task_check_now | 0.01562500 | 0 | | housekeeper | 0.01562500 | 0 | | ids | 0.01562500 | 52 | | globalvars | 0.01562500 | 0 | | dbversion | 0.01562500 | 0 | | task_acknowledge | 0.01562500 | 0 | | opinventory | 0.01562500 | 0 | | host_inventory | 0.01562500 | 20 | +----------------------------+---------------+------------+ 144 rows in set (0.02 sec) mysql>
发现history和trends相关表数据量很大，一个是历史数据，一个是趋势数据
需要对这些表进行分区操作，提高效率
三、下载分区脚本
drwxr-xr-x. 4 root root 4096 12月 26 2018 share [root@zabbix_server zabbix-4.0.3]# wget https://dl.cactifans.com/zabbix/partitiontables_gt_zbx34.sh --2019-06-28 11:20:28-- https://dl.cactifans.com/zabbix/partitiontables_gt_zbx34.sh 正在解析主机 dl.cactifans.com... 222.186.135.67 正在连接 dl.cactifans.com|222.186.135.67|:443... 已连接。 已发出 HTTP 请求，正在等待回应... 200 OK 长度：15458 (15K) [application/octet-stream] 正在保存至: “partitiontables_gt_zbx34.sh.1” 100%[===========================================================================================>] 15,458 --.-K/s in 0s 2019-06-28 11:20:28 (428 MB/s) - 已保存 “partitiontables_gt_zbx34.sh.1” [15458/15458]) [root@zabbix_server zabbix-4.0.3]#
四、编辑脚本相关参数
脚本默认详情数据保留30天，趋势数据保留12个月，如需修改，请修改以下内容：
daily_history_min=30monthly_history_min=12脚本默认连接数据库信息，更改成你的：DBHOST=localhostDBUSER=zabbixDBPASS=zabbix[root@zabbix_server zabbix-4.0.3]# chmod +x partitiontables_gt_zbx34.sh
五、执行分区脚本
停止zabbix_server服务
12130 12129 12128) 正在运行... [root@zabbix_server zabbix-4.0.3]# service zabbix_server stop Shutting down zabbix_server: [确定]
[root@zabbix_server zabbix-4.0.3]# service zabbix_server status zabbix_server 已停
执行脚本
[root@zabbix_server zabbix-4.0.3]# ./partitiontables_gt_zbx34.sh Ready to partition tables. Ready to update permissions of Zabbix user to create routines Enter root DB user:
输入数据库用户名和密码
Enter root DB user: root Enter root password: xxxxxxxx mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1133 (42000) at line 1: Can't find any matching row in the user table Do you want to backup the database (recommended) (Y/n): n Are you certain you have a backup (y/N): y Ready to proceed: Starting yearly partioning at: 2019 and ending at: 2019 With 30 days of daily history
Ready to proceed (Y/n): y Altering table: history Altering table: history_log Altering table: history_str Altering table: history_text Altering table: history_uint Altering table: trends Altering table: trends_uint Creating monthly partitions for table: trends Creating monthly partitions for table: trends_uint Creating daily partitions for table: history Creating daily partitions for table: history_log Creating daily partitions for table: history_str Creating daily partitions for table: history_text Creating daily partitions for table: history_uint Ready to apply script to database, this may take a while.(Y/n):
Ready to apply script to database, this may take a while.(Y/n): y mysql: [Warning] Using a password on the command line interface can be insecure. Altering tables history history_log history_str history_text history_uint trends trends_uint trends trends_uint history history_log history_str history_text history_uint Installing procedures If Zabbix Version = 2.0 Do you want to update the /etc/zabbix/zabbix_server.conf to disable housekeeping (Y/n):
这里会耗费很长时间，大概是1个小时左右
If Zabbix Version = 2.0 Do you want to update the /etc/zabbix/zabbix_server.conf to disable housekeeping (Y/n): n Do you want to update the crontab (Y/n): y The crontab entry can be either in /etc/cron.daily, or added to the crontab for root Do you want to add this to the /etc/cron.daily directory (Y/n): y Enter email of who should get the daily housekeeping reports: chengpeng@9500.cn [root@zabbix_server zabbix-4.0.3]#
到此，脚本执行完毕
六、查看生成的定时任务
上面的脚本会在/etc/cron.daily目录下生成一个名称为zabbixhousekeeping的脚本
[root@zabbix_server zabbix-4.0.3]# vim /etc/cron.daily/zabbixhousekeeping 1 #!/bin/bash 2 /usr/local/zabbix/cron.d/housekeeping.sh
里面是执行了usr/local/zabbix/cron.d/housekeeping.sh脚本，打开这个脚本
[root@zabbix_server zabbix-4.0.3]# vim /usr/local/zabbix/cron.d/housekeeping.sh #!/bin/bash MAILTO=chengpeng@9500.cn tmpfile=/tmp/housekeeping$$ date >$tmpfile /usr/bin/mysql --skip-column-names -B -h localhost -uroot -pxxxxxx zabbix -e "CALL create_zabbix_partitions();" >>$tmpfile 2>&1 /usr/bin/mail -s "Zabbix MySql Partition Housekeeping" $MAILTO <$tmpfile rm -f $tmpfile
这个脚本是调用了Mysql的创建分区表存储过程
七、查看分区结果
1、首选查看zabbix数据库大小，
分区之前
mysql> select table_schema, concat(truncate(sum(data_length)/1024/1024,2),' mb') as data_size,concat(truncate(sum(index_length)/1024/1024,2),'mb') as index_size from information_schema.tables group by table_schema order by data_size desc; +--------------------+------------+------------+ | table_schema | data_size | index_size | +--------------------+------------+------------+ | zabbix | 8326.06 mb | 3031.85mb | | jumpserver | 26.57 mb | 8.21mb | | mysql | 2.49 mb | 0.21mb | | information_schema | 0.15 mb | 0.00mb | | sys | 0.01 mb | 0.00mb | | performance_schema | 0.00 mb | 0.00mb | +--------------------+------------+------------+ rows in set (0.40 sec)
分区之后
mysql> select table_schema, concat(truncate(sum(data_length)/1024/1024,2),' mb') as data_size,concat(truncate(sum(index_length)/1024/1024,2),'mb') as index_size from information_schema.tables group by table_schema order by data_size desc; +--------------------+------------+------------+ | table_schema | data_size | index_size | +--------------------+------------+------------+ | zabbix | 6173.34 mb | 3859.25mb | | jumpserver | 26.57 mb | 8.21mb | | mysql | 2.50 mb | 0.21mb | | information_schema | 0.15 mb | 0.00mb | | sys | 0.01 mb | 0.00mb | | performance_schema | 0.00 mb | 0.00mb | +--------------------+------------+------------+ 6 rows in set (0.30 sec) mysql>
可以看出，数据库由8G缩小到6G，节省空间25%
查看history数据表的分区信息
mysql> select partition_name ,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='history'; +----------------+----------------------+-----------------------+------------+ | partition_name | partition_expression | partition_description | table_rows | +----------------+----------------------+-----------------------+------------+ | p20190529 | clock | 1559145600 | 6532933 | | p20190530 | clock | 1559232000 | 713544 | | p20190531 | clock | 1559318400 | 821808 | | p20190601 | clock | 1559404800 | 862368 | | p20190602 | clock | 1559491200 | 801216 | | p20190603 | clock | 1559577600 | 757224 | | p20190604 | clock | 1559664000 | 802152 | | p20190605 | clock | 1559750400 | 711048 | | p20190606 | clock | 1559836800 | 769080 | | p20190607 | clock | 1559923200 | 755976 | | p20190608 | clock | 1560009600 | 621504 | | p20190609 | clock | 1560096000 | 709176 | | p20190610 | clock | 1560182400 | 777192 | | p20190611 | clock | 1560268800 | 806520 | | p20190612 | clock | 1560355200 | 839592 | | p20190613 | clock | 1560441600 | 840840 | | p20190614 | clock | 1560528000 | 941616 | | p20190615 | clock | 1560614400 | 1120080 | | p20190616 | clock | 1560700800 | 987085 | | p20190617 | clock | 1560787200 | 1106976 | | p20190618 | clock | 1560873600 | 1124136 | | p20190619 | clock | 1560960000 | 1118520 | | p20190620 | clock | 1561046400 | 1019437 | | p20190621 | clock | 1561132800 | 3016416 | | p20190622 | clock | 1561219200 | 3108162 | | p20190623 | clock | 1561305600 | 3152510 | | p20190624 | clock | 1561392000 | 3090047 | | p20190625 | clock | 1561478400 | 3286296 | | p20190626 | clock | 1561564800 | 3281930 | | p20190627 | clock | 1561651200 | 3124056 | | p20190628 | clock | 1561737600 | 1766947 | | p20190629 | clock | 1561824000 | 0 | | p20190630 | clock | 1561910400 | 0 | +----------------+----------------------+-----------------------+------------+ 33 rows in set (0.00 sec) mysql>
查看趋势数据表分区信息
mysql> select partition_name ,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='trends'; +----------------+----------------------+-----------------------+------------+ | partition_name | partition_expression | partition_description | table_rows | +----------------+----------------------+-----------------------+------------+ | p201901 | clock | 1548950400 | 172494 | | p201902 | clock | 1551369600 | 313390 | | p201903 | clock | 1554048000 | 412587 | | p201904 | clock | 1556640000 | 461279 | | p201905 | clock | 1559318400 | 533022 | | p201906 | clock | 1561910400 | 994819 | | p201907 | clock | 1564588800 | 0 | +----------------+----------------------+-----------------------+------------+ 7 rows in set (0.00 sec)
表分区成功，重启zabbix服务
[root@zabbix_server zabbix-4.0.3]# service zabbix_server start Starting zabbix_server: [确定] [root@zabbix_server zabbix-4.0.3]#