Search results matching tag 'Hardware'

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.

The Zombie PerfMon Counter That Never Dies! Quick Tip

KKline — Mon, 08 Oct 2012 11:55:00 GMT

The PerfMon Counters That Just Won't Die

One of the things that's simultaneously great and horrible about the Internet is that once something gets posted out in the ether, it basically never goes away. (Some day, politicians will realize this. We can easily fact check their consistency). Because of longevity of content posted to the Internet, a lot of performance tuning topics become "zombies". We shoot 'em in dead, but they keep coming back!

In other words, those old recommendations were a suggested best practices for long ago, for a specific version of SQL Server, but are now inappropriately for the newer version. It's not uncommon for me, when speaking at a conference, to encounter someone who's still clinging to settings and techniques which haven't been good practice since the days of SQL Server 2000. Here's an example of Microsoft SQL Server 2000 Best Practices that are very version-specific.

So here's an example. The %Disk Time counter and the Disk Queue Length were heavily recommended as a key performance indicator for IO performance. SQL Server throws a lot of IO at the disks using scatter/gather to maximize the utilization of the disk-based IO subsystem. This approach leads to short bursts of long queue depths during checkpoints and readaheads for an instance of SQL Server. Sometimes the server workload is such that your disk can't keep up with the IO shoved at it and when that happens, you'll see long queue lengths too. The short burst scenario isn't a problem. The lengthening queue length scenario usually is a problem. So is that a good practice?

In a word, not-so-much.

Those counters can still be of some use on an instance of SQL Server which only has one hard disk drive. But that's exceedingly rare these days. Why?

The PerfMon counter %Disk time is a bogus performance metric for several reasons. It does not take into account asynchronous I/O requests. It can't tell what the real performance profile is for an underlying RAID set may be, since they contain multiple disk drives. The PerfMon counter Disk Queue Length is also mostly useless, except on SQL Server's with a single physical disk, because the hard disk controller cache obfuscates how many IO operations are actually pending on the queue or not. In fact, some hard disks even have tiny write caches as well, which further muddies the water was to whether the IO is truly queued, in a cache somewhere between the OS and the disk, or has finally made it all the way to the CMOS on the disk.

Better IO PerfMon Counters

Instead of using those PerfMon counters, use the Ave Disk Reads /sec, Avg Disk Write /sec, and Avg Disk Transfers/sec to track the performance of disk subsystems. These counters track the average number of read IOs, write IOs, and combined read and write IOs to occured in the last second. Occassionally, I like to track the same metrics by volume of data rather than the rate of IO operations. So, to get that data, you may wish to give these volume-specific PerfMon counters a try: Avg Disk Transfer Bytes/sec, Ave Disk Read Bytes /sec, and Avg Disk Write Bytes/sec

For SQL Server IO Performance, Use Dynamic Management Views (DMV)

And unless you've been living in a cave, you should make sure to use SQL Server's Dynamic Management Views (DMVs) to check on IO performance for recent versions of SQL Server. Some of my favorite DMV's for IO include:

Sys.dm_os_wait_stats
Sys.dm_os_waiting_tasks
Sys.dm_os_performance_counters
Sys.dm_io_virtual_file_stats
Sys.dm_io_pending_io_requests
Sys.dm_db_index_operational_stats
Sys.dm_db_index_usage_stats

Many of these DMVs are fully document in this Books Online article here at Microsoft SQL Server 2012 Index Related Dynamic Management Views and Functions.

So how are you tracking IO performance metrics? Which ones are you using?

I look forward to hearing back from you!

Enjoy,

-Kev

-Follow me on Twitter!

Intel Xeon E5 (Sandy Bridge-EP) and SQL Server 2012 Benchmarks

jchang — Wed, 07 Mar 2012 23:53:00 GMT

Intel officially announced the Xeon E5 2600 series processor based on Sandy Bridge-EP variant with upto 8 cores and 20MB LLC per socket. Only one TPC benchmark accompanied product launch, summary below.

Processors	Cores per	Frequency	Memory	SQL	Vendor	TPC-E
2 x Xeon E5-2690	8	2.9GHz	512GB (16x32GB)	2012	IBM	1,863.23
2 x Xeon E7-2870	10	2.4GHz	512GB (32x16GB)	2008R2	IBM	1,560.70
2 x Xeon X5690	6	3.46GHz	192GB (12x16GB)	2008R2	HP	1,284.14

Note: the HP report lists SQL Server 2008 R2 Enterprise Edition licenses at $23,370 per socket.
The first IBM report lists SQL Server 2012 Enterprise Edition licenses at $13,473 per pair of cores(?) or $53,892 per socket. All results used SSD storage. The IBM E7 result used eMLC SSDs, the IBM E5 results showed more expensive SSDs, but did not explicitly say SLC?.

The Xeon E5 superceeds 2-socket systems based on both the Xeon 5600 (Westmere-EP) and Xeon E7 (Westmere-EX). It is evident that Sandy Bridge improves performance over Westmere at both the socket and core levels and also on a GHz basis.

Architecture	Total Cores	Frequency	Core-GHz	TPC-E	tps-E per core-GHz
Sandy Bridge-EP	2 x 8 = 16	2.9GHz	46.4	1,863.23	40.16
Westmere-EX	2 x 10 = 20	2.4GHz	48.0	1,560.70	32.51
Westmere-EP	2 x 6 = 12	3.46GHz	41.52	1,284.14	30.93

One advantage of the Xeon E7 (Westmere-EX) system is that the memory expanders support for 4 DIMMs per channel or 16 DIMMs per socket (4 memory channels). However, a two-socket Sandy Bridge-EP system supports 256GB with 16 (8 per socket) of the lower price (per GB) 16GB DIMMs. And really, 256GB is more than enough for most situations, so it is quite reasonable to not burden outlier configuration requirements on the large majority.

A later version of the Xeon E5 will support 4-socket systems. There is no explanation as to whether glue-less 8-socket systems will be supported in the future. It was previously discussed that there would a EN variant of Sandy Bridge with 3 memory channels and fewer PCI-E lanes.

Hardware Strategy for SQL Server 2012 per core licensing
Top frequency on the 6 core E5-2667 is 2.9GHz, the same as the 8 core (excluding the 8 core 2687W model at 3.1GHz). Top frequency for the 4 core E5-2643 and 2 core E5-2637 are 3.3 and 3.0GHz respectively. The desktop i7-2830 is 3.6GHz with 4 cores, so Intel is deliberately constraining the top frequency on 2 & 4 core version for the server parts, apparently to favor interest in the 8 core part.

