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

In this issue, the editor will bring you about how to analyze the mysql log in mysqlsla. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Installation of mysqlsla

Wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz

Tar zvxf mysqlsla-2.03.tar.gz

Cd mysqlsla-2.03

Perl Makefile.PL

Make

Make install

Use parameter description

-log-type (- lt) type logs:

This parameter is used to determine the type of log, mainly slow, general, binary, msl, udl, and slow when analyzing slow log.

-- sort:

Determine what parameters are used to sort the analysis results, which is sorted by t_sum by default.

T_sum is sorted by total time, c_sum by total number of times

-- top:

Displays the number of sql. The default is 10, indicating the number of entries sorted by rules.

-- statement-filter (- sf) [+ -] [TYPE]:

Filter the types of sql statements, such as select, update, drop. [TYPE] there are SELECT, CREATE, DROP, UPDATE, INSERT, such as "+ SELECT,INSERT". If it does not appear, the default is -, that is, not included.

-- databases db:

Log of which library to process:

Statistical parameter description

Queries total: total number of queries

Unique: the number of sql after weight removal

Sorted by: output slow sql statistics with the most significant ranking of the contents of the report, including average execution time, waiting lock time, total number of resulting rows, total number of rows scanned.

Count: the number of times sql is executed and its percentage to the total number of slow log.

Time: execution time, including total time, average time, minimum, maximum time, time as a percentage of total slow sql time.

95% of Time: removal of the fastest and slowest sql, coverage accounts for 95% of the sql execution time.

Lock Time: the time to wait for the lock.

95% of Lock: 95% slow sql waiting time for lock.

Rows sent: the number of resulting rows, including average, minimum, and maximum.

Rows examined: the number of rows scanned.

Database: which database does it belong to?

Users: which user, IP, accounts for the percentage of sql executed by all users.

Query abstract: abstract sql statement.

Query sample: sql statement.

Use example

Count the slow query sql of all select whose slow query file is dowload_server1-slow.log, and show the 10 sql with the longest execution time, and write them to sql_time.sql

Mysqlsla-lt slow-sf "+ select"-top 10 dowload_server1-slow.log > test_time.log

Count the slow query sql of all the select and update of ultraxsmutf8 in the database with the slow query file of dowload_server1-slow.log, and write to sql_num.sql the sql with the largest number of queries.

Mysqlsla-lt slow-sf "+ select,update"-top 100-sort c_sum-db ultraxsmutf8 dowload_server1-slow.log > num_time.log

FAQ

Error:

Can't locate DBI.pm in @ INC (@ INC contains: / usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-... .

Solution: perl-MCPAN-e'install DBI' or cpan DBI

2. Error:

Use of uninitialized value in numeric comparison () at / usr/bin/mysqlsla line 2962.

Solution: this error occurs when using-sort t_su. The default is to use time sorting, so it can be removed.

The above is how to analyze the mysql log in mysqlsla shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.