http://tech.noredink.com/post/133347435578/optimizing-mysql-for-high-concurrency-on-amazon
- Looking for concurrency bottlenecks
- performance schema
- List the total maximum wait by event inside of InnoDB
SELECT EVENT_NAME, SUM_TIMER_WAIT/1000000000 WAIT_MS, COUNT_STAR FROM performance_schema.events_waits_summary_global_by_event_name ORDER BY SUM_TIMER_WAIT DESC, COUNT_STAR DESC LIMIT 30;
- List current and last wait events on InnoDB.
-- "what the current, or last completed, wait for each session was, and for exactly how long they waited" SELECT NAME, IF(PPS.THREAD_ID = 0, 'Internal Thread', CONCAT(IPS.USER, '@', IPS.HOST)) USER, DB, COMMAND, STATE, TIME, EVENT_NAME LAST_WAIT, IF(TIMER_WAIT IS NULL , 'Still Waiting', TIMER_WAIT/1000000000) LAST_WAIT_MS FROM performance_schema.events_waits_current JOIN performance_schema.threads PPS USING (THREAD_ID) LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST IPS ON IPS.ID = PPS.THREAD_ID;
- Disabling the query cache
- Increasing InnoDB thread concurrency
- Increasing InnoDB open tables
