A hard drive is a hard drive, right? Storage has come a long way, but is probably still the biggest bottleneck in your system. Solid State Drives (ssd) have changed the rules in terms of performance and there is new technology on the horizon to allow us to access our data even faster.
I talk with Justin Strong and Ananda Sankaran from Micron Technologies about their experiences with SSD and how organizations are using in their environments. We also chat about some of the new technologies on the horizon for Micron.
Transcription: How Solid State Drives Can Help SQL Server
Carlos L. Chacon: This is a SQL Data Partners podcast. My name is Carlos L. Chacon, your host, and this is episode 19. This week, the United States celebrates Thanksgiving Day. I know I have lots of things to be grateful for, and I hope that you do as well.
Today, we’re talking about SSDs, the role that they play in SQL Server, how they’ve changed some of the server infrastructure, and some of the things that we can take advantage of.
This is an example of why you’d want to talk to the vendors at some of these events, the SQLSaturday events and other conferences that you attend. Yes, they do have products they want to sell you, but they are people, and they have interesting stories as well.
I happened to meet the Micron guys, and they were at several different events. They mentioned they had a technology evangelist in Justin Strong. So today, along with Justin, I’ve got Ananda Sankaran. They talked to us a little bit about their experiences in SSDs.
Yes, they are a vendor, but it’s not vendor-specific. This is more of an abstraction. In very general terms we will provide some information if you’d like about that, but again, it’s not super specific to them.
Ultimately, we are talking about a variety of different topics. We’d love to hear about what you want to talk about. You can hit me up on Twitter at @CarlosLChacon, and, as always, compañeros, it’s good to have you and welcome to the show.
Children: SQL Data Partners.[music]
Carlos: So compañeros, today we are talking about SSDs, and as I mentioned, I have Justin and Ananda here from Micron Technologies. Gentlemen, welcome to the program.
Justin Strong: Thank you, Carlos.
Ananda Sankaran: Thank you, Carlos. It’s great to be here.
Carlos: Ultimately, today, we’re talking about SSDs and so I thought first, just in case someone is not as familiar with SSDs, let’s define what they are and how they’re being used. We talk about SSDs, solid state drives, solid state disks, and what role or how are they changing the way that computers are getting built and used and how they are different from traditional disks.
Justin: With solid state disk drives, what we’re seeing, basically they are made of flash memory. Flash memory is a storage technology that is quite a bit faster than disk drives. What we’re seeing is a fundamental shift in the marketplace in storage technology. It’s enabling storage to be significantly faster.It’s allowing storage to be significantly less consuming of electricity, of power. It’s also enabling significantly denser storage in the marketplace. I’ll talk about it a little bit later as we go through the podcast, but that’s having some significant impact on the way that storage is being implemented.
Carlos: Even from the physical perspective the way that SSDs fit into the system is different from disk drives. They have to have certain slots for it to be able to handle the SSDs.
Justin: In some ways, there are some form factors that are slightly different. Right now, the SSDs have basically the same form factor as a lot of the hard drives. There’s a two and a half inch, three and a half inch form factor.There also is some additional form factors, there’s cards. Traditionally those have been PCIe cards. Those are very high performance. There are also a couple of new form factors. One is called a U.2. That is a form factor that allows you to plug in to a standard SATA slot, a PCIe, a SAS drive, or a SATA drive.
There’s also another form factor called M.2 and that’s the size of a gum stick. It’s quite a bit thinner than that, but that allows a significant amount of flash storage to exist in a server or a client within a very small amount of space.
Carlos: You have these different flavors, if you will. I’m sure based on size generally there could be a cost differentiation. From a speed, or from a usability perspective, are there any differences between these technologies?
Justin: For the standard two and a half inch, it’s typically what you would find in a SATA or a SAS. PCIe is also now available in the two and a half inch form factor. That’s probably going to be the most popular one.Form factor wise, what you’ll see is the cards usually have the most speed available to them. That’s typically going to be your PCIe. That’s the only one that’s available in that half-height, half-length, or full-height, full-length card format.
You’ll typically see the fastest speed from that. Part of that is because you have more space to lay out some of the NAND, you can actually get a higher speed, because you can light up or power more NAND chips simultaneously to access a lot more data simultaneously and, hence, get a lot of parallelism in that.
Carlos: That’s not a word that I’m super familiar with. You said the word NAND chips. Can you help me understand what that is?
Justin: NAND chips, those are basically the type of flash memory, the type of flash memory that SSDs are typically made out of.
Carlos: So each of these have these different segments or areas, these NAND chips. Then the ability to power them will dictate simply how much we’re able to use in the speed.
Justin: Yeah, there are other things also. The PCIe does have a faster interface so that comes into it. Then the architecture PCIe allows you to do faster, but there is some relationship to the speed.M.2, you typically have a very small amount of real estate to work with. There are other reasons for M.2 to having maybe a lower performance. But typically, you’ll have less NAND that you can actually power up at the same time. Then the two and a half form factor fits in between.
Carlos: Obviously, listeners are over the space, if you will, from what they are able to afford and the kind of servers that they have, but we tend to have a niche with some of the smaller players. I have a server, let’s say we’re considering SSDs, but we’re not exactly sure how those would integrate.The PCIe you mentioned is the most popular, how would an organization go about deciding whether if, one, if they can fit SSDs into their current infrastructure. Then two, how do they go about testing it or getting their hands on somebody to see if it’s an option for them?
Justin: There are a lot of different options available in SSDs. I can say, everything from low end SATA drives, all the way to the high end PCIe drives. Typically, we have it fall into three inter-phase camps, SATA, SAS, and PCIe.Each one of those has different form factors. I think one of the things that somebody would have to look at is what are they trying to accomplish putting in an SSD? The most common reason that people use SSDs is for increasing performance.
It’s not necessarily just increasing performance, but they’re really looking for consistent performance. One of the things that an SSD will provide is you will always get that same level of performance. When you have like, let’s say a SAN or NAS, sometimes you’ll get a pretty decent performance, but because it’s shared, sometimes you’ll have other applications that are on that shared resource impact the performance that you’ve got.
Typically, with an SSD, you can obviously add SSDs into a SAN or a NAS, but because the performance level is so much greater, that you’ll always get that consistent level of performance from an SSD and hence, consistent level of performance.
That’s one of the things to do to evaluate what do we need to do, what are we trying to accomplish with our application. Once you do that, then you can decide how much performance do I need? Do I need a PCIe card where I need the ultimate level of performance? Or can I deal with the performance of, let’s say, a SATA SSD and that will be sufficient for what my application needs?
Then the other thing to look at is where do I want to put my SSD? Do I want to put it directly on the server, which will give me my greatest bank for my buck? Or do I want to put it somewhere else in my hardware architecture?
Carlos: You have mentioned the SAN and sharing that space? I’m not sure that Micron, if Micron heads up, I’m sure they probably do, but I have heard of flash arrays in SAN architecture. Can that flash drive not be shared or allocated, if you will?Let’s say I have several flash drives to make up, for example, I heard one of a terabyte flash array. Can I not divide that up to share among several machines?
Justin: Yeah, you can absolutely do that. The most common way that we see people at Enterprise in a way implementing flash is a tier in a SAN or a NAS. The other thing you can do is buy up on all flash array.
Carlos: If I’m a small organization, obviously you have the SAN array. I think you mentioned the ORD Enterprise, so for larger customers. Let’s say, what’s even direct or go more towards the SQL Server, the route. I’m a small organization that has a SQL Server, we are not seeing the performance that I want, how would I make a determination that maybe flash arrays are the way to go versus the SSDs are the way to go?
Ananda: If you’re a small organization and have a SQL Server deployment, let’s say, you’re using a Gboard attached to the server or its local drives on the server for SQL data. The simplest thing you can do to add flash is add a couple of flash drives, maybe one or two PCIe or a SATA, depending on your performance needs, like Justin said.Move some of your database objects. For example, move your SQL Server logs to the flash drive and move your SQL Server database in [inaudible 11:56] . What we have found in our internal testing is just by moving logs onto a flash drive, we are able to increase the transaction throughput by almost 50 percent and reduce the latency by almost 90 percent for your transactions. That’s huge.
Not only reducing the latency but the consistency of the latency. Justin again mentioned that, the 99 percentile. I’m talking about majority of your transactions will be at a constant low latency especially when you use a PCIe drive. That’s the simplest move that a small organization can make by deploying a few number of flash drives and immediately see the benefit in terms of performance.
Performance extends to larger business problems. Now you’re able to do more with [inaudible 12:49] . You’re able to drive more transactions with Video SQL environment. That translates to business productivity.
Carlos: Let’s continue with that. On the technical perspective, obviously, the logs become the bottleneck because everything has to go through the log first.
Carlos: You mentioned the transaction. Throughput can increase because now my logs are super, super fast. More transactions can happen because my logs are much faster. Now, question. My think or my understanding is that a flash with SSDs they actually do slightly better maybe at those speeds, its six of one half a dozen of the other.But they do slightly better in a random reading and writing perspective, which is more common with our data files. It’s just that you mentioned moving the log files there first, obviously the easiest because it’s the very first step. Talk to us about using random reads and writes versus the incremental reads and writes.
Ananda: That’s a great point. There is this understanding that SSDs are good at random reads and writes, which they are. They are very good at random I/O patterns, but what’s not well known is that they’re even better with sequential I/O. For example, if you look at the NAND media, let me go a couple of levels deeper.The NAND media stores information in the form of electrons stored in a transistor. What it essentially is matched down to is the media is stored as blocks and pages. Let’s take an example, you can have 4K page sitting in a one megabyte block. You can write and read in terms of page sizes, which is 4K in this example.
If you want to rewrite a page you have to erase the whole block, which is one megabyte block. That’s the fundamental characteristic of the NAND media. What happens is when you have a lot of random reads and writes to rewrite an area you have to erase a block and you end up with a situation of writing the new data in some other block and later moving the data pieces within this original block, which are stale, and gather them and move somewhere else, so called garbage collection which is a common term within SSD devices.
Why I went this path is, take sequential I/O, if you have no data written in an SSD device, if you have sequential I/O coming, you fill a block and then you go and fill the next block and therefore you avoid the problem of rewrites. Once you reach the end of the device, then you come back and start writing the first block.
Sequential I/O benefits the way the NAND media operates and it’s even better. That’s something not widely discussed in the industry. When you take a fresh SSD out of the box, we call it the default factory state where you secure which means all NAND blocks have been erased and then you directly put it for log files. It’s going to perform very well.
Carlos: We should probably just one back just a moment. First of all, any time you’re experimenting with your server configurations, your individual instants will vary. Your server needs are your server needs. They’re not my server needs. They are not what we did in the lab. We should throw the flag out there and say ultimately, you need to test your environment first.We’ve seen some patterns here that we’re discussing, but compañeros, please do your own testing before you [laughs] make changes in your server, obviously. Getting back to that is you had mentioned the 4K pages. In SQL Server the default is 8K, is there a change by default there?
Ananda: Mine is just an example. Each NAND media will have its own page and block sizes. Let’s not assume everything is 4K, but it’s a good question. You’ve said SQL is 8K per page.
Carlos: Per page.
Ananda: That’s correct. 64K per extent, pages are grouped together into extents. We just find when you write, you’re going to write into a 4K page NAND, you’re going to write into two pages.It’s extremely fast. Think of this, if you compare to the rotating hard drive media, you’re writing on a disk platter and you’re magnetically coding your information on sectors of vital bytes and size.
Carlos: Yes. You can specify the size. There are some formatting options, but yeah.
Ananda: Correct. In NAND, it’s all semi-conductor. You have multiple flash NAND chips and when you are writing 8K synchronously, which is what the data files for SQL Server would do, when you’re sending multiple 8K writes, you just platterly write them to all these NAND chips within the SSD.Think about a single hard drive, you have a head, so you have to go all over the platters to write that data. You’re moving across back and forth. That’s why your traditional hard drive is, a 15K rpm hard drive, is rated at 300 IOPS or so, whereas the parallelism relies electronically storing data in a semiconductor chip yields you hundreds of thousands of IOPS on a single drive.
Regardless of the sizes, where I’m going back to the original question is, regardless of the sizes you are still able to come with them to the media quicker than how a traditional hard drive would come at that data to a magnetic platter.
Carlos: We talked about IOPS. Inputs and outputs I think is generally how that’s referred to as, the number of times we have to either write or read from the disk, we group together and call that IOPS?
Ananda: Correct. It’s an Input/Output Operations Per Second. Usually that’s a term people use, but as always there are details behind it. What is the block size of that? In SQL we talked about 8K.For an application you need to be aware of the block size for the IOPS, as well as the read/write percentage. Are you doing 70 percent read, 30 percent write? Or is it in worse, or is it a 100 percent write? All those characteristics of the IO matter. IOPS is directly related to that.
Carlos: I talked about the read/write because I’ll admit where most of the information that we’re getting, I’m not sure if it’s vendor-specific. Many of the sessions that I have attended they talk about the read/writes and maybe being as fast.It’s one of the recommendations that we’ve heard is to remove your random read stuff over. Ultimately, I guess where I’m going with that is we are going to have some additional information available to you. If you want to take a deep dive, of course compañeros, you can always check out sqldatapartners.com/podcast.
There we’ll have links to the show, today’s show and with information about all of these things that we’re talking about so that you can find out and deep-dive a little bit more.
Ananda: They can share some of our findings. Justin can send out our published material that has some of this information available.
Carlos: I know you’re in the very, very beginning stages and obviously SSDs are very, very cool. I don’t have as much experience as I would like to with them, implementing them. You’ve just announced a joint venture, if you will, with Intel for something called NVMe drives and 3D XPoint.An announcement has just been made, but can we quickly talk about that and what the future of that will be?
Justin: I’ll go ahead and take that Carlos. NVMe and 3D XPoint are two different technologies. NVMe is an industry standard implementation or industry standard for SSD. What happen was is that the SATA specification and any of the interfaces SATA, SCSI, PCIe, SATA and SCSI mainly and SAS to some extent were defined mainly with hard drives in mind.You actually look in some of these specifications and they talk about cylinders and things like that. The industry got together and said, “Hey, these SSDs are much faster, they’re a different technology, they really deserve a different interface, a software interface to really take advantage of what they have to offer.”
The industry we came up with, the standard they came up with is NVMe. NVMe is Non-volatile Memory for enterprise. The NVMe really takes advantage. It was specifically designed for SSDs. It’s a software stack for SSDs, and it allows similar interfaces and similar exchange ability, I guess if you can use that term in between SSD.
You could have an out of the box, an operating system would have an NVMe interface and you could put in a drive from one vendor or another vendor and each of the drives should work with that standard type of interface and driver.
Carlos: That’s an interesting point. I didn’t read the announcement very well obviously, is that I envisioned that those replacing SSDs, but it almost sounds like it’s going to be the software that helps really open up the floodgates to the SSDs.
Justin: Yes. Right now, most of the NVMe drives are PCIe. They’re almost synonymous right now with PCIe drives the high-end drives. You’re still going to have SATA drives and you’re still going to have SAS drives. Then NVMe right now mainly is this PCIe interface.
Carlos: SATA will be coming down the road. I’m sure we’ll get some more information in 2016 as to how that evolves.
Justin: Yes, absolutely. 3D XPoint, completely different technology. As we talked about flash, NAND flash is the most common type of flash memory that you have today. It’s been around for 10 years, very, very good technology. We continue to see NAND flash have a long future.3D XPoint is a new type of non-volatile memory. I don’t want to call it flash memory, but a new type of non-volatile memory. That fills the gap in between DRAM and flash memory. DRAM is very fast. It pretty well has infinite capability for endurance. You can read and write to it as many times as you want, but it’s relatively expensive and it’s volatile, you turn off the power and you’d lose all the data.
Flash memory on the other hand is non-volatile and it’s relatively inexpensive, but it’s much slower than DRAM and it’s got some other attributes that make it as suitable for a memory, like a DRAM type of replacement. There is two ends of the spectrum.
What 3D XPoint does is fill that gap in between. What I see is the use for this is two areas. One is storage and then the other is storage semantics and the other is memory semantics. For storage, obviously you’re going to get a much faster SSD than you would using NAND flash.
There are two reasons for that. One, it’s just inherently a thousand times faster than NAND is. The other is its byte or even bit-addressable. Right now, the smallest area that you can write to a NAND flash is 4K. With the 3D XPoint you’re going to be able to write to individual bytes or bits, so for small data that will make that inherently faster to use.
That’s the storage. The other type of semantic is the memory semantic. While it’s not a complete replacement for DRAM, there are some instances where you can see where you may want quite a bit of memory in your server and DRAM might be a little bit too expensive and the 3D XPoint while it’s not quite as fast as DRAM, it is quite a bit cheaper and it’s also durable. It’s non-volatile.
You could see something, let’s say for example like swap files. I know that most servers are engineered with enough DRAM so you don’t have to swap, but if you could put in a little less DRAM and then maybe put in some 3D XPoint that could potentially be a use for that where you could use a 3D XPoint for a swap file because you can get away with having slower access to that.
Carlos: Then the SQL server space, for example SQL server 2016 they’re going to introduce the option of extending your buffer pool out to even flash drives, or in this case, could be 3D XPoint when that becomes more available.Question there again, so it’s not yet available, any testing or any experimentation in NAND now that you’ve done with SQL server in putting the buffer pool out onto flash storage?
Ananda: Yeah. I have done limited testing. This extension is only for read data, not write data. Write data will still go back to your original storage whether it’s your jboard or SAN, or whatever it be.Environments where you have a contingent for DRAM, for example virtualized environment. You have SQL server running on VMware, maybe for VMs running on 128GB DRAM server, each of them getting GBs each. In those cases you will benefit by adding a flash drive and adding a buffer pool extension file on the flash drive.
Where I’m heading with my answer is from my testing, where I believe this feature benefits is where environment SQL server, environments where you have low DRAM allocated to the server and your active data set is larger than the DRAM allocation for SQL server.
If you have 32gig DRAM and then your data set is for example 50GB or a 100GB, the active portion of your data, that SQL server access is larger than that then probably you will benefit by using a flash drive and the BPE feature.
Carlos: I think this is a nice segue for organizations that maybe aren’t quite ready to invest in another server or to increase exponentially, but they need a little bit of wiggle room to help them get a boost in performance, because they’ve hit a threshold. I think this would be an option for them.
Ananda: What I deemed this and there are already a lot of conversation we had, more locks to an SSD drive or have this buffer pool extension feature to extend your DRAM on a flash drive. These are all good immediate options for accelerating your SQL server deployment. We call them as accelerating your existing deployment.Then on the other end of the spectrum is what you earlier mentioned, the old flash arrays is and flash storage to your external SAN.
Carlos: Justin and Ananda, thanks so much for the conversation. I think we always learn something a little bit and today we’ve talked about SSDs, and so I do appreciate the conversation.We do have a couple of questions, changing, switching gears just a little bit. One of those things that everybody is always interested in is what tools are other people using? Really quickly, so what’s your favorite tool? This could be a free tool, a paid tool, but what are you using that helps you be more productive?
Justin: In our industry one of the biggest things with SSDs are performance and measuring performance. Iometer I think will have to, or a file called FIO file would have to be my two favorite tools, because we use them pretty well on a daily basis to measure the performance of our SSDs.
Ananda: Likewise, performance as being my favorite topic over the year, the good old Windows performance monitor and the surrounding tools for that. I live and breathe it every day. It’s a great tool to diagnose an environment, especially Windows environments.
Carlos: Last question. If you could have one superhero power, what would it be and why would you want it?
Justin: That’s an easy one for me, flying. From a kid I’ve always wanted to be able to fly, and I thought that it would be just an awesome thing to be able to fly whenever you wanted, yeah, hands down, flying.
Carlos: Yes, I agree. That’s a very neat super power. Ananda, what about you?
Ananda: You all are familiar with the European comics Asterix?
Carlos: I must admit I’m not, but I’m very interested to hear.
Ananda: It’s not French, but it’s a village in France, geographically, that is fighting against the Roman invasion. This village is powered by a magic potion built by a wizard. My super power would be to be that wizard, building this magic potion, so I can add strength to everyone around me.
Carlos: That’s very noble of you there, Ananda, to be able to help other people. Justin, Ananda, thanks again for being here on the show, taking some time with me.
Justin: Thank you, Carlos. It was great to be able to talk to you.
Ananda: Thanks Carlos, it was great chatting with you.
Carlos: Yes. Compañeros, again, check out sqldatapartners.com/podcasts for this episode, the show notes there. If we missed something let us know. I’m on Twitter at @CarlosLChacon, or you can hit me up at [email protected]. We’ll see you on the SQL Trail.[music]
Children: SQL Data Partners.