search

odanado / isucon12-qualifier

1 stars 0 forks source link

過去問を解く #1

Open mizdra opened 2 years ago

mizdra commented 2 years ago

IPアドレス

リンク集

odanado commented 2 years ago

isucon11-qualify/manual.md at main · isucon/isucon11-qualify

mizdra commented 2 years ago

見れるようになった！ http://52.68.207.4:3000/

odanado commented 2 years ago

ベンチの回し方

参考: https://github.com/matsuu/aws-isucon/blob/main/isucon11-qualify/README.md#bench

cd ~/bench/
./bench -all-addresses 127.0.0.11 -target 127.0.0.11:443 -tls -jia-service-url http://127.0.0.1:4999
odanado commented 2 years ago

ログのみかた

journalctl -xef -u isucondition.nodejs.service
mizdra commented 2 years ago

ブラウザからログインする

$ ssh isucon@52.68.207.4 -L 5000:localhost:5000
$ open http://52.68.207.4:3000/
$ # ログインボタンをおす
$ # id:isucon, pass: isucon でログイン
mizdra commented 2 years ago

参考

https://zenn.dev/dyoshikawa/scraps/f43611f37cf6d4

odanado commented 2 years ago

サーバで git init していい感じに .gitignore を整理する

git commit -m "init" 時に設定する必要があるメールアドレスは次の通り

git config --global user.name "yuyushiki"
git config --global user.email "yuyushiki@example.com"
odanado commented 2 years ago

過去問リポジトリ https://github.com/odanado/isucon11-qualifier-with-yuyushiki

odanado commented 2 years ago

Cloud Trace を試してみる Node.js and OpenTelemetry  |  Cloud Trace  |  Google Cloud

mizdra commented 2 years ago

alp でアクセスログ分析

