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?
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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.