1 / 85

Storage Performance for SQL Server

Storage Performance for SQL Server. Joe Chang jchang6@yahoo.com www.sql-server-performance.com/joe_chang.asp. Objectives. Understand storage components Learn how interpret performance counters How to determine if you have a bottleneck Sizing and configuring the storage system

eytan
Download Presentation

Storage Performance for SQL Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Storage Performance for SQL Server Joe Chang jchang6@yahoo.com www.sql-server-performance.com/joe_chang.asp

  2. Objectives • Understand storage components • Learn how interpret performance counters • How to determine if you have a bottleneck • Sizing and configuring the storage system • How many disks and IO channels do you need • Verify storage system performance • Does the system perform as expected • How SQL operations translate to disk ops

  3. Quiz • How much capacity do you have? • For each data and log partition • What is the RAID level for each? • How many disks and IO channels on each? • What are the performance characteristics? • Small block low queue random IOPS • Small block high queue random IOPS • Large block sequential transfer rate • Read and write for each

  4. Topics • Storage components & system • Performance counters • IO Performance Tools & Testing • IO characteristics of SQL Server operations • Configuring the storage system • File placement strategies

  5. Storage Components Storage Performance for SQL Server

  6. Storage Components • System Overview • Disk Interfaces • Disks • Disk Performance • RAID Controllers / Host Bus Adapters • PCI-X, PCI-Express

  7. System Overview Chipset (memory controller & IO bridges) connects processors, memory & IO System bus usually means processor bus Internal IO connects memory controller to IO bridges, may be proprietary PCI – connects IO adapters to IO bridge Disk interface connects disks to IO adapters

  8. Serverworks GC-LE chipset PCI-X 64-bit 0.8GB/sec@100MHz 1.0GB/sec-133MHz What is actual realizable IO bandwidth? Popular in 2-way Xeon servers with 400 & 533MHz FSB EMC Clarion CX500/700 series

  9. Intel E7520 chipset

  10. Disk Interfaces Desktop Interfaces ATA 133 MBytes/sec SATA 1.5 Gbit/sec (8b/10b) ~150MB/sec SATA-IO (formerly SATA-II) up to 3.0 Gbit/sec defined Enterprise Interfaces SCSI – LVD signaling 14-15 disks per SCSI bus U160 Full speed only on data, not command U320 More efficient protocol, full speed command FC 126 devices per loop 1 Gbit/sec 2 Gbit/sec - 4 Gbit/sec - soon SAS – point to point, with fan out, 128 devices 3.0 Gbit/sec full duplex, 6.0 Gbit/sec second generation

  11. SCSI SCSI 1 controller per bus and up to 15 disks or up to 2 controllers and up to 14 disks Shared bus, disks must arbitrate for bus Common form factor: 14 disks in 3U rack Can be 1 SCSI channel (1x14) or 2 SCSI channels (2x7)

  12. Fiber Channel One port of loop topology Dual port Arbitrated Loop, Fabric, or Point-to-point topologies Bandwidth is shared Can achieve higher utilization than shared bus

  13. SATA Differential signals - 2 wires, +/- 1 pair for transmit, 1 pair for receive

  14. SATA 1 disk per port New features in SATA Native Command Queuing Port Multiplier 1 port can connect to multiple devices Port Selector Each disk can have 2 ports

  15. Disk Drives Bare drive, no hot-plug carrier, no enclosure

  16. Drive Speed versus Capacity 65mm 84mm 95mm 7200RPM, 8ms 200, 300, 400GB BPI 763K/in 91.56Mbit/in2 10,000RPM, 5ms 73, 146, 300GB BPI 658K/in 15,000RPM, 3.6ms 36, 73, 146GB BPI 628K/in Lower RPM drives have higher bit density and larger platters contributing to very low $/GB. Desktop rated for 2 years @ 20% duty cycle, server for 5 years @ 100%

  17. Disk Performance Characteristics • Random I/O • Rotational speed • Seek time • Command Queuing, Short Stroke • Sequential I/O • Media transfer rate • Outer versus Inner tracks • Disk interface saturation

  18. Disk Specs (2003) * Includes 0.2ms controller overhead

  19. Disk Specs (2005)

  20. Random IO Rate IO rate based on data distributed over entire disk accessed at random, one IO command issued at a time

  21. Other Factors – Random IO Short Stroke: Data is distributed over a fraction of the entire disk Average seek time is lower (track-to-track minimum) Command Queuing: More than one IO issued at a time, Disk can reorder individual IO accesses, lowering access time per IO

  22. Controllers and Adapters • [RAID] Controllers • Processing capability for RAID logic etc • SCSI 2-4 channels per adapter • SAS 8 ports • SATA 4-8 ports (12 & 16) • Host Bus Adapters (HBA) • Only interfaces IO bridge to disk interface • Fiber channel 1 or 2 ports, SCSI

  23. Controllers and Adapters

  24. U320 RAID Controllers • May generate 240MB/sec per channel • 2 Channel adapter ~480MB/sec • 2 adapters per PCI-X bus OK • Minute part of PCI-e x8 port • 4 Channel adapter could generate 1GB/sec • Prefer 1 adapter on 133MHz PCI-X bus

  25. Fiber Channel HBA • 1 & 2 port adapters • PCI-X and PCI-e • 2 port may generate 350-400MB/sec • 2 dual port adapters per PCI-X bus if bandwidth used is mostly uni-directional • Dual port adapter only uses fraction of PCI-Express x8 port

  26. SAS Adapters • HP Smart Array P600 RAID Controller • 8 3.0Gbit/sec SAS ports • 2.4GB/sec each direction • 2 x 4 port connectors • Max 38 drives • PCI-X • LSI Logic SAS3xxx

  27. SATA Raid Controllers • 8 port SATA common • 1.5Gbit/sec per port • 3.0Gbit/sec per port on SATA-II • PCI-X • SATA disks max out at 50-70MB/sec • 560MB/sec per 8-port adapter max

  28. PCI-X and PCI Express • PCI-X 64-bit wide • 100MHz 2 slots per bus, 133MHz 1 slot • 800MB/sec, 1GB/sec • Most adapters available • PCI Express • 3 x8 slots • Each 2GB/sec in direction • No single adapter can generate this

  29. Performance Counters Storage Performance for SQL Server

  30. Performance Counters • System Monitor - measured from OS • Can only see disks visible to OS • HW specific – detail for each disk in array • OS: Physical & Logic disks • Size, latency, queue depth, IOPS, MB/sec • Are disk ops small block random • Large or sequential ops • Read/Write mix

  31. OS & Hardware Counters • OS Counters • Average values only • Example: 100 Reads, Average Bytes 16K • Don’t actual mix of 8K, 64K etc • Hardware – Vendor Specific • May give distribution of actual IOPS

  32. OS: Physical & Logical Disk • Physical Disk • Frequently most useful • Seen by OS as distinct physical disk • Hardware RAID may have striped multiple disks • Disks may be shared by other partitions • Logical Disk • When partition is striped across multiple physical disk

  33. Counters (Transfer, Read, Write) • Avg. Disk Bytes/[Read] [Write] [Transfer] • Disk [Reads] [Writes] [Transfer]/sec • Disk [Read] [Write] [] Bytes/sec • Avg. Disk [Read] [Write] [] Queue Length • Avg. Disk sec/[Read] [Write] (latency) • No simple single value interpretation • Must examine all the above counters together

  34. Interpreting Counters • No single counters can determine whether IOPS are random or sequential • High activity at 0 ms latency indicates small block sequential IOPS • Latency ~ Queue depth X Media transfer time also indicates sequential activity • Ex. 64MB/sec – 64K in 1ms • For queue depth 2, latency doubles

  35. Random IO • Low queue depth • For small block IO, 8-64K • Rotational latency and seek time are primary contributors to latency • Avg. Seek time for data spread across entire disk • Track-to-track seek time for highly localized data • High Queue depth • High IOPS per disk possible for small block IO due to command queuing

  36. Counters • Looking for indications of: • 1) small random transfers • 2) sequential or large block transfers

  37. IO Performance Tools Storage Performance for SQL Server

  38. IO Performance Tools & Testing

  39. IOMeter 8K Random Reads SATA Peak Sustained Reads/sec 380 Latency 170ms 4 10K SATA drives, no Command Queuing

  40. 8K Random Reads 10K SCSI Peak Sustained Reads/sec 1000 Latency 127ms 4 10K SCSI drives, Command Queuing

  41. 8K Random Reads 15K SCSI Peak Sustained Reads/sec 1400 Latency 95ms 4 15K SCSI drives, Command Queuing

  42. 8K Random Read – Full Disk 15K drives best on both peak IOPS and best latency

  43. Short Stroke Test Database 8 GB Max Server Memory 512MB Random read workload Disks Total Capacity Percent Use 4 x 73GB SATA 10K 280GB 2.8% 4 x 73GB SCSI 10K 280GB 2.8% 4 x 18GB SCSI 15K 72GB 11.0%

  44. Short Stroke SQL Reads 70% more Reads/sec 10K 25% more Rd/sec 15K 50-70% lower disk latency 15K drives better than 10K at low disk queue, about equal at high queue

  45. IO Characteristics of SQL Server operations Storage Performance for SQL Server

  46. IO Characteristics of SQL Server Ops

  47. SQL Reads 10K SATA Peak Sustained Reads/sec 670 Latency 92ms 4 10K SATA

More Related