1000 connections in PostgreSQL on OS X

PostgreSQL is not really geared towards more than a few dozen simultaneous connections on desktop operating systems. In this post, I'll show you how to push PostgreSQL 9 on Max OS X 10.6 to handle 1000 connections on a single system running both the webserver and the database.

The first step to getting to 1000 connections is to make PostgreSQL actually try to do it. By default, it only allows 100 simultaneous connections. Change /Library/PostgreSQL/9.0/data/postgresql.conf from max_connections = 100 to max_connections = 1000.

PostgreSQL creates one process per connection, and the web server will need one TCP connection to each one. Thus, to have 1000 connections, we need to allow at least 1000 processes per user (for the PostgreSQL processes) and at least 1000 open file descriptors per process (for the app server). Each PostgreSQL process seems to use 32 fds, and with some margin in case it grows, we should permitt 35000 fds in the system, plus some for other uses (in case you want to do other things on your machine, like logging in to it). The kernel seems to be picky about power-of-two increments, so I'll round the values up to things that it will accept.

Change /etc/sysctl.conf from




This will make sure the kernel reserves enough space etc. In addition, we need to allow processes to create enough sub-processes (as reported by ulimit -u). This limit is set by OS X's equivivalent of init, called launchd. Change /etc/launchd.conf (or create if you don't have it already) to say limit maxproc 2000 2000.

The changes to /etc/sysctl.conf and /etc/launchd.conf both take effect on bootup, so reboot the system and have fun with your 1000 connections!


Thee-tiered testing

The three-tiered program structure has been used to great benefit in many types of programs. I'm using it for my test script. It has been of great help, and I'll show you how I've done it.

The top tier in my tests is the test cases. These read like a high-level description of the steps of the test case, like this:

def test_create_thread():
session=login("p1", "p1")
other=get_forum_id(session, "Other")
before=get_num_threads(session, other)
create_thread(session, other, "New Thread", "Newly added thread post")
after=get_num_threads(session, other)
return before+1==after

Log in, pick a forum and check how many threads there are. Post a thread and verify that the thread count has increased by one. Easy stuff. All the details about how a thread is created in the application are abstracted away, and what's left is just the code related to the test case. So what do the get_num_threads etc. functions in the middle layer (application adaptation might be a name for it) look like?

def get_num_threads(session, forum_id):
return int(fetch_data(session, "forum?id=" + forum_id, "count(//html:tr[@class='thread'])"))

These functions tell the bottom layer which URL to fetch, and what parts of the results they are interested in. As you can see, there's an XPath query there. In some, like get_thread_id, where the text of a DOM node is too much, a regex can also be used to pick out parts of the text:

def get_thread_id(session, forum_id, title):
return fetch_data(session, "forum?id=" + forum_id, "//html:th[@class='subject']/html:a[text()='%s']/@href" % title, "id=(\\d+)")[0].group(1)

Since I'm testing a web application, the functions in the adaptation layer are of course implemented by fetching and parsing web pages. For other applications, I expect this layer to be implemented by composing and decomposing structured messages sent on some link, direct functions calls etc., but the role of the layer is the same: provide functions that correspond to functionality in the application, so that the upper layer can talk about things like reading a post instead of details about where the post is read from etc.

The bottom layer then is the workhorse functions like fetch_data and post. Here, I'll show fetch_data, which has proven itself to be very useful:

def fetch_data(session, url, xpath_query, regex=None, params={}):
conn=httplib.HTTPConnection("localhost", 8080)
encoded_params = urllib.urlencode(params)
headers = {"cookie":session}
conn.request("GET", "/myapp/" + url, encoded_params, headers)
response = conn.getresponse()
if response.status != 200:
print response.read()
raise Exception("Failed to fetch data. status=%d, reason=%s" % (response.status, response.reason))
doc=html5lib.parse(html, treebuilder="dom")
context = xpath.XPathContext()
context.namespaces['html'] = 'http://www.w3.org/1999/xhtml'
results=context.find(xpath_query, doc)
if regex:
results=map(lambda node:r.search(node.value), results)
return results

Build the request using the URL from the caller. Send it, verify that it was OK, and parse the response. Pick out the part that the caller is interested in, and return it. In the old days, parsing HTML was practically impossible. Only a handfull of companies had the resources necessary to write an HTML parser that could parse HTML as it is, not as it should. Even though I'm attempting to have my application only send out valid HTML, it may fail (and we should assume it does here: this is the test suite after all!), so having a parser that can handle anything would of course be nice.

Enter the new HTML spec, where Hixie has done an astounding job in specifying how tagsoup can be parsed in a way that is compatible with the major browsers. Since we now have a spec for parsing HTML, little parsing libs based on this keep popping up everywhere. I'm using the Python html5lib, which can produce DOM trees, which in turn support XPath queries.

That's it for the how of three-tiered tests. Now the why: In addition to having easily readable test cases for the functionality test, it also helped with the load test. Having the middle layer in place meant that the load test I'm writing has simply been a joy to write. Had the functionality testing been done using copy-pasted HTTP/HTML-related code (as it was before I started restructuring it), I'd have to start over. Now, I had almost every function I needed, with names that make sense. Just look at it!

for i in range(num_users):
username="load_user_%s_%d" % (instance, i)
signup(username, password, "%s@example.org" % username)
session=login(username, password)

for i in range(num_actions):

if random.randint(1,10)<10:
post_to_existing_thread(session, forum_id)
create_new_thread(session, forum_id)

for session in sessions:

Here, post_to_existing_thread and create_new_thread are functions similar to the test cases in the functionality test. All in all, I had to add two new functions to the adaptation layer. The rest was reused, and the load test is (at least to me) plainly readable.

So: the three-layered approach to writing tests definitely zooms. Not only should you use it for your next project: you should apply it to the tests in your current one as soon as possible!