I just finished reading a post to the Media Temple blog about their MySQL problems . I think it is an excellent example of what happens when you only have one side of the house trying to fix a problem. The post leaves out some details but they make it clear that they believe their problems were caused by badly written apps hammering the database. It sounds like they tried very hard to fix the issues on the hardware and MySQL side but couldn't so have switched the way they are provisioning the database systems to more isolate the problem sites. The moral of that story is that even when you are smart you can't always fix software problems on the systems side.
The Media Temple guys don't go into any great detail on their current shared MySQL system but I would think that if nothing else they ran into the problems listed in this post: performance of complex queries. At some point you just have too many people trying to hit your database for any one person to achieve efficiency.