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:
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.
Move the application to Oregon
- Appropriate if Oregon must remain the primary data Region due to data residency, integrations, or operational requirements.
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.
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:
- Create a temporary small RDS MySQL instance in Ireland.
- Run the same
SELECT 1 and representative application query. - 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:
CPUUtilizationFreeableMemorySwapUsageDatabaseConnectionsReadLatencyWriteLatencyReadIOPSWriteIOPSReadThroughputWriteThroughputDiskQueueDepthFreeStorageSpaceNetworkReceiveThroughputNetworkTransmitThroughputBurstBalance, if applicableReplicaLag, 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:
- Identify top SQL first.
- Check query plans and indexes.
- Review connection concurrency.
- Check batch workloads.
- 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
- Confirm the exact AWS Regions and network path.
- Measure TCP, TLS, login, and
SELECT 1 time from Ireland. - Add application tracing for:
- Total request duration
- Connection wait time
- SQL duration
- SQL call count
- Enable Database Insights or Performance Insights.
- Enable Enhanced Monitoring.
- Review CloudWatch CPU, memory, connections, IOPS, latency, and queue depth.
- Enable controlled slow-query logging.
- Identify the top 10 SQL statements by total database time.
- Check connection-pool configuration.
- Find N+1 queries and repeated sequential calls.
Phase 2: Short term, one to two weeks
- Tune the highest-impact SQL.
- Add validated composite indexes.
- Batch inserts, updates, and reads.
- Reduce ORM-generated round trips.
- Correct connection-pool sizing.
- Introduce RDS Proxy if connection churn or autoscaling is a problem.
- Add application caching.
- Right-size the instance and storage.
- Tune parameters based on observed wait events.
- Run repeatable load tests.
Phase 3: Architecture correction
- Move the DB writer to Ireland, or move the application to Oregon.
- If migration is not immediately possible, create an Ireland read replica.
- Route stale-tolerant read traffic to the Ireland replica.
- Retain the other Region for disaster recovery.
- Test failover, replica promotion, DNS changes, and recovery procedures.
- 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.
