First time in ISUCON12: Finishing with nearly the initial score

A record of my first time participating in the ISUCON12 Qualifiers on Saturday, July 23, 2022.
Team name: "FormerColleague_Latest_FinalVersionCopy(2)_oldFinalVersion.xlsx"
Final score: 3160 (Go implementation)

TL;DR

• Challenged with Ruby
  • Initial score before any modifications was around 900–1400.
• 14:00 Decided to migrate from SQLite to MySQL.
  • Abandoned the migration around 17:30 after benchmarks failed data checks, even though I thought it was done.
  • Looking back after the qualifier results were announced the next day, it turned out 2.db through 100.db were not migrated.
• 17:40 Decided to use Server 3, which @VTRyo had been working on almost entirely alone, for the final score.
  • Score was around 1600–1999.
• 17:45 Thinking "Wouldn't the Go implementation be faster than this?", we switched to the untouched Go implementation.
  • Score was around 3000.
• 17:52 Added an index to the visit_history table.
• 17:57 Finished after running the benchmark twice and recording a score of 3160.

Team Member's Blog Post

https://blog.vtryo.me/entry/entry-isucon12

Now, for some impressions after completing the race (a witty pun in Japanese)

• It was fun, but the sense of despair is overwhelming.
• I've been a backend specialist for nearly 20 years, but I was completely useless.
  • I'm making witty jokes, but if I don't use this kind of escapism, the humiliation might drive me crazy.
• I really wanted to complete the SQLite → MySQL migration.
• I'll start over from scratch to ensure I'm not cut from the team, as I want to compete with them again next year.

Detailed Timeline of the Day

Recorded as accurately as possible from commit history, Slack logs, and memory.
I've written this timeline in detail so I can look back on it even after some time has passed.

Before the Competition Starts

• 8:00 Woke up. I was actually quite worried about waking up on time, so it was a relief. Getting enough sleep is crucial.
• 9:20 Team assembly complete.
• 9:30 Local Ruby versions aligned across team members (ruby 3.1.2p20).
• 9:40 Discord voice call started.
• 10:00 Competition starts.
  • https://www.youtube.com/watch?v=75YnJ_3289g

10:00–14:00: Leading up to the SQLite → MySQL Decision

• 10:00 Manual walkthrough.
  • ISUCON12 Qualifier Manual
    • https://gist.github.com/mackee/4320c18919c8f6f1867849378a17e651
  • ISUPORTS Application Manual
    • https://gist.github.com/mackee/460eeb8040389ed5bdeaf2c48327707c
• 10:10 @dyoshikawa1993 executed CloudFormation.
• 10:20 SSH confirmed OK to the launched server; edited sudo vim /etc/hosts and confirmed browser access OK.
  • Started looking at the application code.
  • My memory is a bit fuzzy, but I recall deciding to give up on setting up a local environment quite early around this point.
• 10:28 @VTRyo finished git init for the three servers and started introducing various measurement tools.
  • @VTRyo had prepared snippets for installing various measurement tools beforehand.
    • https://github.com/VTRyo/vtryo-isucon-setup
  • Due to a manual error, the root user ID/pass were only shared in the Q&A around 11:40, but for some reason, @VTRyo was already logged into the MySQL root user by this point.
    • @VTRyo: "I figured it would just be root/root, tried it, and it worked."
• 10:40 Switched to Ruby implementation and ran the first benchmark: SCORE: 1668 (+1668 0(0%))
• 10:42 First commit push to GitHub.
• 10:55 Started measurements while running benchmarks; made minor adjustments to alp, etc.
• 10:58 At this point, I noticed files named 1.db to 100.db in /home/isucon/initial_data, but I didn't realize what they were yet.
• 11:10 @dyoshikawa1993 adjusted API endpoints that could be suppressed in the alp regex logs.
• 11:15 alp results became much easier to read.
  • I'm saying "Whooo, awesome lol" on Slack, so I was quite energetic at this stage.
  • @VTRyo explained the difference between AVG and P99 in the alp results.
• 11:20 @dyoshikawa1993 started working on /api/player/competition/:competition_id/ranking, and I started on /api/organizer/billing.
• 11:42 Prepared to install rack-mini-profiler and rack-lineprof.
  • In the end, I didn't use them during the competition (couldn't get to the point where I'd need them).