brew install alp
$ cat access.log | alp ltsv -r --sort=sum -m "/api/condition/.+,/api/isu/.*,/isu/.*"
+-------+-----+-------+-----+------+-----+--------+------------------------------+-------+-------+----------+-------+-------+-------+-------+--------+-----------+------------+-------------+------------+
| COUNT | 1XX |  2XX  | 3XX | 4XX  | 5XX | METHOD |             URI              |  MIN  |  MAX  |   SUM    |  AVG  |  P90  |  P95  |  P99  | STDDEV | MIN(BODY) | MAX(BODY)  |  SUM(BODY)  | AVG(BODY)  |
+-------+-----+-------+-----+------+-----+--------+------------------------------+-------+-------+----------+-------+-------+-------+-------+--------+-----------+------------+-------------+------------+
| 60688 |   0 | 54747 |   0 | 5941 |   0 | POST   | /api/condition/.+            | 0.004 | 0.132 | 2114.512 | 0.035 | 0.088 | 0.100 | 0.104 |  0.025 |     0.000 |     14.000 |      70.000 |      0.001 |
|   218 |   0 |    44 |   0 |  174 |   0 | POST   | /api/isu                     | 0.004 | 1.016 |  178.424 | 0.818 | 1.004 | 1.004 | 1.008 |  0.345 |     0.000 |    145.000 |    5370.000 |     24.633 |
|   182 |   0 |    13 |   0 |  169 |   0 | GET    | /api/isu                     | 0.024 | 1.012 |  167.436 | 0.920 | 1.004 | 1.004 | 1.012 |  0.262 |     0.000 |   2478.000 |    6096.000 |     33.495 |
|   209 |   0 |   125 |   0 |   84 |   0 | GET    | /api/isu/.*                  | 0.004 | 1.012 |   80.668 | 0.386 | 1.004 | 1.004 | 1.008 |  0.440 |     0.000 | 135259.000 | 1415692.000 |   6773.646 |
|    68 |   0 |    16 |   0 |   52 |   0 | POST   | /api/auth                    | 0.016 | 1.012 |   36.696 | 0.540 | 1.004 | 1.004 | 1.012 |  0.490 |     0.000 |     19.000 |     184.000 |      2.706 |
|    29 |   0 |    20 |   0 |    9 |   0 | GET    | /api/condition/.+            | 0.004 | 1.004 |    2.776 | 0.096 | 0.720 | 1.000 | 1.004 |  0.279 |     0.000 |   5431.000 |   39977.000 |   1378.517 |
|     9 |   0 |     9 |   0 |    0 |   0 | GET    | /api/trend                   | 0.032 | 0.244 |    1.448 | 0.161 | 0.244 | 0.244 | 0.244 |  0.070 |  2661.000 |   2661.000 |   23949.000 |   2661.000 |
|    48 |   0 |    12 |  36 |    0 |   0 | GET    | /assets/vendor.ee7444dd.js   | 0.004 | 0.044 |    0.420 | 0.009 | 0.040 | 0.040 | 0.044 |  0.013 |     0.000 | 743417.000 | 8921004.000 | 185854.250 |
|    48 |   0 |    12 |  36 |    0 |   0 | GET    | /assets/favicon.d0f5f504.svg | 0.004 | 0.028 |    0.260 | 0.005 | 0.020 | 0.028 | 0.028 |  0.008 |     0.000 |    592.000 |    7104.000 |    148.000 |
|    48 |   0 |    12 |  36 |    0 |   0 | GET    | /assets/index.144d8ca8.css   | 0.004 | 0.028 |    0.256 | 0.005 | 0.020 | 0.024 | 0.028 |  0.007 |     0.000 |  19066.000 |  228792.000 |   4766.500 |
|     1 |   0 |     1 |   0 |    0 |   0 | POST   | /initialize                  | 0.256 | 0.256 |    0.256 | 0.256 | 0.256 | 0.256 | 0.256 |  0.000 |    21.000 |     21.000 |      21.000 |     21.000 |
|    48 |   0 |    12 |  36 |    0 |   0 | GET    | /assets/index.23dac98b.js    | 0.004 | 0.028 |    0.252 | 0.005 | 0.020 | 0.020 | 0.028 |  0.007 |     0.000 |  26667.000 |  320004.000 |   6666.750 |
|    26 |   0 |    22 |   4 |    0 |   0 | GET    | /                            | 0.012 | 0.016 |    0.232 | 0.009 | 0.016 | 0.016 | 0.016 |  0.006 |     0.000 |    528.000 |   11616.000 |    446.769 |
|    48 |   0 |    12 |  36 |    0 |   0 | GET    | /assets/logo_white.svg       | 0.012 | 0.020 |    0.208 | 0.004 | 0.016 | 0.020 | 0.020 |  0.006 |     0.000 |   3285.000 |   39420.000 |    821.250 |
|    11 |   0 |    11 |   0 |    0 |   0 | GET    | /assets/logo_orange.svg      | 0.016 | 0.016 |    0.128 | 0.012 | 0.016 | 0.016 | 0.016 |  0.006 |  3288.000 |   3288.000 |   36168.000 |   3288.000 |
|    12 |   0 |    10 |   0 |    2 |   0 | GET    | /api/user/me                 | 0.012 | 0.020 |    0.128 | 0.011 | 0.020 | 0.020 | 0.020 |  0.007 |    21.000 |     37.000 |     358.000 |     29.833 |
|     6 |   0 |     2 |   0 |    4 |   0 | POST   | /api/signout                 | 0.004 | 0.032 |    0.044 | 0.007 | 0.032 | 0.032 | 0.032 |  0.011 |    21.000 |     21.000 |      84.000 |     14.000 |
|    30 |   0 |    18 |  12 |    0 |   0 | GET    | /isu/.*                      | 0.000 | 0.004 |    0.032 | 0.001 | 0.004 | 0.004 | 0.004 |  0.002 |     0.000 |    528.000 |    9504.000 |    316.800 |
|     3 |   0 |     1 |   2 |    0 |   0 | GET    | /register                    | 0.000 | 0.000 |    0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |    528.000 |     528.000 |    176.000 |
+-------+-----+-------+-----+------+-----+--------+------------------------------+-------+-------+----------+-------+-------+-------+-------+--------+-----------+------------+-------------+------------+
odanado commented 2 years ago

https://cloud.google.com/trace/docs/setup/nodejs

