优化Zabbix表结构的一些思考

共计 10757 个字符，预计需要花费 27 分钟才能阅读完成。

刚过完年, 在日常检查服务器备份数据的时候发现 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;

ZABBIX 的详细介绍：请点这里
ZABBIX 的下载地址：请点这里

相关阅读：

安装部署分布式监控系统 Zabbix 2.06 http://www.linuxidc.com/Linux/2013-07/86942.htm

《安装部署分布式监控系统 Zabbix 2.06》http://www.linuxidc.com/Linux/2013-07/86942.htm

CentOS 6.3 下 Zabbix 安装部署 http://www.linuxidc.com/Linux/2013-05/83786.htm

Zabbix 分布式监控系统实践 http://www.linuxidc.com/Linux/2013-06/85758.htm

CentOS 6.3 下 Zabbix 监控 apache server-status http://www.linuxidc.com/Linux/2013-05/84740.htm

CentOS 6.3 下 Zabbix 监控 MySQL 数据库参数 http://www.linuxidc.com/Linux/2013-05/84800.htm

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 < ~/zabbix.sql

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

USE ${DB};

DELETE FROM ${TABLE} WHERE clock < ${CLOCK};

OPTIMIZE TABLE ${TABLE};

DELETE FROM ${TABLE}_uint WHERE clock < ${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 < 1391861640

6390 Query OPTIMIZE TABLE history

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

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

6390 Quit

6391 Query OPTIMIZE TABLE trends

6391 Query DELETE FROM trends_uint WHERE clock < 1391861640

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 经过清理和瘦身, 终于不会显得太臃肿.

刚过完年, 在日常检查服务器备份数据的时候发现 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;

ZABBIX 的详细介绍：请点这里
ZABBIX 的下载地址：请点这里

相关阅读：

安装部署分布式监控系统 Zabbix 2.06 http://www.linuxidc.com/Linux/2013-07/86942.htm

《安装部署分布式监控系统 Zabbix 2.06》http://www.linuxidc.com/Linux/2013-07/86942.htm

CentOS 6.3 下 Zabbix 安装部署 http://www.linuxidc.com/Linux/2013-05/83786.htm

Zabbix 分布式监控系统实践 http://www.linuxidc.com/Linux/2013-06/85758.htm

CentOS 6.3 下 Zabbix 监控 apache server-status http://www.linuxidc.com/Linux/2013-05/84740.htm

CentOS 6.3 下 Zabbix 监控 MySQL 数据库参数 http://www.linuxidc.com/Linux/2013-05/84800.htm