4 ms·

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-mor

• Manozco 9y ago
  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.
  • ausjke 9y ago
    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.
    • olavgg 9y ago
      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.
      • ausjke 9y ago
        the key is error reporting
  • macdice 9y ago
    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.
• tathougies 9y ago
  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.
  • thaumasiotes 9y ago
    ...doesn't this bug mean that affected database systems do not have working transactions?
    • tathougies 9y ago
      Yes
    • anarazel 9y ago
      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.
• neuronexmachina 9y ago
  From MySQL's documentation: https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html 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.
  • ausjke 9y ago
    great, thanks for the finding.
  • stronglikedan 9y ago
    Pardon my ignorance, but do you know if this would affect PG as well, since it's an OS/hardware sourced issue?
    • ddorian43 9y ago
      It's same issue grandparent mentioned.
• madhadron 9y ago
  Unless you need partitioned tables or have significant operational experience with MySQL and not with PostgreSQL, you should pick PostgreSQL.
• dveeden2 9y ago
  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.
  • macdice 9y ago
    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.
• softwarefounder 9y ago
  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).
  • zo1 9y ago
    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.
    • softwarefounder 9y ago
      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.
    • ausjke 9y ago
      true, mysql workbench is great
• deleted 9y ago
  [deleted]
• buvanshak 9y ago
  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-problems.html https://dev.mysql.com/doc/refman/8.0/en/temporary-table-prob...