Java web application with slow MySQL queries

Question

I have a web application written using Java servlets. I use Tomcat 7 as my servlet, but previously used Glassfish and had the same problem.

This page has a "statistics" section, which is updated every 5 minutes. Statistics take about 30 seconds to generate due to the size of the MySQL tables used.

When I get the page load, I show cached statistics. After everything on the page has been displayed, I will close and close the output stream. Then I update the statistics. Thus, the user does not have to wait ~ 30 seconds to load the page and update statistics after the page has already been completely sent.

The problem is that if I refresh the page during the execution of the request, the page does not load until the request completes, which means that although the original user has no delays, there is a long delay after that.

Why is the application effectively stopping? Can't Tomcat use another workflow to process the request, although one thread is still busy?

Thanks.

Answer 1

What can happen, your data is “blocked for updating” during the update - it depends on how the data is recalculated.

A good way around this is to make a new calculation in a separate data area, and then move on to using the new data after all this. One way to implement this is to use the database view and version number, for example:

create table my_statistics ( id int not null primary key auto_increment, version int not null, -- other columns with your data ); create table stats_version (current_version int); -- holds just one row create view stats as select * from stats_version v join my_statistics s on s.version = v.current_version); 

Put your new statistics in the table with the version number current_version + 1. After all the calculations are done. Then a simple update stats_version set current_version = current_version + 1 switch to using the new data. This last statement takes only a few milliseconds, so blocking expectations are tiny.

After switching, you can delete the old statistics to save space.

Using the “switch” approach makes updating and “atomic updating” - updating occurs “instantly and completely”, therefore users do not see a partially modified data set.