Thoughts on optimizing zabbix table structure

Just a year later, I found that zabbix's mysql backup file was unusually large during routine checks on server backup data, considering that zabbix regularly sends alerts and SQL records stored during monitoring during routine monitoring of server status, I checked the database table of zabbix, it is found that the database directory of zabbix under the mysql System database file is not very large, that is, several hundred mb. However, it is found that the ibdata1 file under the same directory is unusually large, reaching 4.7 GB.

Zabbix's daily monitoring data volume is large, but why does it feel that it is not stored in the zabbix directory? Why is ibdata1 so huge?

I checked the database storage principles of zabbix on the Internet and found that the zabbix database uses the innodb engine's shared tablespace. innodb stores data and indexes under ibdata1. As the data grows, ibdata1 will grow bigger and bigger. Performance may be affected.

Then I wonder why zabbix uses innodb's shared tablespace to store data.

Bytes ----------------------------------------------------------------------------------------

If you have used MySQL, The MyISAM Table engine is the most frequently used at the beginning. The database of this engine creates three files: Table Structure, table index, and table data space. You can migrate a database directory to another database.

However, when you use InnoDB, everything changes. 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.

In the MySQL configuration file [mysqld], add the innodb_file_per_table parameter to change InnoDB to an independent tablespace mode. Each table in each database generates a data space.

Independent tablespace

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 recycling: The drop/truncate table operations cannot be performed automatically)

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:

The shared tablespace has some advantages in the Insert operation, but it does not do well in other independent tablespaces.

When the independent tablespace is enabled, adjust the innodb_open_files parameter properly.

Bytes ----------------------------------------------------------------------------------------

By default, innodb stores all the table data of the InnoDB engine in a shared space, and the ibdata1 file does not automatically contract when adding or deleting databases, the backup of a single database will also become a problem.

Therefore, we decided to change the innodb shared tablespace to an independent tablespace, and then we would not back up the entire database file when backing up the zabbix database separately, resulting in a waste of system resources, the last step is to regularly clean up the zabbix history script, so that you will not worry about the excessive backup file in the future, resulting in insufficient server hard disk capacity.

OK, start working ....

System Environment:

-------------------------------

SYSYTEM: CentOS 6.3x64

APACHE: httpd-2.4.4

MYSQL: mysql-5.6.10

PHP: php-5.4.13

ZABBIX: Zabbix 2.2.0rc2

-------------------------------

1. View bdata1 File Size

# Cd/usr/local/mysql/data

# Du-sh *

-------------------------------------

4.0 K file-test.xxx.cn.pid

4.0 K file-test-relay-bin.000001

4.0 K file-test-relay-bin.index

4.7G ibdata1

5.0 M ib_logfile0

5.0 M ib_logfile1

780 K mysql

146 M Syslog

4.0 K webserver01.xxx.cn. pid

43 M zabbix

--------------------------------------

The size of the shared table data space file ibdata1 has reached 4.7 GB.

Log on to MySQL and check which tables occupy space.

# Mysql-uroot-p

-----------------------------------------

> 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, 2841.00000000 | 34957883 |

| History_log | 0.04687500 | 0 |

...

| History_text | 0.04687500 | 0 |

| History_uint | 1563.98437500 | 1, 23940661 |

| History_uint_sync | 0.04687500 | 0 |

...

| Timeperiods | 0.01562500 | 0 |

| Trentds | 17.89564700 | 145780 |

| Trends_uint | 25.567894000 | 271256 |

...

103 rows in set (1.46 sec)

As you can see, the history Table contains 3 GB and 34957883 records, that is, more than records. At the same time, history_unit is large, with 1 GB and more than records;

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.

Because we automatically send data reports every day, we decided to directly use the truncate table method to quickly clear the data of these tables, and then use mysqldump to export data and delete the shared tablespace data files, re-import data.

2. Stop related services to avoid writing data during transformation

#/Etc/init. d/zabbix_server stop

#/Usr/local/apache2/bin/apachectl stop

3. Clear historical data

# Mysql-uroot-p123456

---------------------------------------------

Mysql> use zabbix;

Mysql> truncate table history;

Mysql> optimize table history;

Mysql> truncate table history_uint;

Mysql> optimize table history_uint;

Mysql> truncate table trends;

Mysql> optimize table trends;

Mysql> truncate table trends_uint;

Mysql> optimize table trends_uint;

------------------------------------------

4. Back up data

# Mysqldump-uroot-p123456 zabbix> ~ /Zabbix_bak. SQL

5. Stop the database

# Service mysqld stop

6. Delete shared tablespace data files

# Cd/usr/local/mysql/data

# Rm-rf ibdata1 ib_logfile0 ib_logfile1

7. added the innodb_file_per_table independent tablespace parameter.

# Vi/etc/my. cnf

