Which AWS service to use for my large MySQL table?

user_1456885 0
Viewed 160

I currently have 1 big app using a AWS RDS MySQL database. All my tables are small, but there is one large table with over 12M rows that will keep growing, that i'd like to separate into something else. This table is used for reporting and analytics.

I have thought about the following options:

  • create another RDS DB for just that big table
  • switch the DB to an Amazon Aurora cluster and use a read replica just for that big table
  • move the big table to a DynamoDB table
  • move the big table to Redshift
  • move the big table to ElasticSearch

What would you guys suggest?

Thank you!

amazon-redshift amazon-dynamodb amazon-rds amazon-web-services mysql
1 Answers

Based on the 10-15 calls per second (1000 per hour) comment you can set it up on DynamoDB. It is cheaper and faster writes. I have faced issues in high throughput writes to MySQL unless they are properly orchestrated. DDB will give you enough room to grow easily. This approach is easier if you have less time to develop.

DDB: PrimaryKey - Phone [Number], SortKey - Call Start Time In Epoch MS [Number]

Now based on your queries -

  • if they are about calls from a number: your APIs have to fetch it from DDB.
  • you might have to setup DDB streams or hourly aggregates table in MySQL which stores the aggregated data.

This will make your reports run faster and persist/update the call records faster. Incase you need accurate information regarding a particular call - make sure you use consistent reads from DDB.

On the contrary -

I had tested, MySQL can write up to 24,000 records per-second consistently using batch write. This was a dual core laptop test. ec2s can be smaller. The benchmark is much higher. If you plan to use MySQL, you have to manage the batch. Buffer the call records and batch update in an interval. Make sure each transaction commits. This will give you more aggregate capability for reporting.

user_9232270
Related