The thing that impresses me most about MySQL are the MySQL tech staff. These are a bunch of guys I consider myself privileged to have worked with.
The trainers were also pretty hot. Neither group gives any sales BS. They are totally upfront and honest about what their product will and won't do. That means they inspire confidence.
Being upfront about the limitations means that we design a solution accordingly.
We looked at the various engines when we evaluated MySQL.
Memory engine is fast but writes cause table locks. Also all strings become fixed length. VARCHAR(255) becomes CHAR(255). As a non-expiring cache it is unsurpassed.
Blackhole engine will run any SQL statement it just doesn't store data. It is useful as a relay station for replication. Replication is Master to Slave and the MySQL guys recommend that the maximum number of slaves is 8. Go beyond this and you replicate to 8 black holes, each of which have 8 slaves.
Archive engine is really a compressed MyISAM.
One of the MySQL guys told me that the origins of the pluggable engine architecture came about because the developers didn't like having to wait to recompile MySQL so they split the storage and query engines to shorten recompile times. Serrendipity!
As for size, it depends on what you are doing. For a read-only store you can get really big databases. One of ours in near 1TB and gives acceptable performance.
MySQL also works well if you want massive write capability such as a logging application. Where it all gets a bit hectic is if you want massive reads AND writes.
Backups are a pain. We really need a Litespeed equivalent for compressed backups.
Although I have seen religious flame posts on DB engines there seems to be less of it than with, say, OS flame wars. I'm probably wrong but I feel that DBAs are interested in databases and database technology per se rather than any particular heresy.