Your first query is asking for 20,000 rows. what is being victimized here ???
Your Innodb Buffer Pool and Log Files.
Because of SELECT * FROM history WHERE log LIKE '%a%' ORDER BY log ASC LIMIT 0, 20000;, all the data pages for the history table is hogging InnoDB resources.
Since you are using Amazon RDS, you cannot resize the InnoDB Log Files. They are always 128M under all RDS MySQL models. Your buffer pool may or may not be full (You have 5882511360 set for it, which is 5610M or 5.4785G). Naturally, bigger model means more IOPs.
You can run this to see how full the buffer pool is
SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;
Another aspect I see is the log field (TEXT). You are asking for an ORDER BY on this big column.
You need to change the first query to throttle the SELECT. In other words, fetch smaller chunks of history.
If you cannot change the data or the query, there is nothing you can configure except to shift to m1.xlarge (Comes with 11922309120 as the Buffer Pool Size = 11370M = 11.1035G) although log file size would still be stuck at 128M.
Infrastructure aside, it is possible for SELECTs to block INSERTs, UPDATEs, and DELETEs in InnoDB.
I wrote about this before
Best Answer
I can't address the MySQL specifics, but in general, yes, memory usage of database engines will tend to increase over time. As queries are processed, if the data isn't in memory, the engine will load the data from disk into memory. Then, as long as there are no other pressing needs to use that memory, the data will be left there in case it is queried again.
So as a database engine starts up, it'll use a moderate amount of memory. (Some will allocate a "minimum memory" just to have it ready for when they need it.) Then, as it runs, the memory usage will gradually increase, until it reaches either:
Only then will it start ejecting data from memory to make room for more data coming in from disk.
The specifics vary from DBMS to DBMS, and I've used some terms here that are inspired by SQL Server's terminology. But the general concepts should hold for most engines.
I suspect you'll get other answers with better MySQL details, or a better explanation of memory usage and caching and pools. But I hope this summary is still useful.