One problem with web applications is often observed in practice: Without much effort (and without much thought) developers put together a web application with a database as a back end. Over time, the amount of data grows and eventually the system become sluggish. Impatient users press the reload button over and over again. To remedy this, the database administrator first creates indexes in the database and then purchases new technology, which helps – at least for a while. In the long term, the combination of a database at the edge of its capacity and an impatient user can very quickly lead to an application that takes frequent breaks.

Finding the Root Cause

The application in the example here is quite conservative: the Apache web server, mod_cgi, some CGI programs in Perl, and the PostgreSQL database. Apache HTTPD provides the Timeout directive to deal with user-induced denial-of-service problems like this and allows monitoring of the web server's I/O channels, such as the TCP connection to the browser or the stdout channel of a CGI program. If no data is transmitted over the specified period of time, the server terminates the request with an error. When releasing the resources allocated for the request, it also stops the CGI program. For this purpose, it first sends a SIGTERM signal to the program. If that does not help, this is followed a few seconds later by a SIGKILL.

This approach is simple but has two major drawbacks. First, the web server does not notice whether the browser closes the connection in the meantime because it does not expect any data to come in on the browser connection. Second, you cannot release the occupied resources in the database simply by terminating the CGI program.

This is what really happens: The browser sends a request, and the CGI program launches and sends the database query, which takes some time. The impatient user presses the button to reload the page.

Although the browser terminates the current request, the server doesn't see this because it is waiting for the CGI program. Now the timeout occurs and the program is terminated. Unfortunately, the database will not notice this and will continue the SQL query. A new request from the user then initiates a second query against the database, which is just as slow. After a few more clicks, the system is a goner.

Without changing mod_cgi or adding another module, it is impossible to terminate the request on the web server when the browser closes the connection. However, it is feasible to complete the database query as soon as the web server sends the SIGTERM signal. In this article, I'll show you how this is done. To follow the examples, you need a database in which the PL/pgSQL language is enabled. I used PostgreSQL version 8.4.

Test Environment

If you have a large test database available for which you can formulate SQL queries that take a few seconds or minutes to complete, you can use that. If not, PostgreSQL provides the pg_sleep function. The plpgsql function from Listing 1 does nothing but consume a huge amount of computing time so that the associated process will assume one of the top positions in the top output. For testing, you can call the burncpu program with psql:

r2=> select burncpu('30s');
     burncpu
-----------------
 00:00:30.000053

The results take 30 seconds to deliver. The CPU load table shows the associated database process for this time (Figure 1).

01 CREATE OR REPLACE FUNCTION burncpu (tm INTERVAL)
02   RETURNS INTERVAL AS $CODE$
03 DECLARE
04   stmp TIMESTAMP := now()+tm;
05   i INT;
06 BEGIN
07   WHILE clock_timestamp()<stmp LOOP
08     i:=1;
09   END LOOP;
10   RETURN clock_timestamp()-now()
11 END;
12 $CODE$ LANGUAGE plpgsql;

Figure 1: The burncpu() process at the top of the Top chart.

Another ingredient you need is the CGI program shown in Listing 2. You might need to install the Perl modules common::sense, DBI, and DBD::Pg if they are not in place – recent Linux distributions should have them in their software repositories  – and you also need to customize the login information for the database.

01 #!/usr/bin/perl
02
03 use common::sense;
04 use DBI;
05
06 print "Status: 200\nContent-Type: text/plain\n\n";
07 $|=1; $|=0;                     # flush
08
09 my $dbh=DBI->connect('dbi:Pg:dbname=r2', 'ipp', undef, {RaiseError=>1});
10
11 my $sth=$dbh->prepare('select burncpu(?)');
12 $sth->execute((($ENV{QUERY_STRING}+0) || .5).'s');
13
14 while( my $row=$sth->fetchrow_arrayref ) {
15   print "@$row\n";
16 }

Workaround

The script is simple, but the attentive reader might wonder what line 7 is good for: It bypasses a bug present at least in Apache 2.2.21 compiled with the prefork multiprocessing module (MPM). If a CGI program does not output anything on stdout or stderr, the web server runs into the timeout when trying to read stdout; then, when it tries to read from stderr, it runs into the timeout again.

Line 7 ensures that two lines are written on stdout; thus, only one timeout interval is needed. Unfortunately, this prevents the correct HTTP error code 504 being sent. Instead, the web server returns an empty response.

You need to install the program as burn0.pl in a directory configured for CGI scripts. In what follows, I assume the web server is running on localhost and the URL path /cgi/ is mapped to the CGI directory:

$ curl http://localhost/cgi/burn0.pl
00:00:00.50023

The output shows the amount of computing time the database engine consumed. You can pass in the desired time as a CGI parameter:

$ curl http://localhost/cgi/burn0.pl\?3
00:00:03.000207

In my httpd.conf file, I set a timeout of five seconds. If the SQL query runs for 4.9 seconds, the server should still provide a reasonable answer, but not after 5.1 seconds (Listing 3).

$ time curl http://localhost/cgi/burn0.pl\?4.9
00:00:04.900198
real    0m4.958s
user    0m0.003s
sys     0m0.006s
$ time curl http://localhost/cgi/burn0.pl\?5.1
curl: (52) Empty reply from server
real    0m5.044s
user    0m0.005s
sys     0m0.005s

To observe the effect described above, call burn0.pl with the parameter 60. The curl command is finished after five seconds, but you can watch the database process eating CPU cycles for a full minute.