Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

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?
 help



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.


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?


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

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.

I am doing many writes inserts/updates per second.

Fsync

Do you mean fsync can lead ro poor performance?

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.

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.

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.

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.



Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: