When monitoring MySQL performance, which metrics are most important to watch?

Approaching monitoring from this point of view suffers from the "tail wagging the dog" problem that you watch what metrics are available to watch, instead of watching what is most important. It is better to reason from first principles and arrive at a conclusion of what you need to monitor, and then find a way to do that. I will demonstrate what I believe to be the correct approach: Your MySQL server's purpose is to execute queries you send it. Therefore you need to monitor queries, above all. Of all the things you care about in query monitoring, the most important is performance. Performance can be measured by what the user cares about. Cary Millsap's book Optimizing Oracle Performance, and his Method R, are highly recommended reading. "I sent this query to the server. I don't care what it does as long as it returns the correct answer, quickly." Therefore you should monitor queries for latency and errors/warnings first and foremost. Additionally, you need to monitor that queries are consistently fast and correct; this means that longterm averages won't suit, because short (1-second to 10-second) issues will be lost in averages over 1 or 5 minutes and you'll miss them. Now, with that in mind, you need to be measuring query performance at a very high level of granularity. Such information is primarily exposed in the slow query log, the PERFORMANCE_SCHEMA, or the TCP network traffic. I have written about this in detail on my company's blog at https://www.vividcortex.com/blog/2014/02/25/performance-schema-slow-query-log-tcp-sniffing/ . As a quick summary: logging has high overhead and operational risk; P_S isn't always available and misses crucial information such as prepared statements (though that will be fixed in MySQL 5.7, at least to some extent) and TCP sniffing is difficult and not always possible, e.g. when SSL is used. There's no silver bullet. Now, what about all the metrics that you normally think of as "performance" metrics such as query cache hit ratio, buffer pool reads, CPU utilization, etc? Are they useless? No, they are not, however they are a) not performance metrics, as Cary Millsap explains; and b) they are not a starting point. The right way to approach these is as evidence to prove or disprove a theory about an observed phenomenon. In other words, you see a performance problem and you hypothesize that it is related to buffer pool reads - so you look at the buffer pool metrics to prove or disprove this. You do NOT, if you want to avoid the tail wagging the dog, stare at buffer pool reads and wonder if there is any meaning in it. I also recorded a webcast on this topic, of a presentation I've given at Velocity and other events, called What Should I Monitor And How Should I Do It. That is available here: This presentation has been widely cited by performance experts in the industry including https://www.datadoghq.com/blog/monitoring-101-collecting-data/ Hope that helps!

I think some of the most important MySQL metrics to take into considerations are : 1. Slow query log: this can tell you about the problematic queries running on your server which is causing the load on your server and making your server run slow. 2. Error log: MySQL Error log contains information on server start and stop time and also critical errors that occur while the server is running. Any entry of the type [error] should be properly looked into by you. 3. CPU usage: If your MySQL server is causing a huge load on your CPU, it is time that you reconfigure you MySQL settiings. 4. Full Table Scans: This value indicates the percentage of rows that were accessed via full table scans. You should try to keep this value as low as possible. Try to isolate queries that are not using indexes. Once you have indentified the queries, create the appropriate indexes or rewrite the queries to use indexes. 5. Deadlocks: A deadlock in MySQL happens when two or more transactions mutually hold and request for locks, creating a cycle of dependencies. A deadlock is almost unavoidable, and is fine if it occurs occasionaly, but it can be a huge problem if it starts happening frequently and that is when it needs your attention. You can use "show engine innodb status" to determine the cause of the most recent deadlock. All the above can be easily found out using any MySQL client available, but you will have to query your server to know the values. If you want to have a nice, neat representation for these, you can use this tool https://www.webyog.com/product/monyog, which I have been using for quite a long time now. It has many good features and can let you know about what all is going on in your MySQL server and what can cause a major issue for your server. Hope this helps.

Actually a lot of things need to be considered for mysql tuning. Mysql deeply affected by improper/absence of indexing. Other then indexing mysql system variables like query_cache_size , innodb buffer pool size , table open cache etc are major system variable s . I have a article regarding this http://www.techinfobest.com/important-mysql-system-variables/ I have other some post optimizing important system variables 1) http://www.techinfobest.com/optimize-mysql-query_cache_size/ 2) http://www.techinfobest.com/optimize-mysql-table_open_cache/ 3) http://www.techinfobest.com/optimize-mysql-innodb_buffer_pool_size/

The most important metrics to pay attention is server CPU, memory usage, free disk space, free swap space, MySql availaibility, error logs etc. I have named the most important and i pay attention to them when I my monitor my MySQL performance with the help of Anturis (http://www.anturis.com) which I consider to be one of the best monitoring tools at present. It finds the problem and offers troubleshooting. In case you are not experienced enough or so is your sys admin it can be of great help.

As there are lots of variables  available in mysql. You can start with watching below variables:- Basically these are mysql status 1.Threads_connected 2.Created_tmp_disk_tables 3.Innodb_buffer_pool_wait_free 4.Key_reads 5.Max_used_Connections 6.Open_tables 7.Select_full_join 8.Slow_queries E.g   show global status like '%Threads_connected%' Further you can do google for their much discription.

You can read this blog: http://blog.webyog.com/2012/09/03/top-10-things-to-monitor-on-your-mysql/