odanado commented 2 years ago 
@google-cloud/trace-agent DEBUG TraceWriter#getHostname: Encountered an error while retrieving GCE hostname from the GCP metadata service (metadata.google.internal): FetchError: request to http://169.254.169.254/computeMetadata/v1/instance/hostname failed, reason: connect EHOSTDOWN 169.254.169.254:80 - Local (192.168.11.3:62483)
@google-cloud/trace-agent DEBUG TraceWriter#getInstanceId: Encountered an error while retrieving GCE instance ID from the GCP metadata service (metadata.google.internal): FetchError: request to http://169.254.169.254/computeMetadata/v1/instance/id failed, reason: connect EHOSTDOWN 169.254.169.254:80 - Local (192.168.11.3:62484)
mizdra commented 2 years ago

slow query を取る

$ sudo apt-get install percona-toolkit
$ ./scripts/bench.sh
$ pt-query-digest logs/mariadb-slow.log
$ pt-query-digest logs/mariadb-slow.log

# 230ms user time, 0 system time, 32.39M rss, 39.49M vsz
# Current date: Sun Jun 19 18:32:32 2022
# Hostname: ip-172-31-11-189
# Files: logs/mariadb-slow.log
# Overall: 160 total, 5 unique, 0.11 QPS, 0.02x concurrency ______________
# Time range: 2022-06-19 18:04:04 to 18:29:17
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            23s   100ms   271ms   143ms   219ms    35ms   134ms
# Lock time           14ms       0     3ms    86us   366us   284us    33us
# Rows sent            130       0       1    0.81    0.99    0.39    0.99
# Rows examine       6.73M       0  57.91k  43.06k  54.03k  20.70k  51.46k
# Rows affecte          36       0      28    0.23    0.99    2.16       0
# Bytes sent        98.00k      11     782  627.20  755.64  289.00  755.64
# Query size       808.89k       6 790.36k   5.06k  246.02  59.35k  124.25

# Profile
# Rank Query ID                        Response time Calls R/Call V/M   It
# ==== =============================== ============= ===== ====== ===== ==
#    1 0x8155B89FFD74A9D523D19AC409... 18.3341 80.2%   130 0.1410  0.00 SELECT isu_condition
#    2 0xFFFCA4D67EA0A788813031B8BB...  3.4867 15.2%    20 0.1743  0.02 COMMIT
# MISC 0xMISC                           1.0526  4.6%    10 0.1053   0.0 <3 ITEMS>