Given the SQL Server 2012 per core licensing, there should be interest in a system with fewer cores per socket running at higher frequency, while taking advantage of the high memory and IO bandwith of the E5 system. Consider also that SQL Server write operations (Insert, Update, Delete, the final stage of index builds) and even certain SELECT operations are not parallel (the Sequence Project operator that support the ROW_NUMBER function).

I think it would also make sense for Intel to allow cores to be disabled in BIOS (now UEFI) on the top of line E5-2690 like the desktop extreme edition unlocked processors. Large corporate customers can buy a batch of identical systems, disabling cores that are not needed on individul systems.

It would also be of value to engage a (nolonger quite so, relative to core licenses) exhorbitantly priced consultant to tune SQL Server to run on fewer cores.(Not to be construed as a solicitation for services)

Accelerate OLTP with HP and Microsoft's New High Performance Reference Architecture

KKline — Tue, 06 Mar 2012 15:25:00 GMT

If you haven't started to read Shashank Pawar (blog), you're missing out. Shashank is part of Microsoft Australia and has been writing some very good content lately. Here's an example from the Reference Architecture for High Performance SQL Server:

HP and Microsoft engineering teams have worked together to create a reference architecture to Accelerate Online Transaction Processing (OLTP) database workloads with a fully-flash based HP/Microsoft architecture and achieve significant performance increases, simplified database manageability, and industry leading TCO.

The details come in a torrent after that leading paragraph with lots of pretty pictures and charts to help explain. This is great stuff, especially for competitive platforms such as Oracle Exadata.

Read more about the new HP High Performance Reference Architecture for SQL Server 2012 here.

And just out of curiousity, are any of you using high performance architectures such as Oracle Exadata, IBM Netezza, or Teradata? I'd love to hear your feedback, questions, and comments.

Enjoy,

-Kev

-Follow me on Twitter

-More on my KevinEKline.com

Intel Server Strategy Shift with Sandy Bridge EN & EP

jchang — Tue, 29 Nov 2011 19:04:00 GMT

The arrival of the Sandy Bridge EN and EP processors, expected in early 2012, will mark the completion of a significant shift in Intel server strategy. For the longest time 1995-2009, the strategy had been to focus on producing a premium processor designed for 4-way systems that might also be used in 8-way systems and higher. The objective for 2-way systems was use the desktop processor that later had a separate brand and different package & socket to leverage the low cost structure in driving volume. The implication was that components would be constrained by desktop cost requirements.

The Sandy Bridge collection will be comprised of one group for single processor systems designed for low cost, and one premium processor. The premium processor will support both the EN and EP product lines, the EN limited to 2-way, and the EP for both 2-way and 4-way systems, with more than adequate memory and IO in each category. The cost structure of both 2-way and 4-way increased from Core 2 to Nehalem, along with a significant boost in CPU, memory and IO capability. With quad-core available in 1P, the more price sensitive environments should move down to single processor systems. This allows 2 & 4-way systems to be built with balanced compute, memory and IO unconstrained by desktop cost requirements.

In other blogs, I had commented that the default system choice for database server for a long time had been a 4-way system should now be a 2-way since the introduction of Nehalem in mid-2009. Default choice means in the absence of detailed technical analysis, basically a rough guess. The Sandy Bridge EP, with 8 cores, 4 memory channels and 80 PCI-E lanes per socket in a 2-way system provides even stronger support for this strategy.

The glue-less 8-way capability of the Nehalem and Westmere EX line is not continued. One possibility is that 8-way systems do not need to be glue-less. The other is that 8-way systems are being abandoned, but I am inclined to think this is not the case.

The Master Plan

The foundation of the premium processor strategy, even though it may have been forgotten in the mists of time, not to mention personnel turnover, was that a large cache improves scaling at the 4-way multi-processor level for the shared bus SMP system architectures of the Intel Pentium to Xeon MP period. The 4-way server systems typically deployed with important applications that could easily justify a far higher cost structure than that of desktop components, but required critical capabilities not necessary in personal computers. Often systems in this category were fully configured with top line components whether needed or not.

Hence the Intel large cache strategy was an ideal match between premium processors and high budget systems for important applications. One aspect that people with an overly technical point of view have difficulty fathoming is that the non-technical VP's don't want their mission critical applications running on a cheap box. In fact, more expensive means that is must be better, and the most expensive is the best, right? From the Intel perspective, a large premium is necessary to amortize the substantial effort necessary to produce even a derivative processor in volumes small relative to desktop processors.

The low cost 2-way strategy was to explore demand for multi-processor systems in the desktop market. Servers were expected to be a natural fit for 2-way systems. Demand for 2-way servers exploded to such an extent that it was thought for a brief moment there would be no further interest for single processor servers. Eventually, the situation sorted itself out, in part with the increasing power of processors. Server unit volume settled to a 30/60/10 split between single, dual and quad processors (this is old data, I am not sure what the split is today). The 8-way and higher unit volume is low, but potentially of importance in having a complete system lineup.

AMD followed a different strategy based on the characteristics of thier platform. The Hyper-Transport (HT) interconnect and integrated memory controller architecture did not have a hard requirement for large cache to support 4-way and above. So AMD elected to pursue a premium product strategy on the number of HT links. Single processor systems require one HT to connect the IO hub. Two HT is required in a 2-way system, one HT connecting to IO, and another to the second processor. Three HT could support 4-way and higher with various connection arrangements. The pricing structure is based on the number of HT links enabled, on the theory that the processor has higher value in big systems than in small systems.

What Actually Happened

Even with the low cost structure Intel enabled in 2-way, desktop systems remained and actually became defined as single processor. Instead, the 2-way systems at the desk of users became the workstation category. This might have been because the RISC/UNIX system vendors sold workstations. The Intel workstations quickly obliterated RISC workstations, and there have been no RISC workstations for sometime? Only two RISC architectures are present today, having retreated to the very high-end server space, where Intel does not venture.

Itanium was supposed to participate in this space, but the surviving RISC vendors optimized at 8-way and higher. Intel would not let go of the 4-way system volume and Itanium was squeezed by Xeon at 4-way and below, yet could not match IBM Power in high SMP scaling. To do so would incur a high price burden on 4-way systems. One other aspect of Intel server strategy of the time was the narrow minded focus on optimizing for a single platform.

Most of the time, this was the 4-way server. There was so much emphasis on 4-way that there actually 2 reference platforms, almost to the exclusion of all else. For a brief period in 1998 or so, there was an incident of group hysteria that 8-way would become the standard high volume server. But this phase wore off eventually. The SPARC was perhaps the weakest of the RISC at the processor level. Yet the Sun strategy to design for a broad range of platforms from 2-way to 30-way, (then with luck 64-way via acquisition of one of the Cray spin-offs) was successful until their processor fell too far behind.

