Skip to content

Instantly share code, notes, and snippets.

@kazuph
Created August 5, 2014 07:38
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save kazuph/32aca1d3618495cce750 to your computer and use it in GitHub Desktop.
Save kazuph/32aca1d3618495cce750 to your computer and use it in GitHub Desktop.
pt-query-digestの実行結果の例
# 4.6s user time, 280ms system time, 40.89M rss, 287.59M vsz
# Current date: Tue Aug 5 07:36:47 2014
# Hostname: ip-172-31-23-18
# Files: tmp/mysql-slow.log.20140805-072831
# Overall: 9.98k total, 41 unique, 25.28 QPS, 0.46x concurrency __________
# Time range: 2014-08-05 07:21:41 to 07:28:16
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 183s 4us 7s 18ms 95ms 79ms 194us
# Lock time 557ms 0 4ms 55us 185us 85us 42us
# Rows sent 251.10k 0 201 25.75 97.36 45.74 0
# Rows examine 81.71M 0 40.62k 8.38k 40.32k 14.91k 0
# Query size 139.33M 13 1021.73k 14.29k 183.58 117.40k 62.76
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ===============
# 1 0x0389A01E13B4CB79 53.1083 29.0% 1430 0.0371 0.06 SELECT memos
# 2 0x23688CB381CADC9D 38.2254 20.9% 2086 0.0183 0.02 SELECT memos users memos
# 3 0x1A485622DCAF34D0 34.8531 19.0% 269 0.1296 0.01 SELECT memos
# 4 0x88929194AB4B746F 31.9038 17.4% 140 0.2279 1.42 INSERT REPLACE UPDATE REPLACE DELETE SELECT UPDATE INSERT SELECT INSERT UPDATE DELETE UPDATE DELETE INSERT UPDATE DELETE SELECT UPDATE DELETE memos
# 5 0x5682EDFB6674847D 15.0885 8.2% 265 0.0569 0.03 SELECT memos
# 6 0x08E3E2CA950A9030 6.3971 3.5% 2086 0.0031 0.02 SELECT memos
# MISC 0xMISC 3.4324 1.9% 3708 0.0009 0.0 <35 ITEMS>
# Query 1: 4.04 QPS, 0.15x concurrency, ID 0x0389A01E13B4CB79 at byte 147057657
# Scores: V/M = 0.06
# Time range: 2014-08-05 07:22:22 to 07:28:16
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 14 1430
# Exec time 29 53s 18us 202ms 37ms 116ms 46ms 596us
# Lock time 5 30ms 0 124us 20us 54us 24us 0
# Rows sent 23 60.18k 0 201 43.09 174.84 62.42 0
# Rows examine 28 23.56M 0 40.47k 16.87k 40.32k 19.49k 0
# Query size 0 68.02k 47 49 48.71 46.83 0 46.83
# String:
# Databases isucon
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us ###############################
# 100us ################################################################
# 1ms ####
# 10ms ################################################
# 100ms #######################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucon` LIKE 'memos'\G
# SHOW CREATE TABLE `isucon`.`memos`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM memos WHERE user='363' ORDER BY id\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: memos
# partitions: NULL
# type: index
# possible_keys: NULL
# key: PRIMARY
# key_len: 4
# ref: NULL
# rows: 60987
# Extra: Using where
# Query 2: 5.89 QPS, 0.11x concurrency, ID 0x23688CB381CADC9D at byte 147147752
# Scores: V/M = 0.02
# Time range: 2014-08-05 07:22:22 to 07:28:16
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 20 2086
# Exec time 20 38s 28us 148ms 18ms 53ms 19ms 13ms
# Lock time 48 268ms 0 4ms 128us 194us 125us 144us
# Rows sent 60 152.25k 0 100 74.74 97.36 42.30 97.36
# Rows examine 35 28.60M 0 40.62k 14.04k 38.40k 13.76k 10.29k
# Query size 0 379.90k 178 191 186.49 183.58 4.08 183.58
# String:
# Databases isucon
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us #######################
# 100us ######
# 1ms ######################
# 10ms ################################################################
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucon` LIKE 'memos'\G
# SHOW CREATE TABLE `isucon`.`memos`\G
# SHOW TABLE STATUS FROM `isucon` LIKE 'users'\G
# SHOW CREATE TABLE `isucon`.`users`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT memos.*,users.username FROM memos JOIN users ON users.id = memos.user JOIN (SELECT id FROM memos WHERE is_private=0 ORDER BY id DESC LIMIT 100 OFFSET 900) AS tmp ON tmp.id = memos.id\G
# EXPLAIN failed:
# Query 3: 0.76 QPS, 0.10x concurrency, ID 0x1A485622DCAF34D0 at byte 145502209
# Scores: V/M = 0.01
# Time range: 2014-08-05 07:22:22 to 07:28:15
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 269
# Exec time 19 35s 69ms 298ms 130ms 208ms 41ms 110ms
# Lock time 3 17ms 40us 183us 62us 103us 17us 57us
# Rows sent 10 27.42k 1 200 104.36 192.76 58.16 107.34
# Rows examine 12 10.60M 40.21k 40.47k 40.34k 40.32k 133 40.32k
# Query size 0 25.93k 97 99 98.70 97.36 0.74 97.36
# String:
# Databases isucon
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms #############
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucon` LIKE 'memos'\G
# SHOW CREATE TABLE `isucon`.`memos`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT id, content, is_private, created_at, updated_at FROM memos WHERE user='296' ORDER BY id DESC\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: memos
# partitions: NULL
# type: index
# possible_keys: NULL
# key: PRIMARY
# key_len: 4
# ref: NULL
# rows: 60987
# Extra: Using where
# Query 4: 4.24 QPS, 0.97x concurrency, ID 0x88929194AB4B746F at byte 122409915
# Scores: V/M = 1.42
# Time range: 2014-08-05 07:21:43 to 07:22:16
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 140
# Exec time 17 32s 32ms 7s 228ms 477ms 570ms 78ms
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 99 138.38M 813.06k 1021.73k 1012.13k 1009.33k 26.04k 1009.33k
# String:
# Databases isucon
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms ################################################################
# 100ms #####################################################
# 1s #
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucon` LIKE 'memos'\G
# SHOW CREATE TABLE `isucon`.`memos`\G
INSERT INTO `memos` VALUES (34759,345,'# Glob\n\nMatch files using the patterns the shell uses, like stars and stuff.\n\nThis is a glob implementation in JavaScript. It uses the `minimatch`\nlibrary to do its matching.\n\n## Attention: node-glob users!\n\nThe API has changed dramatically between 2.x and 3.x. This library is\nnow 100% JavaScript, and the integer flags have been replaced with an\noptions object.\n\nAlso, there\'s an event emitter class, proper tests, and all the other\nthings you\'ve come to expect from node modules.\n\nAnd best of all, no compilation!\n\n## Usage\n\n```javascript\nvar glob = require(\"glob\")\n\n// options is optional\nglob(\"**/*.js\", options, function (er, files) {\n // files is an array of filenames.\n // If the `nonull` option is set, and nothing\n // was found, then files is [\"**/*.js\"]\n // er is an error object or null.\n})\n```\n\n## Features\n\nPlease see the [minimatch\ndocumentation](https://github.com/isaacs/minimatch) for more details.\n\nSupports these glob features:\n\n* Brace Expansion\n* Extended glob matching\n* \"Globstar\" `**` matching\n\nSee:\n\n* `man sh`\n* `man bash`\n* `man 3 fnmatch`\n* `man 5 gitignore`\n* [minimatch documentation](https://github.com/isaacs/minimatch)\n\n## glob(pattern, [options], cb)\n\n* `pattern` {String} Pattern to be matched\n* `options` {Object}\n* `cb` {Function}\n * `err` {Error | null}\n * `matches` {Array<String>} filenames found matching the pattern\n\nPerform an asynchronous glob search.\n\n## glob.sync(pattern, [options])\n\n* `pattern` {String} Pattern to be matched\n* `options` {Object}\n* return: {Array<String>} filenames found matching the pattern\n\nPerform a synchronous glob search.\n\n## Class: glob.Glob\n\nCreate a Glob object by instanting the `glob.Glob` class.\n\n```javascript\nvar Glob = require(\"glob\").Glob\nvar mg = new Glob(pattern, options, cb)\n```\n\nIt\'s an EventEmitter, and starts walking the filesystem to find matches\nimmediately.\n\n### new glob.Glob(pattern, [options], [cb])\n\n* `pattern` {String} pattern to search for\n* `options` {Object}\n* `cb` {Function} Called when an error occurs, or matches are found\n * `err` {Error | null}\n * `matches` {Array<String>} filenames found matching the pattern\n\nNote that if the `sync` flag is set in the options, then matches will\nbe immediately available on the `g.found` member.\n\n### Properties\n\n* `minimatch` The minimatch object that the glob uses.\n* `options` The options object passed in.\n* `error` The error encountered. When an error is encountered, the\n glob object is in an undefined state, and should be discarded.\n* `aborted` Boolean which is set to true when calling `abort()`. There\n is no way at this time to continue a glob search after aborting, but\n you can re-use the statCache to avoid having to duplicate syscalls.\n* `statCache` Collection of all the stat results the glob search\n performed.\n* `cache` Convenience object. Each field has the following possible\n values:\n * `false` - Path does not exist\n * `true` - Path exists\n * `1` - Path exists, and is not a directory\n * `2` - Path exists, and is a directory\n * `[file, entries, ...]` - Path exists, is a directory, and the\n array value is the results of `fs.readdir`\n\n### Events\n\n* `end` When the matching is finished, this is emitted with all the\n matches found. If the `nonull` option is set, and no match was found,\n then the `matches` list contains the original pattern. The matches\n are sorted, unless the `nosort` flag is set.\n* `match` Every time a match is found, this is emitted with the matched.\n* `error` Emitted when an unexpected error is encountered, or whenever\n any fs error occurs if `options.strict` is set.\n* `abort` When `abort()` is called, this event is raised.\n\n### Methods\n\n* `abort` Stop the search.\n\n### Options\n\nAll the options that can be passed to Minimatch can also be passed to\nGlob to change pattern matching behavior. Also, some have been added,\nor have glob-specific ramifications.\n\nAll options are false by default, unless otherwise noted.\n\nAll options are added to the glob object, as well.\n\n* `cwd` The current working directory in which to search. Defaults\n to `process.cwd()`.\n* `root` The place where patterns starting with `/` will be mounted\n onto. Defaults to `path.resolve(options.cwd, \"/\")` (`/` on Unix\n systems, and `C:\\` or some such on Windows.)\n* `dot` Include `.dot` files in normal matches and `globstar` matches.\n Note that an explicit dot in a portion of the pattern will always\n match dot files.\n* `nomount` By default, a pattern starting with a forward-slash will be\n \"mounted\" onto the root setting, so that a valid filesystem path is\n returned. Set this flag to disable that behavior.\n* `mark` Add a `/` character to directory matches. Note that this\n requires additional stat calls.\n* `nosort` Don\'t sort the results.\n* `stat` Set to true to stat *all* results. This reduces performance\n somewhat, and is completely unnecessary, unless `readdir` is presumed\n to be an untrustworthy indicator of file existence. It will cause\n ELOOP to be triggered one level sooner in the case of cyclical\n symbolic links.\n* `silent` When an unusual error is encountered\n when attempting to read a directory, a warning will be printed to\n stderr. Set the `silent` option to true to suppress these warnings.\n* `strict` When an unusual error is encountered\n when attempting to read a directory, the process will just continue on\n in search of other matches. Set the `strict` option to raise an error\n in these cases.\n* `cache` See `cache` property above. Pass in a previously generated\n cache object to save some fs calls.\n* `statCache` A cache of results of filesystem information, to prevent\n unnecessary stat calls. While it should not normally be necessary to\n set this, you may pass the statCache from one glob() call to the\n options object of another, if you know that the filesystem will not\n change between calls. (See \"Race Conditions\" below.)\n* `sync` Perform a synchronous glob search.\n* `nounique` In some cases, brace-expanded patterns can result in the\n same file showing up multiple times in the result set. By default,\n this implementation prevents duplicates in the result set.\n Set this flag to disable that behavior.\n* `nonull` Set to never return an empty set, instead returning a set\n containing the pattern itself. This is the default in glob(3).\n* `nocase` Perform a case-insensitive match. Note that case-insensitive\n filesystems will sometimes result in glob returning results that are\n case-insensitively matched anyway, since readdir and stat will not\n raise an error.\n* `debug` Set to enable debug logging in minimatch and glob.\n* `globDebug` Set to enable debug logging in glob, but not minimatch.\n\n## Comparisons to other fnmatch/glob implementations\n\nWhile strict compliance with the existing standards is a worthwhile\ngoal, some discrepancies exist between node-glob and other\nimplementations, and are intentional.\n\nIf the pattern starts with a `!` character, then it is negated. Set the\n`nonegate` flag to suppress this behavior, and treat leading `!`\ncharacters normally. This is perhaps relevant if you wish to start the\npattern with a negative extglob pattern like `!(a|B)`. Multiple `!`\ncharacters at the start of a pattern will negate the pattern multiple\ntimes.\n\nIf a pattern starts with `#`, then it is treated as a comment, and\nwill not match anything. Use `\\#` to match a literal `#` at the\nstart of a line, or set the `nocomment` flag to suppress this behavior.\n\nThe double-star character `**` is supported by default, unless the\n`noglobstar` flag is set. This is supported in the manner of bsdglob\nand bash 4.1, where `**` only has special significance if it is the only\nthing in a path part. That is, `a/**/b` will match `a/x/y/b`, but\n`a/**b` will not.\n\nIf an escaped pattern has no matches, and the `nonull` flag is set,\nthen glob returns the pattern as-provided, rather than\ninterpreting the character escapes. For example,\n`glob.match([], \"\\\\*a\\\\?\")` will return `\"\\\\*a\\\\?\"` rather than\n`\"*a?\"`. This is akin to setting the `nullglob` option in bash, except\nthat it does not resolve escaped pattern characters.\n\nIf brace expansion is not disabled, then it is performed before any\nother interpretation of the glob pattern. Thus, a pattern like\n`+(a|{b),c)}`, which would not be valid in bash or zsh, is expanded\n**first** into the set of `+(a|b)` and `+(a|c)`, and those patterns are\nchecked for validity. Since those two are valid, matching proceeds.\n\n## Windows\n\n**Please only use forward-slashes in glob expressions.**\n\nThough windows uses either `/` or `\\` as its path separator, only `/`\ncharacters are used by this glob implementation. You must use\nforward-slashes **only** in glob expressions. Back-slashes will always\nbe interpreted as escape characters, not path separators.\n\nResults from absolute patterns such as `/foo/*` are mounted onto the\nroot setting using `path.join`. On windows, this will by default result\nin `/foo/*` matching `C:\\foo\\bar.txt`.\n\n## Race Conditions\n\nGlob searching, by its very nature, is susceptible to race conditions,\nsince it relies on directory walking and such.\n\nAs a result, it is possible that a file that exists when glob looks for\nit may have been deleted or modified by the time it returns the result.\n\nAs part of its internal implementation, this program caches all stat\nand readdir calls that it makes, in order to cut down on system\noverhead. However, this also makes it even more susceptible to races,\nespecially if the cache or statCache objects are reused between glob\ncalls.\n\nUsers are thus advised not to use a glob result as a guarantee of\nfilesystem state in the face of rapid changes. For the vast majority\nof operations, this is never a problem.\n',1,'2013-10-04 06:18:31','2013-10-04 11:39:43') /*... omitted ...*/\G
# Query 5: 0.75 QPS, 0.04x concurrency, ID 0x5682EDFB6674847D at byte 145226196
# Scores: V/M = 0.03
# Time range: 2014-08-05 07:22:23 to 07:28:16
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 265
# Exec time 8 15s 18us 152ms 57ms 122ms 43ms 65ms
# Lock time 2 13ms 0 130us 49us 119us 39us 57us
# Rows sent 3 9.03k 0 106 34.88 88.31 33.29 24.84
# Rows examine 8 6.93M 0 40.47k 26.79k 40.32k 18.67k 38.40k
# Query size 0 16.74k 63 65 64.68 62.76 0 62.76
# String:
# Databases isucon
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us ####################
# 100us #######################
# 1ms ###
# 10ms ################################################################
# 100ms ##############################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucon` LIKE 'memos'\G
# SHOW CREATE TABLE `isucon`.`memos`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM memos WHERE user='370' AND is_private=0 ORDER BY id\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: memos
# partitions: NULL
# type: index
# possible_keys: NULL
# key: PRIMARY
# key_len: 4
# ref: NULL
# rows: 60987
# Extra: Using where
# Query 6: 5.89 QPS, 0.02x concurrency, ID 0x08E3E2CA950A9030 at byte 145319863
# Scores: V/M = 0.02
# Time range: 2014-08-05 07:22:22 to 07:28:16
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 20 2086
# Exec time 3 6s 15us 52ms 3ms 22ms 7ms 35us
# Lock time 5 28ms 0 751us 13us 98us 36us 0
# Rows sent 0 305 0 1 0.15 0.99 0.35 0
# Rows examine 14 12.02M 0 40.47k 5.90k 40.32k 13.96k 0
# Query size 0 101.86k 50 50 50 50 0 50
# String:
# Databases isucon
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us ################################################################
# 100us #
# 1ms
# 10ms ###########
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucon` LIKE 'memos'\G
# SHOW CREATE TABLE `isucon`.`memos`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT count(*) AS c FROM memos WHERE is_private=0\G
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: memos
# partitions: NULL
# type: ALL
# possible_keys: NULL
# key: NULL
# key_len: NULL
# ref: NULL
# rows: 60987
# Extra: Using where
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment