Mysql – Reviewing all new MySql queries on staging environment

MySQLperformancequery-performanceslow-log

The problem I am trying to solve: It's well known that not all developers know what is EXPLAIN in MySql, and not every knows how to use it. Large part of them consider MySql as a black box, no matter what is the query I write, I expect maximal response time and right result. I think you recognize this situation. Of course more experienced developers should educate them, but I am looking for best practices how to prevent the situation when bad, stucking query goes over QA team, and takes down production environment.

Approaches to solution: we might have few approaches, the first one, do review of the code of the next deployment. It's not to convenient, might take a lot of time, but very effective. The second one, try to identify slow queries on staging environment by looking at slow_logs, this approach is very easy, but not to efficient, data on staging is not the same data as data on production, therefore slow query on production can be completely good query on staging.

What's your opinion, how to prevent bad written, slow query to go to production environment?

Best Answer

  • These are some tools suggested by MySQL Performance blog post: http://www.mysqlperformanceblog.com/tools/

  • pt-query-digest can be useful to review queries as well It can review a slow log, saving results to the test.query_review table in a MySQL server running on host1. See --review for more on reviewing queries:

    pt-query-digest --review h=host1,D=test,t=query_review /path/to/slow.log

  • Using Show Profiles also you can review query performance

http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html

SET PROFILING = 1; 
SET profiling_history_size = 0; 
SET profiling_history_size = 15; 
<Your own query>; 
SHOW PROFILES; 
select state, round(sum(duration),5) as `duration (summed) in sec` from information_schema.profiling where query_id = 11 group by state order by `duration (summed) in sec` desc; 
SET PROFILING = 0;

When EXPLAIN is used with the EXTENDED keyword, the output includes a filtered column not otherwise displayed. This column indicates the estimated percentage of table rows that will be filtered by the table condition. In addition, the statement produces extra information that can be viewed by issuing a SHOW WARNINGS statement following the EXPLAIN statement. The Message value in SHOW WARNINGS output displays how the optimizer qualifies table and column names in the SELECT statement, what the SELECT looks like after the application of rewriting and optimization rules, and possibly other notes about the optimization process.

Related Question