设为首页收藏本站

追梦Linux

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 300|回复: 0

zabbix数据库表分区

[复制链接]

482

主题

485

帖子

16万

积分

CEO

Rank: 9Rank: 9Rank: 9

积分
168140

最佳新人活跃会员热心会员推广达人宣传达人灌水之王突出贡献优秀版主荣誉管理论坛元老

QQ
发表于 2016-10-29 13:31:27 | 显示全部楼层 |阅读模式
  由于测试环境上面使用的zabbix服务器配置比较低,经常会遇到性能瓶颈(主要是数据库和磁盘I/O等),于是倒逼我使用了一些方式来缓解这些问题。
主要是以前使用的那个备份数据库的脚本是对zabbix数据库进行全备的,使用的又是mysql自带的工具mysqldump,当数据量大了之后进行全备所花的时间比较长,这样将会造成数据库的锁读。。。从而使zabbix服务以为mysql死掉了,产生一大堆的报警。
后来发现原来造成数据库数据量大量增加的是zabbix数据库中的一些存储数据的大表导致的。于是备份数据库的时候可以选择跳过这些表进行备份,这样,将大大减少数据库备份所花的时间(PS:之前备份数据库所花时间在十分钟左右,现在跳过大表备份,所花时间在1S左右就能备份完,大大缩短了备份数据库时间)。
下面就贴出某位大神写的专门为zabbix数据库做备份以及恢复的脚本:
  1. #!binbash
  2. #author itnihao
  3. red='e[0;31m' # 红色  
  4. RED='e[1;31m'
  5. green='e[0;32m' # 绿色  
  6. GREEN='e[1;32m'
  7. blue='e[0;34m' # 蓝色  
  8. BLUE='e[1;34m'
  9. purple='e[0;35m' # 紫色  
  10. PURPLE='e[1;35m'
  11. NC='e[0m' # 没有颜色  
  12. source etcbashrc
  13. source etcprofile
  14. MySQL_USER=zabbix
  15. MySQL_PASSWORD=zabbix
  16. MySQL_HOST=localhost
  17. MySQL_PORT=3306
  18. MySQL_DUMP_PATH=optbackup
  19. MYSQL_BIN_PATH=optsoftwaremysqlbinmysql
  20. MYSQL_DUMP_BIN_PATH=optsoftwaremysqlbinmysqldump
  21. MySQL_DATABASE_NAME=zabbix
  22. DATE=$(date '+%Y%m%d')
  23. MySQLDUMP () {
  24.     [ -d ${MySQL_DUMP_PATH} ]  mkdir ${MySQL_DUMP_PATH}
  25.     cd ${MySQL_DUMP_PATH}
  26.     [ -d logs    ]  mkdir logs
  27.     [ -d ${DATE} ]  mkdir ${DATE}
  28.     cd ${DATE}
  29.      
  30.     #TABLE_NAME_ALL=$(${MYSQL_BIN_PATH} -u${MySQL_USER} -p${MySQL_PASSWORD}  -h${MySQL_HOST} ${MySQL_DATABASE_NAME} -e show tablesegrep -v (Tables_in_zabbix))
  31.     TABLE_NAME_ALL=$(${MYSQL_BIN_PATH} -u${MySQL_USER} -p${MySQL_PASSWORD}  -h${MySQL_HOST} ${MySQL_DATABASE_NAME} -e show tablesegrep -v (Tables_in_zabbixhistorytrendsacknowledgesalertsauditlogeventsservice_alarms))
  32.     for TABLE_NAME in ${TABLE_NAME_ALL}
  33.     do
  34.         ${MYSQL_DUMP_BIN_PATH} --opt -u${MySQL_USER} -p${MySQL_PASSWORD} -P${MySQL_PORT} -h${MySQL_HOST} ${MySQL_DATABASE_NAME} ${TABLE_NAME} ${TABLE_NAME}.sql
  35.         sleep 0.01
  36.     done
  37.     [ $ == 0 ] && echo ${DATE} Backup zabbix succeed      ${MySQL_DUMP_PATH}logsZabbixMysqlDump.log
  38.     [ $ != 0 ] && echo ${DATE} Backup zabbix not succeed  ${MySQL_DUMP_PATH}logsZabbixMysqlDump.log
  39.      
  40.     cd ${MySQL_DUMP_PATH}
  41.     rm -rf $(date +%Y%m%d --date='5 days ago')
  42.     exit 0
  43. }
  44. MySQLImport () {
  45.     cd ${MySQL_DUMP_PATH}
  46.     DATE=$(ls  ${MySQL_DUMP_PATH} egrep b^[0-9]+$b)
  47.     echo -e ${green}${DATE}
  48.     echo -e ${blue}what DATE do you want to import,please input date${NC}
  49.     read SELECT_DATE
  50.     if [ -d ${SELECT_DATE} ];then
  51.         echo -e you select is ${green}${SELECT_DATE}${NC}, do you want to contine,if,input ${red}(yesyY)${NC},else then exit
  52.         read Input
  53.         [[ 'yesyY' =~ ${Input} ]]
  54.         status=$
  55.         if [ ${status} == 0  ];then
  56.             echo now import SQL....... Please wait.......
  57.         else
  58.             exit 1
  59.         fi
  60.         cd ${SELECT_DATE}
  61.         for PER_TABEL_SQL in $(ls .sql)
  62.         do
  63.            ${MYSQL_BIN_PATH} -u${MySQL_USER} -p${MySQL_PASSWORD}  -h${MySQL_HOST} ${MySQL_DATABASE_NAME}  ${PER_TABEL_SQL}
  64.            echo -e import ${PER_TABEL_SQL} ${PURPLE}........................${NC}
  65.         done
  66.         echo Finish import SQL,Please check Zabbix database
  67.     else
  68.         echo Don't exist ${SELECT_DATE} DIR
  69.     fi
  70. }
  71. case $1 in
  72. MySQLDUMPmysqldump)
  73.     MySQLDUMP
  74.     ;;
  75. MySQLImportmysqlimport)
  76.     MySQLImport
  77.     ;;
  78. )
  79.     echo Usage $0 {(MySQLDUMPmysqldump) (MySQLImportmysqlimport)}
  80.     ;;
  81. esac
