In the last post I mentioned that the server was getting hammered by robots. If the CPAN Testers Reports site was the only site served by the DB, this wouldn't be a problem, however in order to build the database and serve several sites, the DB gets quite a demand placed on it. I've tweaked some of the configurations for the DB, such as increasing cache and reducing connection timeouts, but this is only eases the symptoms, and doesn't get to the heart of the problem.

The problem is that of long running queries, particularly for some very complex queries. So this week, I've been taking a look at some of those queries. In some cases I've been able to take advantage of index and summary tables that are now used, thus reducing complexity, and re-evaluating processing to reduce calls too. This has meant changes to the Reports Summary AJAX script, The Uploads Generator and the Statistics Generator, all of which now take less time and have noticeably reduced their load on the server. We're still seeing lots of robot hits, but now they are not taking out the server.

The biggest change has been to the code behind the CPAN Testers Statistics website, by reducing processing time by as much as 20 minutes. It's been the first step to making it easier to produce statistics, and introduce new ones. While reworking the code to reduce DB load, I've also introduced some new features to the site.

The first new feature is the availability of the raw data that is used to generate the graphs. Last month Tim Bunce wanted to update his Perl Myths talk, but had to ask me to produce some fresh graphs for him, rather than be able to generate them himself as he wanted them. At his suggestion, I've now made all the raw data files available as plain text (in CSV format) that produce all the graphs you see on the site. I'm hoping to produce more graphs in the future, but I'm also hoping that others may be able to use the raw data to produce other graphs that highlight different trends too.

The second new feature is the Performance Graph. This shows the number of requests the CPAN Testers Page Builder is processing and the number of reports being submitted each day. The 'requests' is the total number of requests for a page rebuild that have been processed, and 'page' indicates the number of unique requests that have been processed. A request is created when a report is submitted, a new distribution is uploaded or a website user requests a page on the dynamic Reports website, where a previous request exists. A page can either be a distribution page or an author page. Although it may appear that the page processing is low (it's actually producing 6 system files for every unique page request), the fact that the total requests processed is far higher than the number of reports being submitted, does mean that the Page Builder is performing well.

As you can see from the Performance Graph, the site attacks are more evident, but it shows that the Page Builder is coping very well with the ups and downs of the server load. There are more tweaks to come, as I turn my attention to other areas of the site, as well more changes to the CPAN Testers Statistics site, following several suggestions I received over the last few months. Expect more updates soon.