MySQLチューニング

By | 2013年3月18日

nigaraです。MySQLの基本的なチューニングをいくつか。

・EXPLAIN

SQL分の頭にEXPALINを付けるとクエリの流れが見えます。
例えばこんな感じ

この出力で見る場所はrowsとExtra。rowsはそのクエリで

操作に関わったレコードですので、少なければ少ないほど
速くなります(最小は1)。またI/O負荷の低減にもなります。
逆にこの値が大きいとCPU負荷やI/O負荷が高くなるので、
rowsの値が増えれば増えるほど重くなって行きます。
rowsが多い場合は基本的な対処方として、まずINDEXの
見直しとなります。例えばMySQLは複数カラムINDEXと単数
カラムINDEXは別物ですのでwhereの条件が複数あると
INDEXが効かない場合があるので、EXPLAINのrowsに
よりそのINDEXの効力を確認できます。

Extraについては「Using filesort」「Using temporary」が
表示された場合、裏側でメモリ上に一時TABLEが作成されて
遅くなりますので、この場合はSQL自体の見直しが必要になります。
(GROUP BYとORDER BYのカラムが違う場合によく起きます)

ただRowsにせよExtraにせよ、仕様の都合で対処出来ない場合が
あると思います。そのた対処法はまた別の機会にでも。

 

・my.cnfの設定

/usr/share/mysql以下に下記の5つのcnfファイルがあるので、
サーバーや用途に見合ったcnfを/etc/my.cnfにリネームして
設置。設置後は再起動で反映。

my-huge.cnf
my-innodb-heavy-4G.cnf
my-large.cnf
my-medium.cnf
my-small.cnf

最近のサーバーだとメモリ4Gが標準ですので、my-huga.cnfか
my-innodb-heavy-4G.cnfを使用することになります。

さらにチューニングを行う場合はmy.cnfの各設定値を変えるわけ
ですが、とりあえずinnodb_buffer_pool_size(table_open_cache)を
上げるのが効果的です。innodb_buffer_pool_sizeですが、
グローバルバッファですので物理メモリの半分以上を設定しても
大丈夫です。そしてinnodb_buffer_pool_sizeを上げた際には、
table_open_cacheもセットで上げてください。これはinnodb_log_fileが
上限に達するとinnodb_buffer_poolの更新部分をディスクに書き込むので、
こちらも併せて値を増やしてください。

なおinnodb_log_file_sizeを変更した場合はib_logfile*を削除して
ください。起動しないか書き込めなくなります。

・遅延更新
更新のSQLにLOW_PRIORITYを設定する。これによりロックを回避し待ちを
少なくします。ただし即時性がないので、更新内容をリアルタイムで
参照しているTABLEには使えません。

 

・レプリケーションの導入

レプリケーションについてはいくつかメリットがありまして、
チューニング的な観点で見ると
「参照系と更新系の分離」「slaveを増やすことにより参照系の分散」
があります。
例えばこんな感じで設置
web – master --slave
∟slave
∟slave

ただ最近はハードウェアの進化もあり、チューニング的な意味での
レプリケーションは薄れつつあります。バックアップ目的での
レプリケーションを構築し、負荷対策はハードでごり押ししてる
システムもよく見かけます。

次回は重いMySQLをハードウェア(フォーマット)で軽くする
方法でも書く予定。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です