Hi,
I have just noticed that a few new users who joined our organisation had a habit of hitting the refresh button on the reports multiple times and due to this multiple requests were fired to the mariadb server. What it is doing to our production server is that our server basically stops to work since its busy processing the report requests and the queue on the mariadb keeps on increasing either till the time the old requests are cleared or you clear the queue by restarting mariadb. During this time of excessive load we can see that the server load sometimes goes upto 4~5 and even upto 8 on htop and most of the times the load is mainly upto 0.5~1 only.
I then tried this on my test server by clicking the refresh button multiple times and found out that the server indeed can be blocked by a user if he/she clicks the refresh button multiple times on a report especially if the report is not a prepared report that means that the load time is less than 30seconds.
Any way possible that we can stop a user from clicking the refresh button multiple times?
Since this seems more like a BUG to me as due to this a single user can block all the server resources for their multiple clicks.
Edit:
So I have checked a report which loads in 14 seconds as shown below:
Now if I click Refresh button before this report loads multiple times say like 10 times the mariadb process list would show 10 requests in my test account and pressing any further there is an error called too many connections
pymysql.err.OperationalError: (1040, 'Too many connections')
Well I would give this guide so that users can try and overload the server with multiple report requests. This would probably CRASH and if not CRASH it would definitely make the SERVER not respond to other REQUESTS and would seem like it has CRASHED.
First Step is Choose a Report from ERPNext Reports which take some long time a good choice would be a report which is NOT a PREPARED Report. Basically any report which takes less than 30 seconds would do and best possible candidates would be any report which takes around 10~15 seconds to load (The Sweet Spot). Best Candidates from Standard Report == Stock Balance Report in the Stock Module it takes around 15 seconds on my data for 1 month range can be increased
Second Step Open this report like a very well mannered user and check the time to load at the bottom left of the report.
Once the report is fully loaded now awaken the HACKER inside you and start clicking on the REFRESH Button multiple times the MORE THE MERRIER. Till the time you get the error Too Many Connections. Chances are that if you are on Production Server there ample mysql connections the default setting in erpnext is 500 to bog down the server in test environment I generally limit it to 10 so I don’t get that much fun.
As soon as you start to HIT the REFRESH button multiple times there is going to be a BARRAGE of MYSQL queries for same report sent to the MARIADB server which you can check with SHOW FULL PROCESSLIST; in mysql console.
This would increase the load on the server many folds and might also crash the system if you have been a Great Clicker and your my.cnf defines max_connections = 500
Possible Solutions would be to BLOCK the Refresh Button till the time the report loads but there might be scenarios when you change the report filter and still the report has not loaded and in this ways we might have to block auto-update feature.
Or am I missing something as I can see that there is no time limit between clicking the refresh button or a meager 300 millisecond, ideally a report request should not go till the time the earlier one has not loaded or at max 2 if the load times are high the system should be intelligent enough to disallow such requests.
What I have seen many sites doing with big query report, is that you do not have access to the report directly. It will redirect you to a page where you can see that your report is being generated. Once created, you will have a link to the downloadable report (or link to the page if not downloadable).
I would say that anything that takes more than a couple fo second to generate should by default be queued as to no jam the server. User is being given a link with the status of his report (either in queue or report generated / ready to view)
Well that is the case with Prepared Reports but the system is generous enough to give prepared reports a skip if they take less than 30 seconds.
Check the code its hardcoded:
So its not a problem with prepared reports but a problem with other reports where the user has the option to click refresh as many times he/she likes and potentially giving the user a tool to potentially hang the server for other users knowingly or unknowingly.
Not all reports are prepared reports and I did give one of the standard reports as an example.
Also making all reports prepared reports or reducing the threshold for prepared reports also does not seem like the solution.
The idea should be if a user clicks 2 times on refresh then the old request should be closed on mysql or there should not be any option to click the refresh multiple times till the first request is executed.
Good points. Could we go even one step further and let the report and the threshold time (here 30sec by default) to be chosen as a parameter in a settiing doctype? Depending on the Cie and its users, we would then be able to choose for which reports, we can queue and after how long.
