Mastodawn

https://blog.gslin.org/archives/2026/02/21/12901/mariadb-12-3-%e5%b0%8d-innodb-%e7%9a%84%e5%a4%a7%e6%94%b9%e9%80%b2/

MariaDB 12.3 對 InnoDB 的大改進

#binlog #cpu #database #db #engine #fsync #i/o #innodb #latency #mariadb #mysql #performance #rdbms #speed #storage #write

MariaDB 12.3 對 InnoDB 的大改進

看到 Mark Callaghan 寫的「MariaDB innovation: binlog_storage_engine」、「MariaDB innovation: binlog_storage_engine, small server, Insert Benchmark」這篇，裡面提到了 MariaDB 12.

Gea-Suan Lin's BLOG

卡拉今天看了什麼 Aug 25, 2025

SQLite (with WAL) doesn't do fsync on each commit under default settings | Hacker News

LinkSQLite WAL 模式下的 fsync 預設與實務影響分析
https://www.sqlite.org/pragma.html#pragma_synchronous

📌 Summary:
本文主要探討 SQLite 在啟用 Write-Ahead Logging（WAL）模式時，PRAGMA synchronous 設定與 fsync 行為之間的關係，以及不同作業系統和編譯環境下的預設差異。SQLite 的 WAL 模式能提升寫入效能，但在預設的 synchronous=NORMAL 狀態下，交易提交並不會在每次操作時都呼叫 fsync，導致系統崩潰或斷電時可能回滾部分已提交交易。相對地，設定為 FULL 會增加交易耐久性，確保每次提交後都執行同步操作，降低資料遺失風險。文中以 macOS 內建 sqlite 與 Homebrew 版本為例，指出兩者在此設定上的不同，且編譯階段的宏定義（SQLITE_DEFAULT_SYNCHRONOUS 與 SQLITE_DEFAULT_WAL_SYNCHRONOUS）通常預設為 FULL，但發行版或包裝方式可能改變預設，產生行為差異。文章並輔以 Hacker News 社羣討論，引伸對於資料庫耐久性預設的安全性疑慮、不同檔案系統、fsync 性能影響以及應用場景需求的範例分析，提醒使用者必須理解底層配置與預設差異，並根據實務需求調整參數，而非盲目依賴默認設定。

🎯 Key Points:
→ 🔧 SQLite WAL 模式與同步機制：
★ WAL 模式預設 journal mode 為 WAL，優化寫入效能；
★ PRAGMA synchronous 控制 fsync 行為，影響資料耐久性與系統穩定性；
★ synchronous=NORMAL（數值1）表示不對每次提交呼叫 fsync，僅於檢查點前後同步，可能導致部分交易因斷電丟失；
★ synchronous=FULL（數值2）確保每次交易提交後同步 WAL 檔案，提升耐久性；

→ 💻 系統與編譯差異：
★ macOS 內建 sqlite3 版本默認為 WAL 模式下同步為 NORMAL；
★ Homebrew 版本默認為 FULL，顯示編譯宏設定差異；
★ 編譯預設宏 SQLITE_DEFAULT_SYNCHRONOUS 和 SQLITE_DEFAULT_WAL_SYNCHRONOUS 皆預設值為 FULL，但發行包裝可能自行調整；
★ 不同 Linux 發行版 Docker 映像大多維持 FULL 為預設；

→ 🗣 社羣討論與應用影響：
★ 斷電後資料持久性及交易一致性成為關鍵，尤其金融或加密貨幣等重要場景；
★ 許多應用為提升效能採用 NORMAL，但犧牲了部分交易的耐久保障；
★ fsync 操作較耗費資源且影響性能，故預設採折衷策略；
★ 使用者應明確檢視並調整關鍵設定以符合應用需求，避免默認帶來資料遺失風險；
★ 針對 Apple 平臺 fsync 行為存在特殊處理，如使用 F_FULLFSYNC 或 F_BARRIERFSYNC ；
★ SQLite 不同默認設置的不透明性亦可能導致開發者誤解資料庫的安全保證；

🔖 Keywords:
#SQLite #WAL模式 #PRAGMA_synchronous #fsync #資料耐久性

SQLite (with WAL) doesn't do `fsync` on each commit under default settings | Hacker News

GripNews Aug 24, 2025