• 11:50 Around here, I felt a sense of unity, as if the team's concentration had peaked.
  • It felt like we were executing what we had practiced.
  • There's a log from @VTRyo saying, "11:48: It's turning into a festival."
• 12:05 @dyoshikawa1993 enabled connections from local MySQL Workbench via TCP/IP over SSH.
• 12:10 Around this time, we noticed that the Schema didn't change even after modifying sql/admin/10_schema.sql or sql/tenant/10_schema.sql.
  • Started checking sql/init.sh.
  • Realized that since sql/init.sql uses range deletions, a simple DROP TABLE might break things.
  • @dyoshikawa1993 noticed sql/admin/90_data.sql, which was gitignored.
• 12:15 Discovered SQLite.
  • Couldn't fully process the shock of def connect_to_tenant_db and flock_by_tenant_id.
• 12:30 Realized that because we had given up on local development, simultaneous work was difficult.
  • @dyoshikawa1993 started on server 1, I on server 2, and @VTRyo on server 3.
• 12:53 @VTRyo noticed Redis was running.
• 12:54 @VTRyo created a command to view docker logs.

docker logs -f `docker ps | grep webapp | awk '{print $1}'`

• 12:55 @VTRyo enabled access to Netdata.
  • @VTRyo noticed the heavy Disk Write load.
  • Ultimately, I deeply regret that I barely looked at Netdata myself.
• 13:00 Discussed issues like being unable to use puts in Sinatra and changes to app.rb not reflecting.
  • Decided to proceed by restarting the server to apply changes.
• 13:15 I was working on server 2 and realized I couldn't git pull; fixed it so I could.
• 13:34 @VTRyo re-checked the score calculation in the manual and shared it with the team.
• 13:38 At this point, it seems we were still working on the endpoints we started at 11:20.
• 14:00 At the halfway point, we decided: let's do it, SQLite → MySQL!
  • I believe our reasoning was as follows:
    • We had too little knowledge of SQLite to measure or tune it as is.
      • We couldn't even add indexes.
        • Even if we modified CREATE TABLE to include an INDEX, it wouldn't be applied to the existing 1.db through 100.db.
      • We didn't even know how to check the records effectively.
      • At this stage, we still thought 1.db through 100.db were physical dump files.
    • We felt a strong intention for migration embedded throughout the code.
      • SQLite was being used for multi-tenancy, yet WHERE tenant_id = ? was already implemented.
      • There was a script called sqlite3-to-sql.

14:00–17:30: Until the Abandonment of SQLite → MySQL

• 14:00 Immediately after deciding on the SQLite → MySQL migration, @dyoshikawa1993 began the migration work.

• 14:19 Since my tuning of /api/organizer/billing yielded no results in the benchmark, I stopped working on it.

• 14:39 @dyoshikawa1993 finished modifying sql/init.sh and began the SQLite → MySQL data insertion.

• 14:43 I started tuning /api/player/player/:player_id.

• 14:48 There was an exchange in the portal Q&A on the day, and we discussed how 90_data.sql was definitely the right place for initial data like tenant. We also confirmed that deleting data and running sql/admin/90_data.sql didn't break the benchmark.
  

• 15:05 It turned out that the SQLite → MySQL data insertion was taking forever, so we discussed how the queries created by the sqlite3-to-sql script were inserting one line at a time and needed to be converted to bulk inserts.

  • I started working on bulk insertion on server 2.

• 15:07 I remember the sense of urgency starting to get intense with less than three hours remaining.

• 15:10 Since we decided not to set up a local environment, modifying app.rb began to get difficult.

  • We talked about adding racksh, but couldn't solve the issue of not being able to run bundle install.

• 15:23 @VTRyo: "This is harder than my actual job."

  • Me: "I feel you."

• 15:51 Around this time, I couldn't push to GitHub from server 1, perhaps because I removed sql/admin/90_data.sql from .gitignore.

ERROR 1153 (08S01) at line 1: Got a packet bigger than 'max_allowed_packet' bytes

• 16:00 I'm still trying to implement bulk inserts.
  • I was trying my best with sed.
