初めてのシステムと日記

システムも日記も初めてです。

「お金をかけないDBチューニング」という題名で社内LTした

MySQL

今年1年を振り返る意味で「お金をかけないDBチューニング」という題名でLTしました。

詳しくはslideshareにアップしたのでそちらをご覧ください。


DBチューニングで真っ先に思い浮かぶのがサーバー増強等のハードウェアの対応ですが、

僕が関わったプロジェクトでは、

  • 諸事情であまりハードウェアへのコストがかけられない(Orz)
  • 途中から引き継いだが過去の負債が多くアプリ側のDB、SQL構成がボトルネックになっている(OrzOrz)
  • ハードウェアでの対応は根本の重たい原因が残ったままなので最初の手段としては相応しくない

ということもあり、ハードウェア対応の前にアプリ側の対応をする必要がありました。。


という状況で僕が対応した、また対応したい事は、

  • my.cnfのチューニング(対応したい)
  • SQLのチューニング(対応した)

の2つです。この2つのチューニングであればコストがかからず、

また根本のDBが重たい原因を解消する事が出来ます。


my.cnfのチューニング

これはまだ対応しておらず、僕が調べて試したい事になりますが、基本的に、

my.cnfのチューニング = メモリ割当の最適化

となります。


key_buffer_size、sort_buffer_size、record_buffer_size、

それぞれをサーバのメモリと他のミドルウェアを考慮して割り当てます。

最適なメモリ割当に関しては調べた限り以下の感じでした。

key_buffer_size + (sort_buffer_size + record_buffer_size) * max_connections
実メモリサイズ + スワップメモリサイズ


それ以外のもチューニング出来る項目は以下のようなものがあります。

  • max_allowed_packet
  • table_open_cache
  • query_cache_size
  • innodb_buffer_pool_size
  • innodb_log_buffer_size

この辺もチューニングする事でより速くなると思われます。
(今回は時間の都合と、簡単に出来るチューニングということで省きました。)


SQLのチューニング

今年一番の収穫だったのがmysqldumpslowコマンド。

MySQL5.1からあるようでしたが最近鍵本を見て初めて知りました。

MySQLのスロークエリログを解析してくれます。実行は以下の通りです。

$ mysqldumpslow [option] [log_file]

## よく使うオプションは-s(並び替え)
-s c  → 総クエリ数の多い順
-s l   → 総ロックタイムの長い順
-s r  → 総行数の多い順
-s t  → 総実行時間の長い順
-s a(l | r | c) → 各平均の長い順

実際に実行した結果は以下の通りです。

## 文字列 → S、 数字 → N と置換してくれる
## Count:総クエリ数 Time=平均実行時間(総実行時間)
## Lock=平均ロックタイム(総ロックタイム) Rows=平均行数(総行数)

$ mysqldumpslow -s c slow_query.log | less

Count: 49  Time=1.49s (73s)  Lock=0.00s (0s)  Rows=1.0 (49), user[user]@2hosts
  SELECT * FROM tbl1 WHERE  name = 'S'  ORDER BY id LIMIT N

Count: 36  Time=1.47s (52s)  Lock=0.00s (0s)  Rows=1.0 (36), user[user]@2hosts
  SELECT * FROM tbl2 WHERE id = 'S' AND deleted = 'S' ORDER BY id LIMIT N
 .
 .
 .

WHERE句やLIMIT句などの値をSやNに置換してくれるので、

条件式の値が異なるSQLを丸めてくれるので生ログよりとても見やすいです。

秒数はさほどだけど頻繁にスロークエリにのるSQLや、

実行時間がやたら多いSQLなどを簡単に確認する事が出来ます。


実際のSQLチューニングに関しては、

既存DBに手を加えるのはかなりリスクだったので、

テーブル変更はせず以下のような形でSQLのみをチューニングしました。

  • 最低限のレコードを取得するようにする
  • なるべくJOINの数を減らす
  • テーブルをまたぐORDER BYはなるべく避ける

基本的なことですが、

データ件数が多い場合(10万件以上とか)は、上の項目1つやるだけで数sec落とせたりします。


もしこれでもまだDBサーバが重たいようであれば、

そこでサーバー増強や台数を増やす等の対応となります。

重たい原因がない状態でより最適化が出来るはずです。