With these four tools, the boss is no longer afraid that I write bad SQL

Time:2021-2-6

How about the performance of running MySQL? Is the parameter setting reasonable? Is there any security risk in account setting? 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 several MySQL optimization tools. You can use them to conduct a physical examination on your MySQL and generate an AWR report, so that you can grasp the performance of your database as a whole.
With these four tools, the boss is no longer afraid that I 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 engine, security suggestions and performance analysis. In view of the potential problems, this paper gives suggestions for improvement, which is a good helper of MySQL optimization.

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

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

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
>> MySQLTuner1.7.4- MajorHayden<[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Runwith'--help'for additional options and output filtering
[--] Skipped version check forMySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.
[OK] Operating on 64-bit architecture 

1.3 report analysis

1) Important attention [!] (exclamation mark in brackets) for example, [!] maximum possible memory usage: 4.8g (244.13% of installed RAM), which indicates that the memory has been seriously used.
With these four tools, the boss is no longer afraid that I write bad SQL
2) Pay attention to the last suggestion “recommendations”.
With these four tools, the boss is no longer afraid that I write bad SQL
2、tuning-primer.sh

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

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

At present, the contents supporting the detection and optimization suggestions are as follows:
With these four tools, the boss is no longer afraid that I 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: MatthewMontgomery- 

2.3 report analysis

Focus on the options with red alarm, and modify them according to the actual situation of your own system, such as:
With these four tools, the boss is no longer afraid that I 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/downl…

[[email protected] ~]#wget https://www.percona.com/downloads/perconatoolkit/3.0.13/binary/redhat/7/x86_64/pecona-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 toolkit, which is mainly used to diagnose whether your parameter setting is 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:
With these four tools, the boss is no longer afraid that I write bad SQL
4、pt-qurey-digest

The main function of Pt query digest is to analyze MySQL query from log, process list and tcpdump.

4.1 installation

Refer to Section 3.1 for details

4.2 use

PT query digest is mainly used to analyze slow logs of MySQL. Compared with MySQL dumpshow, py query is more efficient_ 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) Analyze slow query files directly:

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

2) Analysis of 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 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 one: overall statistical results: Total: how many queries are there in total time range: the time range of query execution unique: the number of unique queries, that is, the total number of different queries after parameterization of query criteria total: total Min: minimum Max: maximum AVG: Average 95%: arrange all the values from small to large, the number in the position of 95%, which is generally the most valuable reference
  • The second part: query grouping statistics result rank: the ranking of all statements, which is sorted in descending order by default according to the query time. Query ID: the ID of the statement is specified by – order by, (remove the redundant spaces and text characters, and calculate the hash value) response: total response time time: the proportion of the total time of the query in this analysis Call: number of times of execution, that is, how many queries 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 three: detailed statistical results of each query ID: the ID number of the query, corresponding to the query ID in the figure above_ Time distribution: query time distribution, length reflects interval proportion. Tables: tables involved in the query explain: SQL statements

Author | amateurs
Source|https://urlify.cn/fQBNnq
With these four tools, the boss is no longer afraid that I write bad SQL
Welcome to my WeChat official account, “code breakout”, sharing technologies like Python, Java, big data, machine learning, AI, etc., focusing on code farm technology upgrading, workplace breakout and mental transition, and 200 thousand + code growth, the first stop to grow with you.