Hi Folks,
I had configure percona plugin on zabbix 3.0 for MySQL server, it is showing graphs but I want to know what query is creating locks. Is there any way to find which is query is slow or which is creating the deadlock / table locks ?
Thanks,
Hi Adnan,
you can use Percona Monitoring and Management, it is fully open source and it has good query analytic inside. It will show you slow queries and explain them.
https://www.percona.com/software/database-tools/percona-monitoring-and-management
Thank you Mykola. Using Percona plugin in zabbix i can see a graph that shows some tables are locked or creating deadlocks but I cannot see that which table or databases doing it. How can I check it in Percona plugin ? Or can i see this in Percona Monitoring and Management ?
Percona Monitoring and Management has Query Analytics tool.
Query Analytics enables you to analyze MySQL query performance over periods of time.
This tool shows queries and explain them.
[LEFT]
Hi Adnan,
Monitoring plug-ins should be quick and produce as minimal load as possible, deadlock investigation is not quick procedure.
You may want to investigate the Percona Toolkit pt-deadlock-logger utility which may do what you want. You can read about it here: https://www.percona.com/blog/2012/09/19/logging-deadlocks-errors/
–Dave
I already check out the Toolkit but I am more towards self alerts and GUI based results, don’t want to run on terminal.
Hi adnan,
I created feature requests for you:
deadlock monitoring in zabbix plugins: https://bugs.launchpad.net/percona-monitoring-plugins/+bug/1659518
deadlock monitoring in PMM: https://jira.percona.com/browse/PMM-540
feel free to add any additional comments inside tickets.
Thank you Mykola. It would be very helpful for me as well as for the community.
I have an other question related to Percona Server. If I switched my MySQL to Percona MySQL then can I achieve the feature that I want ?
Thanks,
If you want get list of deadlocks in mysqld error log, you can enable innodb_print_all_deadlocks feature in MySQL.
https://dba.stackexchange.com/questions/87350/view-last-several-innodb-deadlocks
You can use this option in any MySQL server.
Buddy I clear my concern in the Reply #7. I can do this in my current environment but I want the feature that can alert when deadlock lock and it would be GUI based. Don’t want tun the CLI commands or append the output of that command
Hi adnan,
We don’t have a deadlock notification feature in our Zabbix plugin at this time.
You may be able to create one using the pt-deadlock-logger CLI in conjunction with Zabbix actions and triggers.
–Dave