Shulou(Shulou.com)06/01 Report--

This article will explain in detail what the MySQL optimization tools are, and the editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

What is the performance for a running mysql? Is the parameter set reasonable? Are you aware of the security risks set up by your account?

As the saying goes, if you want to do a good job, you must first sharpen its tools. A regular physical examination of your MYSQL database is an important means to ensure the safe operation of the database.

Today I would like to share with you several mysql optimization tools that you can use to conduct a physical examination of your mysql and generate an awr report that allows you to grasp the performance of your database as a whole.

1 、 mysqltuner-pl

This is a commonly used database performance diagnosis tool for mysql, which mainly checks the rationality of parameter settings, including log files, storage engine, security recommendations and performance analysis. In view of the potential problems, some suggestions for improvement are given, which is a good helper for mysql optimization.

In the previous version, MySQLTuner supported about 300 metrics of MySQL / MariaDB / Percona Server.

Project address: https://github.com/major/MySQ...

1.1 download

[root@localhost ~] # wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

1.2 use

[root@localhost] #. / mysqltuner.pl-- socket / var/lib/mysql/mysql.sock > > MySQLTuner 1.7.4-Major Hayden > > Bug reports, feature requests, and downloads at http://mysqltuner.com/ > > Run with'--help' for additional options and output filtering\ [- -\] Skipped version check for MySQLTuner scriptPlease enter your MySQL administrative login: rootPlease enter your MySQL administrative password:\ [OK\] Currently running supported MySQL version 5.7.23\ [OK\] Operating on 64-bit architecture

1.3. Report analysis

1) important attention [!] (items with exclamation marks in square brackets) such as [!] Maximum possible memory usage: 4.8G (244.13% of installed RAM), indicating that memory has been seriously overrun.

2) follow the final suggestion "Recommendations".

2 、 tuning-primer.sh

This is another optimization tool of mysql, which aims at a physical examination of mysql as a whole and gives optimization suggestions for potential problems.

Project address: https://github.com/BMDan/tuni...

At present, the recommendations to support detection and optimization are as follows:

2.1 download

[root@localhost ~] # wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh

2.2 use

[root@localhost] # [root@localhost dba] #. / tuning-primer.sh-- MYSQL PERFORMANCE TUNING PRIMER-By: Matthew Montgomery-

2.3 report Analysis

Focus on the options with red alarm, and modify them according to the recommendations combined with the actual situation of your system, such as:

3 、 pt-variable-advisor

Pt-variable-advisor can analyze MySQL variables and make suggestions for possible problems.

3.1 installation

Https://www.percona.com/downl...

[root@localhost ~] # wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86\_64/percona-toolkit-3.0.13-re85ce15-el7-x86\_64-bundle.tar\[root@localhost ~\] # yum install percona-toolkit-3.0.13-1.el7.x86_64.rpm

3.2 use

Pt-variable-advisor is a sub-tool of the pt toolset, which is mainly used to diagnose whether your parameter settings are reasonable.

[root@localhost] # pt-variable-advisor localhost-- socket / var/lib/mysql/mysql.sock

3.3 report Analysis

Focus on entries with information about WARN, such as:

4 、 pt-qurey-digest

The main function of pt-query-digest is to analyze MySQL queries from logs, process lists, and tcpdump.

4.1 installation

Refer to Section 3.1 for details.

4.2 use

Pt-query-digest is mainly used to analyze the slow log of mysql. Compared with mysqldumpshow tool, the analysis result of py-query_digest tool is more specific and perfect.

[root@localhost ~] # pt-query-digest / var/lib/mysql/slowtest-slow.log

4.3 Analysis of common usage

1) analyze slow query files directly:

Pt-query-digest / var/lib/mysql/slowtest-slow.log > slow_report.log

2) analyze the queries in the last 12 hours:

Pt-query-digest-- since=12h / var/lib/mysql/slowtest-slow.log > slow_report2.log

3) analyze the queries within the specified time range:

Pt-query-digest / var/lib/mysql/slowtest-slow.log-- since '2017-01-07 09 since'-- until '2017-01-07 10 slow_report3.log

4) Analysis refers to slow queries with select statements

Pt-query-digest-- filter'$event- > {fingerprint} = ~ m / ^ select / i' / var/lib/mysql/slowtest-slow.log > slow_report4.log

5) slow query for a user

Pt-query-digest-- filter'($event- > {user} | | ") = ~ m / ^ root / i' / var/lib/mysql/slowtest-slow.log > slow_report5.log

6) query all full table scans or full join slow queries

Pt-query-digest-- filter'(($event- > {Full\ _ scan} | | ") eq" yes ") | | ($event- > {Full\ _ join} | |") eq" yes ")'/ var/lib/mysql/slowtest-slow.log > slow_report6.log

4.4 report Analysis

Part I: overall statistical results

Overall: total number of queries Time range: time range of query execution unique: unique number of queries, that is, after parameterization of the query conditions, how many different query total: total min: minimum max: maximum avg: average 95%: the number that arranges all values from small to large, located at 95%, this number is generally the most valuable reference median: median, arranging all values from small to large The number in the middle

Part II: query grouping statistical results

Rank: ranking of all statements, sorted by default in descending order of query time. Specify ID of Query ID: statement through-- order-by, (remove extra spaces and text characters, calculate hash value) Response: total response time time: total time percentage of this query in this analysis calls: number of execution That is, the total number of query statements of this type in this analysis: average response time per execution R/Call M: response time Variance-to-mean ratio Item: query object

Part III: detailed statistical results of each query

ID: the ID number of the query, which corresponds to the Query ID in the figure above. Databases: database name Users: number of times executed by each user (percentage) Query_time distribution: query time distribution, length reflects interval percentage. Tables: the table Explain:SQL statement involved in the query.

What about the MySQL optimization tools to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.