清理 zabbix 历史数据, 缩减 mysql 空间
zabbix 由于历史数据过大, 因此导致磁盘空间暴涨,下面是结局方法步骤1. 停止 ZABBIX SERER 操作
view plaincopyprint?https://code.csdn.net/assets/CODE_ico.pnghttps://code.csdn.net/assets/ico_fork.svg
[*] # killall zabbix_server
[*] # lsof -i:10051
2. 停止 mysql 操作
view plaincopyprint?https://code.csdn.net/assets/CODE_ico.pnghttps://code.csdn.net/assets/ico_fork.svg
[*] # mysqladmin -u root -p -h 127.0.0.1 shutdown
3. 修改 my.cnf
添加 skip-new 参数, 目标可用缩减 innodb 磁盘空间
4. 重启启动 mysql
view plaincopyprint?https://code.csdn.net/assets/CODE_ico.pnghttps://code.csdn.net/assets/ico_fork.svg
[*] /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 &
[*]
[*] # lsof -i:3306
[*] COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
[*] mysqld25527 apps 11uIPv4 29371110 0t0TCP *:mysql (LISTEN)
5. 分析 history 表
view plaincopyprint?https://code.csdn.net/assets/CODE_ico.pnghttps://code.csdn.net/assets/ico_fork.svg
[*] 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 日
view plaincopyprint?https://code.csdn.net/assets/CODE_ico.pnghttps://code.csdn.net/assets/ico_fork.svg
[*] # date +%s -d "Mar 25, 2014 00:00:00"
[*] 1395676800
删除 history, history_unit 表方法
view plaincopyprint?https://code.csdn.net/assets/CODE_ico.pnghttps://code.csdn.net/assets/ico_fork.svg
[*] 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)
尝试对表进行缩减发生故障
view plaincopyprint?https://code.csdn.net/assets/CODE_ico.pnghttps://code.csdn.net/assets/ico_fork.svg
[*] mysql> optimize table history_uint;
[*] ERROR 1114 (HY000): The table '#sql-63b7_2d' is full
[*] mysql> quit
[*] Bye
[*] # df -h
[*] 文件系统 容量已用 可用 已用% 挂载点
[*] /dev/vda1 20G 18G1.3G94% /
[*] tmpfs 2.0G 02.0G 0% /dev/shm
故障原因, 当前 / 下磁盘空间不够.
临时删除 swapfile
view plaincopyprint?https://code.csdn.net/assets/CODE_ico.pnghttps://code.csdn.net/assets/ico_fork.svg
[*] # swapoff -a
[*] # rm -rf swapfile
[*] # df -h
[*] 文件系统 容量已用 可用 已用% 挂载点
[*] /dev/vda1 20G 15G3.7G81% /
[*] tmpfs 2.0G 02.0G 0% /dev/shm
原理说明, mysql 执行 optimize 过程中, 生成了临时表见下面文件, mysql 直接吧 history_unit 表复制成临时表再重新改名, 实现空间缩减.
view plaincopyprint?https://code.csdn.net/assets/CODE_ico.pnghttps://code.csdn.net/assets/ico_fork.svg
[*] # 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
再次缩减
view plaincopyprint?https://code.csdn.net/assets/CODE_ico.pnghttps://code.csdn.net/assets/ico_fork.svg
[*] mysql> optimize table history_uint;
[*] Query OK, 40496963 rows affected (20 min 0.04 sec)
[*] Records: 40496963Duplicates: 0Warnings: 0
[*]
[*] mysql> optimize table history;
[*] Query OK, 45998084 rows affected (21 min 54.99 sec)
[*] Records: 45998084Duplicates: 0Warnings: 0
7. 缩减前后文件大小比较
缩减前
view plaincopyprint?https://code.csdn.net/assets/CODE_ico.pnghttps://code.csdn.net/assets/ico_fork.svg
[*] # 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
缩减后
view plaincopyprint?https://code.csdn.net/assets/CODE_ico.pnghttps://code.csdn.net/assets/ico_fork.svg
[*] # 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 分钟后初始化, 下面报警会自动消除, 不用担心.
view plaincopyprint?https://code.csdn.net/assets/CODE_ico.pnghttps://code.csdn.net/assets/ico_fork.svg
[*] 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
页:
[1]