Saturday, April 6, 2013

Speedup mysql index using ssd disk

You probably ask yourself sometimes how you can boost MySQL indexes when you are working with BIG databases/tables.

Really the solution is here for a long time already. You can use SSD disks (also known as flash disks). These disks are not that big that traditional SATA hard drives but they provide a superior performance. If your database is rather big to be placed on SSD disk you can still place your database indexes onto it. So, your data will be saved on an old disk and the index will be placed in faster SSD disk.

If can be easily done.

1.    You need to create a directory on your SSD disk that will be used for mysql indexes. In addition you need to give to mysql service write permission to that directory.


mkdir /ssd/mysql-indexes
mkdir /ssd/mysql-indexes/my-big-db-name
chmod 700  /ssd/mysql-indexes
chown –R mysql:mysql /ssd/mysql-indexes


2.    OPTIONAL STEP:

Here is another tip shared with me by another MySQL expert. Add the following line to [mysqld] configuration section in mysql. It will speed up the index rebuild operation in a number of times if you work with MyISAM tables. You need to restart mysql server for the changes take effect.

[mysqld]
myisam_repair_threads=10


3.    Finally you can run the following query to alter location of the index files.

ALTER TABLE my-table INDEX DIRECTORY= '/ssd/mysql-indexes/my-big-db-name';


After that you will have to wait some time till MySQL indexes will be rebuild and you are ready to work with your faster database!

You are welcome to publish your benchmarks.

Best regards,

Yuli

5 comments:

  1. Oddly, I was reminded of this just last night and planning to put it into practice this next week. You can also put heavily used or massive range-scan prone tables (including innodb tables) on disks by specifying the datadir as part of the create table or alter table syntax.

    ReplyDelete
    Replies
    1. This only works for innodb in mysql 5.6. previously you have to hack this with symlinks, which is unreliable.

      Delete
  2. Rather than rebuild the indices -- which could take some time -- couldn't you simply shut down the database, copy them to the SSD, then symlink them back to the data directory?

    I guess that would only work with MyISAM, since InnoDB doesn't have separate index files.

    ReplyDelete
  3. Very nice.

    I do try to use MyISAM engine as least as possible specifically with Big tables as it's not crash safe and takes long to repair.
    But I learned some nice tricks today.

    Many thanks.

    mkdir -p /ssd/mysql-indexes/my-big-db-name # in one go

    ReplyDelete