Http://heylinux.com/archives/2367.html
http://blog.csdn.net/ywh147/article/details/8996022
Students who have used MySQL have just started to touch the MyISAM table engine, the database of this engine will create three files: Table structure, table index, table data space. We can migrate a database directory directly to a different database and work properly.
When you use InnoDB, however, everything changes. InnoDB default will store all database InnoDB engine table data in a shared space: ibdata1, so feel uncomfortable, add and delete the database, ibdata1 file does not automatically shrink, the backup of a single database will become a problem. You can usually only export data using mysqldump and then import to resolve this issue.
In the MySQL configuration file [mysqld] section, add the innodb_file_per_table parameter, you can modify the InnoDB as a stand-alone tablespace pattern, and each table in each database will generate a data space.
Stand-alone table space
Advantages:
1. Each table has its own self-contained table space.
2. The data and index for each table will exist in the table space itself.
3. You can implement a single table to move in different databases.
4. Space can be recycled (drop/truncate table-mode operation table space is not automatically recycled)
5. For tables that use stand-alone table spaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too severely, and there is a chance to process it.
Disadvantages:
Single-table increments are larger than shared space.
Conclusion:
Shared tablespace has some advantages over insert operations, but it does not perform well in any other table space.
When you enable stand-alone table spaces, adjust the innodb_open_files parameters appropriately.
Below, is a MySQL database for online Zabbix history record too much to cause the Ibdata1 file too large practical solution steps
1. View File size
$ sudo cd/var/lib/mysql
$ ls-lh
2 |
-rw-r--r-- 1 root root 0 Dec 1 14:31 debian-5.1.flag |
3 |
-rw-rw---- 1 mysql mysql 5.0M Jan 17 21:31 ib_logfile0 |
4 |
-rw-rw---- 1 mysql mysql 5.0M Jan 17 21:29 ib_logfile1 |
5 |
-rw-rw---- 1 mysql mysql 14G Jan 17 21:31 ibdata1 |
6 |
drwx------ 2 mysql root 4.0K Dec 1 14:31 mysql |
7 |
-rw-rw---- 1 root root 6 Dec 1 14:31 mysql_upgrade_info |
8 |
drwx------ 2 mysql mysql 4.0K Jan 17 21:29 zabbix |
Shared table data space file Ibdata1 size has reached 14G
Log in to MySQL to see which tables occupy space
$ mysql-uroot-p
01 |
mysql > selecttable_name, (data_length+index_length)/1024/1024 as total_mb, table_rows from information_schema.tables where table_schema=‘zabbix‘; |
03 |
+-----------------------+---------------+------------+ |
04 |
| table_name | total_mb | table_rows | |
05 |
+-----------------------+---------------+------------+ |
06 |
| acknowledges | 0.06250000 | 0 | |
08 |
| help_items | 0.04687500 | 103 | |
09 |
| history| 9678.00000000 | 123981681 | |
10 |
| history_log | 0.04687500 | 0 | |
12 |
| history_text | 0.04687500 | 0 | |
13 |
| history_uint | 5386.98437500 | 57990562 | |
14 |
| history_uint_sync | 0.04687500 | 0 | |
16 |
| timeperiods | 0.01562500 | 0 | |
17 |
| trends | 54.54687500 | 537680 | |
18 |
| trends_uint | 100.53125000 | 1035592 | |
20 |
103 rows inset(1.46 sec) |
It can be seen that the history table records have reached the 9g,123981681 bar, that is, 120 million, while the history_unit is also relatively large, reaching 5G, about 60 million;
In addition, there are some data in trends,trends_uint.
Because the amount of data is too large, it is almost impossible to delete the data in the normal way.
Because we automatically send the data report every day, we decide to use TRUNCATE table 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.
2. Stop related services and avoid writing data
$ sudo/etc/init.d/zabbix-server Stop
$ sudo/etc/init.d/apache2 Stop
3. Clear Historical data
$ mysql-uroot-p
04 |
mysql > truncate table history; |
05 |
Query OK, 123981681 rows affected (0.23 sec) |
07 |
mysql > optimize table history; |
10 |
mysql > truncate table history_uint; |
11 |
Query OK, 57990562 rows affected (0.12 sec) |
13 |
mysql > optimize table history_uint; |
16 |
mysql > truncate table trends; |
17 |
Query OK, 537680 rows affected (0.04 sec) |
19 |
mysql > optimize table trends; |
22 |
mysql > truncate table trends_uint; |
23 |
Query OK, 1035592 rows affected (0.02 sec) |
25 |
mysql > optimize table trends_uint; |
4. Backing up data
$ mysqldump-uroot-p Zabbix > ~/zabbix.sql
5. Stop the database
$ sudo stop MySQL
6. Delete a shared tablespace data file
$ cd/var/lib/mysql
$ RM ib*
7. Add innodb_file_per_table Parameters
$ sudo vim/etc/mysql/my.cnf
Set under [Mysqld]
1 |
innodb_file_per_table=1 |
8. Start MySQL
$ sudo start MySQL
9. See if the parameters are valid
$ mysql-uroot-p
1 |
mysql> show variables like ‘%per_table%‘; |
2 |
+-----------------------+-------+ |
3 |
| Variable_name | Value | |
4 |
+-----------------------+-------+ |
5 |
| innodb_file_per_table | ON | |
6 |
+-----------------------+-------+ |
10. Re-import data
$ mysql-uroot-p Zabbix < ~/zabbix.sql
11. Write a script that automatically cleans up data every day, keeping 30 days of data
$ sudo vim/etc/cron.daily/clean_zabbix_olddata.sh
View Source print?
02 |
DATE=`date-d "30 days ago"` |
03 |
CLOCK=`date+%s -d "${DATE}"` |
04 |
MYSQL="mysql -uroot -p zabbix" |
06 |
forTABLE in historytrends |
08 |
$MYSQL -e "DELETE FROM ${TABLE} WHERE clock < ${CLOCK};" |
09 |
$MYSQL -e "OPTIMIZE TABLE ${TABLE};" |
10 |
$MYSQL -e "DELETE FROM ${TABLE}_uint WHERE clock < ${CLOCK};" |
11 |
$MYSQL -e "OPTIMIZE TABLE ${TABLE}_uint;" |
12. Finally, restore the relevant service process
$ sudo/etc/init.d/zabbix-server Start
$ sudo/etc/init.d/apache2 Start
Use parameter innodb_file_per_table to support MySQL innodb table data sharing space Auto shrink
