slp

module
v0.1.1 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jul 26, 2022 License: Apache-2.0

README

slp

slp is a MySQL SlowLog Profiler.

日本語

This tool is similar to mysqldumpslow, but can check more metrics.

Installation

Download from https://github.com/tkuchiki/slp/releases

Usage

$ slp --help
slp is a (MySQL) SlowLog Profiler

Usage:
  slp [flags]
  slp [command]

Available Commands:
  completion           Generate the autocompletion script for the specified shell
  diff                 Show the difference between the two profile results
  help                 Help about any command
  print-output-options Print --output options

Flags:
      --bundle-values            Bundle VALUES of INSERT statement
      --bundle-where-in          Bundle WHERE IN conditions
      --config string            The configuration file
      --dump string              Dump profiled data as YAML
      --file string              The slowlog file
  -f, --filters string           Only the logs are profiled that match the conditions
      --format string            The output format (table, markdown, tsv, csv and html) (default "table")
  -h, --help                     help for slp
      --limit int                The maximum number of results to display (default 5000)
      --load string              Load the profiled YAML data
  -m, --matching-groups string   Specifies Query matching groups separated by commas
  -a, --noabstract               Do not abstract all numbers to N and strings to 'S'
      --noheaders                Output no header line at all (only --format=tsv, csv)
      --nosave-pos               Do not save position file
  -o, --output string            Specifies the results to display, separated by commas (default "simple")
      --page int                 Number of pages of pagination (default 100)
      --percentiles string       Specifies the percentiles separated by commas
      --pos string               The position file
  -r, --reverse                  Sort results in reverse order
      --show-footers             Output footer line at all (only --format=table, markdown)
      --sort string              Output the results in sorted order (default "count")
  -v, --version                  version for slp

Use "slp [command] --help" for more information about a command.

$ cat example/slow.log | slp
+-------+---------------------------------+----------------+----------------+----------------+----------------+
| COUNT |              QUERY              | MIN(QUERYTIME) | MAX(QUERYTIME) | SUM(QUERYTIME) | AVG(QUERYTIME) |
+-------+---------------------------------+----------------+----------------+----------------+----------------+
| 1     | DELETE FROM `t2` WHERE 'S'      | 0.369618       | 0.369618       | 0.369618       | 0.369618       |
|       | < `c1_date` OR `c2` NOT IN      |                |                |                |                |
|       | (SELECT `c3` FROM `t3`)         |                |                |                |                |
| 1     | DELETE FROM `t4` WHERE `c4`     | 7.148949       | 7.148949       | 7.148949       | 7.148949       |
|       | NOT IN (SELECT `c1` FROM `t1`)  |                |                |                |                |
| 1     | INSERT INTO `t2`                | 0.010498       | 0.010498       | 0.010498       | 0.010498       |
|       | (`c2_id`,`c2_string`,`c2_date`) |                |                |                |                |
|       | VALUES (N,'S','S')              |                |                |                |                |
| 1     | INSERT INTO `t2`                | 0.010498       | 0.010498       | 0.010498       | 0.010498       |
|       | (`c2_id`,`c2_string`,`c2_date`) |                |                |                |                |
|       | VALUES (N,'S','S'),(N,'S','S')  |                |                |                |                |
| 1     | SELECT * FROM `t5` WHERE        | 0.010753       | 0.010753       | 0.010753       | 0.010753       |
|       | `c5_id` IN ('S','S','S')        |                |                |                |                |
| 1     | SELECT `t1`.`id` FROM `t1`      | 0.020219       | 0.020219       | 0.020219       | 0.020219       |
|       | JOIN `t2` ON `t2`.`t1_id` =     |                |                |                |                |
|       | `t1`.`id` WHERE `t2`.`t1_id` =  |                |                |                |                |
|       | 'S' ORDER BY `t2`.`t1_id`       |                |                |                |                |
| 2     | UPDATE `t1` SET                 | 1.428614       | 3.504247       | 4.932861       | 2.466430       |
|       | `c1_count`=(SELECT COUNT(N) AS  |                |                |                |                |
|       | `cnt` FROM `t2` WHERE `c3_id`   |                |                |                |                |
|       | = `t3`.`id`)                    |                |                |                |                |
+-------+---------------------------------+----------------+----------------+----------------+----------------+

print-output-options

You can see the --output option values.

$ slp print-output-options
count
query
min-query-time
max-query-time
sum-query-time
avg-query-time
min-lock-time
max-lock-time
sum-lock-time
avg-lock-time
min-rows-sent
max-rows-sent
sum-rows-sent
avg-rows-sent
min-rows-examined
max-rows-examined
sum-rows-examined
avg-rows-examined
min-rows-affected
max-rows-affected
sum-rows-affected
avg-rows-affected
min-bytes-sent
max-bytes-sent
sum-bytes-sent
avg-bytes-sent