Add a row under [mysqld]

-------------------------------

Innodb_file_per_table = 1

-------------------------------

8. Start MySQL

# Service mysqld start

9. Check whether the parameters take effect

# Mysql-uroot-p123456

----------------------------------------------

Mysql> show variables like '% per_table % ';

+ ----------------------- + ------- +

| Variable_name | Value |

+ ----------------------- + ------- +

| Innodb_file_per_table | ON |

+ ----------------------- + ------- +

1 row in set (0.00 sec)

------------------------------------------------

10. re-import data

# Mysql-uroot-p123456 zabbix <~ /Zabbix. SQL

11. Write a script for zabbix to automatically clean up historical data and keep the data for 30 days.

# Vi/etc/rc. d/zabbix_olddata_clean.sh

---------------------------------------------

#! /Bin/bash

DATE = 'date-d "30 days ago "'

CLOCK = 'date + % s-d "$ {date }"'

USER = "root"

PW = "123456"

DB = "zabbix"

MYSQL = "mysql-u $ USER-p $ PW"

Function zabbix_olddata_clean (){

For TABLE in history trends

Do

$ {MYSQL} <EOF

USE $ {DB };

Delete from $ {TABLE} WHERE clock <$ {CLOCK };

Optimize table $ {TABLE };

Delete from $ {TABLE }_uint WHERE clock <$ {CLOCK };

Optimize table $ {TABLE} _ uint;

EOF

Done

}

Zabbix_olddata_clean

---------------------------------------------

12. Add the script to the scheduled task

# Crontab-e

Add a rule and execute this script at on the first day of each month.

---------------------------------

35 03 1 **/bin/sh/etc/rc. d/zabbix_olddata_clean.sh

---------------------------------

13. Restart related service processes

#/Etc/init. d/zabbix_server restart

#/Usr/local/apache2/bin/apachectl start

# Service crond restart

14. Verification

First, we will monitor the mysql database record logs.

# Tail-f/usr/local/mysql/log/mysql. log

Manually execute this script

# Sh/etc/rc. d/clean_zabbix_olddata.sh

---------------------------------------------

Table Op Msg_type Msg_text

Zabbix. history optimize status OK

Table Op Msg_type Msg_text

Zabbix. history_uint optimize status OK

Table Op Msg_type Msg_text

Zabbix. trends optimize status OK

Table Op Msg_type Msg_text

Zabbix. trends_uint optimize status OK

----------------------------------------------

View related database cleanup statements

----------------------------------------------------

......

6390 Query select database ()

6390 Init DB zabbix

6390 Query delete from history WHERE clock <1391861640

6390 Query optimize table history

140208 10:40:34 6390 Query delete from history_uint WHERE clock <1391861640

6390 Query optimize table history_uint

6391 Connect root @ localhost on

6391 Query select @ version_comment limit 1

6391 Query select database ()

6391 Init DB zabbix

6391 Query delete from trends WHERE clock <1391861640

6390 Quit

6391 Query optimize table trends

6391 Query delete from trends_uint WHERE clock <1391861640

6391 Query optimize table trends_uint

6391 Quit

.......

----------------------------------------------------

Finally, let's check the system database file.

# Cd/usr/local/mysql/data/

# Du-sh *

------------------------------------

4.0 K file-test.iscard.cn.pid

4.0 K file-test-relay-bin.000001

4.0 K file-test-relay-bin.index

10 M ibdata1

5.0 M ib_logfile0

5.0 M ib_logfile1

780 K mysql

146 M Syslog

4.0 K webserver01.iscard.cn. pid

42 M zabbix

------------------------------------

# Ls-lh

----------------------------------------

Total usage 21 M

-Rw ---- 1 mysql 6 February 8 08:30 file-test.xxx.cn.pid

-Rw ---- 1 mysql 117 September 9 14:09 file-test-relay-bin.000001

-Rw ---- 1 mysql 29 September 9 14:09 file-test-relay-bin.index

-Rw ---- 1 mysql 10 M February 8 10:44 ibdata1

-Rw ---- 1 mysql 5.0 M February 8 10:44 ib_logfile0

-Rw ---- 1 mysql 5.0 M February 8 10:44 ib_logfile1

Drwxr-x ---. 2 mysql root 4.0 K September 9 08:32 mysql

Drwxr-x --- 2 mysql 4.0 K February 7 18:22 Syslog

-Rwxr-x --- 1 mysql 2013 webserver01.xxx.cn. pid

Drwxr-x --- 2 mysql 12 K February 8 10:40 zabbix

------------------------------------------

Zabbix library files have been independently stored in the zabbix database directory. After ibdata1 is cleared and reduced, it will not look too bloated.

This article from "all the way to the North" blog, please be sure to keep this source http://showerlee.blog.51cto.com/2047005/1357212