AWS RDS MySQL Slowing down over time

Solution 1:

T2 and T3 instances (incl db.t2 db.t3 instances) use CPU Credit system. When the instance is idle it accumulates CPU Credits that it can then use to run faster for short periods of time - Burst performance. Once you deplete the credits it slows down to a Baseline performance.

One option is to enable T2/T3 Unlimited setting in your RDS config which will let the instance run at full speed for as long as needed, but you will pay for the extra credits needed.

The other option is to change the instance type to db.m5 or some other non-T2/T3 type that supports consistent performance.

Here is a more in-depth explanation of the CPU credits and how they are accrued and spent: On clarifying t2 and t3 working conditions?

Hope that helps :)

Solution 2:

• Single-row INSERTs are 10 times as slow as 100-row INSERTs or LOAD DATA.

• UUIDs are slow, especially when the table gets large.

• UNIQUE indexes need to be checked before finishing an iNSERT.

• Non-unique INDEXes can be done in the background, but they still take some load.

Please provide SHOW CREATE TABLE and the method used for INSERTing. There may be more tips.

---

Solution 3:

Each time you commit a transaction index(es) need to be updated. The complexity of updating an index is related to the number of rows in the table, so as the number of rows increases, the index update becomes progressively slower.

Assuming you are using InnoDB tables, you can do the following:

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
ALTER TABLE table_name DISABLE KEYS;

Then do the inserts, but batch them so that one statement inserts (e.g.) several dozen rows. Like INSERT INTO table_name VALUES ((<row1 data>), (<row2 data>), ...). When the inserts have finished,

ALTER TABLE table_name ENABLE KEYS;
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

You can adjust this for your own situation, for example if the number of rows is huge then maybe you want to insert half a million then commit. This assumes your database is not 'live' (i.e. users actively reading/writing to it) while you are doing the inserts, because you're disabling checks that you might otherwise rely on when they enter data.