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
This is an awesome post.
To answer your final question, I'd speculate that your answer is "yes".
That said, I probably would have pursued soft numa before resorting to the trace flags. I think you are right about the numa node allocation and that's could be at the root of your problem. Via soft numa, you could scale out the requests, depending on your count of numa nodes (4?) - to 4, if that's the correct number, and then assign, via ip address, each host to a specific numa node, in addition to that, I'd disable hyper threading. Combined, the issue would likely decrease, however, it would do so at the cost of fewer schedulers.
On a seperate thought, I'd look at forced parameterization - the fact that your load is driving your CPU so high is very interesting and it may be worth looking into that.
Lastly, on multi-numa node systems, I typically have the output of the following queries dumping to a table every N seconds. Makes for some interesting analysis when workload changes or trace flags are implemented:
SELECT getdate() as poll_time, node_id, node_state_desc, memory_node_id, online_scheduler_count, active_worker_count, avg_load_balance, idle_scheduler_count
FROM sys.dm_os_nodes WITH (NOLOCK)
WHERE node_state_desc <> N'ONLINE DAC'
and
SELECT top 10 getdate() as sample_poll, wait_type, count (*)
FROM sys.dm_os_waiting_tasks
WHERE [wait_type] NOT IN
('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK',
'SQLTRACE_BUFFER_FLUSH','WAITFOR', 'BROKER_TASK_STOP',
'BROKER_RECEIVE_WAITFOR', 'OLEDB','CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT' )
GROUP BY wait_type
ORDER BY COUNT (*) DESC
Best Answer
Database "warm up" is a real thing... but then again, people making excuses for under-performing systems is also a real thing. An important take-away here is that once a server is warmed up, it doesn't generally spontaneously cool back down again.
Warm-up is needed because the best performance of a database is achieved when its various caches are populated with the most relevant data, since reading from memory is generally substantially faster than reading from disk.
InnoDB has the InnoDB buffer pool. MyISAM has the key cache and the OS cache. Then there's also the MySQL query cache. MySQL has the open table cache. Some or all of these structures contribute to the ideal performance of a server. When a server is handling traffic that is characteristic of its workload, it tends to have the most frequently used -- and therefore presumably the most relevant data -- already cached.
Take the load off of an active MySQL server and you will not see it start closing tables and evicting data from its caches. If you leave it running, then come back to it tomorrow, you should find that it's still as warm as you left it.
The need for a warm-up is not typically something that is a relevant consideration needed unless the server process is restarted. Percona Server has a feature that can pre-warm a server's InnoDB buffer pool on restart, by storing the pointers to the pages that were in-memory before shutdown and reloading those pages again into the pool on startup.
However, you can also undo a well-warmed system to some extent by taking a full backup from the client-side of the server, such as with
mysqldumpand most graphical tools, but not backup utilities that understand native tablespaces, like innobackupex (if my understanding of how that utility works is correct), since a large portion of the data being backed up may be data that is older or irrelevant to the primary workload but may still cause the eviction of other data from the various buffers and caches as it is loaded in, on its way back out for the backup.The logic in the question you referenced is that reading from the tables and their indexes brings them into memory -- warms things up -- before the "real" queries jump in to start doing their work.
Generally speaking, though... a single query, run once, will have warmed up whatever structures were needed to execute that particular query. If the same query, executed again immediately, is still slow, then warm-up is not the issue. Indexes are not reloaded every time you use them, unless there is competition for space in the buffer and cache structures.