Search results matching tags 'Performance' and 'Storage'

Storage Performance

jchang — Mon, 25 Mar 2013 03:52:00 GMT

Storage has changed dramatically over the last three years driven by SSD developments. Most of the key components necessary for a powerful storage system are available and the cost is highly favorable for direct placement of data files. Some additional infrastructure elements could greatly enhance the flexibility of storage systems with SSDs. There is still some discussion on whether SSD should interface directly to PCI-E or continue using the SAS/SATA interfaces originally designed for hard disks. New products coming this year include Express Bay, an ecosystem of connectors allowing both PCI-E and SAS/SATA to co-exist until a clear direction is established. Also expected in the coming year are PCI-E SSDs based on the NVM Express interface.

System Level

The Intel Xeon E5 processors, codename Sandy Bridge-EP, have 40 PCI-E gen 3 lanes on each processor socket. Even though PCI-E gen 3 is 8GT/s, a change in the encoding means that the usable bandwidth is double that of PCI-E gen2 at 5GT/s. The net realizable bandwidth of a PCI-E gen 3 x8 slot is 6.4GB/s versus 3.2GB/s for gen 2.

The unfortunate aspect is that the major server vendors all implement a mix of x16 and x4 slots, while the HBA vendors seem to be concentrating on products for PCI-E x8. Only Supermicro has a system with 10 8 PCI-E gen 3 x8 slots. Could a vendor put 2 HBA/RAID Controllers designed for x8 onto a single card for a x16 slot? Perhaps the new Express Bay form factor will have some means to use x16 slots?

Another disappointment is that the 4-socket Xeon E5-46xx systems only connect half of the PCI-E lanes. This might be because the base system configuration is 2-socket populated. If a full set of slots are provided, there would no connection to half of the slots unless all four sockets are populated. But this is also an issue on the 2-socket systems if only 1 socket is populated.

Direct-Attach

For the most part, I will discuss direct-attach storage configuration, as we can pick and choose among the latest components available. Technically, direct-attach with SAS can support a 2-node cluster, but few system vendors promote this configuration. Dell sells the MD3200 as direct-attach storage supporting 4 hosts in a cluster (or not), but technically it is a SAN that just happens to use SAS interfaces on both the front-end and back-end.

The objective in the baseline storage configuration below is to achieve very high IO bandwidth even in the low capacity configuration. Of course it will also have very high IOPS capability because the main elements are SSD. My recommended storage system has both SSD and HDD in each IO channel.

This intent is to place the main databases on SSD and use the HDD for backups and for restore verification. For an Inmon style data warehouse, the HDD might also be used for older data. The reason for having both SSD and HDD on each IO channel is to take advantage of simultaneous bi-directional IO. On a database backup, the IO systems reads from SSD, and simultaneously writes to HDD.

4 RAID Controllers, 4GB/s per controller, 16GB/s total IO bandwidth
4 Disk enclosures (yes, I am showing 8 enclosures in the diagram above)
4 x 16 = 64 SSD
4 x 8 = 32 (10K) HDD

The standard 2U enclosure has 24 x 15mm bays. My preference is for a 16 SSD and 8 HDD mix. With the small capacity 100GB SSD, there will be 1.6TB per enclosure and 6.4TB over 4 enclosures before RAID. In 7+1 RAID 5 groups, there will be 5.6TB net capacity, and 4.8TB in 3+1 RG across 4 units. The goal is 4GB/s per controller because the SAS infrastructure is still 6Gbps, supporting 2.2GB/s on each x4 port. With 16 SSDs per controller, each SSD needs to support 250MB/s. Most of the recent enterprise class SSDs are rated for well over 300MB/s per unit, allowing for a large degree of excess capability. Another option is to configure 12 SSDs per controller, expecting each SSD to support 333MB/s.

The cost structure for the above is as follows:
RAID controller $1K
2U Enclosure $3K
Intel SSD DC 3700 100GB SSD $235 x 16 = $3760, RAID 5 7+1: 1.6TB
Seagate Savvio 600GB 10K HDD $400 x 8 = $3200.

This works out to $11K per unit or $44K for the set of 4. The set of 16 x 100GB contributes $3760. For the 800GB SSD, the R5 7+1 capacity is 44.8TB at cost $148K.

At maximum expansion of 4 enclosures per RAID controller, capacity is 170TB at cost is $590K. Of course at this level, I would elect a system with more PCI-E slots for greater IO bandwidth. Another option is a RAID controller with 4 x4 SAS ports. Unfortunately none of these have 4 external ports.

While the Intel SSD DC 3700 drew reviews for pioneering consistency of IO performance instead over peak performance, it is only available in SATA interface. Micron ~~Crucial~~ has ~~announced~~ the P410m with similar specifications but with SAS interface. This is listed on the Micron website as in production, but probably only to OEM customers. There are other enterprise grade high endureance MLC SSDs with SAS interface as well.

Note: I do not recommend anything less than 10K HDD even to support database backups. The 10K HDDs are not particularly expensive as direct-attach components ($400 for the 600GB model). Only SAN vendors sell $400 HDDs for $2K or more.

SAS 12Gbps Enclosures

Disk enclosures supporting SAS at 12Gbps might become available as early as this year. Each of the 12Gbps SAS x4 uplink and down link ports would then support 4GB/s. The RAID controller (HBA) can support 6GB/s+ in a PCI-E gen 3 x8. The system with 4 RAID controllers could then deliver 24GB/s instead of 16GB/s. At 16 SSDs per controller, this would require 400MB/s per SSD. While SSDs are rated as high as 550MB/s, achieving the full aggregate bandwidth in an array is not necessarily practical. So 400MB/s per SSD in an array is a more reasonable expectation. Also, enterprise SAS SSDs many only be rated to 400MB/s.

We should not need 12Gbps SAS SSDs or HDDs in the near future (but 8 NAND channels is a good match for a 1.1GB/s interface). The internal wires in the enclosure connect through a SAS expander. The IO from each device bay can signal at 6Gbps, then uplink to the HBA at 12Gbps, assuming that packets are buffered on the expander.

The standard 2U disk enclosure today supports 24 or 25 2.5in (SFF) bays, with 15mm thickness. This is the dimension of an enterprise class 10K or 15K HDD with up to 3 platters. The older full size notebook used a 9mm HDD supporting 2 platters. Thin notebooks used a 7mm HDD restricted to a single platter. There is no particular reason for an SSD to be more than 7mm.

It would be better if the new 12Gbps SAS enclosures support more than 24 bays. My preference is for 16 x 15mm and 16 x 7mm bays. The key is to match the practically realizable aggregate bandwidth of SSDs to the uplink with some degree of excess. Personally, I would like to discard the SSD case to further reduce thickness.

Another option is to employ the NGFF, perhaps a 1U stick, at 5mm or less. There could be 2 rows of 24 for SSD, and the 16 x 15mm bays.

I believe that the all-SSD idea is misguided. SSDs are wonderful, but HDD still have an important role. One example is having the HDDs available for backup and restores. I want local HDD for backups because so very few people know how to configure for multiple parallel 10GbE network transmission, not to mention IO bandwidth on the backup system.

A database backup that has not been actually verified to restore (with recovery) is a potentially useless backup. Having HDDs for backup and restore verification preserves the write endurance on the SSD. This allows the use of high-endurance MLC instead of SLC. In some cases, it might even be possible to use consumer grade MLC if and only if the database organization maintenance strategy is architected to minimize wear on the SSD.

PCI-E SSD

Some of the discussion on PCI-E versus SATA/SAS interface for the NAND/Flash controller incorrectly focuses on the bandwidth of a single 6Gbps lane versus 4 or 8 lanes on PCI-E. It is correct that PCI-E was designed to distribute traffic over multiple lanes and that hard drives were never expected to exceed to bandwidth of a single lane at the contemporary SATA/SAS signaling rate. The transmission delay across an extra silicon trip, from NAND controller with SATA interface to a SATA to PCI-E bridge chip, on the order of 50ns, is inconsequential compare this with the 25-50µsec access time of NAND.

The more relevant matter is matching NAND bandwidth to the upstream bandwidth. All (or almost all?) the SATA interface flash controllers have 8 NAND channels. Back when SATA was 3Gbps and NAND was 40-50MB/s, 8 channel s to the 280MB/s net bandwidth of SATA 3G was a good match. About the time SATA moved to 6Gbps, NAND at 100-133MB/s became available so 8 channels was still a good choice.

