Time Out During Requests

[BYMIntegrations]

Describe the bug
A clear and concise description of what the bug is.
We are having issues when getting data from the Foundations API for a number of accounts relating to Countrywide. These are happening with Contact, Property, Vendor and Applicant. It is resulting in missed data and no contacts being pulled through at all.

The errors we are getting are 504 responses.

Additional context
Add any other context about the problem here.
Applicant_archived_00000001_error504.json
Property_archived_00000001_error504.json
Vendor_archived_00000001_error504.json
Contact_live_00000001_error504.json

Specification

• This is office group related due to the way the underlying data has to be queried. Review the requests being executed and replay locally, inspecting each query in isolation. Involve SI team if needed to assist with any query performance review that may need to be undertaken. This may not be easy to resolve, however adding other query string parameters might solve the problem

[github-actions]

Thank you for taking the time to report a bug. We prioritise bugs depending on the severity and implications, so please ensure that you have provided as much information as possible. If you haven’t already, it really helps us to investigate the bug you have reported if you provide ‘Steps to Replicate’ and any associated screenshots.
Please ensure any personal information from the production database is obscured when submitting screenshots.
This issue will be reviewed in our weekly refinement sessions and assigned to a specific project board. We may also update the ticket to request additional information, if required.
For more information on our processes, please click here

[BYMIntegrations]

Dataset name: CWO-GBTG

[BYMIntegrations]

• Kyle-Reece Cooper,

Please feel free to call/email if you need to, this is very urgent our-side due to it being countrywide.

[BYMIntegrations]

Any news on this issue?

[plittlewood-rpt]

Hi @BYMIntegrations - is this a new problem (ie were you querying the same data successfully previously)? Can you give me more information about the requests you are making. I can see in your screenshot above that you aren't using any query string parameters, but is this the case in all scenarios?

[github-actions]

This issue has been updated and moved to our ‘Near Term’ column (typically completed within 0 - 4 months). We have assessed the effort required and outlined a technical specification - please take the time to review this detail. When we're ready to schedule the issue, it will be assigned to the relevant board where you can continue to track its progress to completion.
For more information on our processes, please click here

[BYMIntegrations]

hi it is just this customer for only the contact endpoint, we can query other endpoints

[BYM-Henry]

Hi @plittlewood-rpt,

Hope you are well.

This is regarding customers: CWO-GBTG and ABB-GBTG

Just to confirm the main issue here is we are experiencing a 504 response when making the following request:

https://platform.reapit.cloud/contacts?pageSize=100&pageNumber=1&sortBy=-modified&modifiedFrom=1900-01-01T00:00:00Z&fromArchived=false

For us this is a new customer so we have no experience with this working for us in the past, all other endpoints for this customer that we use seem to be working as expected.

This seems to be reproducible 100% of the time. Removing the sortBy parameter doesn't seems to have an affect. Reducing the pageSize parameter to around 10 seems to give a successful response periodically, but given the amount of calls we would need to make (around 100,000) and the response times (10 - 30 seconds) this wouldn't work for us and I wouldn't want to cause any problems on your end.

Changing the modifiedFrom parameter to a more recent date (2023-11-01 for example) seems to resolve the issue completely but we need to fetch all contacts.

Let me know if you have any suggestions.

Many Thanks,

Henry Fitch
BriefYourMarket

[plittlewood-rpt]

Hi Henry I’m not in the office today, however it looks like this is your initial pull. The timeouts are ultimately being caused by slow database queries which stem from the way we need to do the modified filter and sort. On larger databases we sometimes need to step in to add additional indexes etc to make them more performant. As it is an initial pull though, ordering and even querying by modified date probably isn’t even necessary anyway given it looks like you want to pull ALL contacts. If that’s the case, I’d highly recommend only using the modified parameters for subsequently pulls. You will get much better performance sorting by created date and probably omitting the date filter altogether for the initial pull. I’ll review the performance of the queries as soon as I can next week but the above should unblock you I think Thanks Pete

…

