Skip to content

Instantly share code, notes, and snippets.

@edvakf edvakf/01.access.txt
Last active Dec 27, 2015

Embed
What would you like to do?
sort by avg_time
avg total count method path
9.46 2753.95 291 GET /ticket/1
5.28 1510.27 286 GET /artist/1
4.54 1329.08 293 GET /
4.37 454.62 104 POST /buy
3.82 3.82 1 GET /admin/order.csv
1.22 2.45 2 POST /admin
0.15 0.15 1 GET /artist/2
0.03 4.28 130 GET /js/jquery-ui-1.8.24.custom.min.js
0.02 2.88 136 GET /js/jquery-1.8.2.min.js
0.02 2.62 143 GET /images/isucon_title.jpg
0.02 2.44 134 GET /css/ui-lightness/jquery-ui-1.8.24.custom.css
0.01 1.11 122 GET /css/isucon2.css
0.01 0.86 114 GET /js/isucon2.js
===============
sort by total_time
avg total count method path
9.46 2753.95 291 GET /ticket/1
5.28 1510.27 286 GET /artist/1
4.54 1329.08 293 GET /
4.37 454.62 104 POST /buy
0.03 4.28 130 GET /js/jquery-ui-1.8.24.custom.min.js
3.82 3.82 1 GET /admin/order.csv
0.02 2.88 136 GET /js/jquery-1.8.2.min.js
0.02 2.62 143 GET /images/isucon_title.jpg
1.22 2.45 2 POST /admin
0.02 2.44 134 GET /css/ui-lightness/jquery-ui-1.8.24.custom.css
0.01 1.11 122 GET /css/isucon2.css
0.01 0.86 114 GET /js/isucon2.js
0.15 0.15 1 GET /artist/2
===============
sort by total_count
avg total count method path
4.54 1329.08 293 GET /
9.46 2753.95 291 GET /ticket/1
5.28 1510.27 286 GET /artist/1
0.02 2.62 143 GET /images/isucon_title.jpg
0.02 2.88 136 GET /js/jquery-1.8.2.min.js
0.02 2.44 134 GET /css/ui-lightness/jquery-ui-1.8.24.custom.css
0.03 4.28 130 GET /js/jquery-ui-1.8.24.custom.min.js
0.01 1.11 122 GET /css/isucon2.css
0.01 0.86 114 GET /js/isucon2.js
4.37 454.62 104 POST /buy
1.22 2.45 2 POST /admin
3.82 3.82 1 GET /admin/order.csv
0.15 0.15 1 GET /artist/2
#!/usr/bin/env ruby
# cat access.log | ruby parse.rb
# if --since is given, logs before the specified time are ignored
# (by string comparison)
# eg. --since='2013-11-05T02:23'
# supported accesslog format : http://i2bskn.hateblo.jp/entry/2013/05/14/003726 style
#
# log_format ltsv "time:$time_iso8601"
# "\thost:$remote_addr"
# "\txff:$http_x_forwarded_for"
# "\tmethod:$request_method"
# "\tpath:$request_uri"
# "\tstatus:$status"
# "\tua:$http_user_agent"
# "\treq_size:$request_length"
# "\treq_time:$request_time"
# "\tres_size:$bytes_sent"
# "\tbody_size:$body_bytes_sent"
# "\tapp_time:$upstream_response_time";
#
# time:2013-11-05T02:23:14+09:00 host:202.232.134.129 xff:- method:GET path:/ status:200 ua:Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36 req_size:599 req_time:0.005 res_size:2951 body_size:2711 app_time:0.005
since = nil
if ARGV.length
m = /--since=(.*)/.match(ARGV[0])
since = m[1] if m
end
logs = []
while l = STDIN.gets
l.strip!
log = {}
l.split("\t").each{|kv|
k,v = kv.split(":", 2)
k = k.to_sym
if k == :req_time || k == :app_time
log[k] = v.to_f
else
log[k] = v
end
}
if !since.nil? && log[:time] < since
next
end
logs.push(log)
end
template = {
:path => '',
:mathod => '',
:total_time => 0,
:total_count => 0,
:avg_time => 0,
}
tmp = {}
logs.each {|log|
path = log[:path]
method = log[:method]
key = method+'::'+path
if tmp[key].nil?
tmp[key] = template.clone
tmp[key][:path] = path
tmp[key][:method] = method
end
tmp[key][:total_time] += log[:req_time]
tmp[key][:total_count] += 1
}
paths = []
tmp.to_a.each {|path, detail|
detail[:avg_time] = detail[:total_time] / detail[:total_count]
paths.push(detail)
}
def print_paths(paths)
puts "\tavg\ttotal\tcount\tmethod\tpath"
paths.each {|path|
puts "\t#{sprintf("%.2f",path[:avg_time])}\t#{sprintf("%.2f",path[:total_time])}\t#{path[:total_count]}\t#{path[:method]}\t#{path[:path]}"
}
end
puts "==============="
puts "sort by avg_time"
paths.sort! {|a, b| b[:avg_time] <=> a[:avg_time] }
print_paths(paths)
puts "==============="
puts "sort by total_time"
paths.sort! {|a, b| b[:total_time] <=> a[:total_time] }
print_paths(paths)
puts "==============="
puts "sort by total_count"
paths.sort! {|a, b| b[:total_count] <=> a[:total_count] }
print_paths(paths)
root@localhost [isucon2] > show tables;
+-------------------+
| Tables_in_isucon2 |
+-------------------+
| artist |
| order_request |
| stock |
| ticket |
| variation |
+-------------------+
5 rows in set (0.00 sec)
root@localhost [isucon2] > show create table artist\G
*************************** 1. row ***************************
Table: artist
Create Table: CREATE TABLE `artist` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost [isucon2] > show create table order_request\G
*************************** 1. row ***************************
Table: order_request
Create Table: CREATE TABLE `order_request` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`member_id` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
KEY `member_order` (`member_id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
root@localhost [isucon2] > show create table stock\G
*************************** 1. row ***************************
Table: stock
Create Table: CREATE TABLE `stock` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`variation_id` int(10) unsigned NOT NULL,
`seat_id` varchar(255) NOT NULL,
`order_id` int(10) unsigned DEFAULT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `variation_seat` (`variation_id`,`seat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=40961 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost [isucon2] > show create table ticket\G
*************************** 1. row ***************************
Table: ticket
Create Table: CREATE TABLE `ticket` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`artist_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost [isucon2] > show create table variation\G
*************************** 1. row ***************************
Table: variation
Create Table: CREATE TABLE `variation` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`ticket_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost [isucon2] > select * from ticket;
+----+-----------------------------------------------+-----------+
| id | name | artist_id |
+----+-----------------------------------------------+-----------+
| 1 | 西武ドームライブ | 1 |
| 2 | 東京ドームライブ | 1 |
| 3 | さいたまスーパーアリーナライブ | 2 |
| 4 | 横浜アリーナライブ | 2 |
| 5 | 西武ドームライブ | 2 |
+----+-----------------------------------------------+-----------+
5 rows in set (0.00 sec)
root@localhost [isucon2] > select * from artist;
+----+------------------------------+
| id | name |
+----+------------------------------+
| 1 | NHN48 |
| 2 | はだいろクローバーZ |
+----+------------------------------+
2 rows in set (0.00 sec)
root@localhost [isucon2] > select * from variation;
+----+-----------------+-----------+
| id | name | ticket_id |
+----+-----------------+-----------+
| 1 | アリーナ席 | 1 |
| 2 | スタンド席 | 1 |
| 3 | アリーナ席 | 2 |
| 4 | スタンド席 | 2 |
| 5 | アリーナ席 | 3 |
| 6 | スタンド席 | 3 |
| 7 | アリーナ席 | 4 |
| 8 | スタンド席 | 4 |
| 9 | アリーナ席 | 5 |
| 10 | スタンド席 | 5 |
+----+-----------------+-----------+
10 rows in set (0.00 sec)
# 2s user time, 110ms system time, 25.51M rss, 204.91M vsz
# Current date: Mon Nov 4 12:39:54 2013
# Hostname: ip-172-31-5-245
# Files: slow.log
# Overall: 6.04k total, 29 unique, 82.67 QPS, 22.48x concurrency _________
# Time range: 2013-11-04 12:19:48 to 12:21:01
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 1641s 2us 2s 272ms 2s 526ms 20ms
# Lock time 32s 0 1s 5ms 131us 54ms 44us
# Rows sent 2.13M 0 4.00k 370.68 3.88k 1.11k 0.99
# Rows examine 39.92M 0 40.32k 6.77k 38.40k 12.62k 4.96
# Query size 1.76M 5 60.06k 305.81 346.17 3.42k 59.77
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== =============== ===== ====== ===== =============
# 1 0x7D19E013D00D578E 1265.6517 77.1% 787 1.6082 0.05 SELECT stock variation ticket artist
# 2 0xE8F8BC1E697E237A 141.2586 8.6% 521 0.2711 0.04 SELECT variation stock
# 3 0x711470CB5B9597FE 109.8334 6.7% 544 0.2019 0.04 SELECT stock
# 4 0x470113498885BED2 45.9764 2.8% 112 0.4105 0.21 UPDATE stock
# MISC 0xMISC 78.5767 4.8% 4071 0.0193 0.0 <25 ITEMS>
# Query 1: 11.24 QPS, 18.08x concurrency, ID 0x7D19E013D00D578E at byte 2477095
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.05
# Time range: 2013-11-04 12:19:51 to 12:21:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 13 787
# Exec time 77 1266s 47ms 2s 2s 2s 270ms 2s
# Lock time 1 579ms 75us 58ms 735us 159us 4ms 113us
# Rows sent 0 5.31k 0 10 6.91 9.83 4.51 9.83
# Rows examine 77 30.80M 40.01k 40.14k 40.07k 38.40k 0.00 38.40k
# Query size 15 273.61k 356 356 356 356 0 356
# String:
# Databases isucon2
# Hosts
# Users isucon2app
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms #
# 100ms ###
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucon2` LIKE 'stock'\G
# SHOW CREATE TABLE `isucon2`.`stock`\G
# SHOW TABLE STATUS FROM `isucon2` LIKE 'variation'\G
# SHOW CREATE TABLE `isucon2`.`variation`\G
# SHOW TABLE STATUS FROM `isucon2` LIKE 'ticket'\G
# SHOW CREATE TABLE `isucon2`.`ticket`\G
# SHOW TABLE STATUS FROM `isucon2` LIKE 'artist'\G
# SHOW CREATE TABLE `isucon2`.`artist`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT stock.seat_id, variation.name AS v_name, ticket.name AS t_name, artist.name AS a_name FROM stock
JOIN variation ON stock.variation_id = variation.id
JOIN ticket ON variation.ticket_id = ticket.id
JOIN artist ON ticket.artist_id = artist.id
WHERE order_id IS NOT NULL
ORDER BY order_id DESC LIMIT 10\G
# Query 2: 7.78 QPS, 2.11x concurrency, ID 0xE8F8BC1E697E237A at byte 2442855
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.04
# Time range: 2013-11-04 12:19:51 to 12:20:58
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 8 521
# Exec time 8 141s 9ms 634ms 271ms 433ms 105ms 266ms
# Lock time 0 51ms 40us 12ms 97us 98us 542us 57us
# Rows sent 0 521 1 1 1 1 0 1
# Rows examine 10 4.08M 8.01k 8.01k 8.01k 8.01k 0 8.01k
# Query size 4 84.46k 166 166 166 166 0 166
# String:
# Databases isucon2
# Hosts
# Users isucon2app
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms #
# 10ms #
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucon2` LIKE 'variation'\G
# SHOW CREATE TABLE `isucon2`.`variation`\G
# SHOW TABLE STATUS FROM `isucon2` LIKE 'stock'\G
# SHOW CREATE TABLE `isucon2`.`stock`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) AS cnt FROM variation
INNER JOIN stock ON stock.variation_id = variation.id
WHERE variation.ticket_id = 2 AND stock.order_id IS NULL\G
# Query 3: 8.63 QPS, 1.74x concurrency, ID 0x711470CB5B9597FE at byte 1499754
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.04
# Time range: 2013-11-04 12:19:56 to 12:20:59
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 9 544
# Exec time 6 110s 13ms 637ms 202ms 339ms 88ms 180ms
# Lock time 0 32ms 28us 5ms 59us 80us 217us 38us
# Rows sent 99 2.12M 4.00k 4.00k 4.00k 4.00k 0 4.00k
# Rows examine 5 2.12M 4.00k 4.00k 4.00k 4.00k 0 4.00k
# Query size 1 35.59k 67 67 67 67 0 67
# String:
# Databases isucon2
# Hosts
# Users isucon2app
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms ####
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucon2` LIKE 'stock'\G
# SHOW CREATE TABLE `isucon2`.`stock`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT seat_id, order_id FROM stock
WHERE variation_id = 2\G
# Query 4: 1.67 QPS, 0.69x concurrency, ID 0x470113498885BED2 at byte 1718474
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.21
# Time range: 2013-11-04 12:19:51 to 12:20:58
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 112
# Exec time 2 46s 8ms 1s 411ms 945ms 297ms 315ms
# Lock time 95 30s 47us 1s 271ms 816ms 298ms 155ms
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 1 448.11k 4.00k 4.00k 4.00k 4.00k 0 4.00k
# Query size 0 12.25k 111 113 112.01 112.70 1 107.34
# String:
# Databases isucon2
# Hosts
# Users isucon2app
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms #
# 10ms ###
# 100ms ################################################################
# 1s ###
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isucon2` LIKE 'stock'\G
# SHOW CREATE TABLE `isucon2`.`stock`\G
UPDATE stock SET order_id = 12
WHERE variation_id = 1 AND order_id IS NULL
ORDER BY RAND() LIMIT 1\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select order_id = 12 from stock where variation_id = 1 AND order_id IS NULL
ORDER BY RAND() LIMIT 1 \G
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.