1

I was given two fairly new servers and setup master and slave running MySQL 5.7.17, Linux RedHat 6. Both servers are using binlog_format=STATEMENT.

I can't understand why my slave is processing so slowly though I know the bottleneck with the single thread process from my slave. I believe our master server does not contain a lot of parallel queries though.

Slave > show processlist;

Id: 22593 User: system user Host: db: Database1 Command: Connect Time: 2364340 State: updating Info: update TableA set colA..... This transaction is processing like 0.5s for every update. The total rows count for the table is just under 100,000 rows. I am not sure if my expectation is right that it should be fast. I turn on general_log to capture for several seconds and switch off back. Excerpt: 2016-12-28T10:26:53.937824Z 22093 Query COMMIT /* implicit, from Xid_log_event */ 2016-12-28T10:26:53.938293Z 22093 Query BEGIN 2016-12-28T10:26:54.404481Z 22093 Query update Table10 set COLA='XXX', COLB='XXXX', COLC='', COLD='', COLE='XXXX', COLF='XXXX' where COLB='YY' and COLF='ZZZZZZ' 2016-12-28T10:26:54.404576Z 22093 Query COMMIT /* implicit, from Xid_log_event */

slave > show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: masterA Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: masterA-bin.000117 Read_Master_Log_Pos: 818913948 Relay_Log_File: slaveB-relay-bin.001168 Relay_Log_Pos: 42738489 Relay_Master_Log_File: masterA-bin.000116 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 42738264 Relay_Log_Space: 1939474024 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: 60598 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 10 Master_UUID: XXXXXXXXXXXXXXXXXXXXXXXXXXXxx Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: updating 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 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

masterA> show master status\G; *************************** 1. row *************************** File: masterA-bin.000117 Position: 818913948 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

Based on the readings, my I/O thread is doing not too bad. Catching up with master log at masterA-bin.000117. But the SQL thread is lagging behind. masterA-bin.000116. Relay_Log_Pos: 42738489

SQL thread is replaying at slaveB-relay-bin.001168. But when I check my OS directory /log, I can see slaveB-relay-bin.001341 as the latest generated.

The slave is continuously lagging behind master. I have set

  1. sync_binlog=1
  2. Comment log-slave-updates
  3. innodb_flush_log_at_trx_commit=2

But it helps a little only, reducing probably 1-2 seconds of lag.

I am using STATEMENT binlog, will it matters if some tables does not have primary/unique key?

I'd to know if there are other means to speed up the replication without multithreaded slave?

2

Let's try to speed up the queries.

Table10 needs a composite INDEX(COLB, COLF) (in either order). If it already has that, then let's see some more of the 'slow' queries.

On the slave, turn on log_slow_slave_statements, lower long_quer_time to 0.5, turn on the slowlog, preferably to a FILE. Wait a day, then use mysqldumpslow -s t or pt-query-digest to summarize the slowlog. Then let's discuss the first few.

  • Turn on that the indexes on the table were missing. Recreated the index and speed are much more faster. – Haans Dec 28 '16 at 23:46
  • Recreating an index will not speed up things in MySQL. (Recreating may shrink the disk footprint some.) – Rick James Feb 13 '17 at 16:39

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.