close
プログラミング クラウド Microsoft Azure 情報処理資格 基本情報技術者 IT基礎 応用情報技術者 開発・設計方法 オブジェクト指向 内定者・新人研修 プログラミング基礎 アルゴリズム コンピュータ数学 内定者研修 新人研修 ヒューマンスキル プロジェクトマネジメント プレゼンテーション リーダーシップ 組織マネジメント ネゴシエーション ロジカルシンキング Java UI/UX HTTP JavaScript基礎 情報処理資格 ネットワークスペシャリスト ネットワーク インターネットルーティング応用 IPアドレス データベース応用 SQL応用 パフォーマンスチューニング データベース設計 ER図 概念設計(データベース) 論理設計(データベース) IT資格 Linux基礎 OS・システム基盤 セキュリティ TCP/IP OSI参照モデル データベースセキュリティ ファイアウォール 標的型攻撃 SQLインジェクション ネットワーク基本設計 CCNA Cisco プロジェクトマネジメント資格 情報処理資格プロジェクトマネージャ 情報処理安全確保支援士 人事給与 財務会計 管理会計 簿記 生産管理 在庫管理 ERP バランススコアカード 情報処理資格 ITアーキテクト 情報処理資格 ITストラテジスト 情報処理資格 ITサービスマネジメント 情報処理資格 システム監査 PMBOK® PMP® プロジェクト計画 WBS リスクコントロール ITIL ITサービスマネジメント 要求定義 要件定義 見積手法 ビジネスインダストリ 業種・業界知識 業務知識 提案力 ソフトウェアテスト基礎 情報処理資格 データベーススペシャリスト ハードウェア基礎 外部設計(基本設計) 内部設計(詳細設計) データベース基礎 SQL基礎 RDBMS 物理設計(データベース) C++ Ruby MVC基礎 Webアプリケーション開発 JavaEE Javaプログラミング応用 フレームワーク MVC応用 Spring フレームワーク ソフトウェアテスト応用 テスト手法 JUnit スマートフォンアプリ開発 Androidアプリ開発 C# 基礎 C# 応用 負荷テスト Javaプログラミング基礎 ソフトウェアテスト コーチング メンタリング HTML/CSS サーバー構築 仮想化技術 KVS (NoSQL) アジャイル スクラム ファシリテーション C言語 ITパスポート JSTQB データサイエンス 単体テスト ユニットテスト キャリアアップ インターネットルーティング基礎 パケット解析 LAN構築 データベース データサイエンティスト トレンド 障害対応 インフラ監視 HTTP/2.0 コンピュータサイエンス VPN ネットワーク物理設計 データベース障害 JavaScript モダンJS (Modern JavaScript) 応用 MVS応用 バックアップ/リカバリ 分散処理 Hadoop Hive Python AI 深層学習(DeepLearning) CentOS Linux応用 Zabbix シェルスクリプト Infrastructure as Code Windowsサーバー基礎 内部設計 Docker DevOps Windowsサーバー応用 NginX chef Ainsible ロジカルライティング R テスト自動化 Jenkins Git 継続的インテグレーション (CI) バージョン管理 Vagrant 要求分析 Redmine 継続的インテグレーション(CI) 継続的デリバリー (CD) ヒューマンリソース管理 Web API マイクロサービス コミュニケーション 業務知識/業界知識 マーケティング 語学 AWS 法務 IoT ビジネスマナー OJT 業務効率化 表計算ソフト オフィスソフト コンプライアンス フロントエンド Subversion PHP 関数型プログラミング Laravel モダンJS (Modern JavaScript) 基礎 Android Studio 機械学習 iOSアプリ開発 ぷプログラミング React 次世代高度IT人材 共創 IPA Raspberry Pi Xamarin スクリプト言語 GoF CUI VBA 資格 ビジネス文書 jQuery 研修参加レポート マネジメント OSPF テーブル設計 アンガーマネジメント クリティカル・シンキング PDU 経営改善 Pマーク 問題解決技法 サイバー攻撃 エンジニア 参加してみた エンゲージメントマネジメント 労働関連法 新人育成 ネットワーク構築 情報セキュリティマネジメント デザインパターン リファクタリング マルチスレッドプログラミング ベンダーコントロール Modern JavaScript 冗長化 VLAN インフラエンジニア チームビルディング テストケース リーダブルコード セキュリティ入門 ネットワーク入門 Node.js npm gulp ビルドツール Python入門 冗長化入門 インフラ実機演習 プロジェクト管理 Active Directory ネットワーク管理 コンテナ 正規化理論 Haskell 品質管理 OpenStack シンギュラリティ DBA中級 プロトコル UX 基本設計 FinTech トラブルシューティング 並列処理 見える化 PMO ロジカルコミュニケーション Deep Learning インデックス設計 超上流工程 BGP Excel C-CENT Selenide プライベートクラウド アセンブラ コンピュータ基礎 工数見積 CCENT 法律知識 失敗から学ぶ プロジェクト失敗事例 PDCA プログラミング入門 非エンジニア向け 4Biz DNS セルフマネジメント 片付け術 サーバーダウン サーバー タイムマネジメント GO言語 プロダクトマネジメント プロダクトマネージャ LVS ロードバランサー 負荷分散 仮想通過 犯罪心理学 情報漏えい SEカレッジ導入事例 IT研修を聞いてみた 会社規模 (100~300名) IT研修制度を聞いてみた CentOS7 開発環境構築 数字力 財務 IT人材 UI Machine Learning Go言語 (golang) データマイニング 統計学 新人教育 やり直し数学 RDB つながる工場 モチベーション WebSocket WebWorker HTML5 CSS3 Bootstrap 微分・積分 システム設計 決断力 LAMP環境 トレ担 教育研修担当者向け ルーティング Linux入門 図解術 目標設定 試験対策 インタビュー技法 Vue.js ブロックチェーン 会社規模 (~50名) DHCP 仕掛け学 BSC 財務諸表 自己分析 RIP スタティックルート バッファオーバーフロー DoS攻撃 システム開発 会社規模 (~99名) Wireshark パケットキャプチャ 管理職研修 部下育成 ワークあり 文章力 情報システム部門向け プロジェクトリーダー プロジェクトマネージャ 塗り絵 リスク管理 法改定 会社の仕組み Chainer AI人材 会話術 会社規模 (~25名) テスト技法 組織規模 51名~99名 組織規模:~199名 [組織規模]199名まで 組織規模 199名まで 組織規模199名まで 会社規模199名まで 会社規模49名まで 会社規模99名まで アプリ開発 サーバサイドJava 営業知識 Cloud 栄養学 基本コマンド ウォーターフォールモデル ヘルスケア 論理設計 ニューラルネットワーク ハンズオン UML 顧客ヒアリング マウスで学ぶ Apache EC2 Lightsail M5Stack DevSecOps プロジェクト成果 画像認識 チャットポット コマンド レビュー 基本用語 自動構築 LPIC-1 サーバーサイドJavascript キャリア形成 ワークライフバランス インバスケット テック用語 GitHub Windows エディタ 教養 令和時代 RESTful API 物理設計 会社規模300名以上 データモデリング サーバーサイドJava Webサーバー基礎 Webサーバー応用 Watson IBMWatson Learning Topics OS モバイル コンテスト トレーニング手法 アーキテクチャ 人材モデル インフラ CI/CD Infrastructure as a Code チーム開発 制度づくり Special_Intro AI市場分析 研修ロードマップ 仕事術 デジタルトランスフォーメーション 財務分析手法 情報整理 PowerPoint 新しい研修 オンライン研修 見どころ紹介 統計分析 ディープラーニング G検定 情報処理技術者試験 販売管理 C# テスト計画 Linuxサーバー WEBサーバ構築 http/2 Postfix イーサリアム プロジェクト・メンバ 正規化 パケット実験 作業分解 トラブル調査 ネットワーク設計 Windows server 2016 ネットワーク機器 DX 管理職 最新動向 ポストコロナ時代 IoTデバイス マイコンボード センサ サーバー仮想化 仮想ルータ WAN インターネットVPN 若手エンジニア ITプロジェクト 人事面談 DX人材育成 Java基礎 ZAP 脆弱性診断 NWサービス構築 イノベーション・マネジメント ネットワークセキュリティ ストレッチ

