計測 - Githubissues

karamaru-alpha commented 1 year ago

Mysql

CREATE TABLE `id_generator` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `stub` char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB AUTO_INCREMENT=2678408366 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `tenant` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `display_name` varchar(255) NOT NULL,
  `created_at` bigint NOT NULL,
  `updated_at` bigint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=108 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `visit_history` (
  `player_id` varchar(255) NOT NULL,
  `tenant_id` bigint unsigned NOT NULL,
  `competition_id` varchar(255) NOT NULL,
  `created_at` bigint NOT NULL,
  `updated_at` bigint NOT NULL,
  KEY `tenant_id_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

+---------------+--------+------------+----------+---------+----------+
| TABLE_NAME    | ENGINE | TABLE_ROWS | total_mb | data_mb | index_mb |
+---------------+--------+------------+----------+---------+----------+
| visit_history | InnoDB |    3008940 |      270 |     212 |       57 |
| id_generator  | InnoDB |          1 |        0 |       0 |        0 |
| tenant        | InnoDB |        106 |        0 |       0 |        0 |
+---------------+--------+------------+----------+---------+----------+

Sqlite


DROP TABLE IF EXISTS competition;
DROP TABLE IF EXISTS player;
DROP TABLE IF EXISTS player_score;

CREATE TABLE competition (
  id VARCHAR(255) NOT NULL PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  title TEXT NOT NULL,
  finished_at BIGINT NULL,
  created_at BIGINT NOT NULL,
  updated_at BIGINT NOT NULL
);

CREATE TABLE player (
  id VARCHAR(255) NOT NULL PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  display_name TEXT NOT NULL,
  is_disqualified BOOLEAN NOT NULL,
  created_at BIGINT NOT NULL,
  updated_at BIGINT NOT NULL
);

CREATE TABLE player_score (
  id VARCHAR(255) NOT NULL PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  player_id VARCHAR(255) NOT NULL,
  competition_id VARCHAR(255) NOT NULL,
  score BIGINT NOT NULL,
  row_num BIGINT NOT NULL,
  created_at BIGINT NOT NULL,
  updated_at BIGINT NOT NULL
);
``

karamaru-alpha commented 1 year ago

14 23731

予選突破ライン。本番でいけた人すごい。

kataribe

``` sudo cat /var/log/nginx/access.log | kataribe -f /home/isucon/webapp/kataribe.toml Top 20 Sort By Count Count Total Mean Min Max Request 11763 1935.090 0.1645 0.001 6.211 GET /api/player/player/* 5529 1727.438 0.3124 0.000 5.895 GET /api/player/competition/*/ranking 866 73.861 0.0853 0.001 2.188 GET /api/player/competitions HTTP/2.0 162 24.990 0.1543 0.001 0.817 POST /api/organizer/competition/*/score 141 7.807 0.0554 0.004 0.476 POST /api/organizer/competitions/add HTTP/2.0 135 8.250 0.0611 0.001 0.487 POST /api/organizer/competition/*/finish 128 7.478 0.0584 0.001 0.927 GET /api/organizer/players HTTP/2.0 112 75.866 0.6774 0.002 7.022 GET /api/organizer/billing HTTP/2.0 75 82.912 1.1055 0.001 14.289 GET /api/admin/tenants/billing?before=* 25 80.163 3.2065 0.911 11.060 POST /api/organizer/players/add HTTP/2.0 18 1.095 0.0608 0.002 0.384 POST /api/organizer/player/*/disqualified 16 1.845 0.1153 0.000 0.717 POST /api/admin/tenants/add HTTP/2.0 6 2.477 0.4128 0.029 1.264 GET /api/admin/tenants/billing HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /css/app.83b4c321.css HTTP/2.0 1 0.001 0.0010 0.001 0.001 GET /api/organizer/competitions HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /index.html HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /js/app.3a4ec98c.js HTTP/2.0 1 4.048 4.0480 4.048 4.048 POST /initialize HTTP/2.0 Top 20 Sort By Total Count Total Mean Min Max Request 11763 1935.090 0.1645 0.001 6.211 GET /api/player/player/* 5529 1727.438 0.3124 0.000 5.895 GET /api/player/competition/*/ranking 75 82.912 1.1055 0.001 14.289 GET /api/admin/tenants/billing?before=* 25 80.163 3.2065 0.911 11.060 POST /api/organizer/players/add HTTP/2.0 112 75.866 0.6774 0.002 7.022 GET /api/organizer/billing HTTP/2.0 866 73.861 0.0853 0.001 2.188 GET /api/player/competitions HTTP/2.0 162 24.990 0.1543 0.001 0.817 POST /api/organizer/competition/*/score 135 8.250 0.0611 0.001 0.487 POST /api/organizer/competition/*/finish 141 7.807 0.0554 0.004 0.476 POST /api/organizer/competitions/add HTTP/2.0 128 7.478 0.0584 0.001 0.927 GET /api/organizer/players HTTP/2.0 1 4.048 4.0480 4.048 4.048 POST /initialize HTTP/2.0 6 2.477 0.4128 0.029 1.264 GET /api/admin/tenants/billing HTTP/2.0 16 1.845 0.1153 0.000 0.717 POST /api/admin/tenants/add HTTP/2.0 18 1.095 0.0608 0.002 0.384 POST /api/organizer/player/*/disqualified 1 0.001 0.0010 0.001 0.001 GET /api/organizer/competitions HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /css/app.83b4c321.css HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /index.html HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /js/app.3a4ec98c.js HTTP/2.0 Top 20 Sort By Mean Count Total Mean Min Max Request 1 4.048 4.0480 4.048 4.048 POST /initialize HTTP/2.0 25 80.163 3.2065 0.911 11.060 POST /api/organizer/players/add HTTP/2.0 75 82.912 1.1055 0.001 14.289 GET /api/admin/tenants/billing?before=* 112 75.866 0.6774 0.002 7.022 GET /api/organizer/billing HTTP/2.0 6 2.477 0.4128 0.029 1.264 GET /api/admin/tenants/billing HTTP/2.0 5529 1727.438 0.3124 0.000 5.895 GET /api/player/competition/*/ranking 11763 1935.090 0.1645 0.001 6.211 GET /api/player/player/* 162 24.990 0.1543 0.001 0.817 POST /api/organizer/competition/*/score 16 1.845 0.1153 0.000 0.717 POST /api/admin/tenants/add HTTP/2.0 866 73.861 0.0853 0.001 2.188 GET /api/player/competitions HTTP/2.0 135 8.250 0.0611 0.001 0.487 POST /api/organizer/competition/*/finish 18 1.095 0.0608 0.002 0.384 POST /api/organizer/player/*/disqualified 128 7.478 0.0584 0.001 0.927 GET /api/organizer/players HTTP/2.0 141 7.807 0.0554 0.004 0.476 POST /api/organizer/competitions/add HTTP/2.0 1 0.001 0.0010 0.001 0.001 GET /api/organizer/competitions HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /css/app.83b4c321.css HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /index.html HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /js/app.3a4ec98c.js HTTP/2.0 Top 20 Sort By Maximum(100 Percentile) Count Total Mean Min Max Request 75 82.912 1.1055 0.001 14.289 GET /api/admin/tenants/billing?before=* 25 80.163 3.2065 0.911 11.060 POST /api/organizer/players/add HTTP/2.0 112 75.866 0.6774 0.002 7.022 GET /api/organizer/billing HTTP/2.0 11763 1935.090 0.1645 0.001 6.211 GET /api/player/player/* 5529 1727.438 0.3124 0.000 5.895 GET /api/player/competition/*/ranking 1 4.048 4.0480 4.048 4.048 POST /initialize HTTP/2.0 866 73.861 0.0853 0.001 2.188 GET /api/player/competitions HTTP/2.0 6 2.477 0.4128 0.029 1.264 GET /api/admin/tenants/billing HTTP/2.0 128 7.478 0.0584 0.001 0.927 GET /api/organizer/players HTTP/2.0 162 24.990 0.1543 0.001 0.817 POST /api/organizer/competition/*/score 16 1.845 0.1153 0.000 0.717 POST /api/admin/tenants/add HTTP/2.0 135 8.250 0.0611 0.001 0.487 POST /api/organizer/competition/*/finish 141 7.807 0.0554 0.004 0.476 POST /api/organizer/competitions/add HTTP/2.0 18 1.095 0.0608 0.002 0.384 POST /api/organizer/player/*/disqualified 1 0.001 0.0010 0.001 0.001 GET /api/organizer/competitions HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /css/app.83b4c321.css HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /index.html HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /js/app.3a4ec98c.js HTTP/2.0 TOP 10 Slow Requests 1 14.289 GET /api/admin/tenants/billing?before=52 HTTP/2.0 2 12.699 GET /api/admin/tenants/billing?before=48 HTTP/2.0 3 11.060 POST /api/organizer/players/add HTTP/2.0 4 10.413 GET /api/admin/tenants/billing?before=42 HTTP/2.0 5 9.647 POST /api/organizer/players/add HTTP/2.0 6 7.022 GET /api/organizer/billing HTTP/2.0 7 6.211 GET /api/player/player/6151b6296 HTTP/2.0 8 6.175 GET /api/player/player/01GZV91JKC2WW6G1SKD1W6WX2K HTTP/2.0 9 5.952 GET /api/player/player/01GZV918SGQA392HF9BXAG2SMS HTTP/2.0 10 5.895 GET /api/player/competition/01GZV91HKSZZ2TD2Q0DJ7PH4HC/ranking HTTP/2.0 ```

slow-log

``` sudo /home/isucon/webapp/slow.sh /var/log/mysql/slow-query.log # Query 1: 87.49 QPS, 0.28x concurrency, ID 0x717868DB0FF4601C650032A81060D41E at byte 1324296 # Scores: V/M = 0.00 # Time range: 2023-05-07T14:14:41 to 2023-05-07T14:15:44 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 4 5512 # Exec time 57 18s 85us 54ms 3ms 6ms 2ms 3ms # Lock time 18 13ms 0 5ms 2us 1us 62us 1us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 19 1.14M 182 218 216.34 212.52 5.34 212.52 # String: # Databases isuports # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # # 100us #### # 1ms ################################################################ # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuports` LIKE 'latest_visit_history'\G # SHOW CREATE TABLE `isuports`.`latest_visit_history`\G INSERT INTO latest_visit_history (player_id, tenant_id, competition_id, created_at) VALUES ('1e434e8db', 54, '01GZV916Q4JSGX8BAFF3N4S4Q3', 1683468886) ON DUPLICATE KEY UPDATE `created_at` = 1683468886\G # Query 2: 150.38 QPS, 0.06x concurrency, ID 0x731A1F4786F770DE31CE879DB79AA27F at byte 8771018 # Scores: V/M = 0.00 # Time range: 2023-05-07T14:14:40 to 2023-05-07T14:15:44 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 8 9624 # Exec time 11 4s 115us 35ms 366us 467us 836us 224us # Lock time 19 14ms 0 135us 1us 1us 2us 1us # Rows sent 98 1.70M 0 4.88k 184.87 183.58 649.15 76.28 # Rows examine 33 1.70M 0 4.88k 184.87 183.58 649.15 76.28 # Query size 20 1.17M 124 144 127.73 143.84 5.77 124.25 # String: # Databases isuports # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us ################################################################ # 1ms # # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuports` LIKE 'latest_visit_history'\G # SHOW CREATE TABLE `isuports`.`latest_visit_history`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT player_id, created_at AS min_created_at FROM latest_visit_history WHERE tenant_id = 1 AND competition_id = '4447b4e1'\G # Query 3: 608.29 QPS, 0.04x concurrency, ID 0xDA556F9115773A1A99AA0165670CE848 at byte 18250887 # Scores: V/M = 0.00 # Time range: 2023-05-07T14:14:39 to 2023-05-07T14:15:44 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 32 39539 # Exec time 9 3s 24us 17ms 70us 98us 155us 57us # 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 # Query size 19 1.13M 30 30 30 30 0 30 # String: # Databases isuports # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us ################################################################ # 100us ### # 1ms # # 10ms # # 100ms # 1s # 10s+ administrator command: Prepare\G # Query 4: 0 QPS, 0x concurrency, ID 0x3289E87E94D5A82E348974B3D332E575 at byte 662 # Scores: V/M = 0.00 # Time range: all events occurred at 2023-05-07T14:14:37 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 1 # Exec time 8 2s 2s 2s 2s 2s 0 2s # Lock time 0 7us 7us 7us 7us 7us 0 7us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 61 3.08M 3.08M 3.08M 3.08M 3.08M 0 3.08M # Query size 0 58 58 58 58 58 0 58 # String: # Databases isuports # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms # 1s ################################################################ # 10s+ # Tables # SHOW TABLE STATUS FROM `isuports` LIKE 'visit_history'\G # SHOW CREATE TABLE `isuports`.`visit_history`\G DELETE FROM visit_history WHERE created_at >= '1654041600'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select * from visit_history WHERE created_at >= '1654041600'\G # Query 5: 294.98 QPS, 0.03x concurrency, ID 0x57AC6CF9E75AF6673635BEFFE5F2117E at byte 18254976 # Scores: V/M = 0.00 # Time range: 2023-05-07T14:14:40 to 2023-05-07T14:15:44 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 15 18879 # Exec time 6 2s 48us 9ms 106us 152us 155us 89us # Lock time 47 33ms 0 291us 1us 1us 5us 1us # Rows sent 1 18.44k 0 1 1.00 0.99 0.01 0.99 # Rows examine 0 18.44k 0 1 1.00 0.99 0.01 0.99 # Query size 16 968.19k 42 57 52.51 56.92 4.98 51.63 # String: # Databases isuports # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us ################################################################ # 100us ########################### # 1ms # # 10ms # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuports` LIKE 'tenant'\G # SHOW CREATE TABLE `isuports`.`tenant`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM tenant WHERE name = 'uqvn-ye-1683468894'\G # Query 6: 0 QPS, 0x concurrency, ID 0xD8D99DA87D548EEB1C1FF31A4382E239 at byte 1932 # Scores: V/M = 0.00 # Time range: all events occurred at 2023-05-07T14:14:39 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 1 # Exec time 3 1s 1s 1s 1s 1s 0 1s # Lock time 0 7us 7us 7us 7us 7us 0 7us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 3 195.78k 195.78k 195.78k 195.78k 195.78k 0 195.78k # Query size 0 121 121 121 121 121 0 121 # String: # Databases isuports # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms # 1s ################################################################ # 10s+ # Tables # SHOW TABLE STATUS FROM `isuports` LIKE 'latest_visit_history'\G # SHOW CREATE TABLE `isuports`.`latest_visit_history`\G # SHOW TABLE STATUS FROM `isuports` LIKE 'init_latest_visit_history'\G # SHOW CREATE TABLE `isuports`.`init_latest_visit_history`\G INSERT INTO `latest_visit_history` SELECT tenant_id, competition_id, player_id, created_at FROM init_latest_visit_history\G ```

563ed616750a1efc1dddd7f821d32865c26202fa

karamaru-alpha commented 1 year ago

ranking 23846

kataribe

``` sudo cat /var/log/nginx/access.log | kataribe -f /home/isucon/webapp/kataribe.toml Top 20 Sort By Count Count Total Mean Min Max Request 11870 1779.559 0.1499 0.001 6.287 GET /api/player/player/* 5469 1523.348 0.2785 0.001 5.986 GET /api/player/competition/*/ranking 852 60.291 0.0708 0.000 1.400 GET /api/player/competitions HTTP/2.0 162 23.240 0.1435 0.001 0.672 POST /api/organizer/competition/*/score 142 9.991 0.0704 0.001 1.179 POST /api/organizer/competitions/add HTTP/2.0 136 28.232 0.2076 0.001 2.836 POST /api/organizer/competition/*/finish 127 7.786 0.0613 0.001 0.742 GET /api/organizer/players HTTP/2.0 115 58.767 0.5110 0.002 3.980 GET /api/organizer/billing HTTP/2.0 73 92.441 1.2663 0.001 20.837 GET /api/admin/tenants/billing?before=* 26 74.625 2.8702 0.952 9.706 POST /api/organizer/players/add HTTP/2.0 17 0.887 0.0522 0.001 0.351 POST /api/organizer/player/*/disqualified 16 2.227 0.1392 0.001 0.910 POST /api/admin/tenants/add HTTP/2.0 6 1.413 0.2355 0.041 1.004 GET /api/admin/tenants/billing HTTP/2.0 2 4.003 2.0015 0.000 4.003 POST /initialize HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /js/app.3a4ec98c.js HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /css/app.83b4c321.css HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /index.html HTTP/2.0 1 0.001 0.0010 0.001 0.001 GET /api/organizer/competitions HTTP/2.0 Top 20 Sort By Total Count Total Mean Min Max Request 11870 1779.559 0.1499 0.001 6.287 GET /api/player/player/* 5469 1523.348 0.2785 0.001 5.986 GET /api/player/competition/*/ranking 73 92.441 1.2663 0.001 20.837 GET /api/admin/tenants/billing?before=* 26 74.625 2.8702 0.952 9.706 POST /api/organizer/players/add HTTP/2.0 852 60.291 0.0708 0.000 1.400 GET /api/player/competitions HTTP/2.0 115 58.767 0.5110 0.002 3.980 GET /api/organizer/billing HTTP/2.0 136 28.232 0.2076 0.001 2.836 POST /api/organizer/competition/*/finish 162 23.240 0.1435 0.001 0.672 POST /api/organizer/competition/*/score 142 9.991 0.0704 0.001 1.179 POST /api/organizer/competitions/add HTTP/2.0 127 7.786 0.0613 0.001 0.742 GET /api/organizer/players HTTP/2.0 2 4.003 2.0015 0.000 4.003 POST /initialize HTTP/2.0 16 2.227 0.1392 0.001 0.910 POST /api/admin/tenants/add HTTP/2.0 6 1.413 0.2355 0.041 1.004 GET /api/admin/tenants/billing HTTP/2.0 17 0.887 0.0522 0.001 0.351 POST /api/organizer/player/*/disqualified 1 0.001 0.0010 0.001 0.001 GET /api/organizer/competitions HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /js/app.3a4ec98c.js HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /css/app.83b4c321.css HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /index.html HTTP/2.0 Top 20 Sort By Mean Count Total Mean Min Max Request 26 74.625 2.8702 0.952 9.706 POST /api/organizer/players/add HTTP/2.0 2 4.003 2.0015 0.000 4.003 POST /initialize HTTP/2.0 73 92.441 1.2663 0.001 20.837 GET /api/admin/tenants/billing?before=* 115 58.767 0.5110 0.002 3.980 GET /api/organizer/billing HTTP/2.0 5469 1523.348 0.2785 0.001 5.986 GET /api/player/competition/*/ranking 6 1.413 0.2355 0.041 1.004 GET /api/admin/tenants/billing HTTP/2.0 136 28.232 0.2076 0.001 2.836 POST /api/organizer/competition/*/finish 11870 1779.559 0.1499 0.001 6.287 GET /api/player/player/* 162 23.240 0.1435 0.001 0.672 POST /api/organizer/competition/*/score 16 2.227 0.1392 0.001 0.910 POST /api/admin/tenants/add HTTP/2.0 852 60.291 0.0708 0.000 1.400 GET /api/player/competitions HTTP/2.0 142 9.991 0.0704 0.001 1.179 POST /api/organizer/competitions/add HTTP/2.0 127 7.786 0.0613 0.001 0.742 GET /api/organizer/players HTTP/2.0 17 0.887 0.0522 0.001 0.351 POST /api/organizer/player/*/disqualified 1 0.001 0.0010 0.001 0.001 GET /api/organizer/competitions HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /js/app.3a4ec98c.js HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /css/app.83b4c321.css HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /index.html HTTP/2.0 Top 20 Sort By Maximum(100 Percentile) Count Total Mean Min Max Request 73 92.441 1.2663 0.001 20.837 GET /api/admin/tenants/billing?before=* 26 74.625 2.8702 0.952 9.706 POST /api/organizer/players/add HTTP/2.0 11870 1779.559 0.1499 0.001 6.287 GET /api/player/player/* 5469 1523.348 0.2785 0.001 5.986 GET /api/player/competition/*/ranking 2 4.003 2.0015 0.000 4.003 POST /initialize HTTP/2.0 115 58.767 0.5110 0.002 3.980 GET /api/organizer/billing HTTP/2.0 136 28.232 0.2076 0.001 2.836 POST /api/organizer/competition/*/finish 852 60.291 0.0708 0.000 1.400 GET /api/player/competitions HTTP/2.0 142 9.991 0.0704 0.001 1.179 POST /api/organizer/competitions/add HTTP/2.0 6 1.413 0.2355 0.041 1.004 GET /api/admin/tenants/billing HTTP/2.0 16 2.227 0.1392 0.001 0.910 POST /api/admin/tenants/add HTTP/2.0 127 7.786 0.0613 0.001 0.742 GET /api/organizer/players HTTP/2.0 162 23.240 0.1435 0.001 0.672 POST /api/organizer/competition/*/score 17 0.887 0.0522 0.001 0.351 POST /api/organizer/player/*/disqualified 1 0.001 0.0010 0.001 0.001 GET /api/organizer/competitions HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /js/app.3a4ec98c.js HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /css/app.83b4c321.css HTTP/2.0 1 0.000 0.0000 0.000 0.000 GET /index.html HTTP/2.0 TOP 10 Slow Requests 1 20.837 GET /api/admin/tenants/billing?before=62 HTTP/2.0 2 16.943 GET /api/admin/tenants/billing?before=49 HTTP/2.0 3 10.268 GET /api/admin/tenants/billing?before=49 HTTP/2.0 4 9.706 POST /api/organizer/players/add HTTP/2.0 5 7.651 POST /api/organizer/players/add HTTP/2.0 6 6.287 GET /api/player/player/5edecb738 HTTP/2.0 7 5.986 GET /api/player/competition/01H0DHJWFV76FH323RAQ2PXRX0/ranking HTTP/2.0 8 5.575 GET /api/admin/tenants/billing?before=52 HTTP/2.0 9 5.512 GET /api/player/player/623354f5d HTTP/2.0 10 5.369 GET /api/player/competition/01H0DHKRWZQQZGDQTS4NF2SVKQ/ranking HTTP/2.0 ```

slow-log

``` sudo /home/isucon/webapp/slow.sh /var/log/mysql/slow-query.log # Query 1: 83.89 QPS, 0.26x concurrency, ID 0x717868DB0FF4601C650032A81060D41E at byte 17888537 # Scores: V/M = 0.00 # Time range: 2023-05-14T16:30:26 to 2023-05-14T16:31:31 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 3 5453 # Exec time 53 17s 80us 50ms 3ms 6ms 3ms 3ms # Lock time 9 8ms 0 33us 1us 1us 1us 1us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 15 1.13M 182 218 216.43 212.52 5.50 212.52 # String: # Databases isuports # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # # 100us ##### # 1ms ################################################################ # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuports` LIKE 'latest_visit_history'\G # SHOW CREATE TABLE `isuports`.`latest_visit_history`\G INSERT INTO latest_visit_history (player_id, tenant_id, competition_id, created_at) VALUES ('01H0DHK247VP8SFVZNPGMV174E', 569, '01H0DHKB3H3GDWDX1T273K2AST', 1684081855) ON DUPLICATE KEY UPDATE `created_at` = 1684081855\G # Query 2: 718.75 QPS, 0.05x concurrency, ID 0xDA556F9115773A1A99AA0165670CE848 at byte 27046777 # Scores: V/M = 0.00 # Time range: 2023-05-14T16:30:24 to 2023-05-14T16:31:31 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 33 48156 # Exec time 10 3s 26us 9ms 65us 89us 91us 57us # 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 # Query size 19 1.38M 30 30 30 30 0 30 # String: # Databases isuports # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us ################################################################ # 100us ## # 1ms # # 10ms # 100ms # 1s # 10s+ administrator command: Prepare\G # Query 3: 132.14 QPS, 0.05x concurrency, ID 0x731A1F4786F770DE31CE879DB79AA27F at byte 8212498 # Scores: V/M = 0.00 # Time range: 2023-05-14T16:30:25 to 2023-05-14T16:31:30 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 5 8589 # Exec time 10 3s 104us 20ms 367us 424us 828us 204us # Lock time 15 12ms 0 420us 1us 1us 4us 1us # Rows sent 98 1.75M 0 4.89k 213.23 183.58 714.34 84.10 # Rows examine 34 1.75M 0 4.89k 213.23 183.58 714.34 84.10 # Query size 14 1.03M 124 144 126.23 124.25 2.58 124.25 # String: # Databases isuports # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us ################################################################ # 1ms ## # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuports` LIKE 'latest_visit_history'\G # SHOW CREATE TABLE `isuports`.`latest_visit_history`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT player_id, created_at AS min_created_at FROM latest_visit_history WHERE tenant_id = 1 AND competition_id = '547f03113'\G # Query 4: 0 QPS, 0x concurrency, ID 0x3289E87E94D5A82E348974B3D332E575 at byte 662 # Scores: V/M = 0.00 # Time range: all events occurred at 2023-05-14T16:30:23 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 1 # Exec time 7 2s 2s 2s 2s 2s 0 2s # Lock time 0 8us 8us 8us 8us 8us 0 8us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 60 3.08M 3.08M 3.08M 3.08M 3.08M 0 3.08M # Query size 0 58 58 58 58 58 0 58 # String: # Databases isuports # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms # 1s ################################################################ # 10s+ # Tables # SHOW TABLE STATUS FROM `isuports` LIKE 'visit_history'\G # SHOW CREATE TABLE `isuports`.`visit_history`\G DELETE FROM visit_history WHERE created_at >= '1654041600'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select * from visit_history WHERE created_at >= '1654041600'\G # Query 5: 291.03 QPS, 0.03x concurrency, ID 0x57AC6CF9E75AF6673635BEFFE5F2117E at byte 22839287 # Scores: V/M = 0.00 # Time range: 2023-05-14T16:30:25 to 2023-05-14T16:31:30 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 12 18917 # Exec time 6 2s 49us 9ms 100us 144us 131us 84us # Lock time 46 36ms 0 4ms 1us 1us 30us 1us # Rows sent 1 18.47k 0 1 1.00 0.99 0.01 0.99 # Rows examine 0 18.47k 0 1 1.00 0.99 0.01 0.99 # Query size 13 975.54k 42 58 52.81 56.92 4.30 51.63 # String: # Databases isuports # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us ################################################################ # 100us ################### # 1ms # # 10ms # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuports` LIKE 'tenant'\G # SHOW CREATE TABLE `isuports`.`tenant`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM tenant WHERE name = 'kwpbq-awnl-1684081832'\G # Query 6: 147.65 QPS, 0.02x concurrency, ID 0x6490BDC2BF440C55D94FF35EE9D3FF80 at byte 4409038 # Scores: V/M = 0.00 # Time range: 2023-05-14T16:30:25 to 2023-05-14T16:31:30 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 6 9597 # Exec time 3 1s 71us 8ms 117us 159us 112us 103us # Lock time 17 13ms 0 210us 1us 1us 2us 1us # Rows sent 0 1008 0 1 0.11 0.99 0.30 0 # Rows examine 0 1008 0 1 0.11 0.99 0.30 0 # Query size 11 862.98k 88 108 92.08 107.34 6.02 88.31 # String: # Databases isuports # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us ################################ # 100us ################################################################ # 1ms # # 10ms # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuports` LIKE 'billing_report'\G # SHOW CREATE TABLE `isuports`.`billing_report`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM billing_report WHERE tenant_id = 15 AND competition_id = '4c1d8a5a8' LIMIT 1\G ```

560bb997634feb1f8673f121ec104673877095cd

karamaru-alpha / isucon12-qualify

計測 #1

init 3599

2 3952

3 5021

4 6790

5 8365

6 6443

7 5814

10 8338

11 7287

13 18359

14 23731

ranking 23846