I'm using MariaDB 10.1. How to get actual query time for all of executed query? It seems that SHOW PROFILES; was deprecated. Is there a better solution for database tuning?

Edit: It's deprecated only in MySQL 5.6.7.

  • i just checked SHOW PROFILES is still present in 10.1.18 – jerichorivera Jan 15 '17 at 12:35
  • 1
    @jerichorivera that's true. I'm wrong. It's deprecated only in MySQL 5.6.7. Is there a way to get time of all executed queries (using various drivers)? – x01saa Jan 15 '17 at 12:49

Set long_query_time=0 and turn on the slowlog to a FILE (in my.cnf, and restart). Then the slowlog will contain a variety of info about every command executed, including the query time.

You might also want to run pt-query-digest to summarize the slow log. This will include min/max/avg/etc for each 'different' query.

  • Wonderful! Thanks. Is there any performance issues with enabling slow_query_log? – x01saa Jan 15 '17 at 17:14
  • The performance impact is pretty small -- one append to file per query. However, in a busy system this could fill up the disk pretty fast. So keep an eye on disk space. You might do this for a day or week, then set long_query_time = 1 do save disk space while catching only the really naughty queries. – Rick James Jan 15 '17 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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