A million-element IN clause MySQL absorbed took Mercari's TiDB cluster down with it

Mercari's Database Reliability Engineering team moved a high-load batch read endpoint from MySQL to TiDB and then watched query speed degrade sharply on an hourly cycle, with MySQL-to-TiDB sync lag and commit durations rising at the same moments ^[3][6][7]. The cause was a single query whose Index Join generated an internal IN list of close to one million elements, and the damage did not stay inside that query ^[8][11].

That last point is the operational one. Normally a bad query hurts itself and its neighbours; here reads and writes unrelated to the offending statement slowed too, which is why the team treated it as a cluster stability problem rather than a tuning ticket ^[9][10]. TiDB Cloud support traced it to Protobuf deserialization in TiKV's gRPC threads: the oversized IN clause monopolised those threads, so every subsequent request sharing them queued, and cluster latency spiked ^[12].

The timing was unhelpful. The incident surfaced immediately before Mercari's largest cluster switchover, during a period when MySQL and TiDB ran in parallel and some endpoints had already been pointed at TiDB ^[2]. The cutover plan had three steps in order: OLAP read endpoints, OLTP read endpoints, then writes ^[4]. The team was still on step one ^[5], which leaves two of the three phases ahead of them ^[20]. And before switching, they had replayed ProxySQL's live MySQL traffic against TiDB with an in-house tool, checking both query compatibility and TiDB load, and fixed what that found [c5a]. The post says it will explain why replay did not catch this one; the published excerpt stops before the explanation ^[19].

Containment was fast because it had been planned for. The team read the slow query list, identified the query, and used ProxySQL's mysql_query_rules to route that one query digest back to MySQL, an operation they had anticipated in the migration plan and still use ^[13][14]. Two caveats worth copying into your own runbook: you cannot redirect a single query inside a transaction this way ^[15], and ProxySQL's digests are not compatible with those from pt-query-digest or TiDB, so you have to read the digest out of stats_mysql_query_digest and paste that exact value into the rule ^[16].

PingCAP's first-aid was to cap scan-range memory with tidb_opt_range_max_size = 1048576, which is one mebibyte ^[17][21], and to reduce Index Join parallelism with tidb_index_lookup_join_concurrency = 2, a variable now deprecated in favour of tidb_executor_concurrency ^[17]. The team also priced up resource control to deprioritise the query and scaling TiKV up, and concluded that all of the no-code-change workarounds contributed only marginally [c18a]^[18]. The actual fix was to stop shipping a million IDs through gRPC at all: store them in a temporary table instead of embedding them in SQL [c18b]. That required application work, which meant interrupting a product team's priorities with no guaranteed completion date [c18c].

Reproducing the problem cost real effort too. Building an independent cluster carrying the same data hit resource exhaustion, repeated restore failures against bandwidth quotas, and a data pipeline that fell behind while catching up, because a new TiCDC changefeed's load was not isolated from the changefeed already in production ^[22][23].

Watch for the follow-up posts: the changefeed isolation writeup, and whether the OLTP read and write cutovers turn up more query shapes that only misbehave at production scale.