Shulou(Shulou.com)06/01 Report--

Today, our zabbix-server machine has been online for half a year, taking up 500 gigabytes of space. I found that the libdata1 file under / var/lib/mysql/ is too large, reaching 300 gigabytes. I immediately thought of zabbix's database reasons, zabbix's database his table schema is a shared tablespace schema, as the data grows, ibdata1 becomes larger and larger, performance will have an impact, and innodb puts both data and indexes under ibdata1.

Shared tablespace mode:

By default, InnoDB stores all the table data of the database InnoDB engine in a shared space: ibdata1, which makes it uncomfortable. When adding or deleting a database, the ibdata1 file will not automatically shrink, and the backup of a single database will become a problem. Usually the data can only be exported using mysqldump and then imported to solve this problem.

Independent tablespace schema:

Advantages:

1. Each table has its own independent table space.

two。 The data and indexes of each table are stored in its own tablespace.

3. It is possible to move a single table in different databases.

4. Space can be reclaimed (tablespaces can not be automatically recycled in drop/truncate table mode)

5. For tables that use independent tablespaces, no matter how much they are deleted, the fragmentation of the tablespace will not seriously affect performance, and there is still a chance to deal with it.

Disadvantages:

The increase of a single table is greater than that of shared space.

Conclusion:

Shared tablespaces have some advantages in Insert operations, but nothing else performs as well as independent tablespaces, so we need to change to independent tablespaces.

When independent tablespaces are enabled, adjust the innodb_open_files parameters reasonably.

Let's talk about how to change the zabbix database to an independent tablespace schema.

View file size

L View system space utilization

[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

View file space usage

[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 207May 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

The mysql.log log file takes up too much space. View the details of the log.

[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.

Check the hard disk space usage of the database folder

[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

Found that the ibdata1 file takes up too much space.

two。 Clear zabbix database historical data

1) check which tables have more historical data

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)

You can see that the three tables events, history, and history_uint have the most historical data.

Because of the large amount of data, it is basically impossible to delete data in the normal way.

So I decided to use truncatetable to quickly empty the data of these tables, then use mysqldump to export the data, delete the shared tablespace data file, and re-import the data. Among them, the events space is too large, try truncatetable in vain, resolutely abandon

2) stop related services to avoid writing data

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

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

3) clear the historical data

[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. Backup database

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

4. Stop the database and delete the shared tablespace data file

1) stop the database

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

2) Delete the shared tablespace data file

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

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

[root@zabbix ~] # mkdir / tmp/zabbix

[root@zabbix ~] # mv zabbix/* / tmp/zabbix

5. Add innodb_file_per_table parameters

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

Set under [mysqld]

Innodb_file_per_table=1

6. Start mysql

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

7. Check whether the innodb_file_per_table parameter is in effect

[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. Re-import the database

Mysql > use zabbix

Mysql > source mysql.sql

9. Finally, resume the related service process

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

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

After the service is restored, the capacity of the view / partition goes down, from 50% before to 2% after processing. It can be seen that its effect

[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 plus 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 | 0.03125000 |

| | 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 | 88.67187500 | 479448 | |

| | history_str | 76.17187500 | 743970 | |

| | trends | 122.68750000 | 2023329 | |

| | trends_uint | 3076.00000000 | 51097289 | |

+-+

113 rows in set (0.55 sec)

Note: truncate table events, I haven't seen the effect for too long. Try using delete shell script.

#! / bin/bash

# delete events table

# edit by john zheng 20170606

I, 1

While [$I-lt events_id maximum]

Do

Echo "$I"

Mysql-uroot-p password-e "

Use zabbix

Delete from events where eventid

< $i;" i=`expr $i '+' 100000` done 尝试失败，时间更久，直接放弃，只保留events创建表语句 mysql>

Show create table events

+- - - - -+

| | Table | Create Table |

+- - - - -+

| | events | CREATE TABLE `events` (

`eventid` bigint (20) unsigned NOT NULL

`source`int (11) NOT NULL DEFAULT'0'

`object` int (11) NOT NULL DEFAULT'0'

`objectid` bigint (20) unsigned NOT NULL DEFAULT'0'

`clock`int (11) NOT NULL DEFAULT'0'

`value`int (11) NOT NULL DEFAULT'0'

`roomged` int (11) NOT NULL DEFAULT'0'

`ns`int (11) NOT NULL DEFAULT'0'

PRIMARY KEY (`eventid`)

KEY `source` (`source`, `object`, `objectid`, `clock`)

KEY `events_ 2` (`source`, `object`, `clock`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

+- - - - -+

1 row in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.