复制代码
我这是在大神的脚本上做了修改之后形成的适合我自己备份的脚本,各位也可以自行修改成适合自己的备份脚本。这个脚本实现的效果上面已经说了,之前做全备的时候差不多有4G左右的数据量,现在只备份配置文件数据量只有不到10M,果断大大节省时间以及空间呀。
好了,关于数据库备份的事情搞了。
在说数据库分表之前,先随便聊聊Zabbix的一些参数的优化吧,其实我也不是很懂,只是机器上了1500+,楞着头皮去调一下参数
首先是几个Poller的调整:
### Option: StartPollers (处理监控项的东西)
### Option: StartIPMIPollers (母鸡干什么用的,暂时没用,设为0)
### Option: StartPollersUnreachable (获取数据遇到不可到达时,交给这些进程处理)
### Option: StartHTTPPollers (监控HTTP,WEB那些东西用的,没用,设为0)
### Option: StartJavaPollers  (监控JAVA专用进程,为毛就只有JAVA)
### Option: StartProxyPollers (处理代理的进程,暂时没用,设为0)
### Option: StartDiscoverers   (处理自动发现的,没用,设为0)
### Option: StartPingers  (如果用了ICMP PING那个模板的,这个值要调大一些)
把几个没用到的先关掉:StartHTTPPollers StartJavaPollers  StartProxyPollers,设为0就可以了,然后可以观察ZabbixServer的图形来调整StartPollers和StartPollersUnreachable的数量:

要注意的是,例如StartPollers,虽然配置文件写是可用范围是0-1000,但这个1000是所有的进程的值,也就是说上面那些进程的总数不能超过1000,设的时候要注意一下这点。
关于Cache的调整:
### Option: CacheSize
### Option: CacheUpdateFrequency
### Option: HistoryCacheSize
### Option: TrendCacheSize
### Option: HistoryTextCacheSize
### Option: ValueCacheSize
关于内存的参数有这么多,都是要根据机器数量和item数量的增加而增加,这些内存的值不能大于系统内核的kernel.shmall这个值,否则申请不了内存程序启动不了
如果日志出现连续一大片地出现这些信息:
Zabbix agent item "vfs.fs.size[/boot,free]" on host "192.168.1.100" failed: first network error, wait for 15 seconds
resuming Zabbix agent checks on host "192.168.1.100": connection restored
说明你的poller不够,需要再加大poller,如果加大poller还是这样则需要减少监控的item,或者用proxy来做分布式监控了
机器数量多的时候,mysql里面的history表就会越来越大,虽然zabbix本身有删除功能(就是那个housekeeper的功能),但这东西太影响性能,所以网上的做法都是关闭这个东西,用mysql的表分区功能来实现清理历史数据还可以提升mysql的性能
表分区可以对大数据量的表进行物理上的拆分成多个文件,但是逻辑上来看,还是一张表,对应用程序是透明的。另外,将这一张大表拆分成很多小表的话将使得数据查询速度能够更快。还可以随时删除旧的数据分区,删除过期数据。这种方式适用于大数据量的表,但是查询量比较少的应用场景。如果是大数据量的表,又有大量查询的话建议还是进行分库分表操作。
好了,不多扯了,开始作业了。
首先,登录数据库(PS:这个就不演示了)
然后登陆到zabbix库中修改两张表的结构:
  1. use zabbix;
  2. Alter table history_text drop primary key, add index (id), drop index history_text_2, add index history_text_2 (itemid, id);
  3. Alter table history_log drop primary key, add index (id), drop index history_log_2, add index history_log_2 (itemid, id);
复制代码
修改完之后再按照官网上的过程创建四个存储过程:
  1. DELIMITER $
  2. CREATE PROCEDURE `partition_create`(SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)
  3. BEGIN
  4.         /*
  5.            SCHEMANAME = The DB schema in which to make changes
  6.            TABLENAME = The table with partitions to potentially delete
  7.            PARTITIONNAME = The name of the partition to create
  8.         */
  9.         /*
  10.            Verify that the partition does not already exist
  11.         */

  12.         DECLARE RETROWS INT;
  13.         SELECT COUNT(1) INTO RETROWS
  14.         FROM information_schema.partitions
  15.         WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_description >= CLOCK;

  16.         IF RETROWS = 0 THEN
  17.                 /*
  18.                    1. Print a message indicating that a partition was created.
  19.                    2. Create the SQL to create the partition.
  20.                    3. Execute the SQL from #2.
  21.                 */
  22.                 SELECT CONCAT( "partition_create(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;
  23.                 SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );
  24.                 PREPARE STMT FROM @sql;
  25.                 EXECUTE STMT;
  26.                 DEALLOCATE PREPARE STMT;
  27.         END IF;
  28. END$
  29. DELIMITER ;
复制代码
  1. DELIMITER $
  2. CREATE PROCEDURE `partition_drop`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT)
  3. BEGIN
  4.         /*
  5.            SCHEMANAME = The DB schema in which to make changes
  6.            TABLENAME = The table with partitions to potentially delete
  7.            DELETE_BELOW_PARTITION_DATE = Delete any partitions with names that are dates older than this one (yyyy-mm-dd)
  8.         */
  9.         DECLARE done INT DEFAULT FALSE;
  10.         DECLARE drop_part_name VARCHAR(16);

  11.         /*
  12.            Get a list of all the partitions that are older than the date
  13.            in DELETE_BELOW_PARTITION_DATE.  All partitions are prefixed with
  14.            a "p", so use SUBSTRING TO get rid of that character.
  15.         */
  16.         DECLARE myCursor CURSOR FOR
  17.                 SELECT partition_name
  18.                 FROM information_schema.partitions
  19.                 WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND CAST(SUBSTRING(partition_name FROM 2) AS UNSIGNED) < DELETE_BELOW_PARTITION_DATE;
  20.         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  21.         /*
  22.            Create the basics for when we need to drop the partition.  Also, create
  23.            @drop_partitions to hold a comma-delimited list of all partitions that
  24.            should be deleted.
  25.         */
  26.         SET @alter_header = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " DROP PARTITION ");
  27.         SET @drop_partitions = "";

  28.         /*
  29.            Start looping through all the partitions that are too old.
  30.         */
  31.         OPEN myCursor;
  32.         read_loop: LOOP
  33.                 FETCH myCursor INTO drop_part_name;
  34.                 IF done THEN
  35.                         LEAVE read_loop;
  36.                 END IF;
  37.                 SET @drop_partitions = IF(@drop_partitions = "", drop_part_name, CONCAT(@drop_partitions, ",", drop_part_name));
  38.         END LOOP;
  39.         IF @drop_partitions != "" THEN
  40.                 /*
  41.                    1. Build the SQL to drop all the necessary partitions.
  42.                    2. Run the SQL to drop the partitions.
  43.                    3. Print out the table partitions that were deleted.
  44.                 */
  45.                 SET @full_sql = CONCAT(@alter_header, @drop_partitions, ";");
  46.                 PREPARE STMT FROM @full_sql;
  47.                 EXECUTE STMT;
  48.                 DEALLOCATE PREPARE STMT;

  49.                 SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, @drop_partitions AS `partitions_deleted`;
  50.         ELSE
  51.                 /*
  52.                    No partitions are being deleted, so print out "N/A" (Not applicable) to indicate
  53.                    that no changes were made.
  54.                 */
  55.                 SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, "N/A" AS `partitions_deleted`;
  56.         END IF;
  57. END$
  58. DELIMITER ;
