OLTPブループリント‐OLTPアプリケーションのパフォーマンス分析

  • Article

マイクロソフトの植田です。

 

私はSQL Server 開発部門でSQL Serverのテストを担当しております。現在は主にSQL Server ベスト・プラクティスと呼ばれるプロジェクトに参加しています。このプロジェクトで得られた結果は、米国のSQL Server Development Customer Advisory Team（CAT）によって以下のWebサイトから情報提供されています。

CAT Blog

https://blogs.msdn.com/sqlcat/

Microsoft TechNet

https://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx

 

上記ブログは内容がコンパクトにまとまっていて読みやすいものの、残念ながら現在は英語のみでの提供となっておりますので、この場を使って少しでも多くの情報を日本語でお伝えできればと考えております。

 

第一弾として、OLTPにおけるパフォーマンス分析を取り上げたいと思います。

https://blogs.msdn.com/sqlcat/archive/2006/06/23/Tom-Davidson-SQLCAT-Best-Practices.aspx

注：下記内容に関する詳細の確認をご希望される場合は上記のブログを参照いただけますようお願いします。

 

本ドキュメントは以下の方を対象としております。

· 開発者、テストエンジニア、データベース・アドミニストレータ

· Microsoft SQL Server プラットフォーム上でのアプリケーションのパフォーマンス・チューニングに携わっている、または、経験をお持ちの方

· 上記パフォーマンス分析/チューニングについて基本的な知識をお持ちの方

 

OLTP ブループリント－OLTPアプリケーションのパフォーマンス分析

 

パフォーマンス、および、チューニングのブループリント

様々なタイプのアプリケーションについて、どのようにリソースを利用しているか、そして、どのようにパフォーマンス・チューニングを行っていくべきかを考えてみましょう。OLTPのパフォーマンス分析は、リレーショナル・データ・ウェアハウスや、レポーティング・アプリケーションのパフォーマンス分析とは全く異なっています。これらの違いを理解し、より高いパフォーマンスを得るための方針を知っておくことはパフォーマンス・チューニングにおいてとても役に立ちます。

 

OLTP ブループリント

例えばOLTPアプリケーションの特徴は、個々の比較的小さな単位のトランザクション処理を大量に行うことです。それらはSELECT, INSERT, UPDATE, DELETEなどの処理を含んでいます。これらの処理の実装と、データベース・デザイン、リソース使用率、そしてシステムパフォーマンスはとても密接に関わり合っています。

 

OLTPパフォーマンス分析（ブループリント）の指針：

以下の条件に一致するとき、パフォーマンス劣化の問題が生じます。

注意）値カラムに使われている数値に関してどの値が適切かは、実環境において議論する必要があります。

リソース

ルール

説明

値

ソース

問題点

データベースデザイン

ルール1

X個のテーブルを使ったクエリを頻繁に行う

X>4

Sys.dm_exec_sql_text,　Sys.dm_exec_cached_plans

多くのテーブル結合を使ったクエリを頻繁に行うことは、広範囲のデータでキャッシュが平準化（⇔局所化）されるためOLTPの拡張性を高められない（スケールを大きくすることがリニアにスループット向上につながらない）

ルール2

X個のインデックスを持ったテーブルの更新を頻繁に行う

X >3

Sys.indexes,

Sys.dm_db_operational_index_stats

OLTP処理に対して過度のインデックス再構築が発生している

ルール3

大量のIO

テーブル・スキャン

レンジ・スキャン

X>1

パフォーマンス・オブジェクト：

　SQL Server Access Methods

Sys.dm_exec_query_stats

インデックス非利用、キャッシュのフラッシュ

ルール4

使用していないインデックス

Index not in*

* Sys.dm_db_index_usage_stats

使用していないインデックスのメンテナンス

CPU

ルール1

シグナル 待ち

>25%

Sys.dm_os_wait_stats

実行可能状態のクエリのCPU空き時間待ちが多発している

ルール2

Planの再利用

<90%

パフォーマンス・オブジェクト：

　SQL Server Statistics

個々のOLTPトランザクションが95%以上のプラン再利用率を持つことが理想的

ルール3

並列化：Cxpacket待ち

>5%

Sys.dm_os_wait_stats

