If Database and Application running in different region in AWS Cloud ?

If Database and Application on different region ?

1. Highest-priority recommendation

Move the application and primary database into the same AWS Region

For an interactive application, the writer database should normally be in the same Region as the application services.

Preferred architecture

Users

|

Application / API: Ireland eu-west-1

|

| Low-latency regional connection

|

RDS MySQL Primary: Ireland eu-west-1

|

| Asynchronous cross-Region replication

|

Read Replica / DR: Oregon us-west-2

Possible approaches:

1. Move RDS MySQL primary to Ireland

   • Best option if the main application and most users are closer to Ireland.
   • Keep an Oregon cross-Region read replica for disaster recovery if required.

2. Move the application to Oregon

   • Appropriate if Oregon must remain the primary data Region due to data residency, integrations, or operational requirements.

3. Create a read replica in Ireland

   • Route eligible read-only queries to the local replica.
   • Writes still travel to Oregon.
   • Replication is asynchronous, so the application must tolerate replication lag and eventual consistency.
   • RDS supports cross-Region read replicas for supported RDS for MySQL versions. 

4. If both Regions must actively serve users

   • Consider an architecture designed for multi-Region access.
   • Separate regional reads from globally coordinated writes.
   • Do not assume that a standard RDS MySQL primary can provide low-latency writes from both Regions.

Important: Increasing CPU, memory, IOPS, or buffer pool will not remove the physical cross-Region round-trip delay.

---

2. Diagnose where the response time is being spent

Before changing parameters, separate the latency into four parts:

Total request time

= application processing

+ connection acquisition

+ network round trips

+ database query execution

