MySQL, RDB, チューニング, Linux

MySQL SHOWによる解析とチューニング

MySQL

MySQLのチューニング調査依頼で時短で終わるように

 

バージョン

  • CentOS7
  • MySQL5.6

 

DB, テーブルのDBエンジンの確認

 

データベースの確認

 

デフォルトのエンジンの確認

 

 

DBエンジンの確認 例) exampledbの各テーブルのエンジンの確認

InnoDBなのか、MyISAMなのか・・・それ以外なのかによってチューニングパラメータは異なる。1つのサーバに異なるエンジンが混在していないのが望ましい。

 

グローバルパラメータの確認

実際に有効になっているパラメータを確認する

 

グローバルパラメータの確認

 

 

ステータスの確認

 

パフォーマンススキーマ

 

 

オープンになっているテーブルの状況が見れる

 

プロシージャの確認

 

権限の確認

 

 

 

エラー状況を確認

 

WARNINGクエリ確認

 

ERRORクエリの確認

 

障害発生時にログインして、MySQLサービスが再起動したかを確認する

 

クエリの実行状況が見れる

 

デッドロック ロック待ちの確認

これはOK

 

こうなっていたらまずい

 

ロック時にSHOW ENGINE INNODB STATUSコマンドでロック原因を特定できるようにする

 

 

 

デッドロック発生時にタイムアウトを設定する

 

 

クエリキャッシュ

注意

  • INSERTが多いシステムでは逆にパフォーマンスが悪くなり向かない
    クエリキャッシュを利用する為に広範囲でロックを取るのでロック競合する
  • SELECT主体の参照が多いサイトでは向いている
  • MySQL8.0では廃止された
    設定にクエリキャッシュ関連のディレクティブがあると起動しない

 

クエリキャッシュを削除する場合

 

 

クエリキャッシュヒット状況の確認

 

  • Qcache_free_blocks
    キャッシュに割り当てが出来るフリーのブロック数
    1SQLキャッシュするのに1ブロック最低消費される
  • Qcache_free_memory
    クエリキャッシュに割り当てができるフリーのメモリ
  • Qcache_hits
    クエリのヒット数
  • Qcache_lowmem_prunes
    メモリがないので削除されたクエリキャッシュ
    すべてのキャッシュがメモリに載っていればここは0になる
  • Qcache_not_cached
    キャッシュに入れられなかったクエリの数
  • Qcache_queries_in_cache
    キャッシュ内のクエリの数
  • Qcache_total_blocks
    ブロック領域の合計

 

クエリキャッシュ?

  • クエリキャッシュはSELECTクエリの結果をキャッシュしたものです。
  • まったく同じSELECTクエリが来た場合にキャッシュを返します。

 

クエリキャッシュヒット率の確認

キャッシュがどれだけヒットしているか

 

query_cache_limitの確認

2MBに設定済。

2MB~4MBにする

 

query_cache_sizeの確認

128MBに設定済。1年後のDBサイズの20%程度が目安とされる。

128MB~256MB程度まで。クエリキャッシュが多すぎてもキャッシュ保守にリソースが回されて逆に遅くなる。大きくし過ぎてもいけない。

 

 

 

 

 

スロークエリ対応

 

スロークエリ有効化 1秒以上かかるクエリを出力する

 

遅い順にソートする

 

 

RDS MySQL, PostgreSQL スロークエリ関連ログの出力

EXPLAIN EXTENDEDによるクエリの解析

 

 

typeに注目する

 

良いtype

  • const
    PRIMARY KEYまたはUNIQUEインデックスによる検索で最速
  • eq_ref
    JOINにてPRIARY KEYまたはUNIQUE KEYが利用される時
  • ref
    ユニークでないインデックスによる検索。WHERE name = valueのような時
  • range
    ユニークを用いた範囲検索

あまり良くないtype

  • index
    フルインデックススキャン。インデックス全体を見るので遅い
  • ALL
    フルテーブルスキャン。インデックスが利用されていない!

スロークエリでなければ問題ない。

 

1000 × 200行が対象になっている。減らせないか?

 

ALLはフルテーブルスキャン。インデックスが利用されていないことを示す

 

ref。ユニーク(PRIMARY or UNIQUE)でないインデックスを使って等価検索を行った時に利用されるアクセスタイプ。

 

WHEREで検索されるキーとなるカラムがインデックスされていなければインデックスを張る

 

対象カラムがユニークになっているようにする

 

インデックス関連

MySQLのクエリチューニング、アーキテクチャとか メモ

 

SQL インデックスが効かない検索

 

チューニングツールの利用

MySQLTuner CentOS7

 

ファイルディスクリプタ

カーネルチューニング ファイルディスクプリタ, TIME_WAIT CentOS7

 

 

ジェネラルログを出力する

フレームワークでORMのクエリを出力する時など、すべてのクエリを見たい時に

 

 

優技録のコンフィグ 4CPU 4GB

※2019年01月25日 2CPU 2GBにスケールダウン ボトルネックの改善による省リソースハック成功

 

 

Windows7, 2008以上のMySQLの場合

デフォルトでIPv6優先になっているので、IPv6優先かつlocalhostで接続になっている場合は遅くなる。

  • IPv4優先
  • 127.0.0.1で接続
    // アプリサーバとDBサーバが別でIPによる接続しているなら問題ない
  • 特に強い理由がなければミニマルなLinux版MySQLにした方が速い

 

 

コメントを残す

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

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)