[メモ] pprof

[sor4chi]

(pprof) list competitionRankingHandler
Total: 32.11s
ROUTINE ======================== github.com/isucon/isucon12-qualify/webapp/go.competitionRankingHandler in /home/isucon/webapp/go/isuports.go
      10ms      5.68s (flat, cum) 17.69% of Total
         .          .   1322:// 参加者向けAPI
         .          .   1323:// GET /api/player/competition/:competition_id/ranking
         .          .   1324:// 大会ごとのランキングを取得する
         .          .   1325:func competitionRankingHandler(c echo.Context) error {
         .          .   1326:   ctx := context.Background()
         .      190ms   1327:   v, err := parseViewer(c)
         .          .   1328:   if err != nil {
         .          .   1329:       return err
         .          .   1330:   }
         .          .   1331:   if v.role != RolePlayer {
         .          .   1332:       return echo.NewHTTPError(http.StatusForbidden, "role player required")
         .          .   1333:   }
         .          .   1334:
         .       10ms   1335:   tenantDB, err := connectToTenantDB(v.tenantID)
         .          .   1336:   if err != nil {
         .          .   1337:       return err
         .          .   1338:   }
         .          .   1339:   defer tenantDB.Close()
         .          .   1340:
         .      190ms   1341:   if err := authorizePlayer(ctx, tenantDB, v.playerID); err != nil {
         .          .   1342:       return err
         .          .   1343:   }
         .          .   1344:
         .          .   1345:   competitionID := c.Param("competition_id")
         .          .   1346:   if competitionID == "" {
         .          .   1347:       return echo.NewHTTPError(http.StatusBadRequest, "competition_id is required")
         .          .   1348:   }
         .          .   1349:
         .          .   1350:   // 大会の存在確認
         .       20ms   1351:   competition, err := retrieveCompetition(ctx, tenantDB, competitionID)
         .          .   1352:   if err != nil {
         .          .   1353:       if errors.Is(err, sql.ErrNoRows) {
         .          .   1354:           return echo.NewHTTPError(http.StatusNotFound, "competition not found")
         .          .   1355:       }
         .          .   1356:       return fmt.Errorf("error retrieveCompetition: %w", err)
         .          .   1357:   }
         .          .   1358:
         .          .   1359:   now := time.Now().Unix()
         .          .   1360:   var tenant TenantRow
         .       60ms   1361:   if err := adminDB.GetContext(ctx, &tenant, "SELECT * FROM tenant WHERE id = ?", v.tenantID); err != nil {
         .          .   1362:       return fmt.Errorf("error Select tenant: id=%d, %w", v.tenantID, err)
         .          .   1363:   }
         .          .   1364:
         .       30ms   1365:   if _, err := adminDB.ExecContext(
         .          .   1366:       ctx,
         .          .   1367:       "INSERT INTO visit_history (player_id, tenant_id, competition_id, created_at, updated_at) VALUES (?, ?, ?, ?, ?)",
         .          .   1368:       v.playerID, tenant.ID, competitionID, now, now,
         .          .   1369:   ); err != nil {
         .          .   1370:       return fmt.Errorf(
         .          .   1371:           "error Insert visit_history: playerID=%s, tenantID=%d, competitionID=%s, createdAt=%d, updatedAt=%d, %w",
         .          .   1372:           v.playerID, tenant.ID, competitionID, now, now, err,
         .          .   1373:       )
         .          .   1374:   }
         .          .   1375:
         .          .   1376:   var rankAfter int64
         .          .   1377:   rankAfterStr := c.QueryParam("rank_after")
         .          .   1378:   if rankAfterStr != "" {
         .          .   1379:       if rankAfter, err = strconv.ParseInt(rankAfterStr, 10, 64); err != nil {
         .          .   1380:           return fmt.Errorf("error strconv.ParseUint: rankAfterStr=%s, %w", rankAfterStr, err)
         .          .   1381:       }
         .          .   1382:   }
         .          .   1383:
         .          .   1384:   // player_scoreを読んでいるときに更新が走ると不整合が起こるのでロックを取得する
         .       20ms   1385:   fl, err := flockByTenantID(v.tenantID)
         .          .   1386:   if err != nil {
         .          .   1387:       return fmt.Errorf("error flockByTenantID: %w", err)
         .          .   1388:   }
         .          .   1389:   defer fl.Close()
         .          .   1390:   pss := []PlayerScoreRow{}
         .      2.82s   1391:   if err := tenantDB.SelectContext(
         .          .   1392:       ctx,
         .          .   1393:       &pss,
         .          .   1394:       "SELECT * FROM player_score WHERE tenant_id = ? AND competition_id = ? ORDER BY row_num DESC",
         .          .   1395:       tenant.ID,
         .          .   1396:       competitionID,
         .          .   1397:   ); err != nil {
         .          .   1398:       return fmt.Errorf("error Select player_score: tenantID=%d, competitionID=%s, %w", tenant.ID, competitionID, err)
         .          .   1399:   }
         .          .   1400:   ranks := make([]CompetitionRank, 0, len(pss))
         .          .   1401:   scoredPlayerSet := make(map[string]struct{}, len(pss))
         .          .   1402:   for _, ps := range pss {
         .          .   1403:       // player_scoreが同一player_id内ではrow_numの降順でソートされているので
         .          .   1404:       // 現れたのが2回目以降のplayer_idはより大きいrow_numでスコアが出ているとみなせる
         .       30ms   1405:       if _, ok := scoredPlayerSet[ps.PlayerID]; ok {
         .          .   1406:           continue
         .          .   1407:       }
         .          .   1408:       scoredPlayerSet[ps.PlayerID] = struct{}{}
         .      2.19s   1409:       p, err := retrievePlayer(ctx, tenantDB, ps.PlayerID)
         .          .   1410:       if err != nil {
         .          .   1411:           return fmt.Errorf("error retrievePlayer: %w", err)
         .          .   1412:       }
      10ms       10ms   1413:       ranks = append(ranks, CompetitionRank{
         .          .   1414:           Score:             ps.Score,
         .          .   1415:           PlayerID:          p.ID,
         .          .   1416:           PlayerDisplayName: p.DisplayName,
         .          .   1417:           RowNum:            ps.RowNum,
         .          .   1418:       })
         .          .   1419:   }
         .       10ms   1420:   sort.Slice(ranks, func(i, j int) bool {
         .          .   1421:       if ranks[i].Score == ranks[j].Score {
         .          .   1422:           return ranks[i].RowNum < ranks[j].RowNum
         .          .   1423:       }
         .          .   1424:       return ranks[i].Score > ranks[j].Score
         .          .   1425:   })
         .          .   1426:   pagedRanks := make([]CompetitionRank, 0, 100)
         .          .   1427:   for i, rank := range ranks {
         .          .   1428:       if int64(i) < rankAfter {
         .          .   1429:           continue
         .          .   1430:       }
         .          .   1431:       pagedRanks = append(pagedRanks, CompetitionRank{
         .          .   1432:           Rank:              int64(i + 1),
         .          .   1433:           Score:             rank.Score,
         .          .   1434:           PlayerID:          rank.PlayerID,
         .          .   1435:           PlayerDisplayName: rank.PlayerDisplayName,
         .          .   1436:       })
         .          .   1437:       if len(pagedRanks) >= 100 {
         .          .   1438:           break
         .          .   1439:       }
         .          .   1440:   }
         .          .   1441:
         .          .   1442:   res := SuccessResult{
         .          .   1443:       Status: true,
         .          .   1444:       Data: CompetitionRankingHandlerResult{
         .          .   1445:           Competition: CompetitionDetail{
         .          .   1446:               ID:         competition.ID,
         .          .   1447:               Title:      competition.Title,
         .          .   1448:               IsFinished: competition.FinishedAt.Valid,
         .          .   1449:           },
         .          .   1450:           Ranks: pagedRanks,
         .          .   1451:       },
         .          .   1452:   }
         .      100ms   1453:   return c.JSON(http.StatusOK, res)
         .          .   1454:}

[sor4chi]

(pprof) list retrievePlayer
Total: 32.11s
ROUTINE ======================== github.com/isucon/isucon12-qualify/webapp/go.retrievePlayer in /home/isucon/webapp/go/isuports.go
      10ms      2.93s (flat, cum)  9.12% of Total
         .          .    377:}
         .          .    378:
         .          .    379:// 参加者を取得する
         .          .    380:func retrievePlayer(ctx context.Context, tenantDB dbOrTx, id string) (*PlayerRow, error) {
         .          .    381:   var p PlayerRow
         .      2.92s    382:   if err := tenantDB.GetContext(ctx, &p, "SELECT * FROM player WHERE id = ?", id); err != nil {
         .          .    383:       return nil, fmt.Errorf("error Select player: id=%s, %w", id, err)
         .          .    384:   }
      10ms       10ms    385:   return &p, nil
         .          .    386:}