1

I am working on migration from mysql 5.7 to mysql 8 (AWS Aurora). As part of the migration I set up AWS DMS tasks and I came to notice each task takes more and more CPU from the writer.

The underline issue is that each DMS task runs every few seconds SHOW BINARY LOGS command to get the list of binlogs to read from, and in MySQL 8 that command runs in seconds instead of ms. We have a retention of 144 hours on them coz of replication and DMS potential lag when there is huge processing.

How can I speed this command?

MySQL [(none)]> show binary logs;
+----------------------------+-----------+-----------+
| Log_name                   | File_size | Encrypted |
+----------------------------+-----------+-----------+
| mysql-bin-changelog.414470 | 134230455 | No        |
...
+----------------------------+-----------+-----------+
945 rows in set (12.35 sec)

MySQL [(none)]> show variables like '%bin%';
+------------------------------------------------+-------------------------------------------------+
| Variable_name                                  | Value                                           |
+------------------------------------------------+-------------------------------------------------+
| aurora_binlog_reserved_event_bytes             | 1024                                            |
| aurora_enable_repl_bin_log_filtering           | ON                                              |
| bind_address                                   | *                                               |
| binlog_cache_size                              | 32768                                           |
| binlog_checksum                                | NONE                                            |
| binlog_direct_non_transactional_updates        | OFF                                             |
| binlog_encryption                              | OFF                                             |
| binlog_error_action                            | ABORT_SERVER                                    |
| binlog_expire_logs_seconds                     | 0                                               |
| binlog_format                                  | ROW                                             |
| binlog_group_commit_sync_delay                 | 0                                               |
| binlog_group_commit_sync_no_delay_count        | 0                                               |
| binlog_gtid_simple_recovery                    | ON                                              |
| binlog_max_flush_queue_time                    | 0                                               |
| binlog_order_commits                           | ON                                              |
| binlog_rotate_encryption_master_key_at_startup | OFF                                             |
| binlog_row_event_max_size                      | 8192                                            |
| binlog_row_image                               | FULL                                            |
| binlog_row_metadata                            | MINIMAL                                         |
| binlog_row_value_options                       |                                                 |
| binlog_rows_query_log_events                   | OFF                                             |
| binlog_stmt_cache_size                         | 32768                                           |
| binlog_transaction_compression                 | OFF                                             |
| binlog_transaction_compression_level_zstd      | 3                                               |
| binlog_transaction_dependency_history_size     | 25000                                           |
| binlog_transaction_dependency_tracking         | COMMIT_ORDER                                    |
| innodb_api_enable_binlog                       | OFF                                             |
| log_bin                                        | ON                                              |
| log_bin_basename                               | /rdsdbdata/log/binlog/mysql-bin-changelog       |
| log_bin_index                                  | /rdsdbdata/log/binlog/mysql-bin-changelog.index |
| log_bin_trust_function_creators                | OFF                                             |
| log_bin_use_v1_row_events                      | OFF                                             |
| log_statements_unsafe_for_binlog               | ON                                              |
| max_binlog_cache_size                          | 18446744073709547520                            |
| max_binlog_size                                | 134217728                                       |
| max_binlog_stmt_cache_size                     | 18446744073709547520                            |
| sql_log_bin                                    | ON                                              |
| sync_binlog                                    | 1                                               |
+------------------------------------------------+-------------------------------------------------+

UPDATE

I did a test on MySQL-RDS 8 - the performance were much better - I got SHOW BINARY LOGS results of 150 files in 0.3s. So it seems as Aurora issue.

At the moment the only workaround I can think of is to set a replication of from the prod and set DMS on it to isolate potential prod impact. This come with a cost obviously, need to manage and pay for another instance.

4
  • What "DMS task" needs to look in the binlogs? And why so often?
    – Rick James
    Sep 5, 2022 at 1:26
  • It is migration tool for full loads and CDC between DBs. Like Golden Gate and others.
    – Nir
    Sep 5, 2022 at 6:32
  • Hmmm... Seems like it would mostly be streaming data from the latest, or next-to-latest binlog. And being a very light load because it should not need to read anything twice. And the blocks of the file should "always" be cached in RAM.
    – Rick James
    Sep 5, 2022 at 17:10
  • I agree. But this is a black box. Also show binary logs slowness is Aurora 3 thing, not even mysql 8.
    – Nir
    Sep 6, 2022 at 9:06

2 Answers 2

1

Aurora gives you limited control over configuration and operations.

If you can't change the max_binlog_size in your parameter group, then I think your options are limited to:

  • Shorten binlog_expire_logs_seconds so you expire binlogs sooner, and therefore keep fewer binlogs on average. I know you said you need 144 hours retention, but you have to make a choice between that versus the performance problem you have.

    But even if you do decrease the retention period, you might still have a problem if your traffic is spiky. That is, if you have a short-term surge in updates, you could end up with 900+ binary log files anyway, and that will lead you right back to the problem you have now.

  • Decrease your rate of binlog growth, by writing less to the database. We can estimate you are currently writing 120GB in 144 hours, based on the number of binlogs (assuming they are all close to max size). That's 853MB per hour, on average. Maybe that rate of updates is too high.

    It might be time for you to refactor to a sharded architecture, so your updates are spread over multiple Aurora instances. Aurora can scale up pretty high, but it can't keep scaling up forever.

  • Decrease the size of binlog events (if possible), by setting binlog_row_image=MINIMAL. But I don't know if this is a configurable option in Aurora parameter groups, and I don't know if DMS requires a full row image.

9
  • I don't see how "spiky" would make any difference unless something else is causing the roll to the next binlog. (That is, other than hitting the max.)
    – Rick James
    Sep 5, 2022 at 1:25
  • The expiration rate is based on time, not number of binlogs or total space used by binlogs (Percona has options for that, but not upstream MySQL). So if there's a surge in write traffic in a short time, it could cause a large number of binlogs to accumulate before they expire. I've seen this happen, and accumulate 500GB+ of binlogs in less than 72 hours. Sep 5, 2022 at 5:52
  • Those are not possible, nor easy to do.
    – Nir
    Sep 5, 2022 at 6:32
  • I didn't mean to imply they were easy. But as you scale up your traffic, you will hit a wall, so you'll have to make a choice. Sep 5, 2022 at 7:33
  • I know, but even with the high DML rate the DB acts fine. It just this command which seems broken in Aurora.
    – Nir
    Sep 5, 2022 at 10:09
0

You have about 120GB across 945 binlogs. Let's change that to 120GB across about 95 binlogs. (I and assuming that the sluggishness is due to the number of files, not the size of the files.)

Change the configuration (if RWS lets you):

max_binlog_size = 1073741824

(1GB is the max value for MySQL.)

2
  • Unfortunately max_binlog_size is not modifiable in Aurora.
    – Nir
    Sep 4, 2022 at 20:25
  • Send this Q&A to them.
    – Rick James
    Sep 5, 2022 at 1:21

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.