以下是关于InnoDB的一个更完整的答案.这是一个漫长的过程,但值得付出努力.
请记住,/var/lib/mysql/ibdata1
是InnoDB基础设施中最繁忙的文件.它通常包含六种类型的信息:
Pictorial Representation of ibdata1
了吗许多人创建多个ibdata
文件,希望获得更好的磁盘空间管理和性能,但这种 idea 是错误的.
OPTIMIZE TABLE
?不幸的是,对存储在共享表空间文件ibdata1
中的InnoDB表运行OPTIMIZE TABLE
会做两件事:
ibdata1
以内连续ibdata1
增长,因为连续数据页和索引页是appended到ibdata1
但是,您可以将表数据和表索引从ibdata1
中分离出来,并独立管理它们.
OPTIMIZE TABLE
with innodb_file_per_table
?假设你把innodb_file_per_table
和/etc/my.cnf (my.ini)
相加.你能在所有InnoDB表上运行OPTIMIZE TABLE
吗?
Good News:当您在启用innodb_file_per_table
的情况下运行OPTIMIZE TABLE
时,这将为该表生成一个.ibd
文件.例如,如果表mydb.mytable
的datadir为/var/lib/mysql
,它将产生以下结果:
/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd
.ibd
将包含该表的数据页和索引页.太棒了
Bad News:你所做的就是从living in ibdata
中提取mydb.mytable
的数据页和索引页.每个表(包括mydb.mytable
)的数据字典条目仍保留在数据字典中(参见Pictorial Representation of ibdata1).YOU CANNOT JUST SIMPLY DELETE 104 AT THIS POINT !!!请注意ibdata1
根本没有缩水.
要一次性收缩ibdata1
,您必须执行以下操作:
将所有数据库转储(例如,使用mysqldump
)到.sql
文本文件中(下面使用SQLData.sql
)
删除所有数据库(mysql
和information_schema
除外)CAVEAT:作为预防措施,请运行此脚本以确保您拥有所有用户授权:
mkdir /var/lib/mysql_grants
cp /var/lib/mysql/mysql/* /var/lib/mysql_grants/.
chown -R mysql:mysql /var/lib/mysql_grants
登录mysql并运行SET GLOBAL innodb_fast_shutdown = 0;
(这将完全刷新ib_logfile0
和ib_logfile1
中所有剩余的事务性更改)
关闭MySQL
将以下行添加到/etc/my.cnf
(或Windows上的my.ini
)
[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%.
另外:innodb_flush_method=O_DIRECT
在Windows上不可用)
删除ibdata*
和ib_logfile*
,也可以删除/var/lib/mysql
中除/var/lib/mysql/mysql
之外的所有文件夹.
启动MySQL(默认情况下,这将重建ibdata1
[10MB]以及ib_logfile0
和ib_logfile1
,每个1G).
进口SQLData.sql
现在,ibdata1
仍将增长,但只包含表元数据,因 for each InnoDB表都将存在于ibdata1
之外.ibdata1
将不再包含InnoDB数据和其他表的索引.
例如,假设您有一个名为mydb.mytable
的InnoDB表.如果查看/var/lib/mysql/mydb
,您将看到两个代表该表的文件:
mytable.frm
(存储引擎标题)mytable.ibd
(表数据 and Indexes)使用/etc/my.cnf
中的innodb_file_per_table
选项,可以运行OPTIMIZE TABLE mydb.mytable
,而文件/var/lib/mysql/mydb/mytable.ibd
实际上会收缩.
在MySQL DBA的职业生涯中,我已经做过很多次了.事实上,我第一次这样做时,我将50GBibdata1
文件缩小到只有500MB!
试试看.如果你对此有进一步的问题,尽管问.相信我;这将在短期和长期内奏效.
在第6步,如果mysql因为mysql
模式begin被删除而无法重新启动,请回顾第2步.您制作了mysql
模式的物理副本.您可以按如下方式还原它:
mkdir /var/lib/mysql/mysql
cp /var/lib/mysql_grants/* /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql/mysql
返回步骤6并继续
关于在第5步中设置innodb_log_file_size%到25%的innodb_buffer_pool_size,这是一条很老套的规则.
回到July 03, 2006
年,Percona写了一篇很好的文章why to choose a proper innodb_log_file_size.后来,在Nov 21, 2008
年,Percona又在how to calculate the proper size based on peak workload keeping one hour's worth of changes年发表了另一篇文章.
此后,我在DBA StackExchange上写过关于计算日志(log)大小的帖子,以及我在哪里引用了这两篇Percona文章.
Aug 27, 2012
: Proper tuning for 30GB InnoDB table on server with 48GB RAMJan 17, 2013
: MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?就我个人而言,对于初始设置,我仍然会遵循25%的规则.然后,随着生产时间的推移,工作量可以更准确地确定,在一个维护周期内,只需几分钟就可以确定you could resize the logs.