MySQLを高速に?! tmpdirをtmpfsに する方法

Pocket

MySQLのソート処理を高速化する

MySQLを高速に?! tmpdirをtmpfsに する方法
MySQLを高速に?!tmpdirをtmpfsにする方法

はい。

季節も秋めいてきて、涼しくなってきましたね。

勉強の秋、今日から何かの勉強を始められても良いかも知れませんね。

今日は皆さんが普段使われているMySQLの設定ファイルを少しチューニングして、MySQLのパフォーマンスアップを図るお話しをします。

具体的にはMySQLが一時的に使うファイルをラムディスクと言うメモリ上に作成した仮想的なディスク上に作成するようにし、処理速度を上げようというものです。

メモリ上に作成した仮想的なディスクですので、通常のディスクに比べ、非常に高速です。

それでは、行ってみましょう!

/etc/my.cnfのtmpdirとは

tmpdir とは、UNION ALL やテーブルJOINやネステッド・ループ・ジョインなどで、MySQLが使用するディスクソートであるマージソートを行う際に一時的に使用するフォルダです。

マージソートが早く完了すればするほど、SQLの戻りが早くなります。

そのため、通常tmpdirは速いディスクを割り当てます。

※ただし、既に列に索引があった場合、ソートは行われません。索引は列のソート済みのファイルだからです。

参考URL:
「MySQLでデータ領域をシステムと別diskにするならtmpdirも設定した方がいい – 酒日記 はてな支店」

以下上記URLより一部引用します。

テンポラリフォルダを作成し、スティッキービットを立てる。

# mkdir /data/tmp
# chmod 1777 /data/tmp

/etc/my.cnfにtmpdirを指定する。
通常tmpdirには高速なディスクを指定する。

 [mysqld]
tmpdir = /data/tmp

スティッキービットとは、以下のサイトを参照してください。

上記サイトから、一部引用します。

スティッキービットが設定されたディレクトリは、すべてのユーザがファイルおよびディレクトリを作成書き込みを行えるようになる。

しかし、作成したファイルやディレクトリを削除出来るのは所有者だけとなるというパーミッションだ。

このような設定を行い、/data/tmpに速いディスクを割り当てます。

このことで、MySQLのパフォーマンスをアップします。


tmpfsとは

では、tmpfsとは何の事なのでしょう?

一般的には、フォルダにマウントできるメモリ上の仮想的で一時的に使用するディスクという意味です。

以下にtmpfsの特徴を上げてみます。

  • 通常、tmpfsとは、指定した最大メモリまでは、動的にメモリが確保されていくメモリ上の仮想敵意なディスクのこと
  • 最初から、例えば2GBなら最初から2GB全てメモリが確保されるという事ではない。
  • 使用した分だけメモリが確保されるため、メモリ使用効率が良い

tmpfsについては、特に、以下のサイトが詳しいです。

「マイクラ鯖に持って来い!!、LinuxでRAMディスク!! | 純規の暇人趣味ブログ」

また、以下のサイトでは、tmpfsについて分かりやすい説明がされています。

「tmpfsってなんですか? – 今日も元気にlinux – mylinuxグループ」

/data/tmpにtmsfsをマウントする

それでは、実際にtmpfsでメモリ上のラムディスクを/data/tmpに割り当ててみましょう。

最大メモリサイズは、ここでは512MBとします。

$ mkdir -p /data/tmp
$ chmod 1755 /data/tmp
$ mount -t tmpfs -o size=512m tmpfs /data/tmp

/etc/my.cnfのtmpdirにtmpfsでマウントしたラムディスクを指定する

それでは、実際にMySQLの設定ファイル/etc/my.cnfにtmpdirを設定してみましょう。

cp /etc/my.cnf /etc/my.cnf.orig
vi /etc/my.cnf

[mysqld]
tmpdir = /data/tmp

後は、MySQLを再起動します。
CentOS6:

$ /etc/init.d/mysqld restart

CentOS7:

$ systemctl restart mysqld 

メモリが多く必要ですので、AWHさんのVPSを使用しています。

ラムディスクを大きな容量を取りたいとも思ったりしませんか?

ただ、メモリ容量に余裕のあるVPSやレンタルサーバーはなかなか亡いところです。

私の場合は、AWHというVPSを使っています。

  • CentOS7
  • メモリ8GB
  • SSD50GB

のプランで使っていますが、費用は999円/月です。

他にもKAGOYAさんなど、最近は価格がこなれたVPSやレンタルサーバーも出てきています。

皆さんも、色々と検討されると良いかもしれませんね。

なお、下図のAWHさんは、クーポンコード「aff0905」にて、全使用期間5%割引になります。宜しかったらどうぞご覧になってみてください。(画像をクリックするとAWHさんのサイトにジャンプします)

※クーポンコード「aff0905」は2019/12/31に失効します。

AWH VPS メモリ8GBで999円プランあり
AWH VPS メモリ8GBで999円プランあり

まとめ

皆さん、大変お疲れ様でした。

ここまで読んで頂いて、誠にありがとうございました。

本日の学びを以下にまとめます。

  • tmpdirは、MySQLがディスクソートを行う時のフォルダ。ディスクソートは非常に重い。
  • tmpfsとは、使用した分だけのみメモリを消費しるメモリ上の仮想的なディスク。一般的にはラムディスクと呼ばれます。
  • tmpdirにラムディスクを使用すると、MySQLのディスクソートが大変高速になる。また、ソート処理は1回きりの一時処理であるため、ずっと保存しなくて良くラムディスクに向いている。

この方法を採ることで、特に今までディスクソートを多用していたWebシステムの場合はパフォーマンスのアップが期待できます。

ぜひ、挑戦されて見てください。

それではー またー (^_^)/