パフォーマンスチューニングの勘所 研修コースに参加してみた


2020-05-12 更新

今回参加した研修コースは パフォーマンスチューニングの勘所 です。

パフォーマンスチューニングというとSQLの改善というのが挙げられがちで、効果も発揮するのですが、このコースでは 「推測するな、計測せよ」 という鉄則通り、モニタリングから始めて、なぜこのパラメータをターゲットにして、なぜこの数値をメトリクスにするのか、それをRDBMSのアーキテクチャから解説いただきました。

講師の林さんの言葉通り、勘に頼ったチューニングではなく、アーキテクチャから体系化して理解したい方にはとてもオススメの内容です。

では、どんな内容だったのか、レポートします!!

 

想定している受講者

想定している受講者 データベース物理構成に関する用語、SQLに関する用語が理解できている
受講目標 データベース全体のパフォーマンス改善、劣化予防のポイントが指摘できるようになる

 

講師紹介

登壇されたのは 林 優子さん です。前回 ハンズオンで学ぶデータベース障害のパターンと復旧 研修コースに参加してみた でもレポートしましたが、今回も参加された方に合わせた丁寧に解説いただきました!

林 優子
林 優子
Oracle認定講師としてExcellent Instructorを連続受賞した人気トレーナー。
Oracleだけでなく様々なRDBMSに対応し、内部構造もわかりやすく解説

