0

I have a AWS RDS t2.micro storing over 150 million records in a table from which i need to make a select insert query to another table, the purpose of this select query is to make a percentage calculation into second table.

I tested this query in a testing db with less than 100 rows(same t2.micro) and it ran correctly. I think the query is being canceled because of the t2.micro capabilities.

I'm considering upgrading the RDS from t2.micro to r6g.large, but im not sure if this will solve my problem, any suggestions?

Christopher Martinez
  • 101
  • 2
  • Suggest you consider some kind of timeout setting. Google found this page as a starting point https://aws.amazon.com/blogs/database/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-3-parameters-related-to-security-operational-manageability-and-connectivity-timeout/ – Tim Apr 04 '21 at 08:14
  • Let's look at the query -- 12 hours is far more than makes sense, even for 150M rows. Maybe there is a simple fix to make it faster. – Rick James Apr 04 '21 at 16:20
  • This is the question to improve the query, on mysql 8.x https://dba.stackexchange.com/questions/289231/select-insert-query-optimization-on-large-volume – Christopher Martinez Apr 05 '21 at 05:16

1 Answers1

2

Why don’t you try it? M6g.large costs $3.60/day - you’ll hardly get broke on that. Also you can use a bigger instance for inserting the records and downsize when done.

Beside that check out the Monitoring tab in the RDS console and see if anything happened at around the time your query failed. You may want to enable ‘T2 Unlimited’ to prevent running out of CPU credits.

Hope that helps :)

MLu
  • 24,849
  • 5
  • 59
  • 86