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

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

 

私は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

 

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