One Milly-yon IOPS - Database Weekly (Sept 1, 2008)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715370

  • Jeff Moden

    SSC Guru

    Points: 994523

    If today's programmers can count on faster disk drives, essentially memory speed, are we going to see more and more bad SQL in tomorrow's databases?

    Heh... The answer, just like horizontal surfaces accumulate dust, is "Absolutely". 🙂

    You, me, and a couple of other good folks on this forum have all been involved in the wars over a "hardware solution" and taking the time to write correct code You know... the same old saw about "time to market" and the like. Even you have said, in the past, that you'd rather spend the money on more robust hardware than to spend the time and retest time on fixing and retesting code for performance and scalability... I think the quote was something like "gives you more time to ride horses." 😉

    ... they just gave the advantage to the hardware, for the time being, but Crap code will eventually overwhelm even this ingenious technology, as well. Heh, and that's alright because regardless of how good they make the hardware, crap code will keep folks like me employed forever. 😛 After all, a triangular join will always be a triangular join no matter what the hardware is. :hehe:

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • RBarryYoung

    SSC Guru

    Points: 143327

    The first is that I wonder if all the query optimizer's various algorithms for read-ahead, costing, etc. will be rendered obsolete. (by Solid State Disks)

    Yes, "Absolutely". 🙂

    The large majority of the reading, writing and caching strategies for hard drives centers on the fact that sequential sector IO's are assumed to be approximately 10x faster than random sector IO's.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715370

    What about a fast triangular join? Maybe it will flatten out to a linear algorithm :hehe:

  • RBarryYoung

    SSC Guru

    Points: 143327

    Steve Jones - Editor (8/31/2008)


    What about a fast triangular join? Maybe it will flatten out to a linear algorithm :hehe:

    Oh, it'll be faster, but it will still be an O(n2) algorithm. The whole point of Big-O notation in algorithmic analysis is that the highest power will always dominate everything else in the end.

    For instance, the notorious Bubble Sort is a Triangular method and thus O(n2). That's why, even when a sort is entirely in memory any self-respecting programmers will always use some O(n*Log(n)) algorithm, such as Mergesort or Randomized Quicksort (my choice) intead of Bubble sort.

    What it does change is the point at which the difference between the two becomes unbearable.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Jeff Moden

    SSC Guru

    Points: 994523

    Steve Jones - Editor (8/31/2008)


    What about a fast triangular join? Maybe it will flatten out to a linear algorithm :hehe:

    Shoot! It's flat now... all you need is some Log/Log paper to plot it on 😛

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Gail Shaw

    SSC Guru

    Points: 1004446

    rbarryyoung (8/31/2008)


    The large majority of the reading, writing and caching strategies for hard drives centers on the fact that sequential sector IO's are assumed to be approximately 10x faster than random sector IO's.

    Isn't it the opposite for flash? Random IOs faster than a traditional drive (because there's no drive head to reposition), but sustained throughput is slower?

    If today's programmers can count on faster disk drives, essentially memory speed, are we going to see more and more bad SQL in tomorrow's databases?

    Of course, because there are still going to be people writing queries with just the smallest amount of SQL knowledge (enough to do the job, not enough to know where the pitfalls are). There are still going to be people who just want it done and someone else's problem. There's still going to be the 'but it worked fine in dev' or 'it must be a problem with your server config' types.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Jeff Moden

    SSC Guru

    Points: 994523

    GilaMonster (9/1/2008)


    If today's programmers can count on faster disk drives, essentially memory speed, are we going to see more and more bad SQL in tomorrow's databases?

    Of course, because there are still going to be people writing queries with just the smallest amount of SQL knowledge (enough to do the job, not enough to know where the pitfalls are). There are still going to be people who just want it done and someone else's problem. There's still going to be the 'but it worked fine in dev' or 'it must be a problem with your server config' types.

    Glad I'm not the only voice heard on that. 😛

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • RBarryYoung

    SSC Guru

    Points: 143327

    GilaMonster (9/1/2008)


    rbarryyoung (8/31/2008)


    The large majority of the reading, writing and caching strategies for hard drives centers on the fact that sequential sector IO's are assumed to be approximately 10x faster than random sector IO's.

    Isn't it the opposite for flash? Random IOs faster than a traditional drive (because there's no drive head to reposition), but sustained throughput is slower?

    True, if you're talking about a USB Flash Drive, but SSD's are usually made with faster/better grade memory and designed for throughput equal to the bus speed (as hard disks are). Of course they cost more too.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715370

    A lot more, but that's changing. We might get SSDs sooner than we think.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    rbarryyoung (9/1/2008)


    True, if you're talking about a USB Flash Drive, but SSD's are usually made with faster/better grade memory and designed for throughput equal to the bus speed (as hard disks are). Of course they cost more too.

    Good to know. Thanks

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail Shaw

    SSC Guru

    Points: 1004446

    Jeff Moden (9/1/2008)


    GilaMonster (9/1/2008)


    Of course, because there are still going to be people writing queries with just the smallest amount of SQL knowledge (enough to do the job, not enough to know where the pitfalls are). There are still going to be people who just want it done and someone else's problem. There's still going to be the 'but it worked fine in dev' or 'it must be a problem with your server config' types.

    Glad I'm not the only voice heard on that. 😛

    I used to work with a 'but it worked fine in dev' developer. No matter how many times his code failed a code review, no matter how many times he was shown how a better way of doing things, he wrote terrible SQL. I don't know what his C# was like and I don't want to know.

    When I did unofficial SQL training sessions, he was the one person who never came. Never took advantage of the book collection we had, only questions he ever asked was of the form 'What's the quickest way to do this?' (and by quick, he wanted the quickest to write that involved the least thought)

    When things broke or ran slow, it was always some one else's fault.

    His answer to the problem of constently failing code reviews - appeal to his manager to allow him to bypass code reviews because of the tight deadlines he was under.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • jim.powers

    SSCrazy Eights

    Points: 8698

    One thing I don't quite understand is why these SSDs are using flash type memory for server solutions (due to its limited life span)? For laptops, absolutely, they need to maintain the data on them when the power is turned off.

    For server solutions, consider the following scenario:

    - Use regular RAM (instead of flash) for the drive since it is considerably faster (yes, I know it is susceptible to power failure). It would probably be good to "RAID" the RAM so RAM modules could fail without losing data.

    - Add to that multiple redundant power supplies, a HDD array of the same size as the "ram disk" and redundant UPSs.

    - A smart control system that constantly backs up the live data to HDD to help prevent data loss due to a power failure. Ideally, the "live" data would be mostly backed up at any given point in time so at the point of power failure, it would only take a few minutes to complete the backup before the batteries fail.

    - When the power is restored, the smart control system would load the data from HDD back to the "ram disk". It would be smart enough to allow for immediate access to the data while loading it to the "ram disk" so the production system wouldn't have to wait.

    Would this exceed 1 million IOPS?

  • RBarryYoung

    SSC Guru

    Points: 143327

    jim.powers (9/2/2008)


    One thing I don't quite understand is why these SSDs are using flash type memory for server solutions (due to its limited life span)? For laptops, absolutely, they need to maintain the data on them when the power is turned off.

    For server solutions, consider the following scenario:

    - Use regular RAM (instead of flash) for the drive since it is considerably faster (yes, I know it is susceptible to power failure). It would probably be good to "RAID" the RAM so RAM modules could fail without losing data.

    - Add to that multiple redundant power supplies, a HDD array of the same size as the "ram disk" and redundant UPSs.

    - A smart control system that constantly backs up the live data to HDD to help prevent data loss due to a power failure. Ideally, the "live" data would be mostly backed up at any given point in time so at the point of power failure, it would only take a few minutes to complete the backup before the batteries fail.

    - When the power is restored, the smart control system would load the data from HDD back to the "ram disk". It would be smart enough to allow for immediate access to the data while loading it to the "ram disk" so the production system wouldn't have to wait.

    SSD configurations like this have existed for well over 20 years. The problem is (and always has been) that compared to Hard Drives they are small and extremely expensive. Flash memory changes this equation because it more readily approaches a cost comparable to Hard Drives.

    The limited Write-Life problem is addressable through a number of different means.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715370

    And the technology is getting better and better. Some of these new SSDs have great specs.

Viewing 15 posts - 1 through 15 (of 15 total)

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