Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Accelerator products such as Fusion IO Expand / Collapse
Author
Message
Posted Monday, June 10, 2013 9:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:23 PM
Points: 266, Visits: 893
Can anyone share their experiences with accelerator products such as Fusion IO?

I'm interested to know how easy they are to implement and what benefits / issues people have seen in practice.

I presume that even for smaller databases that can mainly be cached, there would still be a benefit through increasing the speed the log can be committed to 'disk'.

Any comments would be greatly appreciated.

Thanks in anticipation

Tim


.
Post #1461624
Posted Monday, June 10, 2013 10:09 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 4,405, Visits: 6,267
Tim Walker. (6/10/2013)
Can anyone share their experiences with accelerator products such as Fusion IO?

I'm interested to know how easy they are to implement and what benefits / issues people have seen in practice.

I presume that even for smaller databases that can mainly be cached, there would still be a benefit through increasing the speed the log can be committed to 'disk'.

Any comments would be greatly appreciated.

Thanks in anticipation

Tim


FusionIO is NOT an "accelerator" product as in "caching", for which there are now numerous products on the market. FusionIO is just a very fast storage device. And yes, if your data can be completely contained in memory faster tlog operations is a win - but so is flushing dirty data pages down to disk, which can be a killer on rotating media for an active OLTP system.

