實戰Zabbix-Server資料庫MySQL的libdata1檔案過大

實戰Zabbix-Server資料庫MySQL的libdata1檔案過大

今天我們的zabbix-server機器根空間不夠了，我一步步排查結果發現是/var/lib/mysql/下的libdata1檔案過大，已經達到了41G。我立即想到了zabbix的資料庫原因，隨後百度、Google才知道zabbix的資料庫他的表模式是共用資料表空間模式，隨著資料增長，ibdata1 越來越大，效能方面會有影響，而且innodb把資料和索引都放在ibdata1下。

共用資料表空間模式：

InnoDB 預設會將所有的資料庫InnoDB引擎的表資料存放區在一個共用空間中：ibdata1，這樣就感覺不爽，增刪資料庫的時候，ibdata1檔案不會自動收縮，單個資料庫的備份也將成為問題。通常只能將資料使用mysqldump 匯出，然後再匯入解決這個問題。

獨立資料表空間模式：

優點：
1.每個表都有自已獨立的資料表空間。
2.每個表的資料和索引都會存在自已的資料表空間中。
3.可以實現單表在不同的資料庫中移動。
4.空間可以回收（drop/truncate table方式動作表空間不能自動回收）
5.對於使用獨立資料表空間的表，不管怎麼刪除，資料表空間的片段不會太嚴重的影響效能，而且還有機會處理。
缺點：
單表增加比共用空間方式更大。

結論：
共用資料表空間在Insert操作上有一些優勢，但在其它都沒獨立資料表空間表現好，所以我們要改成獨立資料表空間。
當啟用獨立資料表空間時，請合理調整一下 innodb_open_files 參數。

下面我們來講下如何講zabbix資料庫修改成獨立資料表空間模式

1.查看檔案大小

[root@localhost ~]#cd /var/lib/mysql

[root@localhost ~]#ls -lh

-rw-rw---- 1 mysql mysql 41G Nov 24 13:31 ibdata1

-rw-rw---- 1 mysql mysql 5.0M Nov 24 13:31 ib_logfile0

-rw-rw---- 1 mysql mysql 5.0M Nov 24 13:31 ib_logfile1

drwx------ 2 mysql mysql 1.8M Nov 24 13:31 zabbix

大家可以看到這是沒修改之前的共用表資料空間檔案ibdata1大小已經達到了41G

2.清除zabbix資料庫曆史資料

1)查看哪些錶的歷史資料比較多

[root@localhost ~]#mysql -uroot -p

mysql > 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              | 1020.00000000 |  123981681 |

| history_log          |    0.04687500 |          0 |

...

| history_text          |    0.04687500 |          0 |

| history_uint          | 3400.98437500 |  34000562 |

| history_uint_sync    |    0.04687500 |          0 |

可以看到history和history_uint這兩個錶的歷史資料最多。

另外就是trends,trends_uint中也存在一些資料。

由於資料量太大，按照普通的方式delete資料的話基本上不太可能。

所以決定直接採用truncate table的方式來快速清空這些表的資料，再使用mysqldump匯出資料，刪除共用資料表空間資料檔案，重新匯入資料。

2）停止相關服務，避免寫入資料

[root@localhost ~]#/etc/init.d/zabbix_server stop

[root@localhost ~]#/etc/init.d/httpd stop

3）清空曆史資料

[root@localhost ~]#mysql -uroot -p

mysql > use zabbix;

Database changed

 

mysql > truncate table history;

Query OK, 123981681 rows affected (0.23 sec)

 

mysql > optimize table history;

1 row in set (0.02 sec)

 

mysql > truncate table history_uint;

Query OK, 57990562 rows affected (0.12 sec)

mysql > optimize table history_uint;

1 row in set (0.03 sec)

3.備份資料庫由於我/下的空間不足所以我掛載了一個NFS過來

[root@localhost ~]#mysqldump -uroot -p zabbix > /data/zabbix.sql

4.停止資料庫並刪除共用資料表空間資料檔案

1)停止資料庫

[root@localhost ~]#/etc/init.d/mysqld stop

2）刪除共用資料表空間資料檔案

[root@localhost ~]#cd /var/lib/mysql

[root@localhost ~]#rm -rf ib*

5.增加innodb_file_per_table參數

[root@localhost ~]#vi /etc/my.cnf

在[mysqld]下設定

innodb_file_per_table=1

6.啟動mysql

[root@localhost ~]#/etc/init.d/mysqld start

7.查看innodb_file_per_table參數是否生效

[root@localhost ~]#mysql -uroot -p

mysql> show variables like '%per_table%';

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

| Variable_name | Value |

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

| innodb_file_per_table | ON |

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

1 row in set (0.00 sec)

8.重新匯入資料庫

[root@localhost ~]#mysqldump -uroot -p zabbix < /data/zabbix.sql

9.最後，恢複相關服務進程

[root@localhost ~]#/etc/init.d/zabbix_server start

[root@localhost ~]#/etc/init.d/httpd start

恢複完服務之後，查看/分區的容量就下去了，之前是99%，處理完之後變成了12%。可見其成效

 

一些Zabbix相關教程集合：

安裝部署分布式監控系統Zabbix 2.06

《安裝部署分布式監控系統Zabbix 2.06》

CentOS 6.3下Zabbix安裝部署

Zabbix分布式監控系統實踐

CentOS 6.3下Zabbix監控apache server-status

CentOS 6.3下Zabbix監控MySQL資料庫參數

64位CentOS 6.2下安裝Zabbix 2.0.6   

ZABBIX 的詳細介紹：請點這裡
ZABBIX 的：請點這裡

本文永久更新連結地址：