After the initial implementation of the high volume 2-way strategy, desktop systems became intensely price sensitive. The 2-way workstations and server system were in fact not price sensitive even though it was thought they were. It became clear that desktops could not incur any burden to support 2-way capability. The desktop processor for 2-way systems was put into a different package and socket, and was given the Xeon brand.

Other cost reduction techniques were implemented over the next several generations as practical on timing and having the right level of maturity. The main avenue is integration of components to reduce part count. This freed 2-way system from desktop cost constraints, but as with desktops, it would take several generations to evolve into a properly balanced architecture.

The 4-way capable processors remained on a premium derivative, given the Xeon MP brand in the early Pentium 4 architecture (or NetBurst) period. To provide job security for marketing people, 2-way processors were then became the Xeon 5000 series, and 4-way the Xeon 7000 series in the late NetBurst to 2010 period. In 2011, the new branding scheme is E3 for 1P servers, E5 for 2-way and E7 for 4-way and higher. Presumably each branding adjustment requires changes to thousands of slidedecks.

At first, Intel thought both 2-way and 4-way systems had high demand versus cost elasticity. If cost could be reduced, there would be substantially higher volume. Chipsets (MCH and IOH) had overly aggressive cost objectives that limited in memory and IO capability. In fact, 4-way systems had probably already fallen below the boundary of demand elasticity.

The same may have been true for 2-way systems, as people began to realize that single processor systems were just fine for entry server requirements. For Pentium II and III 2-way systems, Intel only had a desktop chipset. In 2005-6, Intel was finally able to produce a viable chipset for 2-way systems (E7500? or 5000P) that provided memory and IO capability beyond desktop systems. Previously, the major vendors elected for chipsets from ServerWorks.

It was also thought at the time that there was not a requirement for premium processors in 2-way server systems. The more correct interpretation was that the large (and initially faster) cache of premium processors did not contribute sufficient value for 2-way systems. A large cache does improve performance in 2-way systems, but not to the degree that it does at the 4-way level. So the better strategy by far on performance above the baseline 2-way system with standard desktop processors was to step up to a 4-way system with the low-end premium processors instead of a 2-way system with the bigger cache premium processors.

And as events turned out, the 4-way premium processors lagged desktop processors in transitions to new microarchitectures and manufacturing processes by 1 full year or more. The 2-way server on the newer technology of the latest desktop processors was better than a large cache processor of the previous generation, especially one that carried a large price premium. So the repackaged desktop processor was the better option for 2-way systems

The advent of multi-core enabled premium processors to be a viable concept for 2-way systems. A dual-core processor has much more compute capability than a single core and the same for a quad-core over dual-core in any system, not just 4-way, provided that there not too much difference in frequency. The power versus frequency characteristics of microprocessors clearly favors multiple cores for code that scale with threads, as in any properly architected server application.

However, multi-core at the dual and quad-core level was employed for desktop processors. So the processors for 2-way servers did not have a significant premium in capability relative to desktops. The Intel server strategy remained big cache processors. There was the exception of Tigerton, when two standard desktop dual-core processor die in the Xeon MP socket was employed for the 4-way system, until a large cache variant was readied in the next generation Dunnington processor incorporated a large cache. This also happened for the Paxville and Tulsa.

System Architecture Evolution from Core 2 to Sandy Bridge

The figure below shows 4-way and 2-way server architecture evolution relative to single processor desktops (and servers too) from 45nm Core 2 to Nehalem & Westmere and then to Sandy Bridge. Nehalem systems are not shown for space considerations, but are discussed below.

System architecture from Penryn to Westmere to Sandy Bridge, (Nehalem not shown)

The Core 2 architecture was the last Intel processor to use the shared bus, which allows multiple devices, processors and bridge chips, to share a bus with a protocol to arbitrate for control of the bus. It was called the front-side bus (FSB) because there was once a back-side bus for cache. When cache was brought on-die more than 10 years ago, the BSB was no more. By the Core 2 period, to support higher bus frequency, the number of devices was reduced to 2, but the shared bus protocol was not changed. The FSB was only pushed to 1066MHz for Xeon MP, 1333MHz for 2-way servers, and 1600MHz for 2-way workstations.

Nehalem was the first Intel processor with a true point-to-protocol, Quick Path Interconnect (QPI), at 6.4GHz transfer rate, achieving much higher bandwidth to pin efficiency than possible over shared bus. Intel had previously employed a point-to-point protocol for connecting nodes of an Itanium system back in 2002. (AMD implemented point-to-point with HT for Opteron in 2003? at an initial signaling rate of 1.6GHz?) Shared bus also has bus arbitration overhead in addition to lower frequency of operation. The other limitation of Intel processors up to Core 2, was the concentration of signals on the memory controller hub (also known as North Bridge) for processors, memory and PCI-E. The 7300 MCH for the 4-way Core 2 has 2013-pins, which is at the practical limit, and yet the memory and IO bandwidth is somewhat inadequate.

Nehalem and Westmere implement a massive increase in memory and PCI-E bandwidth (number of channels or ports) for the 2-way and 4-way systems compared to their Core 2 counterparts. Both Nehalem 2-way and 4-way systems have significantly higher cost structure than Core 2. Previously, Intel had been mindlessly obsessed with reducing system to the detriment of balanced memory and IO. This shows Intel recognized that their multi-processor systems were already below the price-demand elasticity point, and it was time to rebalance memory and IO bandwidth, now possible with point to point interconnect and the integrated memory controller.

QPI in Nehalem required an extra chip to bridge the processor to PCI-E. This was not an issue for multi-processor systems, but was undesirable for the hyper sensitive cost structure of desktop systems. The lead quad-core 45nm Nehalem processor with 3 memory channels and 2 QPI ports in a LGA 1366 socket was followed by a quad-core, 2-memory channel derivative (Lynnfield) with 16 PCI-E plus DMI replacing QPI in a LGA 1156 socket. The previously planned dual-core Nehalem on 45nm was cancelled. Nehalem with QPI was employed in the desktop extreme line, while the quad-core without QPI was employed in the high-end of the regular desktop line.

The lead 32nm Westmere was a dual-core with the same LGA 1156 socket (memory and IO) as Lynnfield. Per the desktop and mobile objective, cost structure was reduced with integration, with 1 processor die and potentially a graphics die in the same package, and just 1 other component the PCH.

The follow-on Westmere derivative was a six-core using the same LGA 1366 socket as Nehalem, i.e., 3 memory channels and 2 QPI. This began the separation process of desktop and other single processor systems from multi-processor server and workstation systems. Extreme desktops employ the higher tier components designed for 2-way, but are still single-socket systems. I suppose that a 2-way extreme system is a workstation. Gamers will have settle for the mundane look of a typical workstation chassis.

