mysqlsla低速クエリ分析ツール

Mysqlsla Slow Query Analysis Tool

mysqlslaとは何ですか？

Mysqlslaは、Perlのdaniel-nichterによって記述されたスクリプトであり、Mysqlログを処理および分析するために特別に存在します。

mysqlslaはどのような問題を解決できますか？

Mysql DBAとして、日常業務でログを処理するのは通常のことです。 Mysqlを介したログは、主に3つのタイプに分けられます。一般ログ、低速ログ、およびバイナリログです。クエリログを通じて、ビジネスロジックとビジネス特性を分析できます。遅いログを通して、サーバーのボトルネックを見つけることができます。バイナリログを介して、データを復元できます。 Mysqlslaはすべてのログを処理できます。これが、私が気に入っている主な理由の1つです。

mysqlslaのインストール

ダウンロードリンク https://pan.baidu.com/s/1kfBYu49RtdSOhiT4hlvj_A
tar -xvfz mysqlsla＆shy2.03.tar.gz
cd mysqlsla＆shy2.03
perl Makefile.PL
作る
インストールする

一般的な問題

1.perl環境でエラーが報告されることはありません。yumをインストールするだけです。
yum install perl
2、ExtUtils /MakeMaker.pmが見つかりません

yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

mysqlslaの使用

前提条件、mysqlの低速クエリ機能をオンにする必要があります

「％slow％」などの変数を表示する
set global slow_query_log = ON #OpenMySQLスロークエリ関数

slow_query_logのステータスがONになっていることがわかります

「long_query_time」のような変数を表示します。＃MySQLの遅いクエリ時間設定を表示します。デフォルトは10秒です。
set global long_query_time = 5 #Modifyは、5秒以内にクエリを記録します
mysql構成ファイルを変更して低速クエリを開くこともできます

slow-query-log = on #Open MySQL slow query function slow_query_log_file = /mysql/mysql5.7/data/localhost-slow.log #Set the MySQL slow query log path long_query_time = 5 #Modify to record the query within 5 seconds, the default does not set this parameter to record the query within 10 seconds log-queries-not-using-indexes = on #Record queries that do not use indexes

次のエラーの解決策

エラーの原因は、システムがDBIコンポーネントをインストールしないことです。また、perl-DBIをyumインストールすることもできます。

wget http://www.cpan.org/modules/by-module/DBI/DBI-1.643.tar.gz tar -zxvf DBI-1.643.tar.gz cd DBI-1.643 perl Makefile.PL make make install

-ltは-log-typeの省略形で、タイプはslow、general、binary、msl、udlであり、slowクエリ分析はslowとして指定できます。
-sortは、結果の並べ替えに使用するパラメーターを指定します。デフォルトでは、t_sumが並べ替えに使用されます。

t_sum: sort by total time c_sum: sort by total number of times c_sum_p: sql statement execution times as a percentage of total execution times.

-上：
SQLの数を表示します。デフォルトは10です。これは、ルールに従った注文の最上位の番号を意味します。

オンラインで見つけた文法の紹介

* ­­--log­-type (-­lt) TYPE LOGS It is used to specify what type of log is parsed, but rest assured, even if you don't specify it, it will automatically determine the basic log, slow, general, binary, msl or udl by default. * ­­--db-­inheritance This parameter was discovered through the source code, and I didn't read the document carefully before. This parameter is very important. It was not used much before, but if you do not specify it, it is very likely that the corresponding SQL statement in the parsed log cannot be found in the database source. * ­­--explain (­-ex) The default is disabled, and the report format must be standard. The function is to perform explain analysis on each uniq SQL, which is very practical. * --­­grep PATTERN Regular matching function. You can do any regular matching on your report. For example: I want to check all the analysis results on the two tables'table_lc' and'table_cl'. There is no way to find out the ordinary way, but you can do this: mysqlsla --grep '(table_lc)|(table_cl)' db10-037.log * --­­meta-­filter (­-mf) CONDTIONS This is a kind of filter, here is a brief introduction, see the filter chapter for details. The meta here refers to meta-property, which will be introduced later. The typical is: c_sum, which refers to the sum of the number of SQL. Basic usage: [meta][op][value] Pay special attention to the op here. There are only three kinds of ops here. >,=, you can't get the result you want. Here [op] has a special usage: t_sum between 10 and 100, which can be written like this:'t_sum>10,t_sum<100' For example: I want to view all the results of ark_db mysqlsla -mf 'db=ark_db' --top 100 slow_1.log slow_2.log ­­* --statement-­filter (­-sf) CONDTIONS This is another type of filter If nothing else, just look at the usage form: [+-][type],[type] The value of [TYPE] is: SELECT, CREATE, DROP, UPDATE, INSERT, etc. For example: if I only want to view select, create type of SQL. mysqsla -sf '+SELECT,CREATE' slow.log For example: If I want to view all results except select. mysqlsla -sf'-SELECT' type of SQL. * --top N Display top N in descending order by default, default is 10. * --sort META Sort according to the value of Meta. The default slow log meta is t_sum, as for what t_sum is, please see the filter chapter. * ­­--report-­format (-­rf) FILE Reports can be customized here. But it must be in a fixed format so that mysqlsla can parse it. Such as: (extra command line options) HEADER (header line format) (header line values) REPORT report line format report line values This is very useful, especially when you can use scripts to process your own customized reports later, which is very convenient. This is also a manifestation of its powerful functions. * ­­--reports (­-R) REPORTS You can specify to output reports in a certain format. The fixed report forms are: standard, time-­all, print-­unique, print-­all, dump What you must pay attention to: If you choose time-­all, time­-each­-query, it will default to safetySQL statement filter of '+SELECT,USE'. However, if you customize -statment-fileter, these dml statements will be executed in the production environment, which will pollute online data, which is very dangerous. Please pay special attention and be careful. * --­­udl­-format (­-uf) FILE You can customize the log and let mysqlsla parse it. This is more complicated and will be discussed in a later chapter. * ­­--replay FILE You can replay the SQL in the file. Practical, but I generally don’t use it, worrying about data security. Summary: Basically, the above commands can solve 50% of your needs and are very commonly used functions. Try it yourself. Here are a few simple examples: 1) List the first 100,000 records of non-SELECT type starting with the user usr_rx and containing ac_. --The default m/P/io is case sensitive mysqlsla --grep 'ac_.*' -mf user='usr_rx' -sf '-SELECT' --top 100000 slow.log 2) List the records containing broker_system_messages_bj or broker_system_messages_oth. --The key is to put parentheses. mysqlsla --grep '(broker_system_messages_bj)|(broker_system_messages_oth.*)' slow.log 3) List all queries of ajk_propertys, and sort them according to SQL rows_exam. mysqlsla --grep 'ajk_propertys' -sf '+SELECT' --sort='re_sum' --db-inheritance slow.log

＃記録された20個のSQLステートメントをクエリし、select.logに書き込みます

mysqlsla -lt slow --sort t_sum --top 20 /data/mysql/127-slow.log >/tmp/select.log #Statistics slow query file is the slow query sql of all selects in /data/mysql/127-slow.log, and displays the 100 sql with the longest execution time, and writes it to sql_select.log mysqlsla -lt slow -sf '+select' -top 100 /data/mysql/127-slow.log >/tmp/sql_select.log #Statistics slow query file is /data/mysql/127-slow.log The database is the slow query sql of all select and update of mydata, and the 100 sql with the most queries are written to sql_num.sql mysqlsla -lt slow -sf '+select,update' -top 100 -sort c_sum -db mydata /data/mysql/127-slow.log >/tmp/sql_num.log