2011-02-19

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

kern.maxprocperuid=512
kern.maxproc=2048

to

kern.maxprocperuid=2048
kern.maxproc=4096
kern.maxfiles=40960
kern.maxfilesperproc=2048

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!

7 comments:

  1. Cool, but why would you need that many connections? Is it related to caching connections for the "each user should get their own authenticated connection"-design?

    ReplyDelete
  2. Yes. But since PostgreSQL has so much problems with it (and Firebird would need some hackery to do more than that), I'm investigating whether I can disconnect connections and then use Kerberos to reconnect when necessary without storing the passwords. In theory it should not be a problem, but Jaas and GSSAPI are flexible in every single way except for how I want to bend them...

    ReplyDelete
  3. Like you say: In _theory_ Jaas/Kerberos is the "right thing". I forsee a great new blog post about your findings :-)

    ReplyDelete
  4. if i install postgres from macports where do i locate file postgresql.conf ? thank's :)

    ReplyDelete
  5. I don't really know, but the port script at https://trac.macports.org/browser/trunk/dports/databases/postgresql90/Portfile seems to imply it will be under /opt/local/share/postgresql90 (assiuming that you have your macports in /opt/local).
    Have you tried searching for it in Spotlight?

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Any way to do this on windows?

    ReplyDelete