mysqlinnodb表过大问题

  • Z时代
  • 2024-01-10
  • 分类：综合

database

Solution One：

To shrinking a shared InnoDB tablespace is: 
1. Backup *all* InnoDB tables with mysqldump. 
2. Drop all of the InnoDB tables. 
3. Physically delete the ibdata1 file at the filesystem interface. 
4. Restart MySQL Server, which recreates a new, small tablespace file. 
5. Restore all your InnoDB tables, which expands the tablespace file as needed. 

Solution Two：

To shrink an .ibd file it"s enough to run ALTER TABLE t1 ENGINE INNODB. It will rebuild the tablespace and the new one will be the most compact. I think OPTIMIZE TABLE does exactly the same internally. ALTER TABLE however is preferred because you can use pt-online-schema-change to avoid blocking the table.

To get better understanding how data use the tablespace I would recommend InnoDB tools https://github.com/jeremycole/innodb_ruby

For example, to check how many pages are user by PRIMARY secondary indexes and how many free pages:

# /usr/local/bin/innodb_space -f actor.ibd space-extents-illustrate
  Start Page ╭────────────────────────────────────────────────────────────────╮
           0 │███▄▂░░                                                         │
             ╰────────────────────────────────────────────────────────────────╯
Legend (█ = 1 page):
  Page Type                                                         Pages    Ratio
  █ System                                                              3   42.86%
  █ Index 15                                                            1   14.29%
  █ Index 16                                                            1   14.29%
  ░ Free space                                                          2   28.57%

以上是 mysqlinnodb表过大问题 的全部内容， 来源链接： utcz.com/z/534206.html

回到顶部