MySQL Forums
Forum List  »  Replication

5.6 to 5.7 row-based replication performance issue
Posted by: JOSEPH MASK
Date: October 27, 2020 05:22PM

We are upgrading from 5.6 (Percona Server 5.6.49-89.0-log) to 5.7 (Percona Server 5.7.31-34-log), and we are seeing a significant (2x to 10x) degradation in performance of row-based replication on the 5.7 replica.

The master is still on 5.6 and is configured for row-based replication, the replica is also on 5.6, and we configured an additional replica on 5.7 (cnf files for 5.6 and 5.7 below).

The 5.6 replica has no problem keeping up with the master, and if it is manually stopped for some period of time and then restarted, it catches up very quickly -- usually at least 3x elapsed time. But the 5.7 replica can't keep up with the master and is lagging farther and farther behind. It performs between 2x and 10x worse than the 5.6 replica. The 5.7 replica was created from a disk snapshot of the 5.6 replica, so we know the data was identical prior to the upgrade. These are the upgrade steps we did on the 5.7 replica after the snapshot:
- remove innodb_log_block_size=4096 from cnf file
- start mysqld 5.6
- stop mysqld 5.6
- upgrade mysqld from 5.6 to 5.7 (including cnf file)
- remove innodb_log_write_ahead_size=4096 from cnf file
- start mysqld 5.7
- run mysql_upgrade -S /var/run/mysqld/app.sock -s
- add innodb_log_write_ahead_size=4096 back to cnf file
- restart mysqld 5.7

I've read over a number of articles on upgrading from 5.6 to 5.7 and on troubleshooting replication lag, but I haven't found anything pointing to a solution to our performance problem with row-based replication when upgrading from 5.6 to 5.7.

We have quite a few settings in our cnf files, but we are trying to keep them as identical as possible through the upgrade. One notable difference in the cnf files is that 5.6 was using mysqld_safe, but 5.7 uses systemd.

We appreciate any advice you can provide to help us through this.

5.6 cnf file:
[mysqld]
auto_increment_increment=2
auto_increment_offset=1
character_set_server=utf8
collation_server=utf8_unicode_ci
connect_timeout=30
datadir=/var/app.dbserver
default-storage-engine=innodb
innodb_stats_on_metadata=0
innodb_old_blocks_time=1000
innodb_file_per_table=1
innodb_io_capacity=2000
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
innodb_file_format=barracuda
innodb_file_format_max=barracuda
innodb_data_file_path=ibdata1:32M:autoextend
innodb_log_buffer_size=8M
innodb_purge_threads=1
innodb_buffer_pool_size=60G
innodb_log_file_size=1G
key_buffer_size=
log-slave-updates
slow-query-log=1
slow-query-log-file=mysqlSlow.log
log_slow_verbosity=full
log_bin=/var/mysql_app_logs/masterLog
long_query_time=20
max_binlog_files=300
max_allowed_packet=32MB
max_connect_errors=9000000
max_connections=2048
max_heap_table_size=128M
max_tmp_tables=128
myisam_sort_buffer_size=32M
query_cache_size=64M
plugin_dir=/var/mysql-plugins
port=3306
read_buffer_size=4M
relay-log=/var/mysql_app_logs/relay
replicate-wild-ignore-table=%.tmpi\_%
replicate-wild-ignore-table=%.tmpNoRepl\_%
replicate-wild-ignore-table=%.TMPNOREPL\_%
report-host=db-replica4-019
server-id=176160857
skip-name-resolve
skip-slave-start
slave_skip_errors=1062,1032
slave_compressed_protocol=1
socket=/var/run/mysqld/app.sock
sort_buffer_size=8M
table_open_cache=80000
tmpdir=/tmp
tmp_table_size=128M
wait_timeout=1800
user=mysql
basedir=/usr
table_definition_cache=20000
lock_wait_timeout=600
performance_schema=0
core-file
group_concat_max_len=16777215
secure_file_priv=
sql_mode=NO_ENGINE_SUBSTITUTION
innodb_log_block_size=4096
log-warnings-suppress=1592
thread_cache=256
innodb_buffer_pool_populate=1
innodb_buffer_pool_instances=4
binlog_format=ROW
binlog_rows_query_log_events=1

[mysqld_safe]
flush_caches
numa_interleave
log-error=/var/log/mysql/app.log
pid-file=/var/app.dbserver/db-replica4-019.pid
open-files-limit=450000
core-file-size=unlimited

5.7 cnf file:
[mysqld]
auto_increment_increment=2
auto_increment_offset=1
character_set_server=utf8
collation_server=utf8_unicode_ci
connect_timeout=30
datadir=/var/app.dbserver
default-storage-engine=innodb
innodb_stats_on_metadata=0
innodb_old_blocks_time=1000
innodb_file_per_table=1
innodb_io_capacity=2000
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
innodb_file_format=barracuda
innodb_file_format_max=barracuda
innodb_data_file_path=ibdata1:32M:autoextend
innodb_log_buffer_size=8M
innodb_purge_threads=1
innodb_buffer_pool_size=60G
innodb_log_file_size=1G
key_buffer_size=
log-slave-updates
slow-query-log=1
slow-query-log-file=mysqlSlow.log
log_slow_verbosity=full
log_bin=/var/mysql_app_logs/masterLog
long_query_time=20
max_binlog_files=300
max_allowed_packet=32MB
max_connect_errors=9000000
max_connections=2048
max_heap_table_size=128M
max_tmp_tables=128
myisam_sort_buffer_size=32M
query_cache_size=64M
plugin_dir=/var/mysql-plugins
port=3306
read_buffer_size=4M
relay-log=/var/mysql_app_logs/relay
replicate-wild-ignore-table=%.tmpi\_%
replicate-wild-ignore-table=%.tmpNoRepl\_%
replicate-wild-ignore-table=%.TMPNOREPL\_%
report-host=db-replica4-019
server-id=176160857
skip-name-resolve
skip-slave-start
slave_skip_errors=1062,1032
slave_compressed_protocol=1
socket=/var/run/mysqld/app.sock
sort_buffer_size=8M
table_open_cache=80000
tmpdir=/tmp
tmp_table_size=128M
wait_timeout=1800
user=mysql
basedir=/usr
table_definition_cache=20000
lock_wait_timeout=600
performance_schema=0
core-file
group_concat_max_len=16777215
secure_file_priv=
sql_mode=NO_ENGINE_SUBSTITUTION
innodb_log_write_ahead_size=4096
innodb_buffer_pool_instances=4
log_statements_unsafe_for_binlog=OFF
innodb_numa_interleave=ON
binlog_format=ROW
binlog_rows_query_log_events=1

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.