Analyze MySQL Performance

Slow queries and Slowlog

MySQL optionally create a slowlog file or table.

Setup:

• Set long_query_time = 1 – Preferrable in my.cnf We may change that threshold up or down later, but this is a reasonable start.
• Set up the slowlog to be captured to FILE.
• Turn on (the details of this vary with the Version)
• Wait at least 24 hours.

Writing slow_log to file – this is preferred, since there are tools for condensing such:

log_output = FILE
slow_query_log = ON
slow_query_log_file = (fullpath to some file)
long_query_time = 1
log_slow_admin_statements = ON
log_queries_not_using_indexes = OFF

Notes

Understanding MySQL Queries with EXPLAIN

SHOW PROFILE

The SHOW PROFILE and SHOW PROFILES statements display profiling information that indicates resource usage for statements executed during the course of the current session.