Ibdata的使用和建议？

Question

我有一个生产服务器

2011年11月5日，ibdata大小为100 G。在大约3个月的时间里，它增加到了200G.So，它刚刚翻了一番它的size.So，它是一个巨大的Data.Currently，lvm是251 g。

我有大约所有的innodb表，我不会在每个表中使用innodb。我在lvm.So上有我的idbata，我应该为将来的使用增加多少..？

或者任何其他最好的方法来处理这个场景。

Answer 1

您需要尽快停止使用InnoDB (关闭诺姆b_文件_每_表格 )和LVM快照。原因如下：

我有一个以MySQL为数据库的监控系统。它也把innodb_file_per_table关掉了。在6个月的时间内，它增长到1.3TB。

我雇主的公司不可能有这个监控系统的停机时间。

我试图为这个怪物数据库创建一个奴隶。我将/var/lib/mysql的rsync运行到从服务器。它的第一次通过花费了42个小时(这不是一个错误，1天18小时)。第二次检查耗时84小时(3天，12小时)，仅完成15% (220 15拷贝)。正如你所看到的，我放弃了第二个rsync。

问题的根源是ibdata1为1.3TB。

当ibdata1关闭时，innodb_file_per_table内部是什么？

• 表数据页
• 表索引页
• 表元数据(表空间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的大小进行比较。您会发现，可能有一些差异，30 of的更少。在我的监控系统中，我运行的不是针对ibdata1的查询，而是只有27 in的表元数据和新的MVCC信息。此文件将始终保持稳定增长。

下面是您可以做的事情:将所有InnoDB转换为使用innodb_file_per_table。我在StackOverflow上写了一篇关于如何去做的文章。不仅可以将easch表存在于单独的文件中，还可以将ibdata1增长率保持在最低水平。

更新2012-02-22 13:00 EST

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

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend

Answer 2

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

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

通过运行show状态，您可以看到哪些表将从中受益。交替运行

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

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

然而，有一些警告：

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