Random write/read is slower than sequential for HDDs and some SSDs. Some database operations (UPDATE .. WHERE ..) may cause many random writes/reads. So I want to know if modern databases implement mechanisms to optimize them (e.g., innodb_flush_neighbors in MySQL).

The database can be relational, key-value, No-SQL, or any other kinds of databases.

  • You seem to have answered your own question. – mustaccio Feb 22 at 14:16
  • So only MySQL has such optimization? I want to know if this is a common optimization in db. Thanks. – haochen he Feb 23 at 1:40
  • Why is this important to you? What's going to happen if "only MySQL has such optimization"? And what if some other DBMSes do that? Or all of them? – mustaccio Feb 23 at 1:53
  • I am researching on "mis-optimization" in DBMS while using new types of storage media. For example, Intel Optane can provide excellent random write/read performance. Given that, converting ramdom to sequential could be harmful for performance. But if such conversion is not prevelant among DBMS, the "mis-optimization" shall not be worried about. – haochen he Feb 24 at 11:14

Yes. Pretty much every relational database is optimised for random IO (read: OLTP work). This is implemented in the buffer pool, page swap-out strategy, checkpointing, the write-ahead log and the recovery protocol (Aries mostly).

The hardware configuration can affect this, too. For example smaller block sizes are typical. Disk buffering helps.

Schema design is significant. Using compressed columnar storage on write-intensive work is a poor choice, for example, whereas it is an excellent choice for write-once analytical type work.

  • Thanks for the reply. Can we turn off these strategies (i.e., page swap-out strategy, checkpointing) easily, say, via configurations? – haochen he Feb 24 at 11:18
  • Mostly no. They're so inter-dependent that choosing an implementation for one problem affects the others' implementations. These choices get baked in. It's not impossible to parameterize them but unlikely to be worth the engineering effort in a commercial product. – Michael Green Feb 25 at 0:48
  • @MichaelGreen There's a big move these days (or at the very least, a lot of marketing brouhaha) about HTAP (Hybrid Transactional Analytical Processing) engines or (the appalling neologism) Translytical (sounds like something for which one should be seeing a medical professional) dbs. – Vérace Mar 7 at 16:22

Not the answer you're looking for? Browse other questions tagged or ask your own question.