[MySQL]キャッシュサイズを変更して高速化チューニングにトライ

お客さんのシステムをAccess+MySQLで運用しているんだけれども

結構データの容量が大きくなってきてメインのテーブルが16万レコード、BLOBナシで40MBほど来ちゃってた。

お客さんからも「遅いんだよねぇ…」ってクレームが来るほどだったから高速化チューニングにトライしてみる。

以下の3つの方法で、Access側で16万レコードを取得し、requeryをかけてカーソルの砂時計が元に戻るまで10秒弱かかっていたのが、1.5秒ほどで完了するようになった。

最近MySQL重いな…という方にはおすすめ。

1.キャッシュサイズを変更

mysqlのmy.iniを開いて、以下の項目を変更していく
(※数値は私の環境で効果が出たものなので、環境に応じて数値は変更してみてね)

innodb_buffer_pool_size

512M
いちばん大事なとこ。思いっきり大きく設定してやろう。
多分ここだけでもかなり効果はあると思う。

innodb_additional_mem_pool_size

20MB

innodb_log_buffer_size

16MB

innodb_log_file_size

128MB
ここも大事。innodb_buffer_pool_sizeの4分の1くらいに設定すればいいかと思ってる。
ここも効果があった箇所。大きめに設定するのがおすすめ。
ちなみにここを変更するなら、この後の3番の項目もやっておこう。

終わったらMySQLを再起動。

参考にさせていただいております!
参照:DSAS開発者の部屋 様

2.クエリキャッシュを有効にする

クエリをキャッシュさせることで、同じSQLや似たようなSQLを高速化させる。

1の手順で編集したmy.iniを更に編集していく。

27行目あたりの[mysqld]から、163行目あたりの[mysqldump]までの間に以下を挿入

[crayon-5df16e2c97273450768587/]

「# 今日の日付 ADD 編集者」とかコメントをつけておくといいかも。

終わったらMySQLを再起動。

参考にさせていただきました!
参考:fururemix 様

3.キャッシュサイズを変更

これは1の手順の「innodb_log_file_size」に付随してる。

ログファイルのサイズ設定を変更したので、既に作成済みのログファイルを削除して、MySQL側に再作成させてやる必要があるってこと。

MySQLのdataフォルダ(XamppならC:\xampp\mysql\data)を開く。

“ib_logfile0” と “ib_logfile1” を削除してやります。
自分は念のため「MySQLログのバックアップ」というフォルダを作って、別の場所に保存した。

dataフォルダの直下にバックアップフォルダを置くとMySQL側が誤認識して「MySQLログのバックアップ」というデータベースが存在すると勘違いするので、あくまでも別の場所に保存するように気をつけよう。

終わったらMySQLを再起動。

参考にさせていただきました!
参考:furemix 様

まとめ

Access側で16万レコードを取得し、requeryをかけてカーソルの砂時計が元に戻るまで10秒弱かかっていたのが、1.5秒ほどで完了するようになった。

キャッシュクエリを有効にしたので、これから処理を重ねていくことでさらなる高速化が見込めると思う。

ぜひお試しあれ。