主页 / dba / 问题 / 8982
Accepted
Abdul Manaf
Asked: 2011-12-09 22:15:25 +0800 CST

减少mysql中ibdata大小的最佳方法是什么？

  • 772

我有一些生产服务器，其ibdata文件的大小每天都在增加。

它已经消耗了 290GB 的空间。

服务器中的表主要是 InnoDB，并且有很高的读写请求。

日志文件大小也在增加。表中有大量数据。

如何控制两者的增长？

我没有使用innodb_file_per_table.

mysql innodb

1 个回答

  1. Best Answer

    请记住，InnoDB 基础架构中最繁忙的文件是 /var/lib/mysql/ibdata1

    该文件通常包含许多类别的信息（当innodb_file_per_table为 0 时）

    • 表数据
    • 表索引
    • MVCC（多版本并发控制）数据
      • 回滚段
      • 撤消表空间
    • 表元数据
    • 见图示

    许多人创建多个 ibdata 文件，希望获得更好的磁盘空间管理和性能。它没有帮助。

    不幸的是，针对存储在 ibdata1 中的 InnoDB 表的 OPTIMIZE TABLE 做了两件事：

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

    您可以从 ibdata1 中分离表数据和表索引，并使用innodb_file_per_table独立管理它们。要一劳永逸地缩小 ibdata1，您必须执行以下操作

    步骤 01) MySQLDump 所有数据库到一个 SQL 文本文件（称为 SQLData.sql）（更多细节在这里）

    步骤 02)删除所有数据库（除了mysql、performance_schema和information_schema）

    步骤 03)关闭 mysql

    步骤 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)删除 ibdata1、ib_logfile0 和 ib_logfile1

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

    步骤 06)重启 mysql

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

    步骤 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实际上会缩小。

    在我作为 MySQL DBA 的职业生涯中，我已经多次这样做了

    事实上，我第一次这样做时，我将一个 50GB 的 ibdata1 文件折叠成 500MB。

    试试看。如果您对此还有其他问题，请给我发电子邮件。相信我。这将在短期和长期有效。！！！

    如果您想查看 MyISAM 和 InnoDB 中存储了多少实际数据，请运行以下查询：

    SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;
    • 117

相关问题