Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

スコア履歴 #2

Open
minhquang4334 opened this issue Jul 22, 2022 · 12 comments
Open

スコア履歴 #2

minhquang4334 opened this issue Jul 22, 2022 · 12 comments

Comments

@minhquang4334
Copy link
Collaborator

No description provided.

@henchiyb
Copy link
Owner

henchiyb commented Jul 23, 2022 •
edited
Loading

Score: 3768

   PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND                                                                                               
   4043 isucon    20   0 2504312  67304  10820 S 104.7   1.8   1:10.28 main                                                                                                  
   4883 mysql     20   0 2569292 528048  34932 S  89.0  13.9   2:27.14 mysqld                                                                                                
   3763 www-data  20   0   58884  12068   7296 S   1.3   0.3   0:00.83 nginx                                                                                                 
    166 root      19  -1   72600  27788  26524 S   0.7   0.7   0:01.28 systemd-journal                                                                                       
    836 root      20   0 1384056  86792  54108 S   0.7   2.3   0:01.66 dockerd        
                                                                                    
+-------+-----+-----+-----+-----+-----+--------+---------------------------------------+-------+--------+----------+-------+--------+--------+--------+--------+-----------+------------+-------------+-----------+
| COUNT | 1XX | 2XX | 3XX | 4XX | 5XX | METHOD |                  URI                  |  MIN  |  MAX   |   SUM    |  AVG  |  P90   |  P95   |  P99   | STDDEV | MIN(BODY) | MAX(BODY)  |  SUM(BODY)  | AVG(BODY) |
+-------+-----+-----+-----+-----+-----+--------+---------------------------------------+-------+--------+----------+-------+--------+--------+--------+--------+-----------+------------+-------------+-----------+
|   885 |   0 | 834 |   0 |  50 |   1 | GET    | /api/player/player/                   | 0.004 | 12.661 | 1125.174 | 1.271 |  3.300 |  3.976 | 11.949 |  2.131 |     0.000 |   1607.000 |  728122.000 |   822.737 |
|   943 |   0 | 889 |   0 |  53 |   1 | GET    | /api/player/competition/              | 0.004 | 12.557 | 1120.952 | 1.189 |  3.624 |  4.312 | 12.033 |  2.236 |     0.000 |  14697.000 | 8492274.000 |  9005.593 |
|   118 |   0 | 109 |   0 |   9 |   0 | POST   | /api/organizer/competition/           | 0.004 | 11.825 |  109.775 | 0.930 |  3.192 |  3.648 |  8.656 |  1.680 |     0.000 |     62.000 |    4597.000 |    38.958 |
|    14 |   0 |  13 |   0 |   1 |   0 | GET    | /api/admin/tenants/billing?before=xxx | 1.952 | 13.185 |  109.686 | 7.835 | 11.280 | 13.185 | 13.185 |  3.224 |     0.000 |   1542.000 |   19635.000 |  1402.500 |
|     9 |   0 |   9 |   0 |   0 |   0 | POST   | /api/organizer/players/add            | 0.908 |  3.032 |   17.960 | 1.996 |  3.032 |  3.032 |  3.032 |  0.815 | 12150.000 |  26940.000 |  180415.000 | 20046.111 |
|    30 |   0 |  30 |   0 |   0 |   0 | GET    | /api/organizer/billing                | 0.004 |  1.292 |    4.940 | 0.165 |  0.580 |  0.948 |  1.292 |  0.303 |   323.000 |   3859.000 |   59722.000 |  1990.733 |
|     1 |   0 |   1 |   0 |   0 |   0 | POST   | /initialize                           | 4.136 |  4.136 |    4.136 | 4.136 |  4.136 |  4.136 |  4.136 |  0.000 |    55.000 |     55.000 |      55.000 |    55.000 |
|   153 |   0 | 139 |   0 |  14 |   0 | GET    | /api/player/competitions              | 0.044 |  0.080 |    2.720 | 0.018 |  0.040 |  0.044 |  0.064 |  0.015 |    39.000 |   2666.000 |  171883.000 |  1123.418 |
|     1 |   0 |   1 |   0 |   0 |   0 | GET    | /api/admin/tenants/billing            | 2.644 |  2.644 |    2.644 | 2.644 |  2.644 |  2.644 |  2.644 |  0.000 |  1566.000 |   1566.000 |    1566.000 |  1566.000 |
|    52 |   0 |  51 |   0 |   1 |   0 | POST   | /api/organizer/competitions           | 0.004 |  0.048 |    0.812 | 0.016 |  0.024 |  0.036 |  0.048 |  0.009 |    39.000 |    189.000 |    8377.000 |   161.096 |
|    11 |   0 |   6 |   0 |   5 |   0 | POST   | /api/admin/tenants/add                | 0.004 |  0.076 |    0.304 | 0.028 |  0.068 |  0.076 |  0.076 |  0.026 |    39.000 |    199.000 |    1300.000 |   118.182 |
|    34 |   0 |  34 |   0 |   0 |   0 | GET    | /api/organizer/players                | 0.004 |  0.052 |    0.296 | 0.009 |  0.020 |  0.048 |  0.052 |  0.012 |  2941.000 | 106892.000 |  877127.000 | 25797.853 |
|    16 |   0 |  15 |   0 |   1 |   0 | POST   | /api/organizer/player/                | 0.004 |  0.044 |    0.232 | 0.014 |  0.024 |  0.044 |  0.044 |  0.009 |    39.000 |    165.000 |    2379.000 |   148.688 |
|     1 |   0 |   1 |   0 |   0 |   0 | GET    | /api/organizer/competitions           | 0.004 |  0.004 |    0.004 | 0.004 |  0.004 |  0.004 |  0.004 |  0.000 |   177.000 |    177.000 |     177.000 |   177.000 |
|     1 |   0 |   1 |   0 |   0 |   0 | GET    | /css/app.83b4c321.css                 | 0.000 |  0.000 |    0.000 | 0.000 |  0.000 |  0.000 |  0.000 |  0.000 |  4868.000 |   4868.000 |    4868.000 |  4868.000 |
|     1 |   0 |   1 |   0 |   0 |   0 | GET    | /js/app.3a4ec98c.js                   | 0.000 |  0.000 |    0.000 | 0.000 |  0.000 |  0.000 |  0.000 |  0.000 | 33294.000 |  33294.000 |   33294.000 | 33294.000 |
|     1 |   0 |   1 |   0 |   0 |   0 | GET    | /index.html                           | 0.000 |  0.000 |    0.000 | 0.000 |  0.000 |  0.000 |  0.000 |  0.000 |   835.000 |    835.000 |     835.000 |   835.000 |
|     1 |   0 |   0 |   0 |   1 |   0 | GET    | /api/v1/time                          | 0.000 |  0.000 |    0.000 | 0.000 |  0.000 |  0.000 |  0.000 |  0.000 |    39.000 |     39.000 |      39.000 |    39.000 |
+-------+-----+-----+-----+-----+-----+--------+---------------------------------------+-------+--------+----------+-------+--------+--------+--------+--------+-----------+------------+-------------+-----------+


Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 2106  Time=0.05s (106s)  Lock=0.00s (0s)  Rows=83.3 (175390), isucon[isucon]@localhost
  SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = N AND competition_id = 'S' GROUP BY player_id

Count: 12296  Time=0.00s (7s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  REPLACE INTO id_generator (stub) VALUES ('S')

Count: 1  Time=3.97s (3s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  DELETE FROM visit_history WHERE created_at >= 'S'

Count: 37069  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=4.8 (178423), isucon[isucon]@localhost
  #

Count: 871  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO visit_history (player_id, tenant_id, competition_id, created_at, updated_at) VALUES ('S', N, 'S', N, N)

Count: 2163  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (2162), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE name = 'S'

Count: 871  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (871), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE id = N

@minhquang4334
Copy link
Collaborator Author

4920
CREATE INDEX tenant_id_competition_id_idx ON visit_history (tenant_id, competition_id, player_id)

+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+----------+-------+--------+--------+--------+--------+-----------+------------+--------------+-----------+
| COUNT | 1XX | 2XX  | 3XX | 4XX | 5XX | METHOD |                  URI                  |  MIN  |  MAX   |   SUM    |  AVG  |  P90   |  P95   |  P99   | STDDEV | MIN(BODY) | MAX(BODY)  |  SUM(BODY)   | AVG(BODY) |
+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+----------+-------+--------+--------+--------+--------+-----------+------------+--------------+-----------+
|  2285 |   0 | 2177 |   0 | 107 |   1 | GET    | /api/player/competition/              | 0.004 | 26.188 | 2918.216 | 1.277 |  3.784 |  4.812 | 11.780 |  2.429 |     0.000 |  14697.000 | 19408815.000 |  8494.011 |
|  2024 |   0 | 1913 |   0 | 110 |   1 | GET    | /api/player/player/                   | 0.004 | 27.036 | 2775.250 | 1.371 |  3.604 |  4.532 | 11.833 |  2.391 |     0.000 |   2325.000 |  1641421.000 |   810.979 |
|   258 |   0 |  241 |   0 |  17 |   0 | POST   | /api/organizer/competition/           | 0.004 | 11.825 |  253.399 | 0.982 |  3.224 |  3.864 |  8.656 |  1.723 |     0.000 |     62.000 |    10030.000 |    38.876 |
|    49 |   0 |   46 |   0 |   3 |   0 | GET    | /api/admin/tenants/billing?before=xxx | 0.004 | 18.404 |  194.094 | 3.961 | 10.548 | 11.836 | 18.404 |  4.116 |     0.000 |   1565.000 |    67954.000 |  1386.816 |
|    20 |   0 |   20 |   0 |   0 |   0 | POST   | /api/organizer/players/add            | 0.908 |  3.040 |   40.136 | 2.007 |  2.952 |  3.032 |  3.040 |  0.760 | 12150.000 |  26940.000 |   404406.000 | 20220.300 |
|    68 |   0 |   66 |   0 |   2 |   0 | GET    | /api/organizer/billing                | 0.004 |  1.300 |    8.616 | 0.127 |  0.328 |  0.680 |  1.300 |  0.265 |     0.000 |   4656.000 |   124019.000 |  1823.809 |
|   356 |   0 |  329 |   0 |  27 |   0 | GET    | /api/player/competitions              | 0.004 |  0.080 |    6.260 | 0.018 |  0.040 |  0.044 |  0.056 |  0.014 |    39.000 |   2688.000 |   383885.000 |  1078.329 |
|     2 |   0 |    2 |   0 |   0 |   0 | POST   | /initialize                           | 2.084 |  4.136 |    6.220 | 3.110 |  4.136 |  4.136 |  4.136 |  1.026 |    55.000 |     55.000 |      110.000 |    55.000 |
|     3 |   0 |    3 |   0 |   0 |   0 | GET    | /api/admin/tenants/billing            | 0.444 |  2.644 |    3.736 | 1.245 |  2.644 |  2.644 |  2.644 |  0.993 |  1566.000 |   1575.000 |     4715.000 |  1571.667 |
|   114 |   0 |  112 |   0 |   2 |   0 | POST   | /api/organizer/competitions           | 0.004 |  0.068 |    1.924 | 0.017 |  0.032 |  0.044 |  0.056 |  0.011 |    39.000 |    192.000 |    18281.000 |   160.360 |
|    78 |   0 |   78 |   0 |   0 |   0 | GET    | /api/organizer/players                | 0.012 |  0.108 |    0.808 | 0.010 |  0.024 |  0.036 |  0.108 |  0.015 |  2805.000 | 602067.000 |  2486329.000 | 31876.013 |
|    23 |   0 |   13 |   0 |  10 |   0 | POST   | /api/admin/tenants/add                | 0.040 |  0.100 |    0.680 | 0.030 |  0.068 |  0.076 |  0.100 |  0.028 |    39.000 |    199.000 |     2805.000 |   121.957 |
|    32 |   0 |   30 |   0 |   2 |   0 | POST   | /api/organizer/player/                | 0.008 |  0.084 |    0.540 | 0.017 |  0.024 |  0.044 |  0.084 |  0.015 |    39.000 |    165.000 |     4753.000 |   148.531 |
|     2 |   0 |    2 |   0 |   0 |   0 | GET    | /api/organizer/competitions           | 0.004 |  0.004 |    0.008 | 0.004 |  0.004 |  0.004 |  0.004 |  0.000 |   177.000 |    177.000 |      354.000 |   177.000 |
|     2 |   0 |    2 |   0 |   0 |   0 | GET    | /css/app.83b4c321.css                 | 0.000 |  0.000 |    0.000 | 0.000 |  0.000 |  0.000 |  0.000 |  0.000 |  4868.000 |   4868.000 |     9736.000 |  4868.000 |
|     2 |   0 |    2 |   0 |   0 |   0 | GET    | /js/app.3a4ec98c.js                   | 0.000 |  0.000 |    0.000 | 0.000 |  0.000 |  0.000 |  0.000 |  0.000 | 33294.000 |  33294.000 |    66588.000 | 33294.000 |
|     2 |   0 |    2 |   0 |   0 |   0 | GET    | /index.html                           | 0.000 |  0.000 |    0.000 | 0.000 |  0.000 |  0.000 |  0.000 |  0.000 |   835.000 |    835.000 |     1670.000 |   835.000 |
|     1 |   0 |    0 |   0 |   1 |   0 | GET    | /api/v1/time                          | 0.000 |  0.000 |    0.000 | 0.000 |  0.000 |  0.000 |  0.000 |  0.000 |    39.000 |     39.000 |       39.000 |    39.000 |
|     2 |   0 |    2 |   0 |   0 |   0 | GET    | /                                     | 0.000 |  0.000 |    0.000 | 0.000 |  0.000 |  0.000 |  0.000 |  0.000 |   835.000 |    835.000 |     1670.000 |   835.000 |
+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+----------+-------+--------+--------+--------+--------+-----------+------------+--------------+-----------+

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 6516  Time=0.02s (139s)  Lock=0.00s (0s)  Rows=126.1 (821742), isucon[isucon]@localhost
  SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = N AND competition_id = 'S' GROUP BY player_id

Count: 27825  Time=0.00s (12s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  REPLACE INTO id_generator (stub) VALUES ('S')

Count: 2  Time=2.32s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  DELETE FROM visit_history WHERE created_at >= 'S'

Count: 89574  Time=0.00s (3s)  Lock=0.00s (0s)  Rows=9.3 (829234), isucon[isucon]@localhost
  #

Count: 2255  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO visit_history (player_id, tenant_id, competition_id, created_at, updated_at) VALUES ('S', N, 'S', N, N)

Count: 5237  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=1.0 (5235), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE name = 'S'

Count: 2255  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (2255), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE id = N

@minhquang4334
Copy link
Collaborator Author

Score: 5094

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
  11118 isucon    20   0 2399024  50968  11360 S 102.7   1.3   0:19.25 main
  11013 mysql     20   0 2567240 627564  34288 S  65.3  16.5   0:17.37 mysqld
  10732 www-data  20   0   17708  13232   6236 S   5.0   0.3   0:00.65 nginx
    123 root      20   0       0      0      0 D   2.3   0.0   0:09.55 jbd2/nvme0n1p1-

@minhquang4334
Copy link
Collaborator Author

minhquang4334 commented Jul 23, 2022 •
edited
Loading

Score: 9955
Bulk Insert

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
  30597 isucon    20   0 2566764  92548  11452 S 155.0   2.4   1:01.15 main
  30434 mysql     20   0 2567380 658472  33888 S  35.3  17.3   0:31.71 mysqld
  30400 www-data  20   0   19588  15196   6316 S   5.0   0.4   0:02.26 nginx
  30399 www-data  20   0   18276  13936   6288 S   3.0   0.4   0:00.96 nginx
    123 root      20   0       0      0      0 S   0.3   0.0   0:18.27 jbd2/nvme0n1p1-
   1426 root      20   0 1392000  29248  16700 S   0.3   0.8   0:02.69 ssm-agent-w

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 4127  Time=0.01s (32s)  Lock=0.00s (0s)  Rows=105.7 (436107), isucon[isucon]@localhost
  SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = N AND competition_id = 'S' GROUP BY player_id

Count: 20413  Time=0.00s (7s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  REPLACE INTO id_generator (stub) VALUES ('S')

Count: 1  Time=2.30s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  DELETE FROM visit_history WHERE created_at >= 'S'

Count: 5619  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (5618), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE name = 'S'

Count: 1888  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO visit_history (player_id, tenant_id, competition_id, created_at, updated_at) VALUES ('S', N, 'S', N, N)

Count: 1888  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1888), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE id = N

Count: 33  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=106.2 (3504), isucon[isucon]@localhost
  SELECT * FROM tenant ORDER BY id DESC

------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+----------+-------+-------+-------+--------+--------+-----------+------------+--------------+-----------+
| COUNT | 1XX | 2XX  | 3XX | 4XX | 5XX | METHOD |                  URI                  |  MIN  |  MAX   |   SUM    |  AVG  |  P90  |  P95  |  P99   | STDDEV | MIN(BODY) | MAX(BODY)  |  SUM(BODY)   | AVG(BODY) |
+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+----------+-------+-------+-------+--------+--------+-----------+------------+--------------+-----------+
|  3048 |   0 | 2979 |   0 |  69 |   0 | GET    | /api/player/player/                   | 0.004 | 20.180 | 1549.768 | 0.508 | 1.204 | 1.572 |  3.764 |  0.991 |     0.000 |   2146.000 |  3854564.000 |  1264.621 |
|  1905 |   0 | 1870 |   0 |  35 |   0 | GET    | /api/player/competition/              | 0.004 | 19.008 | 1042.544 | 0.547 | 1.200 | 1.896 |  4.588 |  1.151 |     0.000 |  14720.000 | 21829388.000 | 11458.996 |
|    31 |   0 |   29 |   0 |   2 |   0 | GET    | /api/admin/tenants/billing?before=xxx | 0.028 | 21.520 |   87.216 | 2.813 | 3.876 | 4.824 | 21.520 |  3.574 |     0.000 |   1548.000 |    42317.000 |  1365.065 |
|   171 |   0 |  165 |   0 |   6 |   0 | POST   | /api/organizer/competition/           | 0.004 |  3.316 |   34.284 | 0.200 | 0.528 | 0.684 |  2.768 |  0.379 |    21.000 |     62.000 |     6759.000 |    39.526 |
|    12 |   0 |   12 |   0 |   0 |   0 | POST   | /api/organizer/players/add            | 0.996 |  3.492 |   25.060 | 2.088 | 3.140 | 3.492 |  3.492 |  0.763 | 12177.000 |  26983.000 |   244172.000 | 20347.667 |
|    50 |   0 |   48 |   0 |   2 |   0 | GET    | /api/organizer/billing                | 0.004 | 10.204 |   21.360 | 0.427 | 0.632 | 2.016 | 10.204 |  1.518 |     0.000 |   5042.000 |   105842.000 |  2116.840 |
|   279 |   0 |  263 |   0 |  16 |   0 | GET    | /api/player/competitions              | 0.008 |  0.084 |    6.048 | 0.022 | 0.052 | 0.060 |  0.076 |  0.018 |    39.000 |   2669.000 |   320207.000 |  1147.695 |
|     1 |   0 |    1 |   0 |   0 |   0 | POST   | /initialize                           | 3.632 |  3.632 |    3.632 | 3.632 | 3.632 | 3.632 |  3.632 |  0.000 |    55.000 |     55.000 |       55.000 |    55.000 |
|     2 |   0 |    2 |   0 |   0 |   0 | GET    | /api/admin/tenants/billing            | 0.756 |  1.444 |    2.200 | 1.100 | 1.444 | 1.444 |  1.444 |  0.344 |  1559.000 |   1570.000 |     3129.000 |  1564.500 |
|    77 |   0 |   76 |   0 |   1 |   0 | POST   | /api/organizer/competitions           | 0.004 |  0.108 |    1.908 | 0.025 | 0.048 | 0.056 |  0.108 |  0.020 |    39.000 |    186.000 |    12418.000 |   161.273 |
|    58 |   0 |   58 |   0 |   0 |   0 | GET    | /api/organizer/players                | 0.004 |  0.072 |    0.752 | 0.013 | 0.036 | 0.052 |  0.072 |  0.015 |  3100.000 | 602391.000 |  2085044.000 | 35949.034 |
|    12 |   0 |    7 |   0 |   5 |   0 | POST   | /api/admin/tenants/add                | 0.080 |  0.100 |    0.464 | 0.039 | 0.088 | 0.100 |  0.100 |  0.036 |    39.000 |    199.000 |     1498.000 |   124.833 |
|    18 |   0 |   17 |   0 |   1 |   0 | POST   | /api/organizer/player/                | 0.004 |  0.052 |    0.276 | 0.015 | 0.032 | 0.052 |  0.052 |  0.012 |    39.000 |    162.000 |     2696.000 |   149.778 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /css/app.83b4c321.css                 | 0.000 |  0.000 |    0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  4868.000 |   4868.000 |     4868.000 |  4868.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /js/app.3a4ec98c.js                   | 0.000 |  0.000 |    0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 | 33294.000 |  33294.000 |    33294.000 | 33294.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /index.html                           | 0.000 |  0.000 |    0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 |   835.000 |    835.000 |      835.000 |   835.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /api/organizer/competitions           | 0.000 |  0.000 |    0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 |   177.000 |    177.000 |      177.000 |   177.000 |
+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+----------+-------+-------+-------+--------+--------+-----------+------------+--------------+-----------+

@henchiyb
Copy link
Owner

henchiyb commented Jul 23, 2022 •
edited
Loading

Score: 11884
id_genarator削除

+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+----------+-------+-------+-------+--------+--------+-----------+------------+--------------+-----------+
| COUNT | 1XX | 2XX  | 3XX | 4XX | 5XX | METHOD |                  URI                  |  MIN  |  MAX   |   SUM    |  AVG  |  P90  |  P95  |  P99   | STDDEV | MIN(BODY) | MAX(BODY)  |  SUM(BODY)   | AVG(BODY) |
+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+----------+-------+-------+-------+--------+--------+-----------+------------+--------------+-----------+
|  5029 |   0 | 4977 |   0 |  52 |   0 | GET    | /api/player/player/                   | 0.008 | 22.848 | 1283.200 | 0.255 | 0.360 | 1.624 |  3.768 |  0.982 |     0.000 |   2124.000 |  5922926.000 |  1177.754 |
|  2823 |   0 | 2790 |   0 |  33 |   0 | GET    | /api/player/competition/              | 0.004 | 22.316 |  585.556 | 0.207 | 0.224 | 0.680 |  3.528 |  1.039 |     0.000 |  12265.000 | 26362948.000 |  9338.628 |
|    35 |   0 |   34 |   0 |   1 |   0 | GET    | /api/admin/tenants/billing?before=xxx | 0.004 | 20.316 |   87.492 | 2.500 | 4.392 | 4.420 | 20.316 |  3.239 |     0.000 |    989.000 |    31536.000 |   901.029 |
|    65 |   0 |   64 |   0 |   1 |   0 | GET    | /api/organizer/billing                | 0.004 | 29.788 |   38.224 | 0.588 | 0.212 | 1.124 | 29.788 |  3.667 |     0.000 |   6133.000 |   154929.000 |  2383.523 |
|    13 |   0 |   13 |   0 |   0 |   0 | POST   | /api/organizer/players/add            | 0.784 |  2.016 |   16.664 | 1.282 | 1.820 | 2.016 |  2.016 |  0.421 | 10947.000 |  24073.000 |   229336.000 | 17641.231 |
|   376 |   0 |  359 |   0 |  17 |   0 | GET    | /api/player/competitions              | 0.008 |  0.112 |    8.852 | 0.024 | 0.048 | 0.060 |  0.096 |  0.019 |    30.000 |   3220.000 |   474848.000 |  1262.894 |
|   208 |   0 |  202 |   0 |   6 |   0 | POST   | /api/organizer/competition/           | 0.004 |  0.708 |    8.468 | 0.041 | 0.068 | 0.104 |  0.572 |  0.081 |    16.000 |     53.000 |     5653.000 |    27.178 |
|     1 |   0 |    1 |   0 |   0 |   0 | POST   | /initialize                           | 3.700 |  3.700 |    3.700 | 3.700 | 3.700 | 3.700 |  3.700 |  0.000 |    37.000 |     37.000 |       37.000 |    37.000 |
|     2 |   0 |    2 |   0 |   0 |   0 | GET    | /api/admin/tenants/billing            | 0.744 |  1.440 |    2.184 | 1.092 | 1.440 | 1.440 |  1.440 |  0.348 |   999.000 |   1030.000 |     2029.000 |  1014.500 |
|    95 |   0 |   94 |   0 |   1 |   0 | POST   | /api/organizer/competitions           | 0.004 |  0.056 |    1.456 | 0.015 | 0.032 | 0.036 |  0.056 |  0.011 |    30.000 |    179.000 |    14026.000 |   147.642 |
|    75 |   0 |   75 |   0 |   0 |   0 | GET    | /api/organizer/players                | 0.004 |  0.056 |    0.624 | 0.008 | 0.020 | 0.024 |  0.056 |  0.010 |  1965.000 | 382228.000 |  2060485.000 | 27473.133 |
|    12 |   0 |    7 |   0 |   5 |   0 | POST   | /api/admin/tenants/add                | 0.008 |  0.068 |    0.384 | 0.032 | 0.064 | 0.068 |  0.068 |  0.026 |    30.000 |    145.000 |     1091.000 |    90.917 |
|    20 |   0 |   19 |   0 |   1 |   0 | POST   | /api/organizer/player/                | 0.004 |  0.052 |    0.308 | 0.015 | 0.028 | 0.040 |  0.052 |  0.012 |    30.000 |    142.000 |     2177.000 |   108.850 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /css/app.83b4c321.css                 | 0.000 |  0.000 |    0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  4868.000 |   4868.000 |     4868.000 |  4868.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /js/app.3a4ec98c.js                   | 0.000 |  0.000 |    0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 | 33294.000 |  33294.000 |    33294.000 | 33294.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /index.html                           | 0.000 |  0.000 |    0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 |   835.000 |    835.000 |      835.000 |   835.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /api/organizer/competitions           | 0.000 |  0.000 |    0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 |   142.000 |    142.000 |      142.000 |   142.000 |
+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+----------+-------+-------+-------+--------+--------+-----------+------------+--------------+-----------+
Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 4773  Time=0.01s (37s)  Lock=0.00s (0s)  Rows=120.8 (576593), isucon[isucon]@localhost
  SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = N AND competition_id = 'S' GROUP BY player_id

Count: 1  Time=2.37s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  DELETE FROM visit_history WHERE created_at >= 'S'

Count: 8705  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=1.0 (8704), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE name = 'S'

Count: 2805  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO visit_history (player_id, tenant_id, competition_id, created_at, updated_at) VALUES ('S', N, 'S', N, N)

Count: 2805  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (2805), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE id = N

Count: 37  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=106.2 (3928), isucon[isucon]@localhost
  SELECT * FROM tenant ORDER BY id DESC

Count: 1  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost

@minhquang4334
Copy link
Collaborator Author

minhquang4334 commented Jul 23, 2022 •
edited
Loading

Score 12472

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
  54907 isucon    20   0 2581840  67148  11444 R 106.3   1.8   0:34.65 main
  54745 mysql     20   0 2568412 656368  33856 S  76.0  17.3   0:36.40 mysqld
  54667 www-data  20   0   19664  15472   6332 S   4.3   0.4   0:01.89 nginx
  54666 www-data  20   0   18288  13840   6300 S   3.0   0.4   0:00.93 nginx

+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+---------+-------+-------+--------+--------+--------+-----------+------------+--------------+-----------+
| COUNT | 1XX | 2XX  | 3XX | 4XX | 5XX | METHOD |                  URI                  |  MIN  |  MAX   |   SUM   |  AVG  |  P90  |  P95   |  P99   | STDDEV | MIN(BODY) | MAX(BODY)  |  SUM(BODY)   | AVG(BODY) |
+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+---------+-------+-------+--------+--------+--------+-----------+------------+--------------+-----------+
|  5217 |   0 | 5190 |   0 |  27 |   0 | GET    | /api/player/player/                   | 0.016 |  2.788 | 627.284 | 0.120 | 0.372 |  0.644 |  1.604 |  0.287 |     0.000 |   1686.000 |  4434584.000 |   850.026 |
|  3046 |   0 | 3009 |   0 |  37 |   0 | GET    | /api/player/competition/              | 0.008 |  2.460 | 572.804 | 0.188 | 0.568 |  0.864 |  1.832 |  0.365 |     0.000 |   9176.000 | 20046602.000 |  6581.288 |
|    36 |   0 |   34 |   0 |   2 |   0 | GET    | /api/admin/tenants/billing?before=xxx | 0.004 | 16.388 |  84.748 | 2.354 | 3.468 | 10.760 | 16.388 |  2.882 |     0.000 |    988.000 |    31322.000 |   870.056 |
|   213 |   0 |  206 |   0 |   7 |   0 | POST   | /api/organizer/competition/           | 0.004 |  2.396 |  46.536 | 0.218 | 0.564 |  0.856 |  1.844 |  0.360 |     0.000 |     53.000 |     5768.000 |    27.080 |
|    18 |   0 |   18 |   0 |   0 |   0 | POST   | /api/organizer/players/add            | 1.076 |  4.660 |  36.808 | 2.045 | 3.472 |  4.660 |  4.660 |  0.908 |  7717.000 |  15704.000 |   225291.000 | 12516.167 |
|    66 |   0 |   65 |   0 |   1 |   0 | GET    | /api/organizer/billing                | 0.004 | 20.392 |  23.892 | 0.362 | 0.124 |  0.312 | 20.392 |  2.486 |     0.000 |   4597.000 |   127689.000 |  1934.682 |
|   402 |   0 |  384 |   0 |  18 |   0 | GET    | /api/player/competitions              | 0.004 |  0.080 |   6.600 | 0.016 | 0.036 |  0.044 |  0.056 |  0.013 |    30.000 |   1964.000 |   381732.000 |   949.582 |
|     2 |   0 |    1 |   0 |   0 |   1 | POST   | /initialize                           | 6.328 |  6.328 |   6.328 | 3.164 | 6.328 |  6.328 |  6.328 |  3.164 |    37.000 |    166.000 |      203.000 |   101.500 |
|    97 |   0 |   96 |   0 |   1 |   0 | POST   | /api/organizer/competitions           | 0.004 |  0.080 |   1.516 | 0.016 | 0.028 |  0.036 |  0.080 |  0.011 |    30.000 |    145.000 |    11259.000 |   116.072 |
|     2 |   0 |    2 |   0 |   0 |   0 | GET    | /api/admin/tenants/billing            | 0.468 |  0.760 |   1.228 | 0.614 | 0.760 |  0.760 |  0.760 |  0.146 |   995.000 |    995.000 |     1990.000 |   995.000 |
|    77 |   0 |   77 |   0 |   0 |   0 | GET    | /api/organizer/players                | 0.004 |  0.096 |   0.856 | 0.011 | 0.032 |  0.048 |  0.096 |  0.015 |  1770.000 | 394227.000 |  2392885.000 | 31076.429 |
|    12 |   0 |    7 |   0 |   5 |   0 | POST   | /api/admin/tenants/add                | 0.008 |  0.112 |   0.524 | 0.044 | 0.108 |  0.112 |  0.112 |  0.039 |    30.000 |    145.000 |     1057.000 |    88.083 |
|    21 |   0 |   20 |   0 |   1 |   0 | POST   | /api/organizer/player/                | 0.004 |  0.040 |   0.288 | 0.014 | 0.024 |  0.028 |  0.040 |  0.009 |    30.000 |    118.000 |     2213.000 |   105.381 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /css/app.83b4c321.css                 | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  0.000 |  4868.000 |   4868.000 |     4868.000 |  4868.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /js/app.3a4ec98c.js                   | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  0.000 | 33294.000 |  33294.000 |    33294.000 | 33294.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /index.html                           | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  0.000 |   835.000 |    835.000 |      835.000 |   835.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /api/organizer/competitions           | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  0.000 |   110.000 |    110.000 |      110.000 |   110.000 |
+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+---------+-------+-------+--------+--------+--------+-----------+------------+--------------+-----------+

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 5000  Time=0.01s (54s)  Lock=0.00s (0s)  Rows=151.1 (755475), isucon[isucon]@localhost
  SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = N AND competition_id = 'S' GROUP BY player_id

Count: 30649  Time=0.00s (16s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  REPLACE INTO id_generator (stub) VALUES ('S')

Count: 1  Time=2.64s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  DELETE FROM visit_history WHERE created_at >= 'S'

Count: 3027  Time=0.00s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO visit_history (player_id, tenant_id, competition_id, created_at, updated_at) VALUES ('S', N, 'S', N, N)

Count: 9158  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=1.0 (9157), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE name = 'S'

Count: 3027  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (3027), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE id = N

Count: 38  Time=0.00s (0s)  Lock=0.00s (0s)

@henchiyb
Copy link
Owner

henchiyb commented Jul 23, 2022 •
edited
Loading

Score: 15175
N+1解約

+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+---------+-------+-------+--------+--------+--------+-----------+------------+--------------+-----------+
| COUNT | 1XX | 2XX  | 3XX | 4XX | 5XX | METHOD |                  URI                  |  MIN  |  MAX   |   SUM   |  AVG  |  P90  |  P95   |  P99   | STDDEV | MIN(BODY) | MAX(BODY)  |  SUM(BODY)   | AVG(BODY) |
+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+---------+-------+-------+--------+--------+--------+-----------+------------+--------------+-----------+
|  6728 |   0 | 6660 |   0 |  68 |   0 | GET    | /api/player/player/                   | 0.004 |  3.676 | 502.972 | 0.075 | 0.080 |  0.460 |  1.480 |  0.259 |     0.000 |   2063.000 |  6772934.000 |  1006.679 |
|  3758 |   0 | 3719 |   0 |  39 |   0 | GET    | /api/player/competition/              | 0.004 |  1.868 | 222.996 | 0.059 | 0.104 |  0.232 |  1.020 |  0.171 |     0.000 |  12272.000 | 33989312.000 |  9044.522 |
|    37 |   0 |   36 |   0 |   1 |   0 | GET    | /api/admin/tenants/billing?before=xxx | 0.744 | 14.840 |  83.308 | 2.252 | 3.040 | 14.696 | 14.840 |  3.090 |     0.000 |    988.000 |    33204.000 |   897.405 |
|    78 |   0 |   77 |   0 |   1 |   0 | GET    | /api/organizer/billing                | 0.004 | 17.708 |  38.340 | 0.492 | 1.124 |  1.712 | 17.708 |  2.101 |     0.000 |   7006.000 |   238905.000 |  3062.885 |
|    21 |   0 |   20 |   0 |   1 |   0 | POST   | /api/organizer/players/add            | 0.828 |  5.160 |  32.368 | 1.541 | 1.932 |  2.028 |  5.160 |  0.880 |     0.000 |  23473.000 |   362772.000 | 17274.857 |
|   491 |   0 |  470 |   0 |  21 |   0 | GET    | /api/player/competitions              | 0.004 |  1.052 |  12.256 | 0.025 | 0.048 |  0.052 |  0.096 |  0.058 |     0.000 |   3220.000 |   764713.000 |  1557.460 |
|   242 |   0 |  235 |   0 |   7 |   0 | POST   | /api/organizer/competition/           | 0.004 |  0.928 |  10.304 | 0.043 | 0.088 |  0.124 |  0.328 |  0.079 |     0.000 |     53.000 |     6545.000 |    27.045 |
|     1 |   0 |    1 |   0 |   0 |   0 | POST   | /initialize                           | 6.020 |  6.020 |   6.020 | 6.020 | 6.020 |  6.020 |  6.020 |  0.000 |    37.000 |     37.000 |       37.000 |    37.000 |
|   111 |   0 |  110 |   0 |   1 |   0 | POST   | /api/organizer/competitions           | 0.004 |  0.252 |   2.176 | 0.020 | 0.036 |  0.040 |  0.056 |  0.025 |    30.000 |    182.000 |    16523.000 |   148.856 |
|     2 |   0 |    2 |   0 |   0 |   0 | GET    | /api/admin/tenants/billing            | 0.596 |  0.740 |   1.336 | 0.668 | 0.740 |  0.740 |  0.740 |  0.072 |  1000.000 |   1003.000 |     2003.000 |  1001.500 |
|    90 |   0 |   90 |   0 |   0 |   0 | GET    | /api/organizer/players                | 0.000 |  0.120 |   1.224 | 0.014 | 0.028 |  0.048 |  0.120 |  0.018 |  1862.000 | 402827.000 |  3905284.000 | 43392.044 |
|    12 |   0 |    7 |   0 |   5 |   0 | POST   | /api/admin/tenants/add                | 0.004 |  0.084 |   0.412 | 0.034 | 0.064 |  0.084 |  0.084 |  0.030 |    30.000 |    142.000 |     1065.000 |    88.750 |
|    23 |   0 |   22 |   0 |   1 |   0 | POST   | /api/organizer/player/                | 0.008 |  0.108 |   0.332 | 0.014 | 0.016 |  0.020 |  0.108 |  0.020 |    30.000 |    142.000 |     2513.000 |   109.261 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /css/app.83b4c321.css                 | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  0.000 |  4868.000 |   4868.000 |     4868.000 |  4868.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /js/app.3a4ec98c.js                   | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  0.000 | 33294.000 |  33294.000 |    33294.000 | 33294.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /index.html                           | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  0.000 |   835.000 |    835.000 |      835.000 |   835.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /api/organizer/competitions           | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  0.000 |   142.000 |    142.000 |      142.000 |   142.000 |
+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+---------+-------+-------+--------+--------+--------+-----------+------------+--------------+-----------+
➜  isucon12q git:(master) make get-slowlog-1

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 5540  Time=0.01s (52s)  Lock=0.00s (0s)  Rows=145.1 (803600), isucon[isucon]@localhost
  SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = N AND competition_id = 'S' GROUP BY player_id

Count: 1  Time=2.32s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  DELETE FROM visit_history WHERE created_at >= 'S'

Count: 3737  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO visit_history (player_id, tenant_id, competition_id, created_at, updated_at) VALUES ('S', N, 'S', N, N)

Count: 11543  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=1.0 (11542), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE name = 'S'

Count: 3737  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (3737), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE id = N

Count: 2506  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  #

Count: 39  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=106.3 (4144), isucon[isucon]@localhost
  SELECT * FROM tenant ORDER BY id DESC

@henchiyb
Copy link
Owner

henchiyb commented Jul 23, 2022 •
edited
Loading

Score: 18581
Bulk Insert

+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+---------+-------+-------+--------+--------+--------+-----------+------------+--------------+-----------+
| COUNT | 1XX | 2XX  | 3XX | 4XX | 5XX | METHOD |                  URI                  |  MIN  |  MAX   |   SUM   |  AVG  |  P90  |  P95   |  P99   | STDDEV | MIN(BODY) | MAX(BODY)  |  SUM(BODY)   | AVG(BODY) |
+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+---------+-------+-------+--------+--------+--------+-----------+------------+--------------+-----------+
|  8704 |   0 | 8650 |   0 |  54 |   0 | GET    | /api/player/player/                   | 0.004 |  0.920 | 250.480 | 0.029 | 0.076 |  0.116 |  0.220 |  0.046 |     0.000 |   2123.000 |  8084210.000 |   928.793 |
|  4355 |   0 | 4317 |   0 |  38 |   0 | GET    | /api/player/competition/              | 0.004 |  1.024 | 201.608 | 0.046 | 0.116 |  0.156 |  0.308 |  0.075 |     0.000 |  12283.000 | 40785990.000 |  9365.325 |
|    36 |   0 |   34 |   0 |   2 |   0 | GET    | /api/admin/tenants/billing?before=xxx | 0.004 | 16.256 |  84.792 | 2.355 | 3.400 | 14.224 | 16.256 |  3.246 |     0.000 |    988.000 |    31387.000 |   871.861 |
|    90 |   0 |   89 |   0 |   1 |   0 | GET    | /api/organizer/billing                | 0.004 | 20.320 |  40.444 | 0.449 | 0.556 |  0.836 | 20.320 |  2.266 |     0.000 |   6489.000 |   260072.000 |  2889.689 |
|   566 |   0 |  547 |   0 |  19 |   0 | GET    | /api/player/competitions              | 0.004 |  0.164 |  14.556 | 0.026 | 0.052 |  0.064 |  0.092 |  0.020 |    30.000 |   3303.000 |   877563.000 |  1550.465 |
|   265 |   0 |  259 |   0 |   6 |   0 | POST   | /api/organizer/competition/           | 0.004 |  0.960 |  12.364 | 0.047 | 0.100 |  0.124 |  0.348 |  0.077 |    16.000 |     53.000 |     7167.000 |    27.045 |
|     1 |   0 |    1 |   0 |   0 |   0 | POST   | /initialize                           | 7.244 |  7.244 |   7.244 | 7.244 | 7.244 |  7.244 |  7.244 |  0.000 |    37.000 |     37.000 |       37.000 |    37.000 |
|   122 |   0 |  121 |   0 |   1 |   0 | POST   | /api/organizer/competitions           | 0.004 |  0.792 |   2.988 | 0.024 | 0.036 |  0.044 |  0.072 |  0.071 |    30.000 |    180.000 |    18126.000 |   148.574 |
|   101 |   0 |  101 |   0 |   0 |   0 | GET    | /api/organizer/players                | 0.004 |  0.172 |   2.332 | 0.023 | 0.068 |  0.108 |  0.160 |  0.037 |  1770.000 | 482694.000 |  8051238.000 | 79715.228 |
|     2 |   0 |    2 |   0 |   0 |   0 | GET    | /api/admin/tenants/billing            | 0.560 |  0.744 |   1.304 | 0.652 | 0.744 |  0.744 |  0.744 |  0.092 |   964.000 |    984.000 |     1948.000 |   974.000 |
|    31 |   0 |   31 |   0 |   0 |   0 | POST   | /api/organizer/players/add            | 0.008 |  0.088 |   0.748 | 0.024 | 0.052 |  0.076 |  0.088 |  0.019 | 10947.000 |  24073.000 |   595054.000 | 19195.290 |
|    12 |   0 |    7 |   0 |   5 |   0 | POST   | /api/admin/tenants/add                | 0.048 |  0.136 |   0.568 | 0.047 | 0.128 |  0.136 |  0.136 |  0.047 |    30.000 |    132.000 |     1026.000 |    85.500 |
|    22 |   0 |   21 |   0 |   1 |   0 | POST   | /api/organizer/player/                | 0.004 |  0.088 |   0.348 | 0.016 | 0.020 |  0.024 |  0.088 |  0.017 |    30.000 |    142.000 |     2420.000 |   110.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /css/app.83b4c321.css                 | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  0.000 |  4868.000 |   4868.000 |     4868.000 |  4868.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /js/app.3a4ec98c.js                   | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  0.000 | 33294.000 |  33294.000 |    33294.000 | 33294.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /index.html                           | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  0.000 |   835.000 |    835.000 |      835.000 |   835.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /api/organizer/competitions           | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  0.000 |   142.000 |    142.000 |      142.000 |   142.000 |
+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+---------+-------+-------+--------+--------+--------+-----------+------------+--------------+-----------+
➜  isucon12q git:(cached-viewr) make get-slowlog-1

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 5500  Time=0.01s (50s)  Lock=0.00s (0s)  Rows=135.9 (747469), isucon[isucon]@localhost
  SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = N AND competition_id = 'S' GROUP BY player_id

Count: 1  Time=3.48s (3s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  DELETE FROM visit_history WHERE created_at >= 'S'

Count: 4335  Time=0.00s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO visit_history (player_id, tenant_id, competition_id, created_at, updated_at) VALUES ('S', N, 'S', N, N)

Count: 14257  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=1.0 (14256), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE name = 'S'

Count: 4335  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (4335), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE id = N

Count: 1  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  DELETE FROM tenant WHERE id > N

Count: 3332  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost

@minhquang4334
Copy link
Collaborator Author

Score: 19441
Cache Competition Finished

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
  78513 isucon    20   0 2254800  75268  11300 S 119.9   2.0   0:40.03 main
  78402 mysql     20   0 2567528 660704  33268 S  64.1  17.4   0:28.99 mysqld
  78318 www-data  20   0   19648  15252   6380 S   6.6   0.4   0:02.20 nginx
  78317 www-data  20   0   19324  14960   6340 R   6.0   0.4   0:01.55 nginx
     13 root      20   0       0      0      0 S   0.3   0.0   0:01.26 ksoftirqd

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 3779  Time=0.01s (46s)  Lock=0.00s (0s)  Rows=179.0 (676478), isucon[isucon]@localhost
  SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = N AND competition_id = 'S' GROUP BY player_id

Count: 1  Time=2.33s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  DELETE FROM visit_history WHERE created_at >= 'S'

Count: 14908  Time=0.00s (2s)  Lock=0.00s (0s)  Rows=1.0 (14907), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE name = 'S'

Count: 4477  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO visit_history (player_id, tenant_id, competition_id, created_at, updated_at) VALUES ('S', N, 'S', N, N)

Count: 4477  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (4477), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE id = N

Count: 53  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=106.8 (5662), isucon[isucon]@localhost
  SELECT * FROM tenant ORDER BY id DESC

Count: 2981  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost

➜  isucon12q git:(fix_billing) make get-alp-1                       
+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+---------+-------+-------+-------+--------+--------+-----------+------------+--------------+-----------+
| COUNT | 1XX | 2XX  | 3XX | 4XX | 5XX | METHOD |                  URI                  |  MIN  |  MAX   |   SUM   |  AVG  |  P90  |  P95  |  P99   | STDDEV | MIN(BODY) | MAX(BODY)  |  SUM(BODY)   | AVG(BODY) |
+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+---------+-------+-------+-------+--------+--------+-----------+------------+--------------+-----------+
|  9113 |   0 | 9068 |   0 |  45 |   0 | GET    | /api/player/player/                   | 0.004 |  0.876 | 437.817 | 0.048 | 0.120 | 0.220 |  0.476 |  0.087 |     0.000 |   2035.000 |  8249542.000 |   905.250 |
|  4497 |   0 | 4452 |   0 |  45 |   0 | GET    | /api/player/competition/              | 0.004 |  0.748 | 314.278 | 0.070 | 0.172 | 0.284 |  0.556 |  0.105 |     0.000 |  12280.000 | 44344491.000 |  9860.905 |
|    50 |   0 |   48 |   0 |   2 |   0 | GET    | /api/admin/tenants/billing?before=xxx | 0.020 | 13.736 |  78.546 | 1.571 | 2.104 | 5.776 | 13.736 |  2.114 |     0.000 |    999.000 |    43520.000 |   870.400 |
|    99 |   0 |   98 |   0 |   1 |   0 | GET    | /api/organizer/billing                | 0.004 | 10.141 |  37.938 | 0.383 | 0.392 | 0.744 | 10.141 |  1.611 |     0.000 |   8961.000 |   330293.000 |  3336.293 |
|   613 |   0 |  594 |   0 |  19 |   0 | GET    | /api/player/competitions              | 0.004 |  0.228 |  17.716 | 0.029 | 0.060 | 0.076 |  0.140 |  0.027 |    30.000 |   4275.000 |  1083034.000 |  1766.777 |
|   286 |   0 |  280 |   0 |   6 |   0 | POST   | /api/organizer/competition/           | 0.004 |  0.592 |  15.178 | 0.053 | 0.108 | 0.176 |  0.496 |  0.078 |    16.000 |     53.000 |     7721.000 |    26.997 |
|     1 |   0 |    1 |   0 |   0 |   0 | POST   | /initialize                           | 6.124 |  6.124 |   6.124 | 6.124 | 6.124 | 6.124 |  6.124 |  0.000 |    37.000 |     37.000 |       37.000 |    37.000 |
|   112 |   0 |  112 |   0 |   0 |   0 | GET    | /api/organizer/players                | 0.016 |  0.396 |   3.096 | 0.028 | 0.068 | 0.124 |  0.180 |  0.049 |  1863.000 | 498273.000 |  8704534.000 | 77719.054 |
|   133 |   0 |  132 |   0 |   1 |   0 | POST   | /api/organizer/competitions           | 0.004 |  0.180 |   2.705 | 0.020 | 0.044 | 0.052 |  0.144 |  0.023 |    30.000 |    180.000 |    19749.000 |   148.489 |
|     3 |   0 |    3 |   0 |   0 |   0 | GET    | /api/admin/tenants/billing            | 0.448 |  0.740 |   1.916 | 0.639 | 0.740 | 0.740 |  0.740 |  0.135 |  1010.000 |   1018.000 |     3045.000 |  1015.000 |
|    32 |   0 |   32 |   0 |   0 |   0 | POST   | /api/organizer/players/add            | 0.008 |  0.172 |   0.972 | 0.030 | 0.056 | 0.088 |  0.172 |  0.031 | 10954.000 |  24072.000 |   632308.000 | 19759.625 |
|    13 |   0 |    8 |   0 |   5 |   0 | POST   | /api/admin/tenants/add                | 0.008 |  0.124 |   0.548 | 0.042 | 0.084 | 0.124 |  0.124 |  0.038 |    30.000 |    156.000 |     1207.000 |    92.846 |
|    22 |   0 |   21 |   0 |   1 |   0 | POST   | /api/organizer/player/                | 0.004 |  0.036 |   0.328 | 0.015 | 0.024 | 0.036 |  0.036 |  0.009 |    30.000 |    142.000 |     2396.000 |   108.909 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /css/app.83b4c321.css                 | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  4868.000 |   4868.000 |     4868.000 |  4868.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /js/app.3a4ec98c.js                   | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 | 33294.000 |  33294.000 |    33294.000 | 33294.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /index.html                           | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 |   835.000 |    835.000 |      835.000 |   835.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /api/organizer/competitions           | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 |   142.000 |    142.000 |      142.000 |   142.000 |
+-------+-----+------+-----+-----+-----+--------+---------------------------------------+-------+--------+---------+-------+-------+-------+--------+--------+-----------+------------+--------------+-----------+

@minhquang4334
Copy link
Collaborator Author

Score: 22705

cache billing and async calculate

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
  88509 isucon    20   0 1892944  66176  11400 R 109.7   1.7   0:20.93 main
  88396 mysql     20   0 2567524 766972  33456 S  76.7  20.2   0:19.62 mysqld
  88301 www-data  20   0   18908  14448   6080 R   6.0   0.4   0:01.31 nginx
  88300 www-data  20   0   17980  13340   6020 S   3.3   0.4   0:00.76 nginx

+-------+-----+-------+-----+-----+-----+--------+---------------------------------------+-------+--------+---------+-------+-------+-------+--------+--------+-----------+------------+--------------+-----------+
| COUNT | 1XX |  2XX  | 3XX | 4XX | 5XX | METHOD |                  URI                  |  MIN  |  MAX   |   SUM   |  AVG  |  P90  |  P95  |  P99   | STDDEV | MIN(BODY) | MAX(BODY)  |  SUM(BODY)   | AVG(BODY) |
+-------+-----+-------+-----+-----+-----+--------+---------------------------------------+-------+--------+---------+-------+-------+-------+--------+--------+-----------+------------+--------------+-----------+
| 10329 |   0 | 10300 |   0 |  29 |   0 | GET    | /api/player/player/                   | 0.004 |  0.796 | 332.152 | 0.032 | 0.092 | 0.160 |  0.312 |  0.072 |     0.000 |   2148.000 |  9516826.000 |   921.370 |
|  5687 |   0 |  5664 |   0 |  23 |   0 | GET    | /api/player/competition/              | 0.004 |  0.916 | 282.600 | 0.050 | 0.128 | 0.192 |  0.508 |  0.091 |     0.000 |  12269.000 | 51420645.000 |  9041.787 |
|    56 |   0 |    55 |   0 |   1 |   0 | GET    | /api/admin/tenants/billing?before=xxx | 0.024 | 11.284 |  74.860 | 1.337 | 2.128 | 7.032 | 11.284 |  2.054 |     0.000 |    999.000 |    49014.000 |   875.250 |
|   112 |   0 |   112 |   0 |   0 |   0 | GET    | /api/organizer/billing                | 0.004 | 10.556 |  30.120 | 0.269 | 0.060 | 0.108 |  8.684 |  1.506 |   242.000 |   6810.000 |   315668.000 |  2818.464 |
|   762 |   0 |   743 |   0 |  19 |   0 | GET    | /api/player/competitions              | 0.012 |  0.144 |  21.844 | 0.029 | 0.064 | 0.088 |  0.128 |  0.028 |    30.000 |   3395.000 |  1112662.000 |  1460.186 |
|   316 |   0 |   310 |   0 |   6 |   0 | POST   | /api/organizer/competition/           | 0.004 |  0.776 |  15.124 | 0.048 | 0.100 | 0.156 |  0.536 |  0.085 |    16.000 |     53.000 |     8518.000 |    26.956 |
|     1 |   0 |     1 |   0 |   0 |   0 | POST   | /initialize                           | 7.836 |  7.836 |   7.836 | 7.836 | 7.836 | 7.836 |  7.836 |  0.000 |    37.000 |     37.000 |       37.000 |    37.000 |
|   128 |   0 |   128 |   0 |   0 |   0 | GET    | /api/organizer/players                | 0.004 |  0.216 |   3.000 | 0.023 | 0.072 | 0.108 |  0.200 |  0.037 |  1965.000 | 541804.000 | 11017734.000 | 86076.047 |
|   149 |   0 |   148 |   0 |   1 |   0 | POST   | /api/organizer/competitions           | 0.004 |  0.104 |   2.460 | 0.017 | 0.040 | 0.048 |  0.072 |  0.015 |    30.000 |    173.000 |    22101.000 |   148.329 |
|     4 |   0 |     4 |   0 |   0 |   0 | GET    | /api/admin/tenants/billing            | 0.196 |  0.772 |   2.076 | 0.519 | 0.772 | 0.772 |  0.772 |  0.245 |  1023.000 |   1050.000 |     4152.000 |  1038.000 |
|    39 |   0 |    39 |   0 |   0 |   0 | POST   | /api/organizer/players/add            | 0.004 |  0.352 |   1.200 | 0.031 | 0.052 | 0.108 |  0.352 |  0.056 | 10954.000 |  24072.000 |   759209.000 | 19466.897 |
|    15 |   0 |    10 |   0 |   5 |   0 | POST   | /api/admin/tenants/add                | 0.040 |  0.284 |   1.052 | 0.070 | 0.232 | 0.284 |  0.284 |  0.082 |    30.000 |    148.000 |     1483.000 |    98.867 |
|    22 |   0 |    21 |   0 |   1 |   0 | POST   | /api/organizer/player/                | 0.008 |  0.036 |   0.264 | 0.012 | 0.024 | 0.032 |  0.036 |  0.009 |    30.000 |    142.000 |     2371.000 |   107.773 |
|     1 |   0 |     1 |   0 |   0 |   0 | GET    | /css/app.83b4c321.css                 | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 |  4868.000 |   4868.000 |     4868.000 |  4868.000 |
|     1 |   0 |     1 |   0 |   0 |   0 | GET    | /js/app.3a4ec98c.js                   | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 | 33294.000 |  33294.000 |    33294.000 | 33294.000 |
|     1 |   0 |     1 |   0 |   0 |   0 | GET    | /index.html                           | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 |   835.000 |    835.000 |      835.000 |   835.000 |
|     1 |   0 |     1 |   0 |   0 |   0 | GET    | /api/organizer/competitions           | 0.000 |  0.000 |   0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  0.000 |   142.000 |    142.000 |      142.000 |   142.000 |
+-------+-----+-------+-----+-----+-----+--------+---------------------------------------+-------+--------+---------+-------+-------+-------+--------+--------+-----------+------------+--------------+-----------+

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 3886  Time=0.01s (45s)  Lock=0.00s (0s)  Rows=174.3 (677394), isucon[isucon]@localhost
  SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = N AND competition_id = 'S' GROUP BY player_id

Count: 1  Time=4.05s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  DELETE FROM visit_history WHERE created_at >= 'S'

Count: 5667  Time=0.00s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO visit_history (player_id, tenant_id, competition_id, created_at, updated_at) VALUES ('S', N, 'S', N, N)

Count: 5667  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (5667), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE id = N

Count: 1604  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1603), isucon[isucon]@localhost
  SELECT * FROM tenant WHERE name = 'S'

Count: 60  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=107.0 (6422), isucon[isucon]@localhost
  SELECT * FROM tenant ORDER BY id DESC

Count: 1  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  DELETE FROM tenant WHERE id > N

@minhquang4334
Copy link
Collaborator Author

minhquang4334 commented Jul 23, 2022 •
edited
Loading

Score: 31452
Use GoLang Mutex instead File Lock

+-------+-----+-------+-----+-----+-----+--------+---------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+------------+--------------+-----------+
| COUNT | 1XX |  2XX  | 3XX | 4XX | 5XX | METHOD |                  URI                  |  MIN  |  MAX  |   SUM   |  AVG  |  P90  |  P95  |  P99  | STDDEV | MIN(BODY) | MAX(BODY)  |  SUM(BODY)   | AVG(BODY) |
+-------+-----+-------+-----+-----+-----+--------+---------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+------------+--------------+-----------+
| 10577 |   0 | 10546 |   0 |  31 |   0 | GET    | /api/player/competition/              | 0.004 | 0.248 | 197.888 | 0.019 | 0.048 | 0.068 | 0.120 |  0.025 |     0.000 |  12271.000 | 62226543.000 |  5883.194 |
| 12917 |   0 | 12883 |   0 |  34 |   0 | GET    | /api/player/player/                   | 0.004 | 0.144 | 111.132 | 0.009 | 0.024 | 0.032 | 0.052 |  0.012 |     0.000 |   2176.000 | 12414902.000 |   961.129 |
|    72 |   0 |    71 |   0 |   1 |   0 | GET    | /api/admin/tenants/billing?before=xxx | 0.340 | 7.784 |  72.440 | 1.006 | 1.376 | 5.148 | 7.784 |  1.379 |     0.000 |    999.000 |    63204.000 |   877.833 |
|   141 |   0 |   138 |   0 |   3 |   0 | GET    | /api/organizer/billing                | 0.004 | 7.764 |  40.716 | 0.289 | 0.344 | 0.464 | 6.956 |  1.173 |     0.000 |   9417.000 |   456432.000 |  3237.106 |
|  1301 |   0 |  1281 |   0 |  20 |   0 | GET    | /api/player/competitions              | 0.004 | 0.188 |  25.408 | 0.020 | 0.048 | 0.060 | 0.112 |  0.023 |    30.000 |   4472.000 |  2518607.000 |  1935.901 |
|   372 |   0 |   366 |   0 |   6 |   0 | POST   | /api/organizer/competition/           | 0.004 | 0.752 |  16.356 | 0.044 | 0.088 | 0.136 | 0.536 |  0.085 |    16.000 |     53.000 |     9983.000 |    26.836 |
|     1 |   0 |     1 |   0 |   0 |   0 | POST   | /initialize                           | 5.644 | 5.644 |   5.644 | 5.644 | 5.644 | 5.644 | 5.644 |  0.000 |    37.000 |     37.000 |       37.000 |    37.000 |
|   177 |   0 |   176 |   0 |   1 |   0 | POST   | /api/organizer/competitions           | 0.004 | 0.060 |   2.248 | 0.013 | 0.024 | 0.036 | 0.056 |  0.011 |    30.000 |    177.000 |    26368.000 |   148.972 |
|   155 |   0 |   155 |   0 |   0 |   0 | GET    | /api/organizer/players                | 0.004 | 0.124 |   2.116 | 0.014 | 0.036 | 0.056 | 0.096 |  0.020 |  1947.000 | 536882.000 | 11771172.000 | 75943.045 |
|     5 |   0 |     5 |   0 |   0 |   0 | GET    | /api/admin/tenants/billing            | 0.084 | 0.512 |   1.612 | 0.322 | 0.512 | 0.512 | 0.512 |  0.150 |  1032.000 |   1060.000 |     5225.000 |  1045.000 |
|    39 |   0 |    39 |   0 |   0 |   0 | POST   | /api/organizer/players/add            | 0.008 | 0.136 |   0.776 | 0.020 | 0.032 | 0.048 | 0.136 |  0.021 | 10947.000 |  24072.000 |   766392.000 | 19651.077 |
|    15 |   0 |    10 |   0 |   5 |   0 | POST   | /api/admin/tenants/add                | 0.004 | 0.084 |   0.560 | 0.037 | 0.076 | 0.084 | 0.084 |  0.029 |    30.000 |    158.000 |     1486.000 |    99.067 |
|    23 |   0 |    21 |   0 |   2 |   0 | POST   | /api/organizer/player/                | 0.004 | 0.044 |   0.240 | 0.010 | 0.016 | 0.028 | 0.044 |  0.009 |     0.000 |    142.000 |     2384.000 |   103.652 |
|     1 |   0 |     1 |   0 |   0 |   0 | GET    | /css/app.83b4c321.css                 | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  4868.000 |   4868.000 |     4868.000 |  4868.000 |
|     1 |   0 |     1 |   0 |   0 |   0 | GET    | /js/app.3a4ec98c.js                   | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 | 33294.000 |  33294.000 |    33294.000 | 33294.000 |
|     1 |   0 |     1 |   0 |   0 |   0 | GET    | /index.html                           | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |   835.000 |    835.000 |      835.000 |   835.000 |
|     1 |   0 |     1 |   0 |   0 |   0 | GET    | /api/organizer/competitions           | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |   142.000 |    142.000 |      142.000 |   142.000 |
+-------+-----+-------+-----+-----+-----+--------+---------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+------------+--------------+-----------+

@minhquang4334
Copy link
Collaborator Author

minhquang4334 commented Jul 23, 2022 •
edited
Loading

Score: 37056
Add Index to existing tenant

+-------+-----+-------+-----+-----+-----+--------+---------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+------------+--------------+-----------+
| COUNT | 1XX |  2XX  | 3XX | 4XX | 5XX | METHOD |                  URI                  |  MIN  |  MAX  |   SUM   |  AVG  |  P90  |  P95  |  P99  | STDDEV | MIN(BODY) | MAX(BODY)  |  SUM(BODY)   | AVG(BODY) |
+-------+-----+-------+-----+-----+-----+--------+---------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+------------+--------------+-----------+
| 11240 |   0 | 11157 |   0 |  83 |   0 | GET    | /api/player/competition/              | 0.004 | 1.132 | 506.136 | 0.045 | 0.116 | 0.168 | 0.324 |  0.070 |     0.000 |  12271.000 | 98470894.000 |  8760.756 |
| 20358 |   0 | 20292 |   0 |  66 |   0 | GET    | /api/player/player/                   | 0.004 | 0.112 | 237.744 | 0.012 | 0.028 | 0.036 | 0.056 |  0.013 |  2066.000 |   2070.000 | 20055417.000 |   985.137 |
|   116 |   0 |   114 |   0 |   2 |   0 | GET    | /api/admin/tenants/billing?before=xxx | 0.008 | 4.024 |  70.044 | 0.604 | 1.788 | 2.580 | 3.200 |  0.734 |     0.000 |   1046.000 |    98883.000 |   852.440 |
|  1532 |   0 |  1495 |   0 |  37 |   0 | GET    | /api/player/competitions              | 0.004 | 1.012 |  49.956 | 0.033 | 0.076 | 0.108 | 0.192 |  0.047 |     0.000 |   3631.000 |  2342219.000 |  1528.864 |
|   213 |   0 |   212 |   0 |   1 |   0 | GET    | /api/organizer/billing                | 0.004 | 3.080 |  33.616 | 0.158 | 0.068 | 2.320 | 2.844 |  0.602 |     0.000 |   7205.000 |   567180.000 |  2662.817 |
|   521 |   0 |   511 |   0 |   9 |   1 | POST   | /api/organizer/competition/           | 0.004 | 0.220 |  14.532 | 0.028 | 0.060 | 0.084 | 0.128 |  0.027 |     0.000 |     53.000 |    13802.000 |    26.491 |
|     1 |   0 |     1 |   0 |   0 |   0 | POST   | /initialize                           | 9.584 | 9.584 |   9.584 | 9.584 | 9.584 | 9.584 | 9.584 |  0.000 |    37.000 |     37.000 |       37.000 |    37.000 |
|   235 |   0 |   235 |   0 |   0 |   0 | GET    | /api/organizer/players                | 0.004 | 0.156 |   3.564 | 0.015 | 0.040 | 0.064 | 0.112 |  0.023 |  1555.000 | 587844.000 | 17690218.000 | 75277.523 |
|   254 |   0 |   253 |   0 |   1 |   0 | POST   | /api/organizer/competitions           | 0.004 | 0.072 |   3.308 | 0.013 | 0.028 | 0.036 | 0.064 |  0.011 |    30.000 |    179.000 |    37596.000 |   148.016 |
|     9 |   0 |     9 |   0 |   0 |   0 | GET    | /api/admin/tenants/billing            | 0.016 | 0.576 |   1.368 | 0.152 | 0.576 | 0.576 | 0.576 |  0.165 |  1012.000 |   1089.000 |     9377.000 |  1041.889 |
|    21 |   0 |    16 |   0 |   5 |   0 | POST   | /api/admin/tenants/add                | 0.004 | 0.204 |   1.208 | 0.058 | 0.096 | 0.108 | 0.204 |  0.046 |    30.000 |    149.000 |     2305.000 |   109.762 |
|    54 |   0 |    54 |   0 |   0 |   0 | POST   | /api/organizer/players/add            | 0.004 | 0.104 |   0.876 | 0.016 | 0.028 | 0.044 | 0.104 |  0.018 | 10949.000 |  24072.000 |  1044679.000 | 19345.907 |
|    22 |   0 |    21 |   0 |   1 |   0 | POST   | /api/organizer/player/                | 0.004 | 0.024 |   0.232 | 0.011 | 0.024 | 0.024 | 0.024 |  0.007 |    30.000 |    142.000 |     2390.000 |   108.636 |
|     1 |   0 |     0 |   0 |   1 |   0 | GET    | /api/support/tickets                  | 0.004 | 0.004 |   0.004 | 0.004 | 0.004 | 0.004 | 0.004 |  0.000 |    30.000 |     30.000 |       30.000 |    30.000 |
|     1 |   0 |     1 |   0 |   0 |   0 | GET    | /api/organizer/competitions           | 0.004 | 0.004 |   0.004 | 0.004 | 0.004 | 0.004 | 0.004 |  0.000 |   142.000 |    142.000 |      142.000 |   142.000 |
|     1 |   0 |     1 |   0 |   0 |   0 | GET    | /js/app.3a4ec98c.js                   | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 | 33294.000 |  33294.000 |    33294.000 | 33294.000 |
|     1 |   0 |     1 |   0 |   0 |   0 | GET    | /index.html                           | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |   835.000 |    835.000 |      835.000 |   835.000 |
|     1 |   0 |     1 |   0 |   0 |   0 | GET    | /css/app.83b4c321.css                 | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  4868.000 |   4868.000 |     4868.000 |  4868.000 |

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@henchiyb @minhquang4334