NAND is now at 200 and 333MB/s, while SATA is still 6Gpbs. The nature of silicon product cost structure is such that there is only minor cost reduction in building a 4 channel flash controller. The 8 channel controller only requires 256-pin package.

The PCI-E flash controllers have been designed with 32 NAND channels. The IDT 32-channel controller has 1517 pins, which is not excessively difficult or expensive for a high-end server product. Note the Intel Xeon processors are 2011-pins. As noted earlier a PCI-E gen 3 x8 port supports 6.4GB/s. Over 32 channels, each channel needs to provide 200MB/s. The new 333MB/s NAND is probably a better fit to sustain the full PCI-E gen 3 x8 bandwidth after RAID (now RAIN because disks are replaced by NAND).

Based on 64Gbit die, and 8 die per package, a package has 64GB raw capacity. The 32-channel PCI-E with 1 package per channel would have 2TB raw capacity (net capacity with 0.78 for over-provisioning and 0.875 for RAIN would be 1400GB) versus 512GB on an 8-channel SATA/SAS SSD. The IDT document states capacity is 4TB raw for their 32-channel controllers, so perhaps it allows 2 packages per channel? The Micron datasheet mentions 32-channel and 64 placements.

As is today, a PCI-E SSD can achieve maximum bandwidth at lower NAND capacity and in a more compact form factor than with SAS SSD. On the other hand, SAS infrastructure provides flexible expansion. Capacity can be increased without replacing existing devices. Some systems support hot swap PCI-E slots. However the orientation of the connector in the system chassis makes this a complicated matter. The implications are that PCI-E slot SSDs are highly suitable for high density requirements with limited expansion needs. One database server example is tempdb on SSD.

NVM Express

The new generation of PCI-E SSDs may employ the NVMe interface standard. There is a standard driver for Windows and other operating systems, which will later be incorporated into to the OS distribution media allowing boot from an NVMe device, as with SATA devices today. This is mostly a client side feature.

For the server side, the NVMe driver is designed for massive bandwidth and IOPS. There can be up to 64K queues, 64K commands (outstanding IO requests?). The driver is designed for IO to be both super-efficient in cpu-cycles and scalable on NUMA systems with very many processor cores.

Express Bay

To promote the growth of SSD without betting on which interface, the Express Bay standard defines a connector that can support both PCI-E and SATA or SAS. Some Dell servers today support PCI-E to SSDs in the 2.5in HDD form factor (SFF), but I am not sure if this is Express Bay. This form factor will allow PCI-E devices to hot-swapped with the same ease as SAS devices today.

PCI-E Switches

As mentioned earlier, the PCI-E slot arrangement in server systems does not facilitate hot-add, even if it is supported. Existing PCI-E SSDs also do not provide a mechanism for capacity expansion, aside from adding a card to an empty slot or replacing an existing card.

Of course, there are PCI-E switches, just like the SAS expanders. A 64 lane PCI-E switch could connect 5 x8 PCI-E devices over a x8 upstream link. Other possibilities is a x16 link supporting 12.8GB/s to host with 4 ports for SSD, or 8 x4 ports to SSD for finer grain expansion. It may also be possible to support multiple hosts, as in a cluster storage arrangement?

SAN Configuration

Below is a representation of a typical configuration sold to customers by the SAN vendor. I am not joking in that it is common to find 2 ports FC or FCOE on each host. The most astounding case was a SAN with 240 x 15K disks and 2 single port FC HBAs in the server. Even though the storage system service processors had 4 FC port each (and the FC switches had 48-ports), only 1 on each SP was connected. Obviously the storage engineer understood single component and path failure fault-tolerant design. It was just too bad he built a fault-tolerant garden hose system when a fire hose was needed.

As I understand it, what happened was the SAN engineer asked how much space is needed for the databases accounting for growth, and then created one volume for it. The Windows OS does support multi-path IO. Originally the storage vendor provided the MPIO driver, but now it is managed by Microsoft. Apparently it was not considered that even with MPIO, all IO for a single volume has a primary path. The secondary path is only used when the primary is not available.

High-bandwidth SAN Configuration

A proper SAN configuration for both OLTP and DW database servers is shown below. Traditionally, a transaction processing database generates small block random IO (2KB in the old days, 8KB since SQL Server 7). As it was difficult to get 10K IOPS (x8KB = 80MB/s), it was thought that IO bandwidth was not a requirement. This was 15 years ago. Apparently the SAN vendors read documents from this period, but not more recently, hence their tunnel vision on IOPS, ignoring bandwidth.

For the last 10 or more years, people have been running large queries on the OLTP system. I have noticed report queries that saturate the storage IO channels could essentially shutdown transaction processing. This is because the report query generates asynchronous IO at high queue depth, while the transaction queries issue synchronous IO at queue depth 1. And the report may escalate to a table lock (or it may use nolock). Furthermore, it is desirable to be able to backup and restore the transaction database quickly. This means bandwidth.

Note that the system below shows 8Gbps FC, not 10Gbps FCOE. A single 10Gbps FCOE may have more bandwidth than a single 8Gbps FC port. But no serious storage system will have less than 4 or even 8 ports. Apparently FCOE currently does not scale well over multiple ports, due to the overhead of handling Ethernet packets? An Intel IDF 2012 topic mentions that this will be solved in the next generation.

The above diagram shows 8 x 8Gbps FC ports between host and storage system. Each 8Gbps FC port can support 700MB/s for a system IO bandwidth target of 5.6GB/s. An OLTP system that handles very high transaction volume may benefit from a dedicated HBA and FC ports for log traffic. This would allow the log HBA to be configured for low latency, and the data HBA to be configured with interrupt moderation and high throughput.

An alternate SAN configuration for SQL Server 2012 is shown below with local SSD for tempdb.

The write cache on a SAN must be mirrored for fault tolerance. There is very little detail on the bandwidth capability of the link between controllers (or SP) on SAN systems, beyond what can be deduced from the fact that the sustained write bandwidth is much lower than the read bandwidth. So keeping tempdb off the SAN should preserve IO write bandwidth for traffic that actually needs protection.

The number of volumes for data and temp should be some multiple of 8. It would be nice to have 1 volume for each FC path. However we do need to consider how SQL Server place extents over multiple files. This favors RAID groups of 4 disks.

File Layout

In a HDD storage system, the objective for bandwidth is to simultaneously issue large block IO to all data disks across all IO channels. A 256K block could be sufficiently large to generate 100MB/s per disk (400 IOPS, not random). If this were issued at low queue depth (2?), then the storage system would not only generate high IO bandwidth and still be perceptually responsive to other requests for small block IO.

For small block random IO, it is only necessary to distribute IO over all hard disks with reasonable uniformity.

The file layout strategy has two objectives. One is to not overwhelm any single IO channel. In direct-attach, this is not a proper as the smallest pipe is x4 SAS for 2GB/s. In a SAN, even using 8Gbps FC, this is a concern as 8Gb FC can support only 700-760MB/s. Although 10Gb FCoE seems to have higher bandwidth, this may not scale with the number of channels as well as straight FC. The new Intel Xeon E5 (Sandy-Bridge EP) processors may be able to scale 10Gb FCoE with Data Direct IO (DDIO) - but this needs to be verified.

The second is to ensure IO goes to every disk in the RAID Group (or volume). By default, SQL Server allocates a single 64K extent from each file before round-robin allocating from the next file. This might be the reason that many SAN systems generate only 10MB/s per disk (150 IOPS at 64K), along with no read-ahead.

The -E startup flag instructs SQL to allocate up to 4 consecutive extents before proceeding to the next file. See James Rowland-Jones Focus on Fast Track : Understanding the –E Startup Parameter for more on this. In a 4-disk RAID group with stripe-size 64K, a 256K IO to the file would generate a 64K IO to each disk.

It would be necessary to rebuild indexes before this scheme takes effect. Somewhere it was mentioned that it is important to build indexes with max degree of parallelism limited to either 4 or 8. It might be in the Microsoft Fast Track Data Warehouse Reference Architecture. Start with version 4 for SQL Server 2012, and work backwards?

Trace Flag 1117 (-T1117) causes all files in a filegroup to grow together. See SQL Server 2008 Trace Flag -T 1117.

With SSD, the second may not be important as the SQL Server read-ahead strategy (1024 pages?) should generate IO to all units. On the hard disk, generate close-to-sequential IO was important. On SSD, it is sufficient beneficial just to generate large block IO, with 64K being large.

