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
SELECT 1 FROM TABLE2 WHERE (
last_parsedIS NULL OR
last_parsed< DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY)) AND
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
idis 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-digestto summarize the log. (This will also provide info on poorly performing queries.)
Plan B: Turn on the general log. Use
pt-query-digestto 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.