Shlemiel the Painter Is Back: How Zig’s libc Rewrite Made SQLite Slower

本文为原文前 6,000 字符的节选翻译，完整内容请查看原文。

Shlemiel the Painter Is Back: How Zig’s libc Rewrite Made SQLite Slower

Spolsky Wisdom Back in 2001, Joel Spolsky – whose writings had a big influence my career – wrote a great piece titled Back to Basics. The premise of the piece is that practitioners should understand the basics: However abstract and remote we may seem from the basic algorithms that drive computing, they can unexpectedly have enormous consequences on our projects. One of his examples is the “Shlemiel the Painter” trap, where a seemingly simple choice leads to quadratic runtime complexity. In that parable, a street line painter never moves his can of paint. As he gets further and further along in the line, increasingly more of his time is spent walking to and from his can of paint. It is astonishing how often this Shlemiel the Painter pattern recurs, including now in 2026.

Spolsky 的智慧 早在 2001 年，Joel Spolsky（他的文章对我职业生涯影响深远）写了一篇名为《回归基础》（Back to Basics）的精彩文章。该文的前提是从业者应该理解基础知识：无论我们看起来距离驱动计算的基础算法有多么抽象和遥远，它们都可能对我们的项目产生意想不到的巨大后果。他举的例子之一是“Shlemiel 画家”陷阱，即一个看似简单的选择导致了二次方的时间复杂度。在这个寓言中，街头划线工人从不移动他的油漆桶。随着他在线条上走得越来越远，他花在往返油漆桶上的时间也越来越多。令人惊讶的是，这种“Shlemiel 画家”模式竟如此频繁地重现，甚至在 2026 年的今天依然如此。

My Chess Analysis Service Staggers Under Load A few days ago I posted about a chess analysis service I put online. This is primarily a Rust service backed by sqlite that dishes out analysis tasks to runners. Each runner processes upwards of hundreds of chess games and return a large set of JSON bundles back to the main service, where they are processed and then stuffed into sqlite. While the analysis pegs two dozen cores on a runner for minutes on end, the process of storing the data should take milliseconds. Sub-milliseconds. Imagine my surprise when on the first really large set of games, the simple process of inserting the JSON data – maybe about 40,000 records – into the data store took minutes. During all of my testing this never really appeared as a critical path, but with a large set of records it suddenly was a breaking problem. What made this more perplexing was that it completely saturated a core, being a CPU-bound operation. How could inserting simple JSON into SQLite yield minutes of CPU time? Shlemiel the painter has made an appearance. To drop a spoiler, he was introduced to the project via Zig’s cc compiler and its replacement for standard library functions.

我的国际象棋分析服务在负载下步履蹒跚 几天前，我发布了一个我上线运行的国际象棋分析服务。这主要是一个由 SQLite 支持的 Rust 服务，负责向运行器分发分析任务。每个运行器处理数百场国际象棋比赛，并将大量 JSON 数据包返回给主服务，在那里进行处理并存入 SQLite。虽然分析过程会让运行器的二十几个核心持续满载数分钟，但存储数据的过程本应只需几毫秒，甚至不到一毫秒。想象一下，当我在处理第一批真正庞大的比赛数据时，将大约 40,000 条 JSON 记录插入数据存储的简单过程竟然耗费了数分钟，我感到多么惊讶。在我所有的测试中，这从未表现为关键路径，但面对大量记录时，它突然变成了一个致命问题。更令人困惑的是，它完全占满了一个 CPU 核心，成为了一项 CPU 密集型操作。将简单的 JSON 插入 SQLite 怎么会耗费数分钟的 CPU 时间？“Shlemiel 画家”出现了。剧透一下，他是通过 Zig 的 cc 编译器及其对标准库函数的替换被引入到项目中的。

