What is your performance on running mysql? Are the parameters set properly? Is there any security risk in the account setting? Do you know?

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.pl
Copy the code

1.2 the use of

[root @ localhost ~] #. / mysqltuner pl - socket/var/lib/mysql/mysql. The 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 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 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: MatthewMontgomery-
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/perconatoolkit/3.0.13/binary/redhat/7/x86_64/pecona-toolkit-3.0.13-re85ce15-el7-x86_64 -bundle.tar [root@localhost ~]#yum install percona-toolkit 3.0.13-1.el7.x86_64Copy 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:



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.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 '2017-01-07 09:30:00'--until'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
  • Part 3: Detailed statistical results of each Query ID: Query ID, which corresponds to the Query ID in the preceding figure Databases: database name Users: execution times of each user (proportion) Query_time Distribution: Query time distribution. Tables: Explain: SQL statements of the Tables involved in the query

The author and personnel source | | love yards urlify. Cn/fQBNnq