Date: Tue, 13 Jan 2004 17:10:42 -0500 From: David Hill <davidh@wmis.net> To: Sean Chittenden <sean@chittenden.org> Cc: performance@freebsd.org Subject: Re: postgresql on freebsd - lots of connections Message-ID: <20040113221042.GA3616@phobia.ms> In-Reply-To: <81F83F77-4612-11D8-A8F0-000A95C705DC@chittenden.org> References: <20040113143650.GA1424@phobia.ms> <81F83F77-4612-11D8-A8F0-000A95C705DC@chittenden.org>
next in thread | previous in thread | raw e-mail | index | archive | help
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@FreeBSD.org or performance@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@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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20040113221042.GA3616>