Author: wang talk about operational www.toutiao.com/a6691523026…

What about performance for running mysql? Are the parameters set properly? Is there any security risk in the account setting?

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

Today I’m going to share with you a few mysql tuning tools that you can use to perform a physical examination of your mysql database and generate AWR reports that give you an overall picture of how your database is performing.

1, mysqltuner – pl

Mysql is a common database performance diagnostic tool. It mainly checks the rationality of parameter Settings, including log files, storage engines, security suggestions, and performance analysis. For potential problems, give suggestions for improvement, is a good helper of mysql optimization.

In the previous release, MySQLTuner supported about 300 metrics for MySQL/MariaDB/Percona Server.

Project address: github.com/major/MySQL…

1.1 download

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

1.2 the use of

[root @ localhost ~] #. / mysqltuner pl - socket/var/lib/mysql/mysql. The sock > > mysqltuner 1.7.4 - Major Hayden <[email protected]> >> 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 architectureCopy the code

1.3 report analysis

1) Important attention [!!] (items with an exclamation mark in brackets) for example [!!] Maximum possible memory usage: 4.8GB (244.13% of installed RAM) indicates that the memory has been severely overused.

2) Focus on the last Recommendations.

2, the quick fix – primer. Sh

This is another optimization tool for mysql, needle in mysql as a whole to carry out a checkup, for potential problems, give optimization suggestions.

Project address: github.com/BMDan/tunin…

Currently, detection and optimization recommendations are as follows:

2.1 download

/ root @ localhost ~ # wget HTTP: / / https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.shCopy the code

2.2 the use of

[root@localhost ~]#  [root@localhost dba]#  ./tuning-primer.sh

-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -Copy the code

2.3 Report Analysis

Check the options with red alarms and modify them based on your system requirements. For example:

3, pt, variable, and advisor

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

3.1 installation

www.percona.com/downloads/p…

[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. RPMCopy the code

3.2 the use of

Pt-variable-advisor is a subtool of the PT toolset that is used to diagnose your parameter Settings.

[root@localhost ~]# pt-variable-advisor localhost --socket /var/lib/mysql/mysql.sockCopy the code

3.3 Report Analysis

Focus on entries with WARN information, such as:

4, pt – qurey – digest

Pt-query-digest analyzes MySQL queries from logs, process lists, and tcpdump.

4.1 installation

Refer to section 3.1 for details

4.2 the use of

Pt-query-digest is mainly used to analyze slow mysql logs. Compared with mysqlDumpshow, py-query_digest provides more detailed and complete analysis results.

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

4.3 Analysis of common usage

1) Directly analyze slow query files:

pt-query-digest /var/lib/mysql/slowtest-slow.log > slow_report.logCopy the code

2) Analyze queries within the last 12 hours:

pt-query-digest --since=12h /var/lib/mysql/slowtest-slow.log > slow_report2.logCopy the code

3) Analyze queries within a specified time range:

pt-query-digest /var/lib/mysql/slowtest-slow.log --since '2017-01-07 09:30:00' --until '2017-01-07 10:00:00'> > slow_report3.logCopy the code

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.logCopy the code

5) Slow queries for a user

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

6) Query all slow queries on full table scan or full join

pt-query-digest --filter '(($event->{Full\_scan} || "") eq "yes") ||(($event->{Full\_join} || "") eq "yes")' /var/lib/mysql/slowtest-slow.log> slow_report6.logCopy the code

4.4 Report Analysis

Part ONE: Overall statistical results

Overall number of queries Time range in which the query was executed unique number of queries parameterized by the query criteria total min Max avg average 95% So if you order all the values from the smallest to the largest, the number in the 95% position, which generally has the most reference value, median, you can order all the values from the smallest to the largest, the number in the middle

The second part: Query group statistics results

–order-by specifies the Query ID: specifies the ID of the statement (excluding extra Spaces and text characters, and calculating the hash value) Response: Total Response time Time: percentage of the total time of the Query in this analysis calls: Execution times, that is, the total number of query statements of this type in this analysis. R/Call: average response time of each execution V/M: ratio of response time Variance to-mean Item: query object

Part III: Detailed statistical results for each query

ID: indicates the Query ID. Databases: indicates the database name. Users: indicates the execution times of each user. Query_time Distribution: indicates the Query time distribution. Tables: Explain: SQL statements of the Tables involved in the query.

Read more on my blog:

1.Java JVM, Collections, Multithreading, new features series tutorials

2.Spring MVC, Spring Boot, Spring Cloud series tutorials

3.Maven, Git, Eclipse, Intellij IDEA series tools tutorial

4.Java, backend, architecture, Alibaba and other big factory latest interview questions

Life is good. See you tomorrow