Investigate high rate of replay upload timeouts

[peppy]

https://sentry.ppy.sh/organizations/ppy/issues/34904/

[peppy]

@bdach to follow up on this, we figured that the number being reported on datadog was much higher than actual, but I forget the reason why, or how i confirmed that this was the case. or if we actually decided this was still an issue..

root@db-master.osu.io:osu> select has_replay, count(*) from scores where preserve = 1 and passed = 1 and legacy_score_id is null and id between 2386002880 and 238700288
                        -> 0 group by has_replay;
+------------+----------+
| has_replay | count(*) |
+------------+----------+
| 1          | 22345    |
| 0          | 3669     |
+------------+----------+

The count without replays still seems quite high, but I might be missing something in the filtering conditions.

[bdach]

That query is missing an approved in (1, 2).

Running the query did look significantly better but we never got to explain why the numbers were being overestimated like that. I believe we did rule out aborted submission.

Next step would be to take a sample of those timeout messages from logs or from sentry and look for possible patterns in database. Or just blindly try increasing timeout I guess.

[peppy]

That query is missing an approved in (1, 2).

aha, that's the one.

root@db-master.osu.io:osu> select has_replay, count(*) from scores join osu_beatmaps using (beatmap_id) where preserve = 1 and passed = 1 and legacy_score_id is null an
                        -> d id between 2386002880 and 2387002880 and approved in (1,2) group by has_replay;
+------------+----------+
| has_replay | count(*) |
+------------+----------+
| 1          | 21148    |
| 0          | 36       |
+------------+----------+

[peppy]

Let's close this one for the time being. As we discovered, the failure rate is actually very low.

[bdach]

I'd still want to investigate the metrics themselves and why they're overreporting the actual count. Maybe we can save on some unnecessary work in the server.