Thread: pg_pconnect - ??
Hi all, I am under the assumtion that, when I do a pg_pconnect instead of a pg_connect, the connection to the db I have established, can be used by another PHP procedure. Somehow, I don't see this working the way I expect it. When I initially connect with pconnect, I see a postgres session staring up and everything works. The postgres process stays active but when I pconnect again, a second process is started. I was expecting that the same process from the first pconnect will be used. At the end, I land up with a whole bunch of postgres client sessions hanging around in the system, doing who knows what. What am I missing (doing wrong) here? Postgres = 7.1.3, OS = Linux 2.4.9 (RedHat 7.2), PHP = 4.0.6, Apache = 1.3.22, memory = plenty (768 MB) Best regards, Chris -- Chris Ruprecht Network grunt and bit pusher extraordinaíre _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
I just use pg_connect, with the same connection string for persistant connections and have no problems. But that is not a answer ;-) RH 7.2, PHP 4.0.6, pg 7.1.3 On Mon, 28 Jan 2002, Chris Ruprecht wrote: > Hi all, > > I am under the assumtion that, when I do a pg_pconnect instead of a > pg_connect, the connection to the db I have established, can be used > by another PHP procedure. Somehow, I don't see this working the way I > expect it. > When I initially connect with pconnect, I see a postgres session > staring up and everything works. The postgres process stays active > but when I pconnect again, a second process is started. I was > expecting that the same process from the first pconnect will be used. > At the end, I land up with a whole bunch of postgres client sessions > hanging around in the system, doing who knows what. > > What am I missing (doing wrong) here? > Postgres = 7.1.3, OS = Linux 2.4.9 (RedHat 7.2), PHP = 4.0.6, Apache > = 1.3.22, memory = plenty (768 MB) > > Best regards, > Chris > -- > Chris Ruprecht > Network grunt and bit pusher extraordinaíre > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- > Virus scanned by edNET. > -- Chris Thompson lightershade t: +44 131 466 7003 d: +44 131 625 5603 -- This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. Any offers or quotation of service are subject to formal specification. Errors and omissions excepted. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of edNET or lightershade ltd. Finally, the recipient should check this email and any attachments for the presence of viruses. edNET and lightershade ltd accepts no liability for any damage caused by any virus transmitted by this email. -- -- Virus scanned by edNET.
The connection will only be reused if the *same* apache child process handles the request. You should expect to see many postgres client connections. A) Each database/user connection combination. B) Each apache child process Multiply A*B to get max number of concurrent connections. If A*B can go over postgres connection limit, then you might start getting connection refused messages. If your postgres database is on the same server as you webserver, there is neglible gains for using pconnect over connect. Frank At 09:56 AM 1/28/02 -0600, Chris Ruprecht wrote: >Hi all, > >I am under the assumtion that, when I do a pg_pconnect instead of a >pg_connect, the connection to the db I have established, can be used >by another PHP procedure. Somehow, I don't see this working the way I >expect it. >When I initially connect with pconnect, I see a postgres session >staring up and everything works. The postgres process stays active >but when I pconnect again, a second process is started. I was >expecting that the same process from the first pconnect will be used. >At the end, I land up with a whole bunch of postgres client sessions >hanging around in the system, doing who knows what. > >What am I missing (doing wrong) here? >Postgres = 7.1.3, OS = Linux 2.4.9 (RedHat 7.2), PHP = 4.0.6, Apache >= 1.3.22, memory = plenty (768 MB) > >Best regards, >Chris >-- >Chris Ruprecht >Network grunt and bit pusher extraordinaíre > >_________________________________________________________ >Do You Yahoo!? >Get your free @yahoo.com address at http://mail.yahoo.com > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Very true -- persistent connections are only going to give you a performance gain of the time it takes to start a new PG process. That gain can matter and matter a lot in certain situations but generally only with high-traffic servers. Be careful, it's easy to run out of resources especially if you have your PG processes set to use a good amount of sort/shared memory (and you really should increase it from the default settings to get much better performance) Good luck! -Mitch ----- Original Message ----- From: "Frank Bax" <fbax@sympatico.ca> To: "Chris Ruprecht" <chrup999@yahoo.com> Cc: "PostGreSQL PHP Group" <pgsql-php@postgresql.org> Sent: Monday, January 28, 2002 12:19 PM Subject: Re: [PHP] pg_pconnect - ?? > The connection will only be reused if the *same* apache child process > handles the request. You should expect to see many postgres client > connections. > A) Each database/user connection combination. > B) Each apache child process > > Multiply A*B to get max number of concurrent connections. If A*B can go > over postgres connection limit, then you might start getting connection > refused messages. > > If your postgres database is on the same server as you webserver, there is > neglible gains for using pconnect over connect. > > Frank > > > At 09:56 AM 1/28/02 -0600, Chris Ruprecht wrote: > >Hi all, > > > >I am under the assumtion that, when I do a pg_pconnect instead of a > >pg_connect, the connection to the db I have established, can be used > >by another PHP procedure. Somehow, I don't see this working the way I > >expect it. > >When I initially connect with pconnect, I see a postgres session > >staring up and everything works. The postgres process stays active > >but when I pconnect again, a second process is started. I was > >expecting that the same process from the first pconnect will be used. > >At the end, I land up with a whole bunch of postgres client sessions > >hanging around in the system, doing who knows what. > > > >What am I missing (doing wrong) here? > >Postgres = 7.1.3, OS = Linux 2.4.9 (RedHat 7.2), PHP = 4.0.6, Apache > >= 1.3.22, memory = plenty (768 MB) > > > >Best regards, > >Chris > >-- > >Chris Ruprecht > >Network grunt and bit pusher extraordinaíre > > > >_________________________________________________________ > >Do You Yahoo!? > >Get your free @yahoo.com address at http://mail.yahoo.com > > > > > >---------------------------(end of broadcast)--------------------------- > >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Ok, so from what I see on here, pg_pconnect doesn't have any advantages over pg_connect in my situation. I have the web server running on the same machine as the database server and yes, I have allocated 16384 shared buffers (at 8 KB each, this is 128 MB of shared memory). I have not noticed any connect overhead when opening a web page which connects to the database (99% of them do), the connection is instantaneously, so I guess, I don't need to do anything here. I was under the impression, that a persistent connection would open one and only one process which then will be used all the time without creating more child processes which keep lingering about. I guess, I was wrong here ... Best regards, Chris -- Chris Ruprecht Network grunt and bit pusher extraordinaíre _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Aside from Apache children, pconnect doesn't really have any advantage over connect... straight from the php.net doc... You can use $GLOBALS to pass connections instead of trying to use the pconnect string over and over. If you write a quick function _one_ function that checks to see if a connection is made, and uses that resource - or makes a new one, then all you should have to do is include() that function in each one of your pages.... Do you need an example? Send us one of yours, and we'll show you (at least I will). I'd like to see what exactly you're using the pconnect for. Regards, On Mon, 28 Jan 2002, Chris Ruprecht wrote: > Hi all, > > I am under the assumtion that, when I do a pg_pconnect instead of a > pg_connect, the connection to the db I have established, can be used > by another PHP procedure. Somehow, I don't see this working the way I > expect it. > When I initially connect with pconnect, I see a postgres session > staring up and everything works. The postgres process stays active > but when I pconnect again, a second process is started. I was > expecting that the same process from the first pconnect will be used. > At the end, I land up with a whole bunch of postgres client sessions > hanging around in the system, doing who knows what. > > What am I missing (doing wrong) here? > Postgres = 7.1.3, OS = Linux 2.4.9 (RedHat 7.2), PHP = 4.0.6, Apache > = 1.3.22, memory = plenty (768 MB) > > Best regards, > Chris > -- > Chris Ruprecht > Network grunt and bit pusher extraordina�re > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > Chadwick Rolfs - cmr@gis.net Cleveland State University - Student Music Major - The Holden Arboretum Volunteer Computer Programmer - Student Employee --*I finally found powdered water; I just can't figure out what to add to it*--
Sorry, I guess you can disregard most of my reply:) But a functiont that checks for connections and makes on only if one isn't already there is still good idea, if you are making many queries in a small amount of time. Are you making many queries at once, or the same one many times, or different ones quickly? On Mon, 28 Jan 2002, Chris Ruprecht wrote: > Ok, so from what I see on here, pg_pconnect doesn't have any > advantages over pg_connect in my situation. I have the web server > running on the same machine as the database server and yes, I have > allocated 16384 shared buffers (at 8 KB each, this is 128 MB of > shared memory). I have not noticed any connect overhead when opening > a web page which connects to the database (99% of them do), the > connection is instantaneously, so I guess, I don't need to do > anything here. > I was under the impression, that a persistent connection would open > one and only one process which then will be used all the time without > creating more child processes which keep lingering about. I guess, I > was wrong here ... > > Best regards, > Chris > -- > Chris Ruprecht > Network grunt and bit pusher extraordina�re > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > Chadwick Rolfs - cmr@gis.net Cleveland State University - Student Music Major - The Holden Arboretum Volunteer Computer Programmer - Student Employee --*I finally found powdered water; I just can't figure out what to add to it*--
> Ok, so from what I see on here, pg_pconnect doesn't have any > advantages over pg_connect in my situation. I have the web server > running on the same machine as the database server and yes, I have > allocated 16384 shared buffers (at 8 KB each, this is 128 MB of > shared memory). I have not noticed any connect overhead when opening > a web page which connects to the database (99% of them do), the > connection is instantaneously, so I guess, I don't need to do > anything here. Well it does take time to startup another pg process, it's just a tiny amount of time with your current utilization. Sometimes a few miliseconds can add up (say if you were spawning many processes per second).. Still, you're right, it doesn't look like persistant connections are going to offer you much increase in performance so I wouldn't bother.. Note that I've *alway* had problems getting them to even work correctly with PHP -- supposedly things were fixed in alter 4.0.x releases but I've not tested it much to say for certain. > I was under the impression, that a persistent connection would open > one and only one process which then will be used all the time without > creating more child processes which keep lingering about. I guess, I > was wrong here ... That's a pretty common misunderstanding about what persistent conenctions are, no worries.. -Mitchell
I usually have a page which connects, does some processing and disconnects - very simple stuff. The user runs a number of queries when she creates some invoicing, but it's always a matter of "1. load page where to enter parameters; 2. execute the script which creates the bill from the parameters; 3. return to step 1". Each invoice is created individually - the user only has a few clients (big amounts per bill, few bills). The most traffic the user creates is when they change their rate table, they might add 20 to 50 rates at per week. I could run that stuff of an old 100 MHz Pentium with 16 MB RAM, if I had to and they would probably not see and performance issues. However, some of the bills they have process 500'000 line items at a time, this is where I need the horsepower - but at that stage, the connection is already made. The query runs across 4 tables and sometimes takes up to 4 minutes. Best regards, Chris At 16:07 -0500 01/28/2002, Chadwick Rolfs wrote: >Sorry, I guess you can disregard most of my reply:) > >But a functiont that checks for connections and makes on only if one isn't >already there is still good idea, if you are making many queries in a >small amount of time. > >Are you making many queries at once, or the same one many times, or >different ones quickly? > >On Mon, 28 Jan 2002, Chris Ruprecht wrote: > >> Ok, so from what I see on here, pg_pconnect doesn't have any >> advantages over pg_connect in my situation. I have the web server >> running on the same machine as the database server and yes, I have >> allocated 16384 shared buffers (at 8 KB each, this is 128 MB of >> shared memory). I have not noticed any connect overhead when opening >> a web page which connects to the database (99% of them do), the >> connection is instantaneously, so I guess, I don't need to do >> anything here. >> I was under the impression, that a persistent connection would open >> one and only one process which then will be used all the time without >> creating more child processes which keep lingering about. I guess, I >> was wrong here ... >> >> Best regards, >> Chris >> -- >> Chris Ruprecht >> Network grunt and bit pusher extraordinaÌre >> >> _________________________________________________________ >> Do You Yahoo!? >> Get your free @yahoo.com address at http://mail.yahoo.com >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >> > >Chadwick Rolfs - cmr@gis.net >Cleveland State University - Student >Music Major - The Holden Arboretum Volunteer >Computer Programmer - Student Employee >--*I finally found powdered water; >I just can't figure out what to add to it*-- -- Chris Ruprecht Network grunt and bit pusher extraordinaíre _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
> The most traffic the user creates is when they change their rate > table, they might add 20 to 50 rates at per week. I could run that > stuff of an old 100 MHz Pentium with 16 MB RAM, if I had to and they > would probably not see and performance issues. However, some of the > bills they have process 500'000 line items at a time, this is where I > need the horsepower - but at that stage, the connection is already > made. The query runs across 4 tables and sometimes takes up to 4 > minutes. If you post schema and queries then perhaps we could help you get that time down a bit? Pardon the questions but you're indexing, VACUUM ANALYZE ' ing and using EXPLAIN to see what your query plans are -- right? I guess if we're going to open this can of worms we'd better take the conversation to pg-general though... Good luck! -Mitch
<Grin> - yes, I do the indexing and all that good stuff - even casting the variables on my select statements. But no matter what you do, it takes time to go through half a million records and add them up in a certain way (sub totals when a value changes). I made sure that I have the right indexes and that they are selected, when I do the 'select'. I'm not new to databases & sql and all that stuff, just new to PHP. I also don't allow the user to enter SQL statements directly or let them do dynamic queries where they can specify their own 'where' or 'order by' or stuff like that. At 15:12 -0700 01/28/2002, Mitch Vincent wrote: > > The most traffic the user creates is when they change their rate >> table, they might add 20 to 50 rates at per week. I could run that >> stuff of an old 100 MHz Pentium with 16 MB RAM, if I had to and they >> would probably not see and performance issues. However, some of the >> bills they have process 500'000 line items at a time, this is where I >> need the horsepower - but at that stage, the connection is already >> made. The query runs across 4 tables and sometimes takes up to 4 >> minutes. > >If you post schema and queries then perhaps we could help you get that time >down a bit? Pardon the questions but you're indexing, VACUUM ANALYZE ' ing >and using EXPLAIN to see what your query plans are -- right? > >I guess if we're going to open this can of worms we'd better take the >conversation to pg-general though... Uhmm - no need :). Best regards, Chris -- Chris Ruprecht Network grunt and bit pusher extraordinaíre _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
*smile* -- you'd be crazy surprised at how many people say "Indexes? What are those?" and "Oh, you mean I *should* VACUUM ANALYZE ?".. It's become second nature to assume the worst! Sorry! Glad to know you have things under control -- good luck :-) -Mitchell ----- Original Message ----- From: "Chris Ruprecht" <chrup999@yahoo.com> To: "Mitch Vincent" <mitch@doot.org> Cc: "PostGreSQL PHP Group" <pgsql-php@postgresql.org> Sent: Monday, January 28, 2002 4:13 PM Subject: Re: [PHP] pg_pconnect - ?? <Grin> - yes, I do the indexing and all that good stuff - even casting the variables on my select statements. But no matter what you do, it takes time to go through half a million records and add them up in a certain way (sub totals when a value changes). I made sure that I have the right indexes and that they are selected, when I do the 'select'. I'm not new to databases & sql and all that stuff, just new to PHP. I also don't allow the user to enter SQL statements directly or let them do dynamic queries where they can specify their own 'where' or 'order by' or stuff like that. At 15:12 -0700 01/28/2002, Mitch Vincent wrote: > > The most traffic the user creates is when they change their rate >> table, they might add 20 to 50 rates at per week. I could run that >> stuff of an old 100 MHz Pentium with 16 MB RAM, if I had to and they >> would probably not see and performance issues. However, some of the >> bills they have process 500'000 line items at a time, this is where I >> need the horsepower - but at that stage, the connection is already >> made. The query runs across 4 tables and sometimes takes up to 4 >> minutes. > >If you post schema and queries then perhaps we could help you get that time >down a bit? Pardon the questions but you're indexing, VACUUM ANALYZE ' ing >and using EXPLAIN to see what your query plans are -- right? > >I guess if we're going to open this can of worms we'd better take the >conversation to pg-general though... Uhmm - no need :). Best regards, Chris -- Chris Ruprecht Network grunt and bit pusher extraordinaíre _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
On Tue, 2002-01-29 at 08:19, Frank Bax wrote:
>
> If your postgres database is on the same server as you webserver, there is
> neglible gains for using pconnect over connect.
I see much larger improvements than that, in the real world, with my
applications which are all running database and webserver on the same
system.
In my applications I use a wrapper for the query process which logs the
query time (amongst other things), and pg_pconnect gives substantial
improvements in regular query performance over pg_connect.
I believe what is happening is that the PostgreSQL client is in some
part caching query plans, metadata and data so that queries execute
faster. For minimal queries the performance gain seems to be as much as
10 times (i.e. the pconnect query frequently runs in 1/10th of the time
of the normal connect).
Obviously these speed improvements also depend on your application's
patterns of data access, but I have not found an application that
doesn't show significant improvement.
I have also found the same order of improvement to be made by using
DBBalancer to pool the database connections.
Regards,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?
hello sir,
i am arun from iisc,bangalore. we are on a database project and are using
postgresql and php. i am finding some problems in keyword search. we have
to store some keywords in a field and when we give some keywords it should
search for the same in that field. how to store in database and how to
access is the problem. pls help me in this matter.
with rgds
Arun
Arun, > i am arun from iisc,bangalore. we are on a database project and are > using > postgresql and php. i am finding some problems in keyword search. we > have > to store some keywords in a field and when we give some keywords it > should > search for the same in that field. how to store in database and how > to > access is the problem. pls help me in this matter. > with rgds Unfortunately, your question does not have a simple answer, as there are several options depending on waht you're searching, how often, how many users, and what kind of keywords you want to use. What you need is a book on the basics of database design. I do not know of one that has been translated into Indonesian. You may check out lists of English-language books at: http://techdocs.postgresql.org/bookreviews.php and http://www3.us.postgresql.org/books/index.html If English books are no use to you, or exchange rates put them out of your price range, e-mail me back and I will outline some simple methods for keyword searching. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
On Mon, 28 Jan 2002, Chris Ruprecht wrote:
> Ok, so from what I see on here, pg_pconnect doesn't have any
> advantages over pg_connect in my situation. I have the web server
> running on the same machine as the database server and yes, I have
> allocated 16384 shared buffers (at 8 KB each, this is 128 MB of
> shared memory). I have not noticed any connect overhead when opening
> a web page which connects to the database (99% of them do), the
> connection is instantaneously, so I guess, I don't need to do
> anything here.
> I was under the impression, that a persistent connection would open
> one and only one process which then will be used all the time without
> creating more child processes which keep lingering about. I guess, I
> was wrong here ...
You're right: one process *per httpd child*. You do want lingering
processes, it's all what pconnect is about. Persistent connections
means persistent postgres backends, of course.
On a server with usually 50/100 httpd processes (and a good request/sec
ratio), it makes a difference if every httpd process is paired with a
postgres backend. No connect(), no fork()/exec(), no auth overhead.
Just read()/write() on preexisting socket.
>
> Best regards,
> Chris
>
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it
On Mon, 28 Jan 2002, Frank Bax wrote:
> The connection will only be reused if the *same* apache child process
> handles the request. You should expect to see many postgres client
> connections.
> A) Each database/user connection combination.
> B) Each apache child process
>
> Multiply A*B to get max number of concurrent connections. If A*B can go
> over postgres connection limit, then you might start getting connection
> refused messages.
Are you sure? Doesn't the httpd child process close the active connection
if the database/username pair is different, before opening a new one?
On a db with say 10000 different users, the usage of pconnect() may
potentially lead to 10000 * B open connections (thus postgres backends),
most of those completely useless...
... some thinking ...
no, I was wrong:
pgsql.max_persistent integer
The maximum number of persistent Postgres connections per process.
^^^^^^^^^^^
you can have more than one persistent connection per httpd process. Now
that I think about it, it seems a good idea to limit it to some sane
value.
> If your postgres database is on the same server as you webserver, there is
> neglible gains for using pconnect over connect.
>
> Frank
Well, it depends on the type of queries. For *a lot* of very simple and
fast queries performed by the same user (like readonly selects in a single
user environment), the TCP connect and fork/exec (of the postgres backends)
overhead may dominate. Of course, whenever the query time dominates,
it makes hardly a difference.
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it
At 09:09 PM 1/31/02 +0100, Marco Colombo wrote: >On Mon, 28 Jan 2002, Frank Bax wrote: >> If your postgres database is on the same server as you webserver, there is >> neglible gains for using pconnect over connect. > >Well, it depends on the type of queries. For *a lot* of very simple and >fast queries performed by the same user (like readonly selects in a single >user environment), the TCP connect and fork/exec (of the postgres backends) >overhead may dominate. Of course, whenever the query time dominates, >it makes hardly a difference. Ah, I hadn't considered that, query time dominates all my scripts. Another thing I've noticed. Just for the record ... Persistent connections do appear to go away after time. When I used them on my relatively idle (compared to many) server, there almost always were none early in the morning. With non-scientific methods, and random observations I determined that the time period was probably between one hour and one day. Too bad all this info about factors affecting pg_pconnect weren't all in one place... Frank
At 09:09 PM 1/31/02 +0100, Marco Colombo wrote: >On Mon, 28 Jan 2002, Frank Bax wrote: > >> The connection will only be reused if the *same* apache child process >> handles the request. You should expect to see many postgres client >> connections. >> A) Each database/user connection combination. >> B) Each apache child process >> >> Multiply A*B to get max number of concurrent connections. If A*B can go >> over postgres connection limit, then you might start getting connection >> refused messages. > >pgsql.max_persistent integer > The maximum number of persistent Postgres connections per process. > ^^^^^^^^^^^ > >you can have more than one persistent connection per httpd process. Now >that I think about it, it seems a good idea to limit it to some sane >value. I was actually referring to max_connections in postgresql.conf (as the limit for A*B). I expect that pgsql.max_persistent puts a limit on A rather than A*B. I let this run wild (value=-1) on my system and use default of max_connections=64) in postgresql. Frank
> Are you sure? Doesn't the httpd child process close the active connection > if the database/username pair is different, before opening a new one? > On a db with say 10000 different users, the usage of pconnect() may > potentially lead to 10000 * B open connections (thus postgres backends), > most of those completely useless... > > ... some thinking ... > > no, I was wrong: > > pgsql.max_persistent integer > The maximum number of persistent Postgres connections per process. > ^^^^^^^^^^^ See the big argument I had with the PHP developers with regards to the crapness of their persistent connection implementation for Postgres: http://bugs.php.net/bug.php?id=13463 I still don't think the guy has any idea what I'm talking about, and they've filed my report as 'bogus'. Very annoying... Chris
On Fri, 2002-02-01 at 09:28, Frank Bax wrote:
>
> Ah, I hadn't considered that, query time dominates all my scripts.
Then persistent connections are probably less useful, but don't be sure
until you have measured the difference. If those long-running queries
visit the same data, and if you have a large enough -B (those buffers
are shared, remember), then there could still be significant benefit.
> Another thing I've noticed. Just for the record ... Persistent connections
> do appear to go away after time. When I used them on my relatively idle
> (compared to many) server, there almost always were none early in the
> morning. With non-scientific methods, and random observations I determined
> that the time period was probably between one hour and one day.
>
> Too bad all this info about factors affecting pg_pconnect weren't all in
> one place...
Apache will recycle it's client processes, so if you have applications
which do _not_ access the persistent connections then enough requests
will be made over time to cause this to happen.
When Apache recycles those processes (MaxRequestsPerChild), the
persistent connections will be closed. The new process will not have a
persistent connection until one is requested. From what you describe is
happening I would guess you have a small part of your application using
these, possibly some part that only you access.
Cheers,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?
On Fri, 1 Feb 2002, Christopher Kings-Lynne wrote: > > Are you sure? Doesn't the httpd child process close the active connection > > if the database/username pair is different, before opening a new one? > > On a db with say 10000 different users, the usage of pconnect() may > > potentially lead to 10000 * B open connections (thus postgres backends), > > most of those completely useless... > > > > ... some thinking ... > > > > no, I was wrong: > > > > pgsql.max_persistent integer > > The maximum number of persistent Postgres connections per process. > > ^^^^^^^^^^^ > > See the big argument I had with the PHP developers with regards to the > crapness of their persistent connection implementation for Postgres: > > http://bugs.php.net/bug.php?id=13463 > > I still don't think the guy has any idea what I'm talking about, and they've > filed my report as 'bogus'. Very annoying... What you want is cached persistent connections, with a specified pool size. This way every time you need a new p-connection and the cache is full, you just evict the LRU one from the cache and replace it with the new one. The only problem I see here is that you need to mark the connections made by the *current* php script as "unclosable". Only connections inherited by previous php scrips (running inside the same httpd child, of course) can be safely closed. The docs clearly state that the percieved behaviour of pconnect() is the same of connect(), and connections can be closed any time after the script that creates them ends. But of course connections made by the current script cannot be closed without an explicit close() by the script itself. > > Chris > .TM.
On Mon, Jan 28, 2002 at 02:19:57PM -0500, Frank Bax wrote: > The connection will only be reused if the *same* apache child process > handles the request. IMHO, also if another apache child handles the request which also has a connection open to the same database using the same connection string. [ . . . ] > If your postgres database is on the same server as you webserver, there is > neglible gains for using pconnect over connect. Not exactly true, I think. A while ago I started a thread about using Unix sockets vs. TCP sockets because I had trouble getting Unix sockets to work on Solaris. It emerged from the discussion that the added overhead vis-a-vis local sockets of going through the TCP/IP stack was completely negligible compared to all the other stuff that is going on when making, or reusing, a (persistent or non-persistent) db connection. I should think that with a fast, non-saturated network, the fact that you then add a physical (e.g. Ethernet) layer to the scenario would not matter much either. I recently split webserver and db server here (basically to make administration less of a headache because I had Postgres servers running on 2 different boxes, for Intranet and Internet), and put the databases from the Postgres server running on the webserver (an old Dual PPro 200) onto a somewhat newer, 2nd box (Dual PII 400). It makes quite a difference, speed-wise, which would support my line of reasoning. I didn't try to measure this scientifically (or pseudo-scientifically, for that matter). Whether you get a performance gain from using pconnect over connect depends on whether you use simple queries (connection overhead is large compared to query execution time) or complex queries (connection overhead is small compared to query execution time). It has little to do, IMHO, with whether you go throug local sockets or TCP/IP sockets, or if your Postgres server is on the same physical machine as your webserver. Regards, Frank
On Sat, 2 Feb 2002, Frank Joerdens wrote: > On Mon, Jan 28, 2002 at 02:19:57PM -0500, Frank Bax wrote: > > The connection will only be reused if the *same* apache child process > > handles the request. > > IMHO, also if another apache child handles the request which also has a > connection open to the same database using the same connection string. > > [ . . . ] > > If your postgres database is on the same server as you webserver, there is > > neglible gains for using pconnect over connect. > PII 400). It makes quite a difference, speed-wise, which would support > my line of reasoning. I didn't try to measure this scientifically (or > pseudo-scientifically, for that matter). It's hard for me to tell here whether you are referring to faster connections, or slower connections. I know the query run time is important in this discussion, so I assume the two latter statments above are regarding connection time. Frank: are you using pconnect? If so: are you using it due to slower connection times through your tcp/ip (?is what you're using, right?) sockets? -and- are you able to use unix sockets with solaris? Does the added physical layer make tcp/ip much slower, slower, or not at all? Point me to the thread, please. Personally, I see pconnects being used on the web/postgres/php server here, and there are always about 10 children lying arond idle. There is no connection time difference between pconnect and connect. I see no need to use them. When the database server does eventually move to it's own box (maybe), I'm wondering if I should recommend persistence if connection is slow... as well as a shorter connection timeout. I'm also wondering if I should offer to change all those pconnect statements to connect to save on resources that the idle children take up.. ;P Chadwick Rolfs - cmr@gis.net Cleveland State University - Student Music Major - The Holden Arboretum Volunteer Computer Programmer - Student Employee --*I finally found powdered water; I just can't figure out what to add to it*--
On Sat, Feb 02, 2002 at 12:35:56PM -0500, Chadwick Rolfs wrote: > On Sat, 2 Feb 2002, Frank Joerdens wrote: [ . . . ] > > PII 400). It makes quite a difference, speed-wise, which would support > > my line of reasoning. I didn't try to measure this scientifically (or > > pseudo-scientifically, for that matter). > > It's hard for me to tell here whether you are referring to > faster connections, or slower connections. I know the query run time is > important in this discussion, so I assume the two latter statments above > are regarding connection time. I seem to be having difficulties expressing myself clearly these days (others have complained) ;-/. All I was trying to say is that, very subjectively, the web pages served from Postgres where appearing quicker after splitting webserver and database server onto 2 distinct machines. I realize this doesn't mean anything. Could be my imagination and/or wishful thinking. > Frank: are you using pconnect? Yes. > If so: are you using it due to slower > connection times through your tcp/ip (?is what you're using, right?) > sockets? No, just because they are generally faster with simple queries. And because I can afford it (I'm not bothered by all those idle postgres children hanging about in memory). > -and- are you able to use unix sockets with solaris? That was a while ago when I tried, and no, I didn't get it work, and used TCP/IP on that server at the client's site (not the server I am using here) instead. > Does the > added physical layer make tcp/ip much slower, slower, or not at all? Impossible to say from my unsystematic experimentation because the new machine is at least twice as fast as the webserver box. For this reason alone I expected everything to be faster (I basically split the work between 4 processors, 2 on each box, and 2 SCSI controllers, one on each box), and so it was. This may count as circumstantial evidence at the very most, I know. > Point me to the thread, please. http://archives.postgresql.org/pgsql-general/2001-01/msg01427.php > Personally, I see pconnects being used on the web/postgres/php server > here, and there are always about 10 children lying arond idle. There is > no connection time difference between pconnect and connect. I doubt it (can anyone come up with an ingeniously simple, straightforward, and counclusive experimental set-up to allow us to talk about this a little more scientifically?). > I see no need > to use them. When the database server does eventually move to it's own > box (maybe), I'm wondering if I should recommend persistence if connection > is slow... as well as a shorter connection timeout. > > I'm also wondering if I should offer to change all those pconnect > statements to connect to save on resources that the idle children take > up.. ;P At one of our client's sites, their sysadmin was bothered too about those idle processes hanging about . . . so I removed pconnect. It still works OK, basically because the machine in question is ludicrously overpowered for the app. Regards, Frank
thnx for reply sir. actually we are maintaining a cdrom database in
postgresql wherein we have cd rom titlename,author,year etc and keywords
as fields. we will store a set of keywords for each record. i want to know
how to enter those keywords in to database (whether to have comma between
keywords or plain) and code to search for keywords. i.e. if i enter a
keyword then it should search for that keyword in keyword field and
display result. keywords are strings an there will also be some
combinational search. i believe u will see to this and do the needful.
thanking you
with rgds
Arun
On Thu, 31 Jan 2002, Josh Berkus wrote:
> Arun,
>
> > i am arun from iisc,bangalore. we are on a database project and are
> > using
> > postgresql and php. i am finding some problems in keyword search. we
> > have
> > to store some keywords in a field and when we give some keywords it
> > should
> > search for the same in that field. how to store in database and how
> > to
> > access is the problem. pls help me in this matter.
> > with rgds
>
> Unfortunately, your question does not have a simple answer, as there
> are several options depending on waht you're searching, how often, how
> many users, and what kind of keywords you want to use.
>
> What you need is a book on the basics of database design. I do not know
> of one that has been translated into Indonesian. You may check out
> lists of English-language books at:
> http://techdocs.postgresql.org/bookreviews.php
> and
> http://www3.us.postgresql.org/books/index.html
>
> If English books are no use to you, or exchange rates put them out of
> your price range, e-mail me back and I will outline some simple
> methods for keyword searching.
>
> -Josh Berkus
>
> ______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology josh@agliodbs.com
> and data management solutions (415) 565-7293
> for law firms, small businesses fax 621-2533
> and non-profit organizations. San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
Here's my implementation of keyword searching, done in pure sql and php. My plan was to have specific document types in their own specific directory (i.e. /pdfs /doc /etc.). The design has changed somewhat but this should give a better idea of how to do keyword searching. If you're comfortable with unix and can usually get software to work, you might look into Open Full Text Search < http://openfts.sourceforge.net >. I don't think it has a php extension, but you may want to look at it. There are also several good search engines available, a list of which is provided at Search Tools http://www.searchtools.com/tools/tools-opensource.html <---------- Postgresql description -------------> CREATE TABLE doclist ( doc_id int not null, doc_summary varchar(50), doc_keywords char(200), doc_type int not null ) CREATE TABLE doctype ( type_id serial, type_name varchar(35), doc_location varchar(36) ) <---------- End Postgresql description -------------> The keywords were to be submitted in a form field called "formvars". This is somewhat of a hack put together with tips and suggestions in the pgsql and php mailing lists. The result of the bottom script is that you will have the relevant documents in weighted order. <---------- PHP description -------------> $_POST["formvars"]=trim($_POST["formvars"]); $search_items=explode(" ", $_POST["formvars"]); // Create query based on search input switch (count ($search_items)) { case 0: header('Location:' . $HTTP_SERVER_VARS["HTTP_REFERER"]); break; case 1: $DocQuery="SELECT "; $DocQuery.=" ds.doc_summary, ds.doc_id, dt.type_name, dt.doc_location, ds.score "; $DocQuery.="FROM "; $DocQuery.=" doctype dt, "; $DocQuery.=" (select doc_id, doc_summary, doc_type, ("; $DocQuery.=" (CASE WHEN doc_keywords LIKE '%" . $search_items[0] . "%' THEN 1 ELSE 0 END) ) "; $DocQuery.=" AS score FROM doclist) ds "; $DocQuery.="WHERE"; $DocQuery.=" ds.doc_type=dt.type_id AND "; $DocQuery.=" ds.score>=1 "; $DocQuery.="ORDER BY"; $DocQuery.=" score DESC;"; break; case 2: $DocQuery="SELECT "; $DocQuery.=" ds.doc_summary, ds.doc_id, dt.type_name, dt.doc_location, ds.score "; $DocQuery.="FROM "; $DocQuery.=" doctype dt, "; $DocQuery.=" (select doc_id, doc_summary, doc_type, ("; $DocQuery.=" (CASE WHEN doc_keywords LIKE '%" . $search_items[0] . "%' THEN 1 ELSE 0 END) + "; $DocQuery.=" (CASE WHEN doc_keywords LIKE '%" . $search_items[1] . "%' THEN 1 ELSE 0 END) ) "; $DocQuery.=" AS score FROM doclist) ds "; $DocQuery.="WHERE"; $DocQuery.=" ds.doc_type=dt.type_id AND "; $DocQuery.=" ds.score>=2 "; $DocQuery.="ORDER BY"; $DocQuery.=" score DESC;"; break; default: $arraycount=count ($search_items); $i=0; $DocQuery="SELECT "; $DocQuery.=" ds.doc_summary, ds.doc_id, dt.type_name, dt.doc_location, ds.score "; $DocQuery.="FROM "; $DocQuery.=" doctype dt, "; $DocQuery.=" (select doc_id, doc_summary, doc_type, ("; while($i < ($arraycount-1)) { $DocQuery.=" (CASE WHEN doc_keywords LIKE '%" . $search_items[$i] . "%' THEN 1 ELSE 0 END) + "; $i++; } $DocQuery.=" (CASE WHEN doc_keywords LIKE '%" . $search_items[$arraycount-1] . "%' THEN 1 ELSE 0 END) ) "; $DocQuery.=" AS score FROM doclist) ds "; $DocQuery.="WHERE"; $DocQuery.=" ds.doc_type=dt.type_id AND "; $DocQuery.=" ds.score>=" . ($arraycount-1) . " "; $DocQuery.="ORDER BY"; $DocQuery.=" score DESC;"; } // Search database $result = pg_Exec($conn, $DocQuery); if (!$result) { exit(); } else { $num=pg_numrows($result); if ($num<>0) { echo "<h2>$num record(s) found.</h2>"; $i=0; while($i < $num) { @$row=pg_fetch_array($result,$i); echo "<br>\n"; echo "<a href=\"" . $row["doc_location"] . $row["doc_id"] . ".pdf\"><strong>" . $row["doc_id"] ."</strong></a>"; echo "<img src=\"/images/pdf.gif\" width=\"20\" height=\"22\" border=\"0\" alt=\"PDF document\"><br>\n"; echo "<strong>Category: </strong>" . $row["type_name"] ."<br>\n"; echo "<strong>Summary: </strong>" . $row["doc_summary"] ."<br>\n"; echo "</br>\n"; $i++; } } else { echo "<h2>No records found.</h2>"; } <---------- End PHP description -------------> The above query, given some keywords, would result in something similar to the following: doc_summary | doc_id | type_name | doc_location | score -----------------------+--------+-----------+---------------+------- AV195 - Exploded view | 1002 | VACUUMS | /techdocs/av/ | 3 AV395 - Exploded view | 1003 | VACUUMS | /techdocs/av/ | 2 (2 rows) ..which php then proceeds to list in ordered form. In any case, hope this helps, if not, maybe some of the links will prove useful. do arun kv wrote: > > thnx for reply sir. actually we are maintaining a cdrom database in > postgresql wherein we have cd rom titlename,author,year etc and keywords > as fields. we will store a set of keywords for each record. i want to know > how to enter those keywords in to database (whether to have comma between > keywords or plain) and code to search for keywords. i.e. if i enter a > keyword then it should search for that keyword in keyword field and > display result. keywords are strings an there will also be some > combinational search. i believe u will see to this and do the needful. > thanking you > with rgds > Arun > > On Thu, 31 Jan 2002, Josh Berkus wrote: > > > Arun, > > > > > i am arun from iisc,bangalore. we are on a database project and are > > > using > > > postgresql and php. i am finding some problems in keyword search. we > > > have > > > to store some keywords in a field and when we give some keywords it > > > should > > > search for the same in that field. how to store in database and how > > > to > > > access is the problem. pls help me in this matter. > > > with rgds > > > > Unfortunately, your question does not have a simple answer, as there > > are several options depending on waht you're searching, how often, how > > many users, and what kind of keywords you want to use. > > > > What you need is a book on the basics of database design. I do not know > > of one that has been translated into Indonesian. You may check out > > lists of English-language books at: > > http://techdocs.postgresql.org/bookreviews.php > > and > > http://www3.us.postgresql.org/books/index.html > > > > If English books are no use to you, or exchange rates put them out of > > your price range, e-mail me back and I will outline some simple > > methods for keyword searching. > > > > -Josh Berkus > > > > ______AGLIO DATABASE SOLUTIONS___________________________ > > Josh Berkus > > Complete information technology josh@agliodbs.com > > and data management solutions (415) 565-7293 > > for law firms, small businesses fax 621-2533 > > and non-profit organizations. San Francisco > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)