$ slp print-output-options --percentiles 95,99
count
query
min-query-time
max-query-time
sum-query-time
avg-query-time
p95-query-time
p99-query-time
min-lock-time
max-lock-time
sum-lock-time
avg-lock-time
p95-lock-time
p99-lock-time
min-rows-sent
max-rows-sent
sum-rows-sent
avg-rows-sent
p95-rows-sent
p99-rows-sent
min-rows-examined
max-rows-examined
sum-rows-examined
avg-rows-examined
p95-rows-examined
p99-rows-examined
min-rows-affected
max-rows-affected
sum-rows-affected
avg-rows-affected
p95-rows-affected
p99-rows-affected
min-bytes-sent
max-bytes-sent
sum-bytes-sent
avg-bytes-sent
p95-bytes-sent
p99-bytes-sent

diff

  • Show the difference between the two profile results
  • + means an increasing count, rows_sent, rows_examined, rows_affected, bytes_sent, and query_time、lock_time are slower
  • - means a decreasing count, rows_sent, rows_examined, rows_affected, bytes_sent, and query_time、lock_time are faster
$ cat /path/to/slow.log | slp --dump dumpfile1.yaml

$ cat /path/to/slow.log | slp --dump dumpfile2.yaml

$ slp diff dumpfile1.yaml dumpfile2.yaml --show-footers
+---------+---------------------------------+----------------+-------------------+-------------------+-------------------+
|  COUNT  |              QUERY              | MIN(QUERYTIME) |  MAX(QUERYTIME)   |  SUM(QUERYTIME)   |  AVG(QUERYTIME)   |
+---------+---------------------------------+----------------+-------------------+-------------------+-------------------+
| 1       | SELECT * FROM `t5` WHERE        | 0.010753       | 0.010753          | 0.010753          | 0.010753          |
|         | `c5_id` IN ('S','S','S')        |                |                   |                   |                   |
| 1       | DELETE FROM `t2` WHERE 'S'      | 0.369618       | 0.369618          | 0.369618          | 0.369618          |
|         | < `c1_date` OR `c2` NOT IN      |                |                   |                   |                   |
|         | (SELECT `c3` FROM `t3`)         |                |                   |                   |                   |
| 1       | DELETE FROM `t4` WHERE `c4`     | 7.148949       | 7.148949          | 7.148949          | 7.148949          |
|         | NOT IN (SELECT `c1` FROM `t1`)  |                |                   |                   |                   |
| 1       | INSERT INTO `t2`                | 0.010498       | 0.010498          | 0.010498          | 0.010498          |
|         | (`c2_id`,`c2_string`,`c2_date`) |                |                   |                   |                   |
|         | VALUES (N,'S','S')              |                |                   |                   |                   |
| 1       | INSERT INTO `t2`                | 0.010498       | 0.010498          | 0.010498          | 0.010498          |
|         | (`c2_id`,`c2_string`,`c2_date`) |                |                   |                   |                   |
|         | VALUES (N,'S','S'),(N,'S','S')  |                |                   |                   |                   |
| 1       | SELECT `t1`.`id` FROM `t1`      | 0.020219       | 0.020219          | 0.020219          | 0.020219          |
|         | JOIN `t2` ON `t2`.`t1_id` =     |                |                   |                   |                   |
|         | `t1`.`id` WHERE `t2`.`t1_id` =  |                |                   |                   |                   |
|         | 'S' ORDER BY `t2`.`t1_id`       |                |                   |                   |                   |
| 2       | UPDATE `t1` SET                 | 1.428614       | 3.504247          | 4.932861          | 2.466430          |
|         | `c1_count`=(SELECT COUNT(N) AS  |                |                   |                   |                   |
|         | `cnt` FROM `t2` WHERE `c3_id`   |                |                   |                   |                   |
|         | = `t3`.`id`)                    |                |                   |                   |                   |
| 2 (+1)  | DELETE FROM `t1` WHERE 'S' <    | 0.035678       | 1.035678 (+1.000) | 1.071356 (+1.036) | 0.535678 (+0.500) |
|         | `c1_date`                       |                |                   |                   |                   |
+---------+---------------------------------+----------------+-------------------+-------------------+-------------------+
| 10 (+1) |                                                                                                               
+---------+---------------------------------+----------------+-------------------+-------------------+-------------------+

Global options

