优化zabbix表结构的一些思考

刚过完年,在日常检查服务器备份数据的时候发现zabbix的mysql备份文件异常庞大,考虑到zabbix会在日常监控服务器状态时定期发送一些告警信息以及监控时保存的SQL记

刚过完年,在日常检查服务器备份数据的时候发现zabbix的mysql备份文件异常庞大,考虑到zabbix会在日常监控服务器状态时定期发送一些告警信息以及监控时保存的SQL记录,所以查看了下zabbix的数据库表,发现mysql系统库文件下zabbix的数据库目录本身并不是很大,也就几百M,但发现同目录下的ibdata1文件异常庞大,达到了4.7G.

zabbix本身日常的监控数据量很大是事实,但为什么感觉没保存在zabbix目录下?且ibdata1为什么这么巨大?

网上查阅了zabbix的数据库存储原理,发现zabbix库是使用的innodb引擎的共享表空间,innodb把数据和索引都放在ibdata1下,随着数据增长,ibdata1会越来越大。性能方面会有影响。

然后就很好奇zabbix为什么会使用innodb的共享表空间存储数据,网上查看到一段资料写到

----------------------------------------------------------------------------------------

使用过MySQL的同学，刚开始接触最多的莫过于MyISAM表引擎了，这种引擎的数据库会分别创建三个文件：表结构、表索引、表数据空间。我们可以将某个数据库目录直接迁移到其他数据库也可以正常工作。

然而当你使用InnoDB的时候，一切都变了。InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：ibdata1，这样就感觉不爽，增删数据库的时候，ibdata1文件不会自动收缩，单个数据库的备份也将成为问题。通常只能将数据使用mysqldump 导出，然后再导入解决这个问题。

在MySQL的配置文件[mysqld]部分，增加innodb_file_per_table参数，可以修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间。

独立表空间

优点：

1.每个表都有自已独立的表空间。

2.每个表的数据和索引都会存在自已的表空间中。

3.可以实现单表在不同的数据库中移动。

4.空间可以回收（drop/truncate table方式操作表空间不能自动回收）

5.对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

缺点：

单表增加比共享空间方式更大。

结论：

共享表空间在Insert操作上有一些优势，但在其它都没独立表空间表现好。

当启用独立表空间时，请合理调整一下 innodb_open_files 参数。

----------------------------------------------------------------------------------------

原来默认情况下innodb会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中ibdata1,而且增删数据库的时候，ibdata1文件不会自动收缩，单个数据库的备份也将成为问题。

所以决定将innodb的共享表空间改成独立表空间,然后以后单独备份zabbix数据库时就不会备份整个数据库文件,导致系统资源浪费,最后再做一个定期的清理zabbix历史记录脚本,这样就不会担心以后备份文件过大,导致服务器硬盘容量紧张.

OK,开始干活....

系统环境:

-------------------------------

SYSYTEM: CentOS 6.3 x64

APACHE: httpd-2.4.4

MYSQL: mysql-5.6.10

PHP: php-5.4.13

ZABBIX: Zabbix 2.2.0rc2

-------------------------------

1.查看bdata1文件大小

# cd /usr/local/mysql/data

# du -sh *

-------------------------------------

4.0K file-test.xxx.cn.pid

4.0K file-test-relay-bin.000001

4.0K file-test-relay-bin.index

4.7G ibdata1

5.0M ib_logfile0

5.0M ib_logfile1

780K mysql

146M Syslog

4.0K webserver01.xxx.cn.pid

43M zabbix

--------------------------------------

共享表数据空间文件ibdata1大小已经达到了4.7G

登陆MySQL查看哪些表占用了空间

# mysql -uroot -p

-----------------------------------------

> 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 |

....

| help_items | 0.04687500 | 103 |

| history | 2841.00000000 | 34957883 |

| history_log | 0.04687500 | 0 |

...

| history_text | 0.04687500 | 0 |

| history_uint | 1563.98437500 | 23940661 |

| history_uint_sync | 0.04687500 | 0 |

...

| timeperiods | 0.01562500 | 0 |

| trends | 17.89564700 | 145780 |

| trends_uint | 25.567894000 | 271256 |

...

103 rows in set (1.46 sec)

可以看到，history表的记录已经达到了3G，34957883条，即3千多万条，同时history_unit也比较大，达到了1G，约2百多万条；

另外就是trends,trends_uint中也存在一些数据。

由于数据量太大，，按照普通的方式delete数据的话基本上不太可能。

因为我们每天会自动发送数据报表，所以决定直接采用truncate table的方式来快速清空这些表的数据，再使用mysqldump导出数据，删除共享表空间数据文件，重新导入数据。

2.停止相关服务，避免改造时写入数据

# /etc/init.d/zabbix_server stop

# /usr/local/apache2/bin/apachectl stop

3.清空历史数据

# mysql -uroot -p123456

---------------------------------------------

mysql > use zabbix;

mysql > truncate table history;

mysql > optimize table history;

mysql > truncate table history_uint;

mysql > optimize table history_uint;

mysql > truncate table trends;

mysql > optimize table trends;

mysql > truncate table trends_uint;

mysql > optimize table trends_uint;

------------------------------------------

4.备份数据

# mysqldump -uroot -p123456 zabbix > ~/zabbix_bak.sql

5.停止数据库

# service mysqld stop

6.删除共享表空间数据文件

# cd /usr/local/mysql/data

# rm -rf ibdata1 ib_logfile0 ib_logfile1

7.增加innodb_file_per_table独立表空间参数

# vi /etc/my.cnf

在[mysqld]下添加一行

-------------------------------

innodb_file_per_table=1

-------------------------------

8.启动MySQL

# service mysqld start

9.查看参数是否生效

# mysql -uroot -p123456

----------------------------------------------

mysql> show variables like '%per_table%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

1 row in set (0.00 sec)

------------------------------------------------

10.重新导入数据

# mysql -uroot -p123456 zabbix

11.编写zabbix自动清理历史数据的脚本，保留30天的数据

# vi /etc/rc.d/zabbix_olddata_clean.sh

---------------------------------------------

#!/bin/bash

DATE=`date -d "30 days ago"`

CLOCK=`date +%s -d "${DATE}"`

USER="root"

PW="123456"

DB="zabbix"

MYSQL="mysql -u$USER -p$PW"

function zabbix_olddata_clean(){

for TABLE in history trends

do

${MYSQL}

USE ${DB};

DELETE FROM ${TABLE} WHERE clock

OPTIMIZE TABLE ${TABLE};

DELETE FROM ${TABLE}_uint WHERE clock

OPTIMIZE TABLE ${TABLE}_uint;

EOF

done

}

zabbix_olddata_clean

---------------------------------------------

12.将该脚本加入计划任务

# crontab -e

加入一条规则,每个月1号凌晨3点35执行此脚本

---------------------------------

35 03 1 * * /bin/sh /etc/rc.d/zabbix_olddata_clean.sh

---------------------------------

13.重启相关服务进程

# /etc/init.d/zabbix_server restart

# /usr/local/apache2/bin/apachectl start

# service crond restart

14.验证

我们首先监控下mysql的数据库记录日志

# tail -f /usr/local/mysql/log/mysql.log

手动执行下此脚本

# sh /etc/rc.d/clean_zabbix_olddata.sh

---------------------------------------------

Table Op Msg_type Msg_text

zabbix.history optimize status OK

Table Op Msg_type Msg_text

zabbix.history_uint optimize status OK

Table Op Msg_type Msg_text

zabbix.trends optimize status OK

Table Op Msg_type Msg_text

zabbix.trends_uint optimize status OK

----------------------------------------------

查看到相关数据库清理语句

----------------------------------------------------

......

6390 Query SELECT DATABASE()

6390 Init DB zabbix

6390 Query DELETE FROM history WHERE clock

6390 Query OPTIMIZE TABLE history

140208 10:40:34 6390 Query DELETE FROM history_uint WHERE clock

6390 Query OPTIMIZE TABLE history_uint

6391 Connect root@localhost on

6391 Query select @@version_comment limit 1

6391 Query SELECT DATABASE()

6391 Init DB zabbix

6391 Query DELETE FROM trends WHERE clock

6390 Quit

6391 Query OPTIMIZE TABLE trends

6391 Query DELETE FROM trends_uint WHERE clock

6391 Query OPTIMIZE TABLE trends_uint

6391 Quit

.......

----------------------------------------------------

最后我们查看下系统数据库文件

# cd /usr/local/mysql/data/

# du -sh *

------------------------------------

4.0K file-test.iscard.cn.pid

4.0K file-test-relay-bin.000001

4.0K file-test-relay-bin.index

10M ibdata1

5.0M ib_logfile0

5.0M ib_logfile1

780K mysql

146M Syslog

4.0K webserver01.iscard.cn.pid

42M zabbix

------------------------------------

# ls -lh

----------------------------------------

总用量 21M

-rw-rw---- 1 mysql mysql 6 2月 8 08:30 file-test.xxx.cn.pid

-rw-rw---- 1 mysql mysql 117 9月 9 14:09 file-test-relay-bin.000001

-rw-rw---- 1 mysql mysql 29 9月 9 14:09 file-test-relay-bin.index

-rw-rw---- 1 mysql mysql 10M 2月 8 10:44 ibdata1

-rw-rw---- 1 mysql mysql 5.0M 2月 8 10:44 ib_logfile0

-rw-rw---- 1 mysql mysql 5.0M 2月 8 10:44 ib_logfile1

drwxr-x---. 2 mysql root 4.0K 9月 9 08:32 mysql

drwxr-x--- 2 mysql mysql 4.0K 2月 7 18:22 Syslog

-rwxr-x--- 1 mysql mysql 5 5月 27 2013 webserver01.xxx.cn.pid

drwxr-x--- 2 mysql mysql 12K 2月 8 10:40 zabbix

------------------------------------------

zabbix库文件已经独立到zabbix数据库目录下,ibdata1经过清理和瘦身,终于不会显得太臃肿.

本文出自 “一路向北” 博客，请务必保留此出处