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?
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.
Thanks. I was looking at mytop at one point, but the package manager wanted to pull in standard mysql dependencies, which I wasn't interested in - I guess I could create custom package without these and try it out.
But in general, I am looking for a more passive monitoring solution.
The ELK stack[1]. If you don't use the ELK stack for everything already, it might be considered an overkill as a safe deployment would take at least three nodes with 8GB of RAM at least.
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.
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.
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 :).
9 comments
[ 2.7 ms ] story [ 27.8 ms ] thread[0] http://jeremy.zawodny.com/mysql/mytop/
But in general, I am looking for a more passive monitoring solution.
[1] https://www.phase2technology.com/blog/adding-mysql-slow-quer...
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.
Thanks!
https://github.com/box/Anemometer
[0] https://www.vividcortex.com/