Thread: RE: [HACKERS] Threads

:
> > Hmm, what about threads in the frontend? Anyone know if 
> libpq is thread
> > safe, and if not, how hard it might be to make it so?
> 
> It is not; the main reason why not is a brain-dead part of 
> the API that
> exposes modifiable global variables.  Check the mail list archives
> (probably psql-interfaces, but maybe -hackers) for previous 
> discussions
> with details.  Earlier this year I think, or maybe late 98.

Hmm. Really?
AFAIK, only:
pgresStatus[]

is exported, and that one is a) read-only and b) deprecated, and replaced
with a function.

No?

Otherwise, I've been darn lucky in the multithreaded stuff I have :-) (I run
with a different PGconn for each thread, and the PGresult:s are protected by
CriticalSections (this is Win32)). And if that's it, then I really need to
fix it...


//Magnus

Magnus Hagander <mha@sollentuna.net> writes:
>> It is not; the main reason why not is a brain-dead part of 
>> the API that exposes modifiable global variables.

> Hmm. Really?

PQconnectdb() is the function that's not thread-safe; if you had
multiple threads invoking PQconnectdb() in parallel you would see a
problem.  PQconndefaults() is the function that creates an API problem,
because it exposes the static variable that PQconnectdb() ought not have
had in the first place.

There might be some other problems too, but that's the main one I'm
aware of.  If we didn't mind breaking existing apps that use
PQconndefaults(), it would be straightforward to fix...

> Otherwise, I've been darn lucky in the multithreaded stuff I have :-) (I run
> with a different PGconn for each thread, and the PGresult:s are protected by
> CriticalSections (this is Win32)). And if that's it, then I really need to
> fix it...

Seems reasonable.  PGconns do need to be per-thread, or else protected
by mutexes, but you can run them in parallel.  PGresults are actually
almost read-only, and I've been wondering if they can't be made entirely
so (until destroyed of course).  Then you wouldn't need a
CriticalSection.  You might want some kind of reference-counting
mechanism for PGresults though.
        regards, tom lane

Tom Lane wrote:
> PQconnectdb() is the function that's not thread-safe; if you had
> multiple threads invoking PQconnectdb() in parallel you would see a
> problem.  PQconndefaults() is the function that creates an API problem,
> because it exposes the static variable that PQconnectdb() ought not have
> had in the first place.
> 
> There might be some other problems too, but that's the main one I'm
> aware of.  If we didn't mind breaking existing apps that use
> PQconndefaults(), it would be straightforward to fix...

Oh, this is interesting.  I've been pointer and thread chasing for the
last few hours trying to figure out why AOLserver (a multithreaded open
source web server that supports pooled database (inluding postgresql)
connections) doesn't hit this snag -- and I haven't yet found the
answer...

However, this does answer a question that I had had but had never
asked...

In any case, I have a couple of cents to throw in to the multithreaded
discussion at large:

1.)    While threads are nice for those programs that can benefit from
them, there are those tasks that are not ideally suited to threads. 
Whether postgresql could benefit or not, I don't know; it would be an
interesting excercise to wrewrite the executor to be multithreaded -- of
course, the hard part is identifying what each thread would do, etc.

2.)    A large multithreaded program, AOLserver, has just gone from a
multihost multiclient multithread model to a single host multiclient
multithread model: where AOLserver before would server as many virtual
hosts as you wished out of a single multi-threaded process, it was
determined through heavy stress-testing (after all, this server sits
behind www.aol.com, www.digitalcity.com, and others), that it was more
efficient to let the TCP/IP stack in the kernel handle address
multiplexing -- thus, the latest version requires you to start a
(multi-threaded) server process for each virtual host. The source code
for this server is a model of multithreaded server design -- see
aolserver.lcs.mit.edu for more.

3.)    Redesigning an existing single-threaded program to efficiently
utilize multithreading is non-trivial.  Highly non-trivial.  In fact,
efficiently multithreading an existing program may involve a complete
redesign of basic structures and algorithms -- it did in the case of
AOLserver (then called Naviserver), which was originally a thread-take
on the CERN httpd.

4.)    Threading PostgreSQL is going to be a massive effort -- and the
biggest part of that effort involves understanding the existing code
well enough to completely redesign the interaction of the internals --
it might be found that an efficient thread model involves multiple
layers of threads: one or more threads to parse the SQL source; one or
more threads to optimize the query, and one or more threads to execute
optimized SQL -- even while the parser is still parsing later statements
-- I realize that doesn't fit very well in the existing PostgreSQL
model.  However, the pipelined thread model could be a good fit -- for a
pooled connection or for long queries.  The most benefit could be had by
eliminating the postmaster/postgres linkage altogether, and having a
single postgres process handle multiple connections on its port in a
multiplexed-pipelined manner -- which is the model AOLserver uses.  

AOLserver works like this: when a connection request is received, a
thread is immediately dispatched to service the connection -- if a
thread in the precreated thread pool is available, it gets it, otherwise
a new thread is created, up to MAXTHREADS.

The connection thread then pulls the data necessary to service the HTTP
request (which can include dispatching a tcl interpreter thread or a
database driver thread out of the available database pools (up to
MAXPOOLS) to service dynamic content).  The data is sequentially
streamed to the connection, the connection is closed, and the thread
sleeps for a another dispatch.

Pretty simple in theory; a bear in practice.

So, hackers, are there areas of the backend itself that would benefit
from threading?  I'm sure the whole 'postmaster forking a backend'
process would benefit from threading from a pure performance point of
view, but stability could possibly suffer (although, this model is good
enough for www.aol.com....).  Can parsing/optimizing/executing be done
in a parallel/semi-parallel fashion?  Of course, one of the benefits is
going to be effective SMP utilization on architectures that support SMP
threading.  Multithreading the whole shooting match also eliminates the
need for interprocess communication via shared memory -- each connection
thread has the whole process context to work with.

The point is that it should be a full architectural redesign to properly
thread something as large as an RDBMS -- is it worth it, and, if so,
does anybody want to do it (who has enough pthreads experience to do it,
that is)?  No, I'm not volunteering -- I know enough about threads to be
dangerous, and know less about the postgres backend.  Not to mention a
great deal of hard work is going to be involved -- every single line of
code will have to be threadsafed -- not a fun prospect, IMO.

Anyone interesting in this stuff should take a look at some
well-threaded programs (such as AOLserver), and should be familiar with
some of the essential literature (such as O'Rielly's pthreads book).

Incidentally, with AOLserver's database connection pooling and
persistence, you get most of the benefits of a multithreaded backend
without the headaches of a multithreaded backend....

Lamar Owen
WGCR Internet Radio

At 07:56 PM 8/3/99 -0400, Lamar Owen wrote:
>Tom Lane wrote:
>> PQconnectdb() is the function that's not thread-safe; if you had
>> multiple threads invoking PQconnectdb() in parallel you would see a
>> problem.  PQconndefaults() is the function that creates an API problem,
>> because it exposes the static variable that PQconnectdb() ought not have
>> had in the first place.
>> 
>> There might be some other problems too, but that's the main one I'm
>> aware of.  If we didn't mind breaking existing apps that use
>> PQconndefaults(), it would be straightforward to fix...
>
>Oh, this is interesting.  I've been pointer and thread chasing for the
>last few hours trying to figure out why AOLserver (a multithreaded open
>source web server that supports pooled database (inluding postgresql)
>connections) doesn't hit this snag -- and I haven't yet found the
>answer...

AOLserver rarely does a connect once the server gets fired up and
receives traffic.  It may be that actual db connects are
guarded by semaphores or the like.  It may be that conflicts are
rare because on a busy site a connection will be made once and only
once and later lives on in the pool forever, with the handle being
allocated and released by individual .tcl scripts and .adp pages.



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, and other goodies at
http://donb.photo.net

