Technical

MySQL Database Scalability

PINT Team

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.

Related Articles

Designing for Digital and Print

Having consistent messaging, look, and feel is essential to providing a seamless experience for users. Translating that across digital and print can be a challenge. ...

Rob McFarlane
By Rob McFarlane
Oct 29th, 2018

All The Tests: PINT’s Overview of Web Testing

To help you get started with testing, we’ve compiled some of the basics: Types of Tests you’ll want to consider and the concept of The Testing Pyramid. ...

Preston Resenbeck
By Preston Resenbeck
Sep 18th, 2018

Tell us about your project

Please fill out your information and submit
X