Question

I have a myisam only dedicated 32 GB RAM mysql server that is running on default configuration. I want to change the engine type to InnoDB of one table in order to avoid table locks. It has 50 million records and size on disk is around 15 GB. I am using mysql version 5.5 I guess I will need to add the following options and restart mysql.

innodb_buffer_pool_size=1G
innodb_log_file_size=100M
innodb_file_per_table=1

在改变发动机类型时,还有哪些其他必要?

Answer 1

你们实际上正在指挥改变每个桌子。

表格的顺序越快:

ALTER TABLE tablename ORDER BY primary_key_column;

其后,指挥机关:

ALTER TABLE tablename ENGINE = INNODB;

如果该表确实庞大,则可能需要时间,而且它将使用许多贵邮联。

Answer 2

首先检查贵国数据库是否支持非行发动机(我没有支持);

SHOW ENGINESG

如果是的话,已经拖欠了。检查:

SHOW VARIABLES LIKE  %innodb%

and try to understand them and alter the to your specific needs. Even if you use the default params, you are now fine to play arround with InnoDB tables.

如果你只想建立印非行表格,你就可以改变您的缺省储存发动机,或者把本届会议改成<代码>。储存:engine=INNODB; 或在座标中使用default-storage-engine。

顺便说一句,将表格转换到非行的最快方式并不是上述方式。只需插入数据即可将表格转换成非行:

CREATE TABE new AS SELECT * FROM old WHERE 1<>1;
ALTER TABLE new ENGINE = INNODB;
INSERT INTO new SELECT * FROM old;

当然,你必须增加人工需要的指数,但与<代码”相比,通常需要时间(和痛苦)。缩略语

友情链接