Potential issue during vacuum operations

[NyaaaWhatsUpDoc]

Attempting to run a vacuum; operation on a large database (~25GB) results in sqlite seemingly endlessly calling fallocate and making no progress on the vacuum. Even after leaving it running for 20 minutes the WAL file still sits at 0 size. I ran a profile during the operation for 300s which might help narrow things down a bit, and as you can see it's spending most of its time calling fallocate. I'm not sure if the operation ever does reach completion, as every duration i've let it run for leaves it sitting at seemingly no progress. Running the operation on a native binary usually completes in 5-10 mins and has nowhere near the memory impact.

I'm marking this as a potential issue as i'm not sure whether it is actually one, or if it's just a situation in which this webassembly transpile will particularly suffer when it comes to performance.

[NyaaaWhatsUpDoc]

profile

[ncruces]

It's unlikely the transpile is the issue, but the custom VFS is a likely culprit.

Some more information: OS, architecture? I'm assuming Linux (but I'm on mobile so I can't tell).

If modifying code is feasible, I'd comment out osAllocate on the applicable platform; either make it just return nil or make it look like this. Then test again.

[NyaaaWhatsUpDoc]

platform: Linux, amd64, using xfs for the filesystem

i tried replacing it with the os_std_alloc.go implementation and it was muuuuuch faster. i tried the vacuum again with the existing fallocate implementation and it took nearly 45 minutes, vs. the 10 minutes w/ truncate. i wonder if this slowdown with fallocate vs truncate is simply an xfs filesystem thing, or effects other filesystems too.

i also noticed that for the vacuum operation they both map they entire file into memory, which probably explains why this operation returns an i/o error on my server (again, Linux amd64 xfs) with 16GB memory since the file is 25GB.

[NyaaaWhatsUpDoc]

interestingly, using return nil actually throws

unexpected fault address 0x7f471085c000
fatal error: fault
[signal SIGBUS: bus error code=0x2 addr=0x7f471085c000 pc=0x484e3a]

hmm though actually that might be because it's also required in shmMap(), i'll try just comment-out its use for SizeHint(). UPDATE: yep that works correctly :p

[ncruces]

Oh thanks! I had meant to ask what the filesystem was.

What file do they map into memory? I'm pretty sure you don't have a 25GB WAL index… and that's the only file mmap should be used for.

I'll check how SQLite is using fallocate and how I'm doing it differently.

[ncruces]

I've been away from keyboard all week. Will look into this next week.

So, only “commenting out” SizeHint() is sufficient? That's very interesting.

[ncruces]

@NyaaaWhatsUpDoc, can you try this:

func osAllocate(file *os.File, size int64) error {
	if size == 0 {
		return nil
	}
	off, err := file.Seek(0, io.SeekEnd)
	if err != nil {
		return err
	}
	if size <= off {
		return nil
	}
	for {
		err := unix.Fallocate(int(file.Fd()), 0, off, size + off)
		if err == unix.EOPNOTSUPP {
			break
		}
		if err != unix.EINTR {
			return err
		}
	}
	return file.Truncate(size)
}

My guess is: for really large files (and maybe on specific file systems, like XFS) ensuring the file does not have holes becomes quadratic.

[NyaaaWhatsUpDoc]

What file do they map into memory? I'm pretty sure you don't have a 25GB WAL index… and that's the only file mmap should be used for.

i don't think i correctly described what i was seeing, i used the wrong wording :p. basically roughly the size of the database worth of memory ends up being reserved during the course of the operation. the database size is 23GB after a vacuum, and writes to the WAL only start once roughly 20-21GB of memory has been reserved. actual usage is much lower, but it still reserves a lot compared to the native binary. in comparison the native binary reserves maybe 1/20 of this.

@NyaaaWhatsUpDoc, can you try this:

this latest change speeds things up greatly.

i'm interested why fallocate seems to be so much slower compared to truncate, and why it's preferable compared to truncate here?

[ncruces]

fallocate tries to ensure the operation won't fail due to a lack of disk space by reserving enough pages on disk, instead of just changing the file size metadata, like ftruncate, which depending on the filesystem creates a "sparse" file. It's a way to improve the odds of the operation succeeding, or failing early.

[ncruces]

this latest change speeds things up greatly.

Can I fix it that way?

[NyaaaWhatsUpDoc]

this latest change speeds things up greatly.

Can I fix it that way?

it definitely seems to fix the issue! i'll time the operation for both and see if there's any big discrepencies between commented-out SizeHint() vs. doing it this way. UPDATE: the above recommended change works out roughly the same time as commenting out SizeHint() :)

[NyaaaWhatsUpDoc]

i actually wrote a super-hacky REPL that made testing this issue and just working debugging with this sqlite driver much easier :p https://codeberg.org/superseriousbusiness/gotosocial/src/branch/main/cmd/sqlite-repl/main.go