Post reply

Anyone already use TEMPDB in RAMDISK ?

  • January 29, 2007 at 9:13 am

    #115429

    Hello,

    Is anyone already use a Ramdisk with TEMPDB ?

    I'd like to have information on this (Does it work well for you ?, what's product to use ?, is it really boosting performance ?).

    I'd like to advise this to my clients because some servers heavily use the disk where tempdb is, so I'd like to have informations with already tested solutions.

    Syncerly yours, David.

     

  • January 30, 2007 at 6:12 am

    #686419

    Tony Rogerson did some work on this

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx

    There are other ways to boost tempdb performance, multiple files for instance. I find many people don't allocate enough spindles to tempdb when they seperate it and then degrade performance further , or put it on raid 5 - which is a really bad idea!

    My worry would be the limitations of the size of the ramdisk, but I did ask Tony if you could raid them up to provide more space.

     

     

     

     

     

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • January 30, 2007 at 12:21 pm

    #686568

    A number of years back I did some benchmarking on the use of RAM DISK (back then it was called 'solid state' disk). I tried a number of configurations with various things residing on RAM disk.

    • tempdb data
    • tempdb log
    • tempdb data and log
    • user database data - data and index
    • user database data - data only
    • user database data - index only
    • user database log

    The databases also used SAN disk as well. The biggest bang for the buck was when only the user database transaction log was on RAM disk - 400-500% improvement in throughput. All of the other remaining combinations were nominal - 10% or less improvement. Now if you are using local disk as opposed to SAN disk, these statements probably will difffer significantly.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • January 31, 2007 at 2:00 am

    #686752

    Thanks a lot.

    I already read the blog of Tony.

    Yes the Tempdb is on a local disk (C Drive and their SAN also have a very poor throughput : Half of the local disk

    On a file copy basis the IBM FastT600 SAN they have give about 25 MB/sec. The Local disk about 50MB/sec (HS20 on a Blade Center). Another Client with a FastT700 Give 60MB/sec.

    I saw the disk was used very intensively with the tempdb file (I used the performance monitor and filemon). That was why I think to put the tempdb on a ramdisk.

    The transaction log on a ramdisk seems to be dangerous in case of a system crash.

     

  • January 31, 2007 at 5:46 am

    #686791

    Hi Delsouc,

    Had some experience with testing out Ramdisks. We found that there were some benefits performance-wise, but not as much as expected because we simply moved the throttle points (no quite bottlenecks) elsewhere. Spoke to a guy who had put this technology in an investment bank. They had anticipated 30% performance gain, but got around 10% due to hitting other bottlenecks. So, need to consider holistic aspect as well if you really want to see marked improvements.

    As for the issues regarding the dangers of systems crash and TLogs, the ones we looked at had redundancies built in, such as redundant HDDs and long-life battery systems, so this shouldn't be a problem. Also, there are Terabyte Ramdisks available now, too. But the biggest inhibitor is cost, they are still rather expensive. 

    Rgds iwg

  • January 31, 2007 at 8:34 am

    #686875

    Just a simple reply to the statement regarding what about TEMPDB transaction logs in ramdisk and crash. Don't forget TEMPDB is recreated everytime you boot up your machine, which is why most SQL books say it is OK to put it on a RAID 0 disk; why? because it does not matter if the RAID0 crashes and you lose your TEMPDB. (RAID 1 would keep your machine running though.)

     

     

  • February 1, 2007 at 3:35 am

    #687053

    yeah raid 0 is fine except if you have transactions running which use tempdb, then you could suffer an inconsistent transaction, you have to decide the risk vs cost vs performance with regard to the data integrity of your data and application. A raid 0 failure would of course bring the server down, not good.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

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

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