Database Timeout only in some scenarios
Hi guys.
I have an user trying to perform an action in bulk for 3000 images but we get a database timeout every single time.
The strange thing is that I'm using the exact same settings but on a 7000 images task and it works normally.
I'm happy to provide all the context and information needed for it but I honestly don't know what could be happening. We thought it was the number of images but then we tried with more than double and it's ok.
23 Replies
What table is this happening on?
Could you please share a traceId?
6e65691d09ec0dd6ad1ca3953aec0f3c
6e65691d09ec0dd6ad1ca3953aec0f3c
Ok, I've just run an operation on your db table. Let me know if this persists
Yes it persists
The store with issues is https://admin.shopify.com/store/pilgrim-b2c
The store doesn't matter in this particular case. You're having database operation timeouts, unrelated to the shop that's experiencing them
I see
Why does it happen there specifically and not in a reqiest twice as big?
I need to investigate further
Are you perhaps passing a very large filter to fetch media records?
This is probably what's causing the issues:
If you pass too many filters you can have quite slow queries
When I do an all images bulk ids is empty
So in the request that breaks, that array comes empty and it skips that
From the traceId that you shared, I see a filter for ids in an array of 51 items
Still digging btw
091b5afd68e8ec59bab267b6b382e2c3 recent one
Still a ton of ids in that query
Yeah. Sorry I don't usually work on this app that's Renan and he is on vacation haha
Trying to put this fire out
I think I can't skip that id filter
I was wrong about the number of id filters
Its way more
Ok, to fix this issue you need to change the way that you folks make requests to the database. You seem to be filtering on over 1700 ids. You need to split the filters into smaller chunks.
It's like 3 k, right?
You should have a filter array of around 100 ids at a time
How can I do that?
Split the ids array into chunks, use a for loop to loop through chunks use a while loop inside the for to fetch all data you need and add it to the overall returned records array
Everything inside bulkGenerateProducts, correct?
I would do that in general for all your helper functions because the same filtering is done in all of them
I see. Thank you
No problem! Let us know if you need more information