Generate PDF report from MYSQL DB looping through large amounts of data without timing out PHP

user_14551576 0
Viewed 212

I need some advice on a problem/ implementation. So my platform has a page where a user can download a PDF report of all there added data. The data downloads into a tables layout and is paginated etc and at the bottom of each page there is totals, totals from previous pages and grand totals which is fetched from the indidual added data as in the picture. Now the problem occurs when a user has maybe 5000+ entries which will generated a pdf report of nearly 1000 pages. For each new page (counter system) then multiple queries happen to get relevant data for that particular page. If a user does have like 5000+ records then the server will just come back with a NGINX 404 not found error after like 45 seconds or something.

So I am wondering what things I can try to stop this happening, I have tried adding usleep() around all the queries to stop it spamming the DB but that doesn't seem to work. Hosted on AWS

I know not much information about bit can add more information if required

Thanks

enter image description here

amazon-web-services mysql php
1 Answers

If the request takes a long time to respond (in web terms honestly anything more than a few seconds is "a long time", anything that can potentially be over 30 seconds is an eternity) then it shouldn't be done in-place on the page. Instead, off-load the actual processing to something else.

Consider a system where a user "queues" a report to be generated. This is little more than just writing a record to a database table. Quick, easy, the user is presented with feedback telling them the report is being generated, and they can continue to use the application.

Meanwhile there's a background process separate from the website (but can still be in PHP if you like) which monitors that table for queued reports and processes them. (Maybe it polls the table at regular intervals, for example.) As a separate non-web application, this process is not bound by web server rules. It can run a long time, it can run multiple parallel instances on multiple threads/cores/etc., it can scale separately from the web interface, and so on.

This background process will see the new record and generate the report. It should update the record immediately to indicate that it's running that report, so another instance of this process doesn't duplicate the work. When it's done, update the record with details (such as the path of the resulting report).

The website can simply have some kind of notification system which lets the user know that a report is ready. For example, maybe every page runs a quick query against the table of reports to see if there are completed/unread reports for the user. When there are, some sort of notification tells the user this and the user can download the new report.

Long story short... Don't generate the report in the web application. The web application is just an interface to the list of queued/in-progress/completed reports. Another process outside of the web application should handle the long-running tasks.

user_3281930
Related