What tools do you use to monitor the health of your MySQL server instance? Are they for free or commercial?
locked by HopelessN00b Dec 5 '14 at 5:03
This question exists because it has historical significance, but it is not considered a good, on-topic question for this site, so please do not use it as evidence that you can ask similar questions here. This question and its answers are frozen and cannot be changed. More info: help center.
Read more about locked posts here.
mytop provides some handy CLI info, it's free (*unix only).
I like SQLyog for remote administration of mySQL. Nice GUI environment, and there is a free community edition.
This company also makes MONyog, a mySQL monitoring solution, but it's not free and I haven't played with it.
I have a custom Zabbix configuration for extracting useful data points from MySQL. I should get around to opening these scripts up to the community.
Apart from running cacti to graph the mysql performance over time, we have mysqltuner.com setup to send us a daily report of any variables on the server we should be keeping an eye on.
innotop has been fantastic for monitoring. i believe its recommended in both editions of
high performance mysql.
As basic as it is, I've used:
$ watch -n1 "mysqladmin processlist -u root -pYouWillNeverGuessThis"
This provides me with a list of connections, users, hosts, databases, commands etc, updated every second. It's a real-time "who's doing what right now?" solution.
Very simple, but also useful.
We use hyperic, it is an open source application, but there is a commercial product as well if you want. It also monitors the operating system, web servers etc.
Don't forget to monitor the underlying platform. I would monitor
- IOPS on the log, data and tmp devices (you do have those on three seperate LUNs don't you ?)
- Swapping (more than a few pages / second is bad for mysql, google "mysql swappiness" to find out why)
- Memory statistics for the host OS. A general rule of thumb I follow is to allocate no more than 80% to the mysql process to leave some room for the OS and the buffer cache
You can use SNMP and Cacti to monitor all of these things
mtop is great for monitoring current running queries and system variables.
Cacti, as mentioned above. Also mytop sometimes to monitor current activity.
Basic monitoring via the nagios nagios_mysql plugin, added a small custom script to monitor slaves status and slavelag.
It's not strictly monitoring but you can use mysqlreport to have a good overview of how well your mysql server is performing and if it is properly configured.
Actually it depends on the database(s) that is running on the sever.
I usually check for the
- number of queries
- depending on database
- slow query log
- mysql connection from a remote host to a certain database
- check that it works from the right hosts
- check that it fails from the right hosts
- select the current time to be sure it's correct
- select for collations/encodings
- connected users
on the framework side I'm using munin for everything that could give me metrics and nagios (soon to be icinga) for everything that gives me status information.
For more comprehensive monitoring of MySQL (but commercial, so only if your database is worth it) check out www.LogicMonitor.com. No configuration required (detects storage engine types, replication status, etc); provides tuning recommendations; full historical graphing and alerting in one system; very extensible, and will monitor the rest of your datacenter infrastructure.