4

In my production environment, I have a single instance of MySQL server running on 16gig of memory that handles up to 20,000 queries an hour. The size of one my table is growing at the rate of 2 million a per month. Both these numbers are expected to go up as time passes yet I'm not sure when I need to improve the architecture.

How could one be proactive about the situation and go about future proofing the system?

Does upgrading the hardware buy much in terms of time and capital efficiency?

What would the common practice be in this instance, if we double the traffic every 3 months, would sharding be a natural progression? Or are there other alternatives?

How do I even tell if my system is reaching it's peak, what are some of the tools available for profiling the database? And what are the metrics I would use to measure it?

Dennis Y.
  • 135
  • 1
  • 7
  • There are too many questions with too little information here, imho. What kind of data is stored, and what kind of queries are executed on it? – Tom Desp Feb 19 '12 at 11:52
  • It is essentially a transactional database with lots of read and write. There are multiple applications attached to the database and unfortunately there no natural separation that allows one application to do pure read or pure write. – Dennis Y. Feb 19 '12 at 20:20

1 Answers1

6

It's very difficult to answer such a vast question of scalability.

First, hardware upgrade on a single machine is not a long -- not even a short -- term option, as you seem to plan on an exponential growth (x2 every 3 months is big, starting from 2M rows per months). So you have to find a distributed scalable hardware architecture.

Then two basic options come into mind:

Stick to SQL

If you stick to SQL storage for your ever growing tables, you'll have to choose between clustering and replication. The latter being often more cost-effective and faster than the former, from my point of view, but a bit harder to settle.

Here, you'll find a very interesting paper on Advanced MySQL Replication Techniques.

You could then start with partitionning or better, sharding, as you mentioned previously.

Note that some MySQL products seem to offer auto-sharding clusters.

Mix with NoSQL

The other option is obviously to envisage using NoSQL technologies on your monster tables. Distributed key-value storage systems are almost costless in terms of scalability, meaning linear at most.

Another point it that key-values work gracefully with distributed caches such as the well known Memcached, very easy so set up with APIs in most languages, granting really good performances at very low cost.

Tom Desp
  • 911
  • 6
  • 19
  • 1
    I want to add - check other vendor's MySQL binaris and not only the stock server. Percona Server/MariaDB will work better on the same hardware. – Moshe L Feb 19 '12 at 18:15
  • 2
    Correct Moshe, you may gain a *linear* performance boost from other vendor's MySQL binaries, but you won't handle *exponentially growing* load with such alternatives. Still worth considering, you're right. – Tom Desp Feb 19 '12 at 20:00
  • @Tom: *"The latter being ... than the latter"*. I guess one of the two should be *"the former"* – ypercubeᵀᴹ Feb 19 '12 at 20:02
  • 1
    @TomDesp: Not only linear. MariaDB has some Query Optimizer optimizations in the later release (still as a RC, not for production) which could benefit more than linear. – ypercubeᵀᴹ Feb 19 '12 at 20:04
  • But you are right, in the long run, it would mean only linear boost. – ypercubeᵀᴹ Feb 19 '12 at 20:10
  • @ypercube: *"...than the former"*, sorry for the mistake. – Tom Desp Feb 19 '12 at 20:11