Using MTSQL

The MTSQL scripting tool is useful for testing and benchmarking your applications. It can be used to write multi-threaded SQL scripts, synchronize separate threads, and capture output from each thread separately.

Like sqlline, MTSQL is automatically configured to connect with s-Server based on the information you supply during client tools installation.

Command-Line Arguments

Default connection values can be set for all the tools in the clienttools/default.conn.properties file. The properties are specified there as DRIVER, SERVER, NAME, and PASSWORD.

Each of those default values can be overwritten by passing a command-line argument to the script as follows:

Property Switch Argument Example
SERVER -u URL -u jdbc:sqlstream:sdp://example.com:5571
DRIVER -d Driver class -d com.example.jdbc.Driver
NAME -n User name n sqlstream
PASSWORD -v Password -v mypassword

How to use the MTSQL input files and language

A MTSQL script is typically divided into sections with the following overall structure. Keywords in mtsql scripts start with an @, and denote special actions to be taken by the test harness, such as repeating a sequence of SQL statements, setting a query timeout, and so on.

  • An optional @setup part, for test setup, which will include the SQL needed to create whatever schemas, tables, etc. must be in place before the multi-threaded testing begins. The setup section can be omitted entirely if it’s not needed
  • Zero or more @thread parts, one for each thread in the test
  • An optional @cleanup part

Each part is a list of SQL statements and mtsql commands.

Execution sequence

  • First, mtsql runs the @setup segment.
  • Then, it runs the @thread segments simultaneously, optionally using @sync commands to synchronize operations.
  • Afterward, it runs any @cleanup segments.
  • Finally, the results of all the parts, which have been saved in memory, are printed as the output.

Note that mtsql may exhaust memory if the results are prolix. By default it saves every result row from a query.

Placement of SQL statements

SQL statements can appear anywhere within the @setup and @thread blocks.

  • Any statement beginning with select is executed as a JDBC query.
  • Statements beginning with insert , update or delete are executed as JDBC updates. Everything else is executed as DDL.
  • Whitespace is ignored at the beginning and end of a statement.
  • Statements are delimited with semicolons, even when preceded by a keyword.
  • Keywords must be the first thing (except for whitespace) that appers on a line.
  • Double dashes indicate comments.

For example:

select * from sales.depts;                 -- good

@timeout 5000 select * from sales.depts;   -- good

@timeout 5000 select *
from sales.depts;                          -- good

@timeout 5000 select * from sales.depts    -- bad, missing semicolon

select * from sales.emps;

select * from sales.depts                  -- bad, missing semicolon

@timeout 5000 select * from sales.emps;

select * from sales.depts; @sync           -- bad, sync must be on next line

File usage

Three files are typically involved when using mtsql:

  • The test script itself, for example, unitsql/foo.mtsql)
  • The reference output file, for example, unitsql/foo.ref)
  • The test output file, for example, unitsql/foo.log)

It is conventional to use the extension .mtsql for multi-threaded test scripts. This makes it easier to distinguish them from single-threaded test scripts.

The key mtsql commands are: @sync, @prepare, @fetch and @print which sets the print mode.

The next section presents a complete list of mtsql commands and keywords.

MTSQL Keywords

