熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> MySQL >> 正文

回收InnoDB表空間

2013-11-23 21:01:57  來源: MySQL 

  以下論述均假定innodb_file_per_table開啟

  先用常規optimize回收

  [plain]

  <pre name=code class=plain>mysql> select count(*) from t;

  ++

  | count(*) |

  ++

  |  |

  ++

  [mysql@even employees]$ ls alh tibd

  rwrw mysql dba M : tibd

  mysql> delete from t limit ;

  mysql> select count(*) from t;

  ++

  | count(*) |

  ++

  |   |

  ++

  [mysql@even employees]$ ls alh tibd

  rwrw mysql dba M : tibd

  mysql> optimize table t;

  +++++

  | Table       | Op       | Msg_type | Msg_text                                                          |

  +++++

  | employeest | optimize | note     | Table does not support optimize doing recreate + analyze instead |

  | employeest | optimize | status   | OK                                                                |

  +++++

  [mysql@even employees]$ ls alh tibd

  rwrw mysql dba M : tibd</pre><br><br>

  來看下這種方法的缺陷

  [plain]

  mysql> show processlist;

  +++++++++

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

  +++++++++

  |  | root | localhost | NULL      | Query   |    | NULL              | show processlist |

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

  +++++++++

  mysqladmin debug結果如下

  [plain]

  Thread databasetable_name          Locked/Waiting        Lock_type

         employeest                 Locked read         Read lock without concurrent inserts

  用optimize缺點顯而易見讀鎖特別當你有大表時性能恐怕會深受影響

  這裡推薦使用percona公司ptonlineschemachange避免加鎖

  [plain]

  $ ptonlineschemachange uroot poracle alter ENGINE=InnoDB D=employeest=t execute

  執行pt工具時有可能發生的類似錯誤

  [plain]

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

  這是因為被作用的表需要含有主鍵或者唯一索引這或許也能成為這款工具的小bug吧


From:http://tw.wingwit.com/Article/program/MySQL/201311/29468.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.