SQL Server Performance Forum – Threads Archive

Weak RAID performance…need ideas….(long)

Atleast I think I have bad RAID performance. Back when I started this current job, performance on the SQL server seemed really sad.
This is a IBM Xseries 255, 4 x Xeon 2.0 Ghz, 4 gigs of RAM.
It had 4 banks of Drives.
C,D,E Drive – 2 x 36 Gig 15 K – RAID 0 – oeprating system (don’t ask why so many partions, I did not set it up)
f Drive – 3 x 36 Gig 15 K – Raid 5e – programs
G Drive – 6 x 36 Gig 15 K – Raid 1e0 – LDF’s
H Drive – 13 x 36 Gig 15 K – Raid 5e – MDf’s H drive was on a ServeRAID 4H card
all other drives on a ServeRAID 4mx The dirve I was most concerned with was the H drive, it was doing
60 MB/s Read and 6.5 MB/s Write….that was the max we saw so, after much talking I got a new ServeRAID 6M card for the H: drives
performance jumped to 101 MB/s read 59 MB/s Write
much better, jobs actually almost completed over night, which is where the heavy processing is done. I then was able to add a 2nd RAID card another 6M for the G: drive
this drive went from
42.9 MB/s Read to 66 MB/s
35 MB/s write to 66 MB/s
the H: drive remained about the same but the overall system seemed a bit more stable.
then, I basically said that we still can not push the data through, the CPU’s sit at 40% usage overnight. I don’t think this is a memory issue since I tested the drives with HD Tach, which might not be the best, but it does say about the same MB/s for the drives. So, I was able to buy more drives, another rack of drives. I changed everthing on the data side to RAID 10.
We now have
G: 4 x 36 Gig 15K Raid 10 – LDF’s
h: 12 x 72 Gig 15K Raid 10 – MDF’s, tempdb.LDF
n: 8 x 36 gig 15K RAID 10 – NDF’s – indexes
t: 6 x 36 Gig 15K Raid 10 – Tempdb.mdf The only drive that hits anywhere close to max is the H: drive,
110 MB/s Read, 30 MB/s Write, and this is with RAID 10.
HD tach shows about the same speed. I am using 3 IBM 6M cards, one for T: one for H: and one for G: and N:
The cpu’s are still topping out at 40 % for the most part.
The Bus on the server should be able to handle 533 MB/s according to IBM
I have upgraded every firmware and driver that is possible and IBM is of no help
Possible changes I might make to the system
move the System drives off the 4mx card to one of the 6m cards and ditch the 6m card.
That would leave 3 RAID cards and one SCSI card for the tape drive.
put the SCSI card and one 6m in slots 2 and 3
one 6m in slot 4
one 6m in slot 6 IBM claims that there are 4 busses for PCI, slot 1
Slot 2 and 3
slot 4 and 5
slot 6 and 7
Redo the install of the operating system (win 2K advanced server and SQl enterprise) I can not ask more more memory at this time, just got the drives and I said this would help performance.
Anyone out there with any good numbers for RAID drive setup, a plus if on IBM equipment. anyone with good ideas out there?
Disk queue lengths on the H drive will hit about 150 and Avg. Disk sec/Transfer 177.93 ms max

it depends what you are actually doing, both in SQL operations and disk ops,
i am not certain if you disk ops are sequential, if so, they are still low,
12 disk RAID 10 should do 400MB/sec+ sequential, however, a SQL Server table scan may only generate 200-300MB/sec
other ops will be slower.
a bulk insert may only generate 100MB/sec write

the major push of data processing happens overnight between 12:00 and 8:00.
A few large data files are loaded….then processed and them move to their tables they need to belong in. Even during the Loading of these tables, write speeds are very slow.
Right now, I am looking at removing the 4mx raid card and moving those drives to one of the existing 6M cards. This is something I don’t wish to do mainly for fear of loosing all the system info and having to do a reinstall.
If I do a SQL statement that does a Table scan, it never will hit over 100 MB/s on the
12 drive Raid 10 Does anyone know a good Util to test the drive speed?
Or better yet, does anyone know a Linux cd like Knoppix that would boot up and allow drive speed testing using ServeRAID drivers for Linux? I have asked IBM but it seems like a person shrugging the shoulders on this one. It would atleast let me know if the problem is hardware or software.

my recommendation is to use IOmeter, originally developed by Intel, now public domain. Source code is available.
http://sourceforge.net/projects/iometer
www.iometer.org there are other disk test tools, but i will try to publish a document sometime soon use IOMeter, then perhaps some SQL scripts for generating specific database loads.

Joe Thanks for the external links about the IOmeter. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>