스트래스 툴 Ostress 사용하기

말그대로 DB부하 테스트 도구 입니다.

보통의 디비 설계를 하고 가상의 데이터를 넣은 다음 쿼리문을 실행하여 DB에 어느정도의

부하가 가해 지는지 테스트 하는 도구 입니다.

 

툴을 얻는 방법으로는 http://support.microsoft.com/

 

설치를 하고 나면

c:\program files\rml 폴더가 생성됩니다.

 

명령프롬프트 창에서

 

1)

ostress -Sservername -E -dpubs -Q"select * from sales" -n5 -r5 이렇게 해봅니다.

 

-S : Servername

-E : windows 인증

-d : 사용db

-Q :쿼리문

-n : 컨넥션 오픈수

-r : 반복횟수

 

2)

윈도우인증 대신 sql 인증을 사용하려면

ostress -Sservername -Usa -P1234 -dpubs -Q"select * from sales" -n5 -r5

 

-U : id

-P : 1234

 

로 합니다.

 

3)

외부파일을 통해서 쿼리를 수행하기 위해서는

ostress -Sservername -E -dpubs -ibatch.sql -n5 -r5

 

-i  :외부 파일명

 

을 사용합니다. 즉 batch.sql 파일에 있는 쿼리를 이용하여 부하 테스트를 수행하게 됩니다.

 

4)

-t : 매개변수

쿼리타임아웃을 설정하여 시뮬레이션을 수행하게 됩니다.

이 것은 사용자가 쿼리를 취소하거나 쿼리 타임아웃으로 인해 SQL서버에 고아가 된 트랜잭션이 남아 있는 경우의 시나리오 테스트를 하는데 유용합니다.

 

ostress -Sservername -E -dpubs -ibatch.sql -n10 -r5 -t1

위의 명령어는

10개의 동시 커넥션에 각 커넥션마다 5번씩 반복수행하며, 1초의 쿼리 타임아웃 옵션으로 배치파일을 수행하는 예제입니다.

 

5)

-c : 매개변수

-c 다음의 파일은 메타파일을 지정합니다.

rml폴더내에 있는 sample.ini 를 보면 ostress 수행을 위해 필요한 옵션들이 우글 우글합니다.

이중

CanclePct =10.00으로 하면 수행횟수의 10%에 대해서 타임아웃이 발생하도록 설정하게 됩니다.

 

ostress -Sservername -E -dpubs -ibatch.sql -n10 -r5 -csample.ini

 

 

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

 

다음은 sample.ini 에 있는 환경변수의 값 설명입니다.

INI File Section	Value	Description
[Connection Options]	LoginTimeout	Login timeout (seconds).  Value of 0 (default) is infinite
	QuotedIdentifier	Issues a SET QUOTED_IDENTIFIER with the specified value (default is OFF)
	AutocommitMode	Sets the SQL_AUTOCOMMIT mode of ODBC.  Sets the IMPLICIT_TRANSACTIONS option to the opposite setting of what is here (default is on)
	DisconnectPct	Randomly disconnect from the server between iterations of the specified input file or query the specified percentage of time.  A value of 0.0 (default) is never.  If the input file contains a single command, or the input is an individual query, setting this option to 100%will simulate applications that use make-break connections.
	MaxThreadErrors	Maximum number of errors per connection before exiting.  Value of 0 (default) is infinite
[Query Options]	NoSQLBindCol	Set Off to disable calls to SQLBindCol for improved performance.  All rows are still fetched, but the values for the row aren’s copied into any application variables.  Use this option when you really don't care about the results returned from the query.  Defaults to On in quiet mode, Off otherwise.
	NoResultDisplay	Doesn't print results from the query. Implicitly set ON if NoSQLBindCol is also ON.  Defaults to ON for quiet mode, OFF otherwise
	PrepareExecute	Use ODBC prepared statements (default is Off)
	ExecuteAsync	Execute the query asynchronously, allowing for cancels.  Defaults setting is Off.  In replay mode, all queries are submitted asynchronously and this setting is ignored.
	RollbackOnCancel	Issues a rollback after each cancel operation. Defaults to Off
	QueryTimeout	Sets a query timeout for each query, in seconds.  Default is 0
	QueryDelay	Interjects a delay (milliseconds) before running each query.  Default is 0
	MaxRetries	Maximum retries for deadlocks & timeouts, with 0 retrying forever.  In stress mode, default value is zero.  In replay mode, this setting is ignored an no retries are done.
	CancelPct	Percentage of time to attempt query cancellation. Must have ExecuteAsync=On.  Default to zero
	CancelDelay	Delay (milliseconds) after issuing query before canceling.  If negative, a random delay between CancelDelayMin and CancelDelay is calculated and used.  Defaults to 0
	CancelDelayMin	Minimum delay (milliseconds) after issuing query before cancelling.  Should be 0 or a positive value
	CursorType	Request the following cursor type for any non-sp_cursor* queries.  Available options are forwardonly (default), keyset, dynamic and static.
	CursorConcurrency	Request the following cursor concurrency of any non-sp_cursor* queries.  Available options are readonly (default), lockcc, optcc and optccval.
	RowFetchDelay	Delay this amount of time (milliseconds) between each request to fetch rows (i.e. SQLFetch).  If negative, a random delay between zero and ABS(RowFetchDelay) will be calculated and used.  This can be used to simulate client apps that don’t process results, or don’t process them in a timely fashion.
	BatchDisconnectPct	After submitting the request to the server, close the connection for this percentage of batches.  A negative number will calculate a random value.
[Replay Options]	Sequencing Options	Comma separated list of options to use in replay mode.  Valid options are global sequence, delta, dtc replay.
	DTC Timeout	DTC transaction timeout (seconds).  Default is 60
	DTC Machine	The server running the MSDTC service to be used as the commit coordinator for DTC transactions.  Default is local server
	Playback Coordinator	For a multi-OSTRESS replay scenario, location where ORCA will run.  Default is local server.
	DefaultPassword	The password to use for any connection that is using SQL authentication but does not have a <PWD> tag in its <CONNECT> node.  If this key is missing a password of 뱑eplay?is used as a default. A user is assumed to be using Windows authentication if the username contains a backslash (\)character.  See the section 밅redentials used for Login?for more information related to this area.
	TimeoutFactor	OSTRESS automatically uses a query timeout to cancel queries that don뭪 run in approximately the same amount of time that they did when the trace was captured.  This is used to re-synchronize the replay in cases where something may have happened during capture (such as a deadlock) that didn뭪 happen at replay.  If you are using a slower machine or otherwise know that the queries may be much slower at replay, this can be used to weight how OSTRESS calculates the timeout.  The value is a float and initially defaults to 1.1, meaning that OSTRESS allows the queries to run approximately 110 percent of the time they did at capture.  A value of 2.0 would allow the queries to run twice as long, etc.  OSTRESS automatically increases this value (up to an upper limit of about 3) when it detects a lot of timeouts occurring during the replay.
	StartSeqNum	Specifies the sequence number of the lowest event number to submit during a replay attempt.  By specifying a StartSeqNum/StopSeqNum, a "window" of events from a larger set of trace files can be replayed.  Another alternative for limiting the scope of the replay is to use the start or end time parameters when running Read80Trace.  Default is zero.
	StopSeqNum	Specifies the highest sequence number to replay.  Default is largest 64 bit integer.