Thread: any experience with multithreaded pg apps?
I'm looking for feedback from anybody who has used pg in a multi-threaded program, particularly one in which several threads each open a database connection. It's documented to work in that scenario, but I'm interested in anybody who can share some real-world with that. Many TIA! Mark -- Mark Harrison Pixar Animation Studios
Mark Harrison wrote:
> I'm looking for feedback from anybody who has used pg in a
> multi-threaded program, particularly one in which several
> threads each open a database connection.
>
> It's documented to work in that scenario, but I'm interested
> in anybody who can share some real-world with that.
I've done some real-world work like this.
The application is a SOAP server written in C using the gsoap library to
query a PostgreSQL database. The nature of the server is that individual
queries are very fast (generally less than .1 sec) and the possibility of
multiple queries in rapid succession is likely. The possibility of multiple
simultaneious queries also exists, but is unlikely.
I built the app thusly:
The bugger fires up and opens a connection to Postgres, and a listening
socket. A thread mutex is used to get exclusive access to the Postgres
database connection. When a connection arrives, an attempt is made to
lock the mutex, if it succeeds, a thread is fired off to handle that
connection and the loop returns to wait for additional connections. When
the handling of the connection is complete, the mutex is released and the
thread ends.
If a new connection arrives and the mutex is already held by a previous
thread, a new connection is established and a flag is set to alert the
thread that it should close the connection to Postgres apon completion.
When the thread completes, it closes the connection instead of releasing
the mutex.
The results meet pretty much what we wanted:
1) Processing of a single connection is _very_ fast, since the connection
To Postgres is already established, and the query and processing of the
results occur very quickly.
2) The daemon _can_ handle multiple simultaneous connections. Performance
degrades a bit (in my tests, it's about half as fast when it has to
establish a connection prior to the query). Since the nature of the
application lends itself more to rapid sequential queries from a single
host than to multiple simultaneous queries, this is acceptable for us.
We've considered moving this to a pre-threaded model, but so far there hasn't
been a need, and there is other work to do :) I do have another SOAP app that
does pre-forking and pre-establishes database connections, but that's not
threaded so it doesn't really apply to your question.
Reliability and performance have been good in this application. Haven't had a
lick of trouble with Postgres or the client libraries. We developed this
initially on 7.3, and it now runs on 7.4.
Hope this helps.
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
On May 7, 2004, at 12:43 PM, Mark Harrison wrote: > I'm looking for feedback from anybody who has used pg in a > multi-threaded program, particularly one in which several > threads each open a database connection. > > It's documented to work in that scenario, but I'm interested > in anybody who can share some real-world with that. > Works great over here. Our app sits around doing 50-150 requests/sec constantly and spends most of its time sitting there with a blank look on its face. Just make sure you take the normal precautions you need to take with threaded apps (no strtok, etc. ) -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
Mark Harrison wrote: > I'm looking for feedback from anybody who has used pg in a > multi-threaded program, particularly one in which several > threads each open a database connection. > > It's documented to work in that scenario, but I'm interested > in anybody who can share some real-world with that. We've wrapped the libpq commands inside some code that makes use of a glib thread pool. On startup, we have a bunch of threads make persistant connections to the database. The rest of our app simply has to push querries onto this pool, and the first available thread will make use of its persistant connection to return the results as fast as possible. If all threads are busy working on queries, our wrapper code takes care of the queuing for us. It's a scaleable solution that we can make as complex as we want. (For instance, if the database crashes, each thread should be able to re-establish its connection.) It works pretty well. The only drawback is that the application can't make use of the threadpool for multi-query transactions, but we've wrapped all of those in stored procs anyway.
Mark Harrison wrote: > I'm looking for feedback from anybody who has used pg in a > multi-threaded program, particularly one in which several > threads each open a database connection. > > It's documented to work in that scenario, but I'm interested > in anybody who can share some real-world with that. It works fine so long as synchronization is guaranteed. We use CORBA (omniORB) which presents problems in the face of a traditional factory pattern where each client gets a server-side, multi-threaded object. For example, we want the following characteristics: 1) Single app server process - muli-threaded access 2) Single-threaded access to each session object 3) Garbage collection of server threads 4) Garbage collection of server objects 5) Single transactions across multiple RPCs #1 comes automatically with CORBA unless you use the SINGLE_THREAD_POLICY for the POA in which the object is created. #2 does not come automatically with CORBA unless you use the SINGLE_THREAD_POLICY and create each session object in its own POA, which is frowned upon. Therefore, all exposed API calls must be wrapped in code which synchronizes on a thread-lock attribute of the session object. #3 happens automatically, depending upon ORB configuration. This means that you cannot simply use TLS for access to the libpq database handle. #4 requires manual coding. #5 requires some voodoo as well, since a multi-threaded client could interleave synchronized RPC calls that screw up another thread's transaction. Of course, if you're just talking about a traditional multi-threaded application (non-CORBA), then storing the libpq handle in TLS would seem like the best solution to me, so long as thread lifetime = database session lifetime. The other annoyance is that the ability to debug multi-threaded applications has progressed slowly over the years: a. older versions of g++ had problems throwing exceptions in multi-threaded applications b. older gdb versions couldn't debug multi-threaded applications c. kernels couldn't dump the core image of multi-threaded applications d. later kernels dumped the core image of the thread which caused the violation, not all threads Depending upon compiler and kernel, all of the above may have been addressed, either partially or completely. I know newer linux kernels can dump the image of each thread when the process dies, but as of RedHat 8, d. was the behavior. HTH, Mike Mascari > Many TIA! > Mark
Mark Harrison wrote: > I'm looking for feedback from anybody who has used pg in a > multi-threaded program, particularly one in which several > threads each open a database connection. > > It's documented to work in that scenario, but I'm interested > in anybody who can share some real-world with that. Take a look at Slony-I. Multithreaded client applications are a headache for themself, since the PostgreSQL client libraries are not built threadsafe by default, cannot be built threadsafe on all platforms, and even if we thought about some platforms being threadsafe buildable, some underlying authentication libs are sometimes not. Someone testing Slony was lately bitten by a non-threadsafe libkrb5 even if he does not use kerberos authentication at all. Having libpq linked against it was enough to coredump slon. It's more or less a nightmare, and we definitely need to cleanup that mess anytime soon. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #