Skip to content

Commit ba5c797

Browse files
matsuumatsuu
committed
3643 nginxチューニングとトップのmysqlクエリーを解消
1 parent 23d058f commit ba5c797

10 files changed

Lines changed: 274 additions & 252 deletions

File tree

‎etc/nginx/nginx.conf‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
user www-data;
22
worker_processes auto;
33
pid /run/nginx.pid;
4-
error_log /var/log/nginx/error.log notice;
4+
error_log /var/log/nginx/error.log info;
55
#include /etc/nginx/modules-enabled/*.conf;
66

77
load_module modules/ngx_http_js_module.so;
@@ -44,14 +44,16 @@ http {
4444
# Logging Settings
4545
##
4646

47-
access_log /var/log/nginx/access.log;
47+
# access_log /var/log/nginx/access.log;
4848

4949
##
5050
# Gzip Settings
5151
##
5252

5353
gzip on;
5454

55+
keepalive_requests 1000000;
56+
5557
# gzip_vary on;
5658
# gzip_proxied any;
5759
# gzip_comp_level 6;

‎etc/nginx/sites-available/isuride.conf‎

Lines changed: 12 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -25,8 +25,14 @@ server {
2525
}
2626
}
2727

28+
upstream app {
29+
server 127.0.0.1:8080;
30+
keepalive_requests 10000;
31+
}
32+
2833
server {
2934
listen 443 ssl;
35+
http2 on;
3036
server_name xiv.isucon.net;
3137
server_name *.xiv.isucon.net;
3238

@@ -43,14 +49,18 @@ server {
4349
}
4450
location /api/ {
4551
proxy_set_header Host $host;
46-
proxy_pass http://localhost:8080;
52+
proxy_set_header Connection "";
53+
proxy_http_version 1.1;
54+
proxy_pass http://app;
4755
}
4856

4957
location /api/internal/ {
5058
# localhostからのみアクセスを許可
5159
allow 127.0.0.1;
5260
deny all;
5361
proxy_set_header Host $host;
54-
proxy_pass http://localhost:8080;
62+
proxy_set_header Connection "";
63+
proxy_http_version 1.1;
64+
proxy_pass http://app;
5565
}
5666
}

‎home/isucon/webapp/go/chair_handlers.go‎

Lines changed: 11 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,7 @@ import (
55
"errors"
66
"fmt"
77
"net/http"
8+
"time"
89

910
"github.com/oklog/ulid/v2"
1011
)
@@ -111,18 +112,23 @@ func chairPostCoordinate(w http.ResponseWriter, r *http.Request) {
111112
}
112113
defer tx.Rollback()
113114

115+
createdAt := time.Now()
116+
114117
chairLocationID := ulid.Make().String()
115118
if _, err := tx.ExecContext(
116119
ctx,
117-
`INSERT INTO chair_locations (id, chair_id, latitude, longitude) VALUES (?, ?, ?, ?)`,
118-
chairLocationID, chair.ID, req.Latitude, req.Longitude,
120+
`INSERT INTO chair_locations (id, chair_id, latitude, longitude, created_at) VALUES (?, ?, ?, ?, ?)`,
121+
chairLocationID, chair.ID, req.Latitude, req.Longitude, createdAt,
119122
); err != nil {
120123
writeError(w, http.StatusInternalServerError, err)
121124
return
122125
}
123126

124-
location := &ChairLocation{}
125-
if err := tx.GetContext(ctx, location, `SELECT * FROM chair_locations WHERE id = ?`, chairLocationID); err != nil {
127+
if _, err := tx.ExecContext(
128+
ctx,
129+
`UPDATE chairs SET total_distance = total_distance + (SELECT IFNULL(ABS(latitude - LAG(latitude) OVER (PARTITION BY chair_id ORDER BY created_at)) + ABS(longitude - LAG(longitude) OVER (PARTITION BY chair_id ORDER BY created_at)),0) AS distance FROM chair_locations WHERE chair_id = ? ORDER BY created_at DESC LIMIT 1), total_distance_updated_at = ? WHERE id = ?`,
130+
chair.ID, createdAt, chair.ID,
131+
); err != nil {
126132
writeError(w, http.StatusInternalServerError, err)
127133
return
128134
}
@@ -162,7 +168,7 @@ func chairPostCoordinate(w http.ResponseWriter, r *http.Request) {
162168
}
163169

164170
writeJSON(w, http.StatusOK, &chairPostCoordinateResponse{
165-
RecordedAt: location.CreatedAt.UnixMilli(),
171+
RecordedAt: createdAt.UnixMilli(),
166172
})
167173
}
168174

