Load testing w/ weird results (1k req, 40s resp times)
Doing some load testing and getting weird results. Any recommendations to get the average response time down to something manageable so I can use D1 in the wild
Doing simple query
SELECT * FROM table_name WHERE updated_at >= ? AND name > ? ORDER BY updated_at ASC, name LIMIT 1000
- 100k row table, 17mb
- 30s load test, 1k requests, average response time is 40s
- normal response time is 230ms
- Confirmed it's using an index with updated_at and name
- using cloudflare worker to query database19 Replies
Also reduced the LIMIT to 100 with still same results
when was this db created?
today
so it's a production database, not alpha - only other thing i can think of is, what happens as you ramp up?
like 1 request vs 10 vs 100 vs 1000
100 gives an average of 4s,
1 is 230ms
yeah i reckon 1k is either getting picked up by protections (since real traffic won't be 1k requests from a single IP) or you're saturating the single DO
Do protections complete the request? Because they eventually get 200s
How do I "not" saturate the single DO? I do expect pretty high loads on this endpoint
D1 doesn't have autoscaling yet - read replication will help with that. In the meanwhile your best bet is sharding the DB if you absolutely need to hit the DB, or use the cache API in Workers to minimise trips to D1
hmm sharding 10k rows seems like overkill. Alright I guess it is what it is. Caching this sort of data is not ideal
I seperated the 1000 req between 10 IP's and still got an average of 34s FYI
yeah must be the DO getting saturated then
I guess I'll wait for read replication then. I expect pretty high load so 34s won't really work
not ideal, as in it changes often?
Yea changes every minute or so and the query is cursor based filtering based on time. So endpoint will have a
nextPage cursor and that includes the updated_at and namei mean if you're doing 1k+ RPS you're probably going to want to cache that for 60 seconds, would save you a ton of server load
Totally agree with you here. But I think it's important to note that I'm talking about 1k requests total across 30 seconds. We're hitting peek 200 rps at 30s+ response times on a very small table. At 15-20 rps we're still hitting 4s response times
Unknown User•11mo ago
Message Not Public
Sign In & Join Server To View
@Neves haven't tried unfortunately. not aware of any easy ways to transfer data over
Unknown User•11mo ago
Message Not Public
Sign In & Join Server To View
Response time is fine locally @Neves