← All IntelClip / EducationThe f-sync mystery: why MySQL writes more than napkin math predicts
From Fireside Chat: Simon Eskildsen on Building Turbopuffer · ≈19:00
“Well, an f-sync takes one millisecond. So, you do a thousand writes per second.”
“So that was one of those things where I tested and it's like, okay, well, my SQL on a little dinky box could do 10,000 writes per second.”
“Because you batch. So an f-sync happens on usually a 4K.”
“I'm convinced. Yeah.”
What’s in it
- Explains why MySQL can do far more writes per second than raw fsync speed implies
- Tells a war story of debugging a mysterious database write-performance discrepancy
- Reveals that fsync calls get batched at the 4K block level to boost throughput
Clip transcript
and see if this is the difference to your query. Well, like is the query plan correct? Like is there a bug in my SQL? Do we have bad discs? Like what's the discrepancy here? And I just got caught with that bug. And so after I left Sha, I was just writing a lot of articles about this. I was just like, well, how long does should this query take? And then I one hypothesis I had at some point is like, okay, well, how many writes per second can MySQL do? Well, shouldn't the amount of writes per second that MySQL do equal the amount of f-syncs that you can do per second? That sort of makes sense, right? Every time you do a write, you f-sync to persist to disk. So, how many f-syncs can you do per second? Well, an f-sync takes one millisecond. So, you do a thousand writes per second. That well, that doesn't really match up. Like, feel like a database can do more than,000 rightes per second. Why can it do that? So, that was one of those things where I tested and it's like, okay, well, my SQL on a little dinky box could do 10,000 writes per second. Well, how is that possible? Mhm. >> And now you would just ask how is it possible? >> Because you batch. So an f-sync happens on usually a 4K. >> Yeah. >> Right. But it's like that's not intuitive. Like it's actually I I I got caught. It was like just like you know probably some like 24-hour period where I just got obsessed with this question where like you're writing like the BPF traces and all of that to do all of this. This is like pre-LM so it took forever and you and then I found out that oh every f-sync was like much larger than I would have inferred like oh it's batching you go into the code and you read it and then you found some obscure article by it's always somewhere in like a central German town that's like written some article about like how some intricacy of my SQL works and a patch that they did to it's like the entire internet runs on small towns in Bavaria. I'm convinced. Yeah.
Comments
Sign in to comment.
Loading comments…