Configuring a report server to free a resource from a web server

Question

Yay, first post on SO! (Good job Jeff et al.)

We are trying to solve a bottleneck in one of our web applications that was introduced when we started allowing users to generate reports on demand.

Our infrastructure is as follows: 1, acting as a Web server / DBServer (ColdFusion 7 and MSSQL 2005)

It serves as a web application for our internal users and website. Reports are generated by users from the backend, so there is a level of security at which users have to register (on the website).

During peak hours, when creating reports, it leads to an unacceptable speed of the web application and front-end due to the fact that SQL Server uses resources for huge queries, and then ColdFusion generates multi-page PDF files.

We are not quite sure that the best practice would be to remove any load, but restricting access to reports is not an option now.

We looked at data denormalization for other tables in order to simplify the most common queries, but it looks like it just pushes the problem.

So, we are thinking about getting a second server and use it as a “report server” with a replicated copy of our database on which requests will be executed. This will fix one problem, but the second remains: generating PDF files is resource intensive.

We would like to offload this task to the report server, but, being in a secure web application, we can’t just run HTTP GET to create PDF files with the user registered in the web application from server 1 and displaying it in the web application, but generates / retrieves it on server 2 without checking user credentials ...

Does anyone have any experience? Thanks in advance Stack Overflow!

Answer 1

"We would also like to offload this task to the report server, but, being in a secure web application, we can’t just run HTTP GET to create PDF files with the user registered in the web application from server 1 and displaying it in the web application but generating / receiving it on server 2 without checking user credentials ... "

Why can not you? you use the simplest language in the world to write web services. here are my suggestions.

first move the database to your own server, having cf and sql server on separate servers. The first reason for this is performance. as already mentioned, having cf and sql on the same server is not an ideal setting. the second reason is security. if someone can hack your web server, it's good there to get your data. you should have a firewall between your cf server and sql to increase security. the last reason is scalability. if you ever need more resources or a cluster of your database, it's easier when it has its own server on it.

now for web services. what you can do is install cf on another server and write web services to handle report generation. just block the new cf server to accept only ssl connections and transfer user credentials to enter the web service. inside your web service, authenticate the user before calling the methods to create the report.

now for the pdf files themselves. one of the methods I did in the passage generates a hash based on some parameters passed (user credentials and generated sql to run the query), and then, as soon as the PDF is created, you assign the hash to the pdf file name and save it to disk. Now you have a simple caching system where you can see if the PDF file exists, and if so, return it, otherwise generate it and cache it.

in closing, your problem is not something that most of them have not seen before. you just need to do a little work and your application will grow faster.

Answer 2

The simplest practice is the lack of a web server server and a db server on the same hardware. I would start with this.

Answer 3

You must separate the perception between creating PDFs and doing calculations. Both are separate steps.

What you can do is

1) Create a table designed for the report that will work daily and fills it with all the calculated values ​​for all your reports.

2) When someone requests a report in PDF format, ask the report to make a simple request for pre-calculated values. This will be much less db effort than on-the-fly computing. You can use coldfusion to create a PDF file if it uses trendy PDF settings. Otherwise, you can leave using the raw PDF format (it looks like html markup) in text form or use another library (cfx_pdf, a suitable java library, etc.) to create them.

If users do not need to download and only need to view / print the report, could you please leave with flash paper?

An alternative is to create a report queue. If you host it on a second server or not, what CF can do, if you can handle it, you can send report requests to the queue and send them to users as they are processed.

You can then manage the queue through the planned process to work as regularly as you like and only create a few reports at a time. I'm not sure if this is suitable for your situation.

As mentioned above, running a stored procedure can also help, and make sure your indexes are set correctly in MySQL. I once had a 3-minute query, which I knocked down to 15 seconds because I forgot to declare additional indexes in each table, which were pretty much used.

Let us know how this happens!

Answer 4

In addition to the tips for individual web servers and db servers, I tried:

a) move requests to stored procedures if you are not already using them;

b) generate reports by the scheduler and store them in special tables in a ready-to-use state, so customers select them with only a few quick queries - this should also reduce the time it takes to create reports for clients.

Hope this helps.