というわけで、今回も参加された方の使っているRBMSを伺ってスタートしました。

 

参加された受講者が使っているRDBMS

  • Oracle 9
  • SQLServer 4
  • MySQL 3
  • DB2 3

状態の把握:モニタリング

パフォーマンスチューニングは勘に頼っている部分があるので、アーキテクチャから体系化してチューニングしましょう、というコースの狙いからモニタリングから解説です。

 

  • まずはOSからモニタリングすること
    • DBサーバーのOSのパフォーマンスを観測
    • DBベンダーは DB:OS = 6:4 ぐらいがよいと推奨
      • DBだけでなくOSもプロセスを動かしているので消費しています
      • %system が上ってきたならば %iowait を見て、ディスクI/Oを見ましょう

パフォーマンス劣化の原因

  • ディスクI/O
  • 競合による待機

ディスクI/Oは当然ながら、競合による待機、ロック待ちも注意です。メモリ高負荷を招き、全件検索が多いとそうなります。
今日はここを中心に解説されるとのことでした。

DBアーキテクチャとチューニングのポイント

では、なぜメモリ高負荷が起こるのか、アーキテクチャから解説いただきました。

 

 

SQL解析

SQL発行 SELECT * FROM CUSTOMER WHERE no=123

  1. ワーカープロセスが動く // ワーカープロセスの呼称はRDBMSによって違う
  2. SQLを解析
  3. 構造解析
    • ちなみに SELECT * or SELECT (column) どっちが早い?
    • 実はあんまり大差はなくカラム指定のほうが零コンマ何秒早い
  4. 最適化 (実行計画作成)
    • メモリ上にある内部的なテーブル (データファイル) を見に行く
      • いきなりディスクは見に行かない
      • このコースではここをSQLキャッシュといいます
    • 最適化というのは乗換案内アプリと同じで、コスト (所要時間) をみて最適経路を考える
  5. すでに実行したSQLがあれば、プログラムコード (Pコード) もキャッシュされているので、それを使って実行
    • なのでメモリで共有しやすいSQLを書こう
    • アプリケーション側でバインド変数、プレースホルダーを利用しましょう
  6. 実行段階でSQLキャッシュが無くなっていれば、空けるためにPコードを追い出す
    • もし追い出したPコードを使うときは内部テーブルからRELOADする
    • パフォーマンスチューニングの指標はこのRELOADを0にすること
    • その上でSQLキャッシュを増やすかどうかを考えましょう

 

一旦ここまでのまとめ

  • SQLを共有できていますか?
    • 指標はヒット率
    • 90%が目標
  • RELOADが0か?
    • 0じゃなければSQLキャッシュを増やす

 

ただしメモリは有限なので、SQLキャッシュを増やすと、バッファキャッシュなどが小さくなってしまいます。
自動チューニングの時代と言っても、ここまでやってくれないので、ここが基本です。

 

SQL実行

  1. バッファキャッシュにテーブルが無ければディスクにアクセス
    • ブロックやページと呼ばれる単位でアクセス
    • これがディスクI/Oの単位 (DB2を除きRDBMSのほとんどは8KB)
    • 1レコード長が8KB以下ならどんなデータだろうがなんだろうがよい
  2. 見つけたものをメモリに上げる
    • これが索引 (インデックス) 検索
    • 一方で全件検索とは?
    • 連続したブロック/ページ 8KB x 8 =64KB が1エクステント
    • 1回で読みに行く単位が1エクステント
      • この64KBにおさまるテーブルかどうかで分割するかどうかを検討する
      • 全件検索が多い (例えばデータ解析) 場合、このエクステントもしくはブロック/ページサイズを大きくする
    • 読み込んだ1エクステントのデータをバッファキャッシュに入れてロックしてから検索する
      • 同時アクセスが多い場合、ロックと解放が繰り返され、CPUが上がる
      • バッファビジーウェイト (buffer busy waits) と呼ばれる症状
      • CPUが高負荷、ディスクI/Oは少ない、というのはだいたいこの症状
      • バッファビジーウェイトが多発するようであれば索引検索に変えましょう
    • それでも全件検索が必要な場合、バッファキャッシュ上のサイズの閾値を設けよう
      • テーブルサイズが大きい場合、バッファキャッシュを食いつぶしてしまう可能性がある
      • バッファキャッシュの**%以上のテーブルサイズを読み込む場合は、その**%以内で読み込んでは捨てています
    • OLTP (INSERTやUPDATEなどが多い) 系のシステム は索引検索を使おう
      • ヒット率を高めるためにはブロックサイズを小さくして、色々な種類のデータをメモリにあった方がヒット率が上がる
      • INSERT が多発する場合、ブロック/ページへの書き込み待ちが発生する (バッファビジーウェイト)
      • ブロック/ページサイズを小さくすると、違うブロック/ページにINSERTさせる
      • OSによって処理できる単位が違うので注意しましょう (Windowsなら8KBが推奨される)

