May 22, 2015 at 8:22 am
Does anyone have any experience with this kit and SQL Server?
I'm having a chat with one of their bods in the near future as we're looking to seperate out our SQL Server data from our other stuff.
Does the auto tuning (moving stuff up and down the tiers) really provide much benefit - or can it be more trouble than it's worth.
We obviously won't be using their snapshotting for the databases themselves.
Also - are their performance policies for SQL Server up to scratch. The screenshot showing block size in the marketing blurb shows 8k, which leaves me suspicious.
Not as much as this does though
"For most SQL Server protection scenarios, the simple recovery
model provides the best combination of management simplicity
and data protection"
Oh dear oh dear.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
May 22, 2015 at 10:21 am
I do have nimble in my environment which is not used it for SQL server but I can give some insight on how it works.
we're looking to separate out our SQL Server data from our other stuff.
As far as I know this is not possible. all disks are used for all access but data access always comes from cache (kinda like how SQL Server always reads from memory) so separating sql data from other data is not necessary as with most SANs.
as for the tuning, it has performed pretty good in my SQLIO test. I did not use the file that SQLIO creates as this is all zeros and compresses to nothing. instead I used a sharepoint db backup file to run a number of read tests( I chose a sharepoint db backup because they do not compress very well). I did eight different read tests and only the first one(8k random) was slow, the rest of the tests performed well. I have attached results.
Basically the nimble cache behaves like SQL Server buffer pool. If data is in cache, access is quick, if data is not in cache, access is slow but now that data is in cache and any subsequent access will be quick.
May 24, 2015 at 9:35 am
I have Nimble for general workload and SQL / databases is one of the biggest areas of improvement vs enterprise storage that didn't have any flash.
Data is written to NVRAM then flushed to disk, so has very fast write performance (good for SQL, it uses same type of filesystem as NetApp, but wrote themselves to overcome some of the shortcomings / optimise for flash), reads are either from flash cache (has various clever stuff going on to maximise cache hit rates) or spinning disk.
Performance profiles wise, you store SQL data on volumes set to use SQL data profile (diff profiles for diff versions of SQL) and same for log files.
Those profiles are optimised to get maximum perf for SQL for their hardware, they've been collecting data via their cloud service for 5 yrs now so they've tuned these over the years to optimise performance.
It's 8kb as that's the paging size that SQL uses, they work off that not the allocation unit size to maximise performance.
Compression rates and cache hit % are very high, highest out of all the types of workload that have going to it.
May 24, 2015 at 10:14 am
We have a bunch of Nimbles in our customers' environments, and depending on your performance requirements, they can be hard to beat for performance/price.
Having said that, there are not "tiers", per se, at least not in the sense of traditional tiered storage like EMC.
There are back-end SATA disks (12 unless you add a capacity shelf, I believe), and then an all-flash cache. The auto-tuning is just moving data in and out of the cache and directing sequential IO to the SATA.
As iannoble pointed out, write performance is generally very good, since it writes to NVRAM, and reads are also fantastic provided they come from cache.
That last point is the big caveat. If a significant bit of IO ends up coming from the back-end SATA disks, whether because you're churning through the cache or the IO is classed as sequential and goes straight there, performance can tank, since 12 SATA disks can only deliver so much performance.
We've run into the biggest problems with that when running CHECKDBs or when customers have very large active data sets that can overrun the cache.
The performance drop from going to those disks is enough that we've basically adopted a policy only to order them with the 4x cache upgrade, since most of the units we ordered with base cache turned out to have huge cache churn issues, and resulting IO issues with too much hitting the SATA disks.
So long as you make sure the cache is adequately sized for your active data set, you should get great performance from them, especially for the price.
Cheers!
May 24, 2015 at 10:22 am
Jacob is right about getting the cache size right. For initial sizing, the starting point for cache is advised to be 10% of the raw capacity of the box, though go for the most you can afford (it's easy to upgrade cache afterwards and they don't charge you huge amounts more than if you had bought that capacity in the first place, but you don't want to have to go back & ask for more capex).
Cache size is probably the most important part, after raw capacity (personally regardless of storage I prefer to base capacity requirements on raw not after storage efficiency figures) and IOPS requirements (different models have different max IOPS, which comes from them having different CPU power, tho you can upgrade controller at a layer date if reqd), then the network interface required.
Choosing a VAR that has installed them often is a good idea. If your VAR is on the ball, they will offer to give you a tool to monitor your environment to work out your IOPS / cache requirements, if not ask your Nimble SE.
Despite getting high cache hit rates, even when it falls through to spinning disk the read latencies are still lower than when my workloads were on a 24 x 10k SAS enterprise storage array from a different vendor, so I'm more than happy with it (write latencies don't use the flash cache, given the use NVRAM & are bound by CPU on the controller). That only works up to a point though, you want to have enough cache to avoid that.
Looking at VMware before and after latency graphs over a few months, the latency just flatlines once the DB workloads were moved over.
I'm not a SQL dba but used to be, the current dba is more than happy with the performance boost.
Like Jacob says, it's great to have such fast performance at a great price from a company I trust enough given they have been around so long now with so many customers.
May 24, 2015 at 12:16 pm
Some 30 day disk latency stats off our main ERP SQL as reported at VM level (not storage)
SAS based enterprise storage (R/W): 12ms / 1.8ms
Nimble: 1.8ms / 0.2ms
and a reporting SQL instance:
SAS: 21ms / 13ms
Nimble: 0.92ms / 0.87ms
May 26, 2015 at 4:57 am
Robert klimes (5/22/2015)
I do have nimble in my environment which is not used it for SQL server but I can give some insight on how it works.we're looking to separate out our SQL Server data from our other stuff.
As far as I know this is not possible. all disks are used for all access but data access always comes from cache (kinda like how SQL Server always reads from memory) so separating sql data from other data is not necessary as with most SANs.
as for the tuning, it has performed pretty good in my SQLIO test. I did not use the file that SQLIO creates as this is all zeros and compresses to nothing. instead I used a sharepoint db backup file to run a number of read tests( I chose a sharepoint db backup because they do not compress very well). I did eight different read tests and only the first one(8k random) was slow, the rest of the tests performed well. I have attached results.
Basically the nimble cache behaves like SQL Server buffer pool. If data is in cache, access is quick, if data is not in cache, access is slow but now that data is in cache and any subsequent access will be quick.
we're looking to separate out our SQL Server data from our other stuff.
As far as I know this is not possible. all disks are used for all access
Ah sorry - badly worded. When the current SAN ws put in the snake oil sales vendor's san engineer just recommended putting everything on one huge RAID5 4 bit block array - SQL Server boxes, file servers, hell it's all the same - just lump them all together, makes no difference, doesn't matter. What we're looking at is pulling all the SQL Server stuff off the environment containing all the other 'stuff' and putting it into it's own environment.
It's a largish estate (100+ servers 1000+ databases) with some bigish systems - largest about 2 1/2 TB, with pretty much all data potentially 'live' (Medical document retrieval/storage) with a fair few over 100 gig and a lot of 365*24*7 stuff. So it needs to be pretty scalable and robust.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
May 26, 2015 at 5:04 am
Jacob Wilkins (5/24/2015)
We have a bunch of Nimbles in our customers' environments, and depending on your performance requirements, they can be hard to beat for performance/price.Having said that, there are not "tiers", per se, at least not in the sense of traditional tiered storage like EMC.
There are back-end SATA disks (12 unless you add a capacity shelf, I believe), and then an all-flash cache. The auto-tuning is just moving data in and out of the cache and directing sequential IO to the SATA.
As iannoble pointed out, write performance is generally very good, since it writes to NVRAM, and reads are also fantastic provided they come from cache.
That last point is the big caveat. If a significant bit of IO ends up coming from the back-end SATA disks, whether because you're churning through the cache or the IO is classed as sequential and goes straight there, performance can tank, since 12 SATA disks can only deliver so much performance.
We've run into the biggest problems with that when running CHECKDBs or when customers have very large active data sets that can overrun the cache.
The performance drop from going to those disks is enough that we've basically adopted a policy only to order them with the 4x cache upgrade, since most of the units we ordered with base cache turned out to have huge cache churn issues, and resulting IO issues with too much hitting the SATA disks.
So long as you make sure the cache is adequately sized for your active data set, you should get great performance from them, especially for the price.
Cheers!
That's interesting, thanks. We do have some rather large avtive datasets, either bacause they actually are (2 1/2 Tb and rapidly growing medical record archive - any patient that comes in the full archive for them has to be available) or because the design is garbage and you're getting unnecessary scans on large (in terms of cache size) datasets. Obviously this is an issue with any infrastructure, would it be more of an issue with this type of kit though?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
May 26, 2015 at 6:12 am
Thanks all for sharing your knowledge with me. Very helpful and much appreciated.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
May 26, 2015 at 12:40 pm
andrew gothard (5/26/2015)
... Obviously this is an issue with any infrastructure, would it be more of an issue with this type of kit though?
It is probably somewhat more of an issue with the Nimble than with a more traditional array at a similar price point, just because the more traditional competitor will likely be using more than 12 HDDs.
Unfortunately, as with so many other things it comes down to a tradeoff. With the Nimble, your baseline performance will likely be significantly better than with similarly priced traditional array, but worst-case performance could be worse than with the traditional array.
Having said that, at least in the several environments we've used Nimbles, they've been an improvement over the previous (often more expensive) arrays, sometimes even when they have run into the cache churn issue.
Also, at this point Nimble has more options than when this was an issue for us. I think now you can add capacity shelves to get more HDDs, as well as all-flash shelves to get more cache.
All in all, a Nimble's probably a good decision. At the very least, it should be at least as good overall as (and probably noticeably superior to) anything else in a similar price range. Obviously going with something all-flash like a Pure would win the performance contest, but well, that's comparing apples and gold-plated, diamond-studded oranges 🙂
May 27, 2015 at 4:54 am
Jacob Wilkins (5/26/2015)
andrew gothard (5/26/2015)
... Obviously this is an issue with any infrastructure, would it be more of an issue with this type of kit though?It is probably somewhat more of an issue with the Nimble than with a more traditional array at a similar price point, just because the more traditional competitor will likely be using more than 12 HDDs.
Unfortunately, as with so many other things it comes down to a tradeoff. With the Nimble, your baseline performance will likely be significantly better than with similarly priced traditional array, but worst-case performance could be worse than with the traditional array.
Having said that, at least in the several environments we've used Nimbles, they've been an improvement over the previous (often more expensive) arrays, sometimes even when they have run into the cache churn issue.
Also, at this point Nimble has more options than when this was an issue for us. I think now you can add capacity shelves to get more HDDs, as well as all-flash shelves to get more cache.
All in all, a Nimble's probably a good decision. At the very least, it should be at least as good overall as (and probably noticeably superior to) anything else in a similar price range. Obviously going with something all-flash like a Pure would win the performance contest, but well, that's comparing apples and gold-plated, diamond-studded oranges 🙂
Yes - there are apparently some new bells and whistles. 300 series and up, up to 6 secondary shelves can be attached. One additional SSD shelf can be attached IIRC up to 16, added in batches of 4. One rather nice thing, certainly for our use patterns, is aggressive caching - which is somewhere between standard and pinning. When a new record's added it stays 'sticky' in cache for a while - so stays in, say, for a day beflore being flushed if it's not read.
Works very well for us, patient comes in, stuff's generated, people access it as they progress through the encounter. Encounter completes and then it's not likely to be accessed until they next see us.
For me, the structure itself works - in principle - better for Database work than the more standard SAN architectures which are IMO still more file server focussed. Although, RAID 6 ... meh. Although can see the argument for triple parity for data integrity.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
January 20, 2016 at 11:59 am
iannoble (5/24/2015)
It's 8kb as that's the paging size that SQL uses, they work off that not the allocation unit size to maximise performance.
This is something I have a question about. Their "optimized" configuration settings use a block size of 8KB, but it's generally considered best practice to use a block size of 64KB for SQL Server. The reason for this is that SQL Server allocates data pages in extents of 8 pages. Using a block size of 64KB allows SQL Server to write the entire extent in one block. In addition, certain features like FileTable cannot reside on volumes that use compression. Does Nimble allow you to create your own custom configuration settings?
January 24, 2016 at 1:56 pm
KJP (1/20/2016)
iannoble (5/24/2015)
It's 8kb as that's the paging size that SQL uses, they work off that not the allocation unit size to maximise performance.This is something I have a question about. Their "optimized" configuration settings use a block size of 8KB, but it's generally considered best practice to use a block size of 64KB for SQL Server. The reason for this is that SQL Server allocates data pages in extents of 8 pages. Using a block size of 64KB allows SQL Server to write the entire extent in one block. In addition, certain features like FileTable cannot reside on volumes that use compression. Does Nimble allow you to create your own custom configuration settings?
Yes they are customisable, but 8KB is the size you want on the Nimble side for SQL.
The following PDF explains why 8KB block size on the Nimble is optimal for SQL:
Essentially, the default IO size for SQL Server is 8KB, so to avoid misalignment, it should be set to 8KB on Nimble, with an NTFS allocation unit size of 64KB.
NIMBLE_VOL_BLOCK_SIZE <= APP_IO_SIZE
amd
NIMBLE_VOL_BLOCK_SIZE <= NTFS_CLUSTER_SIZE
Regards compression, Microsoft are likely talking about Windows built in compression, as opposed storage device compression. You can turn it off if desired, but not recommended (apart from SQL Logs).
Using the performance policies made available by Nimble is recommended, as they optimize them for the best system performance based on data they see in the field.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply