[plt-scheme] connection pooling with schemeql and plt webserver

Daniel Roy <droy at MIT.EDU> writes:

> I wrote a website using schemeql+plt's webserver and am finding that after
> a while connect-to-database fails with "too many connections" exception
> being thrown.  i was under the impression that the connections were
> garbage collected.  looking into connection.ss i found a "FIXME" that
> talks about freeing environment handles.  is this the source of the leak?

No, I don't think so.  Are you using: disconnect-from-database when
you are done with the db?  That would solve the problem methinks.  You
still need to open a connection for every hit you receive, not a Nice
Thing(TM) to do... for an alternative keep reading.

> ideally, i'd like to implement some connection pooling *between* servlet

Yes, that's what you should do. :-)  Here's what I've doing for db
extensive applications using this same combination (plt's web-server,
and schemeql):

   1. Add a intermediate layer (tier) between the db, and the
   front-end (servlets).  This program can create a pool of
   connections (or a single connection if concurrency is low).

   Open a socket (tcp, ssl, etc.) and receive connections from your
   servlets, do db interaction, and return to the client (servlet).

   2. Your servlets will become clients of the above program, and
   whenever they need to interact with the db, they will connect to
   the intermediate server.

You have at all times only a fix set of connections open to your db
(which you can change heuristically, and dynamically of course).  You
can have your web-server, and your db in different running instances
of PLT scheme (which is useful if they are running in different
machines, or you have a multi-processor machine).

Making this kind of (two|three) tier client/server architectures is
fairly standard, and is very easy, search for `run-server' using the
help desk, or visit:

http://download.plt-scheme.org/doc/205/html/mzlib/mzlib-Z-H-36.html#node_chap_36

you don't need to worry much about the protocol between you server,
and your clients (servlets in this case) because both ends are PLT!.
Connecting to your server from a servlet is easy too, one-liners using
`tcp-connect', `ssl-connect', or similar.

[Security is a concern, so make sure to firewall protect the ports you
will be using, or something similar.]

HTH,
--Francisco