Maintaining UI responsiveness when processing large query

YF Distributor South Africa shared this idea 54 days ago
Idea Logged

Good day Support,

Hope you are well.

We have a question from a client that we would like some insight on.

Below is the use-case.


"Their issue is that the Yellowfin front-end becomes unresponsive when a report with a large number of rows is requested.

In their case the only way to recover the system is to restart the Yellowfin server.


Closer investigation revealed that they are indeed requesting a very large number of rows between 2m - 6m and hitting the maximum cpu processing limit allocated to the server.


We have showed them ways to limit the number of rows returned including Yellowfin's own row limiting ability.


Is there a way Yellowfin can detect this type situation let's say as a result of a poorly designed report and either auto terminate the query or govern the worker threads in such a way that it the system as a whole remain responsive?"

Kind regards,

Mikayla

Comments (11)

photo
1

Support will naturally give you a better answer than me but I've recently become aware of a JSP that allows you to kill individual threads. Assuming your server is still able to process the request and you know which thread it is, you can go to

my_server/info_threads_enhanced.jsp and kill them there

Another way we've handled unintended disruptions like this is to run a cron that curls the server(s) and if anything other than a status 200 is returned, it restarts the application. We've set to check every 5 mins and it has worked well for us in exactly the situation you've described, where a user runs a poorly designed report that returns millions of rows


Thanks

Dean

photo
1

Thanks a lot Dean!

photo
photo
1

Hi Mikayla,

Thanks for reaching out.

When a report is taking a long time to run, users encounter this dialog box -


b9b8cb9b1a4f9b458bc26112fbb3210d


Can I ask what version you are running? We have a fixed bug where some versions of 9 were missing this prompt.


Thanks,

Eric

photo
1

Hi Eric,

Trust that you are well.

Please see the below response from our client.

This occurs on Yellowfin 9.6.2 (build 20211028)


 


Thanks for the response and reminding me of the background execution settings, it is relevant. I think the problem with this is that most users would reach the 5 second mark (happens quite often actually on larger resports) click on wait and THEN get the system into trouble. 


 


We would ideally need something which protects the integrity of the system irrespective of the actions of a single user.


 


Looking forward to the response from Yellowfin on this.”


 


Kind regards,

Mikayla

From: Yellowfin Support <support@yellowfin.bi>

Sent: Thursday, 05 May 2022 18:00

To: AIGS Support <support@aigs.co.za>

Subject: New Comment in "Maintaining UI responsiveness when processing large query"

photo
1

Hi Makayla,

Thanks for the reply. I'd recommend using the row limit option capability to manage the long running reports in this case. I could also make a request to devs to look into making an additional parameter that sets a 2nd "cancel" period for long running reports after "wait" is selected, does this sound like something you'd be interested in pursuing?

Thanks,

Eric

photo
1

Hi Eric,

Hope you are well.

Please see the response below from our client.

"Thanks for the feedback.


Yes I would like to suggest that they discuss this with the developers, but the issue should be framed correctly.


The first wait prompt is a user preference, the second issue is a quality of service issue which should prevent a single query from exhausting either cpu (this case) or memory in the interest of other users and overall system stability. I would rather a user prepared to wait, wait longer while the api serving the ui remains responsive to that user changing their mind and cancelling their query or an administrator doing it for them. Issues relating to resource contention is not new and the priority here is to reserve sufficient capacity keep the system responsive to user and administrative action."


Kind regards,

Mikayla

photo
1

Hi Mikayla,

Thanks for the update here. It sounds like they would like some type of defined CPU / Memory limit for report execution, or a reserved amount of overhead that would allow for system navigation, etc. does that sound accurate? I don't think we have anything on file for this but I could have devs look into this possibility on your behalf. Let me know what you think.

Thanks, Eric

photo
1

Hi Mikayla,


Hope things are good on your end, just wanted to check in to see if you had a chance to review my reply at this time.

Thanks,

Eric

photo
1

Hi Eric,

All is well thank you!Trust you are good too.


Just an add-on from the initial scenario:-

Sure, we can optimise and time the broadcasts outside of user hours, but the fact remains that unless the machine runs out of physical resources, the Yellowfin server itself is responsible for maintaining a minimum QOS relating to the responsiveness of its web server in a multi-user environment.

This includes not starving the Tomcat server of resources to the point where it becomes unresponsive. Still my best guess as to what is happening here.

Separating out broadcasts in a separate db pool was a start, but is clearly not helping with cpu/memory pressure in the same java vm.


Not sure how to achieve this separation other than potentially running queries out of process in a separate Java VM, but it is a worthwhile question to put to Yellowfin and have them ponder this.


Could you kindly have the devs look into this.


Have a great Friday!

Mikayla

photo
1

HI Guys,

Thanks for the reply here. I did some digging and found an old task from several years back -

There are situations where end users can create large cross-tabs or report sections, which will kill the CPU and grind YF to a halt for all users.

Can we do something from YF to stop people from killing the server.
Limiting data results is not an option . 

sounds like you guys! I'd add you as an impacted client...

It was closed as a "Won't Do" however - I guess it was more difficult to implement than I'd think. But that was a while back. I think I'll re-submit on your behalf.


We also have an existing "performance monitor" task that might be able to include some of these options -

https://community.yellowfinbi.com/topic/offer-realtime-monitoring-information-in-an-admin-dashboard

Not sure if that would meet requirements however.

Thanks,

Eric

photo
1

Hi Eric,


Hope you are well.


Thank you kindly for the response.

Can I ask that you forward the TASK ID to us once you re-submit please?


Kind regards,

Mikayla

photo
1

Hi Guys,


Thanks for the clarification. I've gone ahead and created a developer task to look into potentially adding this functionality to a future version of the application. I've attached this ticket to the task and added your organization as an affected client for tracking purposes. Task ID here is YFN-24531. Updates to the task will be provided here as they are available. I'll mark this as Idea Logged for now; feel welcome to reply here with any related inquiries.

Thanks,

Eric