Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Other than usual system stats (i/o, ram usage, cpu, load, etc) I am currently collecting questions, qps, running queries and buffer pool hit.

I find qps pretty useless since our production servers uptime is very high, and its average value.

I was wondering about best practices for statistics collection for mysql production server. What other stats should I collect/monitor to understand the load on my server and act quickly without putting more stress on it?

Edit :

I am not looking for a 3rd party solution, I am already using zabbix (and ability to create hand written scripts) to collect stats / monitor our mysql cluster. There is a list of possible stats for collection in this link. And of course there are stats which are not listed here and can be collected via shell scripts. The real question is what stats must be collected to monitor our cluster efficiently without creating an unnecessary junk full of statistics.

Example : should we get Qcache_hit / Qcache_hit + queries ratio to see if our tables are hot enough ?

share|improve this question
    
Why don't you try tools like Zabbix or Cacti ? Do you need more realtime tool instead of this tools ? –  Osman Üngür Jun 20 '12 at 15:27
    
I am already using zabbix but I want the "best practices" for collecting stats, I don't want to have too many statistics collection, just the most necessary ones if possible. Thats why I need "best practices". –  frail Jun 20 '12 at 15:32
    
You are right, I flagged it for being off-topic. –  frail Jun 22 '12 at 15:08
add comment

migrated from stackoverflow.com Jun 23 '12 at 15:47

This question came from our site for professional and enthusiast programmers.

4 Answers

up vote 3 down vote accepted

Monitor everything that you can as frequently as you can. I highly recommend Graphite w/ statsd as a central location to collect all your metrics. It provides a very simple plaintext protocol that makes it trivial to log nearly any metric data and a UI that makes it incredibly easy to compare one metric against another. On my systems, I collect a ton of information and most of it has proved invaluable at some point or another. Here are a few of them:

I wrote a daemon called mysampler that send the output of SHOW GLOBAL STATUS to graphite (or csv, if you want) at regular intervals. We log this at 5s intervals, but there are times that I wish we had it set to 1s intervals. You start to see some very interesting patterns at that level of granularity. It's aware of what stats are counters and which are absolute values (Questions is a counter, Threads_running is an absolute value) and will output the deltas for the counters.

ab-tblstats2g runs from cron every night and sends table size statistics to graphite so we can track table growth. I plan to extend it to include the maximum primary key value and the number of rows (from table statistics) in the near future. It also works with MSSQL Server.

mysql_logger logs the output of SHOW FULL PROCESSLIST to syslog every X interval of time. It makes it trivial to find out exactly what was running concurrently when something goes weird (table locks, long running queries, etc). We dump that data into Splunk for easy searching, but I still sometimes just use grep in the syslog logs.

pt-stalk from the Percona Toolkit is great for "what just happened?" scenarios. It watches server status variables to exceed a certain value (Threads_connected > 25 by default, but Threads_running is usually a more valuable metric, in my experience) and when triggered, collects a bunch of data about MySQL and the system which can be reviewed with pt-sift or by just reviewing the generated files. It will even generate tcpdumps, gdb, oprofile, and strace traces.

That's basically what we monitor, which differs from alerting. For alerting, I suggest you alert on a very small number of metrics. You can cover 90% of cases by simply choosing a workload-representative query and setting a threshold on how long it takes to return. If it exceeds that threshold, alert...there is a problem. Otherwise, you're ok. No need to check "is the process running," or anything like that. Other things to look for are entries in the MySQL error log, approaching too many connections, and the how well replication is functioning (slave lag, slave running, tables in sync). Hit ratios are completely useless for alerting purposes - all that matters is that queries are returning within some period of time.

For further reading, the white paper Preventing MySQL Emergencies by the Percona folks is a good read that goes into great detail on what to monitor and alert on. Percona has also released a set of Nagios Plugins (which should work with Zabbix, I believe) that you can use.

share|improve this answer
    
thanks for your clear and detailed answer –  frail Jul 13 '12 at 5:13
add comment

I'd highly recommend using MONyog. enter image description here

MONyog MySQL Monitor and Advisor is a "MySQL DBA in a box'' that helps MySQL DBAs manage more MySQL servers, tune their current MySQL servers and find and fix problems with their MySQL database applications before they can become serious problems or costly outages.

Our DevOps team uses this extensively for both production and development. These guys have most of the "best practices" baked into the application, so we really don't need to get our hands too dirty in the DBA stuff.

share|improve this answer
    
Thanks for quick answer but we already use zabbix for monitoring, I only need to know which stats are "must" to collect for mysql monitoring. –  frail Jun 20 '12 at 20:01
add comment

Slow queries are definitely something you have to monitor.

You'll find everything usefull about the Slow Query Log here.

And you may be happy to know that we don't monitor more on our production server that what you already are doing.

share|improve this answer
    
Thanks for the kind reminder, we always check our slow query logs. I will also add Slow queries in mysqladmin status to my monitor list. I will also edit the question to make it more clear. –  frail Jun 20 '12 at 20:00
    
Oh ok, I didn't see it in your list :p –  Olivier Coilland Jun 20 '12 at 20:21
add comment

In my research I discovered that ganglia plugin (gmetric-mysql.sh) is only collecting the following stats :

Connections
Com_update
Com_select
Com_insert
Com_delete
Created_tmp_tables
Slow_queries
Qcache_hits
Qcache_queries_in_cache
Questions
Threads_connected
Threads_running
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.