Original: https://www.toutiao.com/a6691523026984370699/

preface

For running mysql, how is the performance, parameter Settings are reasonable, account Settings exist security risks, do you know?

As the saying goes, if you want to do a good job, you must first sharpen its tools, regular check your MYSQL database, is an important means to ensure the safe operation of the database, because, a good tool is to make your work efficiency double!

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.





mysqltuner.pl

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

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

The address of the project: https://github.com/major/MySQLTuner-perl

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

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





tuning-primer.sh

Another optimization tool for mysql, needle in mysql as a whole to carry out a physical examination, for potential problems, to give recommendations for optimization.

The address of the project: https://github.com/BMDan/tuning-primer.sh

Currently, detection and optimization recommendations are as follows:





2.1 download

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

Copy 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:





pt-variable-advisor

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

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

Copy 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.sock

Copy the code

3.3 Report Analysis

Focus on entries with WARN information, such as:





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

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

Copy the code
2) Analyze queries within the last 12 hours: 

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

Copy the code
3) Analyze queries within a specified time range: 

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

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

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

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

Copy the code

4.4 Report Analysis

  • Total: min: Max: avg: Max: avg: Max: avg: Max: avg: Max: avg: Max: avg: Max: avg: Max: avg: Max: avg: Max: avg: Max: avg: Max: avg: Max: avg: Average 95% : Order all values from smallest to largest, the number in the 95% position, which generally has the most reference value. Median: Order all values from smallest to largest, and the number in the middle position
  • Rank: indicates the ranking of all statements in descending order by Query time by default. Run the –order-by command to specify the Query ID: indicates the ID of the statement (remove extra Spaces and text characters, and calculate the hash value) Response: Indicates the total Response time time: R/Call: average response time of each execution V/M: ratio of response time Variance to-mean Item: query object
  • ID: Query ID, which corresponds to the Query ID in the figure above Databases: database name Users: number of execution times of each user (proportion) Query_time distribution: Query time distribution, length reflects the proportion of the interval. Tables: Explain: SQL statements of the Tables involved in the query

The last

Welcome to pay attention to the public number: programmer chasing wind, reply 66, get a 300 page PDF document Java core knowledge summary!

These are some of the things that the interviewer should ask during the interview. These include basics, Java collections, JVMS, multi-threaded concurrency, Spring principles, microservices, Netty and RPC, Kafka, diaries, design patterns, Java algorithms, databases, Zookeeper, distributed caching, data structures, and more.