Slide

  • LINUX

    LINUX:In 1969 AT&T made a decision to withdraw Multics and go with GECOS (General Electric Comprehensive Operating Supervisor / System), with AT & T in Bells Lab when Multics was withdrawn some of the programmers named Ken Thompson and Dennis Ritchie decided to rewrite operating system in order to support low cost computer..To Know More

    CLICK HERE

  • MICROSOFT

    MICROSOFT:Microsoft was established to develop and sell BASIC interpreters for the Altair 8800. It rose to dominate the home computer operating system market with MS-DOS in the mid-1980s,followed by the Microsoft Windows line of operating systems. To Know More

    CLICK HERE

  • CISCO

    CISCO:During the early 1980s, there was a married couple namely Len and Sandy Bosack who used to work in two different departments of computer located in Stanford University. This couple was facing problem in making their computers communicate with each other To Know More

    CLICK HERE

showinfo=1

Monitoring MySQL

Monitoring MySQL




Monitoring MySQL servers is no rocket science provided you know what to monitor. MySQL gives a comprehensive list of variables to check your server’s health and performance. Let me walk you through the crucial variables you should be monitoring. Lets assume that you have one or more MySQL servers, which have been setup and running fine. Here are the top 10 things to monitor on your MySQL.

NOTE:-  Most of the commands run on mysql prompt ( mysql>) to go to mysql prompt check the 1 post intalling MYSQL some in bash shell (#)....

1. MySQL availability

Yes, this is the first thing you should be looking at! It would not make sense to monitor your MySQL if its not even available. MySQL downtime is simply not acceptable in production. At the same time ensuring zero downtime does not guarantee maximum performance.
You can execute 




# mysqladmin -h 192.168.1.95 -u root -p status

to know if MySQL is running or you can just ping MySQL using service mysqld status if you are using RedHat Linux.

2. Presence of insecure users and databases

Do any of your MySQL users have ‘%’ as host? Meaning that the user can connect to MySQL from anywhere around the world? If yes, your servers are prone to attacks. For maximum security, it would be better to give literal host values instead of ‘%’. If for instance, your host is localhost then the hacker will have to gain access to localhost first before touching your servers.Do you have MySQL users with excessive privileges? Why this question you might wonder, as MySQL by default comes with a root user who has super privileges. Unfortunately the word root is too well known but fortunately it does not have any significance in MySQL at all. You can simply give any other name instead of root.

mysql> CREATE USER 'obsure_name'@'%’ IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'obsure_name'@'%' WITH GRANT OPTION;
DROP USER 'root'@‘%’;
FLUSH PRIVILEGES;

In addition, ensure that all your MySQL users have passwords.
The default MySQL installation comes with a database named ‘test’ that anyone can access. This database is intended only for testing or tutorial purpose. The ‘test’ can be accessed by users who do not have explicit privileges granted to that database. Therefore databases named ‘test’ should be avoided on production servers.

3. Aborted connects

aborted_connects gives the total number of failed attempts to connect to MySQL. Excess aborted_connects indicate that the client does not have enough privileges, or the client uses an incorrect password, or someone is trying to hack into your server.
aborted_connects is a global status value and can be retrieved using-

mysql> SHOW GLOBAL STATUS LIKE 'aborted_connects';

4. Error log

MySQL Error log not only contains information on server start and stop time but also critical errors that occur while the server is running. On some operating systems, the error log also contains stack trace of MySQL before it crashed. Any entry of type [error] obviously needs your attention.

5. Innodb Deadlocks

Innodb rolls back transactions if there is a deadlock. Knowing its occurrence is important to trace back the root cause of deadlock. You should know what deadlocks occured and verify that applications have handled them properly or take proper action.
To detect deadlocks, use


mysql> SHOW ENGINE INNODB STATUS;

6. Change in server configuration

Ever wondered why your server was performing perfectly a few weeks ago but isn’t now? Then you should be looking at the MySQL configuration changes that were made in the recent past. MySQL configuration plays a critical role in your server’s performance.

7. Slow Query log

Consists of all the queries that exceed long_query_time seconds to execute. More slow queries would mean more disk reads, more memory usage, more CPU usage which ultimately just slows down your servers, causes bottlenecks and hence results in poor performance. Slow Query log is where you find queries that are potential targets for optimization.

8. Slave lag

Most production servers have one or more replication slaves. Monitoring slaves are equally important. If slave_sql_running, slave_io_running is NO, then your slave has stopped replicating and should be fixed first. Higher seconds_behind_master is, the slower is your slave. By executing -

mysql> SHOW SLAVE STATUS;

you can track your replicas.

9. Percentage of maximum allowed connections

A high value of percentage of maximum allowed connections(max_used_connections / max_connections) tells that you could soon run out of client connections. In other words, new connection requests will simply be refused. So make sure your max_connections is large enough to suite your application. By executing-

mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections';
mysql> SHOW GLOBAL STATUS LIKE 'max_used_connections';

you can track maximum allowed connections.

10. Percentage of full table scans

Percentage of full table scans is best represented using 

((Handler_read_rnd_next + Handler_read_rnd) / (Handler_read_rnd_next + Handler_read_rnd + Handler_read_first + Handler_read_next + Handler_read_key + Handler_read_prev)).

An increase in full table scans (or in creation of temporary tables or similar) may turn satisfactory performance into non-satisfactory. You should understand the queries causing this and consider changes to schemas or queries if needed. You can retrieve these metric by executing-

mysql> SHOW GLOBAL STATUS LIKE "Handler_read%";

There are many tools that do all the above and much more. If you are looking for a free command line tool, you might want to try Percona toolkit. But if you are looking for a GUI based tool, you might be looking for MONyog – MySQL Monitor and advisor.

17 comments:

  1. Well, very good post with informative information. I really appreciate the fact that you approach these topics from a stand point of knowledge and information. This is the first time, I visited at your site and became your fan. You are bookmarked. Please keep on posting.
    elopelab.org |

    ReplyDelete
  2. This is an excellent post I seen thanks to share it. It is really what I wanted to see hope in future you will continue for sharing such a excellent post.
    www.pushautogram.com |

    ReplyDelete
  3. I was suggested this blog by my cousin. I am not sure whether this post is written by him as no one else know such detailed about my difficulty. You are wonderful! Thanks!
    http://www.rsnewhomes.com |

    ReplyDelete
  4. I agree with you. This post is truly inspiring. I like your post and everything you share with us is current and very informative, I want to bookmark the page so I can return here from you that you have done a fantastic job.

    ReplyDelete
  5. Excellent and decent post. I found this much informative, as to what I was exactly searching for.The Article is quite impressive and thoughts been put up has clearly got something to state.
    sibelleeducation.com |

    ReplyDelete
  6. I am truly inspired by this online journal! Extremely clear clarification of issues is given and it is open to every living soul. I have perused your post, truly you have given this extraordinary informative data about it.
    www.georgiagrouptours.com |

    ReplyDelete
  7. You actually make it seem so easy with your presentation but I find this topic to be really something which I think I would never understand. It seems too complicated and very broad for me. I am looking forward for your next post, I will try to get the hang of it!
    http://www.plasticmedicalparts.com |

    ReplyDelete
  8. You have done a great job. I will definitely dig it and personally recommend to my friends. I am confident they will be benefited from this site. 
    jabuconsulting |

    ReplyDelete
  9. I have no words to appreciate this post ..... I'm really impressed with this post .... the person who created this post was a big thank you man .. for sharing with us.
    www.blog-sardinefactory.com |

    ReplyDelete
  10. Great information on your site here. I love this post because we can get some useful information from your blog. I expect more post from you guys.
    www.mccfitnessstems.com |

    ReplyDelete
  11. Hi there, You have done an incredible job. I’ll certainly digg it and personally suggest to my friends. I am sure they’ll be benefited from this website.
    blinktechmobile.com |

    ReplyDelete
  12. This site has a great deal of useful and precious advice and I can’t think about anyone writing an even better article.
    qbelfood.com |

    ReplyDelete
  13. Wow, I have to say. Wow, how come nobody came across your post before. I have always been wondering what else can REST service do and you have just answered that.
    www.blackamericafitness.com |

    ReplyDelete
  14. You’ve got some interesting points in this article. I would have never considered any of these if I didn’t come across this. Thanks!.
    Seattle Best Cars |

    ReplyDelete

Note:- Comment as: Option available to post without login select "Anonymous" from the drop down...........

For Latest Updates: Subscribe Now | | Test Your Knowledge, Take a Quiz now Click Here | | Site Best Viewed In Firefox

X