Keyword Description
@close Closes the prepared statement (see @prepare). (If @close is omitted, @end would force a close, but as a “best practice”, @close should be used.)
@include Indicates external test script to be included, either at the @setup phase or later.
@disabled @enabled Controls whether execution of the test is disabled or not. If neither keyword is present, the default is @enabled . These keywords must appear at the start of the file (before @setup or @thread, or not at all.
@err Executes the given SQL (DDL only) and print the error class and message. May only appear inside of a @thread or @repeat block.
@fetch Opens a cursor and reads rows.
@lockstep @nolockstep Controls whether execution of each command in the script is synchronized. If neither keyword is present, the default is @nolockstep. These keywords must appear at the start of the file (before @setup or @thread), or not at all. @lockstep is not the same as synchronized. Instead, @lockstep is equivalent to a @sync after every line, so that all threads “march in lockstep”: all do their 1st line, then all execute their 2nd line, etc. Since this is not very flexible, so the usual is @nolockstep , when the threads meet up only at the @sync commands: all threads pass the same @sync at the same time.
@prepare <SQL>@sync@fetch [T] @close Prepares the given SQL (opens a JDBC prepared statement). Executes and fetches the results with an optional query timeout of T milliseconds, writing them into the .ref file. Closes the statement. Only the @sync or @sleep keywords may appear between these three keywords. May only appear inside of a @thread or @repeat block.
@print Sets the format for printing the output of a sql select. Parameters: If no parameter is specified, @print prints every row in the result set (this is rarely desirable). every n means print a row after fetching n rows, 2n rows, 3n rows, … total means (also) print a row at the end. time means print the elapsed real time count means print the number of rows (so far). Note: a “(timeout)” in the log file indicates that the query timed out — the elapsed time does not include the timeout so it is valid to conclude that rows were read at the rate count/time, referring to the output of the print command. Examples: @print @print every 3 @print total @print time @print count
@repeat N … @end Specifies that the given commands should be repeated N times. May only appear inside of a @thread block.
@setup … @end Defines SQL statements that will execute before the multi-threaded portion of the test is started. Optional.
@sleep T Sleeps for T milliseconds. May only appear inside of a @thread or @repeat block.
@sync Specifies that the given thread should block and wait for all other threads to reach their @sync commands. The same number of @sync keywords must appear in each thread once all loops have been unrolled. @sync can only be used with @nolockstep. May only appear inside of a @thread or @repeat block.
@thread X[,Y, …] … @end Defines one or more threads of execution and their SQL commands. Note that if multiple thread names (X, Y, …) are given, multiple threads will execute the same commands. At least one @thread block must appear in the script. If only one appears, you should probably be using the old single-threaded testing.
@timeout T Executes the given SQL with a query timeout of T milliseconds. The statement is prepared and executed, all results are fetched and written to the .ref file, and the statement is closed. May only appear inside of a @thread or @repeat block.

Setting Timeout for Fetch

When setting the fetch timeout parameter, it is important to make sure that the query does not time out before it fetches anything. This means that the fetch timeout needs to be greater than the sum of the time it takes to execute the individual statements in each thread when run simultaneously.

  • In the case of a query, it is the time it takes to get a row of data back from the query
  • If a pump is the source for the data, then the time it takes to start the pump (plus the time to get data from the query).

If the query times out before getting any data the .log file will show any empty table. The required fetch timeout value can vary depending on the speed of the machine you are running on, and/or the loads on the system at runtime.

Example MTSQL scripts

Below are two examples of MTSQL scripts. The first has two pumps and the second one has one pump.

Example 1

@setup
  @include catalog.sql
@end

@thread reader
-- @prepare prepares the query, in sync with the first pump starting up (see thread writer).  
@prepare SELECT STREAM  FLOOR("test_stream".ROWTIME TO HOUR) AS FLOOR_HOUR, COUNT(\*) as  "NumberOfRows", COL1, COL2, COL3, COL4 FROM "SCHEMA1"."test_stream"  GROUP BY FLOOR("test_stream".ROWTIME TO HOUR), COL1, COL2, COL3, COL4;

@sync
    -- The server is waiting to receive data from the pumps, once it receives data it is waiting for
    -- an hour’s worth of data before it spits out a row (see query above). The query is using a promoted
    -- rowtime (ie rowtime not equal to current time but rather the time passed in from the data).  
    -- For this reason the fetch timeout is set to one hour + time to start first pump + time to start  
   --  second pump = 360000 + 60000 + 30000 = 450000
    -- (this is =(time to start pump) + (time to emit first row))

@print total time count every 1
    @fetch 450000
    @close
  @sync
  @sync
@end

@thread writer
ALTER PUMP "Stage1" START;

  @sync
    ALTER PUMP "Stage2" START;
  @sync

  @sync
@end

@thread cleanup
  @sync
  @sync
  @sync
    ALTER PUMP "Stage1" STOP;
    ALTER PUMP "Stage2" STOP;
    DROP SCHEMA "SCHEMA1" CASCADE;
@end

Example 2

This is the same MTSQL script with only one pump:

@setup
  @include catalog.sql
@end

@thread reader
@prepare SELECT STREAM FLOOR("test_stream".ROWTIME TO HOUR) AS FLOOR_HOUR, COUNT(*) as "NumberOfRows", COL1, COL2,
-- @prepare prepares the query, in sync with the first pump starting up (see thread writer).  
 COL3, COL4 FROM "SCHEMA1"."test_stream" GROUP BY FLOOR("test_stream".ROWTIME TO HOUR), COL1, COL2, COL3, COL4;
  @sync
    -- The server is waiting to receive data from the pumps, once it receives data it is waiting for
    -- an hour’s worth of data before it spits out a row (see query above). The query is using a promoted
    -- rowtime (ie rowtime not equal to current time but rather the time passed in from the data).  
    -- For this reason the fetch timeout is set to 250000
    -- (this is =(time to start pump) + (time to emit first row))
    @print total time count every 1
    @fetch 250000
    @close
  @sync
  @sync
@end

@thread writer
  @sync
    ALTER PUMP "Stage1" START;
  @sync
  @sync
@end

@thread cleanup
  @sync
  @sync
  @sync
    ALTER PUMP "Stage1" STOP;
    DROP SCHEMA "SCHEMA1" CASCADE;
@end