MySQL/チューニング

Last-modified: 2010-05-31 (月) 18:50:10 (59d)

MySQL関連のWikiへのリンク

現状分析 †

設定値を確認 †

mysql> show variables;

↑

統計情報を取る †

定量的な情報収集のススメ

mysql> show global status; (5.0.2以上の場合)
mysql> show status; (5.0.2未満の場合)

↑

スロー・クエリーのログをチェック †

↑

データサイズと搭載メモリの最適化 †

「データサイズ＜メモリ容量」を心掛ける
- ディスクとメモリの読み書き速度は100万倍の差がある
- Bツリーインデックスの探索、テーブルスキャン時に大量のシークが発生。これをディスク上でやるか、メモリ上でやるかでかなりの差が出る
- MySQLはOSのファイルキャッシュを頼りにする前提で作られている
- OSのファイルキャッシュ、MySQL専用のバッファのどちらでも良い
- データの全容量がメモリに収まらない場合は、インデックスだけでもメモリに収まるようにする

↑

テーブル設計、SQLの見直し †

MySQLのインデックスの制限に注意する
- WHERE句とORDER BY句に利用できるインデックスは１つまで
- 詳細はSELECTステートメントおよびその他のクエリの最適化を参照のこと
条件によく指定する項目で集計が必要なもの（件数など）は、テーブルにカラムを追加して、そのカラムを見るようにする
アプリを改修し、テーブル更新時に常に追加したカラムの値を更新するようにする

↑

チューニング †

↑

使用メモリの調整 †

MySQLサーバが消費するメモリ

↑

ファイルオープン数の調整 †

高負荷なときに以下のコマンドを実行

$ mysqladmin -u root -p extended-status | grep Open
Enter password: 
| Open_files                        | 515        |
| Open_streams                      | 0          |
| Open_tables                       | 256        |
| Opened_tables                     | 45281      |

Open_files, Open_tablesの数が多い場合は /etc/my.cnf の以下を調整。open_filesの値に比べてtable_cacheの値が大きすぎると「Error in accept: Too many open files」エラーが発生し、MySQLが機能不全に陥るので要注意！

[mysqld]
open_files=2048
table_cache=512

上記の数を増やすときは「ユーザごとのファイルディスクリプタ数」にも注意。必要なら増やす。

ファイルディスクリプタの最大数を増やす

【参考】

max_connectionsとthread_cacheのチューニングを行う

↑

I/Oの分散 †

データディレクトリの分散
- ディレクトリに違うパーティションをマウントする
- ディレクトリをシンボリックリンクにし、データは違うパーティションに置く

MyISAMファイルでは、データファイル(MYD)とインデックスファイル(MYI)を置くディレクトリを指定できる

↑

InnoDB †

全テーブルをInnoDB化
- InnoDBとMyISAMどっちが速いか?
  - InnoDB vs MyISAM vs Falcon benchmarks
- 徹底比較!! MySQLエンジン

MySQLリファレンスマニュアル 13.5.3.1. Per-Table テーブルスペースを利用する
- innodb_file_per_tableオプションを使用して、1 InnoDBテーブル＝1ファイルにし、マウントやシンボリックリンクで分散させる。
- InnoDB は、共有テーブルスペース（ibdata1 etc）に内部データディレクトリと取り消しログを置くので、いつもそれを必要とする。.ibd ファイルだけでは InnoDB は動作しない。
- innodb_file_per_tableなのにibdata1が利用される