MySQL Database Scalability

Many web applications bottleneck at the database level. There can be many reasons for this and you want to make sure you’ve looked at a few items before you spend thousands on a bigger, better machine.

If you’ve peeked at the sample MySQL config files, you probably have noticed that they don’t start with very high values. Even if you’re running a few services on one machine, you are going to want to bump up several variables to make good use of your hardware. Balancing the number of Apache user connections to MySQL user connections is also very important.

Efficient, optimized queries are actually more crucial than performance tuning. Understanding how your database makes use of indexes is also critical. MySQL uses at most one index per query so you need to make sure that you created it correctly for it to be used.

If you’ve addressed the above issues and you’re still having issues you may be forced to buy new hardware. If you have a write intensive application then you will be looking at upgrading whatever your server’s bottleneck is. MySQL Cluster might be an option but there are several limitations with it. If you’re like most of the other web sites out there then you probably have a very high read to write ratio. Many sites have about 99 reads to every 1. For these scenarios, replication is a great option. With several slave servers that handle the non-write queries, you can distribute your load over a group of servers and create redundancy at the same time. Unfortunately, load balancing SQL queries isn’t quite as simple as load balancing HTTP traffic so you’re going to have to do some homework.