Insights:Common Website Architecture & Database Mistakes: These 6 Configs Hurt Performance the Most
Mistake 1: Improper Cache Key Design
Symptom: Cache hit rate is very low; cache space is consumed by a large number of useless keys.
Cause: The key includes user ID, timestamp, and random parameters, resulting in countless unique keys for the same content.
Fix: Include only dimensions that affect the content (category, pagination, language) in the key; remove personalized and random elements.
Mistake 2: Reversed Column Order in Composite Index
Symptom: An index was created but queries still perform full table scans.
Cause: The leftmost column is not the most frequently used field in filter conditions.
Fix: Place equality-filtered columns on the left and range-sort columns on the right; verify with execution plans to confirm the index is being used.
Mistake 3: Oversized Connection Pool
Symptom: Under high concurrency, the database experiences numerous connection waits or rejects connections entirely.
Cause: The connection pool upper limit approaches or exceeds the database's maximum connections, with multiple applications competing simultaneously.
Fix: Set the connection pool upper limit to 60%–70% of the database limit, reserving capacity for operational connections.
Mistake 4: Long Transactions Holding Locks
Symptom: Certain updates occasionally time out, and log entries show lock waits.
Cause: The transaction includes external requests, file operations, or large batch loops.
Fix: Restrict transactions to cover only database operations; move external calls outside the transaction boundary.
Mistake 5: Pagination Without Limits
Symptom: List pages become progressively slower after dozens of page turns, even degrading the database.
Cause: LIMIT offset, size must scan all preceding rows when the offset is large.
Solution: Limit the maximum page number, or switch to cursor-based pagination (position by timestamp/ID).
Mistake 6: Cache and Data Updates Are Out of Sync
Symptom: Content is updated in the admin panel, but the frontend still displays old content.
Cause: Only the database was updated without actively invalidating the cache.
Solution: Place cache invalidation and updates in the same workflow; optionally add a version number or short TTL as a fallback.
Bonus: Static Assets Not Using Cache
Symptom: Images and CSS are re-downloaded on every request.
Solution: Set long cache headers for static assets and include version numbers in filenames; change filenames when updating.
How to Self-Inspect
- Open the slow query log and review the top 10 entries.
- Use execution plans to confirm whether indexes are being hit.
- Check cache hit rate and key count growth.
- Monitor peak connection counts and wait times.
Limits
These are common "configuration-layer" mistakes; fixing them can significantly improve response times. However, if the data model itself is poorly designed (e.g., large fields stored in the main table), structural adjustments will also be needed.
Related Reading: