Re: [Sqlrelay-discussion] SQLRelay and PostgreSQL session...

[Firstworks/4access <dav...@fi...>]

Christian,

SQL Relay has a feature called suspended sessions where you can run a
query, suspend the session, pass a session id to the next page (or store
it somewhere) and then the next page can pick up the same session and do
more queries.

It's usually used by people who want a transaction to span multiple
pages.  Ie. one insert is done on each page, and on the last page, you
can commit or rollback the inserts.

In theory, you could prepare and execute the query on one page, suspend
the session, pass the session id around and on each page, resume the
session and re-execute the query.  I've never tried that exactly, but
I'm pretty sure it would work.  You would have to make sure the query
does get executed though on the first page, as the SQL Relay client
doesn't send seperate prepare and execute commands to the server, just
single "prepare-and-execute" or "re-execute" commands.

As for 10 million transactions per day, thats about 115 per second.
I've gotten several reports of people doing "thousands of queries per
second" with SQL Relay, but that was against Oracle and who knows what
kind of hardware.

David Muse
dav...@fi...

On Thu, 2005-11-17 at 10:12 -0600, Cristian Prieto wrote:
> Hi, first at all I'm a new user of SQLRelay and I have a few questions:
> 
> 1. Does SQLRelay keeps alive a PostgreSQL session? I say this cuz I have a
> very intensive web database application. I always use and do the same query
> in the database (just a call to a stored procedure as "select * from
> mysp()") This sp is written in plpgsql, and each time I have to execute it
> eats a lot of cpu. (mainly cuz I have to "recompile" the plpgsql, prepare
> the queries and so on). The first time I run it takes around 114ms, but the
> next time it would take just 15ms. I asked to the postgresql team and so on
> and I get the same answer, in a web application the performance could be so
> poor cuz there is no way to keep the same session alive for each web
> "consumer" process. I guess is so stupid, because I'm doing the same query
> everytime.
> 2. I've been using pgpool, and it doesn't help too much for me, certainly it
> keeps a set of connection alive, but it doesn't share the same
> session/connection between web consumers, so I just minimize the connection
> time, not the compile/prepare time of the plpgsql stored procedures.
> 3. I need to have 10M connections per day, is this possible with sqlrelay?
> Somebody has achieved similar performance?
> 
> Thanks a lot for your answers, Thanks again.
> 
> Cristian Prieto
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by the JBoss Inc.  Get Certified Today
> Register for a JBoss Training Course.  Free Certification Exam
> for All Training Attendees Through End of 2005. For more info visit:
> http://ads.osdn.com/?ad_id=7628&alloc_id=16845&op=click
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>