Just spotted this article by Anand Rajaraman about an Indian version of Twitter that uses mostly mobile SMS rather than web access, and is built on Java and MySQL. It handles many more users and messages than Twitter currently does, and seems to be doing ok.
Anyway, have a read, the usual obvious architecture choices for scalable database apps are in there, but it’s always good to read how people have actually applied these things and made something real work – serves better as an example for others… 😉
On the subject, seen http://identi.ca/?
It uses MySQL as well.