8 ms·

MySQL foreign key cascade operations finally hit the binary log

35 points by marceloaltmann 7mo ago

XCSme 7mo ago
I always end up disabling bin log for single-db setups, and simlly run backup jobs. Using bin log drastically reduces performance. Am I crazy?
- ahofmann 7mo ago
  I've used bin log for almost decades and never experienced a big performance impact. This even holds for write heavy MySQL instances in ancient times where servers had spinning disks.
  - hobs 7mo ago
    Nonetheless its not a WAL, and if you are not replicating it idk why you'd have it enabled, and if you are replicating you must.
  - XCSme 7mo ago
    Thinking back about it, I think the biggest issue was the size, not performance. For a write-intensive app, the bin long quickly got to tens of GBs and filled the entire disk, which was a problem when running the app on smaller VPSs.
    - evanelias 7mo ago
      You can tune binlog_expire_logs_seconds to control how long old binlog files stay around. The default is 2592000 seconds (30 days) which is often too long.
- toast0 7mo ago
  Turning it off cause you're not using it seems reasonable, but I'm surprised it has a big effect on performance. Sequential appends to a file are pretty easy as long as you're not doing so many writes per second that there's contention on the write.
  - xhcuvuvyc 7mo ago
    Fsync
    - XCSme 7mo ago
      Do you mean fsync can lead ro poor performance?
  - XCSme 7mo ago
    I am doing many writes inserts/updates per second.
- evanelias 7mo ago
  The performance impact depends substantially on whether you've configured it to fsync the binlog on every group commit. Also, it's important to consider that replication and backups serve different purposes. Backups alone are insufficient for high availability, change data capture, point-in-time recovery / undoing a bad change, etc.
  - XCSme 7mo ago
    In my case it's for analytics, so I am ok with some data loss in case of a failure. How do I set the fsync stuff? Does if have to be turned off?
    - evanelias 7mo ago
      The sync_binlog server variable controls this behavior. The default of 1 means to fsync every time, which is best for durability but worst for performance. See https://dev.mysql.com/doc/refman/8.4/en/replication-options-binary-log.html#sysvar_sync_binlog https://dev.mysql.com/doc/refman/8.4/en/replication-options-...
TexanFeller 7mo ago
One of the main reasons I use Postgres is I've rarely(never?) seen an article like this posted about it. Every time I've touched MySQL I've found a new footgun.
- ahofmann 7mo ago
  MySQL is the PHP of databases. It was free, easy to setup and had the spotlight at the right time. The bad decisions that are baked into MySQL are plenty and really sad (like the botched utf8 defaults, the myisam storage engine, strange stuff around replication and mucu more)
  - booi 7mo ago
    It still blows my mind that they called that crappy partial buggy characterset “utf8”. Then later came out with actual utf8 and called it “utf8mb4”. Makes no sense
    - evanelias 7mo ago
      They should have addressed it much earlier, but it makes way more sense in historical context: when MySQL added utf8 support in early 2003, the utf8 standard originally permitted up to 6 bytes per char at that time. This had excessive storage implications, and emoji weren't in widespread use at all at the time. 3 bytes were sufficient to store the majority of chars in use at that time, so that's what they went with. And once they made that choice, there was no easy fix that was also backwards-compatible. MySQL avoids breaking binary data compatibility across upgrades: aside from a few special cases like fractional time support, an upgrade doesn't require rebuilding any of your tables.
      - nofriend 7mo ago
        Your explanation makes it sound like an incredibly stupid decision. I imagine what you're getting at is that 3 bytes were/are sufficient for the basic multilingual plane, which is incidentally also what can be represented in a single utf-16 byte pair. So they imposed the same limitation as utf-16 had on utf-8. This would have seemed logical in a world where utf-16 was the default and utf-8 was some annoying exception they had to get out of the way.
jbonatakis 7mo ago
This is excellent. In the past when replicating via Debezium from a system making heavy use of cascade deletes I’ve had to write a layer that infers these deletes by introspecting the database schema, building a graph of all cascades (sometimes several layers) and identifying rows that should have corresponding delete records. These can then be excluded in whatever downstream system via an anti-join. It works but it will be better to not have to do that and instead have first class support for cascades.
garaetjjte 7mo ago
>Authors: Marcelo Altmann Is there any LLM with that name?
- deleted 7mo ago
  [deleted]