With the full set of Sandy Bridge derivatives, the server strategy transition will be complete. Multi-processor products, even for 2-way, are completely separated from desktops without the requirement to meet desktop cost structure constraints. With desktops interested only in dual and quad-core, a premium product strategy can be built for 2-way and above around both the number of cores and QPI links.

The Sandy Bridge premium processor has 8 cores, 4 memory channels, 2 QPI, 40 PCI-E lanes and DMI (that can function as x4 PCI-E). The high-end EP line in a LGA 2011 socket will have full memory, QPI and PCI-E capability. The EN line in LGA 1356 socket will have 3 memory channels, 1 QPI and 24 PCI-E lanes plus DMI to supports up to 2-way systems, and will be suitable for lower priced systems. Extreme desktops will use the LGA 2011 socket, but without QPI.

What is interesting is that the 4-way capable Sandy Bridge EP line is targeted at both 2-way and 4-way systems. This is a departure from the old Intel strategy of premium processors for 4-way and up. Since the basis of the old strategy is no longer valid, of course a new strategy should be formulated. But too often, people only remember the rules of the strategy, not the basis. And hence blindly follow the old strategy even when it is no longer valid (does this sound familiar?)

This element of a premium 2-way system actually started with the Xeon 6500 line based on Nehalem-EX. Nehalem-EX was designed for 4-way and higher with eight-cores, 4 memory channels supporting 16 DIMMs per processor and 4 QPI links. A 2-way Nehalem-EX with 8 cores, 16 DIMMs per socket might be viable versus Nehalem at 4 cores, 9 DIMMs per socket, even though the EX top frequency 2.26GHz versus 2.93GHz and higher in Nehalem. The more consequential hindrance was that Nehalem-EX did not enter production until Westmere-EP was also in production, with 6 cores per socket at 3.33GHz. So the Sandy-Bridge EP line will provide a better indicator for premium 2-way systems.

The Future of 8-way and the EX line

There is no EX line with Sandy Bridge. Given the relatively low volume of 8-way systems, it is better not to burden the processor used by 4-way systems with glue-less 8-way capability. Glue-less means that the processors can be directly connected without the need for additional bridge chips. This both lowers cost and standardizes multi-processor system architecture, which is probably one of the cornerstones for the success Intel achieved in MP systems. I am expecting that 8-way systems are not being abandoned, but rather a system architecture with "glue" will be employed.

Since 8-way systems are a specialized very high-end category, this would suggest a glued system architecture is more practical in terms of effort than a subsequent 22nm Ivy Bridge EX. Below are two of my suggestions for 8-way Sandy Bridge or perhaps Ivy Bridges depending on when components could be available. The first has two 4-port QPI switch, or cross-bar or routers connecting four nodes with 2 processors per node.

The second system below has two 8-port QPI switches connecting single processor nodes.

The 2 processor node architecture would be economical, but I am inclined to recommend building the 8-port QPI switch. Should the 2 processor node prove to be workable, then a 16-way system would be possible. Both are purely speculative as Intel does not solicit my advice on server system architecture and strategy, not even back in 1997-99.

In looking at the HP DL980 diagram, I am thinking that the HP node controllers would support Sandy Bridge EP in an 8-way system.

There are cache coherency implications (Directory based versus Snoop) that are beyond the scope for database server oriented topic. There was an IBM or Sun discussion transactional memory. I would really like to see some innovation on handling locks. This is critical to database performance and scaling. For example, the database engine ensures exclusive access to a row, i.e., memory, before allowing access. Then why does the system architecture need to do a complex cache coherency check when the application has already done so? I had also previously discussed SIMD instructions to improve handling of page and row base storage, SIMD Extensions for the Database Storage Engine (same here).

If that were not enough, I had also called for splitting the memory system. Over the period of Intel multi-processor systems 1995 to 2011, practical system memory has increased from 2GB to 2TB. Most of the new memory capacity is used for data buffers. The exceptionally large capacity of the memory system also means that it cannot be brought very close to the processor, as into to the same package/socket.

So the memory architecture should be split into a small segment that needs super low latency byte addressability. The huge data buffer portion could be changed to block access. If so, then perhaps the database page organization should also be changed to make the metadata access more efficient in terms of modern processor architecture to reduce the impact of off-die memory access by making full use of cache line organization. The NAND people are also arguing for Storage Class Memory, something along the lines of NAND used as memory.

More on QDMPA System Architecture. and Sandy Bridge.

TPC-H Benchmarks - Westmere-EX versus RISC

jchang — Mon, 10 Oct 2011 20:03:00 GMT

There has been relatively litle activity in TPC Benchmarks recently with the exception of the raft of Dell TPC-H results with Exa Solutions. It could be that systems today are so powerful that few people feel the need for benchmarks. IBM published an 8-way Xeon E7 (Westmere-EX) TPC-E result of 4593 in August, slightly higher than the Fujitsu result of 4555, published in May 2011. Both systems have 2TB memory. IBM prices 16GB DIMMs at $899 each, $115K for 2TB or $57.5K per TB. (I think a 16MB DIMM was $600+ back in 1995!) The Fujistu system has 384 SSDs of the 60GB SLC variety, $1014 each, and IBM employed 143 SSDs of the 200GB eMLC variety, $1800 each for 24-28TB raw capacity respectively. Except for unusually write intensive situations, eMLC or even regular MLC is probably good enough for most environments.

HP published a TPC-H 1TB of 219,887.p QphH for their 8-way ProLiant DL980 G7 with the Xeon E7-4870, 26% higher in the overall composite score than the IBM x3580 with the Xeon E7-8870 (essentially the same processor). The HP scores 16% higher in power and 37.7% higher in throughput. Both throughput tests were with 7 streams. The HP system had Hyper-Threading enabled (80 physical cores, 160 logical) while the IBM system did not. Both systems had 2TB memory, more than sufficient to hold the entire database, data and indexes in memory. The IBM system had 7 PCI-E SSDs and the HP system has 416 HDDs over 26 D2700 disk enclosures, 10 LSI SAS RAID controllers, 3 P411 and 1 dual-port 8Gbps FC controller.

Also of interest are TPC-H 1TB reports published for the 16-way SPARC M8000 (June 2011) with SPARC64 VII+ processors and the 4-way SPARC T4-4 (Sep 2011). The table below shows configuration information for recent TPC-H 1000GB results.

TPC-H 1000GB	IBM x3850 X5	HP ProLiant DL980 G7	IBM Power 780	SPARC M8000	SPARC T4-4
DBMS	SQL 2K8R2 EE	SQL 2K8R2 EE	Sybase IQ ASE 15.2	Oracle 11g R2	Oracle 11g R2
Processors	8 Xeon E7	8 Xeon E7	8 POWER7	16 SPARC64 VII+	4 SPARC T4
Cores Threads	80-80	80-160	32-128	64-128	32-256
Memory	2048TB	2048TB	512GB	512GB	512GB
IO Controllers	7	13	12	4 Arrays	4 Arrays
HDD/SSD	7 SSD	416 HDD	52 SSD	4x80 SSD	4x80 SSD

