Shulou(Shulou.com)05/31 Report--

This article shows you what the MySQL optimization tool AWR has, which is concise and easy to understand, which can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Do you know the performance of the running mysql, whether the parameter setting is reasonable, and whether there are security risks in the account setting?

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, because a good tool is to double your work efficiency!

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

Mysqltuner.pl 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, give suggestions for improvement. Is a good helper of mysql optimization.

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

Project address: https://github.com/major/MySQLTuner-perl

(1) download

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

(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 script Please enter your MySQL administrative login: root Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.23 [OK] Operating on 64-bit architecture

(3) report analysis

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.

Follow the suggestion "Recommendations" given by *.

2. Tuning-primer.sh

Another optimization tool of mysql is to conduct a physical examination of mysql as a whole and give optimization suggestions for potential problems.

Project address: https://github.com/BMDan/tuning-primer.sh

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

(1) download

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

(2) use

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

(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.

(1) installation

Https://www.percona.com/downloads/percona-toolkit/LATEST/

[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

(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) 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.

(1) installation

Refer to Section 3.1 for details.

(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

(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) 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 the query conditions have been parameterized, how many different query total: total min: minimum max:*** avg: average 95%: the number that arranges all values from small to large, located at 95%, this number is generally * valuable median: median Arrange all the values from small to large, with the position 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: percentage of total time 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

The third part: the detailed statistical results of each query ID: the ID number of the query, and the Query ID of the above figure corresponds to Databases: database name Users: the number of times executed by each user (percentage) Query_time distribution: query time distribution, length reflects the interval proportion. Tables: table Explain:SQL statements involved in the query

The above is what the MySQL optimization tool AWR has, and have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.