並列化によるCPU間の同期処理がOLTPのスループットを低下させる

メモリー

ルール1

Page life expectancyの平均値

<300 (秒)

パフォーマンス・オブジェクト：

　SQL Server Buffer　Manager

　SQL Server Buffer Nodes

キャッシュのフラッシュ

大量のReadによるインデックス非利用の可能性

ルール2

Page life expectancyの平均値

50%低下

パフォーマンス・オブジェクト：

　SQL Server Buffer　Manager

キャッシュのフラッシュ

大量のReadによるインデックス非利用の可能性

ルール3

Memory Grants Pendingの値

>1

パフォーマンス・オブジェクト：

　SQL Server Memory Manager

ワークスペースメモリの使用許可を待っている現在のプロセス数

IO

ルール1

Avg Disk seconds / read　の値

>20ms

パフォーマンス・オブジェクト：

　Physical Disk

IO負荷がない状況でReadにかかる時間は4-8ms程度

ルール2

Avg Disk seconds / write　の値

>20ms

パフォーマンス・オブジェクト：

　Physical Disk

トランザクションログの連続書き込みは1msで行える

ルール3

大量のIO

テーブル・スキャン

>1

パフォーマンス・オブジェクト：

　SQL Server Access Methods

インデックスの非利用

キャッシュがフラッシュされている

ルール4

Wait statsの上位2つに以下の項目が入っている：

1.ASYNCH_IO_COMPLETION

2.IO_COMPLETION

3.LOGMGR

4.WRITELOG

5.PAGEIOLATCH_ｘ

Top 2

Sys.dm_os_wait_stats

Wait_statsの上位2つの中にIOに関するものが含まれていれば、IOボトルネックが発生している

ブロッキング

ルール1

ブロックの割合

>2%

Sys.dm_db_index_operational_stats

処理のブロックが頻繁に発生している

ルール2

ブロックプロセスレポート

30秒

Sp_configure, profiler

ステートメントのレポート

ルール3

行ロック待ちの平均

>100ms

Sys.dm_db_index_operaional_stats

ブロックの時間

ルール4

Wait statsの上位2つに以下の項目が入っている：

1. LCK_ｘ

Top 2

Sys.dm_os_wait_stats

Wait_statsの上位2つの中にロックに関するものが含まれていれば、ブロックボトルネックが発生している

結論として、OLTPは個々の比較的小さなトランザクション処理が大量に集まって構成されています。そして、それらはレポートタイプの処理やデータウェアハウスの処理とはリソースの使い方が全く異なっています。上記のブループリントは、リソースの使用状況を調べることを、典型的なOLTPアプリケーションの「パフォーマンス分析」を行う手法として考えています。

 

例えば、個々のOLTPトランザクション処理が大量に行われるということは、プランを再利用することが理想的であることを意味します。つまりCPUの使用時間はプランの再コンパイルや結合を減らすことによって削減することができます。効果的なインデックス設定、テーブル結合の軽減、および、page life expectancyを高く保つことによってIOパフォーマンスを改善できます。またインデックスの利用により、ソートを制限することができます。そしてブロッキングはインデックス・デザインとトランザクションを短くすることで発生を減らすことができます。

 

OLTP独特のパフォーマンス分析は、上記のブループリントで述べられているリソース使用率の観点において、パフォーマンス・チューニングの指針を提供します。具体的な値については議論があるかもしれませんが、リソースの使用率やパフォーマンスの特性に関するOLTPアプリケーションの一般的な概念は有効です。

 

なお、パフォーマンスに関するトラブルシュートとしてはMicrosoft TechNet （Japan）にて参考情報を公開させていただいております。上記で取り上げましたカウンタに関しても説明がありますので、興味がございましたらこちらもご参照ください。

Microsoft SQL Server TechCenter

https://www.microsoft.com/japan/technet/prodtechnol/sql/default.mspx

「SQL Server 2005 でのパフォーマンス問題のトラブルシューティング」

https://www.microsoft.com/japan/technet/prodtechnol/sql/2005/tsprfprb.mspx

 

コミュニティにおけるマイクロソフト社員による発言やコメントは、マイクロソフトの正式な見解またはコメントではありません。