SQL Anywhere 11 のテンポラリ・ファイルを管理する方法

このドキュメントでは、SQL Anywhere のテンポラリ・ファイルを扱う際に役立つ情報を提供します。SQLAnywhere 11 向けに記載されていますが、このドキュメントの内容は SQL Anywhere 9 および 10 にも当てはまります。

背景情報

この例では、SQL Anywhere Ver.11 で全文検索をセットアップする方法を示し、検索で新しいCONTAINS 句がどのように使用されるかについて説明します。

テンポラリ・ファイル (sqla####.tmp) は、SQL Anywhere データベース・サーバによって起動時に作成され、シャットダウン時に削除されるファイルです。その名前が示すように、データベース・サーバの実行中に、一時的な情報を保持する目的で使用されます。セッション間で維持する必要のある情報は、テンポラリ・ファイルでは保持されません。sort、distinct、union といった操作の実行時に、十分な量のキャッシュを確保できない場合、データベース・サーバによって使用されます。

テンポラリ・ファイルの格納場所は、TMP、TMPDIR、または TEMP 環境変数を使用するか、-dt データベース・サーバ・オプションを使用して指定します (たとえば、dbeng11 -dt “.” という指定では、現在のディレクトリにテンポラリ・ファイルが作成されます)。テンポラリ・ファイルの格納場所によっては、パフォーマンスに影響を及ぼす場合があります。データベース・サーバによるテンポラリ・ファイルの使用頻度が高い場合、I/O の同時実行性を向上させるため、独立した物理ドライブにテンポラリ・ファイルを配置することをおすすめします。

他の DBMS システム (Adaptive Server Enterprise や SQL Server) とは異なり、SQL Anywhere にはテンポラリ・データベース (tempdb) は用意されていません。作成されたテンポラリ・オブジェクトはテンポラリ・ファイルに格納されますが、tempdb プレフィクスを付加して明示的にアクセスする必要はありません。

予期しないシステム・クラッシュやデータベース・サーバの停止が発生した場合でも、手動で削除されるか、別のデータベース・サーバが起動して既存のテンポラリ・ファイルがすべて削除されるまで、テンポラリ・ファイルは保持され続けます

テンポラリ・ファイルに関連する SQL のヒント

• テンポラリ・ファイルの作成先ディレクトリを確認する場合：
  SELECT PROPERTY(‘TempDir’);
• テンポラリ・ファイルの名前を判別する場合：
  SELECT Value from sa_db_properties() WHERE propname = ‘TempFileName';
• テンポラリ・ファイルの現在のサイズ (バイト数) を取得する場合：
  SELECT DB_EXTENDED_PROPERTY( ‘FileSize’, ‘temporary’) * PROPERTY(‘PageSize’);

テンポラリ・ファイルに関連する問題の可能性

生成された要求によっては、データベース・サーバで短時間に大量のテンポラリ領域の割り当てが発生する可能性があります。多くのケースでは、要求された結果を得るために、このような処理が必要になります。ただし、ファイル・システムを占有するようなランナウェイ・クエリをユーザが作成する場合もあります。この種のクエリは、システムのハングや障害など、さまざまな問題の原因になることがあります。

単純な例として、JOIN 条件を付加せずに 3 つのテーブルに対して SELECTを実行する SQL 文を次に示します。次の例の SQL 文を実行すると、実行時間が数分～ 数時間に及ぶことがあり、数ギガバイトものテンポラリ・ファイル領域が消費されます。

SELECT top 1 *
FROM SYSDEPENDENCY,
SYSOBJECT,
SYSPROCPARM
ORDER BY 1, 2, 3, 4, 5;

いったんテンポラリ・ファイルのサイズが増加してしまうと、サイズを削減するには、データベース・サーバを再起動するしか方法がありません。したがって、このような状況が発生しないように対策を実装しておくことをおすすめします。

予期しないサイズの増加を防ぐ方法

SQL Anywhere には、テンポラリ・ファイルの使用領域を制御する 2 種類のデータベース・オプションが用意されています。TEMP_SPACE_LIMIT_CHECKオプションは領域の制限値を順守するようデータベース・サーバに強制し、MAX_TEMP_SPACE オプションはサイズ単位での領域の制限値の設定を可能にします。

例:

SET OPTION PUBLIC.TEMP_SPACE_LIMIT_CHECK = ‘On';
SET OPTION PUBLIC.MAX_TEMP_SPACE = ‘1G'; — sets the limit to 1GB

要求により、MAX_TEMP_SPACE で指定された制限値を超過するほどテンポラリ・ファイルのサイズが増加した場合、その要求は停止され、次のエラーが生成されます。

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Temporary space limit exceeded
SQLCODE=-1000, ODBC 3 State=”HY000″

(SQL 文の実行結果の読み取り時にエラーが発生しました。表示された実行結果は不正確または不完全である可能性があります。テンポラリ領域の制限値を超過しました。
SQLCODE=-1000、ODBC 3 State=”HY000″)

この種のエラーに対処できるよう、アプリケーションを設計する必要があります。

(マシン固有の) テンポラリ・ファイルのサイズに対する制限値を明確に規定するため、問題が生じうる状況を管理者に通知するよう、監視ツールを設定しておくことをおすすめします。これにより、管理者側で状況を分析して、ファイル・システムの占有を防止し、システム障害の発生を回避するよう、必要な対策を講じることができるようになります。

テンポラリ・ファイル領域の使用状況の監視

データベース・オプションを使用したテンポラリ・ファイルの管理に加えて、データベース・サーバを監視することで、領域関連で起こり得る問題に事前に対処できます。

SQL Anywhere では、システム・イベントをサポートしています。システム・イベントは、特定の条件を満たした場合にデータベース・サーバによって開始されます。詳細については、次のオンライン・マニュアルをご参照ください。

SQL Anywhere サーバ – データベース管理 ≫ データベースの保守 ≫ スケジュールとイベントの使用によるタスクの自動化 ≫ システム・イベントの概要

テンポラリ・ファイルを保持しているデバイスの空きディスク領域の量を確認するには、TempDiskSpace を使用します。次の例では、TempDiskSpace イベントを使用して、テンポラリ・ファイル用に使用できる空きディスク領域の量が 50% を下回るたびに、データベース・サーバのメッセージ・ログにメッセージを送信しています。

CREATE EVENT “evt_monitor_temp_space” TYPE “TempDiskSpace”
WHERE EVENT_CONDITION(‘TempFreePercent’) < 50
HANDLER
BEGIN
— this event will write a message to console log
— when there is less than 50% of free space on the device
— where tempfile is located
declare tf varchar(128);
declare free varchar(128);
declare crn varchar(128);
declare prc varchar(3);

IF event_parameter( ‘NumActive’ ) <= 1 THEN
— find out temp file name
select convert(varchar(128),value) into tf From sa_db_properties()
where propname = ‘TempFileName';;
— find out current size in Mb
select convert(varchar(128),(convert(bigint,DB_EXTENDED_PROPERTY(
‘FileSize’,
‘temporary’))*convert(bigint,PROPERTY(‘PageSize’))/1024/1024))
into crn from dummy;
— find out number of available free space
select convert(varchar(128),convert(int,free_space/1024/1024)) into
free From sa_disk_free_space() where dbspace_name = ‘temporary';
— calculate % of free space
if free <> 0 then select
convert(varchar(4),convert(integer,convert(float,crn)/convert(float,free)*100)) + ‘%’ into prc from dummy end if;
— write space information to console log
MESSAGE prc + ‘ (‘ + crn + ‘ Mb). of free space (‘ + free + ‘ Mb)’ + ‘
have been used by ‘ + tf;
END IF;

END;

このイベントを修正することで、任意の SQL 文を実行したり、xp_cmdshellなどの外部呼び出しを使用して電子メールの送信や別の処理を実行することができます。

このイベントによって生成されるメッセージの例を次に示します。

0% (214 Mb). of free space (22165 Mb) have been used by C:\DOCUME~1\lchmura\LOCALS~1\Temp\asat0001.tmp
2% (558 Mb). of free space (21820 Mb) have been used by C:\DOCUME~1\lchmura\LOCALS~1\Temp\asat0001.tmp
Cache size adjusted to 1362112K
4% (974 Mb). of free space (21404 Mb) have been used by C:\DOCUME~1\lchmura\LOCALS~1\Temp\asat0001.tmp
4% (1038 Mb). of free space (21340 Mb) have been used by C:\DOCUME~1\lchmura\LOCALS~1\Temp\asat0001.tmp

ランナウェイ要求を特定する方法

SQL Anywhere には、データベースと接続ごとに一連の動的なプロパティが用意されており、ランナウェイ要求を特定するための手段として利用できます。
このプロパティ値を経時分析することで、接続ごとのテンポラリ・ファイルの使用状況を確認したり、ランナウェイ接続を簡単に特定したりできるようになります。

次に示すのは、sa_tempfile_monitor テーブル (存在しない場合) を作成し、データベースと接続に関連するプロパティを格納する SQL 文の例です。同じ文を前述の TempDiskSpace イベントに追加することで、手動で実行する代わりに、ディスク上の空きディスク領域の容量が 50% を下回るたびに自動的に実行するようにも設定できます。

この例に示す文を実行するには、-zl および -zp オプションを使用してデータベース・サーバを起動する必要があります。各オプションにより、データベース・サーバで最新の SQL 準備文が取得されます。データベース・サーバの起動時に各オプションを指定するか (例. dbsrv11 … -zl .zp … )、データベース・サーバがすでに実行中の場合は、次の SQL 文を使用して各オプションを有効にします。

CALL sa_server_option(‘RememberLastStatement’,’ON’);
CALL sa_server_option(‘RememberLastPlan’,’ON’);

ランナウェイ・プロセスについての必要な情報を収集するには、次の SQL 文を実行します。

if not exists (select * from sysobjects where name like
‘sa_tempfile_monitor’)
select getdate() as d,db_property(‘ExtendTempWrite’) as
ExtendTempWrite,db_property(‘TempTablePages’) as TempTablePages,
prop.Number,prop.PropNum,prop.PropName,prop.Value,
inf.Name,inf.Userid,inf.LastReqTime,inf.BlockedOn
into sa_tempfile_monitor
from sa_conn_properties() prop,sa_conn_info() inf
where prop.Number = inf.Number and
prop.PropName in
(‘TempTablePages’,’TempFilePages’,’LastStatement’,’LastPlanText’,’CurrentProcedure’,’CurrentLineNumber’)
else
insert into sa_tempfile_monitor
select
getdate(),db_property(‘ExtendTempWrite’),db_property(‘TempTablePages’),
prop.Number,prop.PropNum,prop.PropName,prop.Value,
inf.Name,inf.Userid,inf.LastReqTime,inf.BlockedOn
from sa_conn_properties() prop,sa_conn_info() inf
where prop.Number = inf.Number and
prop.PropName in
(‘TempTablePages’,’TempFilePages’,’LastStatement’,’LastPlanText’,’CurrentProcedure’,’CurrentLineNumber’)

‘TempTablePages’、’TempFilePages’ – 接続ごとのテンポラリ・ファイルの使用状況を通知 (ページ数)

LastStatement’、’LastPlanText’、’CurrentProcedure’、’CurrentLineNumber’ – その時点で実行中の SQL 要求を特定する際に役立つ情報

データの収集後、クエリを実行することで、テンポラリ・ファイルのサイズ増加の原因になっている接続と、その時点で実行されていた処理を確認できます。

例:

接続期間ごとのテンポラリ・ファイルの使用率：

select
d,ExtendTempWrite,TempTablePages,Number,UserID,
convert(Integer,convert(Bigint,Value)/TempTablePages*100) PerUsage
From sa_tempfile_monitor
where PropName = ‘TempTablePages’
order by d,number

Number は接続 ID、UserID はデータベース・ユーザをそれぞれ表します。

問題の原因になっている接続が特定されたら、次の文 (2 は接続 ID を表す)を実行し、問題発生時に実行されていた SQL 文を確認します。

SELECT * FROM sa_tempfile_monitor WHERE Number = 2;

問題の原因になっている文を確認したら、実際に修正します。

1. コマンド・プロンプトで、次のコマンドを実行し、サンプル・データベースを起動します。
   

   “C:\Program Files\SQL Anywhere 11\Bin32\dbsrv11.exe” -zl -zp -m -c 50P -n demo11 “C:\Documents and Settings\All Users\Documents\SQL Anywhere
   11\Samples\demo.db”

2. 次のコマンドを 2 回実行して、Interactive SQL ウィンドウを 2 つ開きます。
   

   “%sqlany11%\bin32\dbisql.exe” -c uid=DBA;pwd=sql;eng=demo11

3. 最初の Interactive SQL ウィンドウで、次のクエリを実行して、ランナウェイ要求をシミュレートします。
   

   SELECT top 1 *
   FROM SYSDEPENDENCY,
   SYSOBJECT,
   SYSPROCPARM
   ORDER BY 1, 2, 3, 4, 5;

4. 2 番目の Interactive SQL ウィンドウで、次の文を実行します。
   

   SELECT * FROM sa_conn_properties() WHERE PropName LIKE ‘TempTablePages’
   ORDER BY CONVERT(integer,value) DESC
   

   このクエリにより、テンポラリ・テーブルのページを最も多く使用していた要求の接続 ID が返されます。

5. 次のクエリを実行します。
   

   SELECT * FROM sa_conn_info() WHERE Number =
   ( SELECT TOP 1 Number FROM sa_conn_properties() WHERE PropName
   LIKE ‘TempTablePages’ ORDER BY CONVERT (integer,value) DESC)

   SELECT * FROM sa_conn_properties() WHERE Number =
   ( SELECT TOP 1 Number FROM sa_conn_properties() WHERE PropName LIKE
   ‘TempTablePages’ ORDER BY CONVERT (integer,value) DESC)

   各文では、接続で処理されている内容についての詳細が表示されます。この情報に基づいて、ランナウェイ・プロセスの原因になったコード部分を特定し、実際に修正します。

 

SAPの SAP SQLAnywhere製品ページはこちら