isucon10-qualify's People
isucon10-qualify's Issues
get '/api/recommended_estate/:id'
- desc と asc が混在していると index が使われないことがある
- popularity が desc になっているカラムを用意する
mysql> explain SELECT * FROM estate WHERE (door_width >= '60' AND door_height >= '40') OR (door_width >= '60' AND door_height >= '157') OR (door_width >= '40' AND door_height >= '60') OR (door_width >= '40' AND door_height >= '157') OR (door_width >= '157' AND door_height >= '60') OR (door_width >= '157' AND door_height >= '40') ORDER BY popularity DESC, id ASC LIMIT 20;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| 1 | SIMPLE | estate | NULL | ALL | NULL | NULL | NULL | NULL | 28732 | 50.67 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
ALTER TABLE estate ADD desc_popularity INTEGER GENERATED ALWAYS AS (estate.popularity * (-1)) VIRTUAL;
explain SELECT * FROM estate WHERE (door_width >= '60' AND door_height >= '40') OR (door_width >= '60' AND door_height >= '157') OR (door_width >= '40' AND door_height >= '60') OR (door_width >= '40' AND door_height >= '157') OR (door_width >= '157' AND door_height >= '60') OR (door_width >= '157' AND door_height >= '40') ORDER BY desc_popularity ASC, id ASC LIMIT 20;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| 1 | SIMPLE | estate | NULL | ALL | NULL | NULL | NULL | NULL | 28601 | 50.67 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)
スコアログ
初期スコア
isucon@ip-172-31-81-66:~/isuumo/bench$ ./bench -target-url=http://18.180.227.123
2021/08/14 01:40:36 bench.go:78: === initialize ===
2021/08/14 01:40:42 bench.go:90: === verify ===
2021/08/14 01:40:43 bench.go:100: === validation ===
2021/08/14 01:41:26 load.go:181: 負荷レベルが上昇しました。
2021/08/14 01:41:31 fails.go:105: [client.(*Client).SearchEstatesNazotte] /home/isucon/isuumo/bench/client/webapp.go:367
message("POST /api/estate/nazotte: リクエストに失敗しました")
[client.(*Client).Do] /home/isucon/isuumo/bench/client/client.go:136
code(error timeout)
*url.Error("Post \"http://18.180.227.123/api/estate/nazotte\": context deadline exceeded (Client.Timeout exceeded while awaiting headers)")
*http.httpError("context deadline exceeded (Client.Timeout exceeded while awaiting headers)")
[CallStack]
[client.(*Client).Do] /home/isucon/isuumo/bench/client/client.go:136
[client.(*Client).SearchEstatesNazotte] /home/isucon/isuumo/bench/client/webapp.go:361
[scenario.estateNazotteSearchScenario] /home/isucon/isuumo/bench/scenario/estateNazotteSearchScenario.go:214
[scenario.runEstateNazotteSearchWorker] /home/isucon/isuumo/bench/scenario/load.go:100
[runtime.goexit] /home/isucon/local/go/src/runtime/asm_amd64.s:1373
2021/08/14 01:41:31 fails.go:105: [client.(*Client).SearchEstatesNazotte] /home/isucon/isuumo/bench/client/webapp.go:367
message("POST /api/estate/nazotte: リクエストに失敗しました")
[client.(*Client).Do] /home/isucon/isuumo/bench/client/client.go:136
code(error timeout)
*url.Error("Post \"http://18.180.227.123/api/estate/nazotte\": context deadline exceeded (Client.Timeout exceeded while awaiting headers)")
*http.httpError("context deadline exceeded (Client.Timeout exceeded while awaiting headers)")
[CallStack]
[client.(*Client).Do] /home/isucon/isuumo/bench/client/client.go:136
[client.(*Client).SearchEstatesNazotte] /home/isucon/isuumo/bench/client/webapp.go:361
[scenario.estateNazotteSearchScenario] /home/isucon/isuumo/bench/scenario/estateNazotteSearchScenario.go:214
[scenario.runEstateNazotteSearchWorker] /home/isucon/isuumo/bench/scenario/load.go:100
[runtime.goexit] /home/isucon/local/go/src/runtime/asm_amd64.s:1373
2021/08/14 01:41:43 bench.go:102: 最終的な負荷レベル: 1
{"pass":true,"score":424,"messages":[{"text":"POST /api/estate/nazotte: リクエストに失敗しました (タイムアウトしました)","count":2}],"reason":"OK","language":"ruby"}
alp
isucon@ip-172-31-126-203:~$ sudo less /var/log/nginx/access.log | alp ltsv -m '/api/estate/[0-9]+','/api/estate/req_doc/[0-9]+','/api/chair/[0-9]+','/api/recommended_estate/[0-9]+','/api/chair/buy/[0-9]+'
+-------+-----+-----+-----+-----+-----+--------+-------------------------------------------------+--------+--------+---------+--------+--------+--------+--------+--------+-----------+-----------+--------------+-----------+
| COUNT | 1XX | 2XX | 3XX | 4XX | 5XX | METHOD | URI | MIN | MAX | SUM | AVG | P90 | P95 | P99 | STDDEV | MIN(BODY) | MAX(BODY) | SUM(BODY) | AVG(BODY) |
+-------+-----+-----+-----+-----+-----+--------+-------------------------------------------------+--------+--------+---------+--------+--------+--------+--------+--------+-----------+-----------+--------------+-----------+
| 1 | 0 | 1 | 0 | 0 | 0 | POST | /initialize | 10.668 | 10.668 | 10.668 | 10.668 | 10.668 | 10.668 | 10.668 | 0.000 | 19.000 | 19.000 | 19.000 | 19.000 |
| 1 | 0 | 1 | 0 | 0 | 0 | GET | /favicon.ico | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 766.000 | 766.000 | 766.000 | 766.000 |
| 1 | 0 | 1 | 0 | 0 | 0 | GET | /estate/search/ | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 15595.000 | 15595.000 | 15595.000 | 15595.000 |
| 1 | 0 | 1 | 0 | 0 | 0 | POST | /api/estate | 0.536 | 0.536 | 0.536 | 0.536 | 0.536 | 0.536 | 0.536 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 1 | 0 | 1 | 0 | 0 | 0 | POST | /api/chair | 0.436 | 0.436 | 0.436 | 0.436 | 0.436 | 0.436 | 0.436 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| 1 | 0 | 1 | 0 | 0 | 0 | GET | /_next/static/chunks/22.775458c558d90bebd549.js | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 32917.000 | 32917.000 | 32917.000 | 32917.000 |
| 65 | 0 | 63 | 0 | 2 | 0 | POST | /api/estate/nazotte | 0.084 | 2.000 | 32.057 | 0.493 | 1.244 | 1.332 | 2.000 | 0.473 | 0.000 | 30210.000 | 975839.000 | 15012.908 |
| 118 | 0 | 118 | 0 | 0 | 0 | POST | /api/chair/buy/[0-9]+ | 0.004 | 0.096 | 2.691 | 0.023 | 0.040 | 0.056 | 0.076 | 0.016 | 0.000 | 0.000 | 0.000 | 0.000 |
| 122 | 0 | 122 | 0 | 0 | 0 | GET | /api/chair/search/condition | 0.004 | 0.108 | 0.956 | 0.008 | 0.020 | 0.032 | 0.104 | 0.016 | 1950.000 | 1950.000 | 237900.000 | 1950.000 |
| 162 | 0 | 162 | 0 | 0 | 0 | GET | /api/estate/search/condition | 0.000 | 0.096 | 1.100 | 0.007 | 0.016 | 0.032 | 0.080 | 0.014 | 1563.000 | 1563.000 | 253206.000 | 1563.000 |
| 239 | 0 | 239 | 0 | 0 | 0 | GET | /api/recommended_estate/[0-9]+ | 0.076 | 0.652 | 34.756 | 0.145 | 0.192 | 0.228 | 0.592 | 0.070 | 11546.000 | 12145.000 | 2820639.000 | 11801.837 |
| 243 | 0 | 241 | 0 | 2 | 0 | GET | /api/chair/[0-9]+ | 0.004 | 0.552 | 5.724 | 0.024 | 0.040 | 0.084 | 0.128 | 0.054 | 453.000 | 608.000 | 127096.000 | 523.029 |
| 309 | 0 | 309 | 0 | 0 | 0 | POST | /api/estate/req_doc/[0-9]+ | 0.004 | 0.096 | 2.768 | 0.009 | 0.024 | 0.040 | 0.076 | 0.015 | 0.000 | 0.000 | 0.000 | 0.000 |
| 342 | 0 | 342 | 0 | 0 | 0 | GET | /api/estate/low_priced | 0.012 | 0.488 | 29.556 | 0.086 | 0.140 | 0.168 | 0.208 | 0.042 | 11742.000 | 11742.000 | 4015764.000 | 11742.000 |
| 342 | 0 | 342 | 0 | 0 | 0 | GET | /api/chair/low_priced | 0.012 | 0.468 | 33.020 | 0.097 | 0.152 | 0.176 | 0.232 | 0.044 | 10737.000 | 10737.000 | 3672054.000 | 10737.000 |
| 588 | 0 | 587 | 0 | 1 | 0 | GET | /api/estate/[0-9]+ | 0.004 | 0.140 | 7.139 | 0.012 | 0.032 | 0.056 | 0.104 | 0.020 | 510.000 | 675.000 | 344465.000 | 585.825 |
| 717 | 0 | 714 | 0 | 3 | 0 | GET | /api/chair/search | 0.039 | 0.696 | 125.397 | 0.175 | 0.232 | 0.260 | 0.316 | 0.050 | 0.000 | 21132.000 | 9422032.000 | 13140.909 |
| 947 | 0 | 944 | 0 | 3 | 0 | GET | /api/estate/search | 0.084 | 0.676 | 151.591 | 0.160 | 0.220 | 0.248 | 0.308 | 0.047 | 0.000 | 29258.000 | 13957158.000 | 14738.287 |
+-------+-----+-----+-----+-----+-----+--------+-------------------------------------------------+--------+--------+---------+--------+--------+--------+--------+--------+-----------+-----------+--------------+-----------+
slow-log
sudo pt-query-digest /var/log/mysql/slow.log --resume /tmp/slow.pos
# A software update is available:
# Resuming /var/log/mysql/slow.log from offset 0 because resume file /var/log/mysql/slow.log does not exist (file size: 3671)...
# Saved resume file stop_offset 3671 to /tmp/slow.pos
# 240ms user time, 30ms system time, 41.45M rss, 127.38M vsz
# Current date: Sat Aug 14 02:44:30 2021
# Hostname: ip-172-31-126-203
# Files: /var/log/mysql/slow.log
# Overall: 12 total, 4 unique, 0.05 QPS, 0.00x concurrency _______________
# Time range: 2021-08-14T02:28:53 to 2021-08-14T02:33:00
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 340ms 991us 75ms 28ms 51ms 18ms 23ms
# Lock time 15ms 101us 10ms 1ms 3ms 3ms 152us
# Rows sent 225 5 20 18.75 19.46 4.01 19.46
# Rows examine 322.49k 5 29.32k 26.87k 28.66k 7.92k 28.66k
# Query size 1011 24 357 84.25 69.19 80.56 69.19
# Profile
# Rank Query ID Response time Calls R/Call V/M
# ==== =================================== ============= ===== ====== ====
# 1 0xF8D9C0871992B1AF9C572B62B2210476 0.1742 51.2% 5 0.0348 0.01 SELECT estate
# 2 0x1592B6A6C5962A79A3BBB20FFD7BCE7C 0.1481 43.5% 5 0.0296 0.00 SELECT chair
# 3 0x3D7F9AD8D0B5ECFA387A1968AED87BF9 0.0171 5.0% 1 0.0171 0.00 SELECT estate
# MISC 0xMISC 0.0010 0.3% 1 0.0010 0.0 <1 ITEMS>
# Query 1: 0.02 QPS, 0.00x concurrency, ID 0xF8D9C0871992B1AF9C572B62B2210476 at byte 469
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2021-08-14T02:28:53 to 2021-08-14T02:33:00
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 41 5
# Exec time 51 174ms 22ms 75ms 35ms 75ms 20ms 24ms
# Lock time 70 11ms 101us 10ms 2ms 10ms 4ms 144us
# Rows sent 44 100 20 20 20 20 0 20
# Rows examine 45 146.58k 29.32k 29.32k 29.32k 29.32k 0 29.32k
# Query size 27 275 55 55 55 55 0 55
# String:
# Databases isuumo
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms ################################################################
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'\G
# SHOW CREATE TABLE `isuumo`.`estate`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM estate ORDER BY rent ASC, id ASC LIMIT 20\G
# Query 2: 0.02 QPS, 0.00x concurrency, ID 0x1592B6A6C5962A79A3BBB20FFD7BCE7C at byte 0
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2021-08-14T02:28:53 to 2021-08-14T02:33:00
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 41 5
# Exec time 43 148ms 21ms 53ms 30ms 51ms 11ms 23ms
# Lock time 26 4ms 123us 4ms 813us 3ms 1ms 131us
# Rows sent 44 100 20 20 20 20 0 20
# Rows examine 45 146.58k 29.32k 29.32k 29.32k 29.32k 0 29.32k
# Query size 35 355 71 71 71 71 0 71
# String:
# Databases isuumo
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms ################################################################
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'\G
# SHOW CREATE TABLE `isuumo`.`chair`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM chair WHERE stock > 0 ORDER BY price ASC, id ASC LIMIT 20\G
# Query 3: 0 QPS, 0x concurrency, ID 0x3D7F9AD8D0B5ECFA387A1968AED87BF9 at byte 1271
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2021-08-14T02:31:06
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 8 1
# Exec time 5 17ms 17ms 17ms 17ms 17ms 0 17ms
# Lock time 1 153us 153us 153us 153us 153us 0 153us
# Rows sent 8 20 20 20 20 20 0 20
# Rows examine 9 29.32k 29.32k 29.32k 29.32k 29.32k 0 29.32k
# Query size 35 357 357 357 357 357 0 357
# String:
# Databases isuumo
# Hosts localhost
# Users isucon
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms ################################################################
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'\G
# SHOW CREATE TABLE `isuumo`.`estate`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM estate WHERE (door_width >= '60' AND door_height >= '40') OR (door_width >= '60' AND door_height >= '157') OR (door_width >= '40' AND door_height >= '60') OR (door_width >= '40' AND door_height >= '157') OR (door_width >= '157' AND door_height >= '60') OR (door_width >= '157' AND door_height >= '40') ORDER BY popularity DESC, id ASC LIMIT 20\G
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.