To minimize down time and to detect database slow down early, we query the processlist table every second, and if we find the queries slowing down, we throttle the lower priority queries and execute other measures.
SELECT avg(time) FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE db = 'mydb'
AND user = 'app_user'
AND state != ''
AND info NOT LIKE '%ALTER TABLE%'
AND info NOT LIKE '%CREATE INDEX%'
AND info NOT LIKE '%CREATE UNIQUE INDEX%'
AND info NOT LIKE '%performance_summary%'
AND info NOT LIKE '%certain_table%'
AND info NOT LIKE '%certain_other_table%';
However, we realise that querying the processlist table is in itself pretty expensive and are looking for an alternative. We already tried
performance_schema.threads, but found that the numbers reported by it were not that accurate and it showed low numbers, even when we were having obvious database issues.
SELECT AVG(PROCESSLIST_TIME) FROM performance_schema.threads
Another alternative is to just have our application track average times itself, but was wondering whether there may be another database specific solution possible.
EDIT: To clarify, the above query is run real time with no human intervention. If the application detects degrading performance, it immediately throttles down other queries to try to get the overall performance back up. Everything is automated.
Granted process list contains many idle queries, but this system has worked for us for many months now, and we are just trying to implement a "no temporary table on disk" solution.