TOP

MySQLのパフォーマンスチューニングした時のメモ

2016-03-14 17:23:46

クエリキャッシュ関連

MySQLのクエリキャッシュ設定でハイフンにハマったメモ

↑概ねこちらに記載

テーブルキャッシュ関連

副問い合わせを含む、複雑なクエリを実行している場合に利用するとよい。

設定項目 説明
max_heep_table_size 作成される一時テーブルの上限サイズ。これを超えるサイズの一時テーブルは作成されない。特に理由がなければ、tmp_table_sizeと同等の値でよい
tmp_table_size この値を超えた場合、一時テーブルはISAMとしてハードディスク上に作成されるが、超えない場合はメモリ上に作成される
// 設定値の確認
MariaDB [(none)]> show variables like '%table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
| tmp_table_size      | 16777216 |
+---------------------+----------+
2 rows in set (0.00 sec)

// 現状確認
MariaDB [(none)]> show global status like 'Created_tmp%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 6005  |     → ディスク上に作成された一時テーブル数
| Created_tmp_tables      | 8375  |     → メモリ上に作成された一時テーブル数
+-------------------------+-------+
2 rows in set (0.00 sec)

クエリのプロファイリングをする

MySQL 5.6からは、プロファイリングは非推奨になったらしい(かわりにperformance_schemaを使いましょうとの事)

なので以下のプロファイリング手順は、MySQLバージョン 5.6未満である事が前提です

// プロファイリングを有効にする
MariaDB [(none)]> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

// プロファイリングしたいSQLを実行
MariaDB [(none)]> select * from mysql.user;

// プロファイルの確認
MariaDB [(none)]> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000013 |
| Waiting for query cache lock   | 0.000002 |
| checking query cache for query | 0.000021 |
| checking permissions           | 0.000005 |
| Opening tables                 | 0.000010 |
| After opening tables           | 0.000003 |
| System lock                    | 0.000003 |
| Table lock                     | 0.000002 |
| After table lock               | 0.000004 |
| init                           | 0.000018 |
| optimizing                     | 0.000006 |
| statistics                     | 0.000010 |
| preparing                      | 0.000006 |
| executing                      | 0.000002 |
| Sending data                   | 0.000040 |
| end                            | 0.000003 |
| query end                      | 0.000002 |
| closing tables                 | 0.000004 |
| freeing items                  | 0.000003 |
| updating status                | 0.000004 |
| cleaning up                    | 0.000001 |
+--------------------------------+----------+
21 rows in set (0.00 sec)

MySQLTunerを使う

MySQLのチューニングを診断してくれるperlのアプリケーション(major/MySQLTuner-perl)

githubからcloneしてくるだけで使える

$ mysqltuner.pl --host [ホスト] --port [ポート] --user [ユーザ] --pass [パスワード] --forcemem [RAMのサイズをMB単位で指定]