postgresql on freebsd - lots of connections

[David Hill]

On Tue, Jan 13, 2004 at 01:50:31PM -0800, Sean Chittenden wrote:
> [ Howdy.  My network's reverse DNS is fubar'ed at the moment, can you 
> CC either ]
> [ database at FreeBSD.org or performance at FreeBSD.org when you reply?  I 
> think ]
> [ others might like to either listen, or contribute to this discussion. 
> ]
> 
> >Sean -
> >If i can borrow your brain for a few :)
> >
> >I am running postfix, postgresql, and courier together.  postgresql 
> >needs to be
> >fast for reading.
> 
> SELECT happy, eh?
> 
> >I dont need to be able to support huge queriesor results..  they are 
> >all "SELECT blah FROM table WHERE domain='domain.com'"
> >The tables are unique'd index'd.
> 
> Good to hear, UNIQUE INDEXes are faster than non-unique INDEXes.  You 
> don't have control over the SQL such that you can make the various 
> programs use persistent connections and/or prepared statements, do you?
> 
> >I am running postgresql with both postfix and courier querying it for 
> >aliases, users, and relay_domains... mail is not stored in SQL.
> 
> The biggest factor in speeding things up will be persistent connections.
> 
> >There will be about 4 postfix servers and 2 or 3 courier servers (we 
> >have a large email userbase) accessing the postgresql server when 
> >finished.
> >
> >What are some good FreeBSD kernel tuning options and postgresql tuning 
> >options to support a large number of connections returning very small 
> >results?
> 
> Hrm.... well, as stated, anything you can do to reduce the connection 
> startup time is going to be key.  If you want to quasi-hack a custom 
> version of PostgreSQL and connections aren't being cached, you'd 
> probably want to have the _client_ do something like:
> 
> int optval = 1;
> setsockopt(s, SOL_TCP, TCP_NOPUSH, &optval, sizeof(optval));
> 
> Actually, here's the patch to make this happen (also at 
> http://people.FreeBSD.org/~seanc/patches/#pgsql-tcp_nopush).  I haven't 
> tested the performance impact of this and I don't know if this will 
> impact interactive sessions or not, but, I'd hope that it'll speed 
> things up and reduce the packet flow since now the server shouldn't 
> flush the socket after every row.. which could cause a startup delay, 
> but when it comes to sending data and closing the connection, it should 
> be a win.  *shrug*  Someone with more TTCP foo than me may be able to 
> predict better than I.  With HTTP, small requests can be handled in 
> three packets, so who knows.  I'd be interested in any impact you 
> notice with this.  So let's see... what else can be done.
> 


> 
> 
> Setting net.inet.tcp.delayed_ack=1 would be a good idea probably, 
> reduces the number of TCP packets.  Beyond that, there's not a whole 
> lot that you can do other than possibly preloading plpgsql.so if you 
> make use of that.  Other things that you may want to _test_ heavily, 
> would be futzing around with the block size.  Only the -devel port has 
> this option, but you may find that SELECTs will be faster at 4K than 8K 
> or 16K.  It's hard to say though... if you increase the caching and are 
> able to keep the entire database in the OS's cache (you may want to 
> increase the amount of kernel space available for that, NBUF default * 
> 2 && and BKVASIZE default * 4), it may be to your advantage to 
> _increase_ the block size to something larger like 16K or even possibly 
> 32K, though be sure to change your postgresql.conf settings when you 
> tweak the page size).
> 
> ... and that's about all I can think of now.  Let me know how your 
> testing goes though as this is something that I'm going to need to 
> spend some time working on later this month (*smells libevent + 
> PostgreSQL coming real soon*).  -sc
> 
> -- 
> Sean Chittenden
> seanc at FreeBSD.org
> http://people.FreeBSD.org/~seanc/

I have Postfix using proxymap to (share one open table among multiple processes), which helped a lot.  My network memory buffers are fine, hardly being used. I pulled some of your FreeBSD kernel options from the postgresql performance mail-list to get mine handling about 128 connections.  The most I have seen postgresql need to open so far is 45, so I might be okay.

I have 2 postfix servers (2.4Ghz celeron, 512MB ram, 40GB IDE, 3com NIC's) accessing postgresql (same hardware config).  Load is about 0.7, a lot of inact/free ram, and network bugs are at a minimum.  

# freebsd kernel options
options         SHMMAXPGS=65536
options         SEMMNI=40
options         SEMMNS=240
options         SEMUME=40
options         SEMMNU=120

# postgresql.conf options
max_connections = 128
shared_buffers = 2048
effective_cache_size = (sysctl -n vfs.hibufspace / 8192)

- David