• 16:02 @VTRyo continues tuning app.rb on server 3.
  • I want to do something about the player_count increment in def billing_report_by_competition, but I feel like I can't come up with anything.
  • I couldn't do bulk inserts or app tuning, so looking back, I think I was in a complete panic during this time.
• 16:20 @dyoshikawa1993 wrote a Node script for bulk inserts, so we switched the SQLite → MySQL data insertion to that. The insertion that hadn't finished in an hour and a half seemed to finish in a few minutes.
  • So in the end, I achieved nothing between 15:00 and 16:20.
• 16:30 @dyoshikawa1993 worked tirelessly on editing app.rb for the SQLite → MySQL migration on server 1 while checking operations via the browser.
  • I offered advice, but since we weren't editing the code simultaneously, I only remember being able to do almost nothing.
  • Ran benchmarks after fixes, but data validation failed...
  • When running the benchmark just before the end, I looked at the MySQL data and saw that only data with tenant_id 1 existed in the player table and others. It seems 2.db and subsequent files hadn't been migrated, but since I didn't notice it at the time, I couldn't identify the reason for the benchmark failure.
• 16:51 Trying to do something about def dispense_id.
  • @VTRyo had an inspiration that we could use SecureRandom, so we adopted it.
  • However, the benchmark failed... SCORE: 0
• 17:17 @VTRyo stopped using SecureRandom in def dispense_id and changed 100.times do to 2.times do, slightly increasing the score on server 3.
• 17:20 Around here, we agreed that if the benchmark for server 1 didn't finish by 17:30, we'd use server 3 for the final score.

17:30–18:00: Until the End of the Competition

• 17:30 Cruelly, the benchmark failed and it became 17:30. @VTRyo removed various measurement tools and restored the environment on servers 1–3.
  • He did things like "restore it or you'll be disqualified by the env-checker."
  • @VTRyo was amazing for properly reading the manual and planning the restoration.
• 17:40 Started running the final benchmark on server 3, which @VTRyo had been working on almost alone. SCORE: 1999
• 17:45 "Since it's almost in the initial state, wouldn't switching to Go from Ruby and running the benchmark increase the score?"
  • Switched to the untouched Go sample implementation and ran the benchmark. SCORE: 3000
• 17:52 Added an index to the visit_history table on server 3.
• 17:57 Ran the benchmark twice and recorded a score of 3160, so we finished.
• 18:00 Competition ends.

Reflections on the Day

Retrospective

• Can you believe that the Go sample implementation had a higher score than the Ruby app we spent 8 hours improving...?
  • I can't accept reality.
• It's so regrettable that I couldn't figure out why I couldn't run bundle install.
• Spending over an hour on the bulk conversion for MySQL insertion of N.db and achieving nothing was terrible. I should have said so if I couldn't do it.
  • Improve your sed and awk skills, come on.
• The regret of not being able to finish the SQLite → MySQL migration is intense.
• As you can see from the timeline, I, @JUNKI555, did almost nothing.
  • Only 2 commits in 8 hours.
  • From halfway through, I ended up just watching the other two work...
• Could I have shared it by committing frequently to GitHub?
  • Maybe doing it via Zoom screen control would have been better.
• I really wanted to split the work on editing app.rb for the SQLite → MySQL migration that @dyoshikawa1993 was doing from 16:30.
  • What should I have done?
• Frustrating.
• We couldn't set up the local environment, so maybe instead of pouring resources into the SQLite → MySQL migration, it would have been better if the three of us did different things.
• I wanted to finish the SQLite → MySQL migration. That's all there is to it.

Good things

• It was a lot of fun.
  • The practice leading up to the day was also good.
• It was good that we noticed the use of SQLite quite early.
  • There was even a possibility we wouldn't have noticed.

What could have been done better as of the next day

*Note: These are my personal thoughts as of the next day, so they may change after future team discussions.
*I wrote the timeline in detail so I can look back on it later.

We should have run the Go benchmark earlier

• If we had known the scores for the Go sample implementation versus the Ruby sample implementation at the start of the competition, our decision at 14:00 might have been different.

What was our goal?

• Was our goal to make it to the finals as first-time participants?
  • If so, the decision to migrate from SQLite to MySQL might not have been wrong.
