In SSD, overwriting a data file means SSD has to first erase it and then write data. This can change the data layout in the SSD for the data file.

MySQL has some policies to optimize random IO, e.g., read-ahead (pre-fetching). The read-ahead policy will pre-read the remaining x pages in an "extent" if prior y pages are read sequentially. From the perspective of MySQL, using consecutive offsets for pread can make reads sequential. But I doubt is it true for an SSD. In other words, pread can only confirm the logically sequential but not physically sequential?

This points to another important question: does an extent (usually contains 64 pages) in MySQL must represent consecutive space for SSD.

So if I am right, why do databases still make a substantial effort in converting random IO to sequential IO?

  • How big is the "erase" on your SSD? (The only one I have heard of is 8MB.)
    – Rick James
    May 6, 2022 at 17:10

1 Answer 1


Nowadays, there are at least two levels of abstraction, often more, between the data file the DBMS works with and the physical storage device blocks. What appears as a contiguous file to the reader is not necessarily contiguous in the filesystem, and even if it it, that's not necessarily a contiguous span of blocks on the underlying device(s). So, no, the database can't possibly know how sequential its sequential read actually is.

As to why, firstly, there's history. When storage architectures were simpler, DBMSes used to operate much closer to the hardware, often working with raw devices, where they had exact control over physical placement of data file pages/blocks and could guarantee the sequential nature of I/O.

Secondly, there's no harm in attempting sequential reads. If the underlying physical blocks happen to be in fact sequential, we get some benefit out of it; if not, well, at least we tried.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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