733
技術社區[雲棲]
清理 zabbix 曆史數據, 縮減 mysql 空間
zabbix 由於曆史數據過大, 因此導致磁盤空間暴漲, 下麵是結局方法步驟
1. 停止 ZABBIX SERER 操作
[root@gd02-qa-plxt2-nodomain-web-95 ~]# killall zabbix_server [root@gd02-qa-plxt2-nodomain-web-95 ~]# lsof -i:10051
2. 停止 mysql 操作
[root@gd02-qa-plxt2-nodomain-web-96 dbdat]# mysqladmin -u root -p -h 127.0.0.1 shutdown
3. 修改 my.cnf
添加 skip-new 參數, 目標可用縮減 innodb 磁盤空間
4. 重啟啟動 mysql
/apps/svr/mysql/bin/mysqld_safe --defaults-file=/apps/conf/mysql5.6/my.cnf --ledir=/apps/svr/mysql/bin --basedir=/apps/svr/mysql/ --datadir=/apps/dbdat/mysql5_data --user=apps & [root@gd02-qa-plxt2-nodomain-web-96 apps]# lsof -i:3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 25527 apps 11u IPv4 29371110 0t0 TCP *:mysql (LISTEN)
5. 分析 history 表
mysql> desc history; +--------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+-------+ | itemid | bigint(20) unsigned | NO | MUL | NULL | | | clock | int(11) | NO | | 0 | | | value | double(16,4) | NO | | 0.0000 | | | ns | int(11) | NO | | 0 | | +--------+---------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> select max(itemid) from history; +-------------+ | max(itemid) | +-------------+ | 46582 | +-------------+ 1 row in set (0.00 sec) mysql> select * from history where itemid=46582 limit 1, 20; +--------+------------+--------+-----------+ | itemid | clock | value | ns | +--------+------------+--------+-----------+ | 46582 | 1396361332 | 0.0000 | 81875000 | | 46582 | 1396361362 | 0.0000 | 768297000 | | 46582 | 1396361392 | 0.0000 | 656787000 | | 46582 | 1396361422 | 0.0000 | 665169000 | | 46582 | 1396361452 | 0.0000 | 973570000 | | 46582 | 1396361482 | 0.0000 | 625619000 | | 46582 | 1396361512 | 0.0000 | 292743000 | | 46582 | 1396361543 | 0.0000 | 340000 | | 46582 | 1396361572 | 0.0000 | 15651000 | | 46582 | 1396361602 | 0.0000 | 153264000 | | 46582 | 1396361632 | 0.0000 | 79316000 | | 46582 | 1396361662 | 0.0000 | 308107000 | | 46582 | 1396361692 | 0.0000 | 237146000 | | 46582 | 1396361722 | 0.0000 | 108810000 | | 46582 | 1396361752 | 0.0000 | 419398000 | | 46582 | 1396361782 | 0.0000 | 284113000 | | 46582 | 1396361812 | 0.0000 | 254230000 | | 46582 | 1396361842 | 0.0000 | 145938000 | | 46582 | 1396361872 | 0.0000 | 403163000 | | 46582 | 1396361902 | 0.3300 | 193302000 | +--------+------------+--------+-----------+ 20 rows in set (0.01 sec)
6. 刪除兩周前數據方法
取得時間戳, 時間隻保留至 2014 3 25 日
[root@gd02-zabbix-db-research api]# date +%s -d "Mar 25, 2014 00:00:00" 1395676800
刪除 history, history_unit 表方法
mysql> delete from history where clock < 1395676800; Query OK, 8961912 rows affected (17 min 22.06 sec) mysql> delete from history_uint where clock < 1395676800; Query OK, 7789494 rows affected (21 min 38.02 sec)
嚐試對表進行縮減發生故障
mysql> optimize table history_uint; ERROR 1114 (HY000): The table '#sql-63b7_2d' is full mysql> quit Bye [root@gd02-qa-plxt2-nodomain-web-96 mysql5_data]# df -h 文件係統 容量 已用 可用 已用% 掛載點 /dev/vda1 20G 18G 1.3G 94% / tmpfs 2.0G 0 2.0G 0% /dev/shm
故障原因, 當前 / 下磁盤空間不夠.
臨時刪除 swapfile
[root@gd02-qa-plxt2-nodomain-web-96 /]# swapoff -a [root@gd02-qa-plxt2-nodomain-web-96 /]# rm -rf swapfile [root@gd02-qa-plxt2-nodomain-web-96 /]# df -h 文件係統 容量 已用 可用 已用% 掛載點 /dev/vda1 20G 15G 3.7G 81% / tmpfs 2.0G 0 2.0G 0% /dev/shm
原理說明, mysql 執行 optimize 過程中, 生成了臨時表見下麵文件, mysql 直接吧 history_unit 表複製成臨時表再重新改名, 實現空間縮減.
[root@gd02-qa-plxt2-nodomain-web-96 zabbix]# ls *63b7_2e* -lh -rw-rw---- 1 apps apps 8.5K 04-02 12:04 #sql-63b7_2e.frm -rw-rw---- 1 apps apps 1.1G 04-02 12:10 #sql-63b7_2e.ibd
再次縮減
mysql> optimize table history_uint; Query OK, 40496963 rows affected (20 min 0.04 sec) Records: 40496963 Duplicates: 0 Warnings: 0 mysql> optimize table history; Query OK, 45998084 rows affected (21 min 54.99 sec) Records: 45998084 Duplicates: 0 Warnings: 0
7. 縮減前後文件大小比較
縮減前
[root@gd02-qa-plxt2-nodomain-web-96 mysql5_data]# find -size +50M -exec ls -lh {} \;
-rw-rw---- 1 apps apps 80M 04-02 10:29 ./zabbix/events.ibd
-rw-rw---- 1 apps apps 5.1G 04-02 11:44 ./zabbix/history.ibd
-rw-rw---- 1 apps apps 152M 04-02 10:29 ./zabbix/trends.ibd
-rw-rw---- 1 apps apps 4.3G 04-02 11:47 ./zabbix/history_uint.ibd
-rw-rw---- 1 apps apps 328M 04-02 10:29 ./zabbix/trends_uint.ibd
-rw-rw---- 1 apps apps 1.1G 04-02 11:47 ./ibdata1
縮減後
[root@gd02-qa-plxt2-nodomain-web-96 mysql5_data]# find -size +50M -exec ls -lh {} \;
-rw-rw---- 1 apps apps 80M 04-02 10:29 ./zabbix/events.ibd
-rw-rw---- 1 apps apps 3.6G 04-02 13:30 ./zabbix/history.ibd
-rw-rw---- 1 apps apps 152M 04-02 10:29 ./zabbix/trends.ibd
-rw-rw---- 1 apps apps 3.2G 04-02 12:24 ./zabbix/history_uint.ibd
-rw-rw---- 1 apps apps 328M 04-02 10:29 ./zabbix/trends_uint.ibd
-rw-rw---- 1 apps apps 1.1G 04-02 13:30 ./ibdata1
8. 重啟啟動 zabbix, php, nginx, mysql
新問題出現:
當前 zabbix 進行初始化, 會對 mysql 進行大量數據 r/w 操作
因此可能會發生下麵警報, 經過 5 分鍾後初始化, 下麵報警會自動消除, 不用擔心.
Disk I/O is overloaded on gd02-qa-plxt2-nodomain-web-96.vclound.com Zabbix history syncer processes more than 75% busy Zabbix timer processes more than 75% busy
最後更新：2017-04-03 12:56:00