statistics on what queries were executed and how many times



  • I'd like to monitor how many times certain queries are executed. Take a look at the 3 queries below, the second and third are written as prepared statement.

    SELECT COUNT(*) FROM TABLE1
    

    UPDATE TABLE2 SET last_parsed = NOW() WHERE id = ?

    SELECT 1 FROM TABLE2 WHERE (last_parsed IS NULL OR last_parsed < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY)) AND id = ?

    Does mariadb/mysql dbms keep track of how many times each query is executed? Does it group the prepared statements into one listing, so that if id is 10 or 20, they both show under the same category?



  • MySQL and MariaDB...

    Plan A: Turn on the slowlog. Have long_query_time = 0. Use pt-query-digest to summarize the log. (This will also provide info on poorly performing queries.)

    Plan B: Turn on the general log. Use pt-query-digest to summarize the log.

    Caution: Both techniques use a lot of disk space and do not clean up after themselves. That is, you will need to deal with the disk space used. if you run this for a long time.

    The 'digest' removes actual numeric and string values. That is "... WHERE x=2" and "... WHERE x=987" are considered the "same". If you need "same" to include same values, then you should plan on using the general log and do your own post-processing.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2