Thread was busy 99% of the last 20 seconds

[aburningflame]

We have a button that does an AJAX callback and selects rows from our database and loops through them to create a CSV.
The select statement can return a varied 10k-50k rows - because of that it seems the application server gets hung up which causes
a site-wide slowdown (as illustrated in the log below).


Are there any settings I can tweak to have this functionality behave without affecting the rest of the site? I know that it may be long-running..but I need it to complete.
If the Alpha Web Server does not work - is there any other approach/workaround I can take to have this behave appropriately?

Thanks in Advance

Here is my XBASIC Stack Log
Tue Jul 04 15:28:18 2017


Thread: AppServerWorker6
Request URI: /app/__a5RunDialog.a5w?__pageName=AAProcessPayroll.a5w&__dialogFilename=uxProcessPayroll&__alias=UXPROCESSP&__unsaved=no
Line: 351 else
Thread priority reduced.
Thread was busy 99% of the last 20 seconds.
Raw Data: {not sure what this represents, omitted for privacy}
The script causing this behavior is using a lot of CPU time.
You may want to review the page to make sure you have optimized any XBasic scripting.

Long running and processor intensive scripts tend to tie up available threads and adversely affect the perceived performance for multiple users.

Execution Stack:
0#1 evaluate_shared_private(
0#37 OOhttpdServerClientMIME(ptemp,"AppServerMain")
0#2 A5WCallResult = evaluate_a5w_to_response(page_contents,Response,quote(" " + eval_settings.global.Request.ScriptName),eval_settings,EVALUATE_A5W_TIMEOUT,RequestBundle.RequestInfo.Filename.Path)
0#179 x_out = a5_ajax_dialog2(tmpl)
0#351

[Steve Workings]

Re: Thread was busy 99% of the last 20 seconds

Scott- can you do this as a SQL stored procedure?

Another option that may or may not fit your needs is to set up a separate runtime instance to do this as needed. One side benefit might be that other similar tasks can also be offloaded to the runtime.

[Pat Bremkamp]

Re: Thread was busy 99% of the last 20 seconds

Scott,
Another idea would be to break it into two steps. Make the 10K to 50K table a temporary table (In MariaDB I'd use a memory engine table), then release the main table and do your export to CSV from the smaller temp table.

[aburningflame]

Re: Thread was busy 99% of the last 20 seconds

We are using SQL server which does not have an INTO OUTFILE like MySQL so we cannot export directly from a table/query to a file. With Alpha WAS acting as the middleman for the CSV generation it seems the large recordset is not playing nicely.

One approach we are going to explore is using a SQL Server stored procedure as Steve suggested and using cmdshell in order to use the bcp.exe utility outlined here for file generation:
https://docs.microsoft.com/en-us/sql/tools/bcp-utility

Hopefully this does work, but if not I think offloading the process to another instance (possibly even PHP+Apache if performance merits) is going to be the Plan B.

Will report my results in case it helps anyone in the future.

Appreciate your time / input Steve and Pat.

[Steve Workings]

Re: Thread was busy 99% of the last 20 seconds

Setting up a runtime instance is easy, requires very little resources, and just sits there and does the job very nicely. I'm constantly working to shove more and more off to runtime in my biggest application. It just fires a function every 60 seconds that checks, then processes whatever is in the work queue.

The most complex part of it is building a work queue processor, but it's not that hard and, of course, its reusable.

[menehune]

Re: Thread was busy 99% of the last 20 seconds

Steve, is the runtime setup on the same machine as WAS? To piggyback off that question is there any documentation on how to setup multiple runtime processes on the same machine?

[kkfin]

Re: Thread was busy 99% of the last 20 seconds

Setting up a runtime instance is easy, requires very little resources, and just sits there and does the job very nicely. I'm constantly working to shove more and more off to runtime in my biggest application. It just fires a function every 60 seconds that checks, then processes whatever is in the work queue.

The most complex part of it is building a work queue processor, but it's not that hard and, of course, its reusable.

This is an amazing idea.

I have not used runtime lately but I can see the benefits.

Runtime also has nodejs integration so you can start for example separate(s) Expressjs server(s) from runtime to show results or to take form inputs from user and so on (I have not yet tested this). Possibilitys are endless using runtime and its nodejs integration. You can operate together with Was or without it.

[menehune]

Re: Thread was busy 99% of the last 20 seconds

This is an amazing idea.

I have not used runtime lately but I can see the benefits.

Runtime also has nodejs integration so you can start for example separate(s) Expressjs server(s) from runtime to show results or to take form inputs from user and so on (I have not yet tested this). Possibilitys are endless using runtime and its nodejs integration. You can operate together with Was or without it.

Exactly! I'm assuming a few runtime instances installed into different locations, monitored by always up, talking to the database as well as a cluster of expressjs servers will allow an app to increase concurrent users without additional WAS licenses. My employer moved on from Alpha, even though they could afford a hundred annual licenses, because of WAS unreliability. Now using Google Firebase for $25/mo gives them 100,000 concurrent user limit on the db plus all the other free cloud functions...and it is *always up*.

[menehune]

Re: Thread was busy 99% of the last 20 seconds

Scott, we had the same type thing going on a few years ago with 100K record batches. The database stored procedure to an indexed staging table solution removed the WAS processor load and allowed the user to issue the callback, wait a bit for success response, then click the "export to excel" button which hit the staging table.