• Or was it simply to get as many points as possible to qualify for the finals?
  • If that was the goal, the decision to migrate was completely wrong.
• It's hard to reflect without a clear goal.

ISUCON Organizers: "Please use this when you feel like meeting at the finals"

We entered the day without aligning on what to do during the competition

• I wasn't even aware that @VTRyo had prepared to make Netdata accessible.
• This was a failure on my part to the point where I couldn't argue if someone said I was sleeping during practice. I, @JUNKI555, need to reflect deeply.
  • Yes...

ISUCON Organizers: "Please use this when the only thing you can do is pour tea"

Was the decision to migrate from SQLite to MySQL correct?

• In hindsight, since we couldn't finish it, deciding to migrate was a mistake.
  • However, again, this judgment should depend on the goal.
• I couldn't resist the big bait dangling in front of me.
• I failed to follow the rule: "You can't do in the actual competition what you don't do normally."
  • It's difficult to suddenly give your all to something you've never done before in a high-stakes environment.
• Based on the Discord After Party with other participants after the competition, it seems some teams took the following approaches:
  • Migrated only the new tenants to MySQL (1.db through 100.db remained as is).
  • Cached reads while keeping SQLite.
  • Migrated only a portion.
• It was tough that we couldn't decide not to do everything at once, even for a migration.
  • Testing small is fundamental.
• I made the decision without even estimating how much better it would get with the SQLite → MySQL migration.
• In fact, I hadn't even fully organized what kind of impact using SQLite was having.
  • It was at a level where I might have proceeded without understanding how the SQLite parts were affecting the application.
• In other words, I proceeded based on assumptions.

ISUCON Organizers: "Please use this when measurement is needed"

Did we even try to look into SQLite?

• No, we didn't.
  • We panicked too much.
  • The feeling of "What even is SQLite!?" was too strong.
• Thinking with a clear head after the scores were finalized the next day, I realized I could have issued queries with the sqlite3 command. If I had done that, I would have immediately noticed the data loss in the migration.
  • I would have also realized that files like 1.db were not dumps, but the SQLite databases themselves.

(It's too late to realize this after the scores are finalized)

Finally

• I knew about ISUCON, but only at a surface level.
  • I didn't realize it was such an emotionally stirring experience.
  • Thanks to @VTRyo for inviting me.
• Thanks to @VTRyo and @dyoshikawa1993 for fighting alongside me.
• Let's fight again next year.
  • I will get stronger.
• Good luck to everyone going to the finals!
• Thank you to the organizers, problem authors, and staff for such wonderful content.

https://twitter.com/JUNKI555/status/1550774569451286528?s=20&t=chxi6QGCWyV5SpqPI4D--A

• Thank you to all the participants of the ISUCON12 Qualifiers. It was a great competition.

https://twitter.com/JUNKI555/status/1550768780854185984?s=20&t=chxi6QGCWyV5SpqPI4D--A

Memo: Impressions posted on Facebook on the night of the event

Today I participated in the ISUCON 12 Qualifiers.

To put it simply, ISUCON is like the "All-Japan Web Application Acceleration Competition."
Teams of three work on different problems (some web application that needs acceleration) for each competition to see how much they can speed it up within an 8-hour window.
I participated with two former colleagues I worked with about two years ago.

The qualifier results will be announced tomorrow, but even without waiting, it's a crushing defeat, and I feel my own lack of skill deeply.

However, the practice sessions we had leading up to the competition and the 8 hours on the day were intense and very enjoyable.

For those who were at gloops, it might be relatable if I describe it as the feeling of "everyone pitching in to fix a content piece that's suffering from major lag due to a failure," but as a competition (lol).
But there's a silver lining to losing: as you gain experience, it's rare to have the experience of "giving your all in your area of expertise but being completely outmatched," so it's good to have felt that.

At the After Party following the ISUCON 12 Qualifiers, I heard various stories from other participants.
Even among the top teams, people were saying things like "I could have done this better," "I should have done that," or "How should I have judged that part?", making it a wonderful place where everyone strives for higher goals. It really braced me.

That being said, winning is better, so I'll study hard for the next competition. 💪

A huge thank you to the sponsoring companies, organizers, and problem authors of ISUCON.
Good luck to everyone in the finals next month! #ISUCON