4 useful MySQL tuning tools recommended

This article recommends 4 mysql optimization tools. You can use them to conduct a physical examination of your mysql and generate an awr report, allowing you to grasp the overall performance of your database.

Video recommendation: MySQL video tutorial

What is the performance of running mysql? Are the parameters set appropriately? Is it clear whether there are security risks in the account settings?

As the saying goes, if you want to do your job well, 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 would like to share with you several mysql optimization tools. You can use them to conduct a physical examination of your mysql and generate an awr report, allowing you to grasp the overall performance of your database.

1. mysqltuner-pl

This is a commonly used database performance diagnostic tool for mysql. It mainly checks the rationality of parameter settings. Includes log files, storage engines, security recommendations and performance analysis. It is a good helper for mysql optimization to provide suggestions for improvement based on potential problems.

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

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

1.1 Download

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

1.2 Use

[root@localhost ~]# ./mysqltuner.pl --socket /var/lib/mysql/mysql.sock >> MySQLTuner 1.7.4 - Major Hayden <major> >> 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 architecture</major>

1.3. Report analysis

1) Important attention [!!] (items with exclamation marks in square brackets) such as [!!] Maximum possible memory usage : 4.8G (244.13% of installed RAM), indicating that the memory has been seriously exceeded.

#2) Pay attention to the last suggestions “Recommendations”.

2. tuning-primer.sh

This is another optimization tool for mysql, which is used to perform an optimization on mysql as a whole. Physical examination and optimization suggestions for potential problems.

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

Currently, the content that supports detection and optimization suggestions is as follows:

2.1 Download

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

2.2 Use

[root@localhost ~]#  [root@localhost dba]#  ./tuning-primer.sh
    
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

2.3 Report analysis

Focus on the options with red alerts and modify them according to the suggestions and the actual situation of your own system, for example:

3, pt-variable-advisor

pt-variable-advisor can analyze MySQL variables and make recommendations on possible problems.

3.1 Installation

https://www.percona.com/downl...

[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

3.2 Use

pt-variable-advisor is a sub-tool of the pt tool set, mainly used to diagnose whether your parameter settings are reasonable.

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

3.3 Report analysis

Focus on entries with WARN information, for example:

4. pt-qurey-digest

pt-query-digest’s main function is to analyze MySQL queries from logs, process lists and tcpdump.

4.1Installation

For details, please refer to Section 3.1

4.2Using

pt-query-digest Main Used to analyze mysql's slow logs. Compared with the mysqldumpshow tool, the analysis results of the py-query_digest tool are more specific and complete.

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

4.3 Common usage analysis

1) Directly analyze slow query files:

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

2) Analyze queries within the last 12 hours:

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

3) Analyze queries within the 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) Analyze slow queries that contain select statements

pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /var/lib/mysql/slowtest-slow.log> slow_report4.log

5) Slow queries for a certain user

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

6) Query all slow queries of 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

4.4 Report analysis

Part 1: Overall statistical results

Overall: How many queries are there in total? Time range: The time range of query execution unique: The number of unique queries, that is, after parameterizing the query conditions, how many different queries are there in total? Total: Total min: Minimum max: Maximum avg: average 95%: arrange all values ​​from small to large, the number located in the 95th percentile, this number generally has the most reference value median: median, arrange all values ​​from small to large, the number located in the middle

Part 2: Query group statistical results

Rank: Ranking of all statements, sorted by query time in descending order by default, specify Query ID through --order-by: ID of the statement, (remove excess spaces and text characters, calculate hash value) Response: total response time time : The total time proportion of this query in this analysis calls: The number of executions, that is, the total number of query statements of this type in this analysis R/Call: The average response time of each execution V/M: Response time Variance -to-mean ratio Item: Query object

Part 3: Detailed statistical results of each query

ID: Query ID number, corresponding to the Query ID in the above figure Databases: Database name Users: the number of executions by each user (proportion) Query_time distribution: query time distribution, the length reflects the interval proportion. Tables: Tables involved in the query Explain: SQL statement.

Life is wonderful, see you tomorrow~

For more programming-related knowledge, please visit: Introduction to Programming! !

The above is the detailed content of 4 useful MySQL tuning tools recommended. For more information, please follow other related articles on the PHP Chinese website!