Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use mysqldumpslow tools, I hope you will learn something after reading this article, let's discuss it together!

Mysqldumpslow tool

Open slow query

View parameters related to slow query

Slow_query_log: whether slow query is enabled

Slow_query_log_file: slow query file location

Long_query_time: how long does it take to record

Log_queries_not_using_indexes: sql without an index

Mysql > show variables like 'slow_query%'

+-- +

| | Variable_name | Value |

+-- +

| | slow_query_log | ON |

| | slow_query_log_file | / var/lib/mysql/db-slow.log |

+-- +

2 rows in set (0.01sec)

Mysql > show variables like 'long_query_time'

+-+ +

| | Variable_name | Value |

+-+ +

| | long_query_time | 2.000000 | |

+-+ +

1 row in set (0.00 sec)

Mysql > show variables like 'log_queries_not%'

+-+ +

| | Variable_name | Value |

+-+ +

| | log_queries_not_using_indexes | OFF |

+-+ +

1 row in set (0.00 sec)

Setting method

Method 1: global variable setting

Mysql > set global slow_query_log='ON'

Mysql > set global slow_query_log_file='/var/lib/mysql/db-slow.log'

Mysql > set global long_query_time=2

Mysql > set global log_queries_not_using_indexes=1

Method 2: profile settings

Modify the configuration file my.cnf by adding the following under [mysqld]

Slow_query_log = ON

Slow_query_log_file = / var/lib/mysql/db-slow.log

Long_query_time = 2

Log_queries_not_using_indexes=1

Restart the mysql service after modification

test

Mysql > select sleep (3)

[root@db] # tail-f / var/lib/mysql/db-slow.log

# Time: 2018-12-25T08:00:48.199670Z

# User@Host: root [root] @ localhost [] Id: 2443797

# Query_time: 3.000198 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0

SET timestamp=1545724848

Select sleep (3)

[root@db] # mysqldumpslow-- help

Usage: mysqldumpslow [OPTS... ] [LOGS... ]

Parse and summarize the MySQL slow query log. Options are

-- verbose verbose

-- debug debug

-- help write this text to standard output

-v verbose

-d debug

-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default

Al: average lock time

Ar: average rows sent

At: average query time

C: count

L: lock time

R: rows sent

T: query time

-r reverse the sort order (largest last instead of first)

-t NUM just show the top n queries

-a don't abstract all numbers to N and strings to'S'

-n NUM abstract numbers with at least n digits within names

-g PATTERN grep: only consider stmts that include this string

-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard)

Default is'*', i.e. Match all

-i NAME name of server instance (if using mysql.server startup script)

-l don't subtract lock time from total time

Common parameters

-s sort mode followed by sequence

Average lock time of al

Average time for ar to return records

Average at query time (default)

C count

L lock time

R return record

T query time

-r reverse sort, the largest is at the end

-t is followed by a number and returns the number of lines

-a does not abstract all numbers as N and strings as S

-n is followed by a number, and at least n numbers in the name are abstracted as numbers.

-g regular can be followed by a regular matching pattern, which is case-insensitive

Case study:

1. Return 5 records based on count

Mysqldumpslow-s c-t 5 / var/lib/mysql/db-slow.log

two。 Return 5 records based on the returned record

Mysqldumpslow-s r-t 5 / var/lib/mysql/db-slow.log

3. Return 5 records according to query time

Mysqldumpslow-s t-t 5 / var/lib/mysql/db-slow.log

4. According to counting statistics, numbers and strings are not abstracted

Mysqldumpslow-s c-a-t 5 / var/lib/mysql/db-slow.log

After reading this article, I believe you have a certain understanding of "how to use mysqldumpslow tools". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.