Speedup MySQL inserts

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.

The original article was published on codeboost.com website.

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 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 the MySQL server for the changes take effect.

[mysqld]
myisam_repair_threads=10

3.    Finally, you can run the following query to alter the 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 rebuilt and you are ready to work with your faster database!

You are welcome to publish your benchmarks.

Comments from Ammnon Sutherland:

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.

Comment from Jan Steinman

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.

Comment from Egor Shevtsov

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

About the author

Yuli Stremovsky
Yuli StremovskyParanoid Security Guy
For the past 15 years I’ve been leading the evolution of startups and enterprises to achieve the highest level of security and compliance. Throughout my career I’ve been a Cyber Security expert and advanced solutions architect with many years of hands on experience both on offensive and defensive side. Knowledgeable at the highest level in application development, networking, data and databases, web applications, large scale Software as a Service solutions, cloud security and blockchain technologies.

I’ve been working with CISO’s of international enterprises, helping them set Information Security strategy, and overseeing the implementation of these recommendations. As part of these projects, I’ve been assisting companies to achieve compliance in GDPR, PCI, HIPAA and SOX.

Among my credits, I was a founder of a database security company GreenSQL/Hexatier which was acquired by Huawei and I’ve co-founded Kesem.io, Secure multi-signature Crypto wallet.

Specialties: Software and cloud architecture, Compliance (GDPR, HIPAA, PCI, SOX), blockchain technologies, software development, secure architectures, project management and low level research.