過去問を解く #1

mizdra · 2022-06-19T06:01:31Z

IPアドレス

1台目: 52.68.207.4 (for @odanado )
2台目: 13.231.217.189 (for @mizdra )

リンク集

odanado · 2022-06-19T06:07:57Z

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

mizdra · 2022-06-19T06:14:42Z

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

odanado · 2022-06-19T06:22:23Z

ベンチの回し方

参考: 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 · 2022-06-19T06:26:04Z

ログのみかた

journalctl -xef -u isucondition.nodejs.service

mizdra · 2022-06-19T06:38:38Z

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

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

mizdra · 2022-06-19T06:51:33Z

参考

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

odanado · 2022-06-19T07:09:59Z

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

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

git config --global user.name "yuyushiki"
git config --global user.email "yuyushiki@example.com"

odanado · 2022-06-19T07:24:56Z

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

odanado · 2022-06-19T07:48:39Z

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

mizdra · 2022-06-19T08:30:53Z

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 · 2022-06-19T09:14:41Z

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

odanado · 2022-06-19T09:18:50Z

@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 · 2022-06-19T09:33:05Z

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 · 2022-06-19T09:40:57Z

@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 · 2022-06-19T09:50:55Z

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

odanado · 2022-06-19T09:52:00Z

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

pm2 あり

odanado · 2022-06-19T10:07:52Z

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

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

mizdra · 2022-06-19T10:16:12Z

1系なるほど…

odanado · 2022-06-19T14:05:12Z

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

agent のインストールは https://cloud.google.com/monitoring/agent/monitoring/installation?hl=ja を参考にした
クレデンシャルの設定は https://cloud.google.com/monitoring/agent/monitoring/authorization?hl=ja を参考にした

トラブルシューティングに従って 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 · 2022-07-09T09:05:12Z

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 ではサポートしているって書いてるんだけどなあ...

mizdra mentioned this issue Jul 9, 2022

VSCode Remote Debug 導入してみる #4

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

過去問を解く #1

過去問を解く #1

mizdra commented Jun 19, 2022 •

edited

odanado commented Jun 19, 2022

mizdra commented Jun 19, 2022

odanado commented Jun 19, 2022 •

edited

odanado commented Jun 19, 2022

mizdra commented Jun 19, 2022

mizdra commented Jun 19, 2022

odanado commented Jun 19, 2022

odanado commented Jun 19, 2022

odanado commented Jun 19, 2022

mizdra commented Jun 19, 2022 •

edited

odanado commented Jun 19, 2022

odanado commented Jun 19, 2022

mizdra commented Jun 19, 2022

odanado commented Jun 19, 2022

mizdra commented Jun 19, 2022

odanado commented Jun 19, 2022

odanado commented Jun 19, 2022

mizdra commented Jun 19, 2022

odanado commented Jun 19, 2022

odanado commented Jul 9, 2022

過去問を解く #1

過去問を解く #1

Comments

mizdra commented Jun 19, 2022 • edited

IPアドレス

リンク集

odanado commented Jun 19, 2022

mizdra commented Jun 19, 2022

odanado commented Jun 19, 2022 • edited

ベンチの回し方

odanado commented Jun 19, 2022

ログのみかた

mizdra commented Jun 19, 2022

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

mizdra commented Jun 19, 2022

参考

odanado commented Jun 19, 2022

odanado commented Jun 19, 2022

odanado commented Jun 19, 2022

mizdra commented Jun 19, 2022 • edited

alp でアクセスログ分析

odanado commented Jun 19, 2022

odanado commented Jun 19, 2022

mizdra commented Jun 19, 2022

slow query を取る

odanado commented Jun 19, 2022

mizdra commented Jun 19, 2022

odanado commented Jun 19, 2022

odanado commented Jun 19, 2022

mizdra commented Jun 19, 2022

odanado commented Jun 19, 2022

odanado commented Jul 9, 2022

mizdra commented Jun 19, 2022 •

edited

odanado commented Jun 19, 2022 •

edited

mizdra commented Jun 19, 2022 •

edited