Category Archives: チューニング

mysql_clr

◇MySQLのチューニングに関連して、メモリ関連のパラメータを整理してみた。 1. innodb_buffer_pool_size:  【デフォルト値:8388608(単位:バイト)】 InnoDBのバッファプールサイズ InnoDBをメインで使う場合の、最も重要なメモリ領域。 各種SQLを処理する際に、レコードデータキャッシュ領域として利用される領域。 InnoDBバッファプール内に収まるデータサイズの場合は、ディスクアクセスが発生しないため、パフォーマンスに大きく影響する。 設定値の目安:1つのインスタンスのみが稼動するMySQL専用サーバ上でInnoDBをメインで動かす場合、全部つりメモリサイズの60%~80%程度を指定する事が推奨されている。 2. innodb_additional_mem_pool_size: 【デフォルト値:1048576(単位:バイト)】 InnoDB追加バッファサイズ InnoDBテーブルの定義情報などのデータディクショナリ情報を格納。InnoDBのテーブル数を多くする場合は、増やした方が良いとされている。 3. innodb_log_buffer_size:  【デフォルト値:1048576(単位:バイト)】 InnoDBログバッファサイズ OracleでいうREDOログファイルのサイズに相当する。1つのトランザクション内で多くのデータを更新するのであれば、サイズを大きくした方がよい。 設定値の目安:1MB~8MBの範囲内での設定が推奨されている。(デフォルトは1MB) 4. query_cache_size:  【デフォルト値:0(単位:バイト)】 クエリキャッシュのサイズ SELECT文の実行結果をメモリ上にキャッシュしておいて、まったく同じ SELECT文が送られた場合には、キャッシュされた結果をそのまま返すため、パフォーマンスに大きく影響する。 5. key_buffer_size:  【デフォルト値:8388600(単位:バイト)】 キーバッファのサイズ MyISAMテーブルに対してインデックス情報を格納する領域。デフォルトは8MBだがMyISAMのテーブル数を多くする場合は、このサイズを大きくした方がよい。 6. binlog_cache_size:  【デフォルト値:32768(単位:バイト)】 バイナリログキャッシュサイズ 未コミットのトランザクションの情報をキャッシュしておく。 トランザクション情報がこのサイズに収まらない場合は、一次ファイルが使用される。 7. read_buffer_size:  【デフォルト値:131072(単位:バイト)】 リードバッファサイズ 全件検索に使用されるメモリ領域。インデックスの使用に関わらず、このリードバッファが使用される。テーブル全体をスキャンするSQL文を何度も実行する場合は、この値を大きくした方がよい。 8. join_buffer_size: 【デフォルト値:131072(単位:バイト)】 ジョインバッファサイズ フルジョインにおいて、レコードデータのキャッシュに使用される。 1回の振るジョインにつき1つのジョインバッファが確保され、SQL文の実行終了とともに解放される。 9. sort_buffer_size: 【デフォルト値:2097144(単位:バイト)】 ソートバッファサイズ ソートの際に使用される領域。多数の検索結果に対してorder byやgroup byを多用する場合は、この値を大きくした方が良い。 10. max_allowed_packet: 【デフォルト値:1048576(単位:バイト)】 通信バッファサイズ ユーザプロセスとサーバスレッドの間のSQLや結果のよりとりの際に使用される。 デフォルトは1MBだが、ブロブ型のようなサイズの大きなデータを扱う場合や、バルクインサート文で大量のデータを一度にロードする場合は、この値を大きくすると良い。 11. thread_stack: 【デフォルト値:196608(単位:バイト)】 スレッド固有のスタックサイズ スタック領域と呼ばれる、スレッド固有の領域のこと。 バイナリパッケージの種類によっては変更できないものもあり、基本的には、デフォルト値のままでよいとされている。 12. thread_cache_size: 【デフォルト値:0(単位:バイト)】 キャッシュするスレッド数 接続終了後のサーバスレッドを、次の接続時に再利用できるようにキャッシュしておける機能があり、接続時のオーバヘッドを軽減できる。 デフォルトは、0で無効になっている。 13. max_connections: […]

mysql_clr

MySQLに限らず、DBチューニング時の着眼点を以下に纏めてみた。 1. SQL 2. DB論理設計(インデックス含む) 3. DB物理設計 4. アプリケーションロジック 5. メモリリソース 6. ディスクI/O 7. ネットワーク 8. OS 上記の各項目の確認時のポイントと方法の概要をMySQLの場合を想定し以下に記載。 1. SQL  ポイント:SQLの書き方によって、パフォーマンスが著しく       低下することがあるため、実行に一定時間以上       かかったものや、テーブルフルスキャンを行った       SQLの有無を確認する。  方法  :スロークエリログの確認 2. DB論理設計(インデックス含む)  ポイント:テーブル毎のデータ件数と、取得方法を想定して       インデックスを定義する。       膨大なデータを扱う場合は、特にテーブルの       パーティショニングも検討する等。  方法  :各テーブル毎のデータ件数と、データサイズの想定、       テーブル間のリレーション(ER)を確認。       EXPLAIN文による診断。 3. DB物理設計  ポイント:サーバ構成とデータファイルを配置するパーティション       及び、ファイルシステムが適切かどうか。       無駄にデータの格納サイズを必要とした型定義は無いか。  方法  :物理的なパーティションの分割やRAIDタイプ、ファイル       システムの確認。       EXPLAIN文による診断。       mytopコマンドによるMySQLの実行状況の確認       TOPコマンド等でOSの各種リソース使用状況の確認。 4. アプリケーションロジック  ポイント:DBアクセスを減らす       クエリキャッシュのヒット率を上げる       JOIN等で取得する必要情報をSQLで取得するか、       ロジックで吸収するかのコスト判断       複数のコアを持ったサーバ上で動かす場合、       CPUを効率よく使用できるつくりになっているか。  方法  :SQLのチューニングと関連して、以下を検討する。       アプリケーションの処理時間をデバックコードで確認       EXPLAIN文による診断。       SHOW STATUS コマンドによる、各種情報の確認。 5. メモリリソース  ポイント:OSのメモリ使用量と合わせて、クエリキャッシュや       max_connectionsやthread_cache_sizeの適切な       設定値を検討する。  方法  :max_used_connectionsを確認し、max_connectionsの       設定値に達しているか否かを確認。       達している場合は設定値を上げるかコネクションプールを       使用する方向で、メモリリソースを検討する。       SHOW STATUS コマンドによる、各種情報の確認。       mytopコマンドによるMySQLの実行状況の確認       TOPコマンド等でOSの各種リソース使用状況の確認。 6. ディスクI/O  ポイント:ディスクI/Oを確認し、I/O待ちが発生している場合は、 […]