I have a problem with restoring databases to an RDS instance. We have about 12 000 databases, and a bash script does the restore, 10 databases at a time. After every 500 hundred database, the script sleeps for a minute, and runs the following queries: RESET QUERY CACHE;FLUSH QUERY CACHE;FLUSH TABLES;, also there is a one second sleep after every database.
I am continuously running into problems with the RDS database, once the InnoDB files are corrupted (I guess just because of the failed restore attempts, I created a new instance, and it works well), or I get this error message:
2017-09-11 06:11:43 29836 [ERROR] /rdsdbbin/mysql/bin/mysqld: Out of memory (Needed 114240 bytes)
2017-09-11 06:11:43 29836 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
After the error message, the DB instance restarts, and the restore fails. It says I should increase the ulimit, but I cannot do this on the Amazon RDS server, or even cannot add more swap space.
The database is a db.t2.medium, we would like to restore to test our daily backups and after the restoration, I obfuscate all user data and create statistics from it, so it should be fresh.
What would be the best solution to achieve this?
innodb_buffer_pool_sizein the parameter group downward from{DBInstanceClassMemory*3/4}to{DBInstanceClassMemory*2/4}to free up some memory by making the buffer pool smaller -- that is not optimal, but you need more free memory and that's the only way to get it without scaling up. – Michael - sqlbot--no-dataand try restoring only the schemata, first, then go for the full backup. TheFLUSHstatements are unnecessary. – Michael - sqlbot