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

Editor to share with you a case of mysqldumpslow log analysis, I hope you will gain something after reading this article. Let's discuss it together.

1. Mysqldumpslow

Official document: http://dev.mysql.com/doc/refman/5.1/en/mysqldumpslow.html

Shell > mysqldumpslow [options] [log_file...] Table 4.12. Mysqldumpslow Option Reference

FormatConfig FileDescriptionIntroductionDeprecatedRemoved-aDo not abstract all numbers to N and strings to Smurn numAbstract numbers with at least the specified digits--debugdebugWrite debugging information-g patternOnly consider statements that match the pattern--helpDisplay help message and exit-h nameHost name of the server in the log file name-i nameName of the server instance-lDo not subtract lock time from total time-rReverse the sort order-s valueHow to sort output-t numDisplay only first num queries--verboseverboseVerbose mode

One of the features of mysql is that slow sql statements can be run under log. There is no such log by default. In order to enable this function, you need to modify my.cnf or add some parameters when mysql starts. If you modify it in my.cnf, you need to add the following lines

Long_query_time = 1

Log-slow-queries = / var/youpath/slow.log

Log-queries-not-using-indexes

Long_query_time refers to how long the execution of the sql will be log down, here is 1 second.

The log-slow-queries setting writes the log there, which can be empty, the system will give a default file host_name-slow.log, and the log I generated is in the data directory of mysql.

Log-queries-not-using-indexes means literally. Log does not use indexed query.

Turn on the above parameters and run them for a period of time, then you can turn them off so as not to affect the production environment.

The next step is the analysis. My file here is called host-slow.log.

First mysqldumpslow-help below, I mainly use

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

-t NUM just show the top n queries

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

-s, is the order of order, the description is not detailed enough, I used it, including looking at the code, mainly

CPeragne, ac,at,al,ar, respectively, are sorted by the number of query, the time, the time of lock and the number of records returned, with a flashback added before.

-t, which means top n, that is, how many previous pieces of data are returned.

-g, a regular matching pattern can be written later, which is case-insensitive.

Mysqldumpslow-s c-t 20 host-slow.log

Mysqldumpslow-s r-t 20 host-slow.log

The above command shows the 20 most visited sql statements and the 20 sql that returned the most recordsets.

Mysqldumpslow-t 10-st- g "left join" host-slow.log

This is to return the first 10 sql statements with left concatenation in time.

Using this tool, you can find out which sql statements are performance bottlenecks and optimize them, such as indexing, the implementation of the application, and so on.

II. Mysqldumpslow

MySQL comes with slow log's analysis tool mysqldumpslow, but there is no description. This paper introduces the usage of the script by analyzing it.

Slow log is a file written by MySQL according to the execution time of SQL statements, which is used to analyze slow statements.

As long as it is configured in the my.cnf file:

Log-slow-queries = [slow_query_log_filename]

SQL statements that exceed the default 10s execution time can be recorded.

If you want to change the default settings, you can add:

Long_query_time = 5

Set to 5s.

To record all SQL statements, you can write:

Log-long-format

# t=time, l=lock time, r=rows

# at, al, and ar are the corresponding averages

The parameters acceptable to mysqldumpslow are:

'Variety, # verbose

'Deliverance, # debug

'sordering, # sort (t, at, l, al, r, ar etc)

'ringing ordering, # inverted (largest last instead of first)

'tasking iqueries, # showing the highest n queries

'All numbers are not displayed as N and strings are displayed as'S'

'Naturified, # abstract numbers with at least n digits within names

'glossaries, # grep: only consider stmts that include this string

'hackers, # hostname of db server for *-slow.log filename (can be wildcard)

'ifans, # name of server instance (if using mysql.server startup script)

'lumbnail, # don't subtract lock time from total time

'verbose | venerable wow wow # verbose

'help+', # write usage info

'debug | dumped, # debug

'skeeper, # what to sort by (t, at, l, al, r, ar etc)

'Renewable, # reverse the sort order (largest last instead of first)

'tabilified, # just show the top n queries

'Another woman, # don't abstract all numbers to N and strings to'S'

'Naturified, # abstract numbers with at least n digits within names

'glossaries, # grep: only consider stmts that include this string

'hackers, # hostname of db server for *-slow.log filename (can be wildcard)

'ifans, # name of server instance (if using mysql.server startup script)

'lumbnail, # don't subtract lock time from total time

Display the first 10 slow query logs in reverse chronological order: mysqldumpslow-s at-t 10 / var/db/mysql/db-slow.log

After reading this article, I believe you have a certain understanding of "the case of mysqldumpslow log analysis". 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.