XCSme 7mo ago

I always end up disabling bin log for single-db setups, and simlly run backup jobs. Using bin log drastically reduces performance. Am I crazy?

ahofmann 7mo ago
I've used bin log for almost decades and never experienced a big performance impact. This even holds for write heavy MySQL instances in ancient times where servers had spinning disks.
- hobs 7mo ago
  Nonetheless its not a WAL, and if you are not replicating it idk why you'd have it enabled, and if you are replicating you must.
- XCSme 7mo ago
  Thinking back about it, I think the biggest issue was the size, not performance. For a write-intensive app, the bin long quickly got to tens of GBs and filled the entire disk, which was a problem when running the app on smaller VPSs.
  - evanelias 7mo ago
    You can tune binlog_expire_logs_seconds to control how long old binlog files stay around. The default is 2592000 seconds (30 days) which is often too long.
toast0 7mo ago
Turning it off cause you're not using it seems reasonable, but I'm surprised it has a big effect on performance. Sequential appends to a file are pretty easy as long as you're not doing so many writes per second that there's contention on the write.
- xhcuvuvyc 7mo ago
  Fsync
  - XCSme 7mo ago
    Do you mean fsync can lead ro poor performance?
- XCSme 7mo ago
  I am doing many writes inserts/updates per second.
evanelias 7mo ago
The performance impact depends substantially on whether you've configured it to fsync the binlog on every group commit. Also, it's important to consider that replication and backups serve different purposes. Backups alone are insufficient for high availability, change data capture, point-in-time recovery / undoing a bad change, etc.
- XCSme 7mo ago
  In my case it's for analytics, so I am ok with some data loss in case of a failure. How do I set the fsync stuff? Does if have to be turned off?
  - evanelias 7mo ago
    The sync_binlog server variable controls this behavior. The default of 1 means to fsync every time, which is best for durability but worst for performance. See https://dev.mysql.com/doc/refman/8.4/en/replication-options-binary-log.html#sysvar_sync_binlog https://dev.mysql.com/doc/refman/8.4/en/replication-options-...

TexanFeller 7mo ago

One of the main reasons I use Postgres is I've rarely(never?) seen an article like this posted about it. Every time I've touched MySQL I've found a new footgun.

ahofmann 7mo ago
MySQL is the PHP of databases. It was free, easy to setup and had the spotlight at the right time. The bad decisions that are baked into MySQL are plenty and really sad (like the botched utf8 defaults, the myisam storage engine, strange stuff around replication and mucu more)
- booi 7mo ago
  It still blows my mind that they called that crappy partial buggy characterset “utf8”. Then later came out with actual utf8 and called it “utf8mb4”. Makes no sense
  - evanelias 7mo ago
    They should have addressed it much earlier, but it makes way more sense in historical context: when MySQL added utf8 support in early 2003, the utf8 standard originally permitted up to 6 bytes per char at that time. This had excessive storage implications, and emoji weren't in widespread use at all at the time. 3 bytes were sufficient to store the majority of chars in use at that time, so that's what they went with. And once they made that choice, there was no easy fix that was also backwards-compatible. MySQL avoids breaking binary data compatibility across upgrades: aside from a few special cases like fractional time support, an upgrade doesn't require rebuilding any of your tables.
    - nofriend 7mo ago
      Your explanation makes it sound like an incredibly stupid decision. I imagine what you're getting at is that 3 bytes were/are sufficient for the basic multilingual plane, which is incidentally also what can be represented in a single utf-16 byte pair. So they imposed the same limitation as utf-16 had on utf-8. This would have seemed logical in a world where utf-16 was the default and utf-8 was some annoying exception they had to get out of the way.

jbonatakis 7mo ago

This is excellent. In the past when replicating via Debezium from a system making heavy use of cascade deletes I’ve had to write a layer that infers these deletes by introspecting the database schema, building a graph of all cascades (sometimes several layers) and identifying rows that should have corresponding delete records. These can then be excluded in whatever downstream system via an anti-join. It works but it will be better to not have to do that and instead have first class support for cascades.

garaetjjte 7mo ago

>Authors: Marcelo Altmann Is there any LLM with that name?

deleted 7mo ago
[deleted]