Using SSD is kinda "throw more hardware at the problem so that we don't need to fix our precious crappy software" type of solution.

Although abhorred by software makers such as most HN-readers, throwing hardware at the problem is often the fastest and cheapest way to tackle performance issues.

Especially when it indeed is "crappy software" that will take major effort to rewrite/refactor.

Hardware is cheaper than developers, and the former doesn't fail to deliver half as often as the latter...

The problem with this principle is that adding hardware tends to scale linearily at best whereas improving an algorithm sometimes buys you an orders of magnitude improvement.

Also, you need to compare the cost of a single developer who fixes innodb to the cost of hardware incurred by _all_ users of innodb who would benefit from the solution.

Adding SSD is not linear scaling. SSD has entirely different characteristics from rotational drives which in itself brings 1-3 orders of magnitude I/O performance improvement.

Trying to fit high I/O workloads on spinning disks is a lot like spending your efforts to make your program run on 64K RAM. An interesting challenge, that would surely exercise your coding skills, but would not deliver the optimal performance.

I'm not denying the benefits of SSD. I just don't think throwing hardware at a problem caused by bad algorithms is a good general principle. It may still be the best choice in a particular situation and timeframe.

The only thing that really matters is overall system performance, which means software / hardware combination.

It is hard to see the benefit of ignoring SSD to get high I/O performance vs. redesigning the software around I/O bottlenecks.

Redesigning software to work on bad hardware is definitely a challenging project, but not always the best use of resources.

Nothing wrong with throwing hardware at a problem like this. In most cases it's much less painful than changing to a new RDBMS.

Amazon EC2 offers 68GB RAM and it's hardly an obscure offering. But they don't offer SSDs.

SSD might be a solution, but we don't have any proof either way, and we also don't understand why is this problem happening.

We do understand why this problem is happening. The algorithm that takes the nice fast (but limited in size) sequential log writes and tries to intelligently write them (mostly randomly) in the background to the data/index blocks where they live can't keep up, and eventually stalls out as the amount of free log space dwindles.

SSDs really help this process happen faster because they have way better random write performance than hard disks.

Quoting from the article:

=============================

What to do?

I wish I could say you should use Percona Server with XtraDB.

If we were using SSD as storage, then I would recommend it. Vanilla MySQL performs equally bad on SSD and HDD, while for SSD in Percona Server we have “innodb_adaptive_flushing_method = keep_average”.

Unfortunately on spinning disks (as in this case), Percona Server may not show significant improvement. I am going to followup with results on Percona Server.

=============================

SSD is the solution because unlike rotational disks it allows for lots of random accesses. While they are too expensive to use as "archiving" storage, SSD excels as a layer between traditional disks and RAM. Put your high I/O workload on SSD and keep your historic data on spinning disks.

If you want to see some SSD benchmarks, here is your resource: http://www.ssdperformanceblog.com/

As far as cloud storage, yes, Amazon EC2 offers 68GB RAM, however if you need hardware of that size it is not hard to justify setting up your own servers. It would be cheaper too and you'll be able to fully optimize your storage.

I get random writes on the order of 5-10 mb/s at best on a typical server we use for our databases.

My macbook pro I upgraded with a gaming grade SSD gets 100-200 mb/s random write.

Just upgrading to SSDs is more viable than hacking up a database with a huge community behind it that you didn't originally create yourself.

Maybe someone didn't like your comment because of this quote _from the article_:

    If we were using SSD as storage, then I would recommend it.
    Vanilla MySQL performs equally bad on SSD and HDD, 
    while for SSD in Percona Server we have
    
    innodb_adaptive_flushing_method = keep_average.

Doesn't seem like a solution at all, if we believe the author.

Of course, you'd have to upgrade from vanilla MySQL to Percona to take advantage of this option.

Given that Percona is fully backwards compatible with MySQL, while adding this and many other tuning options, I just cannot see a reason to use vanilla MySQL ever.