"Is that soutbl? And also desttbl? "
Yes all subject table structure indentical but shared for load sharing reason.
"And the SELECT looks something like
INSERT INTO bb SELECT * FROM aa WHERE C4 < '...' LIMIT 1"
Actually the "fifo feeder" where is WHERE C2=[selectedvalue]. This select nearly dozen row (group of). But according our tests this provide a same load like WHERE C4 < '...'. Remember C2 and C4 indexed.
The "fifo purger" is DELETE FROM bb ORDER BY C4 ASC LIMIT n. The n is nearly 100 detremined by complex calculation based on TABLE STATUS of bb.
This two operation take a way the 10+40 (feeder+purger) percent of CPU. Again looks like the precentage just depend from number of "parking" rows in bb table.
"If the WHERE clause is more complicated, it might be using tmp tables on disk, which could be hitting the "other mount". "
Exchause me but the config become littelbit complex. In that case the tmpdir pointed to separated directory of the ramdisk.
"Please do EXPLAIN SELECT ... with the actual select that is in the INSERT...SELECT."
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | aa | ref | C2 | C2 | 5 | const | 3 | Using where |
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+