Summary

The old concept of distributing IO over both devices and channels still apply. The recent pricing of SSD is sufficiently low to warrant serious consideration ($2-3K/TB eMLC). While there is more flexibility in SSD configuration, it is still necessary to validate performance characteristics with real queries to an actual database. SQLIO or other synthetic tests are not sufficient. If the SAN vendor advised in the configuration, then chances are IO bandwidth will be not be good.

Addendum
If anyone thinks I am being unfair to or overly critical of SAN vendors, do the following test.
Find the biggest table in your database, excluding LOB fields.
Run this:
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT COUNT(*) FROM TableA WITH (INDEX(0))
GO

Then compute 8 (KB/page) * (physical reads + read-ahead reads)/(elapsed time in ms)
Is this closer to 700 MB/s or 4GB/s? What did your SAN vendor tell you?

I am also not fan of SSD caching or auto-tiering on critical databases, meaning the database that runs your business, that is managed by one or more full-time DBAs. In other applications, there may not be a way to segregate the placement of hot data differently from inactive data. In SQL Server, there are filegroups and partitioning. We have all the necessary means of isolating and placing hot data whereever we want it. SSD caching or auto-tiering will probably require SLC NAND. With active management using database controls, we should be able to use HET MLC or even MLC.

I stress the importance of analyzing the complete system and how it will be used instead of over-focusing on the components. There are criteria that might be of interest when there is only a single device or even single HBA. Today it is possible to over-configure the storage performance without unwarranted expense, and this is best accomplished by watching the big picture.

Adaptec reports that their Series 7 SAS RAID Controller (72405 - PCI-E gen 3 x8 on the upstream side and 6 x4 SAS 6Gpbs) using the PMC PM8015 controller can do 500K IOPS and 6.6GB/s.

I will keep this topic up to date on www.qdpma.com Storage 2013

related posts on storage:
io-queue-depth-strategy (2010-08)
data-log-and-temp-file-placement (2010-03)
io-cost-structure-preparing-for-ssd-arrays (2008-09)
storage-performance-for-sql-server (2008-03)

ps,
If you are using SQL Server 2012 clustering on a SAN, I do suggest placing tempdb on local SSD, making use of the new 2012 feature that does not require tempdb to be on shared storage. Keep in mind on the SAN, writes must be mirrored between two storage processors for fault recovery, and this is not a cheap thing to do. We should plan redo whatever was using tempdb at the time.

Consumer SSDs with SQL Server

jchang — Sat, 17 Sep 2011 03:21:00 GMT

Over the last two years, I have stood up several proof-of-concept (POC) database server systems with consumer grade SSD storage at cost $2-4K per TB. Of course production servers are on enterprise class SSD, Fusion-IO and others, typically $25K+ per TB. (There are some special situations where it is viable to deploy a pair of data warehouse servers with non-enterprise SSD).

PCI-E SSDs - OCZ RevoDrive, RevoDrive X2, & RevoDrive 3 X2
The first POC system was a Dell T710 with 2 Xeon 5670 processors 96GB (12x8GB) memory, 16x10K SAS HDDs and 6 OCZ RevoDrive (original version) PCI-E SSDs supporting table scans at nearly 3GB/s. The most difficult query repeatedly hashed a large set of rows (as in there were multiple large intermediate result sets) generating extremely heavy tempdb IO. With tempdb on 12 10K HDDs, the query time was 1 hour. With tempdb on the 6 OCZ RevoDrives, the query time was reduced to 20min.

Before SSDs became viable, I would normally have configured a 2-socket system with 48 (2x24) 15K HDDs, with one RAID controller for each 24-disk enclosure. This setup costs about $11K per enclosure with 24x146GB 15K SAS drive and can be expected to deliver 4GB/s sequential bandwidth, 10K IOPS at low queue, low latency (200 IOPS per 15K disk) and in the range of 15-20K IOPS at high queue, high latency. As it was my intent to deploy on SSD, I only configured 16 HDDs in the internal disk bays and did not direct the purchase of external HDDs.

The 6 OCZ RevoDrive 120GB PCI-E SSDs in the POC system cost about $400 each at the time (now $280?). I recall that the tempdb IO traffic was something like 40K IOPS (64KB), around 2.5GB/s bandwidth. This was consistent with the manufacturers specifications of 540MB/s read and 480MB/s write at 128K IO, and considering that there will be some degradation in aggregating performance over 6 devices. The IO latency was somewhere in the range of 40-60ms (note that the SQL Server engine issues tempdb IO at high queue depth). OK, so the real purpose of the POC exercise was to tell the SAN admin in no uncertain terms that the 350MB/s from his $200K iSCSI storage system (4x1GbE) was pathetic, and even the 700MB/s on 2x4Gbps FC ports does not cut mustard in DW.

The next set of systems was ordered with 4 OCZ RevoDrive X2, 160GB (<$500 each). There was some discussion on whether to employ the OCZ enterprise class ZDrive R3, but this product was cancelled and the OCZ substitute, the VeloDrive (4 SandForce 1565 controllers, rated for ~1GB/s), was not yet available. I was expecting somewhat better performance for 4 RevoDrive X2 (4 SandForce 1222 controllers each, rated for 700MB/s) over 6 of the original RevoDrives (2 SandForce controllers each). The tempdb IO intensive query that took 20min with the 6 RevoDrives now ran in 15min with the 4 RevoDrive X2s. In additional, IO latency was under 10ms.

I was hoping to test the new OCZ RevoDrive 3 X2 with 4 SandForce 2281 controllers, rated for 1500MB/s read and 1200MB/s write. Unfortunately there is an incompatibility with the Dell T110 II with the E3-1240 (Sandy Bridge) processor which has a new UEFI replacing the BIOS. OCZ does not provide server system support on their workstation/enthusiast products. Hopefully Dell will eventually resolve this.

SATA SSDs - OCZ Vertex 2, Vertex 3 & Vertex 3 Max IOPS, Crucial C300 & m4
My preference is to employ PCI-E rather than SATA/SAS SSD devices. This is mostly driven by the fact the disk enclosures reflect the IO capability of HDDs, with 24 bays on 4 SAS lanes. An SSD oriented design should have 4 SSDs on each x4 SAS port. Of course, 4 SSDs and 4-8 HDDs on each x4 SAS port is also a good idea.

So I have also looked at SATA SSDs. Earlier this year, I started with the OCZ Vertex 2 and Crucial C300 SSDs. After encountering the issue with the RevoDrive 3 on the new Dell server, I acquired OCZ Vertex 3, Vertex 3 Max IOPS, and Crucial m4 SATA SSDs. The OCZ Vertex 2 has a 3Gbps interface, the Vertex 3 and both Crucial C300 and m4 all support 6Gbps SATA interface.

The OCZ Vertex SSDs use SandForce controllers; the Vertex 2 uses the previous generation SandForce 1222 and the Vertex 3 uses the current generation 2281 controller. The Crucial SSDs use Marvel controllers (both?). Perhaps the significant difference between the OCZ Vertex and Crucial SSDs are that the SandForce controllers implement compression. The OCZ Vertex SSDs have far better write performance with compressible data, but is comparable for incompressible data. It does appear that SQL Server tempdb IO is compressible and benefits from the compression feature.

Another difference is that OCZ offers 60, 120, 240 and 480GB capacities while Crucial offers 64, 128, 256 and 512GB capacities. All capacities are in decimal, that is, 1GB = 10^9 bytes. Both OCZ 60GB and Crucial 64GB presumably have 64GB NAND flash, the 64GB being binary, meaning 1GB = 1024^3, or 7.37% more than 1GB decimal. Basically, OCZ has more over-provisioning than the Crucial, which in theory should also contribute to better write performance. (Earlier Vertex drives had 50 and 100GB capacities. But there are so many varieties of the Vertex 2 that I cannot keep track.)

In brief, the performance difference between SSD generations, both from the OCZ Vertex 2 to Vertex 3 and from the Crucial C300 to m4, is substantial, so I will focus mostly on the newer Vertex 3 and m4 drives. The performance observed in SQL Server operations seemed to be consistent with manufacturer specifications for both generations of OCZ and Crucial SSDs. It was not noted whether writing compressed SQL Server database tables were further compressible by the SandForce controller. This may be because it is difficult to achieve the high write performance necessary to stress modern SSDs in SQL Server with transactional integrity features.

