Last week I was building VMware images for database and web server
appliances that would host a fairly large Django application. The
application is backed by a PostgreSQL database and I was looking for
some info on compiling/configuring pgpool on Debian (I like to compile
stuff when I can, especially when the last version of Debian is 2
years old). Googling around I came across some very interesting posts
on mailing lists and SO regarding Django, PostgeSQL and connection
pooling.
Among other things, people seem to have a notion that using pooling
middleware won’t accomplish much as the web server still needs to open
a TCP connection and that is the source of a noticeable overhead for
each request. So they’ve come up with solutions to avoid opening TCP
connections as much as possible, trying to accomplish something
similar to what SQLAlchemy’s connection pool does, keeping the web
server connected to the database with multiple connections at all
times. These solutions of course range from changing the code in
django.db.backends.... to monkey-patching it.
When you change Django’s code you’ve just created a fork of a growing
and evolving open source project and based your own project around
that fork you have to maintain yourself. Monkey-patching is not as
bad, but comes very close regarding maintenance problems. And is all
that really necessary?
The Overhead Doesn’t Stem From Opening TCP Connections
Every time you open a database connection (session) to execute some
SQL on a Postgres database the Postgres server spawns a new process
and upon closing the connection (session) from your application that
process is shut down. With the way Django handles database sessions
this is repeated for every request. Which means for every request
Postgres will have to spawn a new worker process that will last for
the duration of the database session involved in responding to that
HTTP request. The overhead involved in opening a TCP connection to a
process running on the same machine or on the same network is not much
compared to the overhead involved in spawning a new process.
Solutions Do Exist
So if the overhead for each request comes from spawning new processes
then the obvious solution would be to keep that at a minimum level. If
you keep the connections to the database server open and reuse them
for every request then the processes spawned at the time the
connections were established would be reused as the connections are
reused.
But you don’t have to keep your web server connected to the database
server to achieve this. Two more popular solutions are pgpool II and
pgbouncer. Both are designed as sort of middleware proxies that sit
between your application and your database. Pgpool is more of a
replication and load balancing solution than a connection pool. It
works as a connection pool because at each connection opened by your
application to pgpool it will have a separate process handling that
connection, but it will keep those processes alive and connected to
the Postgres server even after your application closes those
connections. So using it would have the effect of lowering the net
amount of new processes created to serve a certain number of requests
to your web application. Pgbouncer on the other hand handles all the
requests between your application and Postgres in a highly efficient
asynchronous manner by utilizing libevent and not using
multiprocessing at all, and it will keep the initially opened
connections for a longer time after your application closes them, so
making a new connection to pgbouncer will rarely result in Postges
spawning a process.
The Proof
To prove that solutions like pgbouncer do make a difference I created
a simple test scenario. A very small Django project with one page
displaying 5 rows from a table in a PostgreSQL database. Both the
database server and the web server (Apache with mod-wsgi in daemon
mode) running on one small VM with 1GB of RAM and 4 CPU cores
assigned.
In the first test I configured the application to connect to the
database server directly and put the page under
siege with 1, 5, 15,
50, 100 and 200 concurrent requests, each session lasting for 1
minute. Then I repeated the process with the application configured to
connect to pgbouncer instead. The results show something close to a
50% increase in responsiveness:
