DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Received:User-Agent:Date:Subject:From:To:CC:Message-ID:In-Reply-To:Mime-version:Content-type:Content-transfer-encoding;
	b=lyv/h+YDXeUfE66KEq88++EvoTA4z3Ep96c+jUOoujdvTuJXRREDskLQyFb9wotC68frqppIejCjs8PS9xDdB0HzLgqfxm0bAVo3vIvjuGcoLG21yKo6Uy/MJgu3WL7au3faEefHlmqDSGLf+k7uh/efgQltKn2jdRmCTa6hZwk=
	;
User-Agent: Microsoft-Entourage/11.1.0.040913
Date: Fri, 19 Aug 2005 06:14:54 -0400
Subject: Re: sustained update load of 1-2k/sec
From: Mark Cotner <mcotner@yahoo.com>
To: Bob Ippolito <bob@redivi.com>
Cc: <pgsql-performance@postgresql.org>
Message-ID: <BF2B295E.E88D%mcotner@yahoo.com>
In-Reply-To: <67788A4A-4011-49AB-B329-683FD9532661@redivi.com>
Mime-version: 1.0
Content-type: text/plain;
	charset="US-ASCII"
Content-transfer-encoding: 7bit

Excellent feedback.  Thank you.  Please do keep in mind I'm storing the
results of SNMP queries.  The majority of the time each thread is in a wait
state, listening on a UDP port for return packet.  The number of threads is
high because in order to sustain poll speed I need to minimize the impact of
timeouts and all this waiting for return packets.

I had intended to have a fallback plan which would build a thread safe queue
for db stuffs, but the application isn't currently architected that way.
It's not completely built yet so now is the time for change.  I hadn't
thought of building up a batch of queries and creating a transaction from
them.

I've been looking into memcached as a persistent object store as well and
hadn't seen the reactor pattern yet.  Still trying to get my puny brain
around that one.

Again, thanks for the help.

'njoy,
Mark


On 8/19/05 5:09 AM, "Bob Ippolito" <bob@redivi.com> wrote:

> 
> On Aug 18, 2005, at 10:24 PM, Mark Cotner wrote:
> 
>> I'm currently working on an application that will poll
>> thousands of cable modems per minute and I would like
>> to use PostgreSQL to maintain state between polls of
>> each device.  This requires a very heavy amount of
>> updates in place on a reasonably large table(100k-500k
>> rows, ~7 columns mostly integers/bigint).  Each row
>> will be refreshed every 15 minutes, or at least that's
>> how fast I can poll via SNMP.  I hope I can tune the
>> DB to keep up.
>> 
>> The app is threaded and will likely have well over 100
>> concurrent db connections.  Temp tables for storage
>> aren't a preferred option since this is designed to be
>> a shared nothing approach and I will likely have
>> several polling processes.
> 
> Somewhat OT, but..
> 
> The easiest way to speed that up is to use less threads.  You're
> adding a whole TON of overhead with that many threads that you just
> don't want or need.  You should probably be using something event-
> driven to solve this problem, with just a few database threads to
> store all that state.  Less is definitely more in this case.  See
> <http://www.kegel.com/c10k.html> (and there's plenty of other
> literature out there saying that event driven is an extremely good
> way to do this sort of thing).
> 
> Here are some frameworks to look at for this kind of network code:
> (Python) Twisted - <http://twistedmatrix.com/>
> (Perl) POE - <http://poe.perl.org/>
> (Java) java.nio (not familiar enough with the Java thing to know
> whether or not there's a high-level wrapper)
> (C++) ACE - <http://www.cs.wustl.edu/~schmidt/ACE.html>
> (Ruby) IO::Reactor - <http://www.deveiate.org/code/IO-Reactor.html>
> (C) libevent - <http://monkey.org/~provos/libevent/>
> 
> .. and of course, you have select/poll/kqueue/WaitNextEvent/whatever
> that you could use directly, if you wanted to roll your own solution,
> but don't do that.
> 
> If you don't want to optimize the whole application, I'd at least
> just push the DB operations down to a very small number of
> connections (*one* might even be optimal!), waiting on some kind of
> thread-safe queue for updates from the rest of the system.  This way
> you can easily batch those updates into transactions and you won't be
> putting so much unnecessary synchronization overhead into your
> application and the database.
> 
> Generally, once you have more worker threads (or processes) than
> CPUs, you're going to get diminishing returns in a bad way, assuming
> those threads are making good use of their time.
> 
> -bob
>