このページは、以下の英語ページの抄訳です。最新の情報については、
英語ページを参照してください。
WAN 経由で実行される SQL Anywhere パフォーマンス最適化
はじめに
このドキュメントでは、WAN 経由で実行される SQL Anywhere ネットワークサーバーのパフォーマンスチューニングについて説明します。
WAN の実装で選択されるプロトコルは、TCP/IP のため、このドキュメントではこれに主にフォーカスします。
ネットワークパフォーマンスチューニングは、ある特定のアプリケーションとネットワークで最もうまく機能するものを決定するためのインタラクティブなプロセスである点に注意してください。
アプリケーションのパフォーマンス最適化で推奨するステップは以下のとおりです。
- アプリケーションで使用するネットワークのパフォーマンスを測定する。
- ネットワークのパフォーマンスをベースにアプリケーションをチューニングし、リクエストを減らす。そして/または転送するデータの量を削減する。
- ネットワークのパフォーマンスをベースに、SQL Anywhere のサーバーオプションと接続パラメーターをチューニングし、パフォーマンスを最大化する。
- LAN におけるチューニングあるいは同様のマシンにおけるオペレーションのインプリケーションを考慮する。
多くの場合、SQL Anywhere をチューニングするよりも、アプリケーションをチューニングした方がパフォーマンスに大きく影響します。
ネットワークのパフォーマンスを測定する
ネットワークのパフォーマンス説明には、レイテンシーとスループットを使います。
レイテンシーは、1台のマシンがデータのパケットを送信した時間と、2 台目のマシンがデータを受信した時間間の遅延を参照します (例えば、2 台目のマシンが最初のマシンよりデータを10ms 遅く受信した場合、そのレイテンシーは10ms になります)。
スループットは、ある一定の時間に転送可能なデータの総量を参照します (例えば、1台のマシンが1000KB のデータを送信し、2台目のマシンが全てのデータを受信するまでに 5 秒かかった場合、そのスループットは 200 KB/s となります)。
LAN では、通常レイテンシーは1 ms 以下です。そして、スループットは、通常 10 MB/s あるいはそれ以上になります。
WAN では、通常レイテンシーは、非常に大きく (おそらく 1 ms から 500 ms)、スループットは通常より小さくなります ( おそらく 6 KB/s から 2 MB/s)。
ラウンドトリップ時間とは、1台目のマシンから2台目のマシンにデータを転送するレイテンシーと2 台目のマシンから 1 台目のマシンにデータを転送するレイテンシーの合計です。
ネットワークスループットは、最低 200 KB のサイズのファイルを、1 台目から 2 台目にコピーして、そのコピーのタイミングから測ることができます。
コピーは、FTP を使用した一般的なファイルコピー、あるいはインターネットブラウザーを使用したファイルのダウンロードとしてでもかまいません。
2 台のマシン間のレイテンシーとスループットを見積もり方法として、SQL Anywhere ネットワークサーバーを片方のマシンで実行し(データベースサーバーがすでに高負荷になっていないことを確認してください)、下を2台目のマシンで実行するという方法もあります。
dbping -d -c <connection string to network server> -st 10
スループットが大きいネットワークでは、dbping でレポートされるスループット値は実際のネットワークのスループットより小さくなる可能性があることに注意してください。
レイテンシーが大きく、スループットがリーズナブルなネットワークで、リーズナブルな SQL Anywhere のパフォーマンスを得るには、クライアントで作成したリクエスト数を、最小限にする必要があります。
レイテンシーがリーズナブルで、スループットが小さいネットワークの場合には、クライアントとサーバー間で転送されるデータの量を最小限にする必要があります。
アプリケーションをチューニングしてWAN 経由のパフォーマンスを改善する
一般的には下の提案を実装してアプリケーションを変更することで、リクエスト数と転送データ量を削減することができます。
これらの提案は、どの環境(スタンドアロン、LAN、WAN)のアプリケーションのパフォーマンス改善でも役に立ちます。
- アプリケーションから、多くのSQL 文を必要とするロジックを1つ以上のストアドプロシージャーまたは関数に移す。
- ベーシックな同じSQL 文が1回以上使用されている場合には、一度ステートメントを準備し、異なるパラメーターで複数回実行することを検討する。
- アプリケーションが必要以上にクエリーまたはSQL 文を実行しないようにする。特定のクエリーが1度以上実行されている場合には、最初にクエリーが実行された時に結果をキャッシュするようアプリケーションを変更し、クエリーを再実行するのではなく、キャッシュされた値を使用することを検討する。
- 1つのプロパティー、関数、変数値を得るクエリーを結合させて1つのマルチ-カラム-クエリー にする。
以下のクエリーを実行したい場合、
SELECT current user 、
SELECT @@servername および SELECT connection_property('BytesSent') の 3つのクエリーを実行するのではなく、1つのクエリーを使用する。
SELECT current user, @@servername, connection_property( 'BytesSent' )
- サーバーで join を実行できる場合には、アプリケーション内で複数のクエリーを使って join を行うことを避ける。アプリケーションで1つのクエリーを実行し、それから2つめのクエリーを最初のクエリー結果を使用して実行する場合には、本質的にはアプリケーション内で複数のクエリーを一緒に join していることになります。複数のクエリーではなく単一のクエリーを使用して join を行うことが可能であれば、劇的にパフォーマンスを改善することができます。単純な例として、アプリケーションで以下のクエリーを実行しSELECT T.x FROM T WHERE <conditions on T> ORDER BY <order> 、それから T.x の各値にSELECT R.y FROM R WHERE R.z = <value of T.x> を実行するとすると、 SELECT T.x, R.y FROM T, R WHERE <conditions on T> AND R.z = T.x ORDER BY <order> という1つのクエリーにまとめることができます。
- 接続を初期化した後にオプション設定することを避ける。DDL (Data Definition Language、データ定義用語) を避ける。そして設定変数をドロップすることを避ける。これらは、クライアントステートメントキャッシングを発生させて、キャッシュされたステートメントを再使用できなくしてしまう可能性があり、また、これらはキャッシュプランを発生させ、再使用できなくしてしまう可能性があります。DLL を本番環境で使用すると、DLL オペレーション中の全体パフォーマンスの低下、サーバーのメモリーにリロードする必要のあるプロシージャーなどのサイド効果など、結果として潜在的なパフォーマンス問題になる可能性があります。
- 単一の長い接続が使用できる場合には、複数の短い接続を避ける。これが不可能な場合には (例えば web サーバーからの場合)、接続プーリングを検討する。SQL Anywhere は、ビルトインの接続プーリング (SQL Anywhere .NET データプロバイダーでは、POOLING 接続パラメーターを使用し、他のクライアント API では、ConnectionPool 接続パラメーターを使用) を持っており、アプリケーションによっては、パフォーマンスが改善される設定が可能です。
- サードパーティー製の開発ツールを使用する場合 (例えば PowerBuilder、Visual Basic や Delphi など)、パフォーマンスを向上するためにアプリケーションに特有の設定があるかどうかチェックする。例えば、PowerBuilder ではBLOCK 接続プロパティーを変更することで、WAN 経由のパフォーマンスが改善されることがあります。
リクエストの数を減らす
下の提案でリクエスト数を減らせる可能性があります。特に、ネットワークのレイテンシーが大きい場合には有効です。これらの提案は、他のネットワークにおけるパフォーマンスを改善する可能性もあります。
- get データを使用するのではなく、フェッチデータへのbound columnを使用する。これにより、特にカーソルからの最初のローをフェッチする場合にリクエストの数を削減できます。
- 可能なところで、SQL 文をバッチ (全て1つのステートメントであるかのように実行されるセミコロンによって分割された 一連の SQL 文)に統合する。
- オートコミットを無効にし、余分なコミットリクエスト (コミットまたはロールバックは、多くの場合、過剰なブロックを避けるためユーザーのインプットを待つ前に実行する必要がある)を削除する必要がある時だけ明示的にコミットする。
- ワイドフェッチ(ローセットサイズを増加する)と、ワイドインサート(パラメーター値のアレイを使用する)を使用する。これらは、1つのローに対して1つのリクエストではなく、1つのリクエストで複数のローをフェッチまたは挿入する。プリフェッチも、リクエストごとに1以上のローをフェッチします。
ネットワークで転送されるデータ量を減らす
以下の提案は、一般的に転送データ量を削減します。これは特にネットワークのスループットが小さい場合に有効です。これらの提案で、他のネットワークにおけるパフォーマンス低下をもたらすことはあまりありません。
- 大きなデータベースクエリーには、ストアドプロシージャーを使用することを検討する。これは、小さなCALL 文の送信でクエリーを実行できるため、ネットワーク越しに大きな文をサーバーに送る必要性がなくなります。
- クエリーの最初のロー(あるいは最初のいくつかのロー)だけをフェッチングすることがわかっていれば、FIRST または TOP n 節をクエリに追加する。あるクエリー内の最初のいくつかのローをスキップしたいのであれば、START AT 節を使用します。これらの節は、特に、プリフェッチが有効の場合、使用しないローが転送されるのを防止します。また、これらの文を追加することで、クエリーオプティマイザーが効率的な実行方法を理解するのにも役立ちます。
SQL Anywhere をチューニングして WAN 経由のパフォーマンスを改善する
以下の提案は、SQL Anywhere をチューニングすることで、WAN 上のアプリケーションの実行を向上させます。そのため、ネットワークのレイテンシーやスループットとは関係ありません。
- liveness が原因で接続が切断される場合には、liveness timeout 値を増やすことを検討する。この値を増やすこと自体がパフォーマンスを改善するわけではありませんが、livenessが原因で接続が継続してタイムアウトするのであれば、この値を増やす必要があります。このオプションは(LivenessTimeout接続パラメーターを使用して)クライアントまたはサーバー (-tl)で設定することができます。LivenessTimeout 接続パラメーターを使用すれば、WAN 接続専用の liveness timeout に変更することができます。
多くの場合、下の提案方法でリクエスト数を減らすことができます。これは特にネットワークのレイテンシーが大きい場合に有効です。
- プリフェッチの動作を変更することを検討する。アプリケーションがローをフェッチする場合、SQL Anywhere はカーソルタイプや他の要因次第で追加ローをプリフェッチする可能性があります。プリフェッチは、多くのローがカーソルからフェッチされる場合、特にレイテンシーが大きいネットワークにおいて、リクエストを削減し、パフォーマンスを大幅に改善することができます。絶対フェッチ、相対ネガティブまたは相対0フェッチ、ロールバック、そして、ある get データオペレーションは、プリフェッチローを処分し、再フェッチさせます。これはパフォーマンスを低下させることになります。プリフェッチは、最初のロー以上のローがフェッチされた場合には、forward-only, read-only カーソルでこそ最善で使用されます。アプリケーションが各カーソルからの多くのローをフェッチし、fetch next オペレーションだけを使用する場合、PrefetchRows および PrefetchBuffer 接続パラメーターを使用することで、パフォーマンスが向上することがあるかもしれませんが、一般的にはデフォルトの値で十分です。アプリケーションが結果セットの一部のみをフェッチする場合に多くのローをプリフェッチすると、潜在的にパフォーマンスを低下させることになります。さらに、多くのカーソルのオープンがされている場合には、ODBC や JDBC アプリケーションは、PrefetchOnOpen 接続パラメーターの恩恵を享受することができます。これらのカーソルはプリフェッチできないので、正確性のために絶対的に必要でなければ、(ODBC DYNAMIC、KEYSET、ESQL SENSTIVE、SCROLL タイプなどの) value センシティブなカーソルタイプの使用はさけます。
- 特に、多くのカーソルがオープンしクローズされる場合、LazyClose 接続パラメーターを使用することを検討する。これにより、カーソルをクローズする際に余分なリクエストをなくすことができます。
ネットワークのスループットが小さい場合には、以下の提案が有効である可能性があります。
- 通信の圧縮 (サーバーの -pc オプションまたはクライアントの圧縮接続パラメーター) を使用することを検討する。通信の圧縮を使用することで、転送データ量、転送パケット数を減らすことはできますが、リクエスト数を減るわけではありません。通信の圧縮は、特に大きなフェッチ、マルチローフェッチ、または、BLOB オペレーションの場合に有効です。ただし、通信の圧縮では、クライアントとサーバーの両方でCPUやメモリをより多く使用することに注意してください。その結果、場合によってはパフォーマンスが悪くなることがあります。そして多くの場合、LANでのパフォーマンスが低下します。クライアントの圧縮接続パラメーターを使用することで、WAN 接続のみ通信圧縮を有効にすることができます。
- ReceiveBufferSize と SendBufferSize TCP/IP プロトコルオプションをクライアントとサーバーの両方に使用することを検討する。これらのオプションは、プロトコルスタックのメモリーを事前に割り当てて、TCP/IP パケットを送信・受信します。プロトコルスタック内のメモリーの事前割り当ては、ネットワークインテンシブなアプリケーションのクライアント/サーバーのパフォーマンスを増大することができます。デフォルト値はマシン依存で、実験には約65 536 から 262 144 バイトの範囲の値がリーズナブルです。
- アプリケーションがカーソルからの最初のローのみを使用する場合、DisableMultiRowFetch 接続パラメーターを使用してプリフェッチを無効にすることで、パフォーマンスを改善することが可能です。DisableMultiRowFetch を使用するかわりに、上記のアプリケーションチューニングのセクションで提案したように、最初のローだけをフェッチするところで、クエリでFIRST 節を使用するようにアプリケーションを変更すると、より速いパフォーマンスを得られる可能性があります。DisableMultiRowFetch を使用すると、より速いネットワークであっても、多くの場合に、パフォーマンスの悪化を起こす可能性があります。
チューニングをスタートするにあたり、いくつか設定上の提案があります。これらの設定でスタートしてテスト環境に適用し、パフォーマンスにどう影響するか確認してください。また、上に記載したサーバーオプションや接続パラメータの追加、削除を試みて、パフォーマンスにどう影響するか確認してください。これは、厳密な科学ではないので、特定のネットワークにおける特定のアプリケーションのベストのパフォーマンスを得るには、トライアル&エラーを何度か行う必要があります。
LAN の潜在的なパフォーマンスのimplicationについての詳細は、下の「LAN経由、および同様のマシンでのオペレーションのパフォーマンスインプリケーション」のセクションを参照してください。
データベースサーバーコマンドラインオプション
dbsrv16 -x TCPIP(SendBufferSize=100000;ReceiveBufferSize=100000) -n server_name ...
クライアント接続パラメーター
PrefetchOnOpen は、ODBC と OLE DB 専用だということに注意してください。SendBufferSize と ReceiveBufferSize から改善がみられない場合には、下の例にある CommLinks=TCPIP(…) のかわりに、シンプル化のために HOST=w.x.y.z:port を使用することを推奨します。ホスト名または IPv6 アドレスをw.x.y.z のかわりに使用でき、2638 (デフォルト) を使用している場合には、:port は必要ありません。
"ServerName=server_name; Compression=Yes; CommLinks=TCPIP(Host=w.x.y.z:port; DoBroadcast=NONE; SendBufferSize=100000; ReceiveBufferSize=100000); PrefetchOnOpen=Yes; LazyClose=Yes; ..."
注意
ここにリストされているオプションは、通信関連のものです。全環境においてサーバーをより効率的に動作させるにあたり検討する必要のあるオプションが他にもあります (例えば、キャッシュサイズやデータベースページサイズなど。詳細については、SQL Anywhere のマニュアルを参照してください。
LAN経由、および同様のマシンでのオペレーションのパフォーマンスインプリケーション
LAN 環境でもアプリケーションが実行される場合には、SQL Anywhere の通信オプションもそこで忘れずにテストしてください。これらのオプションもその環境に適応させる必要がある可能性があります。
このドキュメントに記載しているアプリケーションパフォーマンスチューニングの提案は全て、LANのパフォーマンスと同様の設定のマシンのパフォーマンスを通常改善、あるいは少なくとも維持することができます。
以下のSQL Anywhere のWAN のチューニングの提案ではLAN あるいは同等のマシンのオペレーションのパフォーマンスが非常に低下する可能性があります。これらはLAN のテストで、LAN経由でもパフォーマンスを上げることが確認された後にのみ、あるいはWANのクライアントのみ有効にするべきです。
- Liveness timeout を変更すると、検出されない接続の切断を長い間発生させることになります。
- メモリーとCPUの使用の増加が潜在的なスループットのゲインよりまさるところでは、通信圧縮を有効にすると、結果としてLAN におけるパフォーマンスが遅くなることがよくあります。
- プリフェッチの設定変更 (PrefetchRows、PrefetchBuffer そしてDisableMultiRowPrefetch)。 全プリフェッチの設定は、潜在的にアプリケーションと環境によってパフォーマンスを増加または減少させます。
過去のブログ記事より