I want to monitor the queries which impact performance on a very busy (Percona) Galera cluster. I've yet to find a monitoring tool which can handle this. What do you use?
Innotop is great for watching things live. To review long or resource intensive queries, the Percona toolkit scripts create excellent summaries of the MySQL slow query log.<p>I use them to send these weekly reports to our developers: Slow queries, lock waits, transactions with the most rows affected, transaction by highest statement count, largest statements by rows affected, busiest tables, queries doing full table scan or full join, and busy writes.
Not sure if this will work/help on Galera but on a single mysql server I have used mytop [0] to watch for long running queries on the fly. Nothing beats the slow query log though for monitoring for problem queries. I only use mytop when there is an active issue I'm trying to debug.<p>[0] <a href="http://jeremy.zawodny.com/mysql/mytop/" rel="nofollow">http://jeremy.zawodny.com/mysql/mytop/</a>
The ELK stack[1]. If you don't use the ELK stack for everything already, it might be considered an overkill as a <i>safe deployment</i> would take at least three nodes with 8GB of RAM at least.<p>[1] <a href="https://www.phase2technology.com/blog/adding-mysql-slow-query-logs-to-logstash/" rel="nofollow">https://www.phase2technology.com/blog/adding-mysql-slow-quer...</a>
Shameless plug: I work at VividCortex[0] and we're building a database monitoring SaaS. Someone else wrote "nothing beats the slow query log though for monitoring for problem queries" ... give our tool a shot and see how it compares :).<p>[0] <a href="https://www.vividcortex.com/" rel="nofollow">https://www.vividcortex.com/</a>