Benchmarking I/O: The test rig
Work has been a little crazy lately, but I’ve still been wanting to do some I/O performance testing since we’re due to get some new servers in soon and I wanted to be able to compare apples with oranges. So this past weekend I decided to combine something I’ve been meaning to learn more about (PowerShell) and some SQL scripts based on those described by Paul Randal (blog | twitter ) in his post about testing Fusion-IO ioDrive Duo SSD’s .
Just to be clear before going any further, I’ve tested these scripts in small scale (i.e. on my laptop) and they worked well, but I’m happy to have any bugs and/or crappy code pointed out to me in the comments. :-) Thanks.
So, because I tend to over-engineer things, I laid out my requirements early on to prevent any self-induced scope-creep. These were:
-
It must accept parameters for anything the user would need to change
-
It must create the test database and any other required objects
-
It should automatically re-run the test the specified number of times (to allow averaging of results)
-
It should automatically record test results without requiring me to intervene between test runs
-
If possible, it should be able to automatically change test configurations (e.g. number of database data files) and re-run the tests without intervention
The jury’s still out on how many of these goals I achieved, but let’s dive in anyway. I’m not going to post all the code inline – otherwise it’ll just get too bulky. Instead I’ll highlight the more important bits, and the full scripts are available at the end of this post.
The PowerShell script basically controls the flow of the test process, but there are 5 SQL scripts which do the actual heavy lifting. The first 2 files create the database (with the specified number of data files, and of the specified size), as well as a few tables used in the test:
--------------------------------------------------------------------------
-- Create test database
--------------------------------------------------------------------------
SET @cmd = N'
IF DB_ID(''' + @db_name + ''') IS NOT NULL DROP DATABASE [' + @db_name + '];
CREATE DATABASE [' + @db_name + '] ON PRIMARY (
NAME = N''' + @db_name + ''',
SIZE = ' + CAST(@data_file_size_mb AS NVARCHAR) + 'MB ,
FILEGROWTH = 250MB,
FILENAME = N''' + @db_data_path + @db_name + '.mdf'')';
-- Add additional files
WHILE @i < $(NoOfDataFiles)
BEGIN
SET @cmd = @cmd + CHAR(10) + ',(NAME = N''' + @db_name + CAST(@i AS NVARCHAR)
+ ''', SIZE = ' + CAST(@data_file_size_mb AS NVARCHAR) + 'MB , FILEGROWTH = 250MB, FILENAME = N'''
+ @db_data_path + @db_name + CAST(@i AS NVARCHAR) + '.ndf'')';
SET @i = @i + 1;
END
SET @cmd = @cmd + CHAR(10) + ' LOG ON (NAME = N''' + @db_name + '_log'', SIZE = '
+ CAST(CASE WHEN $(TargetDbSizeGB) <= 1 THEN 128 WHEN $(TargetDbSizeGB) > 1 AND $(TargetDbSizeGB) <= 5
THEN 512 WHEN $(TargetDbSizeGB) > 5 AND $(TargetDbSizeGB) <= 10 THEN 2048 ELSE 4096 END AS NVARCHAR)
+ 'MB, FILEGROWTH = 100MB, FILENAME = N''' + @db_log_path + @db_name + '_log.ldf''); ALTER DATABASE ['
+ @db_name + '] SET RECOVERY SIMPLE;';
EXEC sp_executesql @cmd, N'';
I could have achieved this by using plain old batch scripting and “FOR %%i IN… DO START sqlcmd….” magic, but I wouldn’t allow myself to use anything but PowerShell for the flow control. After some time spent searching around I found Start-Job which allowed me to launch a given number of instances of SQLCMD and then wait for them to all finish by calling Wait-Job (obviously!). 😉
######################################################################
# Kick off the specified number of insert processes ($NoOfInsertProcesses)
######################################################################
$sj = { param($SQLInstanceName, $TestDBName, $InsertRowsPath, $TargetDbSizeGB, $ApproxRowByteSize, $NoOfInsertProcesses) sqlcmd -S "$SQLInstanceName" -d "$TestDBName" -i "$InsertRowsPath" -v TargetDbSizeGB=$TargetDbSizeGB ApproxRowByteSize=$ApproxRowByteSize NoOfInsertProcesses=$NoOfInsertProcesses } $(for ($x=1; $x -le $NoOfInsertProcesses; $x++){ Start-Job $sj -ArgumentList $SQLInstanceName, $TestDBName, $InsertRowsPath, $TargetDbSizeGB, $ApproxRowByteSize, $NoOfInsertProcesses } ) | Wait-Job #Wait for all insert processes to finish before continuing
Okay – please feel free to grab the scripts below, tear them apart, point and laugh, etc (although, if you point and laugh, please at least tell me why so I can learn from it). As I said before, I haven’t run them on anything apart from my laptop (which I think would’ve taken several years to perform the full-blown test), so I’d be interested to hear of your experiences.
IOTestRun.ps1 — The main PowerShell controller script
##############################################################################
# Change these variable values to alter test rig behaviour/configuration
##############################################################################
# Files & Path variables
$TestScriptBaseUrl = "C:\temp\IOTestScripts" ## The path to any other required files
$OutputWaitStats = "out_waitstats.rpt" ## The filename for the waitstats results file
$OutputAggResults = "out_final_results.rpt" ## The filename for the final aggregated results file
$1_create_database = "_1_create_database.sql" ## Name of the SQL file to create the database
$2_create_tables = "_2_create_tables.sql" ## Name of the SQL file to create the tables
$3_insert_rows = "_3_insert_rows.sql" ## Name of the SQL file that performs the inserts
$4_get_waitstats = "_4_get_waitstats.sql" ## Name of the SQL file to create the tables
$5_aggregate_results = "_5_aggregate_results.sql" ## Name of the SQL file that performs the inserts
# Database creation script variables
$TestDBName = "IOTestDB" ## The name of the database you want to create & use for the testing
$NoOfDataFiles = 1,4,8,16,32 ## the no. of equally sized data files to create (this is an array of 1 or more values)
$TargetDbSizeGB = 10 ## e.g. 20 = 20GB target database size
$DbDataPath = "`"c:\temp`"" ## path for database data file(s)
$DbLogPath = "`"c:\temp`"" ## path for database log file
# Test run variables
$SQLInstanceName = ".\sql2008" ## The instance name you want to run the tests against
$NoOfInsertProcesses = 16 ## number of threads to spawn to insert rows
$NoOfTestRuns = 3 ## the number of times to run each test
$ApproxRowByteSize = 268 ## the approximate size of each row (in bytes)
# You shouldn't need to change the variables below
$TestScriptBaseUrlCmd = "`"$TestScriptBaseUrl`""
$CreateDBPath = Join-Path -Path $TestScriptBaseUrl -ChildPath $1_create_database
$CreateTablesPath = Join-Path -Path $TestScriptBaseUrl -ChildPath $2_create_tables
$InsertRowsPath = Join-Path -Path $TestScriptBaseUrl -ChildPath $3_insert_rows
$GetWaitStatsPath = Join-Path -Path $TestScriptBaseUrl -ChildPath $4_get_waitstats
$AggregateResultsPath = Join-Path -Path $TestScriptBaseUrl -ChildPath $5_aggregate_results
$OutputWaitStatsPath = Join-Path -Path $TestScriptBaseUrl -ChildPath $OutputWaitStats
$OutputAggResultsPath = Join-Path -Path $TestScriptBaseUrl -ChildPath $OutputAggResults
$TestId = 1
# Empty aggregate results table
sqlcmd -S "$SQLInstanceName" -d "master" -Q "IF OBJECT_ID('[tempdb].[dbo].[IOTestDB_Results]') IS NOT NULL TRUNCATE TABLE [tempdb].[dbo].[IOTestDB_Results];"
##############################################################################
# Run the test for each value specified in the $NoOfDataFiles array
##############################################################################
foreach ($dbfiles in $NoOfDataFiles) {
# Create the test database
sqlcmd -S "$SQLInstanceName" -d "master" -i "$CreateDBPath" -v TestDBName=$TestDBName NoOfDataFiles=$dbfiles TargetDbSizeGB=$TargetDbSizeGB DbDataPath=$DbDataPath DbLogPath=$DbLogPath
# Create the test tables
sqlcmd -S "$SQLInstanceName" -d "$TestDBName" -i "$CreateTablesPath"
# Reset the dm_os_wait_stats DMV
sqlcmd -S "$SQLInstanceName" -d "$TestDBName" -Q "DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);"
##########################################################################
# Run the test the specified number of times ($NoOfTestRuns)
##########################################################################
for ($i=1; $i -le $NoOfTestRuns; $i++){
# Truncate the target table
sqlcmd -S "$SQLInstanceName" -d "$TestDBName" -Q "TRUNCATE TABLE [MyBigTable];"
######################################################################
# Kick off the specified number of insert processes ($NoOfInsertProcesses)
######################################################################
$sj = {
param($SQLInstanceName, $TestDBName, $InsertRowsPath, $TargetDbSizeGB, $ApproxRowByteSize, $NoOfInsertProcesses)
sqlcmd -S "$SQLInstanceName" -d "$TestDBName" -i "$InsertRowsPath" -v TargetDbSizeGB=$TargetDbSizeGB ApproxRowByteSize=$ApproxRowByteSize NoOfInsertProcesses=$NoOfInsertProcesses
}
$(for ($x=1; $x -le $NoOfInsertProcesses; $x++){
Start-Job $sj -ArgumentList $SQLInstanceName, $TestDBName, $InsertRowsPath, $TargetDbSizeGB, $ApproxRowByteSize, $NoOfInsertProcesses
}
) | Wait-Job #Wait for all insert processes to finish before continuing
}
##########################################################################
# Collect Wait Stats
sqlcmd -S "$SQLInstanceName" -d "$TestDBName" -i "$GetWaitStatsPath" -o "$OutputWaitStatsPath"
# Collect final aggregated results
sqlcmd -S "$SQLInstanceName" -d "$TestDBName" -i "$AggregateResultsPath" -v TestID=$TestId TargetDbSizeGB=$TargetDbSizeGB ApproxRowByteSize=$ApproxRowByteSize
$TestId++
}
##############################################################################
# Output final aggregated results
sqlcmd -S "$SQLInstanceName" -d "$TestDBName" -Q "SET NOCOUNT ON; SELECT * FROM [tempdb].[dbo].[IOTestDB_Results];" -o "$OutputAggResultsPath"
_1_create_database.sql — Creates the test database with the specified number of data files
DECLARE @cmd NVARCHAR(4000),
@db_name NVARCHAR(200),
@db_data_path NVARCHAR(300),
@db_log_path NVARCHAR(300),
@data_file_size_mb INT,
@i INT
/* SQLCMD variables passed in */
-- $(TestDBName)
-- $(NoOfDataFiles)
-- $(TargetDbSizeGB)
-- $(DbDataPath)
-- $(DbLogPath)
-- You should (usually) leave these variables alone...
SET @db_name = REPLACE(REPLACE('$(TestDBName)', ' ', '_'), '-', '_');
SET @data_file_size_mb = ($(TargetDbSizeGB) * 1024) / $(NoOfDataFiles);
SET @db_data_path = REPLACE(RTRIM(LTRIM('$(DbDataPath)')) + '\', '\\', '\');
SET @db_log_path = REPLACE(RTRIM(LTRIM('$(DbLogPath)')) + '\', '\\', '\');
SET @i = 1;
--------------------------------------------------------------------------
-- Create test database
--------------------------------------------------------------------------
SET @cmd = N'IF DB_ID(''' + @db_name + ''') IS NOT NULL DROP DATABASE [' + @db_name + '];
CREATE DATABASE [' + @db_name + '] ON PRIMARY
(NAME = N''' + @db_name + ''', SIZE = ' + CAST(@data_file_size_mb AS NVARCHAR) + 'MB , FILEGROWTH = 250MB, FILENAME = N'''
+ @db_data_path + @db_name + '.mdf'')';
-- Add additional files
WHILE @i < $(NoOfDataFiles) BEGIN
SET @cmd = @cmd + CHAR(10) + ',(NAME = N''' + @db_name + CAST(@i AS NVARCHAR) + ''', SIZE = '
+ CAST(@data_file_size_mb AS NVARCHAR) + 'MB , FILEGROWTH = 250MB, FILENAME = N''' + @db_data_path
+ @db_name + CAST(@i AS NVARCHAR) + '.ndf'')';
SET @i = @i + 1;
END
SET @cmd = @cmd + CHAR(10) + ' LOG ON (NAME = N''' + @db_name + '_log'', SIZE = '
+ CAST(CASE
WHEN $(TargetDbSizeGB) <= 1 THEN 128
WHEN $(TargetDbSizeGB) > 1 AND $(TargetDbSizeGB) <= 5 THEN 512
WHEN $(TargetDbSizeGB) > 5 AND $(TargetDbSizeGB) <= 10 THEN 2048
ELSE 4096 END AS NVARCHAR)
+ 'MB, FILEGROWTH = 100MB, FILENAME = N''' + @db_log_path + @db_name + '_log.ldf'');
ALTER DATABASE [' + @db_name + '] SET RECOVERY SIMPLE;';
EXEC sp_executesql @cmd, N'';
_2_create_tables.sql — Creates the test tables
IF OBJECT_ID('MyBigTable') IS NOT NULL DROP TABLE MyBigTable;
IF OBJECT_ID('Results') IS NOT NULL DROP TABLE Results;
CREATE TABLE MyBigTable (
c1 UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT(NEWID()) PRIMARY KEY,
c2 DATETIME DEFAULT(GETDATE()),
c3 CHAR (111) DEFAULT('a'),
c4 INT DEFAULT 1,
c5 INT DEFAULT 2,
c6 BIGINT DEFAULT(42)
);
CREATE TABLE Results (duration INT);
-- This table will hold aggregate results from each test run.
-- Because its in the tempdb though, you'll lose any contents if you restart the SQL service.
IF OBJECT_ID('tempdb.dbo.IOTestDB_Results') IS NULL
CREATE TABLE tempdb.dbo.IOTestDB_Results (
test_date DATETIME,
test_id INT,
db_size_gb DECIMAL(10,2),
total_rows BIGINT,
min_dur INT,
max_dur INT,
avg_dur INT,
stdev_dur INT,
avg_dur_m FLOAT
);
_3_insert_rows.sql — Performs the bulk inserts
SET NOCOUNT ON;
DECLARE @approx_row_byte_size INT,
@no_of_rows_to_insert_total BIGINT,
@no_of_rows_to_insert_per BIGINT,
@target_db_size_mb INT,
@target_db_size_gb FLOAT,
@no_of_insert_processes INT,
@round_point INT,
@counter BIGINT,
@start DATETIME,
@end DATETIME;
SET @target_db_size_gb = $(TargetDbSizeGB);
SET @approx_row_byte_size = $(ApproxRowByteSize);
SET @no_of_insert_processes = $(NoOfInsertProcesses);
-- You should (usually) leave these variables alone...
SET @target_db_size_mb = CAST(@target_db_size_gb * 1024 AS INT);
SET @no_of_rows_to_insert_total = ((CAST(@target_db_size_mb AS BIGINT) * 1024 * 1024) / @approx_row_byte_size);
SET @round_point = ((LEN(CAST(@no_of_rows_to_insert_total AS VARCHAR(20))) - 1) * -1);
SET @no_of_rows_to_insert_total = ROUND(@no_of_rows_to_insert_total, @round_point);
SET @no_of_rows_to_insert_per = (@no_of_rows_to_insert_total / @no_of_insert_processes);
SELECT @counter = 0, @start = GETDATE();
WHILE (@counter < @no_of_rows_to_insert_per)
BEGIN
IF @counter % 1000 = 0 BEGIN TRAN;
INSERT INTO MyBigTable DEFAULT VALUES;
SET @counter = @counter + 1;
IF @counter % 1000 = 999 COMMIT TRAN;
END;
SET @end = GETDATE();
INSERT INTO Results (duration) VALUES (CAST(DATEDIFF(SS, @start, @end) AS INT));
_4_get_waitstats.sql — Collects wait statistics
SET NOCOUNT ON;
WITH Waits AS
(SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
)
SELECT
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
_5_aggregate_results.sql — Aggregates results from all test runs
SET NOCOUNT ON;
DECLARE @approx_row_byte_size INT,
@no_of_rows_to_insert_total BIGINT,
@target_db_size_mb INT,
@target_db_size_gb FLOAT,
@round_point INT,
@test_id INT;
SET @test_id = $(TestID);
SET @target_db_size_gb = $(TargetDbSizeGB);
SET @approx_row_byte_size = $(ApproxRowByteSize);
SET @target_db_size_mb = CAST(@target_db_size_gb * 1024 AS INT);
SET @no_of_rows_to_insert_total = ((CAST(@target_db_size_mb AS BIGINT) * 1024 * 1024) / @approx_row_byte_size);
SET @round_point = ((LEN(CAST(@no_of_rows_to_insert_total AS VARCHAR(20))) - 1) * -1);
SET @no_of_rows_to_insert_total = ROUND(@no_of_rows_to_insert_total, @round_point);
INSERT INTO tempdb.dbo.IOTestDB_Results
SELECT GETDATE(),
@test_id,
@target_db_size_gb,
@no_of_rows_to_insert_total,
MIN(duration),
MAX(duration),
AVG(duration),
ISNULL(STDEV(duration), 0),
ROUND((CAST(AVG(duration) AS FLOAT) / CAST(@no_of_rows_to_insert_total AS FLOAT)) * 1000000, 2)
FROM dbo.Results;
In the coming weeks I’m hoping to benchmark several of our existing servers (sitting on top of various storage platforms, such as SAN – both solid state & fibre channel, local disk, NAS, and RAM disks).
Cheers,
DB Dave