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