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



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


 


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


CAT Blog


http://blogs.msdn.com/sqlcat/


Microsoft TechNet


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


 


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


 


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


http://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


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


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


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


 


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

 


 

Comments (0)