Lead Image © Fesus Robert, 123RF.com

Asynchronous PostgreSQL

Sophisticated

Article from ADMIN 16/2013

By Torsten Foertsch

Programming database queries in Perl is pretty easy thanks to the DBI module – but beware, where simplicity reigns, there be dragons.

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

...

Use Express-Checkout link below to read the full article (PDF).