Post reply

Accelerator products such as Fusion IO

  • June 10, 2013 at 9:33 am

    #294311

    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

    .

  • June 10, 2013 at 10:09 am

    #1622523

    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 on googles mail service

  • June 10, 2013 at 4:27 pm

    #1622651

    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.

  • June 10, 2013 at 4:31 pm

    #1622653

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 10, 2013 at 6:54 pm

    #1622676

    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". :hehe:

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • June 11, 2013 at 2:51 pm

    #1623144

    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

    .

  • June 11, 2013 at 2:58 pm

    #1623149

    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

    .

  • June 11, 2013 at 5:05 pm

    #1623182

    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". :hehe:

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply