今天我們的zabbix-server機(jī)器上線半年時間,占用了500G空間,我一步步排查結(jié)果發(fā)現(xiàn)是/var/lib/MySQL/下的libdata1文件過大,已經(jīng)達(dá)到了300G。我立即想到了zabbix的數(shù)據(jù)庫原因,zabbix的數(shù)據(jù)庫他的表模式是共享表空間模式,隨著數(shù)據(jù)增長,ibdata1越來越大,性能方面會有影響,而且innodb把數(shù)據(jù)和索引都放在ibdata1下。
成都創(chuàng)新互聯(lián)公司10多年企業(yè)網(wǎng)站建設(shè)服務(wù);為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁設(shè)計(jì)及高端網(wǎng)站定制服務(wù),企業(yè)網(wǎng)站建設(shè)及推廣,對成都餐廳設(shè)計(jì)等多個領(lǐng)域擁有多年的網(wǎng)站推廣經(jīng)驗(yàn)的網(wǎng)站建設(shè)公司。
共享表空間模式:
InnoDB 默認(rèn)會將所有的數(shù)據(jù)庫InnoDB引擎的表數(shù)據(jù)存儲在一個共享空間中:ibdata1,這樣就感覺不爽,增刪數(shù)據(jù)庫的時候,ibdata1文件不會自動收縮,單個數(shù)據(jù)庫的備份也將成為問題。通常只能將數(shù)據(jù)使用mysqldump 導(dǎo)出,然后再導(dǎo)入解決這個問題。
獨(dú)立表空間模式:
優(yōu)點(diǎn):
1.每個表都有自已獨(dú)立的表空間。
2.每個表的數(shù)據(jù)和索引都會存在自已的表空間中。
3.可以實(shí)現(xiàn)單表在不同的數(shù)據(jù)庫中移動。
4.空間可以回收(drop/truncate table方式操作表空間不能自動回收)
5.對于使用獨(dú)立表空間的表,不管怎麼刪除,表空間的碎片不會太嚴(yán)重的影響性能,而且還有機(jī)會處理。
缺點(diǎn):
單表增加比共享空間方式更大。
結(jié)論:
共享表空間在Insert操作上有一些優(yōu)勢,但在其它都沒獨(dú)立表空間表現(xiàn)好,所以我們要改成獨(dú)立表空間。
當(dāng)啟用獨(dú)立表空間時,請合理調(diào)整一下innodb_open_files 參數(shù)。
下面我們來講下如何講zabbix數(shù)據(jù)庫修改成獨(dú)立表空間模式
查看文件大小
l 查看系統(tǒng)空間利用率
[root@zabbix ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_zabbix-lv_root 1000G 468G 482G 50% /
tmpfs 7.8G 0 7.8G 0% /dev/shm
/dev/sda1 485M 39M 421M 9% /boot
查看文件空間占用情況
[root@zabbix ~]# du / -h --max-depth=1
12K /.dbus
4.0K /selinux
1.7M /tmp
du: cannot access `/proc/7012/task/7012/fd/4': No suchfile or directory
du: cannot access `/proc/7012/task/7012/fdinfo/4': Nosuch file or directory
du: cannot access `/proc/7012/fd/4': No such file ordirectory
du: cannot access `/proc/7012/fdinfo/4': No such fileor directory
0 /proc
4.0K /media
4.0K /srv
8.0K /mnt
1.2G /root
0 /net
36K /home
39M /etc
462G /var
29M /boot
1.1G /backup
7.7M /bin
16K /lost+found
3.5G /usr
0 /misc
27M /lib64
0 /sys
8.0K /opt
15M /sbin
164K /dev
149M /lib
468G /
[root@zabbix ~]# cd /var/
[root@zabbix var]# du . -h --max-depth=1
504K ./spool
139G ./log
4.0K ./tmp
8.0K ./db
103M ./www
244K ./run
16K ./lock
4.0K ./crash
4.0K ./account
4.0K ./local
12K ./yp
33M ./cache
4.0K ./preserve
4.0K ./cvs
4.0K ./games
4.0K ./gdm
4.0K ./opt
8.0K ./empty
324G ./lib
4.0K ./nis
463G .
[root@zabbix var]# cd log/
[root@zabbix log]# ll
total 145373448
-rw-------. 1 root root 2368 Oct 24 2016 anaconda.ifcfg.log
-rw-------. 1 root root 35914 Oct 24 2016 anaconda.log
-rw-------. 1 root root 181001 Oct 24 2016 anaconda.program.log
-rw-------. 1 root root 300366 Oct 24 2016 anaconda.storage.log
-rw-------. 1 root root 114857 Oct 24 2016 anaconda.syslog
-rw-------. 1 root root 28547 Oct 24 2016 anaconda.xlog
-rw-------. 1 root root 115596 Oct 24 2016 anaconda.yum.log
drwxr-x---. 2 root root 4096 May 5 19:50 audit
-rw-r--r-- 1root root 2607 Jun 5 07:00 boot.log
-rw------- 1root utmp 0 Jun 1 03:31 btmp
-rw------- 1root utmp 768 May 5 18:38 btmp-20170601
drwxr-xr-x. 2 root root 4096 Oct 24 2016 ConsoleKit
-rw------- 1root root 24280 Jun 5 10:30 cron
-rw------- 1root root 128516 May 14 03:23 cron-20170514
-rw------- 1root root 129746 May 21 03:40 cron-20170521
-rw------- 1root root 128931 May 28 03:12 cron-20170528
-rw------- 1root root 129139 Jun 4 03:31 cron-20170604
drwxr-xr-x. 2 lp sys 4096 Aug 17 2013 cups
-rw-r--r-- 1root root 69891 Jun 5 07:00 dmesg
-rw-r--r-- 1root root 69891 May 29 07:00 dmesg.old
-rw------- 1root root 0 Jan 1 03:32 dracut.log
-rw-r--r--. 1 root root 345367 Oct 24 2016 dracut.log-20170101
drwxrwx--T. 2 root gdm 4096 Oct 24 2016 gdm
drwx------. 2 root root 4096 Jun 4 03:31 httpd
-rw-r--r--. 1 root root 145708 Jun 5 10:03 lastlog
-rw------- 1root root 1629 Jun 5 07:00 maillog
-rw------- 1root root 4465 May 13 08:19 maillog-20170514
-rw------- 1root root 5153 May 20 06:42 maillog-20170521
-rw------- 1root root 5153 May 27 06:30 maillog-20170528
-rw------- 1root root 5158 Jun 3 06:28 maillog-20170604
-rw------- 1root root 78017 Jun 5 10:05 messages
-rw------- 1root root 1317112 May 14 03:05 messages-20170514
-rw------- 1root root 1068946 May 21 03:06 messages-20170521
-rw------- 1root root 86245 May 28 03:05 messages-20170528
-rw------- 1root root 86065 Jun 4 03:06 messages-20170604
-rw-r-----. 1 mysql mysql 148857106048 Jun 5 10:30mysqld.log
drwxr-xr-x. 2 ntp ntp 4096 Nov 24 2013 ntpstats
-rw-r--r--. 1 root root 89 Oct 24 2016 pm-powersave.log
drwx------. 2 root root 4096 Aug 23 2010 ppp
drwxr-xr-x. 2 root root 4096 Oct 24 2016 prelink
drwxr-xr-x. 2 root root 4096 Jun 5 00:00 sa
drwx------. 3 root root 4096 Oct 24 2016 samba
-rw------- 1root root 855 Jun 5 10:03 secure
-rw------- 1root root 1855 May 12 18:54 secure-20170514
-rw------- 1root root 207 May 15 07:00 secure-20170521
-rw------- 1root root 1455 May 27 19:02 secure-20170528
-rw------- 1root root 1503 May 31 23:22 secure-20170604
-rw-------. 1 root root 0 Oct 24 2016 spice-vdagent.log
-rw------- 1root root 0 Jun 4 03:31 spooler
-rw------- 1root root 0 May 7 03:47 spooler-20170514
-rw------- 1root root 0 May 14 03:23 spooler-20170521
-rw------- 1root root 0 May 21 03:40 spooler-20170528
-rw------- 1root root 0 May 28 03:12 spooler-20170604
drwxr-x---. 2 root root 4096 Nov 23 2013 sssd
-rw-------. 1 root root 0 Oct 24 2016 tallylog
-rw-r--r--. 1 root root 0 Oct 24 2016 wpa_supplicant.log
-rw-rw-r--. 1 root utmp 325632 Jun 5 10:03 wtmp
-rw-r--r--. 1 root root 45892 Oct 25 2016 Xorg.0.log
-rw-r--r--. 1 root root 44521 Oct 24 2016 Xorg.0.log.old
-rw-r--r--. 1 root root 26974 Oct 24 2016 Xorg.9.log
-rw------- 1root root 1663 Apr 12 15:04 yum.log
-rw-------. 1 root root 6936 Dec 2 2016yum.log-20170101
drwxr-xr-x. 2 zabbix zabbix 4096 Oct 28 2016 zabbix
mysql.log日志文件占用空間過大,查看詳細(xì)日志內(nèi)容
[root@zabbix log]# tail mysqld.log
InnoDB: Your database may be corrupt or you may havecopied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB:http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: for more information.
170605 10:30:29 InnoDB: Error: page 14091764 log sequence number 462 2253153679
InnoDB: is in the future! Current system log sequencenumber 76 3553747713.
InnoDB: Your database may be corrupt or you may havecopied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB:http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: for more information.
查看數(shù)據(jù)庫文件夾硬盤空間使用情況
[root@zabbix log]# cd /var/lib/mysql
[root@zabbix mysql]# ll -h
total 324G
-rw-rw----. 1 mysql mysql 324G Jun 5 15:15 ibdata1
-rw-rw---- 1mysql mysql 5.0M Jun 5 15:15 ib_logfile0
-rw-rw---- 1mysql mysql 5.0M Jun 5 15:15 ib_logfile1
drwx------. 2 mysql mysql 4.0K Oct 28 2016 mysql
srwxrwxrwx 1mysql mysql 0 Jun 5 14:54 mysql.sock
drwx------. 2 mysql mysql 4.0K Jun 5 12:39 zabbix
-rw-r--r-- 1root root 251M Jun 5 15:15 zabbix02.sql
-rw-r--r-- 1root root 0 Jun 5 15:06 zabbix.sql
發(fā)現(xiàn)ibdata1 文件占用空間過大
2.清除zabbix數(shù)據(jù)庫歷史數(shù)據(jù)
1)查看哪些表的歷史數(shù)據(jù)比較多
mysql> SELECT table_name AS "Tables",round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"FROM information_schema.TABLES WHERE table_schema = 'zabbix' ORDER BY(data_length + index_length) DESC;
+----------------------------+------------+
| Tables | Size in MB |
+----------------------------+------------+
| events | 181851.00 |
| history_uint | 92227.30 |
| history | 4590.27|
| trends_uint | 3851.88 |
| history_str | 309.39 |
| trends | 156.75 |
| history_log | 89.17 |
| items | 36.41 |
| item_discovery | 6.23 |
| items_applications | 6.17 |
| alerts | 3.45 |
| triggers | 1.69 |
| p_w_picpaths | 1.53 |
| application_template | 0.63 |
| auditlog | 0.55 |
| acknowledges | 0.55 |
| functions | 0.48 |
| graphs | 0.47 |
| graphs_items | 0.44 |
| applications | 0.41 |
| hosts | 0.33 |
| hosts_groups | 0.27 |
| hostmacro | 0.22 |
| history_text | 0.22 |
| trigger_discovery | 0.20 |
| screens_items | 0.16 |
| profiles | 0.16 |
| graph_discovery | 0.13 |
| auditlog_details | 0.13 |
| hosts_templates | 0.11 |
| sysmaps_elements | 0.09 |
| host_discovery | 0.09 |
| interface | 0.09 |
| httptest | 0.08 |
| sysmaps | 0.08 |
| sysmaps_links | 0.06 |
| scripts | 0.06 |
| group_prototype | 0.06 |
| interface_discovery | 0.06 |
| drules | 0.05 |
| icon_mapping | 0.05 |
| users_groups | 0.05 |
| slideshows | 0.05 |
| media | 0.05 |
| icon_map | 0.05 |
| service_alarms | 0.05 |
| opgroup | 0.05 |
| slideshow_usrgrp | 0.05 |
| httptestitem | 0.05 |
| sysmaps_link_triggers | 0.05 |
| item_application_prototype | 0.05 |
| slideshow_user | 0.05 |
| application_prototype | 0.05 |
| maintenances_windows | 0.05 |
| slides | 0.05 |
| application_discovery | 0.05 |
| maintenances_hosts | 0.05 |
| httpstepitem | 0.05 |
| screens | 0.05 |
| config | 0.05 |
| trigger_depends | 0.05 |
| optemplate | 0.05 |
| maintenances_groups | 0.05 |
| sysmap_usrgrp | 0.05 |
| screen_usrgrp | 0.05 |
| opcommand_hst | 0.05 |
| opmessage_usr | 0.05 |
| actions | 0.05 |
| maintenances | 0.05 |
| sysmap_user | 0.05 |
| screen_user | 0.05 |
| opcommand_grp | 0.05 |
| services_links | 0.05 |
| dservices | 0.05 |
| opmessage_grp | 0.05 |
| rights | 0.05 |
| services | 0.03 |
| opmessage | 0.03 |
| usrgrp | 0.03 |
| sysmap_element_url | 0.03 |
| regexps | 0.03 |
| media_type | 0.03 |
| dhosts | 0.03 |
| proxy_history | 0.03 |
| item_condition | 0.03 |
| users | 0.03 |
| globalmacro | 0.03 |
| proxy_dhistory | 0.03 |
| mappings | 0.03 |
| groups | 0.03 |
| dchecks | 0.03 |
| proxy_autoreg_host | 0.03 |
| operations | 0.03 |
| expressions | 0.03 |
| group_discovery | 0.03 |
| opconditions | 0.03 |
| sessions | 0.03 |
| httpstep | 0.03 |
| conditions | 0.03 |
| services_times | 0.03 |
| escalations | 0.03 |
| autoreg_host | 0.03 |
| valuemaps | 0.03 |
| sysmap_url | 0.03 |
| graph_theme | 0.03 |
| opcommand | 0.03 |
| timeperiods | 0.02 |
| opinventory | 0.02 |
| globalvars | 0.02 |
| host_inventory | 0.02 |
| dbversion | 0.02 |
| housekeeper | 0.02 |
| ids | 0.02 |
+----------------------------+------------+
113 rows in set (7.31 sec)
可以看到events、history和history_uint這三個表的歷史數(shù)據(jù)最多。
由于數(shù)據(jù)量太大,按照普通的方式delete數(shù)據(jù)的話基本上不太可能。
所以決定直接采用truncatetable的方式來快速清空這些表的數(shù)據(jù),再使用mysqldump導(dǎo)出數(shù)據(jù),刪除共享表空間數(shù)據(jù)文件,重新導(dǎo)入數(shù)據(jù)。其中events空間過大,嘗試truncatetable無果,果斷拋棄
2)停止相關(guān)服務(wù),避免寫入數(shù)據(jù)
[root@zabbix ~]#/etc/init.d/zabbix_server stop
[root@zabbix ~]#/etc/init.d/httpd stop
3)清空歷史數(shù)據(jù)
[root@zabbix ~]#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.備份數(shù)據(jù)庫
[root@zabbix~]#mysqldump -uroot -p zabbix > /data/zabbix.sql
4.停止數(shù)據(jù)庫并刪除共享表空間數(shù)據(jù)文件
1)停止數(shù)據(jù)庫
[root@zabbix ~]#/etc/init.d/mysqld stop
2)刪除共享表空間數(shù)據(jù)文件
[root@zabbix ~]#cd /var/lib/mysql
[root@zabbix ~]#rm -rf ib*
[root@zabbix ~]#mkdir /tmp/zabbix
[root@zabbix ~]#mv zabbix/* /tmp/zabbix
5.增加innodb_file_per_table參數(shù)
[root@zabbix ~]#vi /etc/my.cnf
在[mysqld]下設(shè)置
innodb_file_per_table=1
6.啟動mysql
[root@zabbix ~]#/etc/init.d/mysqld start
7.查看innodb_file_per_table參數(shù)是否生效
[root@zabbix ~]#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.重新導(dǎo)入數(shù)據(jù)庫
mysql >use zabbix;
mysql >source mysql.sql
9.最后,恢復(fù)相關(guān)服務(wù)進(jìn)程
[root@zabbix ~]#/etc/init.d/zabbix_server start
[root@zabbix ~]#/etc/init.d/httpd start
恢復(fù)完服務(wù)之后,查看/分區(qū)的容量就下去了,之前是50%,處理完之后變成了2%??梢娖涑尚?/p>
[root@zabbix ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_zabbix-lv_root 1000G 13G 936G 2% /
tmpfs 7.8G 0 7.8G 0% /dev/shm
/dev/sda1 485M 39M 421M 9% /boot
mysql> select table_name, (data_length+index_length)/1024/1024 as total_mb,table_rows from information_schema.tables where table_schema='zabbix' order bytable_rows;
+----------------------------+---------------+------------+
| table_name | total_mb | table_rows |
+----------------------------+---------------+------------+
| slideshows | 0.04687500 | 0 |
| globalvars | 0.01562500 | 0 |
| proxy_history | 0.03125000 | 0 |
| icon_map | 0.04687500 | 0 |
| sysmaps_links | 0.06250000 | 0 |
| service_alarms | 0.04687500 | 0 |
| opinventory | 0.01562500 | 0 |
| slideshow_usrgrp | 0.04687500 | 0 |
| proxy_dhistory | 0.03125000 | 0 |
| sysmaps_link_triggers | 0.04687500 | 0 |
| proxy_autoreg_host | 0.03125000 | 0 |
| application_prototype | 0.04687500 | 0 |
| maintenances_windows | 0.04687500 | 0 |
| item_application_prototype | 0.04687500 | 0 |
| host_inventory | 0.01562500 | 0 |
| slideshow_user | 0.04687500 | 0 |
| application_discovery | 0.04687500 | 0 |
| maintenances_hosts | 0.04687500 | 0 |
| opconditions | 0.03125000 | 0 |
| slides | 0.04687500 | 0 |
| maintenances_groups | 0.04687500 | 0 |
| sysmap_usrgrp | 0.04687500 | 0 |
| screen_usrgrp | 0.04687500 | 0 |
| opcommand_hst | 0.04687500 | 0 |
| maintenances | 0.04687500 | 0 |
| housekeeper | 0.01562500 | 0 |
| sysmap_user | 0.04687500 | 0 |
| screen_user | 0.04687500 | 0 |
| opcommand_grp | 0.04687500 | 0 |
| services_times | 0.03125000 | 0 |
| escalations | 0.03125000 | 0 |
| sysmap_url | 0.03125000 | 0 |
| opcommand | 0.03125000 | 0 |
| services_links | 0.04687500 | 0 |
| sysmap_element_url | 0.03125000 | 0 |
| icon_mapping | 0.04687500 | 0 |
| timeperiods | 0.01562500 | 0 |
| services | 0.03125000 | 0 |
| httptest | 0.07812500 | 1 |
| sysmaps_elements | 0.09375000 | 1 |
| dbversion | 0.01562500 | 1 |
| sysmaps | 0.07812500 | 1 |
| config | 0.04687500 | 1 |
| autoreg_host | 0.03125000 | 1 |
| globalmacro | 0.03125000 | 2 |
| optemplate | 0.04687500 | 2 |
| opmessage_usr | 0.04687500 | 2 |
| graph_theme | 0.03125000 | 2 |
| media | 0.04687500 | 3 |
| users_groups | 0.04687500 | 3 |
| httptestitem | 0.04687500 | 3 |
| scripts | 0.06250000 | 3 |
| dchecks | 0.03125000 | 3 |
| opgroup | 0.04687500 | 3 |
| users | 0.03125000 | 3 |
| httpstep | 0.03125000 | 3 |
| regexps | 0.03125000 | 3 |
| media_type | 0.03125000 | 3 |
| drules | 0.04687500 | 3 |
| expressions | 0.03125000 | 4 |
| opmessage_grp | 0.04687500 | 4 |
| usrgrp | 0.03125000 | 5 |
| opmessage | 0.03125000 | 6 |
| httpstepitem | 0.04687500 | 9 |
| actions | 0.04687500 | 9 |
| dhosts | 0.03125000 | 12 |
| operations | 0.03125000 | 14 |
| dservices | 0.04687500 | 14 |
| group_prototype | 0.06250000 | 15 |
| conditions | 0.03125000 | 17 |
| rights | 0.04687500 | 17 |
| group_discovery | 0.03125000 | 19 |
| valuemaps | 0.03125000 | 21 |
| screens | 0.04687500 | 24 |
| groups | 0.03125000 | 31 |
| item_condition | 0.03125000 | 31 |
| sessions | 0.03125000 | 39 |
| ids | 0.01562500 | 46 |
| trigger_depends | 0.04687500 | 51 |
| mappings | 0.03125000 | 100 |
| hosts_templates | 0.12500000 | 181 |
| p_w_picpaths | 1.53125000 | 192 |
| host_discovery | 0.09375000 | 361 |
| interface_discovery | 0.06250000 | 515 |
| hosts | 0.32812500 | 516 |
| profiles | 0.17187500 | 659 |
| interface | 0.09375000 | 691 |
| auditlog_details | 0.12500000 | 707 |
| events | 0.17187500 | 759 |
| screens_items | 0.15625000 | 805 |
| history_text | 0.18750000 | 1201 |
| graph_discovery | 0.12500000 | 1217 |
| graphs | 0.46875000 | 1330 |
| hosts_groups | 0.26562500 | 1381 |
| hostmacro | 0.21875000 | 1569 |
| trigger_discovery | 0.20312500 | 2017 |
| acknowledges | 0.54687500 | 2408 |
| auditlog | 0.53125000 | 2546 |
| graphs_items | 0.43750000 | 2706 |
| triggers | 0.68750000 | 2900 |
| functions | 0.50000000 | 3134 |
| application_template | 0.51562500 | 4364 |
| applications | 0.42187500 | 4512 |
| history | 0.46875000 | 4780 |
| alerts | 3.46875000 | 6242 |
| item_discovery | 5.54687500 | 26567 |
| items_applications | 6.54687500 | 30181 |
| items | 24.12500000 | 34425 |
| history_uint | 12.03125000 | 123327 |
| history_log |  
當(dāng)前題目:Zabbix-Server數(shù)據(jù)庫mysql的libdata1mysqllog文件過大
文章URL:http://bm7419.com/article44/goccee.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供營銷型網(wǎng)站建設(shè)、小程序開發(fā)、網(wǎng)頁設(shè)計(jì)公司、App設(shè)計(jì)、網(wǎng)站策劃、搜索引擎優(yōu)化
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)