isuc: A driver-level SQL caching tool

💡

This article is from Day 2 of NojaRelay 2025

Introduction

We are a team from the traP hackathon in the winter of 2024, and we developed a tool for ISUCON called 'isuc.' This tool caches SQL responses to speed up applications. It automatically analyzes the codebase and extracts queries, then decide what queries to be cached. Users can optimize the application by just switching a SQL driver.

Let’s dive deeper into the details.

What is ISUCON?

ISUCON (Iikanjini Speed Up CONtest) is an event organized by LY Corporation in which participants compete to improve the performance of a given web application. The scope is mainly backend but including some infrastructure parts. Frontend is out of scope in contrast. You can check the target application used in the latest contest below. (Japanese)

isuc

A typical tuning technique in ISUCON is optimizing N+1 query, like code below.

var posts []*PostModel
tx.SelectContext(ctx, &posts, "SELECT * FROM posts WHERE author_id = ?", authorID)
for _, post in range posts {
    var comments []*CommentModel
    tx.SelectContext(ctx, &comments, "SELECT * FROM comments WHERE post_id = ?", postID)
    posts.comments = comments
}

Querying a database many times should be slow and should be optimized. There are two typical solution: using JOIN and using a cache. In a production usecase, using JOIN would be a good option but in ISUCON, using a cache is also a good option because it is easy to implement and enough fast.

isuc performs these optimizations automatically. Once you execute a query, the result is cached and reused for subsequent queries—no extra code is needed; simply switch your database driver.

- db, err := sql.Open("mysql", {dsn})
+ db, err := sql.Open("mysql+cache", {dsn})

We tested isuc with ISUCON13. Initially its benchmark score is 3912 but it increases to 9245 using isuc. After some improvements, the score without isuc is 19563 and one with isuc is 43591. These significant tuning is performed with just a simple edit. It is amazing, isn't it?

Features

isuc is a driver-level tool that intercepts query requests from an application and returns either a cached response or the result from the database.

It also has a functionality to delete outdated caches instantly. The cache purging algorithm is smart enough for ISUCON applications and efficiently handles both row-wise and column-wise purging.

Row-Wise Purging

Here is a brief example for row-wise purging of isuc. It caches all the possible queries but properly purges outdated ones.

SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE name = 'Alice'; -- non-unique column `name`
SELECT * FROM users WHERE id = 2;
SELECT * FROM users WHERE id = 3;

INSERT INTO users (id, name, age) VALUES (10, 'Alice', 20);
SELECT * FROM users WHERE id = 1; -- cached
SELECT * FROM users WHERE name = 'Alice'; -- purged

DELETE FROM users WHERE id = 2;
SELECT * FROM users WHERE id = 2; -- purged
SELECT * FROM users WHERE id = 3; -- cached

Related to it, isuc divides a IN clause into simple equal condition for increasing cache hit ratio.

SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE id IN (1, 2); -- cached
SELECT * FROM users WHERE id IN (3, 4); -- partially cached

Column-Wise Purging

Here is another example for column-wise purging. As same as row-wise purging, It caches all the possible queries.

SELECT id FROM users;
SELECT name FROM users WHERE id = 1;

UPDATE users SET name = 'Alice' WHERE id = 1;
SELECT id FROM users; -- cached
SELECT name FROM users WHERE id = 1; -- purged

Transaction Isolation

isuc also supports transaction isolation, which means one transaction has no effect on other transactions before the commit or the rollback.

A: START TRANSACTION;
A: SELECT * FROM id = 1;
B: START TRANSACTION;
B: SELECT * FROM id = 1;
B: UPDATE users SET name = 'Alice' WHERE id = 1;
B: SELECT * FROM id = 1; -- purged
B: SELECT * FROM id = 1; -- cached
A: SELECT * FROM id = 1; -- cached
B: COMMIT;
A: SELECT * FROM id = 1; -- purged
A: COMMIT;
A: SELECT * FROM id = 1; -- cached

isuc achieves this behavior by MVCC-like algorithm. Recording when a transaction created and not using cache created before the transaction started.

Wrapping Up

Try using isuc in your own projects and let us know your feedback! We also welcome you to star our repository.

Tomorrow, @ramdos will write an article. Happy Coding!