TLDR Tech / Practical
Read-only query's unexpected terabyte disk writes explained
An investigation into why a read-only MonetDB query generated terabytes of disk writes, contrary to expectations of random I/O, revealed the issue stemmed from the Linux kernel's page cache and writeback policies. These mechanisms treated database scratch files like regular files, causing dirty pages to be written to disk every 30 seconds. Solutions like using tmpfs for scratch files or adjusting Linux sysctl writeback settings dramatically reduced query execution time from over an hour to under two minutes, eliminating disk I/O.
Read the full story