- MySQL関連のWikiへのリンク
- MySQL
- MySQL/5.5
- MySQL/InnoDB
- MySQL/エラー
- MySQL/クラスタリング
- MySQL/サーバ移動
- MySQL/チューニング
- MySQL/バージョン
- MySQL/バックアップ
- MySQL/プロファイリング
- MySQL/ベンチマーク
- MySQL/レプリケーション
- MySQL/ロック
- MySQL/制限
クエリ分析 †
サーバ分析 †
設定値を確認 †
mysql> show variables;
統計情報を取る †
- テーブルのレコード数、データサイズ、インデックスサイズなどを調査
select table_name, engine,
table_rows as tbl_rows,
avg_row_length as rlen,
floor((data_length+index_length)/1024/1024) as allMB,
floor((data_length)/1024/1024) as dMB,
floor((index_length)/1024/1024) as iMB
from information_schema.tables
where table_schema=database()
order by (data_length+index_length) desc;
スロー・クエリーのログをチェック †
接続状況の確認 †
$ mysqladmin -p extended-status | grep -E 'Max|Threads'
Enter password:
| Max_used_connections | 51 |
| Threads_cached | 4 |
| Threads_connected | 21 |
| Threads_created | 18015 |
| Threads_running | 11 |
データサイズと搭載メモリの最適化 †
- 「データサイズ<メモリ容量」を心掛ける
- ディスクとメモリの読み書き速度は100万倍の差がある
- Bツリーインデックスの探索、テーブルスキャン時に大量のシークが発生。これをディスク上でやるか、メモリ上でやるかでかなりの差が出る
- MySQLはOSのファイルキャッシュを頼りにする前提で作られている
- OSのファイルキャッシュ、MySQL専用のバッファのどちらでも良い
- データの全容量がメモリに収まらない場合は、インデックスだけでもメモリに収まるようにする
テーブル設計、SQLの見直し †
- 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
上記の数を増やすときは「ユーザごとのファイルディスクリプタ数」にも注意。必要なら増やす。
【参考】
I/Oの分散 †
- データディレクトリの分散
- ディレクトリに違うパーティションをマウントする
- ディレクトリをシンボリックリンクにし、データは違うパーティションに置く
- MyISAMファイルでは、データファイル(MYD)とインデックスファイル(MYI)を置くディレクトリを指定できる
InnoDB特化 †
my.cnf †
MyISAM/InnoDB共通 †
- query_cache_size
- クエリの実行結果のキャッシュ。同じQueryがキャッシュにある場合はSQLを実行せずにキャッシュの内容を返すので、サイズは大きい方が良い。
- 該当するテーブルの内容が変更された場合には、キャッシュもなくなるので、キャッシュによる不具合が生じることはない
MyISAM関連 †
- key_buffer_size
- MyISAMのキーをキャッシュするバッファのサイズ
- MyISAMのみのMySQL専用サーバならRAMの1/4程度割り当てると良い
InnoDB関連 †
- innodb_file_per_table
- テーブル単位に .ibd ファイルを作成
- 必ず!!指定すること
- innodb_buffer_pool_size
- InnoDB テーブルのデータとインデックスを保存するためのバッファの大きさ
- MySQLかつInnoDB専用サーバならRAMの 70-80% 位まで上げても良いとか言われているが、実際にやると落ちることがあるので、50%に留めておく
- innodb_log_file_size
- データの書き込み、得に大きなサイズのデータの書き込み性能に影響する
- 大きなサイズほどデータのリカバリ時間を要する
- サイズを変更する場合、正常停止後、作成済みのログファイル(デフォルトは /var/lib/mysql/ib_logfile**)を削除し、mysqlサーバを起動しなおす必要あり
- innodb_flush_method
データファイルの調整 †
チューニングの手順 †
テーブル圧縮 †
myisampack †
- MyISAMのMYDファイルを圧縮
- 元ファイルの50〜70%
ツール †
複数実行 †