lobste.rs 现已运行在 SQLite 上

Lobsters Hottest 新闻
database-migration sqlite mariadb lobste-rs infrastructure open-source web-development

摘要

Lobste.rs 已将其数据库从 MariaDB 迁移到 SQLite，从而降低了 CPU 和内存使用率，减少了成本，并提升了网站性能。本文详细介绍了迁移过程和背景。

<p>上周六，<a href="https://lobste.rs/~pushcx" rel="ugc">@pushcx</a> 和我将 <a href="https://github.com/lobsters/lobsters/pull/1927" rel="ugc">SQLite 拉取请求</a> 部署到了生产环境。我们一直等到今天早上，观察它在周一流量高峰下的表现后才发布这篇博文。不用说，SQLite 似乎已经出色地通过了考验：CPU 使用率下降，内存使用率下降，至少对我来说网站似乎更灵敏了，一旦移除 MariaDB 的 VPS，成本就能降低一半，而且最终 <a href="https://gist.github.com/pushcx/eb7cdf2dc9707dc3ab9e7173d197ddfc?permalink_comment_id=6251610#gistcomment-6251610" rel="ugc">“我们度过了一个平静的周一。”</a> 最终，<a href="https://github.com/lobsters/lobsters/issues/539#issuecomment-4959857588" rel="ugc">#539 迁移到 SQLite</a> 也在今早关闭了。</p> <p>如果您对迁移有任何疑问，请告诉我们。</p> <p>背景故事：</p> <p>我参与这次迁移是因为在 2019 年，我偶然发现了 <a href="https://github.com/lobsters/lobsters/issues/539" rel="ugc">#539</a>，并且由于我在管理和迁移大型数据库方面有丰富的经验，我留下了一条 <a href="https://github.com/lobsters/lobsters/issues/539#issuecomment-509041532" rel="ugc">评论</a>，建议使用 MySQL 作为替代方案，因为 MariaDB 和 MySQL 之间具有兼容性。当时我并没有计划参与其中，因为已经有关于迁移到 PostgreSQL 的讨论在进行。</p> <p>时间快进到 2025 年，<a href="https://github.com/lobsters/lobsters/issues/539#issuecomment-2604683262" rel="ugc">Rahul 留下了一条评论</a>，提到了 K1 收购 MariaDB 一事。随后进行了关于迁移到 PostgreSQL 细节的讨论。然后在二月份，<a href="https://github.com/lobsters/lobsters/issues/539#issuecomment-2640076884" rel="ugc">Rahul 问道：“lobsters 能在 SQLite 上运行吗？”</a>，其中包含了一篇关于 SQLite 的非常详细的文章。</p> <p>我在 <a href="https://github.com/lobsters/lobsters/issues/539#issuecomment-2956952922" rel="ugc">2025 年 6 月</a> 正式表达了接手这个项目的兴趣。我想这可能在 <a href="https://lobste.rs/s/zzprkr/weekly_lobsters_office_hours" rel="ugc">lobsters 办公时间</a> 中被提及过，但时间过去太久了，我不太确定了。</p> <p>在 <a href="https://github.com/lobsters/lobsters/issues/539#issuecomment-3198784051" rel="ugc">2025 年 8 月</a>，我提交了 <a href="https://github.com/lobsters/lobsters/pull/1705" rel="ugc">第一次拉取请求尝试</a>，但当时我忙了起来，无法处理那个 PR。GitHub 因为长期不活动而关闭了它，我无法重新打开，于是我提交了另一个 PR。<a href="https://github.com/lobsters/lobsters/pull/1871" rel="ugc">第二次 PR 尝试</a> 包含了一些性能测试、一个 <a href="https://github.com/lobsters/lobsters/blob/main/lib/tasks/migrate.rake" rel="ugc">数据库 x 到数据库 y 的迁移脚本</a>（因为现有的 MariaDB/MySQL 到 SQLite 的脚本没有一个让我满意），以及围绕数据完整性的调试和思考。</p> <p>然后是 2 月 21 日的第一次部署。<a href="https://lobste.rs/~pushcx" rel="ugc">@pushcx</a> 和我进行了一次电话沟通，为部署制定了一个 <a href="https://gist.github.com/pushcx/eb7cdf2dc9707dc3ab9e7173d197ddfc/a11e6d5865776f3bb4e1adbc56582a3b1f99878c" rel="ugc">核对清单</a>。一切进展顺利，直到部署 PR。部署后，网站处于只读模式，但仅仅是只读流量就使所有 CPU 飙升到 100%。我们无法查明问题所在，于是决定 <a href="https://github.com/lobsters/lobsters/pull/1924" rel="ugc">回滚</a>。第一次部署失败后，我感觉很糟糕，因为我知道由于无法访问生产数据库，性能可能是个问题。</p> <p>在失败部署两天后，我提交了 <a href="https://github.com/lobsters/lobsters/pull/1927" rel="ugc">第三次也是最后一次 PR 尝试</a>。我修复了在失败部署中发现的一些 <a href="https://github.com/lobsters/lobsters/pull/1927/changes/e7630ffe5c0df9fc0a388db5b6b6e11eb4a4c407" rel="ugc">搜索小问题</a>，创建了一个 <a href="https://github.com/lobsters/lobsters/pull/1927/changes/c3c12e6f134c9d67c37c57a0390f9d3a2c9f26d8" rel="ugc">批量数据创建脚本</a>（花了一周时间才在本地创建了 lobsters 一半的数据集大小），并提交了三个修复第一次部署中性能问题的更改：<a href="https://github.com/lobsters/lobsters/pull/1927/changes/14a8c5f412cc23c887a5b809453596c02d8df6d1" rel="ugc">1</a>、<a href="https://github.com/lobsters/lobsters/pull/1927/changes/4dc58fc1d98ed393c4b447c71568bc0cb700896b" rel="ugc">2</a>、<a href="https://github.com/lobsters/lobsters/pull/1927/changes/4476ae3e50602b5e0d7805fef40fc94a81daaa39" rel="ugc">3</a>。性能问题归结为：SQLite 对数据库中最大的两个表进行了全表扫描，而第三个问题解决了一个 n+1 问题。在第二次部署的早晨，我还添加了一个 <a href="https://github.com/lobsters/lobsters/pull/1927/changes/a62fc572c068f7df45ade8a5fad117dafc68cc00" rel="ugc">慢查询日志</a>，以防部署期间出现更多性能问题。</p> <p>然后是 7 月 11 日的第二次部署。<a href="https://lobste.rs/~pushcx" rel="ugc">@pushcx</a> 和我在上午进行了一次电话沟通，制定了一个部署和回滚核对清单。一切进展顺利，然后 <a href="https://lobste.rs/~pushcx" rel="ugc">@pushcx</a> 合并并部署了 PR。部署后，网站仍然正常运行，CPU/内存使用率也保持良好。这让我大大松了一口气。我们监控了网站指标和 IRC，留意人们报告的问题，确实有几个人报告了问题，这些问题都得到了迅速修复：<a href="https://github.com/lobsters/lobsters/commit/1dd9d5e4d7ecc7f924a88c8792ec44f7c90d4691" rel="ugc">1</a>、<a href="https://github.com/lobsters/lobsters/commit/e88a07e1f121cbae772340c29e482399b1e92b75" rel="ugc">2</a>。总体来看，网站似乎运行正常，所以我们结束了通话，一直等到周一流量高峰。</p> <p>周一到了，网站仍然运行良好，所以我们认为这是一次胜利，继续前行。</p> <p>SQLite 经验教训：</p> <ol> <li>SQLite gem 支持用户自定义函数（UDF），我们用它来实现 SQLite 中一些缺失的函数，如 <a href="https://github.com/lobsters/lobsters/blob/main/config/initializers/sqlite_functions.rb" rel="ugc">regexp、if 和 stddev</a>，这样我们就不必处理太多的 SQL 迁移变通方案。</li> <li>SQLite 不支持无符号大整数。之前，MariaDB 对某些 ID 使用了无符号大整数，因此我们不得不在迁移中将这些 ID 切换为有符号大整数。</li> <li>SQLite 的排序规则比 MariaDB 弱得多。Lobste.rs 在 MariaDB 中使用了 utf8mb4_general_ci，但在 SQLite 中使用了 <a href="https://www.sqlite.org/datatype3.html#collating_sequences" rel="ugc">NOCASE</a>。NOCASE 的缺点是它只支持 ASCII 字符，不支持完整的 UTF 大小写折叠。</li> <li>对于全文搜索表，请使用 SQLite 中推荐的 <a href="https://www.sqlite.org/fts5.html#contentless_delete_tables" rel="ugc">Contentless-Delete 表</a>。这些不是默认选项。SQLite 的默认选择总是让我感到惊讶。</li> </ol> <p>Rails 经验教训：</p> <ol> <li>Rails 中的 <a href="https://github.com/rails/rails/blob/v8.0.5/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb#L84-L91" rel="ugc">默认 PRAGMA</a> 似乎对 lobsters 有效。</li> <li>通常情况下你不会想到，但数据库迁移是特定于数据库的。我不得不将旧的迁移文件移到一个 <a href="https://github.com/lobsters/lobsters/tree/main/db/old_migrations" rel="ugc">旧迁移</a> 目录中，以便 db:migrate 能够继续工作。</li> </ol> <p>Lobsters 代码库经验教训：</p> <ol> <li>有一个 ")}</li> </ol>
查看原文
查看缓存全文

