Hacker News Reader

Comment by ausjke | Hacker News Reader

I'm having a hard time to pick between mysql8 and postgresql10.

MySQL is under Oracle is one of the major concern, plus it's said to be good for less-write-more-read workload, which is fine.

PostgreSQL is more on the open source side, however bugs like this really concerns me: https://lwn.net/Articles/752063/ -- basically, PostgreSQL does not do its own DIO instead it depends on file system and it can lead to data corruption, and there is no quick fix...

tried to install mysql8.0 on ubuntu16.04 failed so far, need a vm to test it out sometime.

View on HN

From MySQL's documentation:

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.ht...

> Caution: Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. In this case, the durability of transactions is not guaranteed even with the setting 1, and in the worst case, a power outage can corrupt InnoDB data. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try to disable the caching of disk writes in hardware caches.

Pardon my ignorance, but do you know if this would affect PG as well, since it's an OS/hardware sourced issue?

It's same issue grandparent mentioned.

great, thanks for the finding.

MySQL does not do its own IO either. I'm not sure what the complaint here is. Almost every database system is affected by that bug.

...doesn't this bug mean that affected database systems do not have working transactions?

No.

It means that IF and only if, your storage is dying, you might learn about it later than you wish. In cases your storage is dying you are likely also going to observe problems that are outside of an fsync() not reporting an error when you'd like it to. As long as your storage hardware isn't broken, fsync still guarantees durability. Obviously it'd be better to get the error out ASAP.

Yes

Unless you need partitioned tables or have significant operational experience with MySQL and not with PostgreSQL, you should pick PostgreSQL.

With O_DIRECT you won't hit that bug. MySQL/InnoDB allows you to do that (not the default). However I think you're not very likely to hit this bug. This is why you have backups and replication.

I agree, this is in some ways a storm in a teacup and unlikely to cause silent data loss (it requires transient failure, but in reality failure storage probably fails completely and you definitely know about that). But as a matter of principle, it's bad that an operating system is so callous with my data.

On the plus side, I think this is great PR for FreeBSD (any filesystem) and ZFS (any OS).

One thing I'm curious about... MySQL still calls fsync() when you use O_DIRECT. See the warnings in the documentation about why O_DIRECT_NO_FSYNC is dangerous (to avoid loss of eg XFS meta-data, otherwise it might forget that a file was truncated or extended). I wonder if in some vanishingly rare failure mode you could lose data that way even with O_DIRECT.

I did not investigate MySQL code (or other product) but I would be very surprised if PostgreSQL is the only one impacted by this issue.

Yes that seems unlikely. Almost all Unix software uses buffered IO, including several other databases or database-like things including package managers, DNS servers, mail servers, ..., and MySQL does too by default. It even uses it when you choose O_DIRECT (though I'm not sure if that's exposed to write-back error eating risk or not).

That said, as several people have said, this is a very unlikely failure mode. When filesystems die, you know about it. The type of transient failure required to lose data this way is quite unlikely.

by reading the lwn article it seems postgresql is hit especially hard per its design and "heavy" use of fsync, I have yet to find similar impact on mysql.

All SQL databases are "heavy" users of fsync. Though, this problem is not relevant on FreeBSD nor Illumos.

Error handling for a operating system is really complex stuff. I'm surprised that this isn't working properly, fsync is not about speed, but reliability. Userland applications expect it to work and behave as intended.

the key is error reporting

For what it's worth, I was in this same situation.

Without getting overly technical, MySQL's ease of use, wide supported drivers and toolsets, along with it's ubiquitous nature made me choose MySQL.

Also, almost everyone knows MySQL. Easy to find talent.

(Avoiding the technical reasons for brevity).

Not just that, but I have yet to come across a piece of software that is both free and comparable to MySQL Workbench. It makes designing the database ridiculously easy.

Absolutely - I spent way too long finding postgres tool, and even then, it was just okay.

Also, so many big players use proven MySQL setups (Uber, Github, etc), that I feel like the scalability is there if needed.

true, mysql workbench is great

I gave up on Mysql long time ago when I found out that it cannot refer to temporary tables multiple times in the same query. Not sure if it has been fixed now...

EDIT: Does not seem fixed https://dev.mysql.com/doc/refman/8.0/en/temporary-table-prob...

Reply on news.ycombinator.com