欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

回收InnoDB表空间_MySQL

程序员文章站 2022-06-08 21:22:49
...
bitsCN.com

回收InnoDB表空间

以下论述均假定innodb_file_per_table开启

先用常规optimize回收:

[plain]

mysql> select count(*) from t;  

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

| count(*) |

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

| 1200096 |

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

[mysql@even employees]$ ls -alh t.ibd

-rw-rw---- 1 mysql dba 72M 10-08 17:39 t.ibd

mysql> delete from t limit 800000;

mysql> select count(*) from t;

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

| count(*) |

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

| 400096 |

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

[mysql@even employees]$ ls -alh t.ibd

-rw-rw---- 1 mysql dba 72M 10-08 17:41 t.ibd

mysql> optimize table t;

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

| Table | Op | Msg_type | Msg_text |

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

| employees.t | optimize | note | Table does not support optimize, doing recreate + analyze instead |

| employees.t | optimize | status | OK |

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

[mysql@even employees]$ ls -alh t.ibd

-rw-rw---- 1 mysql dba 29M 10-08 17:43 t.ibd



来看下这种方法的缺陷

[plain]

mysql> show processlist;

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

| Id | User | Host | db | Command | Time | State | Info |

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

| 5 | root | localhost | NULL | Query | 0 | NULL | show processlist |

| 6 | root | localhost | employees | Query | 64 | copy to tmp table | optimize table t |

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

mysqladmin debug结果如下

[plain]

Thread database.table_name Locked/Waiting Lock_type

6 employees.t Locked - read Read lock without concurrent inserts

用optimize缺点显而易见:读锁,特别当你有大表时性能恐怕会深受影响

这里推荐使用percona公司:pt-online-schema-change,避免加锁

[plain]

$ pt-online-schema-change -uroot -poracle --alter "ENGINE=InnoDB" D=employees,t=t --execute

执行pt工具时有可能发生的类似错误:

[plain]

Cannot chunk the original table There is no good index and the table is oversized

这是因为被作用的表需要含有主键或者唯一索引,这或许也能成为这款工具的小bug吧

bitsCN.com
相关标签: 回收 空间