Performance hit from moving DB to another drive symlink?

Question

We're considering moving one database (there are 4 of them in total) with InnoDB tables, to a separate partition in a Ubuntu 14.04 VPS. This DB gets read/write intensive (500 writes per sec, about 1000 reads).

The primary consideration is that the other drive has much more space. We're using the MySQL guide to achieve this using a symbolic link.

Are there any performance hits from using a symlink?

score 3 · Accepted Answer · answered Jul 07 '14 at 11:45

No, the performance hit from a symlink is almost non-existent. Here's some strace to show it.

jaba@jaba-ubuntu:~$ echo foo >test
jaba@jaba-ubuntu:~$ ln -s test testlink
jaba@jaba-ubuntu:~$ ls -lah test testlink
-rw-rw-r-- 1 jaba jaba 4 heinä  7 14:39 test
lrwxrwxrwx 1 jaba jaba 4 heinä  7 14:39 testlink -> test
jaba@jaba-ubuntu:~$ strace -fFtT cat testlink
<some strace output omitted>
14:39:35 open("testlink", O_RDONLY|O_LARGEFILE) = 3 <0.000042>
14:39:35 fstat64(3, {st_mode=S_IFREG|0664, st_size=4, ...}) = 0 <0.000032>
14:39:35 fadvise64_64(3, 0, 0, POSIX_FADV_SEQUENTIAL) = 0 <0.000033>
14:39:35 read(3, "foo\n", 65536)        = 4 <0.000056>
14:39:35 write(1, "foo\n", 4foo
)           = 4 <0.000048>
14:39:35 read(3, "", 65536)             = 0 <0.000033>
14:39:35 close(3)                       = 0 <0.000036>
14:39:35 close(1)                       = 0 <0.000032>
14:39:35 close(2)                       = 0 <0.000031>

If you get to the point where symlinks can cause a serious bottleneck, then your I/O is VERY heavy. Heavy as in "no storage system in the world can take that kind of workload anyway".

score 1 · Answer 2 · edited Apr 13 '17 at 12:43

When it comes to performance, there is no real impact using the symlink in itself. The performance will only improve or degrade based on the disk the symlink points to. This is true if you move the entire datadir to the symlink's target.

If you want to improve performance, you mayt need a hybrid approach.

According to blog from a MySQL Engineer at Facebook, you should split the MySQL files so that one disk has all the MySQL files that receive sequential writes and another disk that has the MySQL files that receive random writes.

I have discussed this approach for MySQL and PostgreSQL before

IMHO I would keep the number of symlinks to a minimum for an all InnoDB database. Use them for linking to folders or very large logging tables.

Thanks for this. We're moving the whole database folder for one particular db to a different drive. This shouldn't pose a problem the way symlinking each innodb file does, correct? — kouton, Jul 08 '14 at 05:55

Performance hit from moving DB to another drive symlink?

2 Answers2