________________________________ From: BYM - Henry ***@***.***> Sent: Friday, November 17, 2023 3:03:20 PM To: reapit/foundations ***@***.***> Cc: Pete Littlewood ***@***.***>; Mention ***@***.***> Subject: [EXTERNAL] Re: [reapit/foundations] Time Out During Requests (Issue #10149) External Message - Please be cautious when opening links or attachments in email

________________________________ Hi @plittlewood-rpt<https://github.com/plittlewood-rpt>, Hope you are well. This is regarding customers: CWO-GBTG and ABB-GBTG Just to confirm the main issue here is we are experiencing a 504 response when making the following request: [image]<https://user-images.githubusercontent.com/51915903/283839284-9e1b2655-9d5e-4c55-bc08-d61f1e80f510.png> https://platform.reapit.cloud/contacts?pageSize=100&pageNumber=1&sortBy=-modified&modifiedFrom=1900-01-01T00:00:00Z&fromArchived=false<https://platform.reapit.cloud/contacts?pageSize=100&pageNumber=1&sortBy=-modified&modifiedFrom=1900-01-01T00:00:00Z&fromArchived=false> This seems to be reproducible 100% of the time. Removing the sortBy parameter doesn't seems to have an affect. Reducing the pageSize parameter to around 10 seems to give a successful response periodically, but given the amount of calls we would need to make (around 100,000) and the response times (10 - 30 seconds) this wouldn't work for us and I wouldn't want to cause any problems on your end. Let me know if you have any suggestions. Many Thanks, Henry Fitch BriefYourMarket — Reply to this email directly, view it on GitHub<#10149 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AHK7GI6VWFJNTC23422UROTYE54DRAVCNFSM6AAAAAA7KPTYJKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJWGU4DMMRQGE>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[BYM-Henry]

Hi Pete,

Cheers for responding, makes sense and yeh you raise a good point about the full pulls. I'll do some checks our side to make sure we will get the same amount of data without the sort and filter. I can confirm the same endpoint without the filter and sort is returning in about 10 seconds. If all is looking good will get these removed on the full pulls, I can probably looking at removing the sort on the daily pulls as well but I imagine if the database has had to filter them by the modified date it won't take it it much more resource to sort it.

Henry

[plittlewood-rpt]

Thanks Henry. It's likely going to be the quickest solution here (though we will review what's going on). Without getting too deep into it, because you're using a modified from date so far in the past, the database effectively ignores the indexes and ends up doing a near full table scan, hence the time out. When you use a more realistic datetime - as would be the case when you're keeping your dataset up to date after the initial pull - it will use the index as it will get benefit from doing so.

[BYMIntegrations]

We've made the changes to this, managed to pull in all of the data besides Landlords. We're hitting the endpoint https://platform.reapit.cloud/landlords/ and it's returning no data for either of the countrywide customer codes we have. Not sure if there's any data available to us, are you able to confirm?

[plittlewood-rpt]

Hi @BYMIntegrations the CWO and ABB builds don't have any landlords so I presume they don't deal with lettings (at least through our system).

[BYM-Henry]

Hi Pete,

I can confirm we have made the recommended changes on our side over the weekend, full pulls are no longer using the sortBy and modifiedFrom parameters. I will continue to monitor things, but everything seems to be working as expected and we are no longer receiving timeouts for the customers mentioned above.

Thanks,

Henry

[plittlewood-rpt]

Thanks for confirming. For reference we do have a project in flight to alter how this filter works (there'll be no change to behaviour in terms of the output, but we've made some data layer changes to allow us to query the data more efficiently). This should be starting to rollout fairly soon so you should see better performance more generally when using modifiedFrom/sortBy modified once we get these updates out in the wild.

[BYM-Henry]

With a different customer (YOM), we are seeing a large amount of timeouts, even with the above changes removing the filter and sort.

5,242 / 26,799 Appointments Endpoint (/appointments) returned a 504 error after 4 retries per call.

We are also seeing occasional timeouts on the following endpoints:

/applicants
/contacts
/landlords
/propertyimages

We are also seem some failures due to incorrect 429 errors, but this issue has been raised on a separate ticket: #10259

Many Thanks,

Henry

[github-actions]

It looks like you have commented on a closed issue. If your comment relates to a bug or feature request, please open a new issue, and include this issue number/url for reference.
For more information on our processes, please click here