🌖 SQLite (WAL 模式) 預設不對每次提交執行 fsync
➤ 深入解析 SQLite WAL 模式的資料持久性與同步機制
✤ https://avi.im/blag/2025/sqlite-fsync/
這篇文章探討了 SQLite 在寫入日誌模式 (WAL) 下，預設同步設定 (NORMAL) 並不保證每次交易提交時都執行 fsync 的行為。作者指出，這可能導致在斷電或系統崩潰時資料遺失。文章進一步說明，若要確保每次提交都執行 fsync 以提高資料持久性，需將 `synchronous` PRAGMA 設定為 `FULL`。作者也修正了先前對 macOS 預設行為的誤解，並提及編譯時選項 `SQLITE_DEFAULT_SYNCHRONOUS` 可決定預設值。
+ 感謝作者的詳細解釋，終於釐清了 WAL 模式下的同步問題，原來預設並不總是那麼安全。
+ macOS 上 SQLite 的預設行為確實會讓人誤會，幸好有作者的更新和對編譯選項的說明。
#資料庫 #SQLite #WAL #fsync #資料持久性

SQLite (with WAL) doesn't do `fsync` on each commit under default settings - blag

SQLite when used with WAL doesn’t do fsync unless specified.

Hacker News Aug 24, 2025

SQLite (with WAL) doesn't do `fsync` on each commit under default settings

https://avi.im/blag/2025/sqlite-fsync/

#HackerNews #SQLite #WAL #fsync #performance #database #commit #storage

SQLite (with WAL) doesn't do `fsync` on each commit under default settings - blag

SQLite when used with WAL doesn’t do fsync unless specified.

GNU/Trinukso ۞Mar 24, 2025

Ha dicho Pierre-Loup Griffais (dev de #Valve) que no nos flipemos con #ntsync que ya existe #fsync y que es tan rápido o incluso más. Que ntsync encaja bien como una solución general para #Wine.

https://bsky.app/profile/plagman.bsky.social/post/3lkp26xmco22k

Vía: @gamingonlinux.com

Pierre-Loup Griffais (@plagman.bsky.social)

We already include fsync, which should be as fast or faster as ntsync. We developed ntsync as a general solution that'd be acceptable in upstream Wine, but there's no urgency in including it in the Deck / SteamOS kernel.

Bluesky Social

Mrco Mar 18, 2025

@linuxpoweruser @cosmic_happiness I know. But most #windows #gamers still don't!

And yes, for many #linuxgamers this kernel update will bring partly huge performance update, but not for those already using some #fsync kernel on #distros like #nobara for example.

Rob 💚Mar 13, 2025

I've been studying fsync() for a couple of years and I thought I'd seen every weird way that it could be used and misused, but this week I learned of one more that exists in the wild: if the program crashes between write() and fsync(), on restart, fsync() after open() to ensure those writes are on disk.

I am pretty sure this doesn't do what people think it does, and here's why:

https://despairlabs.com/blog/posts/2025-03-13-fsync-after-open-is-an-elaborate-no-op/

#fsync #Linux #FreeBSD #OpenZFS

fsync() after open() is an elaborate no-op

I have spent the last couple of years of my life trying to make sense of fsync() and bringing OpenZFS up to code. I’ve read a lot of horror stories about this apparently-simple syscall in that time, usually written by people who tried very hard to get it right but ended up losing data in different ways. I hesitate to say I enjoy reading these things, because they usually start with some catastrophic data loss situation and that’s just miserably unfair. At least, I think they’re important reads, and I’m always glad to see another story of fsync() done right, or done wrong.

despair labs

Habr Mar 12, 2025

Синхронизация файлов при запуске экземпляра PostgreSQL

Если экземпляр PostgreSQL был некорректно остановлен, то перед восстановлением файлов выполняется синхронизаций всех файлов кластера. Способ синхронизации определяется параметром конфигурации recovery_init_sync_method . В статье рассматривается, как ускорить запуск экземпляра и резервирование, если в директории PGDATA имеется много файлов.

https://habr.com/ru/articles/890354/

#postgresql #postgres #постгрес #pg_basebackup #fsync

Синхронизация файлов при запуске экземпляра PostgreSQL

Хабр

Hacker

Jan 30, 2025

#NTsync (title not approved) is more "Correctness" and "Robustness" alternative implementation of synchronization primitives in #Wine from Zebediah Figura (the author of "#Esync" and "#Fsync"). https://github.com/Frogging-Family/wine-tkg-git/issues/936

NTsync (aka Winesync+Fastsync) - feedback topic · Issue #936 · Frogging-Family/wine-tkg-git

NTsync (title not approved) is more "Correctness" and "Robustness" alternative implementation of synchronization primitives in Wine from Zebediah Figura (the author of "Esync" and "Fsync"). This re...

GitHub

Atemu Dec 14, 2024

So finally got the #ntsync #wine patches applied to #Proton and went to test it on #GuildWars2.

In the Omphalos chamber in the Grove, #fsync and #esync yielded about 74fps, both off yielded 84 (odd, I know).

After allowing my user to access the ntsync device...

Uhh, I'll have to disable my FPS limiter real quick..

146 fps. Holy.

I'm going to be testing this on #Planetside2 the coming days where it actually matters. The times of bad FPS in CPU-bound games on #Linux might be over soon lads.