# Upgraded replica to 8.0 resulted in replica lag **URL:** \ **Category:** Percona Distribution for MySQL\ **Created:** [September 21, 2023, 3:10pm UTC](https://forums.percona.com/t/upgraded-replica-to-8-0-resulted-in-replica-lag/25350 "2023-09-21T15:10:45Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Add](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/add/32/12867_2.png) [@Add](https://forums.percona.com/u/Add)\ **Post date:** [September 21, 2023, 3:10pm UTC](https://forums.percona.com/t/upgraded-replica-to-8-0-resulted-in-replica-lag/25350/1 "2023-09-21T15:10:45Z")
We have a S-M-M-S replication with 5.7 and we planned to migrate to 8.0. I’ve started with a replica that is used only as a fallback and backups (for obvious reasons) and the decrease in performance was appalling. It couldn’t catch up with the master and the lag kept increasing. Only when I set `sync-binlog = 0` it caught up and runs fairly ok. It ran fine with the sync set to 1 on 5.7 and I wouldn’t trade consistency for performance if it’s possible. Here’s the my.cnf ast it was before and after migration ```auto [mysqld] sql_mode=NO_ENGINE_SUBSTITUTION # MyISAM # key-buffer-size = 32M # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 skip-name-resolve # DATA STORAGE # datadir = /var/lib/mysql/ # BINARY LOGGING ########## modified 4 slave replication server-id = 4 binlog_do_db = dbname log-bin = /var/lib/mysql/mysql-bin #expire-logs-days = 14 deprecated in 8.0 binlog_expire_logs_seconds = 1360800 # 8.0 - that's 14 days in seconds sync-binlog = 0 # was 1 on 5.7 binlog_format = ROW #relay_log_info_repository=TABLE deprecated in 8.0 relay_log_recovery=ON # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M #query-cache-type = 0 deprecated in 8.0 #query-cache-size = 0 deprecated in 8.0 max-connections = 2000 thread-cache-size = 50 open-files-limit = 10000 #max 10k in 8.0 table-definition-cache = 1024 table-open-cache = 2048 # INNODB # innodb-flush-method = O_DIRECT #innodb-log-files-in-group = 2 deprecated in 8.0 #innodb-log-file-size = 100M deprecated in 8.0 innodb-file-per-table = 1 innodb-buffer-pool-size = 40G innodb_buffer_pool_instances = 3 innodb_flush_log_at_trx_commit = 2 innodb_io_capacity = 1000 # Parallel Replication # #slave_parallel_type=LOGICAL_CLOCK #slave_parallel_workers=20 #slave_preserve_commit_order=1 # LOGGING # log-error = /var/lib/mysql/mysql-error.log slow-query-log = 1 slow-query-log-file = /var/log/mysql/mysql-slow.log ``` Do you see something that’s obviously wrong so I can try changing that instead of sync-binlog? I’m rather reluctant to upgrade the masters in these circumstances. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [September 21, 2023, 8:52pm UTC](https://forums.percona.com/t/upgraded-replica-to-8-0-resulted-in-replica-lag/25350/2 "2023-09-21T20:52:48Z")
I see a couple things, but nothing night/day. innodb\_io\_capacity should really never be changed from default of 200. > **[InnoDB Flushing in Action for Percona Server for MySQL](https://www.percona.com/blog/innodb-flushing-in-action-for-percona-server-for-mysql/)** > > This post dives into the internal of the InnoDB flushing code, showing how InnoDB flushes dirty pages is key to a proper tuning. Why have you disabled parallel replication? Your master (now called “source”) is multi-threaded, so should your replicas. I would enable parallel replication workers at equal to 1/2 number of CPUs/cores. Examine your replicated queries. Are they INSERTs or UPDATEs? If UPDATEs, is there a WHERE clause with the primary key? How does the disk IO look on the 8.0 replica compared to 5.7 source? ---
**Author:** ![Add](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/add/32/12867_2.png) [@Add](https://forums.percona.com/u/Add)\ **Post date:** [September 22, 2023, 6:52am UTC](https://forums.percona.com/t/upgraded-replica-to-8-0-resulted-in-replica-lag/25350/3 "2023-09-22T06:52:13Z")
Hi Matthew, thank you for your response. Parallel replication was commented in the 5.7 setup. I tried it at the time, did not see any performance improvement, and I think i read that it wasn’t doing much in versions below 8.0, when the whole parallelism was improved. Uncommenting those was actually what helped plateau the lag and made it not increase continuously. So it’s enabled now. Changing innodb\_io\_capacity back to 200 hasn’t improved performance. I don’t know how relevant is looking at the IO comparing the replica and the primary, they are different machines, source is under heavy load, replica runs other services as well. I’ll try to go through all the settings again, see if I’m missing something. ---
**Author:** ![kedarpercona](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kedarpercona/32/809_2.png) [@kedarpercona](https://forums.percona.com/u/kedarpercona)\ **Post date:** [September 22, 2023, 7:02am UTC](https://forums.percona.com/t/upgraded-replica-to-8-0-resulted-in-replica-lag/25350/4 "2023-09-22T07:02:20Z")
Hi @Add … > I tried it at the time, did not see any performance improvement > Can you share the configuration options you used while setting it up? Parallel replication is working well for many of the customers in 5.7. For sure your primary and replica configuration difference is also very important parameter that may affect the lag. I usually choose [pt-config-diff](https://docs.percona.com/percona-toolkit/pt-config-diff.html) to verify the config differences. I’d suggest to evaluate the effectiveness of the config you should read through [this blog](https://www.percona.com/blog/estimating-potential-for-mysql-5-7-parallel-replication/) and see how is it performing before tweaking the configuration further. ```auto UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_transactions%' limit 10; UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'transaction' limit 10 ; ``` ```auto select performance_schema.events_transactions_summary_by_thread_by_event_name.THREAD_ID AS THREAD_ID, performance_schema.events_transactions_summary_by_thread_by_event_name.COUNT_STAR AS COUNT_STAR from performance_schema.events_transactions_summary_by_thread_by_event_name where performance_schema.events_transactions_summary_by_thread_by_event_name.THREAD_ID in (select performance_schema.replication_applier_status_by_worker.THREAD_ID from performance_schema.replication_applier_status_by_worker); ``` Following are the sample parameters that you might want to consider tweaking. ```auto SELECT @@slave_parallel_workers, @@slave_exec_mode, @@slave_parallel_type, @@slave_preserve_commit_order, @@slave_rows_search_algorithms,@@binlog_transaction_dependency_tracking\G ``` Thanks, K ---
**Author:** ![Add](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/add/32/12867_2.png) [@Add](https://forums.percona.com/u/Add)\ **Post date:** [September 22, 2023, 7:25am UTC](https://forums.percona.com/t/upgraded-replica-to-8-0-resulted-in-replica-lag/25350/5 "2023-09-22T07:25:27Z")
These are the settings on the Source (running fine on 5.7): ```auto [mysqld] sql_mode=NO_ENGINE_SUBSTITUTION explicit_defaults_for_timestamp=OFF # MyISAM # key-buffer-size = 32M # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 skip-name-resolve # DATA STORAGE # datadir = /var/lib/mysql/ # BINARY LOGGING ########## modified 4 master-master replication server-id = 11 binlog_do_db = dbname log-bin = /var/lib/mysql/mysql-bin log-slave-updates expire-logs-days = 7 sync-binlog = 1 binlog_format = ROW auto_increment_increment = 2 auto_increment_offset = 1 relay_log_info_repository=TABLE relay_log_recovery=ON # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 0 query-cache-size = 0 max-connections = 2000 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 1024 table-open-cache = 2048 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 100M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 50G innodb_buffer_pool_instances = 3 innodb_io_capacity = 1000 # LOGGING # log-error = /var/lib/mysql/mysql-error.log slow-query-log = 1 slow-query-log-file = /var/log/mysql/mysql-slow.log long_query_time = 4 ``` What baffles me is the difference in performance from sync-binlog=1 to sync-binlog=0. From the graphs of the queries, it’s severals orders of magnitude faster: ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/1/19566fae1076b5b98944fb6bd2fb41a19cba2162.png) I understand why there would be a difference, but I don’t understand why it’s so massive. ---
**Author:** ![Add](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/add/32/12867_2.png) [@Add](https://forums.percona.com/u/Add)\ **Post date:** [September 25, 2023, 8:21am UTC](https://forums.percona.com/t/upgraded-replica-to-8-0-resulted-in-replica-lag/25350/6 "2023-09-25T08:21:38Z")
![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/0/0dd0c4eefdc61973c37e85ef05356f85718c6660.png) I’ve been keeping an eye on this server and noticed that Writing on the disk seems to be the bottleneck. Every time the Replica lags behind, it’s because disks are busy. They are some consumer grade SSDs in a raid 10 array. Nothing changed from before upgrade, so I imagine there’s a change in the way writing or caching or flushing is done in the new version.