解決 mysql innodb 效能問題方法之一

可以用
Using Per-Table Tablespaces : http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces.html

Store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because in effect each table has its own tablespace.

Using multiple tablespaces can be beneficial to users who want to move specific tables to separate physical disks or who wish to restore backups of single tables quickly without interrupting the use of other InnoDB tables.

[mysqld]
innodb_file_per_table

那在把不同的 innodb file 放到不同 disk partition 去, 達到分散實體 IO 的目標, 不過, mysql 對於 path 部份要特別處理, 避免 drop table 或 alter table 時, 它會發生無法預期的結果, 翻了 web , 看到有人這麼作:

改 mysys/mf_format.c

if (!lstat(to,&stat_buff))  /* Check if it's a symbolic link */
    if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff))
成

if (realpath(to,buff))

試看看吧, 我沒改過!