This article is part of the day 21 in the KINTO Technologies Advent Calendar 2024 🎅🎄

Introduction

Hello! I’m Uehara (@penpen_77777) from the KINTO FACTORY Development Group. I joined the company in July 2024 and have been working on backend development for KINTO FACTORY.

This time, KINTO Technologies employees participated in ISUCON14, which was held on December 8th, and would like to share the content and results.

What is ISUCON14?

ISUCON is a tuning competition organized by LINE Yahoo Japan Corporation. Participants are given a predefined web service and compete to optimize its performance as much as possible while adhering to specific regulations. The winning team receives a grand prize of 1 million yen! This year, the competition took place on Sunday, December 8th, from 10:00 AM to 6:00 PM. I (Uehara) have been participating in ISUCON since last year's ISUCON13, not only to gain knowledge about performance tuning but also to challenge myself as an engineer and improve my skills.

Team "ktc-isucon-bu"

I recruited members through the company's Slack and formed the team "ktc-isucon-bu." The team members are as follows.

• Uehara(@penpen_77777)
  • Participated in ISUCON13 last year, making this their second time.
• Furuya
  • Participating in ISUCON for the first time.
• Nishida
  • Participating in ISUCON for the first time.

ISUCON allows participants to choose from several programming languages for the initial implementation. This time, we chose Go. Since many of our team members were first-time participants, rather than aiming for a top ranking, we set a more achievable goal: scoring over 10,000 points.

Preparation Before the Competition

At ISUCON, it is crucial to automate or streamline repetitive tasks so that we can focus on optimizing during the actual competition. This time, we made the following preparations:

• Setting up environment provisioning and deployment commands
• Developing documentation generation tools
• Preparing measurement tools
• Conducting individual and team practice

Setting up environment provisioning and deployment commands

go-task is a task runner specialized for executing tasks in the terminal.
https://taskfile.dev/

Traditionally, make is often used as a task runner, but I personally find go-task more convenient, so I chose to use it this time. (Unlike make, it requires installation, which is a minor drawback, but aside from that, I think it can be effectively utilized in real-world projects.)

For example, create the following taskfile.yaml and define the setup task and deploy task.

version: '3'

tasks:
  setup:
    cmds:
      # Write commands for environment setup
      - echo "setup"
  deploy:
    cmds:
      # Write the deployment command
      - echo "deploy"

After creating the file, you can execute a task by running task name as shown below:

# Run the environment setup task
$ task setup 
setup

# Run the deploy task
$ task deploy
deploy

Additionally, you can pass command-line arguments when running a task, embedding them into the task execution.

version: '3'

tasks:
  setup:
    cmds:
      - echo "setup {{ .CLI_ARGS }}"
  deploy:
    cmds:
      - echo "deploy {{ .CLI_ARGS }}"

# Execute the setup task for server isu1
$ task setup -- isu1
setup isu1

# Execute the deploy task for server isu2
$ task deploy -- isu2
deploy isu2

The example above is a simple demonstration, but in real use, leveraging command-line arguments allows you to easily switch target servers, making it more efficient for teams to divide and manage tasks.

Other benefits of using go-task include:

• Even if you are working in a subdirectory, you can find the taskfile.yaml in the parent directory and execute the task.
  • You can execute tasks without worrying about the directory location.
• Tasks can be called from other tasks, increasing the reusability of the tasks you define.
  • You can combine tasks, such as running a setup task before a deploy task.
• By describing all the tools used in ISUCON in taskfile.yaml, you can execute them just by typing the task command, even if you don't know how to use them (such as the required options).

A template taskfile.yaml was prepared in advance so that variables could be modified easily during the competition, allowing for flexible responses to any issues that might arise.

Developing documentation generation tools

The following two tools were used:

https://github.com/k1LoW/tbls

• Reads the contents of the database and generates documentation (Markdown) that includes ER diagram and schema descriptions.
• Allows checking the schema definition without connecting to the database, making it useful for understanding the database structure.
• Automatically generating documentation in the CI/CD pipeline makes it easier to track changes in the database structure.
  • For more details, see the following article: https://devblog.thebase.in/entry/auto_generated_er_graph_by_tbls_and_github_actions

https://github.com/mazrean/isucrud

• Analyzes application code, visualizes relationships with database tables, and generates documentation (Markdown).
  • The drawback was that the diagrams became difficult to understand as the number of functions increased, but now it's much easier to use because you can interactively narrow down the parts you want to see on a web browser.

Preparing measurement tools

The following tools were used:

https://github.com/kaz/pprotein

• Collects and visualizes access logs, slow query logs, and profile data.
• Automatically starts collecting data via webhook when a benchmark is run, making it convenient.
• Also records commit hashes at the time of data collection, making it easy to track which commits contributed to score improvements. ^[1]

Conducting individual and team practice

For first-time participants, jumping straight into past ISUCON problems can be overwhelming and discouraging due to their high difficulty level. To help them get a feel for performance tuning, we first had them read the book Web Performance Tuning: Experts' Guide.

https://gihyo.jp/book/2022/978-4-297-12846-3

As we progressed through the book, we gathered as a team on weekends to work through the following practice problems.

https://github.com/catatsuy/private-isu
(The book also provides guidance on how to optimize using private-isu)

Once we were able to consistently score around 100,000 to 200,000 points, we shifted our focus to solving problems from ISUCON13. At this point, we had gained enough confidence to transition into competition-focused practice, allowing us to simulate real ISUCON conditions smoothly.

This year’s Task

https://www.youtube.com/watch?v=UFlcAUvWvrY

• The theme for ISUCON 14 was improving the ride chair service "ISURIDE"
  • Chair owners provide chairs for transportation.
  • Users request chairs via the app and travel to their destination.
• Scores are based on factors like distance traveled and ride completion rate.
  • It is necessary to make improvements to increase user satisfaction.

https://github.com/isucon/isucon14

Results

The results are as follows. We surpassed our goal of 10,000 points!

• Final Ranking: 135th place out of 831 teams
• Final Scores: 12514 points

What We Achieved

• Preparation of deployment scripts (Uehara 10:00-10:30)
• Manual for the day (Furuya 10:00)
• Checking running processes and understanding the general configuration (Furuya 10:00)
• Reading the application manual (Nishida 10:00)
• Logging into MySQL and checking table sizes
• Adding indexes (Nishida 11:21)

  -- chair_locations
  CREATE INDEX idx_chair_id ON chair_locations(chair_id);
  CREATE INDEX idx_chair_id_created_at ON chair_locations(chair_id, created_at);
  -- chairs
  CREATE INDEX idx_owner_id ON chairs(owner_id);
  -- ride_statuses
  CREATE INDEX idx_ride_statuses_ride_id_chair_sent_at_created_at
      ON ride_statuses (ride_id, chair_sent_at, created_at);
  

• Preparing pprotein (Uehara 11:48)
  • I had a hard time fixing the nginx settings and it took me over an hour
• Adding more indexes (Nishida 11:54)

  CREATE INDEX idx_ride_statuses_created_at_ride_id_chair_sent_at
      ON ride_statuses (created_at, ride_id, chair_sent_at);
  
  -- rides
  CREATE INDEX idx_chair_id_updated_at ON rides (chair_id, updated_at DESC);
  

• Enabling dynamic parameters and speeding up json processing (Uehara 12:38)

  // Changed import statements
  "encoding/json" → "github.com/goccy/go-json"
  
  // Enabled InterpolateParams when connecting to the database
  dbConfig.InterpolateParams = true
  

• Adding More Indexes (Uehara 13:05)

  -- chairs
  CREATE INDEX idx_chairs_access_token ON chairs(access_token);
  
  -- ride_statuses
  CREATE INDEX idx_ride_statuses_ride_id_app_sent_at_created_at
      ON ride_statuses (ride_id, app_sent_at, created_at);
  
  -- rides
  CREATE INDEX idx_rides ON rides (user_id, created_at);
  
  -- coupons
  CREATE INDEX idx_coupons_used_by ON coupons(used_by);
  

