MySQL capacity planning

Question

In my working environment, I have one instance of a MySQL server running on 16 gigabyte memory that can handle up to 20,000 queries per hour. The size of my table is growing at a speed of 2 million a month. Both of these numbers are expected to grow over time, but I'm not sure when I need to improve the architecture.

How can we proactively relate to the situation and go about the future verification of the system?

Does hardware upgrades help in terms of time and capital efficiency?

What would be the usual practice in this case, if we doubled the traffic every 3 months, would the fragments be a natural continuation? Or are there other alternatives?

How can I tell if my system reaches its maximum, which of the tools are available for profiling the database? And what indicators will I use to measure it?

Answer 1

It is very difficult to answer such an extensive question about scalability .

Firstly, updating the hardware on one machine is not a long - not even a short-term option, since you plan on exponential growth (x2 every 3 months is big, starting from 2M lines per month), So you need to find a distributed scalable hardware architecture.

Then two main options come to mind:

Stick to SQL

If you use SQL repository for ever-growing tables, you will need to choose clustering and replication . The latter, from my point of view, is often more cost-effective and faster than the first, but a little more difficult to solve.

Here you will find a very interesting article about Advanced MySQL Replication Techniques .

You can then start by partitioning, or better, sharding , as you mentioned earlier.

Please note that some MySQL products appear to offer auto-fragmentation clusters .

Mix with NoSQL

Another option, obviously, involves using NoSQL technologies on your monster tables. Distributed storage systems of key importance are almost not required in terms of scalability, which means linearity no more.

Another point is that key values ​​work exquisitely with distributed caches, such as the well-known Memcached , very simple, with API settings in most languages, providing really good performances at a very low price.