search

okashoi / isucon13-practice-20241102

0 stars 0 forks source link

やる #1

Open okashoi opened 6 hours ago

okashoi commented 6 hours ago

ISUCON 13 member: @pinkumohikan, @mism-mism, @okashoi

documents

サーバ構成

i-000a042d29ddd9e6a     54.92.43.36     isu13-3
i-0666ef5bde4748ba2     57.180.110.198  isu13-2
i-070bd23e0f3ea3373     54.178.253.85   isu13-bench
i-0afe8e2b3fac87faf     54.95.217.19    isu13-1

メモ

pinkumohikan commented 6 hours ago

マニュアル

https://github.com/KOBA789/isunarabe-images/blob/19e69b2caec53f6948c4681d1f496419119d196a/isu13/manual.md

mism-mism commented 6 hours ago

image

pinkumohikan commented 5 hours ago

Go実装への切り替え

初期状態でGo実装だった

isucon@ip-192-168-0-11:~$ sudo systemctl status isu*
● isupipe-go.service - isupipe-go
     Loaded: loaded (/etc/systemd/system/isupipe-go.service; enabled; vendor preset: enabled)
     Active: active (running) since Sat 2024-11-02 01:18:05 UTC; 19min ago
   Main PID: 848 (isupipe)
      Tasks: 5 (limit: 4416)
     Memory: 7.3M
        CPU: 8ms
     CGroup: /system.slice/isupipe-go.service
             └─848 /home/isucon/webapp/go/isupipe

Nov 02 01:18:05 ip-192-168-0-11 systemd[1]: Started isupipe-go.
Nov 02 01:18:06 ip-192-168-0-11 isupipe[848]:    ____    __
Nov 02 01:18:06 ip-192-168-0-11 isupipe[848]:   / __/___/ /  ___
Nov 02 01:18:06 ip-192-168-0-11 isupipe[848]:  / _// __/ _ \/ _ \
Nov 02 01:18:06 ip-192-168-0-11 isupipe[848]: /___/\__/_//_/\___/ v4.11.1
Nov 02 01:18:06 ip-192-168-0-11 isupipe[848]: High performance, minimalist Go web framework
Nov 02 01:18:06 ip-192-168-0-11 isupipe[848]: https://echo.labstack.com
Nov 02 01:18:06 ip-192-168-0-11 isupipe[848]: ____________________________________O/_______
Nov 02 01:18:06 ip-192-168-0-11 isupipe[848]:                                     O\
Nov 02 01:18:06 ip-192-168-0-11 isupipe[848]: ⇨ http server started on [::]:8080
mism-mism commented 5 hours ago 
2024-11-02T01:28:23.288Z    warn    isupipe-benchmarker POST /api/initialize のリクエストが失敗しました  {"error": "Post \"https://pipe.t.isucon.pw:443/api/initialize\": tls: failed to verify certificate: x509: certificate has expired or is not yet valid: current time 2024-11-02T01:28:23Z is after 2024-03-01T12:41:54Z"}
{"pass":false,"score":0,"messages":["初期化が失敗しました","initializeのリクエストに失敗しました Post \"https://pipe.t.isucon.pw:443/api/initialize\": tls: failed to verify certificate: x509: certificate has expired or is not yet valid: current time 2024-11-02T01:28:23Z is after **2024-03-01T12:41:54Z"],"language":"unknown","resolved_count":0}**

証明書エラー

以下から取ってくる

以下実行

 # cert.pemを置き換え
sudo wget -O /etc/nginx/tls/_.t.isucon.pw.crt https://github.com/KOBA789/t.isucon.pw/releases/download/20241002134233/fullchain.pem
 # key.pemを置き換え
sudo wget -O /etc/nginx/tls/_.t.isucon.pw.key https://github.com/KOBA789/t.isucon.pw/releases/download/20241002134233/key.pem
 # nginxをreload
sudo systemctl reload nginx
mism-mism commented 5 hours ago

初期スコア

3411
pinkumohikan commented 5 hours ago

slow queryチューニング

mysql> explain SELECT * FROM livestream_tags WHERE livestream_id = 7501;
+----+-------------+-----------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | livestream_tags | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 11524 |    10.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> ALTER TABLE livestream_tags ADD INDEX (livestream_id);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT * FROM livestream_tags WHERE livestream_id = 7501;
+----+-------------+-----------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table           | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | livestream_tags | NULL       | ref  | livestream_id | livestream_id | 8       | const |    2 |   100.00 | NULL  |
+----+-------------+-----------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT IFNULL(SUM(l2.tip), 0) FROM users u
    ->         INNER JOIN livestreams l ON l.user_id = u.id
    ->         INNER JOIN livecomments l2 ON l2.livestream_id = l.id
    ->         WHERE u.id = 222;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                      | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | const  | PRIMARY       | PRIMARY | 8       | const                    |    1 |   100.00 | Using index |
|  1 | SIMPLE      | l2    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                     | 1200 |   100.00 | NULL        |
|  1 | SIMPLE      | l     | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | isupipe.l2.livestream_id |    1 |    10.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

