Direct-attach storage is a major improvement for things like databases, etc which you pretty much never have in the cloud, at least not in a persistent form.
Enterprise-grade SSDs is what I meant by direct-attach storage - I was comparing against network storage which is what all cloud providers use (your EBS volume is accessed over the network internally, which incurs some latency, ultimately limits IOPS and destroys random access performance which can't be cached or read-ahead by the underlying hypervisor).
“multiple nodes only give you reliability, not performance” - this, actually, is not true. Even mysql can offer ways to tune and gain performance improvements.
Presumably they would also lack the durability guarantees of the normal block storage offered by the provider.