The figure below shows TPC-H 1000GB power, throughput and QphH composite scores for 4 x Xeon 7560 (32 cores, 64 threads), two 8 x Xeon E7 (80 cores, 80 and 160 threads) systems, 8 x POWER7 (32 cores, 128 threads) 16 SPARC64 VII+ (64 cores, 128 threads) and the 4 SPARC T4 (32 cores, 256 threads).

TPC-H SF 1000 Results

The HP 8-way Xeon and both Oracle/Sun systems, one with 16 sockets and the newest with 4 SPARC T4 processors, are comparable, within 10%.

An important point is that both Oracle/Sun and the IBM Power systems are configured with 512GB memory versus 2TB for the 8-way Xeon E7 systems, which enough to keep all data and indexes in memory. There is still disk IO for the initial data load and tempdb intermediate results. This good indication that Oracle and Sybase have been reasonably optimized on IO, in particular, when to use an index and when not to. I had previously raised the issue that the SQL Server query optimizer should consider the different characteristics of in-memory, DW optimized HDD storage (100MB/s per disk sequential) and SSD.

Sun clearly made tremendous improvements from the SPARC 64 VII+ to the T4, with the 4-way new system essentially matching the previous 16-way. Of course, the Sun had been lagging at the individual processor socket level until now. The most interesting aspect is that the SPARC T4 has 8 threads per core. The expectation is that server applications have a great deal of pointer chasing code, that is: fetch memory which determines next address to fetch with inherently poor locality.

A modern microprocessor with core frequency 3GHz corresponds to a 0.33 nano-second clock cycle. Local node memory access time might be 50ns, or 150 CPU-clocks. Remote node memory acess time might be 100ns for a neighboring node to over 250ns for multi-hop nodes after cache-coherency is taken into account. So depending on how many instructions are required for each non-cached memory access, we can expect each thread or logical core to have many dead cycles, possibly enough to justify 8 threads per core. What is surprising is that Oracle published a TPC-H benchmark with their new T4-4 and not a TPC-C/E which is more likely to emphasize the pointer chasing code than DW.

Below are the 22 individual query times for the above systems in the power test (1 stream).

TPC-H SF 1000 Queries 1-22

Below are the 22 individual query power times for just the two 8 Xeon E7 systems. Overall, the HP system (with HT enabled) has 16% TPC-H power score, but the IBM system without HT is faster or comparable in 9 of the 22 queries. Not considering the difference in system architecture, the net might be attributed to HT?

TPC-H SF 1000 IBM and HP 8-way Xeon E7

Below are the 22 individual query power times for the HP 8 Xeon E7 and Oracle SPARC T4-4 systems.

TPC-H SF 1000 8-way HP Xeon E7 and 4-way SPARC T4

New Fusion ioDrive2 and ioDrive2 Duo

jchang — Tue, 04 Oct 2011 22:06:00 GMT

Fusion-iO just announced the new ioDrive2 and ioDrive2 Duo on Oct 2011 (at some conference of no importance). The MLC models will be available late November and the SLC models afterwards. See the Fusion-iO press release for more info.

Below are the Fusion-IO ioDrive2 and ioDrive2 Duo specifications. The general idea seems to be for the ioDrive2 to match the realizable bandwidth of a PCI-E gen2 x4 slot (1.6GB/s) and for the ioDrive2 Duo to match the bandwidth of a PCI-E gen2 x8 slot (3.2GB/s). I assume that there is a good explanation why most models have specifications slightly below the corresponding PCI-E limits.

The exception is that 365GB model at about 50% of the PCI-E g2 x4 limit. Suppose that the 785GB model implement parallelism with 16 channels and 4 die per channel. Rather than building the 365GB model with the same 16 channels, but a different NAND package with 2 die each, they just implemented 8 channels using the same 4 die per package. Lets see if Fusion explains this detail.

Fusion-IO ioDrive2

ioDrive2 Capacity	400GB	600GB	365GB	785GB	1.2TB
NAND Type	SLC (Single Level Cell)		MLC (Multi Level Cell)
Read Bandwidth (64kB)	1.4 GB/s	1.5 GB/s	710 MB/s	1.2 GB/s	1.3 GB/s
Write Bandwidth (64kB)	1.3 GB/s	1.3 GB/s	560 MB/s	1.0 GB/s	1.2 GB/s
Read IOPS (512 Byte)	351,000	352,000	84,000	87,000	92,000
Write IOPS (512 Byte)	511,000	514,000	502,000	509,000	512,000
Read Access Latency	47 µs	47 µs	68 µs	68 µs	68 µs
Write Access Latency	15 µs	15 µs	15 µs	15 µs	15 µs
Bus Interface	PCI-E Gen 2 x4
Price	$?	?	$5,950?	$?	?

Fusion-IO ioDrive2 Duo

ioDrive2 Capacity	1.2TB	2.4TB
NAND Type	SLC (Single Level Cell)	MLC (Multi Level Cell)
Read Bandwidth (64kB)	3.0 GB/s	2.6 GB/s
Write Bandwidth (64kB)	2.6 GB/s	2.4 GB/s
Read IOPS (512 Byte)	702,000	179,000
Write IOPS (512 Byte)	937,000	922,000
Read Access Latency	47 µs	68 µs
Write Access Latency	15 µs	15 µs
Bus Interface	PCI-E Gen 2 x8
Price	$?	?

SLC verus MLC NAND
Between the SLC and MLC models, the SLC models have much better 512-byte reads IOPS than the MLC models, with only moderately better bandwidth and read latency. Not mentioned, but common knowledge is that SLC NAND has much greater write-cycle endure than MLC NAND.

It is my opinion that most database, transaction processing and DW, can accommodate MLC NAND characteristics and limitations in return for the lower cost per TB. I would consider budgeting a replacement set of SSDs if analysis shows that the MLC life-cycle does not match the expected system life-cycle. Of course, I am also an advocate of replacing the main production database server on a 2-3 year cycle instead of the traditional (bean-counter) 5-year practice.

The difference in read IOPS at 512B is probably not important. If the ioDrive2 MLC models can drive 70K+ read IOPS at 8KB, then it does not matter what the 512B IOPS is.

Post-RAID?
One point from the press release: "new intelligent self-healing feature called Adaptive FlashBack provides complete chip level fault tolerance, which enables ioMemory to repair itself after a single chip or a multi chip failure without interrupting business continuity." For DW systems, I would like to completely do away with RAID when using SSDs, instead having two system without RAID on SSD units. By this, I mean fault-tolerance should be pushed into the SSD at the unit level. Depending the failure rate of the controller, perhaps there could be two controllers on each SSD unit.

For a critical transaction processing system, it would be nice if Fusion could provide failure statistics for units that have been in production for more than 30 days (or whatever the infant mortality period is) on the assumption that most environments will spend a certain amount of time to spin up a new production system. If the failure rate for a system with 2-10 SSDs is less than 1 per year, then perhaps even a transaction processing system using mirroring for high-availability can also do without RAID on the SSD?

ioDrive2 and ioDrive2 Duo
I do think that it is great idea for Fusion to offer both the ioDrive2 and ioDrive2 Duo product lines matched to PCI-E gen2 x4 and x8 bandwidths respectively. The reason is that server systems typically have a mix of PCI-E x4 and x8 slots with no clear explanation of the reasoning for the exact mix, other than perhaps that being demanded by the customer complaining the loudest.

By have both the ioDrive2 and Duo, it is possible to fully utilize the bandwidth from all available slots balanced correctly. It would have been an even better idea if the Duo is actually a daughter card the plugs onto the ioDrive2 base unit, so the base model can be converted to a Duo, but Fusion apparently neglected to solicit my advice on this matter.

I am also inclined to think that there should also be an ioDrive2 Duo MLC model at 1.2TB, on the assumption that the performance will be similar to the 2.4TB model, as the ioDrive2 765GB and 1.2TB models have similar performance specifications. The reason is that a database server should be configuration with serious brute force IO capability, that is, all open PCI-E gen 2 slots should be populated. But not every system will need the x8 slots populated with the 2.4TB MLC model, hence the viability of a 1.2TB model as well.

ps
if Fusion should be interested in precise quantitative analysis for SQL Server performance, instead of the rubish whitepapers put out by typical system vendors, well I can turn a good performance report very quickly. Of course I would need to keep the cards a while for continuing analysis...

Laptop for database performance consultants

jchang — Fri, 02 Sep 2011 21:39:00 GMT

Today, it is actually possible to build a highly capable database system in a laptop form factor. There is no point to running a production database on a laptop. The purpose of this is so that consultants (i.e., me), can investigate database performance issues without direct access to a full sized server. It is only necessary to have the characteristics of a proper database server, rather than be an exact replica.

Unfortunately, the commercially available laptops do not support the desired configuration, so I am making an open appeal to laptops vendors. What I would like is:

1) Quad-core processor with hyper-threading (8 logical processors),
2) 8-16GB memory (4 SODIMM so we do not need really expensive 8GB single rank DIMMs)
3) 8x64GB (raw capacity) SSDs on a PCI-E Gen 2 x8 interface (for the main database, not the OS)
- alternatively, 2-4 x4 externally accessible PCI-E ports for external SSDs
- or 2 x4 SAS 6Gbps ports for external SATA SSDs
4) 2-3 SATA ports for HDD/SSD/DVD etc for OS boot etc
5) 1-2 e-SATA
6) 2 1GbE

Below is a representation of the system, if this helps clarify.

The Sandy-Bridge integrated graphics should be sufficient, but high-resolution 1920x1200 graphics and dual-display are desired. (I could live with 1920x1080).
There should also be a SATA hard disk for the OS (or SATA SSD without the 2.5in HDD form factor if space constrained) as the primary SSD array should be dedicated to the database.
Other desirable elements would be 1 or 2 e-SATA port, to support backup and restores with consuming the valuable main SSD array,
and 2x1GbE ports (so I can test code for parallel network transfers.

The multiple processor cores allow parallel execution plans. Due to a quirk of the SQL Server query optimizer, 8 or logical processors are more likely to generate a parallel execution plan in some cases.
Ideally, the main SSD array is comprised of 2 devices, one on each PCI-E x4 channel.

The point of the storage system is to demonstrate 2GB/sec+ bandwidth, and 100-200K IOPS. One of the sad fact is even today storage vendors promote $100K+ storage systems that end up delivering less than 400-700MB/s bandwidth and less than 10K IOPS. So it is important to demonstrate what a proper database storage system should be capable of.
Note that is it not necessary to have massive memory. A system with sufficient memory and a powerful storage system can run any query, while a system with very large memory but weak storage can only run read queries that fit in memory. And even if data fits in memory, the performance could still fall off a cliff on tempdb IO.

Based on component costs, the laptop without PCI-E SSD should be less than $2000, and the SSD array should be less than $1000 per PCI-E x4 unit (4x64GB).
It would really help if the PCI-E SSD could be powered off from SW, i.e., without having to remove it. This why I want to boot off the SATA port, be it HDD or SSD.

NAND notes
per below, 2 SSDs on SATA ports do not cut the mustard,
The spec above call for 8 SSDs. Each SSD is comprised of 8 NAND packages, and each package is comprised of 8 die. So there are 64 die in one SSD, and IO is distributed over 8 SSDs, or a total of 512 individual die.
The performance of a single NAND die is nothing special and even pathetic on writes. However, a single NAND die is really small and really cheap. That is why it is essential to employ high parallelism at the SSD unit level. And then, employ parallelism over multiple SSD units.
An alternative solution is for the laptop to expose 2-4 PCI-E x4 ports (2 Gen 2 or 4 Gen 1) to connect to something like the OCZ IBIS, or an SAS controller with 2 x4 external SAS ports.

System notes
The laptop will have 1 Intel quad-core Sandy-Bridge processor, which has 2 memory channels supporting 16GB dual-rank DDR3 memory. The processor has 16 PCI-E gen 2, DMI g2 (essentially 4 PCI-E g2 lanes) and integrated graphics. There must be a 6-series (or C20x) PCH, which connects upstream on the DMI. Downstream, there are 6 SATA ports (2 of which can be 6Gbps), 1 GbE port, and 8 PCI-E g2 lanes. So on the PCH, we can attach 2 HDD or SSD at 6Gbps, plus support 2 eSATA connections. There is only a single 1GbE port, so if we want 2, we have to employ a separate GbE chip.

While the total PCH down stream ports exceeds the upstream, it ok for our purposes to support 2 internal SATA SSDs at 6Gbps, 2 eSATA ports and 2 GbE, plus USB etc. The key is how the 16 PCI-E gen 2 lanes are employed. In the available high-end laptops, most vendors attach a high-end graphics chip (to all 16 lanes?). We absolutely need 8 PCI-E lanes for our high performance SDD storage array. I would be happy with the integrated graphics, but if the other 8 PCI-E lanes were attached to graphics, I could live with it.

The final comment (for now) is that even though it is possible to attach more than 2 SSD off the PCH, we need then bandwidth on the main set of PCI-E ports. It is insufficient for all storage to be clogging the DMI and PCH.

Thunderbolt
Thunderbolt is 2x2 PCI-E g2 lanes, so technically thats almost what I need (8 preferred, but 6 acceptable).
What is missing from the documentation is were Thunderbolt attaches.
If directly to the SandyBridge processor (with bridge chip for external?), then that's OK,
if off the PCH, then that is not good enough for the reasons I outlined above.
Also, we need serious SSDs to attach off TB, does the Apple SSD cut mustard?

The diagram below shows the Thunderbolt controller connected to the PCH, but also states that other configurations are possible. The problem is that most high-end laptops are designed with high-end graphics, which we do not want squandering all 16 PCI-E lanes.

A Thunderbolt controller attached to the PCH is capable of supporting x4 PCI-E gen 2, but cannot also simultaneously support saturation volume traffic from internal storage (SATA ports), and network (not to mention eSATA). I should add that I intend to place the log on the SATA port HDD/SSD, along with the OS, hence I do not want the main SSD array generating traffic over the DMI-PCH connection.

A Thunderbolt SDK is supposed to released very soon, so we can find out more. I am inclined to think that Thunderbolt is really a docking station connector, being able to route both video and IO over a single connector. If we only need to route IO traffic, then there are already 2 very suitable protocols for this, i.e., eSATA for consumer, and SAS for servers, each with a decent base of products. Of course, I might like a 4 bay disk enclosure for 2.5in SSDs on 1x4 SAS, or an 8-bay split over 2 x4 ports. Most of the existing disk enclosures carry over from hard disk environment, with either 12-15 3.5in bays or 24-25 2.5in bays.

IBM System x3850 X5 TPC-H Benchmark

jchang — Fri, 04 Mar 2011 21:04:00 GMT

IBM just published a TPC-H SF 1000 result for their x3850 X5, 4-way Xeon 7560 system featuring a special MAX5 memory expansion board to support 1.5TB memory. In Dec 2010, IBM also published a TPC-H SF1000 for their Power 780 system, 8-way, quad-core, (4 logical processors per physical core).

The figure table below shows TPC-H SF 1000 results for the 8-way 6-core Opteron 8439 on SQL Server and Sybase, the 16-way quad-core Itanium 9350 on Oracle, the 4-way Xeon 7560 on SQL Server and the 8-way POWER7 on Sybase. On TPC-H Power (single stream), the 4-way Xeon on SQL Server is competitively placed relative to the 16-way Itanium and 8-way POWER7 systems. In other words, an 8-way Xeon might be comparable to the 8-way POWER7. If there is a weak point in SQL Server, it is in the throughput test (multiple concurrent query streams). This aspect is probably something that could be corrected. Unfortunately, it is probably not a priority for the SQL Server team at this time.

TPC-H SF 1000 Results for HP DL785 and Integrity Superdome servers

System	Processor	Total Cores	Memory	SQL	Power	Throughput	Composite QphH	Streams
HP DL785 G6	Opt 8439	48	512	2008 rtm	95,789.1	69,367.6	81,367.6	7
HP DL785 G6	Opt 8439	48	384	Sybase 15.1	108,436.8	96,652.7	102,375.3	7
HP Superdome2	It 9350	64	512	O11g R2	139,181.0	141,188.1	140,181.1	64
IBM x3850 X5	Xeon 7560	32	1536	2008 R2	127,676.1	81,039.6	101,719.3	7
IBM Power 780	POWER 7	32	512	Sybase 15.2	170,206.1	159,463.1	164,747.2	9

Additional details are below. The two IBM results employ SSD storage. The older results are on HDD storage. In addition, the IBM x3850 X5 (with Xeon 7560) system is configured with 1.5TB memory. The total size of the TPC-H SF 1000 database, all tables and indexes, should be 1.4TB. A storage system (7 SSDs) capable of very high IO rates is still required to handle the intense tempdb activity.

System	DL785 G6	DL785 G6	Superdome 2	x3850 X5	Power 780
Database	SQL Server	Sybase 15.1	Oracle 11g R2	SQL Server	Sybase 15.2
Processor	Opteron 8439	Opteron 8439	Itanium 9350	Xeon 7560	POWER7
Sockets-Cores	8 x 6 = 48	8 x 6 = 48	16 x 4 = 64	4 x 8 = 32	8 x 4 = 32
Hyper-Threading	no	no	disabled	2 per	4 per
Frequency	2.8GHz	2.8GHz	1.73GHz	2.26GHz	4.1GHz
Memory	512GB	384GB	512G	1536GB	512GB
Storage Controllers	6 P800	8 x 8Gbps dual-port FC	48 8Gpbs dual-port FC	7 PCI-E SSD	12 PCI-E SAS
Storage Ext	12 MSA70	4 MSA2324fc	24 MSA2324		4 EXP 12
Data disks	240 HDD	96 HDD	576 HDD	7 SSD	52 SAS SSD
Controller-Disks	3x50, 25, 30, 35	1 per 24	1 per 24
LUNs-disks	48x5?	?	3 per 6
OS	2008 R2 EE	RHEL 5.3	HP-UX 11	2008 R2 EE	RHEL 6
Database	2008 EE	Sybase 15.1	Oracle 11g R2	2008 R2	Sybase 15.2

Below are the individual query run times.

TPC-H SF 1000 individual query execution times

Note the wide variation in each query between different systems and database engines. This could reflect differences in any of:
1) processor and system architecture,
2) memory versus disk, HDD and SSD
3) execution plans
4) the efficiency between component operations (scan, index seek, hash, sort, etc)
and probably other factors as well. It would be interesting to compare the execution plans between different database engines, even to force the SQL Server execution plan to one as close as possible to the plans employed on the other database engines.

The main point of interest is not moderate differences in the overall (geometric mean) performance, but rather the very large differences in certain queries. The long run time for Q18 should probably be investigated.

Another view, the 4-way Xeon 7560 SF 1TB with 1.5TB memory + SSD versus 4-way Xeon SF 3TB with 0.5TB memory & HDD. The number of processors is doubled, but the database is 3 times larger. On this alone, we might expect a 50% difference in query time, with the caveat that there are complications in projecting TPC-H performance at different scale factor. There are also significant differences in the memory-to-data ratio, and storage performance characteristics.

TPC-H individual query execution times for 4-way 1TB and 8-way 3TB

On the 8-way system at SF 3TB, Q18 actually runs faster than on the 4-way system at SF 1TB. But the other larger queries, Q1, 9, and 21, show the expected pattern. Overall, it does appear that the 3TB query run times are on the order of 50% higher.

Benchmark Update - Astounding Fujitsu RX900 8-way Xeon 7560 TPC-E Scaling

jchang — Tue, 28 Sep 2010 21:13:00 GMT

Fujitsu just published an astounding TPC-E benchmark result of 3,800 tpsE for their 8-way Xeon 7560 system, the Primergy RX900 S1. Fujitsu had previously published a TPC-E result of 2046.96 for their 4-way Xeon 7560 system, the Primergy RX600 S5. The new results shows 85.6% scaling from 4-socket to 8-socket.

Microsoft Windows Server 2008 R2 introduced core OS improvements in that not only increased the number of logical processors supported from 64 to ???, but removed many locks, including the dispatch scheduler lock. This improved high-end scaling (64 to 128 cores?) from 1.5X to 1.7X, based on tests with the HP Superdome and Itanium processors. At the time of this announcement, the Xeon 7500 processor were not yet available.

When the Xeon 7500 did become available in early 2010, the first TPC-E benchmarks were 2,022.64 and 3141.76 tpsE for the 4-way and 8-way Xeon 7560 systems respectively. The scaling from 4S to 8S was 1.55X, well below the expectation of 1.7X set by Microsofts 2008 R2 announcement. This was understandable as the 8-way result was probably rushed to alignment with product launch. Perfect benchmark results are ready on their own schedule, which is not always in time for marketing blitzes. (Of course, considering that the marketing budget may be paying for the benchmarks, it would be advisable to try really really hard to have a good result for product launch.)

There are two apparent differences between the new Fujitsu and original NEC 8-way Xeon 7560 TPC-E reports. One is the Fujitsu uses SSD while the NEC system used HDD storage. The SSD configuration yields much better average response times mostly in the Trade Lookup and Trade Update transactions, with a reductions from 50/56ms to 13/14ms respectively. In the 4-way Xeon 7560 TPC-E reports, the use of SSD over HDD yields 1% improvement

(My mistake, I should compare the Fujitsu RX900 2046.96 tps-E result with the Dell 1933.96 tpsE, both systems at 512GB for 5.8% performance gain attributed to SSD over HDD. The 1% gain was compared to the IBM at 2022.64 tpsE with 1TB memory and HDD storage).

The other difference is that the Fujistu system distributes network traffic over 6 GbE ports compared with 2 for the NEC system. There are 24 or so(?) RPC calls per TPC-E transaction, so the extra network ports might provide another minor improvment.

Nothing apparent can explain the 4S to 8S scaling improvement from 1.55X to 1.85X. This is certainly not impossible, as IBM figured out how to do this and better with their POWER4 line some years ago. At the time, I thought this was mostly the massive inter-processor bandwidth of the POWER4. Now it is more clear that the OS and database engine all contribute to nearly perfect scaling.

My thinking is that some one at Micrsoft has been watching the performance traces and finally figured out the most critical points of contention. (Such persons are always nameless to the outside world, as this would upstage more established egos.) So I believe this is a new build of Windows and SQL Server, but build numbers do seem to be obvious in the TPC reports, even though full disclosure is required. It is never some magic registry entry like Turbo Mode: ON.

ps The more serious impact of SSD may be evident in the Maximum response times, which ran as high as 68.7 seconds for Trade-Result on the NEC system with HDD, and topped out at 7.1 seconds on the Fujitsu system with SSD. I am thinking that having an open transaction for 68 sec can have serious repercussions on an OLTP system. Curious though the 4-way Fujitsu with SSD could not keep max response similarly low (18.53 sec on Trade-Lookup), while the 4-way IBM kept max response to 17 sec with HDD.

Transaction Response Times
The table below shows transaction response times, average and maximum for the 8-way NEC with HHD and the Fujitsu with SSD storage. The SSD storage system has better average response time, with the biggest impact in Trade-Lookup and Trade-Update. The reduction in maximum response is more dramatic in 6 of the 10 transactions.

Transaction Response Times, Average and Maximum

	Avg Response	Max Response
System	NEC A1080	Fujitsu RX900	NEC	Fujitsu	weight	frames
Storage	HDD	SSD
Broker-Volume	0.05	0.06	2.88	6.72	4.9%	1
Customer-Position	0.02	0.05	43.55	3.49	13%	2
Market-Feed	0.03	0.03	48.81	3.48	1%	1
Market-Watch	0.03	0.05	2.77	2.83	18%	1
Security-Detail	0.01	0.02	2.89	3.79	14%	1
Trade-Lookup	0.50	0.13	49.09	3.30	8%	4
Trade-Order	0.07	0.10	45.96	3.74	10.1%	4
Trade-Result	0.07	0.13	68.73	7.10	10%	6
Trade-Status	0.02	0.03	60.23	6.51	19%	1
Trade-Update	0.56	0.14	3.46	3.79	2%	3
Data-Maintenance	0.11	0.07
weighted Avg Response	0.0812	0.0635
Average tx in flight	2526.5	2389.1

System configuration

System	NEC A1080	Fujitsu RX900
Processor	Xeon 7560	Xeon 7560
Sockets-Cores	8 x 8 = 64	8 x 8 = 64
Hyper-Threading	yes	yes
Frequency	2.26GHz	2.26GHz
Memory	1024GB	1024GB
IO	7 FC	14 SAS
Storage	1872 HDD	336 SSD
OS	2008 R2 DC	2008 R2 DC
Database	2008 R2 DC	2008 R2 DC
tps-E	3,141.76	3,800.00

Benchmark Summary 2010-09-28

Below is a summary of the best available TPC benchmark results for recent Intel Xeon and AMD Opteron server systems. Note that Westmere-EP 32nm and Nehalem-EX 45nm have been consolidated.

Processor Architecture Process	TPC	2-way	4-way	8-way	16-way
W-32nm/N-EX45 Xeon 5600 6C 7500 8C	TPC-C TPC-E TPC-H	803,068 1,110 73,974.6@100G	1,807,347 2,022.64 121,346@300G	- 3,800.00 162,601@3TB	future future future
Magny-Cours 45nm 12C	TPC-C TPC-E TPC-H	705,652 887.4 71,438.3@100G	1,193,472 1,464 107,561@300G	n/a n/a n/a	n/a n/a n/a

2010 Sep 19

TPC-H results was finally published for 4-way Xeon 7500 @300GB on 14 Sep. A TPC-C result was also published for the 4-way 7500 on 27 Aug. There will probably not be a TPC-C for the 8-way DL980 as there may be a limitation for SQL Server in the ability to write to a single log file. HP seems to be the only vendor active in TPC-H. This could be because other companies have cut staff. Benchmarking is a specialized skill. It usually takes a dedicated person for each benchmark and environment. It is not the benchmark result that is important. It is the investigation into the root cause of bottlenecks to improve performance in the next iteration that is important. So this means only HP will be making contributions in DW.