mysql> ALTER TABLE livecomments ADD INDEX (livestream_id, tip);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT IFNULL(SUM(l2.tip), 0) FROM users u         INNER JOIN livestreams l ON l.user_id = u.id         INNER JOIN livecomments l2 ON l2.livestream_id = l.id         WHERE u.id = 222;
+----+-------------+-------+------------+--------+---------------+---------------+---------+--------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key           | key_len | ref                      | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------------+---------+--------------------------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | const  | PRIMARY       | PRIMARY       | 8       | const                    |    1 |   100.00 | Using index |
|  1 | SIMPLE      | l2    | NULL       | index  | livestream_id | livestream_id | 16      | NULL                     | 1200 |   100.00 | Using index |
|  1 | SIMPLE      | l     | NULL       | eq_ref | PRIMARY       | PRIMARY       | 8       | isupipe.l2.livestream_id |    1 |    10.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------------+---------+--------------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
mysql> explain SELECT COUNT(*) FROM users u
    ->         INNER JOIN livestreams l ON l.user_id = u.id
    ->         INNER JOIN reactions r ON r.livestream_id = l.id
    ->         WHERE u.id = 274;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | const  | PRIMARY       | PRIMARY | 8       | const                   |    1 |   100.00 | Using index |
|  1 | SIMPLE      | r     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                    | 1181 |   100.00 | NULL        |
|  1 | SIMPLE      | l     | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | isupipe.r.livestream_id |    1 |    10.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

