MySQL/チューニング

Last-modified: 2011-08-26 (金) 18:58:31 (154d)

MySQL関連のWikiへのリンク

MySQL 初めてのチューニング
mysqlを高速化したいときのチューニング方法
MySQL 5.1 リファレンスマニュアル 6.最適化
さらにMySQLを高速化する7つの方法
Sundry MySQL Scripts and Docs
- MySQL Performance Tuning Primer Script ... my.cnfやInformation Schemaの情報から設定のアドバイスをしてくれるスクリプト

クエリ分析 †

↑

サーバ分析 †

MySQLのチューニング？

↑

設定値を確認 †

mysql> show variables;

↑

統計情報を取る †

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

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

テーブルのレコード数、データサイズ、インデックスサイズなどを調査

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;

INFORMATION_SCHEMA データベース

↑

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

↑

接続状況の確認 †

$ 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のインデックスの制限に注意する
- 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化

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

MySQLリファレンスマニュアル 7.5.13.3. InnoDB テーブルのデフラグメント化
- 拡張され続ける InnoDB のデータファイルのサイズを小さくする方法

↑

my.cnf †

↑

MyISAM/InnoDB共通 †

table_open_cache
- MySQL でのテーブルのオープンとクローズの方法
- 接続数が200の場合、最低 200 × N のテーブルキャッシュサイズが必要

max_connections
- サーバが許可する最大同時接続数

query_cache_size (Global)
- クエリの実行結果のキャッシュ。同じQueryがキャッシュにある場合はSQLを実行せずにキャッシュの内容を返すので、サイズは大きい方が良い。
- 該当するテーブルの内容が変更された場合には、キャッシュもなくなるので、キャッシュによる不具合が生じることはない

sort_buffer_size (Thread)
- ソートを実行する必要があるスレッドがこのサイズのバッファを割り当てる

↑

MyISAM関連 †

key_buffer_size (Global)
- MyISAMのキーをキャッシュするバッファのサイズ
- MyISAMのみのMySQL専用サーバならRAMの1/4程度割り当てると良い

myisam_sort_buffer_size (Thread)
- MyISAM用
- インデックス作成時（REPAIR TABLE, CREATE INDEX, ALTER TABLE）に使用するバッファ
- 参考：myisam_sort_buffer_sizeを増やしておくと良いだろう

↑

InnoDB関連 †

innodb_file_per_table
- テーブル単位に .ibd ファイルを作成
- 必ず！！指定すること

innodb_buffer_pool_size (Global)
- InnoDB テーブルのデータとインデックスを保存するためのバッファの大きさ
- 実際にはここで割り当てた値の5～10%ぐらい多めにメモリを使うので注意が必要
- MySQLかつInnoDB専用サーバならRAMの 70-80% 位まで上げても良いとか言われているが、実際にやると落ちることがあるので、50%に留めておいた方が無難

innodb_log_file_size
- データの書き込み、得に大きなサイズのデータの書き込み性能に影響する
- 大きなサイズほどデータのリカバリ時間を要する
- サイズを変更する場合、正常停止後、作成済みのログファイル(デフォルトは /var/lib/mysql/ib_logfile**)を削除し、mysqlサーバを起動しなおす必要あり

innodb_flush_method
- MySQL innodb_flush_method = O_DIRECTの検討
- デフォルト（fdatasync）で良いので未指定でOK

↑

データファイルの調整 †

↑

症状と対策 †

Copying to tmp table が頻発
- テーブルがMyISAMの場合
  - myisam_sort_buffer_size (Thread) を大きくする
  - key_buffer_size (Global) を大きくする（1/6～1/4）
- テーブルがInnoDBの場合
  - sort_buffer_size (Thread) を大きくする

↑

チューニングの手順 †

限界までMySQLを使い尽くす!!

はじめてのMySQLチューニング
- 第５回：key_buffer_sizeの違いによるパフォーマンス比較
- 第６回：query_cache_sizeの違いによるパフォーマンス比較

MySQLのパラメータチューニング

MySQLの内部構造

MySQL Performance Tuning Key
- [MySQL Performance Tuning Primer:http://day32.com/MySQL/]]

↑

テーブル圧縮 †

↑

myisampack †

MyISAMのMYDファイルを圧縮
元ファイルの50～70%

テーブルの最適化

↑

ツール †

↑

複数実行 †

同じマシン上での複数 MySQL サーバの実行

MySQL/チューニング

クエリ分析 †

サーバ分析 †

設定値を確認 †

統計情報を取る †

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

接続状況の確認 †

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

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

チューニング †

使用メモリの調整 †

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

I/Oの分散 †

InnoDB特化 †

my.cnf †

MyISAM/InnoDB共通 †

MyISAM関連 †

InnoDB関連 †

データファイルの調整 †

症状と対策 †

チューニングの手順 †

テーブル圧縮 †

myisampack †

ツール †

Maatkit †

mytop †

MySQLTuner-perl †

MySQL Activity Report †

mysqlreport †

複数実行 †

編集操作

最新の20件

MySQL/チューニング

クエリ分析 †

サーバ分析 †

設定値を確認 †

統計情報を取る †

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

接続状況の確認 †

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

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

チューニング †

使用メモリの調整 †

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

I/Oの分散 †

InnoDB特化 †

my.cnf †

MyISAM/InnoDB共通 †

MyISAM関連 †

InnoDB関連 †

データファイルの調整 †

症状と対策 †

チューニングの手順 †

テーブル圧縮 †

myisampack †

ツール †

Maatkit †

mytop †

MySQLTuner-perl †

MySQL Activity Report †

mysqlreport †

複数実行 †

検索

編集操作

最新の20件