innodb表相当大,存储20演出.
在Ubuntu 10.04上使用内核2.6.36和2.6.38-15 64位进行大量I / O加载的表碎片会导致随机系统崩溃吗？
我们正在研究随机系统崩溃的问题,这些崩溃是在“专用裸机”vps托管服务器上托管的大型innodb表上运行的.
MySQL版本是5.1.
以下是结果：“SELECT data_length,index_length,(data_length index_length)/ power(1024,3)GB FROM information_schema.tables WHERE ENGINE =’InnoDB’ORDER BY data_length index_length DESC LIMIT 10;”：
+-------------+--------------+-------------------+ | data_length | index_length | GB | +-------------+--------------+-------------------+ | 14758707200 | 17220501504 | 29.782958984375 | | 9456762880 | 16465543168 | 24.1420288085938 | | 16983785472 | 6954041344 | 22.2938385009766 | | 5625610240 | 2997813248 | 8.03118896484375 | | 3694133248 | 1730150400 | 5.0517578125 | | 2031091712 | 35209216 | 1.92439270019531 | | 1357905920 | 706740224 | 1.9228515625 | | 1107312640 | 320356352 | 1.32962036132812 | | 637534208 | 760889344 | 1.30238342285156 | | 488636416 | 260620288 | 0.697799682617188 | +-------------+--------------+-------------------+
打开文件= 300.
TIA
如果您想对InnoDB表进行碎片整理mydb.mytable,请运行以下命令：
ALTER TABLE mydb.mytable ENGINE=InnoDB;
根据hodd,它将执行以下操作：
CREATE TABLE mydb.mytablenew LIKE mydb.mytable; INSERT INTO mydb.mytablenew SELECT * mydb.mytable; ALTER TABLE mydb.mytable RENAME mydb.mytableold; ALTER TABLE mydb.mytablenew RENAME mydb.mytable; DROP TABLE mydb.mytableold;
如果您想要对所有InnoDB表进行大规模碎片整理,请运行以下命令：
echo "SET sql_LOG_BIN = 0;" > /root/DefragInnoDB.sql MysqL_USER=root MysqL_PASS=rootpassword MysqL_CONN="-u${MysqL_USER} -p${MysqL_PASS}" sql="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')" sql="${sql} FROM information_schema.tables WHERE engine='InnoDB'" MysqL ${MysqL_CONN} -ANe"${sql}" >> /root/DefragInnoDB.sql MysqL ${MysqL_CONN} -A < /root/DefragInnoDB.sql
您可能不需要经常对InnoDB进行碎片整理. Check out my post on the DBA StackExchange to determine if any one InnoDB table needs to be defragmented.
在旁注中,一些表看起来像索引消耗的空间多于数据.在这些表上运行碎片整理后,返回并查看每个表中的索引.尝试确定if there are any unused indexes并删除它们.
你有300作为innodb_open_files.你可以提高它,但不要疯狂设置它太高
请参阅innodb_open_files中的以下帖子
> http://www.mysqlperformanceblog.com/2009/11/18/how-innodb_open_files-affects-performance/
> http://www.mysqlperformanceblog.com/2009/11/20/rare-evil-mysql-bug/(因为innodb_open_files设置得太大)
我还建议你升级ro MysqL 5.5,你可以在raise innodb_read_io_threads and innodb_write_io_threads for better CPU utilization by the InnoDB Storage Engine.
