分离数据库与Web服务器时RDS性能劣化是否属预期？求排查指导

Hey Jens, sorry to hear you're hitting rough performance snags with your RDS trial—let's break this down step by step to track down where the bottleneck is hiding.

Since you moved from a local database on the same Danish IIS server to remote RDS, network round-trips are now a huge factor. Your 30 per-page queries mean 30 separate trips across the wire, so even small delays add up fast:

• Run ping and tracert from your IIS server to your RDS endpoint to check round-trip time (RTT). If RTT is over 50ms, 30 queries alone would add 1.5+ seconds of pure latency.
• Check for packet loss with pathping (Windows) — even 1% loss can cause retries that blow up total page load time.
• Double-check your RDS region! If you accidentally deployed it outside Denmark, cross-region latency will cripple performance. Move it to the Danish AWS region immediately if that's the case.

That many queries to generate a single HTML page is a red flag—let's make sure they're not the root issue:

• Enable RDS's slow query log (set the threshold to 100ms in the RDS console), then run a few test page loads. Export the log to see which queries are dragging their feet.
• For any slow query, run EXPLAIN before it (e.g., EXPLAIN SELECT * FROM customer WHERE id = ?) to spot full-table scans, missing indexes, or inefficient joins. Your local database might have masked these issues with warm caching, but RDS's cold cache or different configuration exposes them.
• Look for opportunities to merge queries: Can you combine multiple SELECTs into a single JOIN? Or fetch all needed data in one batch instead of 30 separate calls? Cutting down query count will slash network overhead instantly.

Even if your queries are fine, RDS might be starved for resources:

• Dive into RDS CloudWatch metrics: Keep an eye on CPU utilization, memory usage, disk IOPS, and database connections. If CPU is consistently maxed, your instance is too small. If memory is low, the database is swapping to disk (kill performance). If IOPS hit the limit, all queries will queue up.
• Review your RDS parameter group: For MySQL, ensure innodb_buffer_pool_size is set to ~70-80% of your instance's memory (this controls how much data is cached in RAM). For PostgreSQL, check shared_buffers and work_mem settings.
• Avoid burstable instances (t2/t3) if your app has steady traffic: These instances use CPU credits, and once they're exhausted, performance drops drastically. Switch to a general-purpose (m-series) or compute-optimized (c-series) instance instead.

Your old architecture is begging for caching to lighten the load:

• Implement page-level caching: Use IIS's built-in output caching to store generated HTML pages for repeat visitors. Even a 5-minute cache can cut RDS queries by 90%+ for popular pages.
• Add query-level caching: Cache results of repetitive queries (e.g., static site settings, product categories) in memory or a lightweight cache like Redis (if you can deploy it alongside your IIS server).
• Tune your IIS database connection pool: Make sure the maximum connection count is high enough to handle traffic, and that connections are being reused (avoid creating a new connection for every request—this adds unnecessary overhead).

To confirm where the problem lies:

• Export your RDS data back to a local database on your IIS server, then test page load time again. If local is way faster, the issue is network or RDS configuration. If local is still slow, your app's query/architecture is the real problem.

Start with the network check first—since moving from local to remote DB is the biggest change here, that's often the low-hanging fruit. Let me know what you uncover, and we can dig deeper!

内容的提问来源于stack exchange，提问作者Jens Væver Hartfelt