• Implementing user status cache (Furuya 14:30)
  • In-memory cache for users and ride_statuses tables
• Modifying transaction scope (Nishida 14:48, 15:09)
• Adjusting notification polling intervals (Furuya 16:27)
  • Changed the RetryAfterMs returned in appGetNotification and chairGetNotification from 30 ms to 300 ms.
• Shortened chair-user matching interval (Nishida 17:18, 17:28)
  • Shortened ISUCON_MATCHING_INTERVAL from 0.5 s to 0.1 s
  • Score nearly doubled (most effective change)
• Stopped bin log (Furuya 17:24)
  • Disabled bin logs in MySQL settings (/etc/mysql/mysql.conf.d/mysqld.cnf)

    disable-log-bin=1
    innodb_flush_log_at_trx_commit=0
    

• Disabled log output (Uehara 17:43)
  • Stopped log output for nginx, MySQL,
• and application logs
• Improved ownerGetChairs (Nishida 17:43)
  • Implemented memoization for distance
• Adjusting the number of connections (Uehara 17:49)

  db.SetMaxIdleConns(50)
  db.SetMaxOpenConns(50)
  

Measures That Could Not Be Implemented Due to Time Constraints or Score Issues

• Fixed matching process (Uehara 13:00-16:00)
  • Attempted improvements but failed to resolve chair dispatch errors and had to abandon it.
• Caching chair access tokens (Furuya 15:36)
  • Significantly reduced queries from 30,000 to 100. However, the score did not increase.
  • Likely due to a bottleneck elsewhere.
• nginx parameter tuning (Furuya 17:39)
  • Unable to apply optimizations due to benchmark errors...
• Server split (Uehara 16:00-17:00)
  • Attempted to split nginx+Go and MySQL, into two machines but encountered data inconsistency errors.
  • Later realized that both servers were running the chair-matching service, which led to data inconsistencies... (If only we had stopped one...)

Pros

• Achieved our goal of scoring over 10,000 points.
  • Ranked 135th out of 831 teams with 12,514 points, a solid result for a team with one returning participant and two first-timers.
• Everyone contributed to improvement tasks,
  • ensuring no idle time.
• Environment setup and deployment scripts worked smoothly.
  • No major deployment issues,
  • except minor struggles with nginx setup for pprotein. It took me about an hour to do so, so I would like to add a note to the instructions.
  • Using automation tools significantly improved our workflow, and we plan to expand this approach.

Points to Reflect on

• Lacked a deep understanding of the application before making optimizations.
  • In this problem, understanding the application specifications was important. Mechanically optimizing based on slow query logs or application logs didn’t lead to a significant score increase, and there were few areas where we could make effective improvements.
  • Need to develop a structured approach to understand application behavior before tuning.
  • Understanding specifications is important in daily work as well, so I want to keep this in mind moving forward.
• Struggled with the matching process bug, which delayed implementation.
  • Tried to optimize using only queries without modifying the table structure, which made implementation difficult. I realized that adding appropriate columns to the tables and simplifying the application code would have made implementation much smoother.
• I had no prior knowledge of SSE (Server-Sent Events), so I plan to study it.
  • It was presented as a way to update the user’s chair status in real time.

Lessons to Apply in Future Work

• Understanding the application is more critical than just focusing on technical optimizations.
• Use task runners (go-task) and auto-documentation tools (tbls, etc.) to improve workflow efficiency.
• Explore using SSE (Server-Sent Events) in product development.

Summary

We participated in ISUCON14 and achieved a score of 12514 as the team "ktc-isucon-bu", placing 135th out of 831 teams. Although there are things we need to improve on, we were pleased that we were able to achieve our goal of scoring over 10,000 points, even though some of our members were participating in ISUCON for the first time. We will use these lessons to improve further at ISUCON15.

Special thanks to Furuya-san and Nishida-san for joining despite it being their first ISUCON. Also, a big thank you to all KINTO Technologies employees who showed interest, even if they couldn’t participate this time. Lastly, a huge shoutout to the ISUCON organizers for hosting this incredible event.