> Can someone suggest when it is actually useful over a properly tuned relational database?

In your own question you kind of hint at it. You need a tuned database. NoSQL lowers the barrier to entry for fast persistent data storage with replication. NoSQL doesn't replace SQL databases, it's looking to optimise a use case where transactions are not required.

Personally I use Mongo a bit like a cache, sitting in front of a SQL DB. Things that need to be ACID are handed back and forth. Those that don't are kept in Mongo and I get the best of both worlds.