Recently, I upgraded an instance of MySQL Enterprise 5.1 to Percona Server 5.5 and am now having issues with getting the slave to stay in sync. In addition, the seconds behind master field from the show slave status statement is behaving oddly. It fluctuates from 0 to, for example, 20,000 seconds. Here is an example:
slave-server : 09:16:30 : ~
mysql -e “SHOW SLAVE STATUS\G” | grep Seconds_Behind_Master;sleep 5;mysql -e “SHOW SLAVE STATUS\G” | grep Seconds_Behind_Master
Seconds_Behind_Master: 0
Seconds_Behind_Master: 4577
The whole lag is a bit odd,because the slave kept up fine when it was on MySQL 5.1 Enterprise. If you shutdown the server down for a few minutes it would take some time to catch back up cause the hardware is different between the two servers, but not this bad. Thus, I am wondering if I am missing a setting or something. Current settings:
max-allowed-packet = 16M
max-connect-errors = 1000000
back_log = 4096
interactive_timeout = 600
wait_timeout = 600
tmp-table-size = 128M
max-heap-table-size = 128M
query-cache-type = 1
query-cache-size = 64M
max-connections = 100
thread-cache-size = 10
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096
key-buffer-size = 384M
myisam-recover-options = FORCE,BACKUP
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 1G
innodb_log_buffer_size = 16M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 100G
innodb_buffer_pool_instances = 5
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 400
read-only = 1
skip-slave-start = 1
relay-log-purge = 1
slave-net-timeout = 60
sync-master-info = 1
sync-relay-log = 1
sync-relay-log-info = 1
slave_compressed_protocol = 1
NOTE: The master server is still on MySQL Enterprise 5.1.