MySQL遇到单硬盘IO性能不足时
linux下 可使用软链
对目录甚至单个文件解决跨硬盘问题。注意的是,官方并不推荐,官方文档 中说可能遇到不可预知的问题。
实践中,我试过
- 对目录进行建软链,生产环境下,过100G的数据,没问题。
- 对单个*.ibd文件建软链,正常的sql也不会有问题。但alter语句–重建表时,就直接把软链文件当文件复写,即跨硬盘的目标.ibd文件直接被无视。
官方的跨硬盘方案是:*.isl文件,5.6后才有
文档在此:http://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html
其实就是建一个同表名的 *.isl文件
,内容是ibd文件的完整路径。
正常情况下也是运行良好。
但在实际生产环境下,我遇到过:
InnoDB: Submit a detailed bug report to http://bugs.mysql.com
InnoDB: error in sec index entry update in
关于bug:InnoDB: error in sec index entry update in,官网 http://bugs.mysql.com 也能搜索到。 感觉还没有修复。
check table xx;语句会显示:
mysql>check table ***tbl;
+-------------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+-------+----------+----------+
| ***db.***tbl | check | error | Corrupt |
+-------------------------+-------+----------+----------+
执行重建表,直接崩了:
version '5.6.23-72.1-log'
...
InnoDB: Operating system error number 18 in a file operation.
InnoDB: Error number 18 means 'Invalid cross-device link'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
2015-10-15 11:51:59 7fbbe8bf0700 InnoDB: Assertion failure in thread 140445040445184 in file handler0alter.cc line 5004
InnoDB: Failing assertion: error == DB_SUCCESS
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
03:51:59 UTC - mysqld got signal 6 ;
....
-_=!
datadir目录下有 井号开头的.frm临时文件与.ibd临时文件,此后无法再启动mysql。