0

I have one master and two slaves (will become one slave when this is resolved) The master is on a dedicated server that is very fast. The Old slave server that is in sync is on a busy shared server The new slave is on the same spec dedicated tin as the master The base config is the same as far as i can tell However , the new slave just gets further and further behind the master - even after days of leaving it alone Both slaves are on the same site and the link between the master and slaves is good (incidentaly we have also tested tge new slave on the same server as the master with the same slow results)

I cant work out why the 2nd slave will not get up to speed - it looks like the realy logs are not read into the slave db at a speed that keeps up As this is a busy and active db (but not that large in real terms) there are lots of erros on the slave logs as it is trying to replay something that is out of sync - i.e.

[Warning] Slave SQL: Could not execute Update_rows_v1 event on table xxxx.cdr_record_yyyy; Can't find record in 'cdr_record_yyyy', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mariadb-bin.005031, end_log_pos 781678783, Gtid 0-3-50623515684, Internal MariaDB error code: 1032

As the CDR files are recycled every 2 days on the master , i figured that it would eventually sort itself out (as it did when we moved from a previous master to the new faster master and also repointed the old slave to the new master

I am completely at a loss as to what i can do to get this in sync

Stopping the master to do a full export/import into the slave is not an easy option as outage time is not really tolerated

MariaDB [(none)]> show slave status \G ; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.xxx.xxx.xxx Master_User: repl Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mariadb-bin.005101 Read_Master_Log_Pos: 303326735 Relay_Log_File: XXXXX-DB-XX-relay-bin.000043 Relay_Log_Pos: 597352213 Relay_Master_Log_File: mariadb-bin.005030 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Rewrite_DB: 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: 597351912 Relay_Log_Space: 76574950877 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: 88241 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: 3 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Current_Pos Gtid_IO_Pos: 0-3-50831907322 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: After apply log event Slave_DDL_Groups: 4 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 689092428 1 row in set (0.000 sec)

ERROR: No query specified

MariaDB [(none)]>

1 Answer 1

0

It is hard to explain what is happening without looking more in-depth into the performance of your system. In general, even if you have the same specs on both nodes, you need to keep in mind that replication, by default, is single-threaded. If your source node executes hundreds or thousands of writes per second across tens or hundreds of threads, the replica will always attempt to replay all those writes using single SQL thread.

There are couple of solutions that you can try:

  1. Review your queries. Are you sure you should write that much? Are there writes that maybe should not be sent to the MariaDB? Let’s say, data about open sessions. Tokens, session-level keys, “last activity” data refreshed after every click on the website. Maybe you don’t really need to keep all of it in a relational database? Maybe some sort of lightweight in-memory datastore like Redis is a better option?

  2. Reduce the I/O load for each write, allowing more writes to be made using the single thread

  • innodb_flush_log_at_trx_commit - you can set it to 2 or even 0. This comes with a price of durability and ability to survive crashes but if you are ok with rebuilding replica in an unlikely event of its crash, this might speed up the replication

  • Disabling binary logs - if you never expect your replica to be promoted, you can disable binary logs on it - this is always a couple of I/O operations that you can skip per each write

  • Remove unneeded indexes - every write you make to the table requires all indexes in that table to be updated. If you have a bad habit of indexing literally everything, not only those combinations of columns that make sense for your query mix, you probably can remove some of them, which will help speed up the writes. As a special case, you should look into duplicated indexes. Sometimes you have indexes on (colA) and (colA, colB) - both of them will work for:

    SELECT something FROM table WHERE colA = some_value;
    

    Most of the time you can remove index on (colA) and go with a composite index on (colA, colB) only.

  1. Enable parallel replication

    You can increase the value of slave_parallel_threads. This will allow MariaDB to use multiple threads to apply replicated writes. Keep in mind this is not a silver bullet - it should help but replication will never be as efficient as writing to the source node.

  2. Shard your data - this is an ultimate solution and an overkill in 90% of the cases (probably even more than 90%) but if you ran out of options and tried everything to get the replication to keep up with the writes on the source node, you have no other options than to split your data one way or the other (sharding is a complex topic) and, through that, split the amount of writes across multiple source - replica sets. Ideally, more evenly you can split the writes, the better.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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