2 Antworten
- Neueste
- Die meisten Stimmen
- Die meisten Kommentare
0
Consider to troubleshooting the following:
-
Review Instance Class and Storage Configuration:
- Ensure that the instance class and storage type/configuration meet the performance requirements of your workload. Consider upgrading the instance class or adjusting the storage configuration if necessary to handle the workload's I/O demands.
-
Optimize Binlog Configuration:
- Review and optimize the binlog configuration parameters on both the primary and replica instances. Ensure that the binlog format (
binlog_format
) and binlog retention settings are appropriate for your workload.
- Review and optimize the binlog configuration parameters on both the primary and replica instances. Ensure that the binlog format (
-
Monitor Disk I/O and Throughput:
- Use Amazon CloudWatch or other monitoring tools to closely monitor disk I/O metrics on both the primary and replica instances. Identify any spikes or bottlenecks in disk throughput that could be contributing to replication lag.
-
Review Network Configuration:
- Check the network configuration between the primary and replica instances. Ensure that there are no network latency issues or bandwidth constraints affecting replication performance.
-
Optimize Database Queries:
- Review and optimize the database queries executed on the primary instance to reduce the workload's impact on replication. Consider optimizing queries, adding appropriate indexes, and avoiding long-running transactions.
-
Review AWS Support:
- Consider reaching out to AWS Support for assistance. They may be able to provide insights and recommendations specific to your RDS setup and help troubleshoot the replication lag issues.
-
Consider Alternative Solutions:
- If replication lag persists despite optimization efforts, consider alternative solutions such as using a different replication method (e.g., asynchronous replication with delayed secondary) or exploring managed database services other than RDS.
-
Database Schema Optimization:
- Review and optimize the database schema design to reduce contention and improve replication efficiency. This may involve partitioning large tables, denormalizing where appropriate, or optimizing schema indexes.
-
Database Maintenance:
- Regularly perform database maintenance tasks such as vacuuming, analyzing, and optimizing tables to maintain database health and performance.
-
Database Migration:
- If all else fails and replication lag remains a persistent issue, consider migrating to a different database solution or architecture that better suits your performance and scalability requirements.
0
The follow-up on update #4
Output of show slave status
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | Master_public_key_path | Get_master_public_key | Network_Namespace |
|----------------------------------|-------------|--------------|-------------|---------------|-----------------------------|---------------------|-----------------|---------------|-----------------------------|------------------|-------------------|-----------------|---------------------|--------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-------------------------|-----------------------------|------------|------------|--------------|---------------------|-----------------|-----------------|----------------|---------------|--------------------|--------------------|--------------------|-----------------|-------------------|----------------|-----------------------|-------------------------------|---------------|---------------|----------------|----------------|-----------------------------|------------------|--------------------------------------|-------------------------|-----------|---------------------|---------------------------------------------------|--------------------|-------------|-------------------------|--------------------------|----------------|--------------------|--------------------|-------------------|---------------|----------------------|--------------|--------------------|------------------------|-----------------------|-------------------|
| Waiting for master to send event | 172.20.2.70 | rdsrepladmin | 3306 | 60 | mysql-bin-changelog.1193659 | 17271874 | relaylog.000175 | 77384349 | mysql-bin-changelog.1193367 | Yes | Yes | | | | innodb_memcache.cache_policies,innodb_memcache.config_options,mysql.plugin,mysql.rds_configuration,mysql.rds_history,mysql.rds_monitor,mysql.rds_replication_status,mysql.rds_sysinfo,mysql.rds_upgrade_prechecks | | | 0 | | 0 | 77384111 | 36609107623 | None | | 0 | Yes | | | | | | 10603 | No | 0 | | 0 | | | 1628242188 | 009a9080-ebc1-11ee-a3af-027b96cf0647 | mysql.slave_master_info | 0 | | Waiting for slave workers to process their queues | 86400 | | | | | | | | 0 | | | | | 0 | |
Output of SHOW PROCESSLIST Multiple outputs of all variants I could see. #1
| Id | User | Host | db | Command | Time | State | Info |
|----|-----------------|-------------------|--------|---------|-------|---------------------------------------------------|------------------|
| 5 | event_scheduler | localhost | | Daemon | 9722 | Waiting on empty queue | |
| 7 | rdsadmin | localhost | | Sleep | 8 | | |
| 8 | system user | connecting host | | Connect | 9668 | Waiting for master to send event | |
| 9 | system user | | | Query | 0 | Waiting for slave workers to process their queues | |
| 10 | system user | | | Connect | 10725 | waiting for handler commit | |
| 11 | system user | | | Connect | 10725 | waiting for handler commit | |
| 12 | rdsadmin | localhost | | Sleep | 0 | | |
| 46 | master | 172.17.98.7:60464 | SEARCH | Query | 0 | init | SHOW PROCESSLIST |
#2
| Id | User | Host | db | Command | Time | State | Info |
|----|-----------------|-------------------|--------|---------|-------|---------------------------------------------------|------------------------------------------------------------------------------------------------------|
| 5 | event_scheduler | localhost | | Daemon | 9762 | Waiting on empty queue | |
| 7 | rdsadmin | localhost | | Sleep | 8 | | |
| 8 | system user | connecting host | | Connect | 9708 | Waiting for master to send event | |
| 9 | system user | | | Query | 0 | Waiting for slave workers to process their queues | |
| 10 | system user | | SEARCH | Connect | 10756 | starting | INSERT INTO `SEARCH_PRICE` (
| 11 | system user | | SEARCH | Connect | 10756 | update | INSERT INTO `SEARCH_PRICE` (
| 12 | rdsadmin | localhost | | Sleep | 0 | | |
| 46 | master | 172.17.98.7:60464 | SEARCH | Query | 0 | init | SHOW PROCESSLIST |
#3
|----|-----------------|-------------------|--------|---------|-------|---------------------------------------------|------------------|
| 5 | event_scheduler | localhost | | Daemon | 9815 | Waiting on empty queue | |
| 7 | rdsadmin | localhost | | Sleep | 1 | | |
| 8 | system user | connecting host | | Connect | 9761 | Waiting for master to send event | |
| 9 | system user | | | Query | 0 | Waiting for dependent transaction to commit | |
| 10 | system user | | SEARCH | Connect | 10799 | waiting for handler commit | COMMIT |
| 11 | system user | | | Connect | 10799 | Waiting for an event from Coordinator | |
| 12 | rdsadmin | localhost | | Sleep | 1 | | |
| 46 | master | 172.17.98.7:60464 | SEARCH | Query | 0 | init | SHOW PROCESSLIST |
#4
| Id | User | Host | db | Command | Time | State | Info |
|----|-----------------|-------------------|--------|---------|-------|---------------------------------------------------|------------------------------------------------------------------------------------------------------|
| 5 | event_scheduler | localhost | | Daemon | 9856 | Waiting on empty queue | |
| 7 | rdsadmin | localhost | | Sleep | 12 | | |
| 8 | system user | connecting host | | Connect | 9802 | Waiting for master to send event | |
| 9 | system user | | | Query | 0 | Waiting for slave workers to process their queues | |
| 10 | system user | | | Connect | 10832 | Waiting for preceding transaction to commit | |
| 11 | system user | | SEARCH | Connect | 10832 | updating | UPDATE SEARCH_PRICE SET BOOKABLE = 0 WHERE BOOKABLE = 1 AND `DATE` >= '2024-05-01' AND `DATE` <= '20 |
| 12 | rdsadmin | localhost | | Sleep | 1 | | |
| 46 | master | 172.17.98.7:60464 | SEARCH | Query | 0 | init | SHOW PROCESSLIST |
#5
| Id | User | Host | db | Command | Time | State | Info |
|----|-----------------|-------------------|--------|---------|-------|---------------------------------------------|------------------------------------------------------------------------------------------------------|
| 5 | event_scheduler | localhost | | Daemon | 9905 | Waiting on empty queue | |
| 7 | rdsadmin | localhost | | Sleep | 1 | | |
| 8 | system user | connecting host | | Connect | 9851 | Waiting for master to send event | |
| 9 | system user | | | Query | 0 | Waiting for dependent transaction to commit | |
| 10 | system user | | SEARCH | Connect | 10870 | System lock | UPDATE SEARCH_PRICE SET BOOKABLE = 0 WHERE BOOKABLE = 1 AND `DATE` >= '2024-03-27' AND `DATE` <= '20 |
| 11 | system user | | | Connect | 10870 | Waiting for an event from Coordinator | |
| 12 | rdsadmin | localhost | | Sleep | 0 | | |
| 46 | master | 172.17.98.7:60464 | SEARCH | Query | 0 | init | SHOW PROCESSLIST |
beantwortet vor 22 Tagen
Relevanter Inhalt
- AWS OFFICIALAktualisiert vor 2 Jahren
- AWS OFFICIALAktualisiert vor einem Jahr
- AWS OFFICIALAktualisiert vor 3 Jahren
Thanks - I have added the most important line to my post: replication on the same setup running mysql 5.7 was near perfect with only some minor peaks in lag once in a while. With the switch to 8 we expected to get better performance and similar replication.