Ten essential tools for MySQL administrators (1)

Daniel Nichter, author of this article, is a developer of MySQL tools. He recommends ten essential tools for MySQL administrators. The full text is as follows:

MySQL is a complex system that requires a large number of auxiliary tools to repair, diagnose and optimize. Fortunately, for administrators, the high popularity of MySQL has attracted a large number of software developers to create high-quality open-source tools for them, the content covers the complexity balancing, performance maintenance, and stable operation guarantee of the MySQL system, and most of them are free tools.

The following ten open-source tools are extremely valuable to MySQL users, covering various situations from individual instances to multi-node environments. This check item is carefully designed to help you back up MySQL Data, improve performance, prevent benchmark deviations, and filter key data from records when problems occur.

Compared with creating an internal tool manually, this tool has the following advantages. First, because of their wide application scope, they must be superior in terms of system maturity and functional practices. Second, because they are both free open-source tools, they can get the knowledge and use experience from the ever-expanding MySQL Community. Furthermore, these developers are rigorous in the R & D process, and many tools also provide professional technical support (both the free version and the commercial version ), therefore, it can be continuously improved to maintain adaptability to the changing new MySQL industry situation.

Remember that there are always many practical tools that we don't pay attention. In my selection of Recommendation tools, I focus more on free and open-source features, while functional and availability standards are the second standard. In addition, all of these tools belong to the Unix Command Line program, because MySQL is more common in Unix system deployment and development. If you do not find a preferred tool in my recommendation, I hope you can leave a message in the comment column below the article to share your experience with you.

We recommend that you start using the ten necessary MySQL tools.

The first required MySQL tool: mk-query-digest

Nothing is worse than low MySQL performance. Although we often subconsciously think that the hardware configuration lags behind, the real crux of the problem is not here in most cases. Poor performance is often caused by the following reasons: slow execution of some queries blocks the smooth execution of other query commands, and thus produces a vicious circle of slow response time. Optimizing query commands saves a lot of cost compared to upgrading hardware. Therefore, the logical optimization method should start with analyzing and querying command log files.

Database Administrators should analyze and query logs frequently to grasp various fluctuations in the runtime environment. If you have never performed this analysis, proceed immediately. If there is no experience in this, it is also a good choice to rely on third-party software help. Although many people think that those software will only give a fictitious and beautiful result after being busy, I have to say, in fact, they are usually effective.

Among the many current options, mk-query-digest is the best log analysis tool. It was jointly written by Baron Schwartz and myself, and has been well-developed in terms of maturity, record adequacy, and thorough testing. MySQL itself contains a query log analyzer named mysqldumpslow. However, this tool is not only outdated, validation specification is not accurate, but also lacks extensive practical support. Several other well-known log analyzer, including mysqlsla I wrote in the past few years, all have the same disadvantages as mysqldumpslow.

Mk-query-digest can analyze and query log Content and automatically generate reports based on the summary execution time and statistical information of other indicators. Because the amount of information in the query log is extremely large and sometimes even contains millions of entries, such analysis must be completed by specific tools.

Mk-query-digest helps you find the entries that consume the most time than other query commands. The optimization of these low-speed queries will greatly improve the running speed of the entire MySQL System, and the maximum response latency will also decrease accordingly. The optimization of query commands is an art in itself, including a lot of meticulous skills, but the basic principles of the entire process are always common: obtaining low-speed query commands, optimizing, and improving query response time.

The tool is easy to use and executes the mk-query-digest slow-query.log, where query commands that run slowly are output to the slow-query.log file. The tool also provides the query instruction review function to list query instructions that we have not yet checked or approved. In this way, we can only perform targeted processing on the new query commands, and the tedious and boring log analysis work becomes faster and more efficient.

: Http://maatkit.org/get/mk-query-digest

Maintenance Director: Daniel Nichter and Baron Schwartz

More information: http://maatkit.org/| http://code.google.com/p/maatkit/