We have set up a new slave server for our production database. Since than the Seconds_Behind_Master keep growing slowly.
This is the slave output:
Slave_IO_State: Waiting for master to send event Master_Host: notimportant Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.003790 Read_Master_Log_Pos: 42585179 Relay_Log_File: mysqld-relay-bin.002798 Relay_Log_Pos: 32374374 Relay_Master_Log_File: mysql-bin.003492 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 32374215 Relay_Log_Space: 31350956440 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 1448477 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Master_Server_Id: 6524 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Reading event from the relay log Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0
Show processlist on SLAVE:
| 1 | system user | | NULL | Connect | 2143813 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 1448477 | Reading event from the relay log | NULL |
| 1628 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST |
On MASTER side:
| 7947837 | repl | host-vm:59420 | NULL | Binlog Dump | 2143817 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
On MASTER the show slave hosts:
+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
| 3410 | | 3410 | 6524 |
| 347643210 | | 3306 | 6524 |
+-----------+------+------+-----------+
Does anyone has an idea what's going on and how can I fix it?
Best Answer
The solution was to add the following to
my.cnf:Our server was slow in I/O and the row above reduced the disk usage.
It is not recommended for bank databases, but recommended to all others.
Phrased differently:
=1flushes at then end of each transaction, thereby making it ACID-compliant.=2flushes to disk every second, thereby being much less I/O-intensive.