I have been a proponent of FusionIO since WAY before they were a house-hold name, including spending time with their lead driver developer and doing various SQL Server benchmarks, etc. I have quite a few clients that have migrated to them with very good success. BUT BEWARE: I also have numerous clients that have migrated to either FusionIO or other SSD-based storage and their crappy design/code/indexing/maintenance/etc sometimes leads to (massive amounts of) deadlocks because the system is no longer hobbled by poor IO.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1461652
Posted Monday, June 10, 2013 4:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 10:35 AM
Points: 2, Visits: 34
They released directCache (http://www.fusionio.com/data-sheets/directcache/) not so long ago. I see a lot of potential for that product given the overall effectiveness of transparent caching for accelerating a wide variety of applications. Keep in mind directCache will use FusionIO drives as a write-through cache, and as such, you'll want to avoid caching log files and possibly the tempdb system database (the data changes frequently). You can cache multiple volumes to a single caching device, and the most frequently accessed data will be stored in the cache to service read requests. All writes will always persist to the backend storage system.

Note you cannot use a single device for both cache and local storage. For example, if you wanted to house the tempdb and a caching drive on the same host, you'll need more than one card.

Most of our clients who have adopted FusionIO technology have used them to house the tempdb system database. You could always mirror two FusionIO drives inside a server for high availability and Windows will use both halves of the mirror to boost read performance. If you're using a Duo card, make sure to stripe the drives rather than span them. It is suggested that two cards be installed for HA whenever possible. Also, SQL Server 2012 supports local tempdb in failover cluster setups.

The FusionIO card driver requires physical memory to operate. The amount of memory depends on the block sizes being written to the drive. Applications like SQL Server are predictable, as they typically use the same block size for their transactions. If your database is 1TB in size, my understanding is that the total memory footprint for the driver is roughly 1.5GB.

It’s important to follow the setup directions. Down format the drive by a few GB to give the groomer process free space to operate. Otherwise, over time, after many writes to the device, you will begin to see performance degrade, which can be resolved by a format. If for instance, the 785GB drive is down formatted to 700GB, you should never have an issue with the groomer. If it's super write intense, you can also employ an SLC drive instead of MLC, but in most cases down formatting is more than enough.

The quickest way to health check for performance issues is check for avg disk sec transfer times on the ioDrive, which should average under 1ms. It’s important to trend these numbers over time to ensure that the cards are performing as expected.

Assuming the setup directions are followed, the technology is great. The amount of I/O removed from any shared storage allows that SAN or DAS to service all other requests faster. The performance and most importantly reliability of these cards often justifies the cost. Our clients who have used it as local storage devices have been satisfied, the response times can’t be beat as there’s minimal latency with the card being plugged directly into the board, not having to go over legacy SATA or SAS protocols or through any switch fabric.
Post #1461800
Posted Monday, June 10, 2013 4:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
TheSQLGuru (6/10/2013)
Tim Walker. (6/10/2013)
Can anyone share their experiences with accelerator products such as Fusion IO?

I'm interested to know how easy they are to implement and what benefits / issues people have seen in practice.

I presume that even for smaller databases that can mainly be cached, there would still be a benefit through increasing the speed the log can be committed to 'disk'.

Any comments would be greatly appreciated.

Thanks in anticipation

Tim


FusionIO is NOT an "accelerator" product as in "caching", for which there are now numerous products on the market. FusionIO is just a very fast storage device. And yes, if your data can be completely contained in memory faster tlog operations is a win - but so is flushing dirty data pages down to disk, which can be a killer on rotating media for an active OLTP system.

I have been a proponent of FusionIO since WAY before they were a house-hold name, including spending time with their lead driver developer and doing various SQL Server benchmarks, etc. I have quite a few clients that have migrated to them with very good success. BUT BEWARE: I also have numerous clients that have migrated to either FusionIO or other SSD-based storage and their crappy design/code/indexing/maintenance/etc sometimes leads to (massive amounts of) deadlocks because the system is no longer hobbled by poor IO.


The first time I heard of that problem, I thought it both ironic and fitting that you actually have to have good code to run on good devices instead of trying to use hardware to "fix" bad code.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1461802
Posted Monday, June 10, 2013 6:54 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 4,405, Visits: 6,267
I had one client that actually had to ROLL BACK to the old, slow hardware because their system was essentially unusable!! No one ever thinks you need to test out new hardware before upgrading because it might be "too fast".


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1461829
Posted Tuesday, June 11, 2013 2:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:23 PM
Points: 266, Visits: 893
TheSQLGuru (6/10/2013)
Tim Walker. (6/10/2013)
Can anyone share their experiences with accelerator products such as Fusion IO?

I'm interested to know how easy they are to implement and what benefits / issues people have seen in practice.

I presume that even for smaller databases that can mainly be cached, there would still be a benefit through increasing the speed the log can be committed to 'disk'.

Any comments would be greatly appreciated.

Thanks in anticipation

Tim


FusionIO is NOT an "accelerator" product as in "caching", for which there are now numerous products on the market. FusionIO is just a very fast storage device. And yes, if your data can be completely contained in memory faster tlog operations is a win - but so is flushing dirty data pages down to disk, which can be a killer on rotating media for an active OLTP system.

I have been a proponent of FusionIO since WAY before they were a house-hold name, including spending time with their lead driver developer and doing various SQL Server benchmarks, etc. I have quite a few clients that have migrated to them with very good success. BUT BEWARE: I also have numerous clients that have migrated to either FusionIO or other SSD-based storage and their crappy design/code/indexing/maintenance/etc sometimes leads to (massive amounts of) deadlocks because the system is no longer hobbled by poor IO.


Thanks Kevin, that's very useful feedback, and great to hear it from someone with extensive experience of the product over a period of time.

Much appreciated and thanks again

Tim


.
Post #1462382
Posted Tuesday, June 11, 2013 2:58 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:23 PM
Points: 266, Visits: 893
mkolek99 (6/10/2013)
They released directCache (http://www.fusionio.com/data-sheets/directcache/) not so long ago. I see a lot of potential for that product given the overall effectiveness of transparent caching for accelerating a wide variety of applications. Keep in mind directCache will use FusionIO drives as a write-through cache, and as such, you'll want to avoid caching log files and possibly the tempdb system database (the data changes frequently). You can cache multiple volumes to a single caching device, and the most frequently accessed data will be stored in the cache to service read requests. All writes will always persist to the backend storage system.

Note you cannot use a single device for both cache and local storage. For example, if you wanted to house the tempdb and a caching drive on the same host, you'll need more than one card.

Most of our clients who have adopted FusionIO technology have used them to house the tempdb system database. You could always mirror two FusionIO drives inside a server for high availability and Windows will use both halves of the mirror to boost read performance. If you're using a Duo card, make sure to stripe the drives rather than span them. It is suggested that two cards be installed for HA whenever possible. Also, SQL Server 2012 supports local tempdb in failover cluster setups.

The FusionIO card driver requires physical memory to operate. The amount of memory depends on the block sizes being written to the drive. Applications like SQL Server are predictable, as they typically use the same block size for their transactions. If your database is 1TB in size, my understanding is that the total memory footprint for the driver is roughly 1.5GB.

It’s important to follow the setup directions. Down format the drive by a few GB to give the groomer process free space to operate. Otherwise, over time, after many writes to the device, you will begin to see performance degrade, which can be resolved by a format. If for instance, the 785GB drive is down formatted to 700GB, you should never have an issue with the groomer. If it's super write intense, you can also employ an SLC drive instead of MLC, but in most cases down formatting is more than enough.

The quickest way to health check for performance issues is check for avg disk sec transfer times on the ioDrive, which should average under 1ms. It’s important to trend these numbers over time to ensure that the cards are performing as expected.

Assuming the setup directions are followed, the technology is great. The amount of I/O removed from any shared storage allows that SAN or DAS to service all other requests faster. The performance and most importantly reliability of these cards often justifies the cost. Our clients who have used it as local storage devices have been satisfied, the response times can’t be beat as there’s minimal latency with the card being plugged directly into the board, not having to go over legacy SATA or SAS protocols or through any switch fabric.


Thanks very much for the feedback mkolek99. I've read a bit about directCache and it looks good. You've given some comprehensive advice on using it which is great for me and other users.

Thanks for the practical advice on setup configurations too.

Much appreciated.

Tim


.
Post #1462387
Posted Tuesday, June 11, 2013 5:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
TheSQLGuru (6/10/2013)
I had one client that actually had to ROLL BACK to the old, slow hardware because their system was essentially unusable!! No one ever thinks you need to test out new hardware before upgrading because it might be "too fast".


Agreed.

As you have also but worth bringing up, I've also seen it where people spend some pretty big bucks on new hardware thinking that it will provide the performance panacea they've been looking for only to find that they get little or no performance gain simply because the code sucks THAT bad. SSD's are the likely exception to that rule.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1462423
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse