MySQL consumes memory for each portion of read

Question

I'm using MySQL instance for read only operations (data migration to another DB).

I'm reading several dozens of millions rows from one table using limited portions of data (100 000 rows) using select SQL_NO_CACHE * from TABLE.

After each read portion MySQL consumes about 50-100 MB of RAM, and doesn't free memory.

I have no idea, why it does consumes memory as I read data with SQL_NO_CACHE parameter. At least after several iterations of reading all memory becomes eaten and server starts dropping incoming connections.

So my question is: how to avoid this constant memory consuming for read-only usage?

UPD If it matters, it is AWS RDS instance of MySQL

Answer 1

MySQL has several "caches". Some of them grow as needed up to a pre-determined limit. This probably explains (at a high level) what you are seeing.

A well-tuned MySQL-only server will consume nearly all of RAM, then sit there quite happy.

Do you have other applications running on the same server? If so, you need to tune MySQL not to reach for filling up the server.

If you want to discuss tuning, please prove the amount of available RAM (after accounting for other processes), plus the my.cnf settings.