主页 / dba / 问题 / 16747
Accepted
Ran
Asked: 2012-04-20 02:07:00 +0800 CST

MySql - 清理 ibdata1

  • 772

我有一个数据库，其整个大小约为 44GB，其中 ibdata1 约为 35GB。这没有意义，因为数据的大小不应超过 10GB。

我使用以下查询来估计数据大小：

    SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  30;

任何想法如何清理 ibdata1 以及为什么它增长了这么多？

顺便说一句，我使用 innodb_file_per_table

mysql innodb

2 个回答

  1. Best Answer

    安德烈亚斯首先得到了这个答案。为安德烈亚斯 +1 ！！！

    我想澄清为什么这个答案是唯一的方法以及如何去做。

    默认情况下，ibdata1 通常包含四种类型的信息

    • 表数据
    • 表索引
    • MVCC（多版本并发控制）数据
    • 表元数据

    对存储 ibdata1 的 InnoDB 表运行 OPTIMIZE TABLE 会使事情变得更糟，因为它的作用如下：

    • 使表的数据和索引在 ibdata1 内连续
    • 它使 ibdata1 增长，因为连续数据附加到 ibdata1

    您可以从 ibdata1 中分离表数据和表索引，并使用innodb_file_per_table独立管理它们。这样，只有 MVCC 和 Table MetaData 会驻留在 ibdata1 中。

    如果您已经使用它，那么您必须有一个存储大量 MVCC 以支持事务隔离的高写入环境。一旦持有 MVCC 的交易完成，该空间就被简单地放弃以供重复使用。

    要一劳永逸地缩小 ibdata1，您必须执行以下操作：

    STEP 01) MySQLDump 所有数据库到一个 SQL 文本文件（称为 SQLData.sql）

    STEP 02）删除所有数据库（mysql模式除外）

    步骤 03)service mysql stop

    STEP 04) 将以下行添加到 /etc/my.cnf

    [mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

    旁注：无论您为 innodb_buffer_pool_size 设置什么，确保 innodb_log_file_size 是 innodb_buffer_pool_size 的 25%。

    步骤 05)rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile

    此时，/var/lib/mysql 中应该只有 mysql 模式

    步骤 06)service mysql start

    这将重新创建 10MB 的 ibdata1，每个 1G 的 ib_logfile0 和 ib_logfile1

    STEP 07) 将 SQLData.sql 重新加载到 mysql 中

    ibdata1 将增长但仅包含表元数据

    每个 InnoDB 表都将存在于 ibdata1 之外

    假设您有一个名为 mydb.mytable 的 InnoDB 表。如果你进入 /var/lib/mysql/mydb，你会看到代表表的两个文件

    • mytable.frm（存储引擎头文件）
    • mytable.ibd（mydb.mytable 的表数据和表索引的主页）

    ibdata1 将不再包含 InnoDB 数据和索引。

    使用 /etc/my.cnf 中的 innodb_file_per_table 选项，您可以运行OPTIMIZE TABLE mydb.mytable;并且文件 /var/lib/mysql/mydb/mytable.ibd 实际上会缩小。

    我想建议您从我的帖子中查询 InnoDB 缓冲池大小的公式InnoDB 和 MyISAM 之间的主要区别是什么？

    • 13

  2. 我最好的猜测是它在某个时间点有那么大。

    ibdata1 永远不会缩小，因此它将保持最大大小。也许在你做 innodb_file_per_table 之前你已经有几个 if 表了。

    我知道缩小它的唯一方法是进行转储，删除文件（ibdata、iblog 和所有 .idb）并加载转储。

    • 3

相关问题