缓存时间: 2026/07/13 21:56

# lobste.rs 现已运行在 SQLite 上 来源：https://lobste.rs/s/ko1ji1/lobste_rs_is_now_running_on_sqlite 上周六，@pushcx (https://lobste.rs/~pushcx) 和我将 SQLite pull request (https://github.com/lobsters/lobsters/pull/1927) 部署到生产环境。我们一直等到今天早上，观察它如何应对周一流量高峰后才发布这篇文章。毋庸置疑，SQLite 表现出色：CPU 使用率下降，内存使用率下降，至少对我来说网站响应更快了，在关闭 MariaDB VPS 后 VPS 成本减半，最后还有一点："我们迎来了一个平静的周一" (https://gist.github.com/pushcx/eb7cdf2dc9707dc3ab9e7173d197ddfc?permalink_comment_id=6251610#gistcomment-6251610)。最终，\#539 迁移到 SQLite (https://github.com/lobsters/lobsters/issues/539#issuecomment-4959857588) 于今天早上关闭。 如有关于迁移的任何问题，请告知我们。 背景故事： 我参与这次迁移，是因为 2019 年偶然发现了 \#539 (https://github.com/lobsters/lobsters/issues/539)，而且我在管理、迁移大型数据库方面经验丰富，于是留下了一条评论 (https://github.com/lobsters/lobsters/issues/539#issuecomment-509041532)，建议使用 MySQL 作为替代方案，因为 MariaDB 和 MySQL 兼容。当时我并不打算深入参与，因为已经在讨论迁移到 PostgreSQL 了。 时间快进到 2025 年，Rahul 留下了一条评论 (https://github.com/lobsters/lobsters/issues/539#issuecomment-2604683262)，提到 K1 收购了 MariaDB。随后围绕迁移到 PostgreSQL 的细节展开了讨论。接着在 2 月份，Rahul 问道："lobsters 能在 SQLite 上运行吗？" (https://github.com/lobsters/lobsters/issues/539#issuecomment-2640076884)，其中包含一篇关于 SQLite 的非常详细的帖子。 我正式表示有兴趣接手这个项目是在 2025 年 6 月 (https://github.com/lobsters/lobsters/issues/539#issuecomment-2956952922)。我想这可能在 lobsters 办公时间 (https://lobste.rs/s/zzprkr/weekly_lobsters_office_hours) 被提及过，但时隔太久，我不太确定了。 2025 年 8 月 (https://github.com/lobsters/lobsters/issues/539#issuecomment-3198784051)，我提交了第一次 pull request 尝试 (https://github.com/lobsters/lobsters/pull/1705)，但后来忙起来没顾上。GitHub 因长期未动将其关闭，我无法重新打开，于是又提交了一个 PR。第二次 PR 尝试 (https://github.com/lobsters/lobsters/pull/1871) 包含了一些性能测试、一个从数据库 x 到数据库 y 的迁移脚本 (https://github.com/lobsters/lobsters/blob/main/lib/tasks/migrate.rake)（因为现有的从 mariadb/mysql 到 sqlite 的脚本都不令我满意），以及对数据完整性的调试和思考。 然后是 2 月 21 日的第一次部署。@pushcx (https://lobste.rs/~pushcx) 和我通了电话，制定了一份部署检查清单 (https://gist.github.com/pushcx/eb7cdf2dc9707dc3ab9e7173d197ddfc/a11e6d5865776f3bb4e1adbc56582a3b1f99878c)。一切顺利，直到部署 PR 的那一刻。部署后网站进入只读模式，但仅只读流量就让所有 CPU 飙升到 100%。我们无法找出问题所在，于是决定回滚 (https://github.com/lobsters/lobsters/pull/1924)。第一次部署失败后我很沮丧，因为我知道由于无法访问生产数据库，性能可能会出问题。 失败部署两天后，我提交了第三次也是最后一次 PR 尝试 (https://github.com/lobsters/lobsters/pull/1927)。我修复了在失败部署中发现的一些搜索小问题 (https://github.com/lobsters/lobsters/pull/1927/changes/e7630ffe5c0df9fc0a388db5b6b6e11eb4a4c407)，创建了一个批量数据生成脚本 (https://github.com/lobsters/lobsters/pull/1927/changes/c3c12e6f134c9d67c37c57a0390f9d3a2c9f26d8)，花了一周时间才在本地生成了 lobsters 一半的数据集大小，并提交了三个修复第一次部署性能问题的变更：1 (https://github.com/lobsters/lobsters/pull/1927/changes/14a8c5f412cc23c887a5b809453596c02d8df6d1)，2 (https://github.com/lobsters/lobsters/pull/1927/changes/4dc58fc1d98ed393c4b447c71568bc0cb700896b)，3 (https://github.com/lobsters/lobsters/pull/1927/changes/4476ae3e50602b5e0d7805fef40fc94a81daaa39)。性能问题归结于 SQLite 对数据库中的最大表进行了两次全表扫描，第三次修复解决了 n+1 问题。在第二次部署当天早上，我还添加了慢查询日志 (https://github.com/lobsters/lobsters/pull/1927/changes/a62fc572c068f7df45ade8a5fad117dafc68cc00)，以防部署过程中出现更多性能问题。 然后是 7 月 11 日的第二次部署。@pushcx (https://lobste.rs/~pushcx) 和我早上通了电话，制定了部署和回滚检查清单。一切顺利，直到 @pushcx (https://lobste.rs/~pushcx) 合并并部署了 PR。部署后网站仍保持在线，CPU/内存使用率也正常。这让我大大松了一口气。我们监控了网站指标和 IRC，看是否有人提到问题。确实有几个问题，都得到了及时修复：1 (https://github.com/lobsters/lobsters/commit/1dd9d5e4d7ecc7f924a88c8792ec44f7c90d4691)，2 (https://github.com/lobsters/lobsters/commit/e88a07e1f121cbae772340c29e482399b1e92b75)。总体来看，网站似乎工作正常，于是我们结束了通话，等待周一的流量高峰到来。 周一来了，网站依然运行良好，所以我们认为这是一次胜利，继续前进。 SQLite 经验教训： 1. SQLite gem 支持用户自定义函数（UDF），我们用它来实现 SQLite 中缺失的一些函数，如 regexp、if 和 stddev (https://github.com/lobsters/lobsters/blob/main/config/initializers/sqlite_functions.rb)，这样就不必处理太多 SQL 迁移的变通方案。 2. SQLite 不支持无符号大整数。之前 MariaDB 对某些 ID 使用了无符号大整数，因此我们不得不将这些 ID 转换为有符号大整数以进行迁移。 3. 与 MariaDB 相比，SQLite 的排序规则较弱。Lobste.rs 在 MariaDB 中使用了 utf8mb4\_general\_ci，但在 SQLite 中使用了 NOCASE (https://www.sqlite.org/datatype3.html#collating_sequences)。NOCASE 的缺点是只支持 ASCII 字符，不支持完整的 UTF 大小写折叠。 4. 对于全文搜索表，请使用 SQLite 推荐的 Contentless-Delete 表 (https://www.sqlite.org/fts5.html#contentless_delete_tables)。这不是默认选项。SQLite 的默认选择总是让我惊讶。 Rails 经验教训： 1. Rails 中的默认 PRAGMA (https://github.com/rails/rails/blob/v8.0.5/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb#L84-L91) 似乎对 lobsters 工作良好。 2. 通常你不会想到这一点，但数据库迁移是数据库特定的。我不得不将旧的迁移文件移到一个旧迁移目录 (https://github.com/lobsters/lobsters/tree/main/db/old_migrations) 下，以便 db:migrate 继续正常工作。 Lobsters 代码库经验教训： 1. lobsters 代码库中存在一个搜索解析器 (https://github.com/lobsters/lobsters/blob/main/app/models/search_parser.rb)。 2. 我了解到了 heinous\_inline\_partials (https://github.com/lobsters/lobsters/blob/main/config/initializers/heinous_inline_partial.rb)，这是一个加速渲染的 hack。 3. lobsters 的测试套件至关重要，确保了我能够在没有大量手动测试的情况下完成向 SQLite 的迁移。 总体经验教训： 1. 我认为成功的关键因素是参与者的良好沟通。否则不可能实现。 2. 在没有访问生产数据库的情况下迁移底层数据库非常困难。这是我第一次在没有生产环境访问权限的情况下进行底层数据库迁移。我从中汲取的一个教训是，未来再进行此类迁移前，要确保有接近真实大小的数据集。 愿望： 1. 我希望能在测试中这样说："如果遇到任何全表扫描就失败"。这可以在第一次部署时就捕获到我们遇到的性能问题。 2. 我希望创建类似生产环境的数据集会比手动编写脚本并等待一周要容易得多。

相似文章