Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What is INSPECT in MySQL? –  ypercube Jul 20 '12 at 11:41
    
Now, for the real question, perhaps you can set a test/staging envirorment with more data/volume/traffic than the production envirorment (but all other settings identical). –  ypercube Jul 20 '12 at 11:46
add comment

1 Answer 1

up vote 1 down vote accepted
  • 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.