These four tools help you write bad SQL

Time:2021-9-27

What is your performance on running MySQL? Is the parameter setting reasonable? Is there a security risk in the account settings? Is it clear?

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

Today, I’d like to share with you some MySQL optimization tools. You can use them to conduct a physical examination of your MySQL and generate an AWR report, so that you can grasp the performance of your database as a whole.

These four tools help you write bad SQL

1、mysqltuner.pl

This is a commonly used database performance diagnostic tool for MySQL. It mainly checks the rationality of parameter settings, including log files, storage engines, security suggestions and performance analysis. It is a good helper for MySQL optimization to give improvement suggestions for potential problems.

In the previous version, MySQL tuner supported about 300 indicators of MySQL / MariaDB / percona server.

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

1.1 download

[[email protected] ~]#wget https:
//raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

1.2 use

[[email protected] ~]# ./mysqltuner.pl --socket /
var
/lib/mysql/mysql.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
 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

1.3 report analysis

1) Important attention [!!!] (items with exclamation mark in brackets) for example [!!!] maximum possible memory usage: 4.8g (244.13% of installed RAM), indicating that the memory has been seriously used.

These four tools help you write bad SQL

2) Pay attention to the recommendations given at the end.

These four tools help you write bad SQL

2、tuning-primer.sh

This is another optimization tool for MySQL. It conducts a physical examination for the whole of MySQL and gives optimization suggestions for potential problems.

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

At present, the contents supporting detection and optimization suggestions are as follows:

These four tools help you write bad SQL

2.1 download

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

2.2 use

[[email protected] ~]# [[email protected] 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 suggestions in combination with the actual situation of your own system, for example:

These four tools help you write bad SQL

3、pt-variable-advisor

PT variable advisor can analyze MySQL variables and make suggestions on possible problems.

3.1 installation

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

[[email protected] ~]#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

[[email protected] ~]#yum install percona-toolkit-
3.0
.
13
-
1.el7.x86
_64.rpm

3.2 use

PT variable advisor is a sub tool of Pt toolset, which is mainly used to diagnose whether your parameter settings are reasonable.
[[email protected] ~]# pt-variable-advisor localhost --socket /
var
/lib/mysql/mysql.sock

3.3 report analysis

Focus on items with warn information, such as:

These four tools help you write bad SQL

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 logs of MySQL. Compared with MySQL dumpshow, py query_ The analysis results of digest tool are more specific and perfect.

[[email protected] ~]# pt-query-digest /
var
/lib/mysql/slowtest-slow.log

4.3 common usage analysis

1) Direct analysis of slow query files:

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

2) Analyze queries in the last 12 hours:

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

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

4) Analysis refers to a slow query with a select statement

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 slow queries of full join

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 statistics results: Total: how many queries are there in total time range: query execution time range unique: number of unique queries, that is, how many different queries are there after parameterization of query criteria total: total Min: minimum Max: maximum AVG: average 95%: arrange all values from small to large, and the number at 95%, This number generally has the most reference value. Median: rank all values from small to large, and the number in the middle
  • Part II: query grouping statistical results rank: the ranking of all statements. By default, it is arranged in descending order according to the query time. Query ID is specified through — order by: the ID of the statement, (remove redundant spaces and text characters, and calculate the hash value) response: total response time time time time: the proportion of the total time of the query in this analysis. Calls: execution times, That is, how many query statements of this type are there in this analysis R / call: average response time per execution V / M: ratio of response time variance to mean item: query object
  • Part III: detailed statistical result ID of each query: the ID number of the query, which corresponds to the query ID in the figure above. Databases: database name users: the number (proportion) of queries executed by each user_ Time distribution: query the time distribution, and the length reflects the proportion of intervals. Tables: table explain: SQL statement involved in query

What about? You can try, if helpful, support!

Write against the frontIt is a technical platform that focuses on the programmer circle, and you can gainLatest technology trendsLatest internal test qualificationExperience of bat and other big manufacturersGrowth itselfLearning materialsCareer routeMoney making Thinking, wechat searchWrite against the frontAttention!