共有メモリ

今は大容量を扱えることになっているので、気にしないこともありますが、一応基本として紹介されました。

 

  • DB以外のアプリケーション(OSなど)が動いている
  • 昔は夜間バッチの際にはバッファキャッシュの割合を変えていた
    • 昼間はSQLキャッシュやワークエリアの割合を増やしてヒット率を向上
    • 夜間はバッチで大量にINSERTがあったり、全件検索をする場合、バッファキャッシュの割合を増やしたりしていた

SQLのチューニング

他コース (良いSQLと悪いSQL) があるので、深掘りしたい方はそちらへ! ここでは使う頻度の高いテクニックを紹介頂きました。

 

索引検索

 

  • SELECT * FROM orders WHERE ORD_id=2 ならスグに見つけたら終了できる
  • SELECT * FROM orders WHERE CUST_id=20 なら?
    • ソート済みの CUST テーブルを見に行ったほうがよい
    • でも CUST_id=100000 なら?
    • そうならならいよう、BalancedTree を適用しておきましょう
    • これが索引検索
      • hoge = 1, -> 索引検索が使える
      • hoge <> 1, -> 索引検索ではやらない // 全件と一緒なので
      • fuga LIKE 'A%', -> BETWEEN句と同じで前方一致は索引検索を使う
      • fuga LIKE '%Z', -> 後方一致は索引検索を使わない
      • 前方一致検索と後方一致検索の速さの違いはここにある
  • プライマリーキーにクラスタ索引 (値が小さい順に並ぶ) が適用されている場合
    • 例えば商品コードというのがプリマリーキーになるが、数値での設定が望ましい
      • M001T のようなコードはブブー、ダメです。。
      • INSERT するたびにクラスタ索引を再構成している
        • M と T のアルファベットで再構成してからINSERT
      • 一度クラスタ索引を落として、再配置したほうが早い

 

索引検索をわかりやすくホワイトボードに書いて頂いたのですが、写真が撮れず、、ここではわかりやすく書かれている記事がありますので、それをご覧ください。

 

 

JOIN のチューニング

このパートでは実際に用意されたテーブルをもとにJOINを実行して計測してみます。

  • SQL

  • 実行計画


Rows や Bytes を見ると、72件のデータを抽出したいのに 918k を対象としてしまっているので、対象を絞りましょう。

 

  • SQL

  • 実行計画を見る

  • TempSpc というカラムが追加されている
    • ワーカープロセス (グループ・並び替え・結合) を行う仮想メモリ領域のこと
  • 処理が多いので仮想メモリに追い出して実行している
    • つまりはディスクI/Oが発生している -> この部分のSQLを見直す
    • WHERE句などで918Kではなく36148だけを絞り込んで対象にしたい
    • 結合をハッシュではなくWHERE句で絞り込んでネストループに出来ないか
  • この処理が夜間バッチであればワーカーメモリを増やす設定が出来ないか

 

このJOINのチューニングポイントを解説頂いたところで、このコースは終了となりました。

まとめ

このコースではSQLも含めて、パフォーマンスチューニングのポイントをアーキテクチャから体系的に理解して、どのパラメータをどのようなメトリクスで測るのか、なぜ索引検索は速いのかなど、解説いただきました。

ホワイトボードで図を交えながら、とてもわかりやすく解説いただいたので、なぜそのチューニングテクニックが効くのか、理解できました! ちゃんとOSのモニタリングや、SQLの実行計画を見て考える癖をつけて行きます!
(私どもの製品ではORマッパーを多用しているので、それこそちゃんと計測せねば。。)

パフォーマンスチューニングはDBAの腕の見せどころの1つなので、構築設定だけでなく、これから腕を上げていこうとお考えのDBエンジニアにはとてもオススメです!!

 

 

label SE カレッジの無料見学、資料請求などお問い合わせはこちらから!!


SEカレッジについて

label SEカレッジを詳しく知りたいという方はこちらから !!

午前免除 FAQ

タグ一覧