复制代码
  1. DELIMITER $
  2. CREATE PROCEDURE `partition_maintenance`(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT)
  3. BEGIN
  4.         DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16);
  5.         DECLARE PARTITION_NAME VARCHAR(16);
  6.         DECLARE OLD_PARTITION_NAME VARCHAR(16);
  7.         DECLARE LESS_THAN_TIMESTAMP INT;
  8.         DECLARE CUR_TIME INT;

  9.         CALL partition_verify(SCHEMA_NAME, TABLE_NAME, HOURLY_INTERVAL);
  10.         SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'));

  11.         SET @__interval = 1;
  12.         create_loop: LOOP
  13.                 IF @__interval > CREATE_NEXT_INTERVALS THEN
  14.                         LEAVE create_loop;
  15.                 END IF;

  16.                 SET LESS_THAN_TIMESTAMP = CUR_TIME + (HOURLY_INTERVAL * @__interval * 3600);
  17.                 SET PARTITION_NAME = FROM_UNIXTIME(CUR_TIME + HOURLY_INTERVAL * (@__interval - 1) * 3600, 'p%Y%m%d%H00');
  18.                 IF(PARTITION_NAME != OLD_PARTITION_NAME) THEN
  19.                         CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);
  20.                 END IF;
  21.                 SET @__interval=@__interval+1;
  22.                 SET OLD_PARTITION_NAME = PARTITION_NAME;
  23.         END LOOP;

  24.         SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVAL KEEP_DATA_DAYS DAY), '%Y%m%d0000');
  25.         CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE);

  26. END$
  27. DELIMITER ;
复制代码
  1. DELIMITER $
  2. CREATE PROCEDURE `partition_verify`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11))
  3. BEGIN
  4.         DECLARE PARTITION_NAME VARCHAR(16);
  5.         DECLARE RETROWS INT(11);
  6.         DECLARE FUTURE_TIMESTAMP TIMESTAMP;

  7.         /*
  8.          * Check if any partitions exist for the given SCHEMANAME.TABLENAME.
  9.          */
  10.         SELECT COUNT(1) INTO RETROWS
  11.         FROM information_schema.partitions
  12.         WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name IS NULL;

  13.         /*
  14.          * If partitions do not exist, go ahead and partition the table
  15.          */
  16.         IF RETROWS = 1 THEN
  17.                 /*
  18.                  * Take the current date at 00:00:00 and add HOURLYINTERVAL to it.  This is the timestamp below which we will store values.
  19.                  * We begin partitioning based on the beginning of a day.  This is because we don't want to generate a random partition
  20.                  * that won't necessarily fall in line with the desired partition naming (ie: if the hour interval is 24 hours, we could
  21.                  * end up creating a partition now named "p201403270600" when all other partitions will be like "p201403280000").
  22.                  */
  23.                 SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE(), " ", '00:00:00'));
  24.                 SET PARTITION_NAME = DATE_FORMAT(CURDATE(), 'p%Y%m%d%H00');

  25.                 -- Create the partitioning query
  26.                 SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " PARTITION BY RANGE(`clock`)");
  27.                 SET @__PARTITION_SQL = CONCAT(@__PARTITION_SQL, "(PARTITION ", PARTITION_NAME, " VALUES LESS THAN (", UNIX_TIMESTAMP(FUTURE_TIMESTAMP), "));");

  28.                 -- Run the partitioning query
  29.                 PREPARE STMT FROM @__PARTITION_SQL;
  30.                 EXECUTE STMT;
  31.                 DEALLOCATE PREPARE STMT;
  32.         END IF;
  33. END$
  34. DELIMITER ;
复制代码
上面四个存储过程执行后将可以使用
  1. CALL partition_maintenance('<zabbix_db_name>', '<table_name>', <days_to_keep_data>, <hourly_interval>, <num_future_intervals_to_create>)
