MySQL 8.0 Slow Réplication

MySQL & MariaDB Other MySQL® Questions
1

I have difficulty with my MySQL replication and I would like your help.

The MySLQ replication cannot to catch up the master and when the master start a huge workload the replication accumulates delay.

I add the screenshot of PMM:
PMM Dashboard:
MySQL InnoDB Details.pdf (3.6 MB)

MySQL InnoDB Details
MySQL InnoDB Details1920×15795 1.75 MB

Replication:
Replication.pdf (105.8 KB)

Innodb_row_operations
Innodb_row_operations1762×684 60.1 KB

1 Like
2

Hi Snake, there can be a lot of factors. For starters I suggest you take a look at this article: Can MySQL Parallel Replication Help My Slave? - Percona Database Performance Blog

1 Like
3

@Snake Please upload screenshots images and not PDFs

1 Like
4

I have updated the post.

1 Like
5

I had configured slave_parallel_workers on 16.

+-----------------------------+---------------+                                                                                                                                 | Variable_name               | Value         |
+-----------------------------+---------------+
| slave_parallel_type         | LOGICAL_CLOCK |
| slave_parallel_workers      | 16            |
| slave_preserve_commit_order | ON            |
+-----------------------------+---------------+

We can see that not all workers are used.

mysql> SELECT performance_schema.events_transactions_summary_by_thread_by_event_name.THREAD_ID AS THREAD_ID
    -> , performance_schema.events_transactions_summary_by_thread_by_event_name.COUNT_STAR AS COUNT_STAR
    -> FROM performance_schema.events_transactions_summary_by_thread_by_event_name
    -> WHERE performance_schema.events_transactions_summary_by_thread_by_event_name.THREAD_ID IN
    ->      (SELECT performance_schema.replication_applier_status_by_worker.THREAD_ID
    ->       FROM performance_schema.replication_applier_status_by_worker);
+-----------+------------+
| THREAD_ID | COUNT_STAR |
+-----------+------------+
|    174597 |    1791374 |
|    174598 |     199360 |
|    174599 |      45996 |
|    174600 |      15945 |
|    174601 |       4638 |
|    174602 |       1622 |
|    174603 |        625 |
|    174604 |        256 |
|    174605 |        126 |
|    174606 |         62 |
|    174607 |         23 |
|    174608 |          8 |
|    174609 |          5 |
|    174610 |          3 |
|    174611 |          2 |
|    174612 |          2 |
+-----------+------------+
1 Like
6

Your redo log is too small. PMM shows you this at the very top. I saw it within 3s of looking at the image.

You should not expect all replication threads to be working. LOGICAL_CLOCK works by using a transaction dependency tree. If txn B is dependent on txn A, then B and A cannot be executed by separate threads and must be executed in A, B order.

You can also reduce disk flushing by setting innodb_flush_log_at_trx_commit=2 and setting sync_binlog=0. Be aware of the risks these settings carry.

2 Likes
7

I increased the size of the redo log and the replication is going faster.
Thanks for your help.

1 Like