Lamar Owen's comments brought up a thought.  Bruce has talked several
times about moving in Oracle's direction, with dedicated backends for
each database (or maybe in Ingres' direction, since they allow both
dedicated backends as well as multi-database backends).  In any case,
IFF we went that way, would it make sense to reduce the postmaster's
role to more of a traffic cop (a la Ingres' iigcn)?

Effectively, what we'd end up with is a postmaster that knows "which
backends serve which data" that would then either tell the client to
reconnect directly to the backend, or else provide a mediated
connection.

Redirection will end up costing us a whole 'nother TCP connection
build/destroy which can be disregarded for non-trivial queries, but
still may prove too much (depending upon query patterns).  On the
other hand, it would probably be easier to code and have better
throughput than funneling all data through the postmaster.  On the
gripping hand, a postmaster that mediated all transactions could also
implement QoS style controls, or throttle connections taking an unfair
share of the available bandwidth.

In any event, this could also be the start of a naming service.  It
should be relatively easy, with either method, to have the postmaster
handle connections to databases (not just tables, mind you) on other
machines. 

-- 
=====================================================================
| JAVA must have been developed in the wilds of West Virginia.      |
| After all, why else would it support only single inheritance??    |
=====================================================================
| Finger geek@cmu.edu for my public key.                            |
=====================================================================

> Redirection will end up costing us a whole 'nother TCP connection
> build/destroy which can be disregarded for non-trivial queries, but
> still may prove too much (depending upon query patterns).  On the
> other hand, it would probably be easier to code and have better
> throughput than funneling all data through the postmaster.  On the
> gripping hand, a postmaster that mediated all transactions could also
> implement QoS style controls, or throttle connections taking an unfair
> share of the available bandwidth.
> In any event, this could also be the start of a naming service.  It
> should be relatively easy, with either method, to have the postmaster
> handle connections to databases (not just tables, mind you) on other
> machines.

Starting to sound suspiciously like the Corba work I've been doing on
my day job.

We're using ACE/TAO for it's realtime and QoS features, but other
implementations are probably much lower footprint wrt installation and
use. I suppose we'd want a C implementation; the ones I've been using
are all C++...
                     - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

On Wed, 4 Aug 1999, Thomas Lockhart wrote:

> > Redirection will end up costing us a whole 'nother TCP connection
> > build/destroy which can be disregarded for non-trivial queries, but
> > still may prove too much (depending upon query patterns).  On the
> > other hand, it would probably be easier to code and have better
> > throughput than funneling all data through the postmaster.  On the
> > gripping hand, a postmaster that mediated all transactions could also
> > implement QoS style controls, or throttle connections taking an unfair
> > share of the available bandwidth.
> > In any event, this could also be the start of a naming service.  It
> > should be relatively easy, with either method, to have the postmaster
> > handle connections to databases (not just tables, mind you) on other
> > machines.
> 
> Starting to sound suspiciously like the Corba work I've been doing on
> my day job.
> 
> We're using ACE/TAO for it's realtime and QoS features, but other
> implementations are probably much lower footprint wrt installation and
> use. I suppose we'd want a C implementation; the ones I've been using
> are all C++...

KDE/KOffice uses Mico, which is also C++...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 

> KDE/KOffice uses Mico, which is also C++...

Right, it's a nice implementation from the little I've used it (I have
it installed to use the tcl binding).

Presumably we would want a C implementation to match the rest of our
environment, but I can't help thinking that a C++ one for the Corba
parts would be more natural (it maps to the Corba OO view of the
world).

ACE/TAO includes OS abstractions to allow porting to a *bunch* of
platforms, including real-time OSes. However, if you build the whole
package and include debugging symbols then you're taking up 1.2GB of
disk space for a from-source build (yes, that's GigaByte with a gee
:()

The libraries are substantially smaller, but the packaging is not very
good yet so you end up keeping the full installation around.
                  - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> Presumably we would want a C implementation to match the rest of our
> environment

In which case one might want to consider the Orbit ORB from the GNOME
project.  It's pure C, and is supposed to be quite small and fast, and
aims for full CORBA compliance---which I understand MICO and maybe TAO
don't quite achieve.

Mike.

On 4 Aug 1999, Michael Alan Dorman wrote:

> Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> > Presumably we would want a C implementation to match the rest of our
> > environment
> 
> In which case one might want to consider the Orbit ORB from the GNOME
> project.  It's pure C, and is supposed to be quite small and fast, and
> aims for full CORBA compliance---which I understand MICO and maybe TAO
> don't quite achieve.

Actually, unless Orbit has recently changed, MICO is more compliant then
it is...last time we all looked into it, at least, that was the case...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org