‎home/isucon/webapp/go/main.go‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -67,7 +67,7 @@ func setup() http.Handler {
6767
db = _db
6868

6969
mux := chi.NewRouter()
70-
mux.Use(middleware.Logger)
70+
// mux.Use(middleware.Logger)
7171
mux.Use(middleware.Recoverer)
7272
mux.HandleFunc("POST /api/initialize", postInitialize)
7373

‎home/isucon/webapp/go/owner_handlers.go‎

Lines changed: 2 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -203,19 +203,9 @@ func ownerGetChairs(w http.ResponseWriter, r *http.Request) {
203203
is_active,
204204
created_at,
205205
updated_at,
206-
IFNULL(total_distance, 0) AS total_distance,
206+
total_distance,
207207
total_distance_updated_at
208-
FROM chairs
209-
LEFT JOIN (SELECT chair_id,
210-
SUM(IFNULL(distance, 0)) AS total_distance,
211-
MAX(created_at) AS total_distance_updated_at
212-
FROM (SELECT chair_id,
213-
created_at,
214-
ABS(latitude - LAG(latitude) OVER (PARTITION BY chair_id ORDER BY created_at)) +
215-
ABS(longitude - LAG(longitude) OVER (PARTITION BY chair_id ORDER BY created_at)) AS distance
216-
FROM chair_locations) tmp
217-
GROUP BY chair_id) distance_table ON distance_table.chair_id = chairs.id
218-
WHERE owner_id = ?
208+
FROM chairs WHERE owner_id = ?
219209
`, owner.ID); err != nil {
220210
writeError(w, http.StatusInternalServerError, err)
221211
return

‎home/isucon/webapp/sql/1-schema.sql‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@ CREATE TABLE chairs
3636
)
3737
COMMENT = '椅子情報テーブル';
3838
ALTER TABLE chairs ADD INDEX (owner_id);
39+
ALTER TABLE chairs ADD INDEX (access_token);
3940

4041
DROP TABLE IF EXISTS chair_locations;
4142
CREATE TABLE chair_locations

‎home/isucon/webapp/sql/4-change.sql‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,8 @@
1+
SET CHARACTER_SET_CLIENT = utf8mb4;
2+
SET CHARACTER_SET_CONNECTION = utf8mb4;
3+
4+
USE isuride;
5+
6+
ALTER TABLE chairs ADD COLUMN total_distance INTEGER NOT NULL DEFAULT 0 INVISIBLE;
7+
ALTER TABLE chairs ADD COLUMN total_distance_updated_at DATETIME(6) INVISIBLE;
8+
UPDATE chairs, (SELECT chair_id, SUM(IFNULL(distance, 0)) AS total_distance, MAX(created_at) AS total_distance_updated_at FROM (SELECT chair_id, created_at, ABS(latitude - LAG(latitude) OVER (PARTITION BY chair_id ORDER BY created_at)) + ABS(longitude - LAG(longitude) OVER (PARTITION BY chair_id ORDER BY created_at)) AS distance FROM chair_locations) tmp GROUP BY chair_id) AS d SET chairs.total_distance = d.total_distance, chairs.total_distance_updated_at = d.total_distance_updated_at WHERE chairs.id = d.chair_id;

‎home/isucon/webapp/sql/init.sh‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,3 +35,11 @@ gzip -dkc 3-initial-data.sql.gz | mysql -u"$ISUCON_DB_USER" \
3535
--host "$ISUCON_DB_HOST" \
3636
--port "$ISUCON_DB_PORT" \
3737
"$ISUCON_DB_NAME"
38+
39+
mysql -u"$ISUCON_DB_USER" \
40+
-p"$ISUCON_DB_PASSWORD" \
41+
--host "$ISUCON_DB_HOST" \
42+
--port "$ISUCON_DB_PORT" \
43+
"$ISUCON_DB_NAME" < 4-change.sql
44+
45+

0 commit comments

Comments
 (0)