I'm currently working with an RDS cluster that has two db.t4g.large nodes and I'm facing performance issues when querying data from my EC2 instance. Both the RDS and EC2 are in the same VPC and connected appropriately, so that shouldn't be a problem. The query itself is a simple one on a single table, targeting a TEXT column with an index, and normally returns about 500MB of data. However, the entire process, including the query and data transfer, takes around 90 seconds when there's no load on the cluster. I'm wondering what steps I can take to improve this performance. I feel like a different instance type wouldn't help much since 8GB of RAM and 2 CPUs should suffice. Also, I noticed that the db.t4g.large seems to be the largest instance type available when I try to modify it. Am I overlooking something? What are some effective strategies to enhance speed?
5 Answers
You might need to provide more details about your query. Sometimes the index might not even be utilized. Try getting a query execution plan, and let us know how long the TEXT field is. Different databases handle TEXT fields uniquely, and that might be part of the issue.
500MB is quite a chunk of data to pull at once! Have you considered paginating the results? If you only need a portion of that data immediately, breaking it down could significantly reduce your query time.
I absolutely need all of it—it's for finance, so it's all big data. I'm leaning towards using Snowflake since its parallel processing capabilities might help speed things up.
It's worth checking the independent query and transfer speeds. If the transfer speed is the bottleneck, your bandwidth might be limited. Since you're using a burstable instance type, you won't always get consistent network performance, so that could also affect your overall speed.
I'd suggest you analyze where your 90 seconds are being spent. Is it mostly in the query execution or data transfer, or is it more balanced? Knowing where the bottleneck is can guide your next steps on whether to optimize the query itself or the transfer speeds.
Totally agree with checking the query and transfer speeds. What database are you working with? Every database can have different optimization techniques.
Exactly! I've had similar experiences where queries were written in a way that didn’t leverage existing indexes. Also, can you clarify which database you're using? Each has its quirks with TEXT data.