Test System - Dell T110 II, Xeon E3 quad-core Sandy Bridge processor
The test system is a Dell PowerEdge T110 II, Xeon E3-1240 3.30GHz quad-core processor (Sandy Bridge) with 16GB memory. This system has 2 PCI-E Gen2 x8 and 1 Gen 1 x4 slot. All SSDs were attached to a LSI MegaRAID SAS 8260 controller (PCI-E Gen2 x8, 8 6Gbps SAS ports). I did not some testing with 2 SSDs on the SATA ports (3Gbps) but did make detailed observations.

Incidentally, the cost of this system, processor, memory and 1 SATA HD was $1078? The 128GB SSDs were about $220 ($268 for the Max IOPS). So a very capable system with 2 SSDs could be built for $1300-1500 (64GB or 128GB SSDs). A better configuration with 2 SATA HDDs, 4 SSDs and SAS controller would push this to $2500. But if Dell and OCZ could resolve this RevoDrive 3 -UEFI issue, then I would recommend the T110 II, E3 processor, 16GB memory, 2 SATA HDDs and 1 RevoDrive 3 X2.

One unfortunate aspect of this system is that the SATA ports are all 3Gbps per the Intel C202 PCH, even though the ever so slightly more expensive C204 supports 6Gpbs SATA on 2 ports. Basically, this has similar characteristics as the database laptop with super IO that I proposed earlier, except that the laptop would be 2.5GHz to keep power reasonable.

Performance tests with the TPC-H SF 100 database
With 8 SSDs (2 Vertex 3, 2 Vertex 3 MaxIOPS, 2 m4, and 2 C300) I was able to generate 2.4GB/s in table scan aggregation query, possibly gated by the 355MB/s rating of the C300s. A configuration consisting of the 4 Vertex 3 and 2 m4’s would have been gated by the 415MB/s rating of the m4. If can get a total 8 Vertex 3s, which are rated at 550/500MB/s for compressible and incompressible data, then I would either be limited by the adapter or the PCI-E Gen2 x8 limit of 3.2GB/s. There is an LSI SAS8265 adapter with dual-cores that has even higher IOPS capability, but it is not known whether this is necessary for large block IO.

The tests consisted of running the TPC-H queries, single stream (but not per official benchmark requirements). The figure below show the time to run the the 22 queries (excluding statistics, parse and compile) for 2, 4 and 6 SSDs with no data compression (raw) and with page mode data compression.

Run time on 2 OCZ Vertex 3 (regular) SSDs was 815 sec with compression and 936 sec raw (w/o compression). On 4 OCZ Vertex 3 SSDs (2 regular, 2 MaxIOPS) total query times were reduced to 658 sec with compression and 622 sec raw. On 6 SSDs, 4 OCZ and 2 Crucial m4, total query times are 633 sec with compression and 586 sec raw.

The figure below shows tempdb IO write latency for 2, 4 and 6 SSDs, with raw and compressed tables.

On the 2 OCZ SSDs, IO latency from fn virtual file stats (for the entire run) averaged 30ms (temp write) and 90ms (data read) with compression and 60ms (temp write) - 130ms (data read) without compression. The performance with 2 Crucial m4 drives was less, showing much higher write latencies. On 4 OCZ Vertex 3 SSDs, IO latency was 14 temp write and 30ms data read with compression and 18-60ms without compression. The IO latencies on the Max IOPS models were lower than on the regular Vertex 3 models. For 6 SSDs, IO latencies are now down to the 15ms range, with somewhat higher latency on the Crucial m4 SSDs.

With data and tempdb on 2 OCZ Vertex 3 SSDs, performance was decent but IO constrained. Performance was 15% better with data compression (page) than without, even though CPU was 23% higher. Performance with 4 OCZ Vertex 3 SSDs (2 regular, 2 Max IOPS) was 20% better for compression on and 34% better without compression, relative to performance with 2 SSDs. The performance without compression was now 6% better than with compression. At 6 SSDs (4 Vertex 3, 2 m4), there was another 5% performance improvement relative to 4 SSDs, for both compressed and not compressed.

In the above tests, each SSD was kept as a standalone disk, i.e., I did not use RAID. There was 1 data and 1 tempdb file on each SSD. I noticed that the uncompressed (raw) database tended to generate 64K or 128K IO, while the compressed database tended to have 256K IO. Two queries, 17 and 19(?) generated 8KB IO, and would have much better performance with data in memory. There was also wide variation from query to query in whether performance was better with or without compression.

Caching RAID Controller Performance Tuning Secrets (cough-giggle)

jchang — Tue, 12 Oct 2010 00:07:00 GMT

Over weekend I was looking at various RAID controller documents to update my material on storage performance, and I came across three items that were good for a hoot.

Direct IO, not Cached IO

The LSI slidedeck MegaRAID Performance Tuning and Benchmark Tips states that Direct IO is the correct setting on RAID controllers. "All read data is transferred directly to host memory, bypassing RAID controller cache. Any Read Ahead data is cached. All write data is transferred directly from host mempory bypassing RAID controller cache if Write-Through cache is set." The Cached IO " (All read and write data passes through controller cache memory on its ways to or from host memory, includes write data in Write-Through mode), legacy option is being eliminated in a future release.

Dell references Dell PERC, Dell PERC H700 and H800 Technical Guidebook Dell PowerEdge RAID Controllers Cards H700 and H800 Technical Guide.

New features: Cut-Through IO (CTIO)
Performance boosts (up to 2X IOPS performance),
optimized for superior IOPS of SSDs over HDDs,
Ideal for small-block random workloads,
Easy to enable

Cut-Through IO is an IO accelerator that boosts the throughput of devices connected to the PERC Controller. It is enabled through disabling the write-back cache (enable write-through cache) and disabling Read Ahead.

The Dell Technical White Paper High Performance Tier Implementation Guideline for PowerVault MD32200 and MD3200i Storage Arrays. the Dell Technical White Paper Array Tuning Best Practices for PowerVault MD32200 and MD3200i Storage Arrays,

High Performance Tier

The High Performance Tier is described as "an optional upgrade that can increase the performance of MD3200 and MD3220 series arrays that have high drive count, Solid state drives (SSDs) or high data transfer workloads. This implementation is based on an enhanced firmware algorithm and does not require any new hardware dependencies." The License Key for the Performance Tier is $3,660.

Between you and me, I am inclined to think the enhanced firmware disables the cached IO that seems to cripple SAN performance, as in those really expensive SAN storage systems that the vendor sales rep told you was immensely powerful that would solve all your storage problems. Only when you got it, you found the performance was crap. In benchmarking world, it has been known for a long time that caching disk IO had significant CPU overhead (where ever this caching was done) with negative impact on performance. Disabling and preferably bypassing cache code entirely improves performance to the level expected from the sum of the bare disk drives. So what exactly are these enhanced alogorithms? Caching is particularly idiotic with databases because the database engine is a cache. Suppose a block is read from disk, cached in the RAID controller memory, then cached again in the SQL Server buffer pool. Just how likely is it the SQL Server will need to issue a read for this block from disk? Caching write during a temporary surge, ie, check point can help, so long as the caching algorithms don't get in the way of real disk IO

Speaking in Raleigh NC June 15th

Andrew Kelly — Wed, 02 Jun 2010 20:53:00 GMT

Just a heads up to those in the area that I will be speaking at the (TriPASS) Raleigh SQL Server user group on the 15th of June 2010. The topic is Storage & I/O Best Practices. The abstract is listed below:

SQL Server relies heavily on a well configured storage sub-system to perform at its peak but unfortunately this is one of the most neglected or mis-configured areas of a SQL Server instance. Here we will focus on the best practices related to how SQL Server works with the underlying storage subsystem and what you can do to maximize the relationship between the two. The core topics will include discussions on SANs vs. Direct Attached storage, Raid Groups, Caching, Types of I/O, Number of Files & their placement and more.

http://www.tripass.org/dnn/

I am looking forward to seeing you there.

Andrew J. Kelly

HP Storage Works 2000 sa and fc G2 performance specs

jchang — Mon, 14 Sep 2009 18:38:00 GMT

While reading through the documentation for the HP Storage Works 2000 MSA, I found the following performance numbers cited in the quickspecs for both the FC and SAS versions. The 2000fc version cited performance numbers for the fc (Fiber Channel), sa (SAS) and i (iSCSI) models. The 2000sa version only cites SAS results. The MSA 2000 Technical Cookbook also cites performance results.

The results cited in the FC model documentation for the SAS and iSCSI models cannot be correct, unless the SAS and iSCSI models were completely botched, and as far as I am aware of, it has been a really long time since HP/Compaq last botched a design this badly.

I am inclined to think that the results cited in the FC section are incorrect, and may inadvertently be used by HP sales rep to recommend the FC version. The configuration in the performance report below is somewhat ok for OLTP, 4 x 4Gbs FC ports or equivalent. Of course if I had 4 SAS RAID controllers, each connected to 2 MSA-60 with 12 15K HDDs, I could get 4-6GB/s sequential. For a clustered transaction processing system, you pretty much need the SAN to support shared disks. But I am inclined to ask people to consider database mirroring, each node employing direct attach storage.

For Data Warehouse applications, I would outright recommend the direct-attach MSA-60 and skip the SAN.

From HP StorageWorks 2000fc G2 Modular Smart Array on 2009 Sep 14

Performance numbers are a guideline as established by tests using RAW I/O in an Operating System Agnostic test lab environment. A total of 96 72GB 15K SAS drives were used in a dual controller configuration of eight 12 Disk vdisks, eight 806 GB volumes, and two volumes per host. Four hosts directly attached to the MSA2000fc G2 were used in this test configuration (results cannot be expected with a single host). Results were achieved in Sequential Writes with 256K blocks; all random tests were based on 8K block sizes.
Number and type of applications, drive type, operating system used, and the number of hosts will affect overall performance.

Workload	MSA2300fc	MSA2000sa	MSA2000i
Host Connect	4 Gb Fibre Channel	3 Gb SAS	1GbE Ethernet

MSA2000 RAID 10 Performance Results (rounded)
Random Reads IOPs	21,400	10,600	8,200
Random Writes IOPs	8,800	4,900	4,500
Random Mix IOPs 60/40 read/write	13,500	6,800	6,100
Sequential Reads MB	1,300	700	300
Sequential Writes MBs	560	350	260
MSA2000 RAID 5 Performance Results (rounded)
Random Reads IOPs	20,500	10,200	7,800
Random Writes IOPs	2,400	2,000	1,600
Sequential Reads MBs	1,300	700	300
Sequential Writes MBs	780	380	270
Random Mix IOPs 60/40 read/write	5,900	3,300	3,200
MSA2000 RAID 6 Performance Results (rounded)
Random Reads IOPs	20,500	10,100	7,800
Random Writes IOPs	1,600	1,400	1,200
Sequential Reads MBs	1,300	700	300
Sequential Writes MBs	820	380	270
Random Mix IOPs 60/40 read/write	4,500	2,750	2,560

From: HP StorageWorks 2000sa G2 Modular Smart Array

Performance numbers are a guideline as established by tests using RAW I/O in an Operating System Agnostic test lab environment. A total of 96 72GB 15K SAS drives were used in a dual controller configuration of eight 12 Disk vdisks, eight 806 GB volumes, and two volumes per host. Four hosts directly attached to the MSA2000sa G2 were used in this test configuration (results cannot be expected with a single host). Results were achieved in Sequential Writes with 256K blocks; all random test were based on 8K block sizes.
Number and type of applications, drive type, operating system used, and the number of hosts will affect overall performance.

Workload	MSA2300sa G2
Host Connect	3Gb SAS
MSA2000 RAID 10 Performance Results (rounded)
Random Reads IOPs	21861
Random Writes IOPs	14491
Random Mix IOPs 60/40 read/write	17700
Sequential Reads MB	1049
Sequential Writes MBs	531
MSA2000 RAID 5 Performance Results (rounded)
Random Reads IOPs	21019
Random Writes IOPs	2705
Sequential Reads MBs	1051
Sequential Writes MBs	644
Random Mix IOPs 60/40 read/write	5897
MSA2000 RAID 6 Performance Results (rounded)
Random Reads IOPs	21029
Random Writes IOPs	1918
Sequential Reads MBs	1054
Sequential Writes MBs	736
Random Mix IOPs 60/40 read/write	4872

The following slide is from the HP MSA2000 Technical Cook Book.

Update 2009 Oct 30

Apparently HP has updated the HP StorageWorks 2000fc G2 Modular Smart Array spec sheet

Performance numbers are a guideline as established by tests using RAW I/O in an Operating System Agnostic test lab environment. A total of 96 72GB 15K SAS drives were used in a dual controller configuration of eight 12 Disk vdisks, eight 806 GB volumes, and two volumes per host. Four hosts directly attached to the MSA2000fc G2 were used in this test configuration (results cannot be expected with a single host). Results were achieved in Sequential Writes with 256K blocks; all random tests were based on 8K block sizes.
NOTE: Number and type of applications, drive type and number of drives, operating system used, and the number of hosts will affect overall performance.

MSA2 G2 Performance	MSA2300fc G2	MSA2000sa G2	MSA2000i G2
Protocol (host connect)	4 Gb Fibre Channel	3 Gb SAS	1GbE Ethernet
MSA2000 RAID 10 Performance Results
Random Reads IOPs	22,874	21,861	13,658
Random Writes IOPs	15,008	14,491	14,399
Random Mix IOPs 60/40 read/write	17,878	17,700	13,493
Sequential Reads MB	1,238	1,050	274
Sequential Writes MBs	532	532	266
MSA2000 RAID 5 Performance Results
Random Reads IOPs	22,044	21,000	12,335
Random Writes IOPs	2,714	2,714	2,714
Random Mix IOPs 60/40 read/write	5,325	4,926	5,325
Sequential Reads MBs	1,238	1,050	274
Sequential Writes MBs	725	617	266
MSA2000 RAID 6 Performance Results
Random Reads IOPs	21,975	21,000	12,292
Random Writes IOPs	1,876	1,876	1,876
Random Mix IOPs 60/40 read/write	4,293	3,865	3,851
Sequential Reads MBs	1,238	1,050	274
Sequential Writes MBs	772	729	266

Solid State Drive versus Memory, TPC-H Nehalem

jchang — Wed, 12 Aug 2009 04:06:00 GMT

A recently published TPC-H benchmark result, along with previously published results provide some insight into to the relative impact of SSD to disk drives, and SSD versus system memory. The TPC-H configurations are shown below. All results are at SF 100. The first two are on Windows Server 2003 sp1, and SQL Server 2005 sp1 and sp2 respectively use processors based on the Intel Core 2 architecture and HDD storage. The two recent results are both on Windows Server 2008 and SQL Server 2008 sp1 using the Intel Xeon 5570 processors (Nehalem architecture) and Fusion-IO SSD storage.

System	OS	SQL	Processors	Memory	Storage
Dell PE2900	W2K3 sp1	2005 sp1	2 Xeon 5355	48GB	HDD
HP DL580G5	W2K3 sp1	2005 sp2	4 Xeon 7350	128GB	HDD
Dell T610	W2K8	2008 sp1	2 Xeon 5570	48GB	SSD
HP DL380G6	W2K8	2008 sp1	2 Xeon 5570	144GB	SSD

The system configurations above are not the best to make this comparison. Ideally we would like to be able see the TPCH SF 100 result for Dell T610 with HDD storage. This would provide two systems identical except for the storage, and two similar systems that differ in system memory with the large memory configuration sufficient to place the entire database in the buffer cache. But we can make estimates for the expected differences between the two processor architectures based on available information.

First a quick word about TPC-H. Scale Factor 100 means the LINEITEM table data (leaf level) should be 100GB, or did when the table definition employed the 8 byte DATETIME data type. With the 4 byte DATE type employed in SQL Server 2008, the SF 100 LINEITEM table drops to 90GB. The full TPC-H SF100 database with all tables and indexes was approximately 170GB in SQL Server 2005, decreasing to 150GB with the DATE type. With compression, the database size probably fits in the buffer cache with SQL Server maximum memory set to 135GB out of the system memory of 144GB.

TPC-H is comprised of 22 scored queries, and has two test sequences. The Power sequence runs the 22 queries sequentially using a single stream, which is essentially a test of the database engine parallel execution plan capability. The Throughput sequences run multiple concurrent streams. All systems in this group use 5 streams in the throughput test at this SF. It would be helpful to see the Power sequence query time at degree of parallelism 1, 2, 4, 8 and so on to the number of physical and logical cores, but this is not part of the TPC-H. The top level results are shown below.

Processor	Memory	QphH	Power	Throughput
2 Xeon 5355	48GB	15,723.9	20,587.9	12,009.1
4 Xeon 7350	128GB	34,989.9	46,708.6	26,211.3
2 Xeon 5570	48GB	28,772.9	38,550.7	21,475,1
2 Xeon 5570	144GB	51,110.6	72,134.0	36,313.6

transition	Memory	QphH	Power	Throughput
2x 5355 to 2x 5570	48GB	83%	87%	79%
2x 5570 to 4x 7350	48-128	21.6%	21%	22%
2x 5570	48-144	78%	87%	69%

The T610 with 2 Xeon 5570 result is very impressive, being 83% higher than the 2-way Xeon 5355 quad-core 2.66GHz Core 2 architecture (8 physical cores on both systems) with other difference being the SSD storage. The T610 is just 21% lower than the 4-way Xeon 7350 system with 16 Core 2 architecture cores and 128GB memory. The typical TPC-H storage configuration with hard drives can deliver substantial IO bandwidth, with 4-8 PCI-E SAS controllers and 80-200 disk drives. The SSD configuration, however, can also deliver spectacularly more pseudo-random small block IOPS than 200 disk drives and at much lower latency.

The DL380G6 with 144GB memory is even more impressive scoring 78% higher in the composite QphH score, 87% in Power and 69 % higher in overall than the T610 with 48GB.

The chart below the run time for each of the 22 TPC-H queries in the Power sequence (stream 0) on the Dell T610 with Xeon 5570 processors and SSD storage relative to the Dell PowerEdge 2900 with Xeon 5355 processors and HDD storage.

We can use the SPEC CPU 2005 integer benchmark to make a rough estimate for the difference in the processor performance between the Core 2 and Nehalem architectures. The SPEC CPU 2005 integer for the Xeon 5355 (2.6GHz) is 19.4 and for the Xeon 5570 31.5, with a very large gain in 2 components, which means some of the gain should be de-rated due to the nature of SPEC CPU where the very latest compiler improvements are incorporated. The 60% difference in performance implies that the execution time of the faster processor should be approximate 0.625 times that of the baseline processor. Since the reduction in query time is visibly better than 0.625 for many queries, we can attribute this to the SSD.

The chart below shows the TPC-H Power sequence query times for the DL380G6 with 144GB memory relative to the T610 with 48GB. Five queries, 1, 9, 13, 15 and 16, show only minor differences with query 13 actually being 15% faster with less memory, and another two, 3, 14, show 20% gain for the extra memory. Nine queries show gains of 2.5X or more and another shows 2X gain. So overall, there is a significant benefit to having data in system memory, i.e., buffer cache, even with the extraordinarily high IOPS and low latency of SSD storage.

The reason for this is that the CPU cost for the operating system to perform an IO operation, regardless of whether this is to disk or SSD storage devices, and then to evict an existing page in the buffer cache is high relative to the cost of many common SQL operations. See IO Cost Structure (or my blog on SQLblog) for more on this. The duration or elapsed time on disks drives is much higher than SSD, but the CPU cost cannot be evaded.

While the published performance data is not ideal, needing a result for a 2-way Xeon 5570 with 48GB memory and HDD storage for SQL Server 2008, the available results are sufficient to indicate that SSD can significantly improve performance relative to HDD even in data warehouse type applications, and the system memory is still best for database performance.

The chart below shows the stream 0 (Power) runtime for the 16-core (4xXeon 7350 Core 2) 128GB and hard drives, the 8-core (Xeon 5570 Nehalem) with 48G and SSD, and the 8-core (X5570) with 144GB and SSD relative to the 8-core (Xeon 5355 Core 2) 48GB memory with hard drives. In query 3, the 16-core with 128G is actually 20% slower than the 8-core with 48GB. This goes to show parallel execution plans can be very tricky. Without thorough investigation of how each query behaves at degree of parallelism 1, 2, 4, 8, 16 and so on, nasty surprises can and do occur. Big-iron system can be very useful, but a simple migration from a 4-way system can result in minimal or negative performance change. With expert investigation and troubleshooting, very respectable performance gains can usually be achieved.

For the majority of the queries, the 16-core 128G memory system has double or more improvement relative to 8-cores 48GB memory. Doubling the number cores alone should yield 30-40 reduction in query time as the gain is entirely due to the increase degree of parallelism from 8 to 16, which is a difficult aspect to scale. So it is apparent the additional memory probably contributes to performance significantly.

Update 2009-10-11

The original HP 2xX5570 publication has been withdrawn. Three results were later published, all with 2 x Xeon 5570 processors, 144GB memory. One is with the 4 Fusion IO drives, another uses 12 SATA SSD drives spread across 3 SAS controllers, and the third with 100 SAS hard disk drives.

Processor	Memory	QphH	Power	Throughput
100 HDD	144GB	50,738.4	67,712.9	38,019.1
12 SATA SSD	144GB	51,422.4	70,048.5	37,749.1
4 FusionIO SSD	144GB	51,085.6	72,110.5	36,190.8

The chart below shows the TPC-H power results on the 22 individual queries for the SATA SSD and FusionIO SSD relative to the SAS HDD. Most queries are either indentical or very close. The SSD is much faster for certain queries, but slower in others.

The two queries where SSD is much faster are 2 and 12. The execution plan for these at SF10 show nested loops joins to the medium tables ORDERS and PARTSUPP (I do not have the execution plan for SF 100 handy). HDD is faster on Q9, which shows scans and hash match joins between several tables.

Performance impact: Logical scan fragmentation -- 100% may not be as bad as 85%

Linchi Shea — Mon, 22 Jun 2009 16:02:00 GMT

In this previous post I asked whether 100% logical scan fragmentation is always worse than 85% local scan fragmentation for table/index scans. (To be precise, I was talking about a B-tree table, i.e. a table with a clustered index).

The answer is no. 100% logical scan fragmentation is not always worse than 85% logical scan fragmentation in terms of table or clustered index scans. And the following is an example.

Two T-SQL scripts were used to populate a test table in separate tests. The key differences between these two scripts are highlighted in the code segments below in bold red.

Script I – populating data with 85% logical scan fragmentation

Script I below populates the test table with 2,000,000 rows. The clustered index key column is assigned values that alternate between small and large with the intention to fragment the clustered index.

create table test(c1 int, c2 int, c3 char(1000) default ' ')

create clustered index cix_test on test(c2)

set nocount on

declare @i int

set @i = 1

begin tran

while @i <= 2000000

begin

insert test(c1, c2)

select @i,

case when @i % 2 = 0 then @i else 2000000 - @i end

if @i % 100000 = 0

begin

commit tran

begin tran

end

set @i = @i + 1

end

commit tran

At the completion of Script I, the test table can be summarized as follows:

Property	Value
Logical scan fragmentation	~85%
Average page density	~54%
Pages scanned	466,668
Extents scanned	300,131
Rows	2,000,000
Reserved space	~3.6GB

Script II – populating data with 100% logical scan fragmentation

Script II below populates the test table with 3,000,000 rows. That’s right, 3,000,000 instead of 2,000,000. The clustered index key column is assigned values in the order exactly opposite of that for the clustered index.

create table test(c1 int, c2 int, c3 char(1000) default ' ')

create clustered index cix_test on test(c2)

set nocount on

declare @i int

set @i = 1

begin tran

while @i <= 3000000

begin

insert test(c1, c2)

select @i,

3000000 - @i; -- from high to low

if @i % 100000 = 0

begin

commit tran

begin tran

end

set @i = @i + 1

end

commit tran

At the completion of Script II, the test table can be summarized as follows:

Property	Value
Logical scan fragmentation	100%
Average page density	~88%
Pages scanned	428,572
Extents scanned	53,772
Rows	3,000,000
Reserved space	~3.3GB

Script III – the test query

At the completion of Script I and Script II, the following test script is run:

dbcc dropcleanbuffers

select COUNT(*) from test;

The test results

And the elapsed time of the SELECT statement is timed. The following table shows the performance of the test Script III:

Data	*SELECT COUNT() Performance (sec)**
Script I (85% fragmentation)	100
Script II (100% fragmentation)	27

The following chart shows the same data in the above table:

Observations

Note that since Script II populates the test in a more compact fashion (avg. page density = ~88% as opposed ~54% for Script I) and there would be fewer pages if the same number of rows are entered by both scripts. So, to make the comparison meaningful, I increased the number of rows for Script II to 3,000,000, and this resulted in the test table to have similar number of pages and similar sizes.

Why does the table scan performed significantly faster with the data that was 100% fragmented than with the data that was 85% fragmented? In fact, the query performed four times faster with the data that was 100% fragmented.

The key reason is that even though the data was 100% fragmented in terms of DBCC SHOWCONTIG or sys.dm_db_index_physical_stats, the pages were still considered ‘contiguous’, and SQL Server was able to post large reads and drove the I/O subsystem with significantly higher read-ahead reads.

More specifically, with the data populated by Script II (logical scan fragmentation = 100%), SQL Server was able to process the test query with significantly larger reads (~360KB/read vs. ~14KB ~37KB/read for the Script I data) and with significantly higher readahead reads (11,000~12,000 readahead pages/sec vs. 3,600~6,000 readahead pages/sec for the Script I data).

So, higher logical scan fragmentation does not necessarily mean worse table/index scan performance.

HP Oracle 10X Extreme Performance Data Warehouse with Exadata Storage Server

jchang — Tue, 09 Jun 2009 23:26:00 GMT

For several months, we have seen ads for the joint HP/Oracle RAC and Exadata storage combination talking about extreme performance (10X faster) for large data warehouses. One thing I like about Oracle is that they have courage to pursue technology with deep hardware design implications, even if it takes several iterations to iron out the major issues. I just got around to looking through the Oracle papers on this. Like OPS/RAC, the Exadata technology has implications for how hardware is built. Hardware vendors can be squeamish on designing silicon for special requirements if there is not a viable installed base. This leads to the chicken and egg, which comes first situation that many other vendors cannot successfully initiate. Oracle dares to do this, and amazingly get customers to shell out big chunks of money on the first iteration. This in turn provides justification for ~~gutless~~ risk averse hardware vendors to do their part.

I will start by saying that the SAN systems out there today are designed for transaction processing, not data warehousing. Most SAN systems are designed with a certain number of FC ports, with the intent of supporting 1-4 disk enclosures (typically 15 disk drives each) per FC port. The SAN controller (or service processor) is a significant portion of the overall cost. Configuring one enclosure per FC port leads to a higher amortized cost per disk than having multiple enclosures per FC port, but sequential bandwidth is still limited by the number of FC ports. Depending on the architecture, it is possible to sustain between 330MB/sec (loop architecture) and 390MB/sec (star architecture) per 4Gbit/sec FC port. So it can require 3 FC ports and 45 disks to support 1GB/sec, even though each individual 15K disk drive can sustain 125-160MB/sec. The amortized cost of a SAN system might be $2,000 per disk, so each 1GB/sec through-put costs around $90K.

This is why I have advocated direct attach storage for data warehouses, where each 15 disk enclosure can sustain 800-1000MB/sec at an amortized cost of about $500 per disk. But most people do not like inexpensive high-performance storage solutions. And none of the expensive SAN systems provide sufficient bandwidth for really high-end data warehouse systems.

In the Exadata system, the interconnect between host and storage is InfiniBand, which signals at 5Gbit/s using a x4 wide connector (like SAS) for a net bandwidth (after 8B/10B encoding) of 16Gbit/s or 2GB/sec. The Exadata Storage Server (or cell) is an HP DL180G5 with 2 Xeon E5430 quad-core 2.66GHz processors, 8GB memory, a P400 RAID controller, 12 450GB 15K SAS or 1TB 7200 RPM SATA disks and dual-port Infiniband HCA. Curiously, of the 5.4TB raw storage with 12 450GB drives, only 1.5TB is available. With RAID 10 overhead, there is 2.7TB. Some space is required for internal use, but 1.2TB seems to be rather large.

A complete pre-configured HP/Oracle Database Machine full rack comprises 8 HP DL360 servers with two Xeon E5430 quad-core processors, and 32GB memory for running Oracle RAC, 14 Exadata storage cells, 4 InfiniBand switches (and 1 Gigabit Ethernet switch for auxiliary communications). A half-rack has half of the above components. Each storage cell is listed as supporting 1,000MB/sec with SAS drives and 750MB/sec with SATA drives. The listed bandwidth for the pre-configured full-rack is 14GB/sec. It is stated that Exadata bandwidth scales linearly with the number of racks, but without explicit performance numbers.

Compare the Exadata cell with an EMC CLARiiON CX4-960 mid-range SAN. The CX4-960 comprises 2 SPs, each with two quad-core processors, 16GB memory per SP, for which the minimum meaningful configuration is 16 disk enclosures (240 disks) over 16 FC ports. So the resource allocation per SP is 2 quad-core processors, 16GB memory and 120 disk, with probable sequential bandwidth of 3GB/sec (380MB/sec per FC port). The Exadata cell provides approximately the same compute power, 8GB memory, for 12 disks targeting 1GB/sec sequential bandwidth.

The purpose of the massive computer power per disk in the Exadata cell relative to a standard SAN is to offload compute functions from the main database engine. Concentrating capability, be it compute power or IO bandwidth, in a single system is always difficult, so distributing work can be useful if it can be done effectively. One candidate is compression. (SQL Server 2008 can store tables and indexes with row or page level compression, which take CPU resources. Oracle probably has comparable capability as well.) [Exadata is for Oracle systems only?] Offloading this to the storage element might be desirable. Finally, the Exadata cell is not just a storage engine, but can also handle database protocols.

In addition to a command such as fetch this block, and decompress, the Exadata can also handle SELECT * FROM Table WHERE col = ‘SARG’ (Smart Scan Offload Processing). In a data warehouse, that expectation is that queries are ad-hoc, for which indexes have not been built. So a data warehouse must be able to power through very large table scans. This requires both IO bandwidth and CPU resources, as a database table scan is not a simple IO operation (see my other posts on this matter).

The very recent TPC-H report for the HP BladeSystem of June 3, 2009 uses the Oracle Exadata Storage Servers (more on this below) and has price information. The full cost for 6 Exadata storage cells and supporting components is $536,516. (Of this, each Exadata Storage Server is $24,000, and the cost of the Exadata software is $360,000. Interesting, the price of a similarly configure DL180G5 is $14,000.) Three-year support is another $479,846 for a total 3-year cost of approximately $1M. It is unclear how much of the discount applies to the Exadata versus the Oracle database software. The amortized list price per cell is $166K or 14K per disk. That Oracle sells this clearly sends the message that people do not look at cheap hardware for the data center.

Now when HP published a 1000GB TPC-H report for a HP Superdome with 32 Itanium 2 9140 sockets, and 64-cores on April 29, 2009, I was very puzzled. What was the purpose of this publication? HP had already published a 10TB result on a Superdome with 64 sockets (128 cores) of the same Itanium 2 9140 processors back in March 2008 with no real disparity (acknowledging that results at different sizes are not directly comparable). Then about one month later, HP published the 64-node Oracle RAC with Exadata storage result of June 2009 (note that the RAC servers are BL460 with 2 Xeon quad-core 3GHz processors, different from the pre-configured database machine).

System	SuperDome	BL460 Cluster
Database	Oracle 11g + Partitioning	Oracle 11gR2, RAC
QphH@1000GB	123,323	1,166,976
TPC-H Power	118,577	782,608
TPC-H Throughput	128,259	1,740,122
Total System Cost	$2,532,527	$6,320,001
Processors	32 Itanium 9140 1.6GHz	128 X5450 3GHz
Cores	64	512
Memory	384GB	2080GB
Disks	768	6 Exadata Cells
HBA	64 Dual Port FC 4Gb/s	64 x 2 Infini-band

Was the intent that people would see the Oracle RAC with Exadata storage and draw conclusions based on the approximately 10X difference in performance with another recent 1000GB result?

What conclusions can we draw from each of the two results? The first matter to understand is that the TPC-H scale factor 1000GB means the LineItem table, data only, is approximately 1000GB in size. The full database with all tables and indexes is approximately 1700GB. So the entire database fits in the 512-core RAC system with 2080GB and not in the 64 core system with 384GB.

Next the 64 core Itanium system has 64 dual-port FC adapters, meaning 128 x 4Gbit/sec ports, which could support 42GB/sec based on 330MB/sec per 4Gbit/s FC port. But it is unlikely that 768 disk drives can sustain this volume (55MB/sec per disk) in a SAN. It is also interesting that system was configured with the EVA 4400 while other HP SuperDome Unix results employ the MSA1000 storage. (It is nice to have 32 EVA SAN systems or 256 MSA 1000s available for performance testing).

Note that TPC-H query 1 (a table scan of most of the LineItem table) takes 169.8 seconds on the 64 core Itanium, and 10.3 seconds on the 512 core RAC system. This means that if the data had to be read from disk, then the disk system would have to support 6GB/sec on the 64 core and 97MB/sec on the 512 core system. The 64 core Itanium system definitely has to read data from disk and the configured disk can easily support 6GB/sec (only 8MB/sec per disk) while the 512 core system has 6 Exadata storage systems which can support exactly 6GB/sec per specifications, nowhere near 100MB/sec. But all the data fits in memory so disk reads for data does not occur given the TPC-H sequence where the test run occurs after the database load and index build.

The data load time on the 64-core Itanium was 1:07:12 and 2:22:57 on the 512-core RAC, which also may indicate relative storage performance

Another point that can be noted is that there is an 8X difference in cores for a 9.5X difference in performance. However, the Core 2 architecture Xeon 3.0GHz cores (45nm) are much more powerful than the Itanium 2 1.6GHz cores (90nm). The gain in Power is 6.6X and 13.6X in through-put. TPC-H scored is based on a geometric mean of 22 queries, some of which are small and other large. The geometric mean has the effect that a 2X speedup in a small query has the small benefit as 2X in a large query. The issue is the getting 10X gain in a small query is very difficult so scaling on Power is attenuated.

In summary, the two performance reports are definitely sufficient to assert that Oracle RAC can scale, but having single node and 8 node performance reports for the BL460 would be confirm this. The two TPC-H reports say nothing of Exadata storage system performance, either in the sustainable sequential bandwidth or the value of the Smart Scan Offload processing. If the 64 node, 2080GB memory Exadata storage result had been reported at 10TB, then we might have an idea of its capabilities. Based on the 100GB/sec table scan estimate above, it would require 100 Exadata cells, which might beyond its actual scaling capabilities.

The performance data cited in Oracle’s Exadata whitepaper lack details to attribute the source of the performance gain. Given that most SAN systems are horribly configured for Data Warehouse performance, it is quite probable that dropping in the preconfigure full rack Exadata with 14GB/sec sustained table scans can easily generate the quoted numbers.

Notes on the Exadata Storage Server

At the time this product came out, the choice of the DL180G5 was reasonable. However, this system, based on the Intel 5100 chipset, has 1 x8 and 2 x4 PCI-E Gen 1 slots.

I am guessing that the Infini-band dual port HCA occupies the PCI-E x8 slot, the P400 RAID controller occupies one of the x4 slots and that the 12 internal disks are connected to one of two x4 SAS ports on the P400.

Technically, the each Infiniband DDR (5Gbit/s) x4 channel is 20Gbit/s, which after 8B/10B encoding is 16Gbit/sec (2GBytes/s) which could fully consume a x8 PCI-E Gen 1 channel. There are 2 IB channels for path redundancy, not bandwidth aggregation, as the x8 PCI-E bandwidth is limited to 2GB/sec.

Since the disk drives are probably on a single x4 SAS port, this bandwidth is limited to 1GB/sec, even though each 15K disk drive can do 160MB/sec (not accounting for RAID 10 implications). So while there is 4GB/s combined bandwidth on the Infiniband links, only 2GB/sec can be sent over the PCI-E port to the IB HCA, and only 1GB/s can be drive from the RAID controller.

Now that the DL180G6 is available, with 1 x16 and 2 x8 PCI-E Gen 2 channels, I would retain the IB dual port x4 (if it supports PCI-E gen 2), go to the new P410 RAID Controller, the 25-bay SFF drive bay (assuming the drive bays can be split across two x4 SAS channels), and 24 146GB 15K or 300GB 10K SFF drives (There is no point offering a 250GB SATA option). This unit should support 2GB/sec.

I might even be tempted to bug HP to split the drive bays 4 channels, 6 bays per channel, x4 SAS on each channel. There would be 2 P410 controllers, each driving 2 channels. The 6 disks in each channel might not drive 1GB/s, but 4 channels might support 3GB/s? I priced this around $19K.

To bad we cannot have a generic Infiniband SAN, and skip the Exadata software ($5K licensing per disk?).

T-SQL Exercise: Simplest data-loading script to produce worst query performance – June 7th Update

Linchi Shea — Mon, 08 Jun 2009 01:31:00 GMT

All the previously posted results (May 25^th and May 29^th) on this exercise were obtained with query parallelism disabled (i.e. the sp_configure ‘max degree of parallelism’ option was set to 1).

Since the following test query is sensitive to query parallelism, we need to see what impact query parallelism may have.

DBCC DROPCLEANBUFFERS

SELECT COUNT(*) FROM dbo.test;

It turns out that with parallelism the test query exhibited significantly different performance characteristics with the three data sets we have been using as test vehicles than without parallelism. And again, the nature of the storage system proved to be a significant confounding factor. The following table shows the results with query parallelism on both the internal drive (the C: drive) and the drive presented from a departmental level disk array (the E: drive), the same two drives used in the May 25^th update and the May 29^th update.

Drive	Test Run	Elapsed Time (second)
Internal (C:)	Adam’s script – test run 1	90
	Adam’s script – test run 2	88
	Adam’s script – test run 3	87
	Tibor’s script – test run 1	217
	Tibor’s script – test run 2	219
	Tibor’s script – test run 3	226
	Linchi’s script – test run 1	325
	Linchi’s script – test run 2	324
	Linchi’s script – test run 3	324
Disk array (E:)	Adam’s script – test run 1	74
	Adam’s script – test run 2	75
	Adam’s script – test run 3	72
	Tibor’s script – test run 1	93
	Tibor’s script – test run 2	97
	Tibor’s script – test run 3	89
	Linchi’s script – test run 1	51
	Linchi’s script – test run 2	51
	Linchi’s script – test run 3	51

Note the dramatic change in the relative impact of the data sets on the test query when the storage performance and query parallelism were added to the mix. In particular, note how the test query was able to perform much faster on the faster drive (drive E:) with some data sets.

I just want to present the data points in this post, and will dive in a bit in the next follow up.

T-SQL Exercise: Simplest data-loading script to produce worst query performance – May 29th Update

Linchi Shea — Fri, 29 May 2009 16:23:00 GMT

This is another follow-up on the T-SQL exercise.

So the test query below is rather simple:

DBCC DROPCLEANBUFFERS

SELECT COUNT(*) FROM dbo.test;

But beneath its simple appearance, many factors are at play and interact in a complex way to influence the query performance. In other words, trying to predict its performance is not trivial. You may do better than predicting the stock market performance. But still it can be a rather unreliable business.

So in the May 25^th update, I reported on the results of running the test queries with the data file on a RAID-1 set with two internal disks. Yesterday, I triple checked the test result and the result could be re-produced at will.

To see whether or what impact a different storage system may have on the performance, I placed the data file on a departmental disk array behind a SAN. Unlike a typical enterprise class disk array, this departmental disk array does not have a huge amount of cache. In addition, the data block placement is effectively virtualized in that it uses a mapping table to place data across all the drives, making it effectively impossible to know or control where the data is placed at the OS level.

With the data file on this disk array, I repeated the same tests. Each time before the data was loaded, the test table was dropped and the database was empty with no user data.

Again, after the data was loaded by each script, the test query (including DBCC DROPCLEANBUFFERS) was run three times, and each time the query elapsed time was logged. Additional validation test runs were performed to ensure that the test results were re-producible, and they are re-producible.

The following table shows the recorded elapsed times.

Test run	Elapsed time (second)
Adam’s script – test run 1	74
Adam’s script – test run 2	82
Adam’s script – test run 3	81
Tibor’s script – test run 1	248
Tibor’s script – test run 2	235
Tibor’s script – test run 3	239
Linchi’s script – test run 1	155
Linchi’s script – test run 2	146
Linchi’s script – test run 3	148

First thing to note from the above table is that the ranking of the test query performance changed significantly. In this setup, Tibor’s data set caused the test query to have the worst performance.

But what is most striking is that while the test query performed significantly better with Adam’s and my data sets on the disk array than they did on the internal disks, the test query performed noticeably worse with Tibor’s data set on the disk array than it did on the internal disks.

This is getting more interesting. And I might add that this is yet another piece of evidence on the conspiracy of the every-table-must-have-a-clustered-index crowd :-)

I’ll come back to explore what might be the cause later.