0

I have 2 servers with master-master replication using Percona MySQL 5.6 and I am having replication lag in the second server: db-02, however db-01 never had a lag situation.

What could be causing this lag? It rises sometimes up to >1000 secs of lag and sunddenly it fells to 0 secs. I have checked it with Percona's pt-heartbeat tool and is real lag.

This are the server configurations, both have 8 cores, 32GB RAM and SSD disks:

DB-01 (Xeon E3-1245 V2 @ 3.40GHz)

[mysqld]
server-id              = 1
log_bin                = /var/lib/mysql/mysql-bin.log
binlog-ignore-db       = mysql
binlog-ignore-db       = test
datadir                = /var/lib/mysql

expire-logs-days = 10

auto_increment_increment= 2
auto_increment_offset   = 1

max_connections=3000

query_cache_size=0
query_cache_type=0

innodb_file_per_table
innodb_file_format=barracuda
innodb_flush_method=O_DIRECT
innodb_log_file_size=128M
innodb_thread_concurrency=0

innodb_flush_log_at_trx_commit=1
sync_binlog=1

# ~90% of memory
innodb_buffer_pool_size=27000M

# 4 * # of CPU cores
innodb_read_io_threads=32
innodb_write_io_threads=32

innodb_io_capacity = 5000

DB-02 (i7-4770 CPU @ 3.40GHz)

// same that db-01 but with this options different:
server-id               = 2
auto_increment_offset   = 2

The output of SHOW SLAVE STATUS on each server:

DB-01

               Slave_IO_State: Waiting for master to send event
                  Master_Host: db-02
              Master_Log_File: mysql-bin.000022
          Read_Master_Log_Pos: 3348962
               Relay_Log_File: db-01-relay-bin.000042
                Relay_Log_Pos: 3349125
        Relay_Master_Log_File: mysql-bin.000022
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3348962
              Relay_Log_Space: 3349462
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
        Seconds_Behind_Master: 0
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:

DB-02

               Slave_IO_State: Waiting for master to send event
                  Master_Host: db-01
              Master_Log_File: mysql-bin.000019
          Read_Master_Log_Pos: 38905060
               Relay_Log_File: db-02-relay-bin.000048
                Relay_Log_Pos: 17834447
        Relay_Master_Log_File: mysql-bin.000019
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 28196753
              Relay_Log_Space: 28542928
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
        Seconds_Behind_Master: 310
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
  • Please post all the options in my.cnf for DB-02 – RolandoMySQLDBA Mar 23 '15 at 20:15
  • Are all your writes to DB-01 ? – RolandoMySQLDBA Mar 23 '15 at 21:34
  • No, the writes are balanced to both servers. The my.cnf of DB-02 is exactly the same as DB-01, the only differences are obviously the server-id and auto_increment_offset – blacksoul Mar 24 '15 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.