Request abnormal if having big data size through peer connection cross region

I suspect the answer is the network MTU - that is, the largest packet that the instances on either side are allowed to send to each other. In theory, the MTU should be automatically adjusted but this process may not work in some circumstances depending on a variety of factors.

It is pretty easy to test though: On at least one of the instances, set the MTU from what it is today to something lower such as 1500 or maybe even 1400 bytes. I can't give you more detailed instructions because your question does not say what operating system you're running. Note that the inter-region peering limit for peered VPCs is 1500 bytes but depending on the operating system you may have to specify a slightly lower number.

Note that within a region, larger packets are allowed which is why that is working for you. The reason that inter-region connections hang is because one instances (probably the database server) is trying to send a packet that is larger than the MTU (1500 bytes) and that packet is being dropped without feedback getting to the instance.

Ref: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/network_mtu.html

The issue you're experiencing with queries that return larger datasets (LIMIT 100) hanging while smaller queries (LIMIT 1, LIMIT 10) work fine is likely related to data size limitations rather than the VPC peering connection itself.

VPC peering connections don't typically impose data size restrictions. However, there are a few potential reasons for this behavior:

1. Network latency: Larger result sets take longer to transfer across regions, which could appear as a hang if the timeout is too short.

2. Application-level limitations: There might be a size limit in your application or the API you're using to execute the queries.

3. Database response size limit: Some database APIs have limits on the size of the result set they can return. For example, the Amazon Redshift Data API has a 100 MB limit on the result set returned by the database.

4. Network packet size: In rare cases, there could be issues with large network packets traversing the VPC peering connection, though this is unlikely to be the root cause.

To troubleshoot this issue:

1. Check for any timeout settings in your application and consider increasing them for cross-region queries.

2. Monitor the network performance and latency between the regions during query execution.

3. If possible, try executing the same queries directly on the RDS instance in region-B to isolate whether it's a network issue or a database issue.

4. Consider implementing pagination in your queries to retrieve large datasets in smaller chunks.

5. Review your application logs and database logs for any error messages or warnings related to data size or query execution.

If the issue persists after these steps, you may need to contact AWS support for further investigation, as they can provide more detailed analysis of your specific setup and any potential network or service-specific limitations.
Sources
Troubleshooting issues for Amazon Redshift Data API - Amazon Redshift
Cross-Region RDS Replica Not Syncing Existing Data | AWS re:Post