最佳化zabbix表結構的一些思考

   剛過完年,在日常檢查伺服器備份資料的時候發現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 < ~/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經過清理和瘦身,終於不會顯得太臃腫.

本文出自 “一路向北” 部落格，請務必保留此出處http://showerlee.blog.51cto.com/2047005/1357212