A Minor Issue In Zig Becomes A Major Performance Issue The project isn’t built in Zig, but instead uses Rust. The sqlx sqlite crate needs to be compiled for the target environment – sqlite is of course a C project – so to facilitate cross-platform building, generating both Mac ARM64 and Linux ARM64 / x86-64 binaries from this machine in a convenient build and deploy workflow, zig cc is used to compile sqlite when targeting Linux, generously offering up cross-platform targeting via cargo-zigbuild. From one build and deploy script on my Mac I can target all of my platforms without containers or build machines. Zig’s elegant libzigc re-implements the standard library so you can static link the necessary functionality. Zig uses musl (the standard library rewrite), but they have been rewriting it piece by piece in pure Zig (via libzigc), removing the C dependencies one at a time. And that’s where the performance issue was introduced. In the rewrite of strspn, replacing the musl version with the pure zig variant), the first step measures the entire length of the string. This is also seen in the rewrites of strcspn, strpbrk and strstr. This is a problem for tokenizers. A tokenizer takes a giant string and a common iterating step is to skip whitespace, getting the new string location and then repeating this pattern repeatedly. With this shlemiel the painter pattern introduced with these rewrites, as it steps through the text it is tokenizing it is remeasuring the entire length of the string endlessly. This is what caused my performance issue, and it’s why it appeared on Linux but was never seen on my Mac (where zig wasn’t used for compiling sqlite). Tokenizing a 1.2MB file, which was seen passing json to sqlite, is approximately 12,000x slower with this pattern. As the file gets larger, the slowdown is quadratic.

Zig 中的一个小问题演变成重大的性能问题 该项目并非使用 Zig 构建，而是使用 Rust。sqlx sqlite crate 需要针对目标环境进行编译（SQLite 当然是一个 C 项目），因此为了方便跨平台构建，在我的机器上通过便捷的构建和部署工作流生成 Mac ARM64 和 Linux ARM64 / x86-64 二进制文件，我在针对 Linux 进行编译时使用了 zig cc，通过 cargo-zigbuild 慷慨地提供了跨平台目标支持。通过 Mac 上的一个构建和部署脚本，我可以在没有容器或构建机器的情况下针对所有平台进行编译。Zig 优雅的 libzigc 重新实现了标准库，因此你可以静态链接所需的功能。Zig 使用 musl（标准库重写版），但他们一直在用纯 Zig（通过 libzigc）一点一点地重写它，逐一移除 C 依赖。性能问题就是在这里引入的。在重写 strspn 时，用纯 Zig 变体替换 musl 版本，第一步就是测量整个字符串的长度。在 strcspn、strpbrk 和 strstr 的重写中也出现了这种情况。这对分词器来说是个问题。分词器处理一个巨大的字符串，常见的迭代步骤是跳过空白字符，获取新的字符串位置，然后重复此模式。由于这些重写引入了“Shlemiel 画家”模式，当它遍历正在分词的文本时，会不断地重新测量整个字符串的长度。这就是导致我性能问题的原因，也是为什么它出现在 Linux 上，而在我的 Mac 上（未使用 Zig 编译 SQLite）却从未出现的原因。使用这种模式对一个 1.2MB 的文件进行分词（在向 SQLite 传递 JSON 时观察到）大约慢了 12,000 倍。随着文件变大，减速呈二次方增长。

The Classic Issues Recur I mean no malice or disrespect to anyone involved with these great tools that enable so much. But it’s interesting that in the year 2026, these same old issues can recur, and we all need to be vigilant for them. Something so trivial was grinding a process to a halt, in the most unexpectedly trivial of places. My original fix for this issue, at least in my own code, was to pre-prune whitespace from JSON, which greatly reduced the cost when tokenizing in sqlite3, but not knowing where else it might bite I moved my Linux builds over to Linux containers.

经典问题重现 我无意冒犯或不尊重任何参与这些伟大工具开发的人，正是这些工具成就了如此多的可能。但有趣的是，在 2026 年，这些同样的老问题依然会重现，我们都需要对此保持警惕。如此琐碎的事情竟然在最意想不到的琐碎地方让一个进程陷入停滞。我最初针对此问题的修复方案（至少在我自己的代码中）是预先剔除 JSON 中的空白字符，这大大降低了在 sqlite3 中进行分词时的成本，但由于不知道它还会在哪里造成影响，我将我的 Linux 构建迁移到了 Linux 容器中。