サーバ/インフラを支える技術4 MySQLのチューニングのツボ

前回
サーバ/インフラを支える技術4 Apacheのチューニング - by shigemk2

パフォーマンスでDBサーバに求められるものは、
「データをいかに速く出し入れするか」の一点に尽きる(かなり乱暴だが)

より短い時間でデータを出し入れするには、

• サーバサイド
• サーバサイド以外
• 周辺システム

の3点がチューニングの切り口になる。

1. サーバサイドのチューニング

• mysqldのパラメータチューニング
• ディスクI/O関連のkernelパラメータの調整
• 適切なファイルシステムの選択とマウントオプションなどの調整

また、パーティショニング(テーブルごとでのDBサーバの振り分け)も重要になる。
ただし、パーティショニングを行うと、分割されたDB群のうちから
適切なものを選ぶ処理が必要になったり、SQLレベルでのテーブル結合が
できなくなるといった、アプリケーション側の負担が増えるデメリットもある

2. サーバサイド以外

• テーブル設計(適切なインデックスの作成、意図的な非正規化など)
• SQLの最適化(テーブル結合の順序、方法を調整するなど)

特にSQLの最適化は、チューニングの効果が劇的に出やすい

3. 周辺システム
そもそもDBサーバに問い合わせる必要があるのかを考慮する

memcachedなどのキャッシュサーバに問い合わせをさせる、など。
RDBMSのチューニングは、SQLやサーバパラメータに目がいきがちだが、
RDBMSを「データを入出力するための一連の系」と考えると、そこに
キャッシュサーバという構成要素を加えるのも一興かもしれない。

ただし、パーティショニングやキャッシュサーバの導入を行う前に、
SQLの見直しやパラメータのチューニングを行う必要がある。
また、原因は1つだけではないので、
チューニングは上記3つを横断的に観察、検討する必要がある。

MySQLには、性能向上のためにデータを一時的に保存しておく
2種類のバッファが存在する

• グローバルバッファ(mysqldで内部的に1つだけ確保されているバッファ)
• スレッドバッファ(スレッド(コネクション)ごとに確保されている)

ただし、スレッドバッファに多くのメモリを割り当てると、コネクションが
増えたとたんにメモリ不足になるので注意する。

とはいえバッファへの割り当ては大きければ大きいほどパフォーマンスは上が
るのだが物理メモリ以上の大きさを割り当てると、スワップが発生して
逆にパフォーマンスが落ちる。

MyISAMテーブルはMySQLレベルのパラメータチューニングより、
MyISAMのデータファイルがOSのディスクキャッシュに載るように
調整したほうが性能が向上する場合もある。

1MB < innodb_log_file_size < MAX_innodb_log_file_size < 4GB

mysqldはinnodb_log_fileがいっぱいになると、
メモリ上のinnodb_buffer_poolの中でだけ更新されている部分を
ディスク上のInnoDBのデータファイルに書き出すような動作をする。
そのため、innodb_buffer_pool_sizeを大きくしたら、この
innodb_log_file_sizeも大きくしないと、innodb_log_file_sizeがすぐに
あふれてしまうので、頻繁にInnoDBデータファイルに書き出しを行う
羽目になり、パフォーマンスが落ちてしまう。

また、メモリ関連のチェックツールとして、
著者自作のmymemcheckがある。

• 最低限必要な物理メモリの大きさ
• IA-32のLinuxでのヒープサイズの制限
• innodb_log_file_sizeの最大サイズ

の上記3つをチェックする。

次回
サーバ/インフラを支える技術5 サービスの稼動監視 1 稼動監視の種類 - by shigemk2

[24時間365日] サーバ/インフラを支える技術 ?スケーラビリティ、ハイパフォーマンス、省力運用 (WEB+DB PRESS plusシリーズ)