mysql> ALTER TABLE reactions ADD INDEX (livestream_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT COUNT(*) FROM users u         INNER JOIN livestreams l ON l.user_id = u.id         INNER JOIN reactions r ON r.livestream_id = l.id         WHERE u.id = 274;
+----+-------------+-------+------------+--------+---------------+---------------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key           | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | const  | PRIMARY       | PRIMARY       | 8       | const                   |    1 |   100.00 | Using index |
|  1 | SIMPLE      | r     | NULL       | index  | livestream_id | livestream_id | 8       | NULL                    | 1181 |   100.00 | Using index |
|  1 | SIMPLE      | l     | NULL       | eq_ref | PRIMARY       | PRIMARY       | 8       | isupipe.r.livestream_id |    1 |    10.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------------+---------+-------------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
mysql> explain SELECT slot FROM reservation_slots WHERE start_at = 1701205200 AND end_at = 1701208800;
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | reservation_slots | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 8593 |     1.00 | Using where |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> ALTER TABLE reservation_slots ADD INDEX (start_at, end_at);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT slot FROM reservation_slots WHERE start_at = 1701205200 AND end_at = 1701208800;
+----+-------------+-------------------+------------+------+---------------+----------+---------+-------------+------+----------+-------+
| id | select_type | table             | partitions | type | possible_keys | key      | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+----------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | reservation_slots | NULL       | ref  | start_at      | start_at | 16      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------------------+------------+------+---------------+----------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT * FROM themes WHERE user_id = 1015;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | themes | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1241 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> ALTER TABLE themes ADD INDEX (user_id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT * FROM themes WHERE user_id = 1015;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | themes | NULL       | ref  | user_id       | user_id | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

https://isunarabe.org/teams/882/benchmark_jobs/13808 Score: 6462

mism-mism commented 5 hours ago 
Top 20 Sort By Total
Count  Total    Mean  Stddev    Min  P50.0  P90.0  P95.0  P99.0    Max  2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
    1  4.229  4.2290  0.0000  4.229  4.229  4.229  4.229  4.229  4.229    1    0    0    0         100        100        100        100  GET /api/livestream/7496/statistics HTTP/1.1
    1  3.069  3.0690  0.0000  3.069  3.069  3.069  3.069  3.069  3.069    1    0    0    0         140        140        140        140  GET /api/user/suzukitsubasa0/statistics HTTP/1.1
   21  0.456  0.0217  0.0067  0.016  0.020  0.026  0.027  0.050  0.050   21    0    0    0       21051        962       1002       1477  POST /api/livestream/reservation HTTP/1.1
    1  0.199  0.1990  0.0000  0.199  0.199  0.199  0.199  0.199  0.199    1    0    0    0      121120     121120     121120     121120  GET /api/livestream/search?tag=%E6%A4%85%E5%AD%90 HTTP/1.1
    1  0.196  0.1960  0.0000  0.196  0.196  0.196  0.196  0.196  0.196    1    0    0    0      130277     130277     130277     130277  GET /api/livestream/search?tag=%E3%82%AD%E3%83%A3%E3%83%B3%E3%83%97 HTTP/1.1
    1  0.196  0.1960  0.0000  0.196  0.196  0.196  0.196  0.196  0.196    1    0    0    0      126633     126633     126633     126633  GET /api/livestream/search?tag=%E3%83%A9%E3%82%A4%E3%83%96%E9%85%8D%E4%BF%A1 HTTP/1.1
    1  0.178  0.1780  0.0000  0.178  0.178  0.178  0.178  0.178  0.178    1    0    0    0      122866     122866     122866     122866  GET /api/livestream/search?tag=%E6%96%B0%E6%83%85%E5%A0%B1%E7%99%BA%E8%A1%A8 HTTP/1.1
    1  0.176  0.1760  0.0000  0.176  0.176  0.176  0.176  0.176  0.176    1    0    0    0      114220     114220     114220     114220  GET /api/livestream/search?tag=%E5%91%A8%E5%B9%B4%E8%A8%98%E5%BF%B5 HTTP/1.1
    1  0.167  0.1670  0.0000  0.167  0.167  0.167  0.167  0.167  0.167    1    0    0    0      109992     109992     109992     109992  GET /api/livestream/search?tag=DIY%E9%9B%BB%E5%AD%90%E5%B7%A5%E4%BD%9C HTTP/1.1
    1  0.162  0.1620  0.0000  0.162  0.162  0.162  0.162  0.162  0.162    1    0    0    0      103816     103816     103816     103816  GET /api/livestream/search?tag=%E5%81%A5%E5%BA%B7 HTTP/1.1
    1  0.158  0.1580  0.0000  0.158  0.158  0.158  0.158  0.158  0.158    1    0    0    0      106094     106094     106094     106094  GET /api/livestream/search?tag=DIY HTTP/1.1
    1  0.143  0.1430  0.0000  0.143  0.143  0.143  0.143  0.143  0.143    1    0    0    0       98895      98895      98895      98895  GET /api/livestream/search?tag=%E3%82%AE%E3%82%BF%E3%83%BC HTTP/1.1
    2  0.120  0.0600  0.0020  0.058  0.062  0.062  0.062  0.062  0.062    2    0    0    0       98899      49449      49449      49450  GET /api/livestream/search?limit=50 HTTP/1.1
    2  0.068  0.0340  0.0020  0.032  0.036  0.036  0.036  0.036  0.036    2    0    0    0         715        235        357        480  POST /api/register HTTP/1.1
    1  0.014  0.0140  0.0000  0.014  0.014  0.014  0.014  0.014  0.014    1    0    0    0          14         14         14         14  POST /api/icon HTTP/1.1
    1  0.013  0.0130  0.0000  0.013  0.013  0.013  0.013  0.013  0.013    1    0    0    0        5734       5734       5734       5734  GET /api/livestream HTTP/1.1
    1  0.007  0.0070  0.0000  0.007  0.007  0.007  0.007  0.007  0.007    1    0    0    0           0          0          0          0  DELETE /api/livestream/7497/exit HTTP/1.1
    1  0.006  0.0060  0.0000  0.006  0.006  0.006  0.006  0.006  0.006    1    0    0    0           0          0          0          0  POST /api/livestream/7497/enter HTTP/1.1
    3  0.005  0.0017  0.0005  0.001  0.002  0.002  0.002  0.002  0.002    3    0    0    0           0          0          0          0  POST /api/login HTTP/1.1
mism-mism commented 5 hours ago

インフラ構成

スペック

メモリ

               total        used        free      shared  buff/cache   available
Mem:           3.6Gi       905Mi       100Mi       0.0Ki       2.6Gi       2.4Gi
Swap:             0B          0B          0B

ミドルウェア

開いているポート

COMMAND    PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
pdns_serv  849     pdns   10u  IPv4  18697      0t0  TCP localhost:36136->localhost:mysql (CLOSE_WAIT)
pdns_serv  849     pdns   13u  IPv4  40751      0t0  TCP localhost:31912->localhost:mysql (ESTABLISHED)
pdns_serv  849     pdns   20u  IPv4  40793      0t0  TCP localhost:50370->localhost:mysql (ESTABLISHED)
pdns_serv  849     pdns   22u  IPv4  40478      0t0  TCP localhost:59360->localhost:mysql (ESTABLISHED)
nginx     2387     root    6u  IPv4  25846      0t0  TCP *:http (LISTEN)
nginx     2387     root    7u  IPv4  25847      0t0  TCP *:https (LISTEN)
mysqld    2824    mysql   28u  IPv4  40794      0t0  TCP localhost:mysql->localhost:50370 (ESTABLISHED)
mysqld    2824    mysql   32u  IPv4  40442      0t0  TCP localhost:mysql (LISTEN)
mysqld    2824    mysql   45u  IPv4  40479      0t0  TCP localhost:mysql->localhost:59360 (ESTABLISHED)
mysqld    2824    mysql   49u  IPv4  40752      0t0  TCP localhost:mysql->localhost:31912 (ESTABLISHED)
mysqld    2824    mysql   53u  IPv4  87044      0t0  TCP localhost:mysql->localhost:17060 (ESTABLISHED)
mysqld    2824    mysql   58u  IPv4 108655      0t0  TCP localhost:mysql->localhost:43228 (ESTABLISHED)
isupipe   2992   isucon    3u  IPv4 107948      0t0  TCP localhost:43228->localhost:mysql (ESTABLISHED)
isupipe   2992   isucon    7u  IPv6  41001      0t0  TCP *:http-alt (LISTEN)
isupipe   2992   isucon   17u  IPv4  87043      0t0  TCP localhost:17060->localhost:mysql (ESTABLISHED)
nginx     4823 www-data    6u  IPv4  25846      0t0  TCP *:http (LISTEN)
nginx     4823 www-data    7u  IPv4  25847      0t0  TCP *:https (LISTEN)
nginx     4824 www-data    6u  IPv4  25846      0t0  TCP *:http (LISTEN)
nginx     4824 www-data    7u  IPv4  25847      0t0  TCP *:https (LISTEN)

起動中のサービス

  UNIT                                           LOAD   ACTIVE SUB     DESCRIPTION
  acpid.service                                  loaded active running ACPI event daemon
  apparmor.service                               loaded active exited  Load AppArmor profiles
  apport.service                                 loaded active exited  LSB: automatic crash report generation
  blk-availability.service                       loaded active exited  Availability of block devices
  chrony.service                                 loaded active running chrony, an NTP client/server
  cloud-config.service                           loaded active exited  Apply the settings specified in cloud-config
  cloud-final.service                            loaded active exited  Execute cloud user/final scripts
  cloud-init-local.service                       loaded active exited  Initial cloud-init job (pre-networking)
  cloud-init.service                             loaded active exited  Initial cloud-init job (metadata service crawler)
  console-setup.service                          loaded active exited  Set console font and keymap
  cron.service                                   loaded active running Regular background program processing daemon
  dbus.service                                   loaded active running D-Bus System Message Bus
  finalrd.service                                loaded active exited  Create final runtime dir for shutdown pivot root
  getty@tty1.service                             loaded active running Getty on tty1
  irqbalance.service                             loaded active running irqbalance daemon
  isunarabe-env-ipaddr.service                   loaded active exited  run isunarabe-env-ipaddr once
  isupipe-go.service                             loaded active running isupipe-go
  keyboard-setup.service                         loaded active exited  Set the console keyboard layout
  kmod-static-nodes.service                      loaded active exited  Create List of Static Device Nodes
  lvm2-monitor.service                           loaded active exited  Monitoring of LVM2 mirrors, snapshots etc. using dmeventd or progress polling
  multipathd.service                             loaded active running Device-Mapper Multipath Device Controller
  mysql.service                                  loaded active running MySQL Community Server
  networkd-dispatcher.service                    loaded active running Dispatcher daemon for systemd-networkd
  nginx.service                                  loaded active running A high performance web server and a reverse proxy server
  packagekit.service                             loaded active running PackageKit Daemon
  pdns.service                                   loaded active running PowerDNS Authoritative Server
  plymouth-quit-wait.service                     loaded active exited  Hold until boot process finishes up
  plymouth-quit.service                          loaded active exited  Terminate Plymouth Boot Screen
  plymouth-read-write.service                    loaded active exited  Tell Plymouth To Write Out Runtime Data
  polkit.service                                 loaded active running Authorization Manager
  rsyslog.service                                loaded active running System Logging Service
  serial-getty@ttyS0.service                     loaded active running Serial Getty on ttyS0
  setvtrgb.service                               loaded active exited  Set console scheme
  snap.amazon-ssm-agent.amazon-ssm-agent.service loaded active running Service for snap application amazon-ssm-agent.amazon-ssm-agent
  snapd.apparmor.service                         loaded active exited  Load AppArmor profiles managed internally by snapd
  snapd.seeded.service                           loaded active exited  Wait until snapd is fully seeded
  snapd.service                                  loaded active running Snap Daemon
  ssh.service                                    loaded active running OpenBSD Secure Shell server
  systemd-binfmt.service                         loaded active exited  Set Up Additional Binary Formats
  systemd-fsck-root.service                      loaded active exited  File System Check on Root Device
  systemd-fsck@dev-disk-by\x2dlabel-UEFI.service loaded active exited  File System Check on /dev/disk/by-label/UEFI
  systemd-journal-flush.service                  loaded active exited  Flush Journal to Persistent Storage
  systemd-journald.service                       loaded active running Journal Service
  systemd-logind.service                         loaded active running User Login Management
  systemd-modules-load.service                   loaded active exited  Load Kernel Modules
  systemd-networkd-wait-online.service           loaded active exited  Wait for Network to be Configured
  systemd-networkd.service                       loaded active running Network Configuration
  systemd-random-seed.service                    loaded active exited  Load/Save Random Seed
  systemd-remount-fs.service                     loaded active exited  Remount Root and Kernel File Systems
  systemd-resolved.service                       loaded active running Network Name Resolution
  systemd-sysctl.service                         loaded active exited  Apply Kernel Variables
  systemd-sysusers.service                       loaded active exited  Create System Users
  systemd-tmpfiles-setup-dev.service             loaded active exited  Create Static Device Nodes in /dev
  systemd-tmpfiles-setup.service                 loaded active exited  Create Volatile Files and Directories
  systemd-udev-trigger.service                   loaded active exited  Coldplug All udev Devices
  systemd-udevd.service                          loaded active running Rule-based Manager for Device Events and Files
  systemd-update-utmp.service                    loaded active exited  Record System Boot/Shutdown in UTMP
  systemd-user-sessions.service                  loaded active exited  Permit User Sessions
  ufw.service                                    loaded active exited  Uncomplicated firewall
  unattended-upgrades.service                    loaded active running Unattended Upgrades Shutdown
  user-runtime-dir@1100.service                  loaded active exited  User Runtime Directory /run/user/1100
  user@1100.service                              loaded active running User Manager for UID 1100

LOAD   = Reflects whether the unit definition was properly loaded.
ACTIVE = The high-level unit activation state, i.e. generalization of SUB.
SUB    = The low-level unit activation state, values depend on unit type.
62 loaded units listed.
To show all installed unit files use 'systemctl list-unit-files'.

PATH (バックアップ)

/home/isucon/local/python/bin:/home/isucon/local/perl/bin:/home/isucon/webapp/perl/local/bin:/home/isucon/local/ruby/bin:/home/isucon/local/php/bin:/home/isucon/local/php/sbin:/home/isucon/.cargo/bin:/home/isucon/local/node/bin:/home/isucon/local/golang/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin

mism-mism commented 5 hours ago

isudns

classDiagram
direction BT
class comments {
   int id
   int domain_id
   varchar(255) name
   varchar(10) type
   int modified_at
   varchar(40) account
   text comment
}
class cryptokeys {
   int id
   int domain_id
   int flags
   tinyint(1) active
   tinyint(1) published
   text content
}
class domainmetadata {
   int id
   int domain_id
   varchar(32) kind
   text content
}
class domains {
   int id
   varchar(255) name
   varchar(128) master
   int last_check
   varchar(8) type
   int unsigned notified_serial
   varchar(40) account
   varchar(64000) options
   varchar(255) catalog
}
class records {
   bigint id
   int domain_id
   varchar(255) name
   varchar(10) type
   varchar(64000) content
   int ttl
   int prio
   tinyint(1) disabled
   varchar(255) ordername
   tinyint(1) auth
}
class supermasters {
   varchar(64) ip
   varchar(255) nameserver
   varchar(40) account
}
class tsigkeys {
   int id
   varchar(255) name
   varchar(50) algorithm
   varchar(255) secret
}

comments --> domains : domain_id
cryptokeys --> domains : domain_id
domainmetadata --> domains : domain_id
records --> domains : domain_id

isupipe

classDiagram
direction BT
class icons {
   bigint id
   bigint user_id
   longblob image
}
class livecomment_reports {
   bigint id
   bigint user_id
   bigint livestream_id
   bigint livecomment_id
   bigint created_at
}
class livecomments {
   bigint id
   bigint user_id
   bigint livestream_id
   varchar(255) comment
   bigint tip
   bigint created_at
}
class livestream_tags {
   bigint id
   bigint livestream_id
   bigint tag_id
}
class livestream_viewers_history {
   bigint id
   bigint user_id
   bigint livestream_id
   bigint created_at
}
class livestreams {
   bigint id
   bigint user_id
   varchar(255) title
   text description
   varchar(255) playlist_url
   varchar(255) thumbnail_url
   bigint start_at
   bigint end_at
}
class ng_words {
   bigint id
   bigint user_id
   bigint livestream_id
   varchar(255) word
   bigint created_at
}
class reactions {
   bigint id
   bigint user_id
   bigint livestream_id
   varchar(255) emoji_name
   bigint created_at
}
class reservation_slots {
   bigint id
   bigint slot
   bigint start_at
   bigint end_at
}
class tags {
   bigint id
   varchar(255) name
}
class themes {
   bigint id
   bigint user_id
   tinyint(1) dark_mode
}
class users {
   bigint id
   varchar(255) name
   varchar(255) display_name
   varchar(255) password
   text description
}

icons --> users : user_id
livecomment_reports --> livecomments : livecomment_id
livecomment_reports --> livestreams : livestream_id
livecomment_reports --> users : user_id
livecomments --> livestreams : livestream_id
livecomments --> users : user_id
livestream_tags --> livestreams : livestream_id
livestream_tags --> tags : tag_id
livestream_viewers_history --> livestreams : livestream_id
livestream_viewers_history --> users : user_id
livestreams --> users : user_id
ng_words --> livestreams : livestream_id
ng_words --> users : user_id
reactions --> livestreams : livestream_id
reactions --> users : user_id
themes --> users : user_id
mism-mism commented 4 hours ago 
Top 20 Sort By Total
Count    Total     Mean   Stddev     Min   P50.0   P90.0   P95.0   P99.0     Max  2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
   48  221.572   4.6161   2.6518   0.290   4.157   5.266   8.976  17.657  17.657   44    0    4    0        1012          0         21         23  POST /api/livestream/<num>/moderate
  665  194.851   0.2930   0.1951   0.000   0.270   0.556   0.666   0.823   1.104  660    0    5    0     8946184          0      13452      44532  GET /api/livestream/<num>/livecomment
  729  180.342   0.2474   0.2005   0.000   0.211   0.518   0.623   0.812   0.922  728    0    1    0     7929062          0      10876      37491  GET /api/livestream/<num>/reaction
  122  131.185   1.0753   0.5633   0.058   1.163   1.603   2.087   2.577   2.620  119    0    3    0     8779780          0      71965     134052  GET /api/livestream/search
  730  123.471   0.1691   0.0808   0.005   0.154   0.271   0.324   0.460   0.548  723    0    7    0     1389029          0       1902       2222  POST /api/livestream/<num>/livecomment
  669  105.578   0.1578   0.0814   0.000   0.135   0.262   0.315   0.428   0.835  664    0    3    2      312117          0        466        519  POST /api/register
  342   86.274   0.2523   0.1321   0.000   0.273   0.397   0.435   0.542   0.724  334    0    8    0      385036          0       1125       1496  POST /api/livestream/reservation
  692   65.520   0.0947   0.0672   0.005   0.079   0.175   0.231   0.366   0.461  692    0    0    0     1275707       1552       1843       2239  POST /api/livestream/<num>/reaction
  678   62.992   0.0929   0.0818   0.011   0.069   0.188   0.243   0.403   0.828  658    0    1   19       13245          0         19        154  POST /api/icon
    2   37.510  18.7550   1.2430  17.512  19.998  19.998  19.998  19.998  19.998    0    0    2    0           0          0          0          0  GET /api/user/iyamamoto1/statistics
  679   37.070   0.0546   0.0655   0.001   0.032   0.126   0.183   0.339   0.460  675    0    4    0         256          0          0         64  POST /api/login
    2   27.279  13.6395   0.6135  13.026  14.253  14.253  14.253  14.253  14.253    2    0    0    0          54         27         27         27  POST /api/initialize
    1   20.001  20.0010   0.0000  20.001  20.001  20.001  20.001  20.001  20.001    0    0    1    0           0          0          0          0  GET /api/user/atanaka0/statistics
    1   20.001  20.0010   0.0000  20.001  20.001  20.001  20.001  20.001  20.001    0    0    1    0           0          0          0          0  GET /api/user/mikimatsuda0/statistics
    1   20.001  20.0010   0.0000  20.001  20.001  20.001  20.001  20.001  20.001    0    0    1    0           0          0          0          0  GET /api/user/nakamuraryohei0/statistics
    1   20.001  20.0010   0.0000  20.001  20.001  20.001  20.001  20.001  20.001    0    0    1    0           0          0          0          0  GET /api/user/kanasasaki0/statistics
    1   20.000  20.0000   0.0000  20.000  20.000  20.000  20.000  20.000  20.000    0    0    1    0           0          0          0          0  GET /api/user/kazuyasato0/statistics
    1   20.000  20.0000   0.0000  20.000  20.000  20.000  20.000  20.000  20.000    0    0    1    0           0          0          0          0  GET /api/user/murakamisotaro0/statistics
    1   20.000  20.0000   0.0000  20.000  20.000  20.000  20.000  20.000  20.000    0    0    1    0           0          0          0          0  GET /api/user/satomikobayashi0/statistics
mism-mism commented 4 hours ago

CRAD状況

OBJECT_NAME COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
livestreams 7283606 7664 0 0
ng_words 6054982 14363 0 0
livecomments 3923148 1368 0 6
reactions 3891475 1352 0 0
reservation_slots 2971052 8759 1581 0
livestream_tags 202362 11733 0 0
tags 51177 103 0 0
records 40010 1619 0 1606
users 32099 1340 0 0
domains 27724 0 0 0
domainmetadata 27384 0 0 0
icons 17484 345 0 15
themes 14158 1339 0 0
livecomment_reports 675 46 0 0
livestream_viewers_history 266 37 0 29

テーブルごとのレコード数・容量など

TABLE_NAME TABLE_ROWS total_storage_mb avg_storage_b total_data_mb total_index_mb
icons 321 24 79827 24 0
ng_words 14519 3 218 1 1
livestreams 7525 2 350 2 0
reservation_slots 8593 0 93 0 0
users 1292 0 469 0 0
livecomments 1382 0 201 0 0
tags 103 0 318 0 0
reactions 1369 0 131 0 0
livecomment_reports 51 0 321 0 0
livestream_viewers_history 9 0 1820 0 0
livestream_tags 11387 0 71 0 0
domains 0 0 null 0 0
themes 1339 0 110 0 0
tsigkeys 0 0 null 0 0
cryptokeys 0 0 null 0 0
domainmetadata 0 0 null 0 0
comments 0 0 null 0 0
supermasters 0 0 null 0 0
records 1619 0 242 0 0
mism-mism commented 4 hours ago 
Top 20 Sort By Total
Count    Total     Mean   Stddev     Min   P50.0   P90.0   P95.0   P99.0     Max   2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
 5712  349.722   0.0612   0.0679   0.001   0.037   0.145   0.192   0.317   0.882  5706    0    6    0   332033430          0      58129     171848  GET /api/user/<username>/icon
   48  221.572   4.6161   2.6518   0.290   4.157   5.266   8.976  17.657  17.657    44    0    4    0        1012          0         21         23  POST /api/livestream/<num>/moderate
   14  205.740  14.6957   7.5372   2.537  20.000  20.001  20.001  20.001  20.001     4    0   10    0         546          0         39        140  GET /api/user/<username>/statistics
  665  194.851   0.2930   0.1951   0.000   0.270   0.556   0.666   0.823   1.104   660    0    5    0     8946184          0      13452      44532  GET /api/livestream/<num>/livecomment
  729  180.342   0.2474   0.2005   0.000   0.211   0.518   0.623   0.812   0.922   728    0    1    0     7929062          0      10876      37491  GET /api/livestream/<num>/reaction
  122  131.185   1.0753   0.5633   0.058   1.163   1.603   2.087   2.577   2.620   119    0    3    0     8779780          0      71965     134052  GET /api/livestream/search
  730  123.471   0.1691   0.0808   0.005   0.154   0.271   0.324   0.460   0.548   723    0    7    0     1389029          0       1902       2222  POST /api/livestream/<num>/livecomment
  669  105.578   0.1578   0.0814   0.000   0.135   0.262   0.315   0.428   0.835   664    0    3    2      312117          0        466        519  POST /api/register
  342   86.274   0.2523   0.1321   0.000   0.273   0.397   0.435   0.542   0.724   334    0    8    0      385036          0       1125       1496  POST /api/livestream/reservation
  692   65.520   0.0947   0.0672   0.005   0.079   0.175   0.231   0.366   0.461   692    0    0    0     1275707       1552       1843       2239  POST /api/livestream/<num>/reaction
  678   62.992   0.0929   0.0818   0.011   0.069   0.188   0.243   0.403   0.828   658    0    1   19       13245          0         19        154  POST /api/icon
  679   37.070   0.0546   0.0655   0.001   0.032   0.126   0.183   0.339   0.460   675    0    4    0         256          0          0         64  POST /api/login
    2   27.279  13.6395   0.6135  13.026  14.253  14.253  14.253  14.253  14.253     2    0    0    0          54         27         27         27  POST /api/initialize
  302   17.759   0.0588   0.0843   0.001   0.032   0.134   0.192   0.363   0.971   302    0    0    0     1840072       6089       6092       6095  GET /api/tag
    4   16.994   4.2485   0.1178   4.143   4.269   4.433   4.433   4.433   4.433     4    0    0    0         404        100        101        102  GET /api/livestream/<num>/statistics
  119   14.990   0.1260   0.0939   0.011   0.104   0.261   0.329   0.427   0.458   119    0    0    0      261426          3       2196      22520  GET /api/livestream
  106   13.767   0.1299   0.0988   0.010   0.112   0.261   0.301   0.380   0.705    96    0   10    0      249014         57       2349       2964  POST /api/livestream/<num>/livecomment/<num>/report
  109    8.354   0.0766   0.0786   0.002   0.059   0.176   0.291   0.373   0.381   107    0    0    2       83028          3        761      16400  GET /api/livestream/<num>/report
   75    8.152   0.1087   0.0775   0.005   0.099   0.192   0.261   0.532   0.532    75    0    0    0        1020          5         13        140  GET /api/livestream/<num>/ngwords
pinkumohikan commented 4 hours ago

@okashoi

@mism-mism

@pinkumohikan

okashoi commented 4 hours ago

2

7697

okashoi commented 4 hours ago

3

8005

mism-mism commented 4 hours ago 
Top 20 Sort By Total
Count    Total     Mean   Stddev     Min   P50.0   P90.0   P95.0   P99.0     Max   2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
   11  167.621  15.2383   6.5469   2.388  20.000  20.000  20.001  20.001  20.001     2    0    9    0         272          0         24        139  GET /api/user/<username>/statistics
  494  125.473   0.2540   0.2075   0.001   0.212   0.549   0.673   0.833   0.896   493    0    1    0     7165713          0      14505      50701  GET /api/livestream/<num>/livecomment
  529  117.061   0.2213   0.1992   0.000   0.172   0.503   0.621   0.759   0.962   526    0    3    0     6534252          0      12352      48595  GET /api/livestream/<num>/reaction
   23  107.077   4.6555   1.4594   0.191   4.690   5.568   6.693   8.463   8.463    20    0    3    0         460          0         20         23  POST /api/livestream/<num>/moderate
  527   79.232   0.1503   0.0494   0.005   0.150   0.211   0.228   0.261   0.287   524    0    3    0     1005891          0       1908       2201  POST /api/livestream/<num>/livecomment
   75   77.690   1.0359   0.5463   0.040   1.117   1.891   2.152   2.291   2.291    75    0    0    0     5454871      49432      72731     134708  GET /api/livestream/search
  467   58.501   0.1253   0.0461   0.001   0.120   0.163   0.186   0.245   0.606   464    0    2    1      217792          0        466        513  POST /api/register
  173   46.466   0.2686   0.1367   0.001   0.301   0.419   0.437   0.494   0.531   169    0    4    0      194674          0       1125       1462  POST /api/livestream/reservation
 4554   36.719   0.0081   0.0077   0.000   0.006   0.017   0.023   0.037   0.087  4554    0    0    0   266361856       6969      58489     164992  GET /api/user/<username>/icon
  508   24.809   0.0488   0.0234   0.007   0.048   0.079   0.087   0.115   0.155   508    0    0    0      938180       1552       1846       2202  POST /api/livestream/<num>/reaction
  472   19.072   0.0404   0.0221   0.008   0.034   0.070   0.085   0.110   0.136   447    0    0   25       10902         14         23        154  POST /api/icon
    1   14.101  14.1010   0.0000  14.101  14.101  14.101  14.101  14.101  14.101     1    0    0    0          27         27         27         27  POST /api/initialize
   73    6.815   0.0934   0.0592   0.009   0.086   0.163   0.183   0.281   0.281    73    0    0    0      141322          3       1935      22464  GET /api/livestream
    2    5.397   2.6985   0.0505   2.648   2.749   2.749   2.749   2.749   2.749     2    0    0    0         202        100        101        102  GET /api/livestream/<num>/statistics
   47    4.396   0.0935   0.0439   0.006   0.089   0.164   0.176   0.188   0.188    47    0    0    0         610          5         12        131  GET /api/livestream/<num>/ngwords
   56    2.720   0.0486   0.0285   0.002   0.048   0.086   0.111   0.125   0.125    50    0    6    0      129571          0       2313       2712  POST /api/livestream/<num>/livecomment/<num>/report
  472    2.653   0.0056   0.0049   0.001   0.004   0.012   0.015   0.022   0.050   470    0    2    0         128          0          0         64  POST /api/login
   51    2.443   0.0479   0.0965   0.000   0.006   0.208   0.305   0.396   0.396    51    0    0    0      153299          3       3005      20642  GET /api/livestream/<num>/livecomment?limit=<num>
   50    2.281   0.0456   0.1011   0.000   0.004   0.295   0.338   0.365   0.365    50    0    0    0      118754          3       2375      19843  GET /api/livestream/<num>/reaction?limit=<num>
okashoi commented 4 hours ago

GET /api/user/<username>/statistics に対処

pinkumohikan commented 4 hours ago

Google Cloud Profileに入門している

Go アプリを最適化する https://cloud.google.com/profiler/docs/quickstart-go-app?hl=ja

pinkumohikan commented 45 minutes ago

Google Cloud Profilerで取ったアプリのプロファイル

https://console.cloud.google.com/profiler;timespan=30m/isucon13;type=CPU;version=2024-11-02%2006~3a28~3a45/cpu?hl=ja&project=isucon-13-practice

image
pinkumohikan commented 31 minutes ago

kataribe

Top 20 Sort By Total
Count    Total     Mean   Stddev     Min   P50.0   P90.0   P95.0   P99.0     Max   2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
   11  163.780  14.8891   6.6249   2.499  20.000  20.003  20.003  20.003  20.003     2    0    9    0         272          0         24        139  GET /api/user/<username>/statistics
  481  124.968   0.2598   0.2104   0.001   0.214   0.555   0.652   0.905   1.030   479    0    2    0     7192190          0      14952      53109  GET /api/livestream/<num>/livecomment
  517  120.865   0.2338   0.2075   0.000   0.195   0.537   0.608   0.846   1.034   513    0    4    0     6560835          0      12690      49191  GET /api/livestream/<num>/reaction
   26  118.930   4.5742   1.5691   0.200   4.702   5.493   5.571   9.841   9.841    23    0    3    0         529          0         20         23  POST /api/livestream/<num>/moderate
  518   77.045   0.1487   0.0504   0.006   0.150   0.216   0.232   0.267   0.304   516    0    2    0      994621          0       1920       2153  POST /api/livestream/<num>/livecomment
   68   71.401   1.0500   0.6041   0.045   1.136   2.012   2.285   2.472   2.472    68    0    0    0     5143013      49491      75632     137112  GET /api/livestream/search
  493   54.529   0.1106   0.0398   0.000   0.107   0.151   0.165   0.216   0.505   491    0    1    1      230587         67        467        522  POST /api/register
  178   46.309   0.2602   0.1282   0.000   0.282   0.405   0.427   0.451   0.501   174    0    4    0      200759          0       1127       1494  POST /api/livestream/reservation
 4623   37.646   0.0081   0.0076   0.000   0.006   0.017   0.022   0.035   0.089  4623    0    0    0   252428936       6969      54602     171848  GET /api/user/<username>/icon
  495   23.804   0.0481   0.0242   0.007   0.047   0.078   0.091   0.120   0.205   495    0    0    0      916123       1552       1850       2237  POST /api/livestream/<num>/reaction
  500   20.155   0.0403   0.0261   0.012   0.034   0.070   0.081   0.125   0.353   481    0    0   19       10520         14         21        154  POST /api/icon
    1   13.941  13.9410   0.0000  13.941  13.941  13.941  13.941  13.941  13.941     1    0    0    0          27         27         27         27  POST /api/initialize
   61    6.179   0.1013   0.0645   0.012   0.088   0.181   0.230   0.294   0.294    61    0    0    0      136552          3       2238      22529  GET /api/livestream
    2    5.670   2.8350   0.0830   2.752   2.918   2.918   2.918   2.918   2.918     2    0    0    0         202        100        101        102  GET /api/livestream/<num>/statistics
   40    2.968   0.0742   0.0433   0.005   0.071   0.139   0.157   0.176   0.176    40    0    0    0         329          5          8        134  GET /api/livestream/<num>/ngwords
   50    2.922   0.0584   0.1133   0.001   0.006   0.271   0.364   0.406   0.406    50    0    0    0      182382          3       3647      20883  GET /api/livestream/<num>/livecomment?limit=<num>
  499    2.822   0.0057   0.0050   0.001   0.004   0.011   0.014   0.027   0.043   497    0    2    0         128          0          0         64  POST /api/login
   49    2.812   0.0574   0.1149   0.000   0.004   0.308   0.316   0.415   0.415    49    0    0    0      158007          3       3224      20055  GET /api/livestream/<num>/reaction?limit=<num>
   55    2.613   0.0475   0.0348   0.002   0.042   0.085   0.123   0.172   0.172    49    0    6    0      127507         57       2318       2699  POST /api/livestream/<num>/livecomment/<num>/report