Yesterday we tried to alter a table holding part of the data about blog posts and news articles. After several hours of waiting for alter table to complete, we just gave up and reverted the change. It was a naive attempt, but I guess you have to do it occasionally just to see what's really wrong with MySQL (including every other SQL database). The structure of any SQL table of decent size (>10GB) is in essence immutable. You just cannot change table's structure without a prolonged downtime. We had a luxury that this particular table is part of our blog aggregating infrastructure and not part of our recommendation API. Consequently, we can afford a few hours of downtime without serious service degradation. But I can well imagine the utter frustration of some Operations people trying to bring back a failed service and waiting several hours (or even days) for a MySQL operation to finish. Zemanta's recommendation API architecture is (almost) MySQL free. We denormalize data that is originally stored in MySQL to Apache Lucene/Solr and Cassandra and serve it from there. It's interesting that even the one little MySQL database that we do rely upon when serving recommendation requests, causes us problems whenever the database backup process is running.
SQL databases are great. Nowadays everybody knows how to use them, they have efficient management tools, and some great application frameworks are built upon them. But once you go beyond the prototyping phase and you are required to maintain a certain level of service, you should start refactoring your system in order to take into account considerable limitations of SQL databases.
- Addressing Hot Schema Changes in MySQL (tokutek.com)
- Challenges of Big Databases with MySQL - IOUG Presentation (tokutek.com)
- Changing MySQL schemas without downtime (colinhowe.co.uk)