postgresql on freebsd - lots of connections
davidh at wmis.net
Tue Jan 13 14:12:40 PST 2004
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
> >What are some good FreeBSD kernel tuning options and postgresql tuning
> >options to support a large number of connections returning very small
> 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
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
# postgresql.conf options
max_connections = 128
shared_buffers = 2048
effective_cache_size = (sysctl -n vfs.hibufspace / 8192)
More information about the freebsd-performance