SQL Anywhere のテンポラリファイルを管理する方法
目的
このページでは、SQL Anywhere のテンポラリファイルを扱う際に役立つ情報を提供します。
概要
テンポラリファイル (sqla####.tmp) は、SQL Anywhere データベースサーバによって起動時に作成され、シャットダウン時に削除されるファイルです。その名前が示すように、データベースサーバの実行中に、一時的な情報を保持する目的で使用されます。セッション間で維持する必要のある情報は、テンポラリファイルでは保持されません。sort、distinct、union といった操作の実行時に、十分な量のキャッシュを確保できない場合、データベースサーバによって使用されます。
テンポラリファイルの格納場所は、TMP、TMPDIR、または TEMP 環境変数を使用するか、-dt データベースサーバオプションを使用して指定します (たとえば、dbeng16 -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 では、システムイベントをサポートしています。システムイベントは、特定の条件を満たした場合にデータベースサーバによって開始されます。詳細については、関連するドキュメントをご参照ください。
テンポラリファイルを保持しているデバイスの空きディスク領域の量を確認するには、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),(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\user_name\LOCALS~1\Temp\asat0001.tmp
2% (558 Mb). of free space (21820 Mb) have been used by C:\DOCUME~1\user_name\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\user_name\LOCALS~1\Temp\asat0001.tmp
4% (1038 Mb). of free space (21340 Mb) have been used by C:\DOCUME~1\user_name\LOCALS~1\Temp\asat0001.tmpランナウェイ要求を特定する方法
SQL Anywhere には、データベースと接続ごとに一連の動的なプロパティが用意されており、ランナウェイ要求を特定するための手段として利用できます。
このプロパティ値を経時分析することで、接続ごとのテンポラリファイルの使用状況を確認したり、ランナウェイ接続を簡単に特定したりできるようになります。
次に示すのは、sa_tempfile_monitor テーブル (存在しない場合) を作成し、データベースと接続に関連するプロパティを格納する SQL 文の例です。同じ文を前述の TempDiskSpace イベントに追加することで、手動で実行する代わりに、ディスク上の空きディスク領域の容量が 50% を下回るたびに自動的に実行するようにも設定できます。
この例に示す文を実行するには、-zl および -zp オプションを使用してデータベースサーバを起動する必要があります。各オプションにより、データベースサーバで最新の SQL 準備文が取得されます。データベースサーバの起動時に各オプションを指定するか (例. dbsrv16 … -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, numberNumber は接続 ID、UserID はデータベースユーザをそれぞれ表します。
問題の原因になっている接続が特定されたら、次の文 (2 は接続 ID を表す)を実行し、問題発生時に実行されていた SQL 文を確認します。
SELECT * FROM sa_tempfile_monitor WHERE Number = 2;問題の原因になっている文を確認したら、実際に修正します。
例:
1.コマンドプロンプトで、次のコマンドを実行し、サンプルデータベースを起動します。
"C:\Program Files\SQL Anywhere 16\Bin64\dbsrv16.exe" -zl -zp -m -c 50P
-n demo16 "C:\Documents and settings\All Users\Documents\SQL Anywhere 16\Samples\demo.db"2.次のコマンドを 2 回実行して、Interactive SQL ウィンドウを 2 つ開きます。
"C:\Program Files\SQL Anywhere 16\Bin64\dbisql.exe" -c "uid=DBA;pwd=sql;eng=demo16"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.次のクエリを実行します。
各文では、接続で処理されている内容についての詳細が表示されます。
この情報に基づいて、ランナウェイプロセスの原因になったコード部分を特定し、実際に修正します。
関連コンテンツ
関連ドキュメント
このページは、以下の英語ページの抄訳です。
How to Manage Temporary Files in SQL Anywhere