See: Usage samples

  • -c, --config
    • The configuration file
    • YAML
  • --file=FILE
    • The access log file
  • -d, --dump=DUMP
    • File path for creating the profile results to a file
  • -l, --load=LOAD
    • File path to read the results of the profile created with the -d, --dump option
    • Can expect it to work fast if you change the --sort and --reverse options for the same profile results
  • --sort=count
    • Output the results in sorted order
    • Sort in ascending order
    • count, query
    • min-query-time, max-query-time, sum-query-time, avg-query-time
    • min-lock-time, max-lock-time, sum-lock-time, avg-lock-time
    • min-rows-sent, max-rows-sent, sum-rows-sent, avg-rows-sent
    • min-rows-examined, max-rows-examined, sum-rows-examined, avg-rows-examined
    • min-rows-affected, max-rows-affected, sum-rows-affected, avg-rows-affected
    • min-bytes-sent, max-bytes-sent, sum-bytes-sent, avg-bytes-sent
    • The default is count
    • pN(1~100)-<sort-key> is modified by the values specified in --percentiles
      • The p means percentile
      • e.g. p90-query-time
      • count and query does not support
  • -r, --reverse
    • Sort in desecending order
  • --format=table
    • Print the profile results in a table, Markdown, TSV, CSV and HTML format
    • The default is table format
  • --noheaders
    • Print no header when TSV and CSV format
  • --show-footers
    • Print the total number of each 1xx ~ 5xx in the footer of the table or Markdown format
  • --limit=5000
    • Maximum number of profile results to be printed
    • This setting is to avoid using too much memory
    • The default is 5000 lines
  • -o, --output="simple"
    • Specify the profile results to be print, separated by commas
    • count, query, min-query-time, max-query-time, sum-query-time, avg-query-time, min-lock-time, max-lock-time, sum-lock-time, avg-lock-time, min-rows-sent, max-rows-sent, sum-rows-sent, avg-rows-sent, min-rows-examined, max-rows-examined, sum-rows-examined, avg-rows-examined, min-rows-affected, max-rows-affected, sum-rows-affected, avg-rows-affected, min-bytes-sent, max-bytes-sent, sum-bytes-sent, avg-bytes-sent
      • These outputs are the same for all
      • pN(1~100)-<sort-key> is modified by the values specified in --percentiles
    • The default is simple
    • standard outputs all without *-rows-affected and *-bytes-sent
  • -m, --matching-groups=PATTERN,...
    • Treat Queries that match regular expressions as the same Query
    • Evaluate in the specified order. If matched, no further evaluation is performed.
  • -f, --filters=FILTERS
    • Filters the targets for profile
    • See Filter
  • --pos=POSITION_FILE
    • Stores the number of bytes to which the file has been read.
    • If the number of bytes is stored in the POSITION_FILE, the data after that number of bytes will be profiled
    • You can profile without truncating the file
      • Also, it is expected to work fast because it seeks and skips files
  • --nosave-pos
    • Data after the number of bytes specified by --pos is profiled, but the number of bytes reads is not stored
  • --percentiles
    • Specifies the percentile values to output, separated by commas
    • e.g. 90,95,99
  • -a, --noabstract
    • Do not abstract all numbers to N and strings to 'S'
  • --bundle-values
  • --bundle-where-in

Filter

It is a function to include or exclude targets according to the conditions.

Variables

Filter on the following variables:.

  • Query
    • SQL
  • QueryTime
    • The time to acquire queries in seconds
  • LockTime
    • The time to acquire locks in seconds
  • RowsSent
    • The number of rows sent to the client
  • RowsExamined
    • The number of rows examined by the server layer
  • RowsAffected
    • The number of rows changed
  • BytesSent
    • The number of bytes sent to all clients
Operators

The following operators are available:.

  • +, -, *, /, %, **(pow)
  • ==, !=, <, >, <=, >=
  • not, !
  • and, &&
  • or, ||
  • matches
    • e.g.
      • Query matches "PATTERN"
      • not(Query matches "PATTERN")
  • contains
    • e.g.
      • Query contains "STRING"
      • not(Query contains "STRING")
  • startsWith
    • e.g.
      • Query startsWith "PREFIX"
      • not(Query startsWith "PREFIX")
  • endsWith
    • e.g.
      • Query endsWith "SUFFIX"
      • not(Query endsWith "SUFFIX")
  • in
    • e.g.
      • QueryTime in [0.1, 0.2]
      • QueryTime not in [0.1, 0.2]

See: https://github.com/antonmedv/expr/blob/master/docs/Language-Definition.md

Usage samples

See: Usage samples

Donation

Donations are welcome as always!
❤ Sponsor

Directories

Path Synopsis
cmd
slp
slp/cmd
parser
sqlparser

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.