In practice, the libdata1 file of the Zabbix-Server database MySQL is too large.

In practice, the libdata1 file of the Zabbix-Server database MySQL is too large.

Today, the root space of our zabbix-server machine is insufficient. I found that the libdata1 file under/var/lib/mysql/is too large, which has reached 41 GB. I immediately thought about the reason for zabbix's database. Then Baidu and Google learned that zabbix's database adopts the shared tablespace mode. As data grows, ibdata1 is getting bigger and bigger, which may affect performance, in addition, innodb stores data and indexes under ibdata1.

Shared tablespace mode:

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

Independent tablespace mode:

Advantages:
1. Each table has its own independent tablespace.
2. Data and indexes of each table are stored in its own tablespace.
3. A single table can be moved in different databases.
4. space can be recycled (table space cannot be recycled automatically in the drop/truncate table operation mode)
5. For tables that use independent tablespaces, no matter how they are deleted, the tablespace fragments will not seriously affect performance, and there is a chance to process them.
Disadvantages:
A single table is larger than a shared space.

Conclusion:
Shared tablespace has some advantages in the Insert operation, but it does not do well in other independent tablespaces. Therefore, we need to change it to an independent tablespace.
When the independent tablespace is enabled, adjust the innodb_open_files parameter properly.

Next, let's talk about how to change the zabbix database to an independent tablespace mode.

1. view the file size

[Root @ localhost ~] # Cd/var/lib/mysql

[Root @ localhost ~] # Ls-lh

-Rw ---- 1 mysql 41G Nov 24 13:31 ibdata1

-Rw ---- 1 mysql 5.0 M Nov 24 ib_logfile0

-Rw ---- 1 mysql 5.0 M Nov 24 ib_logfile1

Drwx ------ 2 mysql 1.8 M Nov 24 zabbix

We can see that the size of the previously shared table data space file ibdata1 has reached 41 GB.

2. Clear historical data of the zabbix Database

1) check which tables have more historical data

[Root @ localhost ~] # Mysql-uroot-p

Mysql> 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 | 1, 1020.00000000 | 123981681 |

| History_log | 0.04687500 | 0 |

...

| History_text | 0.04687500 | 0 |

| History_uint | 3400.98437500 | 1, 34000562 |

| History_uint_sync | 0.04687500 | 0 |

We can see that history and history_uint have the most historical data.

In addition, some data exists in trends and trends_uint.

Because the data volume is too large, it is basically impossible to delete the data in the normal way.

Therefore, we decided to directly use the truncate table method to quickly clear the data of these tables, and then use mysqldump to export the data, delete the shared tablespace data file, and re-import the data.

2) Stop related services to avoid Data Writing

[Root @ localhost ~] #/Etc/init. d/zabbix_server stop

[Root @ localhost ~] #/Etc/init. d/httpd stop

3) Clear historical data

[Root @ localhost ~] # 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. I mounted an NFS file to the backup database because I/O has insufficient space.

[Root @ localhost ~] # Mysqldump-uroot-p zabbix>/data/zabbix. SQL

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

1) Stop the database

[Root @ localhost ~] #/Etc/init. d/mysqld stop

2) Delete the shared tablespace Data File

[Root @ localhost ~] # Cd/var/lib/mysql

[Root @ localhost ~] # Rm-rf ib *

5. added the innodb_file_per_table parameter.

[Root @ localhost ~] # Vi/etc/my. cnf

Set in [mysqld]

Innodb_file_per_table = 1

6. Start mysql

[Root @ localhost ~] #/Etc/init. d/mysqld start

7. Check whether the innodb_file_per_table parameter takes effect.

[Root @ localhost ~] # 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

[Root @ localhost ~] # Mysqldump-uroot-p zabbix </data/zabbix. SQL

9. Finally, restore the service process.

[Root @ localhost ~] #/Etc/init. d/zabbix_server start

[Root @ localhost ~] #/Etc/init. d/httpd start

After the service is restored, the capacity of the View/partition goes down. The previous value is 99%. After processing, it becomes 12%. The effect is obvious.

 

Some Zabbix Tutorials:

Install and deploy the distributed monitoring system Zabbix 2.06

Install and deploy the distributed monitoring system Zabbix 2.06

Install and deploy Zabbix in CentOS 6.3

Zabbix distributed monitoring system practice

Under CentOS 6.3, Zabbix monitors apache server-status

Monitoring MySQL database Parameters Using Zabbix in CentOS 6.3

Install Zabbix 2.0.6 in 64-bit CentOS 6.2

ZABBIX details: click here
ZABBIX: click here

This article permanently updates the link address: