Re: Long running queries and timeouts

Here is a new project that some people might be interested
in. This project now supports Oracle8i and PostgreSQL.
There is a PHP module as well.

SQL Load Balancer (SQLB)
http://sqlb.sourceforge.net/

The SQLB project is used to improve SQL requests to a
database. It provides programs that have permanent
connections to a DB, a program that checks the integrity of
transactions, and a library to link with programs that need
to make SQL queries.

Brent

> Here is a new project that some people might be interested
> in. This project now supports Oracle8i and PostgreSQL.
> There is a PHP module as well.
>
> SQL Load Balancer (SQLB)
> http://sqlb.sourceforge.net/
>
> The SQLB project is used to improve SQL requests to a
> database. It provides programs that have permanent
> connections to a DB, a program that checks the integrity of
> transactions, and a library to link with programs that need
> to make SQL queries.

FYI, 7.2 will have a RESET ALL; command that is useful for passing
persistent connections, and we use "BEGIN;COMMIT;" to cancel any open
transaction.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

> > SQL Load Balancer (SQLB)
> > http://sqlb.sourceforge.net/
> >
> > The SQLB project is used to improve SQL requests to a
> > database. It provides programs that have permanent
> > connections to a DB, a program that checks the integrity of
> > transactions, and a library to link with programs that need
> > to make SQL queries.

Now, if we can get good, real-time replication among many servers, then
we're in business. : )

steve

New to the PHP/PostgreSQL combo.
I have a couple of queries which take a few minutes to run and we are
getting timeouts. Didn't find anything usefull on the archives.

How are you handling long running queries on your PHP?

On Sat, 19 Jan 2002, Francisco Reyes wrote:

> New to the PHP/PostgreSQL combo.
> I have a couple of queries which take a few minutes to run and we are
> getting timeouts. Didn't find anything usefull on the archives.
>
> How are you handling long running queries on your PHP?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
You can always use persistent connections. It's not a good idea to use
too many, or you will be denied access to Postgres any other way, but it's
a quick fix for the short run.

Long run and responsibility includes using an array, or an object to
retrieve data. You can always split queries up into many small queries.
We'll need a little more information to help you with that, though. Like
what the code is...what your php.ini file and httpd.conf file says about
connection timeouts... maybe more ??-- like what you've read on php.net's
docs...

When new programmers here where I work started using php/postgresql 6
months ago, they used pconnect in EVERY one of their scripts, filled up
the server, and were unable to connect. No one was able to. They didn't
know what they did, and it took a little reading to figure out that Apache
needs to be restarted, NOT postgres. Don't kill -9 the postmaster.
Regards,

Chadwick Rolfs - cmr(at)gis(dot)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*--

set_time_limit( <secs> );

At 11:48 AM 1/19/02 -0500, Francisco Reyes wrote:
>New to the PHP/PostgreSQL combo.
>I have a couple of queries which take a few minutes to run and we are
>getting timeouts. Didn't find anything usefull on the archives.
>
>How are you handling long running queries on your PHP?
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

http://www.php.net/manual/en/function.set-time-limit.php will allow you to
set the timeout but you might post the queries and EXPLAIN output to
pg-general to see if there is something that can be done to speed them up...
Some well placed indexes always help!

> You can always use persistent connections. It's not a good idea to use
> too many, or you will be denied access to Postgres any other way, but it's
> a quick fix for the short run.

The maximum connections is a configuration parameter in postgresql.conf --
persistent connections are pretty cool but have been broken until recent
versions of PHP so I'm still hesitant using them..

> Long run and responsibility includes using an array, or an object to
> retrieve data.

Huh?

>You can always split queries up into many small queries.

What makes you say that?

-Mitch

Hi all

> http://www.php.net/manual/en/function.set-time-limit.php will
allow you to
> set the timeout

This only works if safe mode is off.

If you have a couple of queries in one script you can divide them
up on different pages. Put a redirect at the end of the query. On
the new page the execution time starts new. Normally it is set to
30 seconds in the php.ini file.
Or have a look on the function ini_set()

string ini_set (string varname, string newvalue)

(I have never tested this function;-)

Greetings
Conni

> If you have a couple of queries in one script you can divide them
> up on different pages.

I have a page that uses many queries to gather a bunch of stats that are
saved for later display. It is not too important that they be noted at
exactly the same time so every xxth time (or after a timestamp) 1 of
them is updated. The next case to update is saved in the row. Adds a bit
of overhead, but I don't have to worry as the db grows and can easily
add more cases.

Jeff

On Sat, 19 Jan 2002, Frank Bax wrote:

>
> set_time_limit( <secs> );
>
>
> At 11:48 AM 1/19/02 -0500, Francisco Reyes wrote:
> >How are you handling long running queries on your PHP?

I just read the explanation at the php.net site for that function and it
doesn't seem to do what I would need:
"Any time spent on activity that happens outside the execution of the
script such as ......database queries, etc. is not included when
determining the maximum time that the script has been running."

I am going to try anyway to see if this helps.

On Sat, 19 Jan 2002, Chadwick Rolfs wrote:

> On Sat, 19 Jan 2002, Francisco Reyes wrote:
>
> > New to the PHP/PostgreSQL combo.
> > I have a couple of queries which take a few minutes to run and we are
> > getting timeouts. Didn't find anything usefull on the archives.
> >
> > How are you handling long running queries on your PHP?
> >
> You can always use persistent connections. It's not a good idea to use
> too many, or you will be denied access to Postgres any other way, but it's
> a quick fix for the short run.
>
> Long run and responsibility includes using an array, or an object to
> retrieve data. You can always split queries up into many small queries.
> We'll need a little more information to help you with that, though. Like
> what the code is...what your php.ini file and httpd.conf file says about
> connection timeouts... maybe more ??-- like what you've read on php.net's
> docs...

Thanks for the info/pointers. The issue is basically a query that takes
several minutes to run. I look/read about persistent connections and I
don't think that has anything to do with the problem. I believe the
timeout is probably at the web server.

After someone suggested I looked at "set_time_limit" I found that such
function doesn't account for DBconnection time. Afer reading that and your
suggestion to read httpd.conf I am starting to think that the likely place
of the timeout is the web server.

Will play with the settings on httpd.conf

I wonder how sites that display an update while the long query is running
do it. I have seen sites that display something like "retrieving info..
please wait" and then right before the info comes up the waiting text
dissapears. Is that Javascript? Flash? I can't imagine how that could be
done with plain html.

On Sunday 20 January 2002 16:56, Francisco Reyes wrote:
> On Sat, 19 Jan 2002, Chadwick Rolfs wrote:
> > On Sat, 19 Jan 2002, Francisco Reyes wrote:
> > > New to the PHP/PostgreSQL combo.
> > > I have a couple of queries which take a few minutes to run and we are
> > > getting timeouts. Didn't find anything usefull on the archives.
> > >
> > > How are you handling long running queries on your PHP?
[...]
> After someone suggested I looked at "set_time_limit" I found that such
> function doesn't account for DBconnection time. Afer reading that and your
> suggestion to read httpd.conf I am starting to think that the likely place
> of the timeout is the web server.
>
> Will play with the settings on httpd.conf
>
> I wonder how sites that display an update while the long query is running
> do it. I have seen sites that display something like "retrieving info..
> please wait" and then right before the info comes up the waiting text
> dissapears. Is that Javascript? Flash? I can't imagine how that could be
> done with plain html.

What timeout message do you get, exactly? I could be just the *client*, or
the HTTP proxy, that times out if your script doesn't produce any output,
and *not* the httpd process (thus, no need to look at httpd.conf).

.TM.

On Sat, 19 Jan 2002, Mitch Vincent wrote:

> http://www.php.net/manual/en/function.set-time-limit.php will allow you to
> set the timeout but you might post the queries and EXPLAIN output to
> pg-general to see if there is something that can be done to speed them up...
> Some well placed indexes always help!
>
> > You can always use persistent connections. It's not a good idea to use
> > too many, or you will be denied access to Postgres any other way, but it's
> > a quick fix for the short run.
>
> The maximum connections is a configuration parameter in postgresql.conf --
> persistent connections are pretty cool but have been broken until recent
> versions of PHP so I'm still hesitant using them..
>

It's good to see someone else sees problems with persistent connections.
You could set your maximum connections to infinite, but would that really
do any good? It seems the server might easily run out of memory if there
isn't a cap on connection numbers,,,, as well as connection time.

> > Long run and responsibility includes using an array, or an object to
> > retrieve data.
>
> Huh?

I can see why this doesn't make much sense. Seeing as we have nothing to
work with from the original post regarding just what the query is.. I'll
try to give an example to explain.

querying all the names in a phone book DB:
select first_name, last_name from white_pages;
--UH... this will be,,,let's say 150,000 entries (small city phone book)

WELL, you could select by starting letters, and put it into an array,
using pg_fetch_array, or pg_fetch_object. Make multiple queries in the
same script, and use that array (or object) to ouput to the browser...

select first_name, last_name from white_pages where last_name like 'A%';
select first_name, last_name from white_pages where last_name like 'B%';
select first_name, last_name from white_pages where last_name like 'C%';
select first_name, last_name from white_pages where last_name like 'D%';
...etc

if it's still too long, try 'Aa%', 'Ab%' ... so on. Tedious, but
efficient.

I know this is only the sql, there is php involved. We can save that for
the php list, or, if there are any further questions. Hope this clarifies
at least a little bit. ??

>
> >You can always split queries up into many small queries.
>
> What makes you say that?

if you have your rdbms set up right, you have a unique number (primary
key) relating to each row. So using the sql syntax above, you could
select one row at a time, if you needed to, using a for loop and sending
single queries to the backend. You shouldn't be flooding a webbrowser
with over 30 seconds of data anyway, as you run the risk of a crash.

>
> -Mitch
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Chadwick Rolfs - cmr(at)gis(dot)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*--

> I can see why this doesn't make much sense. Seeing as we have nothing to
> work with from the original post regarding just what the query is.. I'll
> try to give an example to explain.
>
> querying all the names in a phone book DB:
> select first_name, last_name from white_pages;
> --UH... this will be,,,let's say 150,000 entries (small city phone book)
>
> WELL, you could select by starting letters, and put it into an array,
> using pg_fetch_array, or pg_fetch_object. Make multiple queries in the
> same script, and use that array (or object) to ouput to the browser...
>
> select first_name, last_name from white_pages where last_name like 'A%';
> select first_name, last_name from white_pages where last_name like 'B%';
> select first_name, last_name from white_pages where last_name like 'C%';
> select first_name, last_name from white_pages where last_name like 'D%';
> ...etc
>
> if it's still too long, try 'Aa%', 'Ab%' ... so on. Tedious, but
> efficient.

You could always just use CURSORs. Look up 'DECLARE' and 'MOVE' and 'FETCH'
sql commands in postgres.

Chris