复制代码
命令对想要分区的表进行表分区了。其中的参数我这里解释一下。
这是举例:
  1. CALL partition_maintenance(zabbix, 'history_uint', 31, 24, 14);
复制代码
zabbix_db_name:库名
table_name:表名
days_to_keep_data:保存多少天的数据
hourly_interval:每隔多久生成一个分区
num_future_intervals_to_create:本次一共生成多少个分区
这个例子就是history_uint表最多保存31天的数据,每隔24小时生成一个分区,这次一共生成14个分区
这里可以将上面四个存储过程保存为一个文件,导入到数据库中,文件我稍后将会放在附件中,这里使用的命令是:mysql -uzabbix -pzabbix zabbix<partition_call.sql
然后可以将CALL统一调用也做成一个文件,统一调用的内容如下:
  1. DELIMITER $
  2. CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
  3. BEGIN
  4.         CALL partition_maintenance(SCHEMA_NAME, 'history', 28, 24, 14);
  5.         CALL partition_maintenance(SCHEMA_NAME, 'history_log', 28, 24, 14);
  6.         CALL partition_maintenance(SCHEMA_NAME, 'history_str', 28, 24, 14);
  7.         CALL partition_maintenance(SCHEMA_NAME, 'history_text', 28, 24, 14);
  8.         CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 28, 24, 14);
  9.         CALL partition_maintenance(SCHEMA_NAME, 'trends', 730, 24, 14);
  10.         CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 730, 24, 14);
  11. END$
  12. DELIMITER ;
复制代码
也将该文件导入到数据库中,使用命令:mysql -uzabbix -pzabbix zabbix<partition_all.sql
好了,到了这里之后就可以使用如下命令执行表分区了:
  1. mysql -uzabbix -pzabbix zabbix -e "CALL partition_maintenance_all('zabbix');"
  2. +----------------+--------------------+
  3. | table          | partitions_deleted |
  4. +----------------+--------------------+
  5. | zabbix.history | N/A                |
  6. +----------------+--------------------+
  7. +--------------------+--------------------+
  8. | table              | partitions_deleted |
  9. +--------------------+--------------------+
  10. | zabbix.history_log | N/A                |
  11. +--------------------+--------------------+
  12. +--------------------+--------------------+
  13. | table              | partitions_deleted |
  14. +--------------------+--------------------+
  15. | zabbix.history_str | N/A                |
  16. +--------------------+--------------------+
  17. +---------------------+--------------------+
  18. | table               | partitions_deleted |
  19. +---------------------+--------------------+
  20. | zabbix.history_text | N/A                |
  21. +---------------------+--------------------+
  22. +---------------------+--------------------+
  23. | table               | partitions_deleted |
  24. +---------------------+--------------------+
  25. | zabbix.history_uint | N/A                |
  26. +---------------------+--------------------+
  27. +---------------+--------------------+
  28. | table         | partitions_deleted |
  29. +---------------+--------------------+
  30. | zabbix.trends | N/A                |
  31. +---------------+--------------------+
  32. +--------------------+--------------------+
  33. | table              | partitions_deleted |
  34. +--------------------+--------------------+
  35. | zabbix.trends_uint | N/A                |
  36. +--------------------+--------------------+
复制代码
看到如下结果证明所有7张表都进行了表分区,也可以在Mysql的数data目录下看到新生成的表分区文件。(PS:注意,最好是清空history_uint表的数据之后再执行上面这条命令,否则因为这张表数据量太大,转换时间将会好长,清空表中数据的命令为: truncate table history_uint;)
好了,这样可以进行表分区了。
将上面这条命令写入到计划任务中如下:
  1. crontab -l
  2. 0 0 * * * /usr/local/mysql/bin/mysql -uzabbix -pzabbix zabbix -e "CALL partition_maintenance_all('zabbix');"
复制代码
这样就大功告成了,之后再体验一下zabbix的web页面看是不是感觉比以前快了
最后关掉Zabbix的HouseKeeper功能:

最后附上几个用到的文件~





本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

QQ|小黑屋|手机版|Archiver|追梦Linux ( 粤ICP备14096197号  点击这里给我发消息

GMT+8, 2019-3-25 17:30 , Processed in 0.233510 second(s), 36 queries .

Powered by 追梦Linux! X3.3 Licensed

© 2015-2017 追梦Linux!.

快速回复 返回顶部 返回列表