From: Mark Cotner <mcotner@yahoo.com>
To: Bob Ippolito <bob@redivi.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: sustained update load of 1-2k/sec
Date: Fri, 19 Aug 2005 06:14:54 -0400
Message-ID: <BF2B295E.E88D%mcotner@yahoo.com> (raw)
In-Reply-To: <67788A4A-4011-49AB-B329-683FD9532661@redivi.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mcotner@yahoo.com, bob@redivi.com
Subject: Re: sustained update load of 1-2k/sec
In-Reply-To: <BF2B295E.E88D%mcotner@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox