May 2, 2007 at 6:19 am
Hi,
I need to benchmark the i/o performance on a new server we have built and also try various disk configurations and see which is best. I am also hoping to use this to convince our storage guys that we should use raid 10 for the transaction log drive rather than raid 5 and compare the new server against our old server.
I want to use SQLIO for this but the technical jargon in the description of how to use it is giving me a headache, I also have to do this by tomorrow.
Can anyone give me tips on using and setting up this tool. The SAN drive on the new server has 96GB of cache so does this mean I need a 200GB + test file and how do I set that up?
any help gratefully received
---------------------------------------------------------------------
May 2, 2007 at 9:34 am
Hi,
expanding on the above it is the testile that I am not sure about (mostly). Do I just specify which path to put the testfile on and SQLio creates and expands it until it reaches the size I specified. What if it does not reach this size within the runtime I specify? Or fills up the drive?
---------------------------------------------------------------------
May 7, 2007 at 9:40 am
ok I have sussed it out. sqlio does create the file and expand it to size specified. A problem for me as this is the first server connected to SAN so I probaly have the whole 95GB cache assigned to litle ol' me. will just have to take that into account in results.
---------------------------------------------------------------------
May 8, 2007 at 6:16 am
sqlio won't really do much other than show figures which may or may not relate to sql server io, you need to know what your projected io patterns are to make any real sense of such tools.
You should always use raid 1 for tran logs as they are mostly write, a raid 5 puts a 75% degradation on disk writes so is totally inapproiate, in fact raid 5 is really no use for any database.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 8, 2007 at 12:30 pm
Colin is correct. You should be using RAID 1 for tran logs. See item 5 in the link:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
May 9, 2007 at 3:50 am
guys, thanks for the replies. I have a good idea of the i/o patterns from the current live server and am configuring sqlio using these figures. When we have an idea of the max throughput I will use sqliosim to give us a good feel for what we will get i/o wise with SQL with the configuration that gives best throughput.
I know we want raid 1 at least and preferably raid 0+1 for the logs and that is the whole point behind this exercise. The storage guys say these new servers will be much faster anyway and the business is looking to cut costs so why spend the money and recut the SAN unless I can prove the performance from the raid 5 SAN is worse than the current live server.
A sign of the current financial climate I am afraid.
---------------------------------------------------------------------
June 4, 2007 at 1:54 am
Hello George and all,
I have been performing the same kind of tests for the limits of the configuration we have. I would really like to know your methodology for this in detail as this is my first time t o do so. I would appreciate any help. SQLIO and SQLIOSim are fairly easy tools to use but the problem is to devise a reasonable set of test cases and interpreting the results.
I have run hundreds of SQLIO tests in a batch? For
- each logical drive on my SAN
- for read/write
- for different block sizes
I have run the tests for 360 secs. with 30 sec sleep between each run.
My specific questions are:
What other parameters would you advise me to change during the tests?
How do you determine your I/O profile?
How do you push your limits by SQLIO until a system break?
How do you reflect the results acquired by running SQLIO tests into SQLIOSim?
Any best practices on using SQLIO and SQLIOSim are wellcome.
Best regards,
Bora Bozgeyik
June 5, 2007 at 6:56 am
Bora,
I got my i/o profile by running system monitor using all the physical disk counters. I ran it for a good long time to capture all periods of usage and give me good averages/peaks. the main info the counters gave me that was useful was the avg/ bytes per transfer and ratios of reads to writes, I could then use these to calculate the sort of IO I needed to test. I would say you should include 4K writes on you tran log drive and 64K reads/writes on your data drive. Other parameters you should leave as is apart from no. of threads which you should match to physical no of processors and the size of your test files will depend on how much disk cache will be available to you. I found the size of the test file can definitely affect the results.
SQLIO is not designed to break the system, just returm maximum throughput values, so I found it useful as a tool to compare different configurations. SQLioSim however is designed to break the system by running a load designed to emulate a heavy SQL type io profile. Just tun this tool as is out of the box and see if the drives cope. Not coping means high wait times for i/o (> 15 secs). All you need to do with this is ensure database files SQLioSim creates are placed as they would be for your system.
---------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply