Re: [ADMIN] When postgres will be faster? - Mailing list pgsql-hackers

On Tue, 30 Nov 1999 sk.list@comset.net wrote:

> Date: Tue, 30 Nov 1999 18:11:36 +0300 (MSK)
> From: sk.list@comset.net
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: pgsql-hackers@postgreSQL.org, pgsql-admin@postgreSQL.org,
>     Marcin Mazurek - Multinet SA - Poznan <m.mazurek@multinet.pl>
> Subject: Re: [ADMIN] When postgres will be faster?
> 
> Hi!
> 
> On 29-Nov-99 Oleg Bartunov wrote:
> > I'm not concern very much about speed of Postgres but mostly
> > about its connection schema. Every new connect to database postgres
> > forks another children. It's impossible to work with different
> 
> fork and fork/exec are some different. postmaster forks and execute backend
> binary.
> 
> > databases. On my production site I work with persistent connections
> > between http (mod_perl) <-> postgres and quite satisfies with efficiency -
> > I have 20 httpd running and 20 db backends accordingly. 
> > This requires some memory, but I could live. Now other developers
> 
> I have >100 connections in peak load. Not all of them use postgres. If I use
> pconnect I lost my RAM ;-)
> 
> > want to use postgres as a db backend in their Web applications and
> > also want to have persistence to some another databases. 
> > If you have N databases and M httpd servers, you will end with
> > N*M DB backends. This is too much and I'm afraid my solution
> 
> Why? Why N*M? After disconnect the persistent connection backend should not
> finish but next connection opens other bata base? Or i misunderstood?

persistent connections are never disconnected during httpd children's life,
that's what I need for performance reason. every httpd children holds
their own connection to specific database and there are no method 
(well, AFAIK) to share connection between childrens (see discussion in
modperl mailing list for today and yesterday). If you need to work with 
another database you have to open new connection, because postgres doesn't 
works with several database through one connection. Latest version of Mysql
could do this and you could explicitly specify database name  "select something from database.table"
Simple experiment with psql like 1. psql db12. look at process list - you'll see something like:     19714  ?  S
0:00/usr/local/pgsql/bin/postgres localhost megera db1 idle3. \c db24. again look at process list:     19718  ?  S
0:00/usr/local/pgsql/bin/postgres localhost megera db2 idle new process is forked.
 
I dont' know backend internals, probably it's possible using libpq interface
to switch between databases through one connection, but I suspect it could be
difficult to 'hide' so nice feature :-)

> 
> > I don't know if it's possible to have a pool of db childrens,
> > which connected to, say, template1 database and children could
> > switch to requested database on demand. This would require some
> > modification of DBD driver of course, but I think it's not hard.
> 
> Hmmm... There is 2 ways to support pool.
> 1. FORK only.
> Postmaster and postgres are same binary. postmaster accept connection and
> forked. Parent creates structure with child pid, descriptors etc... Child
> becomes backend. When child finish the request it send signal (smem,fifo etc)
> to parent. Parent set IDLE flag to child structure. When next connection
> accepted parent seek through list of child to find first idle one. parent clear
> IDLE flag and fd_dup file descriptors to backend's. Child structure contain
> call counter and time stamp of start and last call time. If call counter exceeds
> N or time exceeds T all descriptors becomes closed. Child catch SIGPIPE on
> closed descriptors and finish. Parent scans list of  structures and check time
> stamps to stop idle backends or start new one (to have pool of idle backends).
> 
> 2. Fork/exec.
> I dont know. But it possible too. Same like previous.
> 
> So, if backend works with one database only and cannot reconnect - add
> 'database' field to child structure described above. Or add keywords 
> CONNECT/DISCONNECT to language. Hmm... I was sure backend can server more then
> 1 database sequentially.
> 

I suggest postgres experts  comment this topic. We really need to work
with different databases using one connection. Postgres is rather good
scalable DB engine and IMO it's worth to have such feature like
DB pooling. Once postgres support db pooling it would be possible
to develope/modify various interfaces to work with httpd.
I'm using mod_perl, apache, perl, DBI, ApacheDBI and now looking 
for CORBA :-)

regards,
    Oleg

> SKiller
> --------------------------
> Sergei Keler
> WebMaster of "ComSet"
> E-Mail: skiller@comset.net
> http://www.comset.net
> --------------------------
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83