# Query 1: 0.09 QPS, 0.01x concurrency, ID 0x8155B89FFD74A9D523D19AC409FD97AF at byte 13046
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-06-19 18:04:05 to 18:29:17
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         81     130
# Exec time     80     18s   100ms   205ms   141ms   180ms    25ms   134ms
# Lock time     96    13ms    23us     3ms   103us   366us   312us    35us
# Rows sent    100     130       1       1       1       1       0       1
# Rows examine 100   6.73M   4.38k  57.91k  52.99k  54.03k   4.95k  51.46k
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent    99  97.62k     743     782  768.97  755.64   10.01  755.64
# Query size     1  15.87k     125     125     125     125       0     125
# String:
# Databases    isucondition
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucondition` LIKE 'isu_condition'\G
#    SHOW CREATE TABLE `isucondition`.`isu_condition`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = 'bb76011b-add2-4428-98d3-e0ef0b79bf46' ORDER BY `timestamp` DESC LIMIT 1\G

# Query 2: 20 QPS, 3.49x concurrency, ID 0xFFFCA4D67EA0A788813031B8BBC3B329 at byte 1946
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2022-06-19 18:04:04 to 18:04:05
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         12      20
# Exec time     15      3s   107ms   271ms   174ms   241ms    63ms   208ms
# 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
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent     0     220      11      11      11      11       0      11
# Query size     0     120       6       6       6       6       0       6
# String:
# Databases    isucondition
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
COMMIT\G
odanado commented 2 years ago 
@google-cloud/trace-agent DEBUG StackdriverTracer#start: Tracing might not work as the following modules were loaded before the trace agent was initialized: [@google-cloud/paginator, @google-cloud/projectify, @google-cloud/promisify, @google-cloud/storage, @sindresorhus/is, @szmarczak/http-timer, abort-controller, accepts, array-flatten, arrify, async-retry, base64-js, bignumber.js, body-parser, buffer-equal-constant-time, bytes, cacheable-request, clone-response, compressible, content-disposition, content-type, cookie, cookie-signature, debug, decompress-response, defer-to-connect, depd, destroy, duplexer3, duplexify, ecdsa-sig-formatter, ee-first, encodeurl, end-of-stream, ent, escape-html, etag, event-target-shim, express, extend, finalhandler, forwarded, fresh, gaxios, gcp-metadata, get-stream, google-auth-library, got, gtoken, has-flag, http-cache-semantics, http-errors, iconv-lite, inherits, ipaddr.js, is-stream, json-bigint, json-buffer, jwa, jws, keyv, lowercase-keys, lru-cache, media-typer, merge-descriptors, methods, mime, mime-db, mime-types, mimic-response, ms, negotiator, node-fetch, normalize-url, on-finished, once, p-cancelable, p-limit, parseurl, path-to-regexp, prepend-http, proxy-addr, pump, pumpify, qs, range-parser, raw-body, readable-stream, responselike, retry, retry-request, safe-buffer, safer-buffer, send, serve-static, setprototypeof, statuses, stream-events, stream-shift, stubs, supports-color, teeny-request, to-readable-stream, toidentifier, tr46, type-is, unpipe, url-parse-lax, util-deprecate, utils-merge, uuid, vary, webidl-conversions, whatwg-url, wrappy, yallist, yocto-queue]

ログにうまく動いてないかもって出てたわ...

mizdra commented 2 years ago

https://yosuke-furukawa.hatenablog.com/entry/2017/12/05/125517

odanado commented 2 years ago

ISUCON8に出て予選に落ちた #isucon - はらへり日記

pm2 あり

odanado commented 2 years ago

Cloud Trace で MySQL がいい感じに表示されてない理由わかった(ref: https://github.com/odanado/isucon11-qualifier-with-yuyushiki/pull/2)

1系しかサポートしてないのが原因だった(isucon11 予選のそれは 2.3.0) https://github.com/googleapis/cloud-trace-nodejs/blob/6f118e9b7fe2194d938bfb3a6e1a8aa763a77b3b/src/plugins/plugin-mysql2.ts#L17

mizdra commented 2 years ago

1系なるほど…

odanado commented 2 years ago

Cloud Monitoring を入れてみたけど動かない...

トラブルシューティングに従って sudo cat /var/log/syslog | grep collectd を実行すると、動いてそうな雰囲気を感じるけどだめ

Jun 19 23:02:49 ip-172-31-5-227 collectd[14938]: plugin_load: plugin "stackdriver_agent" successfully loaded.
Jun 19 23:02:49 ip-172-31-5-227 collectd[14938]: plugin_load: plugin "exec" successfully loaded.
Jun 19 23:02:49 ip-172-31-5-227 collectd[14938]: plugin_load: plugin "aggregation" successfully loaded.
Jun 19 23:02:49 ip-172-31-5-227 collectd[14938]: write_gcm: json credentials parsed successfully. email=isucon-yuyushiki@isucon-yuyushiki.iam.gserviceaccount.com, project=isucon-yuyushiki
Jun 19 23:02:49 ip-172-31-5-227 collectd[14938]: Initialization complete, entering read-loop.
Jun 19 23:02:49 ip-172-31-5-227 collectd[14938]: tcpconns plugin: Reading from netlink succeeded. Will use the netlink method from now on.

わからん 😇

odanado commented 2 years ago

Ubuntu 22.04 だと stackdriver-agent がインストールできないかも...？

Err:5 https://packages.cloud.google.com/apt google-cloud-monitoring-jammy-all Release
  404  Not Found [IP: 172.217.31.142 443]
Hit:6 http://security.ubuntu.com/ubuntu jammy-security InRelease             
Reading package lists... Done                          
E: The repository 'https://packages.cloud.google.com/apt google-cloud-monitoring-jammy-all Release' does not have a Release file.
N: Updating from such a repository can't be done securely, and is therefore disabled by default.
N: See apt-secure(8) manpage for repository creation and user configuration details.

https://cloud.google.com/monitoring/agent/monitoring?hl=ja#linux_operating_systems ではサポートしているって書いてるんだけどなあ...