`

Step 1: Measure from the Ireland application host

Run tests from the actual EC2 instance, ECS task, EKS pod, or other compute environment hosting the application.

DNS resolution

dig your-rds-endpoint.rds.amazonaws.com

Check:

• DNS lookup duration
• Whether the endpoint resolves consistently
• Whether the application is caching an obsolete IP address

AWS recommends keeping cached RDS DNS TTL below 30 seconds because the database IP can change after failover. 

TCP connection time

time nc -vz your-rds-endpoint.rds.amazonaws.com 3306

TLS and MySQL login time

time mysql </span>

--host=your-rds-endpoint.rds.amazonaws.com </span>

--port=3306 </span>

--user=test_user </span>

--password </span>

--ssl-mode=REQUIRED </span>

-e "SELECT 1;"

Run this several times:

for i in $(seq 1 10); do

time mysql </span>

--host=your-rds-endpoint.rds.amazonaws.com </span>

--user=test_user </span>

--password='PASSWORD' </span>

--ssl-mode=REQUIRED </span>

-e "SELECT 1;"

done

Do not expose production passwords in shell history. Use this only with a temporary diagnostic account or a secure credentials mechanism.

Compare against an Ireland test database

For a strong comparison:

1. Create a temporary small RDS MySQL instance in Ireland.
2. Run the same SELECT 1 and representative application query.
3. Compare:
   • Connection-establishment time
   • Single-query duration
   • API response time
   • Transaction duration

If the Ireland database is significantly faster, cross-Region latency is the major contributor.

---

3. Count database round trips per application request

This is often the biggest application-side problem.

Suppose one API request runs 20 sequential SQL operations. Even if each query needs only 5 ms of database execution time, every query also waits for a cross-Region network round trip.

Look for:

• N+1 query patterns
• Queries executed inside loops
• One query per object or per UI row
• Separate queries for data that could be joined
• Auto-commit after every statement
• Repeated lookup queries
• Multiple existence checks
• ORM lazy loading
• Opening a new connection for every query
• Chatty stored-procedure calls
• Small inserts executed one row at a time

Application improvements

Replace repeated queries with set-based operations

Instead of:

SELECT name FROM customer WHERE id = 101;

SELECT name FROM customer WHERE id = 102;

SELECT name FROM customer WHERE id = 103;

Use:

SELECT id, name

FROM customer

WHERE id IN (101, 102, 103);

Use bulk inserts

Instead of executing one insert at a time:

INSERT INTO audit_log (event_type, created_at)

VALUES

('LOGIN', NOW()),

('SEARCH', NOW()),

('LOGOUT', NOW());

Fetch only required data

Avoid:

SELECT *

FROM orders;

Prefer:

SELECT order_id, customer_id, status, created_at

FROM orders

WHERE customer_id = ?

ORDER BY created_at DESC

LIMIT 50;

Reduce transaction round trips

Group related statements into one correctly scoped transaction:

START TRANSACTION;

UPDATE account

SET balance = balance - ?

WHERE account_id = ?;

INSERT INTO transaction_history

(account_id, amount, transaction_type)

VALUES

(?, ?, 'DEBIT');

COMMIT;

Do not make transactions unnecessarily long, because they can retain locks and old row versions.

---

4. Enable RDS observability

Database Insights or Performance Insights

Enable the applicable RDS database-performance monitoring capability and investigate:

• DB load
• Top SQL
• Wait events
• Top users
• Top client hosts
• Calls per second
• Average latency
• Rows examined
• Lock waits
• I/O waits

Performance Insights can display database load and top SQL, and with MySQL Performance Schema enabled it provides detailed wait-event and per-SQL information. 

Prioritize SQL based on:

Total database time = executions × average execution time

Do not optimize only the single slowest query. A query taking 50 ms but running 100,000 times might consume more database capacity than one query taking 10 seconds once per day.

Enable Enhanced Monitoring

Review operating-system-level data, including:

• CPU utilization
• Load average
• Free memory
• Swap usage
• Disk queue depth
• Read and write IOPS
• Read and write throughput
• Process and thread activity

CloudWatch metrics to review

At minimum, review:

• CPUUtilization
• FreeableMemory
• SwapUsage
• DatabaseConnections
• ReadLatency
• WriteLatency
• ReadIOPS
• WriteIOPS
• ReadThroughput
• WriteThroughput
• DiskQueueDepth
• FreeStorageSpace
• NetworkReceiveThroughput
• NetworkTransmitThroughput
• BurstBalance, if applicable
• ReplicaLag, if read replicas are used

AWS recommends monitoring memory, CPU, replica lag, and storage usage, setting CloudWatch notifications, and maintaining sufficient storage and memory headroom. 

Enable slow-query logging

In an RDS parameter group, evaluate:

slow_query_log = 1

long_query_time = 1

log_output = FILE

Start with long_query_time around one second, observe the volume, and reduce it later if required. Do not immediately enable extensive logging on a heavily loaded production system without monitoring the overhead and log volume.

Useful additional setting during controlled investigation:

log_queries_not_using_indexes = 1

Use this temporarily and carefully. A query that does not use an index is not automatically bad, especially for small tables, and this setting can generate substantial logging.

---

5. Analyze and tune the SQL

For each high-impact SQL statement:

EXPLAIN ANALYZE

SELECT ...;

Check for:

• Full table scans on large tables
• Large differences between estimated and actual rows
• Excessive rows examined
• Filesort
• Temporary tables
• Nested-loop amplification
• Incorrect join order
• Functions applied to indexed columns
• Implicit datatype conversion
• Leading-wildcard searches
• Unnecessary DISTINCT
• Large OFFSET values
• Missing filters
• Sorting without a supporting index

Common non-sargable pattern

Avoid:

WHERE DATE(created_at) = '2026-08-21'

Prefer:

WHERE created_at >= '2026-08-21 00:00:00'

AND created_at < '2026-08-22 00:00:00'

The second form gives MySQL a better opportunity to use an index on created_at.

Avoid deep offset pagination

Potentially expensive:

SELECT order_id, created_at

FROM orders

ORDER BY created_at DESC

LIMIT 50 OFFSET 500000;

Prefer keyset pagination:

SELECT order_id, created_at

FROM orders

WHERE created_at < ?

ORDER BY created_at DESC

LIMIT 50;

---

6. Review index design

Create indexes based on real query patterns, not simply on individual columns.

Composite index example

For:

SELECT order_id, status, created_at

FROM orders

WHERE customer_id = ?

AND status = ?

ORDER BY created_at DESC

LIMIT 50;

A possible index is:

CREATE INDEX idx_orders_customer_status_created

ON orders (customer_id, status, created_at DESC);

Validate with EXPLAIN ANALYZE before and after the change.

Index review checklist

• Index join columns.
• Index highly selective filter columns.
• Match composite-index order with equality predicates, ranges, and sorting.
• Check whether existing indexes are redundant.
• Avoid duplicate indexes.
• Avoid indexing every column.
• Consider write overhead before adding indexes.
• Keep primary keys compact when possible.
• Check foreign-key columns for appropriate indexes.
• Review table and index size.
• Refresh optimizer statistics when needed.

For large production tables, plan index creation carefully because it can consume I/O, increase replication lag, or affect application performance.

---

7. Fix connection management

Cross-Region database connections are particularly expensive to establish because TCP, TLS, and authentication can require multiple network exchanges.

Application connection-pool settings

Review:

• Minimum pool size
• Maximum pool size
• Connection acquisition timeout
• Idle timeout
• Maximum connection lifetime
• Validation query behavior
• Connection leak detection
• Number of application instances
• Number of worker threads
• Autoscaling maximum

Calculate the theoretical maximum:

Total possible connections

= number of application instances

× maximum connections per instance

× number of application pools

Keep this below the RDS capacity with sufficient headroom for:

• DB administrators
• Monitoring agents
• Migrations
• Background jobs
• Failover and recovery
• Traffic spikes

Do not increase max_connections blindly. Every connection consumes database resources, and too many active connections can increase scheduling and memory pressure.

Consider RDS Proxy

RDS Proxy can pool and reuse established database connections, reducing the CPU and memory overhead associated with rapid connection creation. It is particularly useful for Lambda, container autoscaling, connection storms, and applications that frequently open and close connections. 

Monitor these proxy metrics:

• Client connections
• Database connections
• Connection borrow latency
• Maximum allowed database connections
• Connection pinning behavior

RDS Proxy exposes controls such as IdleClientTimeout, MaxConnectionsPercent, MaxIdleConnectionsPercent, and ConnectionBorrowTimeout; AWS recommends retaining connection headroom rather than using the database connection limit completely.

RDS Proxy addresses connection overhead and bursts. It does not eliminate the Oregon-to-Ireland latency for SQL execution.

---

8. Review RDS instance capacity

Check whether the instance class matches the workload.

CPU

If CPU is consistently high:

1. Identify top SQL first.
2. Check query plans and indexes.
3. Review connection concurrency.
4. Check batch workloads.
5. Scale to a larger or more appropriate instance class only after identifying the cause.

Memory

Look for:

• Low FreeableMemory
• Increasing SwapUsage
• Buffer-pool misses
• Frequent physical reads
• Per-connection memory growth
• Excessive temporary tables

A larger memory-optimized instance can help if the active data set does not fit into memory, but it will not fix missing indexes or chatty application behavior.

Burstable instances

If using a db.t3 or db.t4g class, review CPU-credit behavior and confirm that sustained CPU demand is appropriate for a burstable class. For steady production workloads, a non-burstable instance may provide more predictable performance.

---

9. Review storage performance

Check:

• Storage type
• Provisioned IOPS
• Provisioned throughput
• Read and write latency
• Disk queue depth
• Workload peaks
• Temporary-table I/O
• Available storage
• Backup and maintenance windows

AWS recommends increasing I/O capability by moving to an instance class with higher I/O capacity, changing from general-purpose SSD to Provisioned IOPS where appropriate, or provisioning additional IOPS and throughput.

For demanding workloads, gp3 allows storage performance to be provisioned separately within applicable service limits, while Provisioned IOPS storage provides more predictable performance for latency-sensitive workloads.

Schedule these during low-traffic periods:

• Automated backups
• Snapshots
• Large batch processing
• Data archival
• Index maintenance
• Schema changes
• ETL jobs

---

10. MySQL parameter tuning

Use a custom DB parameter group, change one category at a time, and perform load testing before production rollout.

High-value parameters to review

InnoDB buffer pool

innodb_buffer_pool_size

On RDS, the default may already be calculated from instance memory, so do not automatically hard-code an internet-recommended percentage. Confirm:

• Database instance memory
• Buffer pool size
• Working-set size
• Buffer-pool hit rate
• Freeable memory
• Swap utilization

Temporary tables

Review:

tmp_table_size

max_heap_table_size

Also inspect:

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

A high number of disk temporary tables can indicate:

• Insufficient limits
• Large sorts or grouping
• TEXT or BLOB data
• Poor indexing
• Inefficient SQL

Increasing limits raises potential per-connection memory consumption, so calculate the concurrency impact first.

Connection limits

max_connections

thread_cache_size

wait_timeout

interactive_timeout

Tune these together with the application pool, not independently.

Durability-sensitive parameters

innodb_flush_log_at_trx_commit

sync_binlog

Changing these can improve write performance but can also change the amount of committed data at risk during a failure. Do not reduce durability without documented recovery-point approval, business sign-off, and failover testing.

Redo and transaction behavior

Depending on the supported MySQL version, review redo log configuration, checkpoint pressure, transaction size, and write bursts. Make sure long transactions are not retaining undo history or blocking purge activity.

---

11. Network and security checks

Prefer private connectivity

Use a supported private design such as:

• Inter-Region VPC peering for simpler connectivity
• Transit Gateway inter-Region peering for a larger hub-and-spoke environment

VPC peering can connect VPCs in different Regions using private IP addresses, and the traffic does not traverse the public internet.

Transit Gateway is useful when centralized and transitive routing is needed across several VPCs, whereas basic VPC peering requires direct connections and does not provide transitive routing. 

Check:

• VPC route tables
• Security groups
• Network ACLs
• DNS resolution across VPCs
• NAT gateway path
• Firewall and inspection path
• Packet loss
• TCP retransmissions
• MTU mismatch
• Accidental routing through on-premises infrastructure
• Cross-Region data-transfer cost

Private connectivity improves security and routing consistency, but it does not remove geographic latency.

---

12. Application caching

Cache frequently requested, slowly changing data using:

• ElastiCache for Redis or Valkey
• Application memory cache
• CDN for static or cacheable responses
• Precomputed summary tables
• Materialized application-level aggregates

Good cache candidates:

• Product catalog
• Configuration
• Reference data
• User permissions, with careful invalidation
• Frequently accessed reports
• Dashboard aggregates
• Search filters

Define:

• Cache key
• TTL
• Invalidation strategy
• Maximum staleness
• Cache-miss behavior
• Protection against cache stampede

Caching should reduce database calls, not conceal incorrect queries or stale-data risks.

---

13. Read/write separation

If the workload is read-heavy:

Application in Ireland

|

+-- Writes and strongly consistent reads --> Oregon primary

|

+-- Eligible read-only queries ----------> Ireland read replica

Suitable for the local replica:

• Reports
• Search
• Product catalog
• Historical data
• Dashboards that tolerate slightly stale data

Keep on the writer:

• Payment or order confirmation
• Read-after-write workflows
• Inventory reservation
• Account-balance checks
• Any workflow that requires immediate consistency

Monitor:

ReplicaLag

Read replica CPU

Read latency

Write throughput on source

Long-running replica queries

Replication errors

---

14. Recommended implementation plan

Phase 1: Immediate, one to two days

1. Confirm the exact AWS Regions and network path.
2. Measure TCP, TLS, login, and SELECT 1 time from Ireland.
3. Add application tracing for:
   • Total request duration
   • Connection wait time
   • SQL duration
   • SQL call count
4. Enable Database Insights or Performance Insights.
5. Enable Enhanced Monitoring.
6. Review CloudWatch CPU, memory, connections, IOPS, latency, and queue depth.
7. Enable controlled slow-query logging.
8. Identify the top 10 SQL statements by total database time.
9. Check connection-pool configuration.
10. Find N+1 queries and repeated sequential calls.

Phase 2: Short term, one to two weeks

1. Tune the highest-impact SQL.
2. Add validated composite indexes.
3. Batch inserts, updates, and reads.
4. Reduce ORM-generated round trips.
5. Correct connection-pool sizing.
6. Introduce RDS Proxy if connection churn or autoscaling is a problem.
7. Add application caching.
8. Right-size the instance and storage.
9. Tune parameters based on observed wait events.
10. Run repeatable load tests.

Phase 3: Architecture correction

1. Move the DB writer to Ireland, or move the application to Oregon.
2. If migration is not immediately possible, create an Ireland read replica.
3. Route stale-tolerant read traffic to the Ireland replica.
4. Retain the other Region for disaster recovery.
5. Test failover, replica promotion, DNS changes, and recovery procedures.
6. Track both performance and cross-Region data-transfer cost.

---

15. Success metrics

Define measurable targets before tuning:

Metric	Suggested objective
API p50 latency	Establish baseline, then reduce
API p95/p99 latency	Main user-experience target
DB connection acquisition	Stable and small
SQL calls per API request	Reduce chatty patterns
Top SQL average latency	Reduce based on query class
Rows examined per row returned	Reduce substantially
CPU utilization	Maintain healthy peak headroom
Freeable memory	Avoid sustained memory pressure
Swap usage	Ideally zero or consistently minimal
Database connections	Below safe capacity
Read/write latency	Stable without peak spikes
Disk queue depth	Appropriate for provisioned storage
Replica lag	Within application staleness limit
Error/timeout rate	Near zero under expected peak load

Bottom line

Work in this order:

1. Put application and writer DB in the same Region

2. Measure network and connection time

3. Reduce SQL round trips

4. Tune top SQL and indexes

5. Correct connection pooling

6. Add caching and local read capacity

7. Right-size RDS compute and storage

8. Tune MySQL parameters using observed evidence

For your setup, moving the primary database to Ireland, or moving the application to Oregon, will likely deliver a larger improvement than parameter tuning alone. Until that is possible, reduce sequential database calls, reuse connections, and place a read replica in Ireland for stale-tolerant reads.