4 ms·

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?

by XCSme 8mo ago

ahofmann 8mo 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 8mo 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 8mo 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 8mo 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 8mo 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 8mo ago
  Fsync
  - XCSme 8mo ago
    Do you mean fsync can lead ro poor performance?
- XCSme 8mo ago
  I am doing many writes inserts/updates per second.
evanelias 8mo 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 8mo 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 8mo 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-...

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?

by XCSme 8mo 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 8mo 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 8mo 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 8mo 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 8mo 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 8mo 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 8mo ago
Fsync
- XCSme 8mo ago
  Do you mean fsync can lead ro poor performance?
XCSme 8mo ago
I am doing many writes inserts/updates per second.

evanelias 8mo 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 8mo 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 8mo 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-...