edvakf/01.access.txt

## 01.access.txt
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

## 02.parse.rb
#!/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)

## 03.mysql.txt
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)

## 04.pt-query-digest.sql
# 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
	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