1. Open Slow Query

- View

show variables like 'slow_query%';
show variables like 'long_query_time';
show variables like 'log_queries_not_using_indexes';

Temporarily open slow log

- Open+Set uses root user, after closing the connection, re-query to see the modified value, only valid for the current service, configuration will be restored back to 10 after mysql restart, permanently valid configuration file needs to be modified;

set global slow_query_log='ON';
set global slow_query_log_file='/usr/local/mysql/data/slow.log';
set global log_queries_not_using_indexes='ON';
set global long_query_time=1;

Long Open Slow Log

Modify my.cnf to add the configuration below [mysqld]

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Remember to restart sql service after modifying configuration file
service mysqld restart
perhaps
systemctl restart mysql

Notes

slow_query_log Slow Query Open State
 slow_query_log_file Location where the slow query logs are stored (This directory requires writable permissions for the MySQL running account, which is typically set to the MySQL data storage directory)
How many seconds does a long_query_time query take to record
 log_queries_not_using_indexes If you run a SQL statement that does not use an index, the mysql database will also log this SQL statement to the slow query log file

Analyzing pt-query-digest output

 1. Part I: Overall statistical results

= User time, system time, physical memory usage, virtual memory usage for log analysis performed by the tool
= 910ms user time, 110ms system time, 25.95 M rss, 220.34M vsz
= Tool Execution Time
= Current date: Tue Jul 31 10:37:16 2018
= Host name for running analysis tools
= Hostname: localhost.localdomain
= File name being analyzed
= Files: mysql_slow_query.log
= Total number of statements, unique number of statements, QPS，Concurrency Number
= Overall: 6 total, 1 unique, 0.01 QPS, 0.01x concurrency ________________
= Time range of logging
= Time range: 2018-07-31 10:17:33 to 10:36:36
= attribute                Total    Minimum    Maximum    Average     95%    standard    secondary
= Attribute          total     min     max     avg     95%  stddev  median
= ============     ======= ======= ======= ======= ======= ======= =======
= Statement Execution Time
= Exec time            16s      1s      5s      3s      5s      2s      4s
= Lock Occupancy Time
= Lock time              0       0       0       0       0       0       0
= Number of rows sent to client
= Rows sent              6       1       1       1       1       0       1
= select Number of statement scan lines
= Rows examine           0       0       0       0       0       0       0
= Number of characters to query
= Query size            90      15      15      15      15       0      15

 2. Part Two: Query Grouping Statistics

= Profile
= Sentence ranking   ID   response time       Average execution response time response ratio per execution
= Rank Query ID                           Response time  Calls R/Call V/M 
= ==== ================================== ============== ===== ====== ====
=    1 0x59A74D08D407B5EDF9A57DD5A41825CA 16.0113 100.0%     6 2.6685  1.19 SELECT

 3. Detailed statistics for each query

= Query 1: 0.01 QPS, 0.01x concurrency, ID 0x59A74D08D407B5EDF9A57DD5A41825CA at byte 790
= This item is included in the report because it matches --limit.
= Scores: V/M = 1.19
= Time range: 2018-07-31 10:17:33 to 10:36:36
= Attribute    pct   total     min     max     avg     95%  stddev  median
= ============ === ======= ======= ======= ======= ======= ======= =======
= Count        100       6
= Exec time    100     16s      1s      5s      3s      5s      2s      4s
= Lock time      0       0       0       0       0       0       0       0
= Rows sent    100       6       1       1       1       1       0       1
= Rows examine   0       0       0       0       0       0       0       0
= Query size   100      90      15      15      15      15       0      15
= String:
= Databases    test
= Hosts        192.168.80.1
= Users        root
= Query_time distribution
=   1us
=  10us
= 100us
=   1ms
=  10ms
= 100ms
=    1s  ================================================================
=  10s+
= EXPLAIN /*!50100 PARTITIONS*/
SELECT SLEEP(5)\G