Can database confirm the sequential read is really sequential in SSD

All we need is an easy explanation of the problem, so here it is.

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 to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply