The question:
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?
The Solutions:
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.
Method 1
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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0