A general question about how arch linux stores files on SSD

[Gary1]

I was watching the progress and write-speed estimates while writing a 15 GB SQLite database to a USB flash drive and noticed that the speed varies from a short-interval high of over 400 MB to a short-interval low of 20 MB; and not just that it starts out fast and continually slows down but it fluctuates during the entire write time.

That caused me to wonder how a database file is stored on disk. Is it scattered all over the drive in pieces, at most as large as a single database write?

If one performs a vacuum of some kind on the database, will it then be written anew as one giant block or will it still be scattered and only the empty blocks are released?

When I plug the drive into another machine to backup the database a second time, it writes much quicker from the flash drive to the machine than from the macine to the drive. Both machines have SSD.

I ask for two reasons.

1. Out of curiosity over the mentioned write speeds and wondered if the speed increases when a large part of the data is close together on the hard drive, such as when I wrote a large number of BLOBs in one script; and is slower when it has to collect data from many, many updates made over time to smaller rows of data.
2. I wonder what happens to database efficiency over time when the data is often updated, such as using a piece table for editing text files.

I read somewhere that I cannot recall at the moment that Linux store data on disk differently than Windows such that it sort of “self-corrects” in that on HDD there is no need to de-fragment the disk. I’m using SSD but am asking with respect to similar thought of a database being “fragmented” across an SSD.

Thank you.

[BG405]

“Fragmentation” on an SSD won’t impact data access in the same way it does on spinning disks and indeed, ext4 for example doesn’t seem to suffer anything like the same level of fragmentation as the likes of NTFS anyway even on those ancient rusticles some of us still need to use.

An SSD, as far as I’m aware, doesn’t necessarily allocate “contiguous” data the way a traditional spinning disk does, anyway?

[Pille]

@Gary1

and not just that it starts out fast and continually slows down but it fluctuates during the entire write time.

I notice that every time i am copying a larger file to an external SSD drive.
The first 3-4 GB are running fast, the rest with the max. physical speed of the disk.

I think it depends on the buffer size of the device where the write attempts are made and not on fragmentation that much.
But that’s my personal opinion from my experience with this topic

[Aragorn]

@Gary1

That caused me to wonder how a database file is stored on disk. Is it scattered all over the drive in pieces, at most as large as a single database write?

Most databases are made up of multiple files, but at the filesystem level, there is no distinction between database files and other files. Besides, in UNIX, everything is a file.

That said, files can get fragmented over time, and the type of filesystem used also plays a role in that. btrfs for instance uses copy-on-write, which is not possible without introducing at least some fragmentation. But on an SSD, fragmentation is irrelevant anyway because there are no moving parts.

@Gary1

If one performs a vacuum of some kind on the database, will it then be written anew as one giant block or will it still be scattered and only the empty blocks are released?

Considering that we’re talking about an SSD, blocks marked for deletion are only released again as reusable after a trim operation.

You should have fstrim.timer running, which will run a full trim on all mounted filesystems once a week — usually on Sunday at midnight, but if the machine is not running at that point in time, it’ll be run upon the next boot.

@Gary1

Out of curiosity over the mentioned write speeds and wondered if the speed increases when a large part of the data is close together on the hard drive, such as when I wrote a large number of BLOBs in one script; and is slower when it has to collect data from many, many updates made over time to smaller rows of data.

This is irrelevant on an SSD, because there is no rotational latency and there are no moving heads.

@Gary1

I wonder what happens to database efficiency over time when the data is often updated, such as using a piece table for editing text files.

This depends on the chosen filesystem type. btrfs uses copy-on-write, but ext4 and xfs will attempt to rewrite the entire file, and if the file has grown beyond the space it used to occupy, then the kernel will attempt to store the whole file in one contiguous block if possible. If not possible, then the file will become fragmented.

@Gary1

I read somewhere that I cannot recall at the moment that Linux store data on disk differently than Windows such that it sort of “self-corrects” in that on HDD there is no need to de-fragment the disk.

That is correct. ntfs does not prioritize contiguous storage and will simply start writing to the first free block it finds. The Linux kernel always tries to keep the file contiguous if possible, regardless of the underlying filesystem. But again, btrfs uses copy-on-write, so the file may get internally fragmented, because only the modified blocks are written to the drive.