We are developing an app, aprox 50k RPM read, 1k RPM write, it ask via key and get a JSON. The search is always made by key. I'm inclined to use one MySQL 8 table with a Id field and JSON field with innodb. Seems simple, cheap and fast accessing by index. Each index can have n rows (30 max), total size of table less than 100gb. Response time is important, I think 2-10ms are achievable on MySQL. The other, more expensive options that I have are DynamoDB and ElasticSearh (can't use another tool). Can't find a comparison for this use case to help me know if I'm in the correct path. Do you see any cons of using MySql or I'm missing something? Thanks!!
1 Answer
So any of those options EXCEPT ElasticSearch will probably work ok for you (equal hardware under the hood considered).
ElasticSearch is quite possibly the opposite use case from what you described. It's primarily best for tokenizing full text (like parsing email messages for example) and searching it efficiently, you mentioned your use case will always read by key.
A NoSQL database system or DynamoDB are actually rather fitting for your use case because of two things you mentioned: always reading by key, and storing the actual data in JSON format which allows for semi-structured, unstructured, and varying structured data. So this would be my first choice (might want to look into read limitations of DynamoDB vs other NoSQL database systems, as I thought I recall it was a little more limited based on how it was designed).
Otherwise MySQL should be able to handle your use case ok as well, being a modern RDBMS, but it wasn't really designed for your use case (serving the data only by the key and storing it all in a JSON format). It's a relational database system and therefore was designed for normalized schemas with Tables and relationships between those Tables.
-
I concur with the ElasticSearh opinion thanks!, now in this case, using the correct field type (JSON), MySQL will be slower retrieving the data? I don't know why one can be faster than the other, or better for a case like this. Like I said only have access to this 2 engines, cannot chose other. MySQL is cheaper, but I'm trying to make a stronger case for myself than "MySql because is cheaper". Thanks!! Dec 14, 2020 at 22:50
-
@Alejandro Different database systems are optimized for different use cases which mostly is to suit how the data is structured. They way they are optimized is based on a significant amount of math and relational theory, so this is why the structure of data affects which system is most performant. NoSQL was designed (speaking a high level) for use cases very similar to what you mentioned: "only reading data by the key" and semi-structured / varying structured data which is typical of JSON. MySQL was designed for relational data and handles use cases for searching against different fields...– J.D.Dec 14, 2020 at 23:50
-
But because MySQL is a modern day database system, it can handle things outside it's norm, like storing JSON. Modern RDBMS like MySQL continue to improve in being better at what they previously lacked. It just wont be the "best" solution to do so, like a NoSQL database system. Depending on how much data you have, you may not notice any difference in testing between the two, but once you get to a certain point the differences are clearer.– J.D.Dec 14, 2020 at 23:51