主页 / dba / 问题 / 13614
Accepted
Abdul Manaf
Asked: 2012-02-22 20:44:29 +0800 CST

Ibdata 的使用和建议？

  • 772

我有一个生产服务器

2011 年 11 月 5 日，ibdata 大小为 100G。在大约 3 个月内它增加到 200G。所以它的大小刚刚翻了一番。所以它是一个巨大的数据。目前 lvm 是 251G。

我几乎所有的表都带有 Innodb。我没有在每个表中使用 innodb。我在lvm上有我的idbata。所以我应该增加多少以备将来使用..？

或任何其他处理场景的最佳方式。

mysql innodb

2 个回答

  1. Best Answer

    您需要尽快停止使用 InnoDB（关闭innodb_file_per_table）和 LVM 快照。原因如下：

    我有一个使用 MySQL 作为其数据库的监控系统。它也关闭了 innodb_file_per_table 。在 6 个月的时间里，它增长到 1.3TB。

    我雇主的公司不能让这个监控系统停机。

    我试图为这个怪物数据库创建一个奴隶。我将 /var/lib/mysql 的 rsync 运行到从服务器。它的第一次通过需要 42 小时（这不是错字，1 天 18 小时）。第二遍耗时 84 小时（3 天 12 小时），只完成了 15%（复制了 220GB）。如您所见，我放弃了第二个 rsync。

    问题源于 ibdata1 为 1.3TB。

    当 innodb_file_per_table 关闭时，ibdata1 里面是什么？

    • 表数据页
    • 表索引页
    • 表元数据（表空间 ID 管理）
    • MVCC 数据（用于事务隔离、ACID 合规性）

    在写繁重的环境中，这四种类型的所有记录都被读取和/或写入。rsync 经历了一场噩梦，试图让 ibdata1 合并到足以将更改写入从服务器。根据我的高级 Linux 工程师同行的说法，LVM 快照再好不过了。

    你唯一的办法是开始使用 innodb_file_per_table。

    为了让您进一步了解此需求，请运行此查询（您应该需要 5-10 分钟）

    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;

    使用 InnoDB 的总数，将其与 ibdata1 的大小进行比较。你会发现可能会有30GB以下的差异。我在监控系统中对 ibdata1 进行了查询，并且只有 27GB 用于表元数据和新的 MVCC 信息。此文件将始终保持稳定增长。

    您可以执行以下操作：将所有 InnoDB 转换为使用 innodb_file_per_table。我在 StackOverflow 上写了一篇关于如何做到这一点的文章。您不仅可以让 easch 表存在于单独的文件中，还可以将 ibdata1 的增长率保持在最低限度。

    更新 2012-02-22 13:00 EST

    在执行 InnoDB 的 CleanUp 时，请确保您消除了多个 ibdata 文件的制作。您的设置应该是默认设置：

    [mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
    • 6

  2. 除了 Rolando 所说的，切换到每个文件设置可能会让您通过定期维护重新获得一些磁盘空间。

    对于单个 ibdata 文件中的所有内容，优化表对您的作用不大。但是，在每个文件设置中，您通常可以通过这样做来重新获得一些磁盘空间。优化表将有效地重建整个表，消除删除期间碎片留下的未使用空间。

    您可以通过运行 show table status 查看哪些表将从中受益。或者运行

    select table_name, data_length, data_free from information_schema.tables
where engine='innodb' order by 3

    优化 data_free 最多的表将为您节省最大的磁盘空间。

    但是有一些警告：

    • 在优化期间每个表都将被完全锁定。如果一个单独的表是 10 到 100 场演出，这不会很快
    • 您不应该期望 data_free 在优化后完全下降到 0。由于 innodb 在 16k 页中分配空间的方式，仍然会有一些未使用的空间。
    • 2

相关问题