sqlrelay-discussion — Discussion of topics related to SQL Relay

Re: [Sqlrelay-discussion] SQL Relay 0.36.3 maintenance release

[vieonet f. <df...@vi...>]

thanks a lot

david
----- Original Message ----- 
From: "David Muse" <dav...@fi...>
To: <sql...@li...>
Sent: Thursday, June 02, 2005 5:50 AM
Subject: [Sqlrelay-discussion] SQL Relay 0.36.3 maintenance release


> Hello all,
>
> I just made a maintenance release of SQL Relay: sqlrelay-0.36.3
>
> This release fixes:
> * a bug causing sqlrsh to hang on systems without readline
> * fixes to php pear db driver for autoExecute
> * readline detection succeeds on systems without libhistory now
> * minor bugfix to init script for SCO OpenServer
>
> If you aren't having one of those problems, then you probably don't need
> to upgrade.
>
> Have fun.
>
> David Muse
> dav...@fi...
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by Yahoo.
> Introducing Yahoo! Search Developer Network - Create apps using Yahoo!
> Search APIs Find out how you can build Yahoo! directly into your own
> Applications - visit http://developer.yahoo.net/?fr=offad-ysdn-ostg-q22005
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 

Re: [Sqlrelay-discussion] bind pear php module

[David M. <dav...@fi...>]

I just made a maintenance release which should address these errors,
give it a try and let me know how it goes.

Dave
dav...@fi...

On Fri, 2005-05-20 at 17:56 +0200, david forums wrote:
> That would be very nice.
> 
> Thanks for your work
> 
> livingdead
> 
> Le Fri, 20 May 2005 11:40:42 -0400, Firstworks/4access  
> <dav...@fi...> a écrit:
> 
> > Ahh, I see.
> >
> > I'll have to look into this a bit further.  Maybe I can get SQL Relay to
> > support ?'s in short order.
> >
> > Dave
> >
> > On Fri, 2005-05-20 at 17:15 +0200, david forums wrote:
> >> Sorry but I'm not the manager of those question mark, it's PEAR DB who  
> >> set
> >> those question mark automatically, when we are using autoExecute  
> >> functions.
> >>
> >> So I do not know how to change it in PEAR DB, and How it will not break
> >> the portability of PEAR DB
> >>
> >> regards
> >>
> >> livingdead
> >>
> >>
> >>
> >>
> >>
> >> Le Fri, 20 May 2005 10:57:29 -0400, Firstworks/4access
> >> <dav...@fi...> a écrit:
> >>
> >> > SQL Relay uses Oracle-style bind variables, so try using:
> >> >
> >> > 	INSERT INTO myTable (col1,col2,col3) VALUES (:1,:2,:3)
> >> >
> >> > and see if that doesn't work better (using the original code for the
> >> > execute function).
> >> >
> >> > Supporting ?'s is on the TODO list, but I haven't implemented it yet.
> >> >
> >> > Dave
> >> > dav...@fi...
> >> >
> >> > On Fri, 2005-05-20 at 16:12 +0200, david forums wrote:
> >> >> Hello
> >> >>
> >> >> so Here is the request
> >> >>
> >> >> $db->autoExecute("myTable", $lstField, DB_AUTOQUERY_INSERT);
> >> >>
> >> >> this build the several request (inside PEAR DB)
> >> >> INSERT INTO myTable (col1,col2,col3) VALUES (?,?,?)
> >> >>
> >> >> But it seems that when it reach the execute fonction, it's not able  
> >> to
> >> >> fill the ?, with the right data.
> >> >> Analysing the function I discover this :
> >> >>
> >> >>   function &execute(&$sqlrcursor, $data = false)
> >> >>      {
> >> >>      	sqlrcur_clearBinds($sqlrcursor->cursor);
> >> >>          if ($data) {
> >> >>              while ($element = current($data)) {
> >> >>                  $index = key($data);
> >> >>                  sqlrcur_inputBind($sqlrcursor->cursor, $index,
> >> >> $data["$index"]);
> >> >>                  next($data);
> >> >>              }
> >> >>          }
> >> >> 	  ..........
> >> >>
> >> >> I try to modify it like :
> >> >>   function &execute(&$sqlrcursor, $data = false)
> >> >>      {
> >> >>      	sqlrcur_clearBinds($sqlrcursor->cursor);
> >> >>          if ($data) {
> >> >>              while ($element = current($data)) {
> >> >>                  $index = key($data);
> >> >>                  if(is_numeric($index)) $index = "?";
> >> >>                  sqlrcur_inputBind($sqlrcursor->cursor, $index,
> >> >> $data["$index"]);
> >> >>                  next($data);
> >> >>              }
> >> >>          }
> >> >>          ..........
> >> >>
> >> >> without success.
> >> >>
> >> >> Please thanks for your help
> >> >>
> >> >> livingdead
> >> >>
> >> >>
> >> >> -------------------------------------------------------
> >> >> This SF.Net email is sponsored by Oracle Space Sweepstakes
> >> >> Want to be the first software developer in space?
> >> >> Enter now for the Oracle Space Sweepstakes!
> >> >> http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
> >> >> _______________________________________________
> >> >> Sqlrelay-discussion mailing list
> >> >> Sql...@li...
> >> >> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >> >>
> >> >
> >> >
> >> >
> >> > -------------------------------------------------------
> >> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> >> > Want to be the first software developer in space?
> >> > Enter now for the Oracle Space Sweepstakes!
> >> > http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
> >> > _______________________________________________
> >> > Sqlrelay-discussion mailing list
> >> > Sql...@li...
> >> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >> >
> >>
> >>
> >>
> >>
> >> -------------------------------------------------------
> >> This SF.Net email is sponsored by Oracle Space Sweepstakes
> >> Want to be the first software developer in space?
> >> Enter now for the Oracle Space Sweepstakes!
> >> http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
> >> _______________________________________________
> >> Sqlrelay-discussion mailing list
> >> Sql...@li...
> >> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >>
> >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_idt12&alloc_id344&op=click
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >
> 
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 

Re: [Sqlrelay-discussion] sqlrsh hanging on openbsd

[David M. <dav...@fi...>]

I just made a maintenance release, give it a try.  It should detect
readline properly now and work even if you disable readline support.

I had some strange problems when I tested it on openbsd though.  All of
my queries failed with authentication errors and 2 sqlr-listener
processes were getting spawned off for each client connection.  It's
possible that it won't happen on real hardware though.  I was running
OpenBSD-3.7 in vmware and vmware doesn't officially support openbsd.

Give it a try and let me know.

Dave
dav...@fi...

On Wed, 2005-06-01 at 10:34 -0500, Jolan Luff wrote:
> On Mon, May 30, 2005 at 05:42:26PM -0400, David Muse wrote:
> > I just figured out the problem with sqlrsh.  sqlrsh can use the readline
> > library, and there appears to be a bug in the code that gets compiled if
> > the readline library is absent.
> 
> thanks for looking into this for me.  openbsd has shipped with readline
> for quite some time.  i looked at config.log and the readline tests fail
> because the tests try to link with -lhistory which doesn't exist on
> openbsd.  the history functions are in -lreadline.
> 
> > You can fix the problem by either installing the readline library
> > package and rebuilding or by editing src/cmdline/sqlrsh.C and changing:
> > 
> > #ifndef HAVE_READLINE
> > sqlrsh::sqlrsh() {
> > 	standardin.setFileDescriptor(0);
> > }
> > #endif
> > 
> > to:
> > 
> > #ifndef HAVE_READLINE
> > sqlrsh::sqlrsh() {
> > 	standardin.setFileDescriptor(0);
> > 	standardin.allowShortReads();
> > }
> > #endif
> > 
> > 
> > I'll make a maintenance release soon with this fix.
> > 
> > As for sqlr-stop not working, I'll need to look into that a bit more.
> > 
> > 
> > Dave
> > dav...@fi...
> > 
> > 
> > On Thu, 2005-05-26 at 23:42 -0500, Jolan Luff wrote:
> > > hi,
> > > 
> > > i'm trying to get sqlrelay-0.36.2 running on openbsd/i386 with a sqlite3
> > > database.
> > > 
> > > i'm using the sqlrelay.conf from:
> > > 
> > > http://sqlrelay.sourceforge.net/sqlrelay/gettingstarted/sqlite.html
> > > 
> > > with the only modifications being the database path and runas
> > > group/user.  i am using sqlrelay.dtd from the distribution as well.
> > > 
> > > the output from 'sqlr-start -id sqlitetest' looks fine:
> > > 
> > > Starting listener:
> > >   sqlr-listener -id sqlitetest -config /etc/pkg/sqlrelay.conf
> > > 
> > > Starting 3 connections to sqlitetest :
> > >   sqlr-connection-sqlite -id sqlitetest -connectionid sqlitetest -config /etc/pkg/sqlrelay.conf
> > >   sqlr-connection-sqlite -id sqlitetest -connectionid sqlitetest -config /etc/pkg/sqlrelay.conf
> > >   sqlr-connection-sqlite -id sqlitetest -connectionid sqlitetest -config /etc/pkg/sqlrelay.conf
> > > 
> > > Starting scaler:
> > >   sqlr-scaler  -id sqlitetest -config /etc/pkg/sqlrelay.conf
> > > 
> > > Starting cache manager:
> > >   sqlr-cachemanager
> > > 
> > > 'sqlrsh -id sqlitetest' semms to connect ok:
> > > 
> > > SQLRShell - Version 0.22
> > >         Connected to: localhost:9000 as sqlitetest
> > > 
> > >         type help; for a help.
> > > 
> > > 0>
> > > 
> > > but after running "help;", it just hangs.
> > > 
> > > shutting things down via 'sqlr-stop sqlitetest' doesn't seem to work either:
> > > 
> > > Killing the following processes:
> > > All processes killed.
> > > 
> > > but ps auwwx |grep sqlr shows 6 processes still running.
> > > 
> > > any ideas on what to check or some pointers on how to debug what's going
> > > on?
> > > 
> > > thanks,
> > > - jolan
> > > 
> > > 
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by Yahoo.
> > > Introducing Yahoo! Search Developer Network - Create apps using Yahoo!
> > > Search APIs Find out how you can build Yahoo! directly into your own
> > > Applications - visit http://developer.yahoo.net/?fr=offad-ysdn-ostg-q22005
> > > _______________________________________________
> > > Sqlrelay-discussion mailing list
> > > Sql...@li...
> > > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> > > 
> > 
> > 
> > 
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Yahoo.
> > Introducing Yahoo! Search Developer Network - Create apps using Yahoo!
> > Search APIs Find out how you can build Yahoo! directly into your own
> > Applications - visit http://developer.yahoo.net/?fr=offad-ysdn-ostg-q22005
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Yahoo.
> Introducing Yahoo! Search Developer Network - Create apps using Yahoo!
> Search APIs Find out how you can build Yahoo! directly into your own
> Applications - visit http://developer.yahoo.net/?fr=offad-ysdn-ostg-q22005
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 

[Sqlrelay-discussion] SQL Relay 0.36.3 maintenance release

[David M. <dav...@fi...>]

Hello all,

I just made a maintenance release of SQL Relay: sqlrelay-0.36.3

This release fixes:
	* a bug causing sqlrsh to hang on systems without readline
	* fixes to php pear db driver for autoExecute
	* readline detection succeeds on systems without libhistory now
	* minor bugfix to init script for SCO OpenServer

If you aren't having one of those problems, then you probably don't need
to upgrade.

Have fun.

David Muse
dav...@fi...

Re: [Sqlrelay-discussion] sqlrsh hanging on openbsd

[Jolan L. <jo...@pr...>]

On Mon, May 30, 2005 at 05:42:26PM -0400, David Muse wrote:
> I just figured out the problem with sqlrsh.  sqlrsh can use the readline
> library, and there appears to be a bug in the code that gets compiled if
> the readline library is absent.

thanks for looking into this for me.  openbsd has shipped with readline
for quite some time.  i looked at config.log and the readline tests fail
because the tests try to link with -lhistory which doesn't exist on
openbsd.  the history functions are in -lreadline.

> You can fix the problem by either installing the readline library
> package and rebuilding or by editing src/cmdline/sqlrsh.C and changing:
> 
> #ifndef HAVE_READLINE
> sqlrsh::sqlrsh() {
> 	standardin.setFileDescriptor(0);
> }
> #endif
> 
> to:
> 
> #ifndef HAVE_READLINE
> sqlrsh::sqlrsh() {
> 	standardin.setFileDescriptor(0);
> 	standardin.allowShortReads();
> }
> #endif
> 
> 
> I'll make a maintenance release soon with this fix.
> 
> As for sqlr-stop not working, I'll need to look into that a bit more.
> 
> 
> Dave
> dav...@fi...
> 
> 
> On Thu, 2005-05-26 at 23:42 -0500, Jolan Luff wrote:
> > hi,
> > 
> > i'm trying to get sqlrelay-0.36.2 running on openbsd/i386 with a sqlite3
> > database.
> > 
> > i'm using the sqlrelay.conf from:
> > 
> > http://sqlrelay.sourceforge.net/sqlrelay/gettingstarted/sqlite.html
> > 
> > with the only modifications being the database path and runas
> > group/user.  i am using sqlrelay.dtd from the distribution as well.
> > 
> > the output from 'sqlr-start -id sqlitetest' looks fine:
> > 
> > Starting listener:
> >   sqlr-listener -id sqlitetest -config /etc/pkg/sqlrelay.conf
> > 
> > Starting 3 connections to sqlitetest :
> >   sqlr-connection-sqlite -id sqlitetest -connectionid sqlitetest -config /etc/pkg/sqlrelay.conf
> >   sqlr-connection-sqlite -id sqlitetest -connectionid sqlitetest -config /etc/pkg/sqlrelay.conf
> >   sqlr-connection-sqlite -id sqlitetest -connectionid sqlitetest -config /etc/pkg/sqlrelay.conf
> > 
> > Starting scaler:
> >   sqlr-scaler  -id sqlitetest -config /etc/pkg/sqlrelay.conf
> > 
> > Starting cache manager:
> >   sqlr-cachemanager
> > 
> > 'sqlrsh -id sqlitetest' semms to connect ok:
> > 
> > SQLRShell - Version 0.22
> >         Connected to: localhost:9000 as sqlitetest
> > 
> >         type help; for a help.
> > 
> > 0>
> > 
> > but after running "help;", it just hangs.
> > 
> > shutting things down via 'sqlr-stop sqlitetest' doesn't seem to work either:
> > 
> > Killing the following processes:
> > All processes killed.
> > 
> > but ps auwwx |grep sqlr shows 6 processes still running.
> > 
> > any ideas on what to check or some pointers on how to debug what's going
> > on?
> > 
> > thanks,
> > - jolan
> > 
> > 
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Yahoo.
> > Introducing Yahoo! Search Developer Network - Create apps using Yahoo!
> > Search APIs Find out how you can build Yahoo! directly into your own
> > Applications - visit http://developer.yahoo.net/?fr=offad-ysdn-ostg-q22005
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> > 
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Yahoo.
> Introducing Yahoo! Search Developer Network - Create apps using Yahoo!
> Search APIs Find out how you can build Yahoo! directly into your own
> Applications - visit http://developer.yahoo.net/?fr=offad-ysdn-ostg-q22005
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion

[Sqlrelay-discussion] (new to sqlrelay) : authtier description : what does this mean ?

[santiago <che...@fr...>]

Hello

I was looking for a database connection pooling and found
sqlrelay.

It looks great !

I'm reading the configuring help at :

http://sqlrelay.sourceforge.net/sqlrelay/configuring.html

there's something I dont understand (I'm not native englis speaker,
maybe that's the reason)

in section : Authtier

>When SQL Relay is used with any other database, it simply logs out and
>logs back in as a different user. This is quite ineffecient and defeats
>the value of keeping persistent database connections.

I don't get it : does it mean that the connection deamons close and
reopen connections each time the listenner handles a user to them ?
I'm sure that's not the correct interpretation, because to me,
it means that the client would be just as performent by just openning
a new connection by himself.

what did I get wrong ?


thanks

Re: [Sqlrelay-discussion] sqlrsh hanging on openbsd

[David M. <dav...@fi...>]

I just figured out the problem with sqlrsh.  sqlrsh can use the readline
library, and there appears to be a bug in the code that gets compiled if
the readline library is absent.

You can fix the problem by either installing the readline library
package and rebuilding or by editing src/cmdline/sqlrsh.C and changing:

#ifndef HAVE_READLINE
sqlrsh::sqlrsh() {
	standardin.setFileDescriptor(0);
}
#endif

to:

#ifndef HAVE_READLINE
sqlrsh::sqlrsh() {
	standardin.setFileDescriptor(0);
	standardin.allowShortReads();
}
#endif


I'll make a maintenance release soon with this fix.

As for sqlr-stop not working, I'll need to look into that a bit more.


Dave
dav...@fi...


On Thu, 2005-05-26 at 23:42 -0500, Jolan Luff wrote:
> hi,
> 
> i'm trying to get sqlrelay-0.36.2 running on openbsd/i386 with a sqlite3
> database.
> 
> i'm using the sqlrelay.conf from:
> 
> http://sqlrelay.sourceforge.net/sqlrelay/gettingstarted/sqlite.html
> 
> with the only modifications being the database path and runas
> group/user.  i am using sqlrelay.dtd from the distribution as well.
> 
> the output from 'sqlr-start -id sqlitetest' looks fine:
> 
> Starting listener:
>   sqlr-listener -id sqlitetest -config /etc/pkg/sqlrelay.conf
> 
> Starting 3 connections to sqlitetest :
>   sqlr-connection-sqlite -id sqlitetest -connectionid sqlitetest -config /etc/pkg/sqlrelay.conf
>   sqlr-connection-sqlite -id sqlitetest -connectionid sqlitetest -config /etc/pkg/sqlrelay.conf
>   sqlr-connection-sqlite -id sqlitetest -connectionid sqlitetest -config /etc/pkg/sqlrelay.conf
> 
> Starting scaler:
>   sqlr-scaler  -id sqlitetest -config /etc/pkg/sqlrelay.conf
> 
> Starting cache manager:
>   sqlr-cachemanager
> 
> 'sqlrsh -id sqlitetest' semms to connect ok:
> 
> SQLRShell - Version 0.22
>         Connected to: localhost:9000 as sqlitetest
> 
>         type help; for a help.
> 
> 0>
> 
> but after running "help;", it just hangs.
> 
> shutting things down via 'sqlr-stop sqlitetest' doesn't seem to work either:
> 
> Killing the following processes:
> All processes killed.
> 
> but ps auwwx |grep sqlr shows 6 processes still running.
> 
> any ideas on what to check or some pointers on how to debug what's going
> on?
> 
> thanks,
> - jolan
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Yahoo.
> Introducing Yahoo! Search Developer Network - Create apps using Yahoo!
> Search APIs Find out how you can build Yahoo! directly into your own
> Applications - visit http://developer.yahoo.net/?fr=offad-ysdn-ostg-q22005
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 

Re: [Sqlrelay-discussion] Re: Can sqlrelay handle 1000 tx/s against Oracle

[David M. <dav...@fi...>]

Years ago, we once had an Oracle 7 database running under SCO OpenServer
(I don't remember the exact version) on an ALR 6x6 (6 pentium 200's) and
a raid.  We added extra cache to the raid, but I don't remember how
much.  I think the ALR had 512mb of ram in it.  SQL Relay was also
running on the ALR.  There was a Dell PowerEdge 4200 (I think, I don't
remember how much ram it had) running web-based applications written in
C++ that talked over a 100mbit network to the ALR and a bunch of FoxPro
processes running on the ALR itself that used a program called sqltodbf
to run queries against oracle through SQL Relay and convert the result
set to a dbf that FoxPro could then read.  There were also several
remote Oracle 8 instances running on a Sun UltraSparc with either 2 or 3
300mhz CPU's and clarion raid cabinets and 512mb of ram that other
instances of SQL Relay (also running on the ALR) connected to across the
same network.  The FoxPro apps talked to those instances as well.  The
web server generally ran 10's of cgi's per second but during peak times,
it would ramp up to hundreds and every now and then the web server would
peak at about 1400 cgi's per second, all of which accessed the database.
With the other apps accessing the databases too, sometimes 2 or 3
thousand queries would get run per second.

That's the only instance where I know of the hardware that was used, and
the details are fuzzy in my mind.  It was a long time ago.  We also had
a sysadmin and DBA tuning the database and other hardware constantly.
It took a lot of work to get the performance that we got.  I remember
the amount of cache on the ALR's raid controller being a critical factor
as well as a bunch of SCO's kernel parameters.

I have gotten a few reports of other cases where people had set up a 3
tiered system with lots of web servers running PHP's under linux in the
first tier, a couple of SQL Relay servers running on linux in the middle
tier and an Oracle cluster on the back end running on Solaris.  But I
don't know any more detail than that.

Dave
dav...@fi...

On Fri, 2005-05-27 at 08:26 -0700, A. Guy wrote:
> Hi,
> 
> What hardware, OS was used to run SQL Relay in order
> to achieve 1000 tx/s against Oracle? 
> 
> Thanks.
> Nick
> 
> 
> 		
> __________________________________ 
> Do you Yahoo!? 
> Yahoo! Small Business - Try our new Resources site
> http://smallbusiness.yahoo.com/resources/
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Yahoo.
> Introducing Yahoo! Search Developer Network - Create apps using Yahoo!
> Search APIs Find out how you can build Yahoo! directly into your own
> Applications - visit http://developer.yahoo.net/?fr=offad-ysdn-ostg-q22005
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 

[Sqlrelay-discussion] Re: Can sqlrelay handle 1000 tx/s against Oracle

[A. G. <hot...@ya...>]

Hi,

What hardware, OS was used to run SQL Relay in order
to achieve 1000 tx/s against Oracle? 

Thanks.
Nick


		
__________________________________ 
Do you Yahoo!? 
Yahoo! Small Business - Try our new Resources site
http://smallbusiness.yahoo.com/resources/

[Sqlrelay-discussion] sqlrsh hanging on openbsd

[Jolan L. <jo...@pr...>]

hi,

i'm trying to get sqlrelay-0.36.2 running on openbsd/i386 with a sqlite3
database.

i'm using the sqlrelay.conf from:

http://sqlrelay.sourceforge.net/sqlrelay/gettingstarted/sqlite.html

with the only modifications being the database path and runas
group/user.  i am using sqlrelay.dtd from the distribution as well.

the output from 'sqlr-start -id sqlitetest' looks fine:

Starting listener:
  sqlr-listener -id sqlitetest -config /etc/pkg/sqlrelay.conf

Starting 3 connections to sqlitetest :
  sqlr-connection-sqlite -id sqlitetest -connectionid sqlitetest -config /etc/pkg/sqlrelay.conf
  sqlr-connection-sqlite -id sqlitetest -connectionid sqlitetest -config /etc/pkg/sqlrelay.conf
  sqlr-connection-sqlite -id sqlitetest -connectionid sqlitetest -config /etc/pkg/sqlrelay.conf

Starting scaler:
  sqlr-scaler  -id sqlitetest -config /etc/pkg/sqlrelay.conf

Starting cache manager:
  sqlr-cachemanager

'sqlrsh -id sqlitetest' semms to connect ok:

SQLRShell - Version 0.22
        Connected to: localhost:9000 as sqlitetest

        type help; for a help.

0>

but after running "help;", it just hangs.

shutting things down via 'sqlr-stop sqlitetest' doesn't seem to work either:

Killing the following processes:
All processes killed.

but ps auwwx |grep sqlr shows 6 processes still running.

any ideas on what to check or some pointers on how to debug what's going
on?

thanks,
- jolan

Re: [Sqlrelay-discussion] bind pear php module

[david f. <df...@vi...>]

That would be very nice.

Thanks for your work

livingdead

Le Fri, 20 May 2005 11:40:42 -0400, Firstworks/4access  
<dav...@fi...> a écrit:

> Ahh, I see.
>
> I'll have to look into this a bit further.  Maybe I can get SQL Relay to
> support ?'s in short order.
>
> Dave
>
> On Fri, 2005-05-20 at 17:15 +0200, david forums wrote:
>> Sorry but I'm not the manager of those question mark, it's PEAR DB who  
>> set
>> those question mark automatically, when we are using autoExecute  
>> functions.
>>
>> So I do not know how to change it in PEAR DB, and How it will not break
>> the portability of PEAR DB
>>
>> regards
>>
>> livingdead
>>
>>
>>
>>
>>
>> Le Fri, 20 May 2005 10:57:29 -0400, Firstworks/4access
>> <dav...@fi...> a écrit:
>>
>> > SQL Relay uses Oracle-style bind variables, so try using:
>> >
>> > 	INSERT INTO myTable (col1,col2,col3) VALUES (:1,:2,:3)
>> >
>> > and see if that doesn't work better (using the original code for the
>> > execute function).
>> >
>> > Supporting ?'s is on the TODO list, but I haven't implemented it yet.
>> >
>> > Dave
>> > dav...@fi...
>> >
>> > On Fri, 2005-05-20 at 16:12 +0200, david forums wrote:
>> >> Hello
>> >>
>> >> so Here is the request
>> >>
>> >> $db->autoExecute("myTable", $lstField, DB_AUTOQUERY_INSERT);
>> >>
>> >> this build the several request (inside PEAR DB)
>> >> INSERT INTO myTable (col1,col2,col3) VALUES (?,?,?)
>> >>
>> >> But it seems that when it reach the execute fonction, it's not able  
>> to
>> >> fill the ?, with the right data.
>> >> Analysing the function I discover this :
>> >>
>> >>   function &execute(&$sqlrcursor, $data = false)
>> >>      {
>> >>      	sqlrcur_clearBinds($sqlrcursor->cursor);
>> >>          if ($data) {
>> >>              while ($element = current($data)) {
>> >>                  $index = key($data);
>> >>                  sqlrcur_inputBind($sqlrcursor->cursor, $index,
>> >> $data["$index"]);
>> >>                  next($data);
>> >>              }
>> >>          }
>> >> 	  ..........
>> >>
>> >> I try to modify it like :
>> >>   function &execute(&$sqlrcursor, $data = false)
>> >>      {
>> >>      	sqlrcur_clearBinds($sqlrcursor->cursor);
>> >>          if ($data) {
>> >>              while ($element = current($data)) {
>> >>                  $index = key($data);
>> >>                  if(is_numeric($index)) $index = "?";
>> >>                  sqlrcur_inputBind($sqlrcursor->cursor, $index,
>> >> $data["$index"]);
>> >>                  next($data);
>> >>              }
>> >>          }
>> >>          ..........
>> >>
>> >> without success.
>> >>
>> >> Please thanks for your help
>> >>
>> >> livingdead
>> >>
>> >>
>> >> -------------------------------------------------------
>> >> This SF.Net email is sponsored by Oracle Space Sweepstakes
>> >> Want to be the first software developer in space?
>> >> Enter now for the Oracle Space Sweepstakes!
>> >> http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
>> >> _______________________________________________
>> >> Sqlrelay-discussion mailing list
>> >> Sql...@li...
>> >> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>> >>
>> >
>> >
>> >
>> > -------------------------------------------------------
>> > This SF.Net email is sponsored by Oracle Space Sweepstakes
>> > Want to be the first software developer in space?
>> > Enter now for the Oracle Space Sweepstakes!
>> > http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
>> > _______________________________________________
>> > Sqlrelay-discussion mailing list
>> > Sql...@li...
>> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>> >
>>
>>
>>
>>
>> -------------------------------------------------------
>> This SF.Net email is sponsored by Oracle Space Sweepstakes
>> Want to be the first software developer in space?
>> Enter now for the Oracle Space Sweepstakes!
>> http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
>> _______________________________________________
>> Sqlrelay-discussion mailing list
>> Sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_idt12&alloc_id344&op=click
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>

Re: [Sqlrelay-discussion] bind pear php module

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

Ahh, I see.

I'll have to look into this a bit further.  Maybe I can get SQL Relay to
support ?'s in short order.

Dave

On Fri, 2005-05-20 at 17:15 +0200, david forums wrote:
> Sorry but I'm not the manager of those question mark, it's PEAR DB who =
set =20
> those question mark automatically, when we are using autoExecute functi=
ons.
>=20
> So I do not know how to change it in PEAR DB, and How it will not break=
 =20
> the portability of PEAR DB
>=20
> regards
>=20
> livingdead
>=20
>=20
>=20
>=20
>=20
> Le Fri, 20 May 2005 10:57:29 -0400, Firstworks/4access =20
> <dav...@fi...> a =C3=A9crit:
>=20
> > SQL Relay uses Oracle-style bind variables, so try using:
> >
> > 	INSERT INTO myTable (col1,col2,col3) VALUES (:1,:2,:3)
> >
> > and see if that doesn't work better (using the original code for the
> > execute function).
> >
> > Supporting ?'s is on the TODO list, but I haven't implemented it yet.
> >
> > Dave
> > dav...@fi...
> >
> > On Fri, 2005-05-20 at 16:12 +0200, david forums wrote:
> >> Hello
> >>
> >> so Here is the request
> >>
> >> $db->autoExecute("myTable", $lstField, DB_AUTOQUERY_INSERT);
> >>
> >> this build the several request (inside PEAR DB)
> >> INSERT INTO myTable (col1,col2,col3) VALUES (?,?,?)
> >>
> >> But it seems that when it reach the execute fonction, it's not able =
to
> >> fill the ?, with the right data.
> >> Analysing the function I discover this :
> >>
> >>   function &execute(&$sqlrcursor, $data =3D false)
> >>      {
> >>      	sqlrcur_clearBinds($sqlrcursor->cursor);
> >>          if ($data) {
> >>              while ($element =3D current($data)) {
> >>                  $index =3D key($data);
> >>                  sqlrcur_inputBind($sqlrcursor->cursor, $index,
> >> $data["$index"]);
> >>                  next($data);
> >>              }
> >>          }
> >> 	  ..........
> >>
> >> I try to modify it like :
> >>   function &execute(&$sqlrcursor, $data =3D false)
> >>      {
> >>      	sqlrcur_clearBinds($sqlrcursor->cursor);
> >>          if ($data) {
> >>              while ($element =3D current($data)) {
> >>                  $index =3D key($data);
> >>                  if(is_numeric($index)) $index =3D "?";
> >>                  sqlrcur_inputBind($sqlrcursor->cursor, $index,
> >> $data["$index"]);
> >>                  next($data);
> >>              }
> >>          }
> >>          ..........
> >>
> >> without success.
> >>
> >> Please thanks for your help
> >>
> >> livingdead
> >>
> >>
> >> -------------------------------------------------------
> >> This SF.Net email is sponsored by Oracle Space Sweepstakes
> >> Want to be the first software developer in space?
> >> Enter now for the Oracle Space Sweepstakes!
> >> http://ads.osdn.com/?ad_id=3D7412&alloc_id=3D16344&op=3Dclick
> >> _______________________________________________
> >> Sqlrelay-discussion mailing list
> >> Sql...@li...
> >> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >>
> >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_id=3D7412&alloc_id=3D16344&op=3Dclick
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >
>=20
>=20
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7412&alloc_id=3D16344&op=3Dclick
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>=20

Re: [Sqlrelay-discussion] bind pear php module

[david f. <df...@vi...>]

Sorry but I'm not the manager of those question mark, it's PEAR DB who set  
those question mark automatically, when we are using autoExecute functions.

So I do not know how to change it in PEAR DB, and How it will not break  
the portability of PEAR DB

regards

livingdead





Le Fri, 20 May 2005 10:57:29 -0400, Firstworks/4access  
<dav...@fi...> a écrit:

> SQL Relay uses Oracle-style bind variables, so try using:
>
> 	INSERT INTO myTable (col1,col2,col3) VALUES (:1,:2,:3)
>
> and see if that doesn't work better (using the original code for the
> execute function).
>
> Supporting ?'s is on the TODO list, but I haven't implemented it yet.
>
> Dave
> dav...@fi...
>
> On Fri, 2005-05-20 at 16:12 +0200, david forums wrote:
>> Hello
>>
>> so Here is the request
>>
>> $db->autoExecute("myTable", $lstField, DB_AUTOQUERY_INSERT);
>>
>> this build the several request (inside PEAR DB)
>> INSERT INTO myTable (col1,col2,col3) VALUES (?,?,?)
>>
>> But it seems that when it reach the execute fonction, it's not able to
>> fill the ?, with the right data.
>> Analysing the function I discover this :
>>
>>   function &execute(&$sqlrcursor, $data = false)
>>      {
>>      	sqlrcur_clearBinds($sqlrcursor->cursor);
>>          if ($data) {
>>              while ($element = current($data)) {
>>                  $index = key($data);
>>                  sqlrcur_inputBind($sqlrcursor->cursor, $index,
>> $data["$index"]);
>>                  next($data);
>>              }
>>          }
>> 	  ..........
>>
>> I try to modify it like :
>>   function &execute(&$sqlrcursor, $data = false)
>>      {
>>      	sqlrcur_clearBinds($sqlrcursor->cursor);
>>          if ($data) {
>>              while ($element = current($data)) {
>>                  $index = key($data);
>>                  if(is_numeric($index)) $index = "?";
>>                  sqlrcur_inputBind($sqlrcursor->cursor, $index,
>> $data["$index"]);
>>                  next($data);
>>              }
>>          }
>>          ..........
>>
>> without success.
>>
>> Please thanks for your help
>>
>> livingdead
>>
>>
>> -------------------------------------------------------
>> This SF.Net email is sponsored by Oracle Space Sweepstakes
>> Want to be the first software developer in space?
>> Enter now for the Oracle Space Sweepstakes!
>> http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
>> _______________________________________________
>> Sqlrelay-discussion mailing list
>> Sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>

Re: [Sqlrelay-discussion] bind pear php module

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

SQL Relay uses Oracle-style bind variables, so try using:

	INSERT INTO myTable (col1,col2,col3) VALUES (:1,:2,:3)

and see if that doesn't work better (using the original code for the
execute function).

Supporting ?'s is on the TODO list, but I haven't implemented it yet.

Dave
dav...@fi...

On Fri, 2005-05-20 at 16:12 +0200, david forums wrote:
> Hello
> 
> so Here is the request
> 
> $db->autoExecute("myTable", $lstField, DB_AUTOQUERY_INSERT);
> 
> this build the several request (inside PEAR DB)
> INSERT INTO myTable (col1,col2,col3) VALUES (?,?,?)
> 
> But it seems that when it reach the execute fonction, it's not able to  
> fill the ?, with the right data.
> Analysing the function I discover this :
> 
>   function &execute(&$sqlrcursor, $data = false)
>      {
>      	sqlrcur_clearBinds($sqlrcursor->cursor);
>          if ($data) {
>              while ($element = current($data)) {
>                  $index = key($data);
>                  sqlrcur_inputBind($sqlrcursor->cursor, $index,  
> $data["$index"]);
>                  next($data);
>              }
>          }
> 	  ..........
> 
> I try to modify it like :
>   function &execute(&$sqlrcursor, $data = false)
>      {
>      	sqlrcur_clearBinds($sqlrcursor->cursor);
>          if ($data) {
>              while ($element = current($data)) {
>                  $index = key($data);
>                  if(is_numeric($index)) $index = "?";
>                  sqlrcur_inputBind($sqlrcursor->cursor, $index,  
> $data["$index"]);
>                  next($data);
>              }
>          }
>          ..........
> 
> without success.
> 
> Please thanks for your help
> 
> livingdead
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 

Re: [Sqlrelay-discussion] performance

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

On Fri, 2005-05-20 at 16:05 +0200, david forums wrote:
> Hi
> 
> Concerning performance I still have some difficulties. But I suppose My  
> settings need surely to be re-set.
> 
> So I'll give you my lines of settings, could you please help me to  
> increase the perfomance
> 
> <instance id="impMysql" port="9000" socket="/tmp/impMysql.socket"  
> dbase="mysql" connections="15" maxconnections="45" maxqueuelength="5000"  
> growby="5" ttl="60" endofsession="commit" sessiontimeout="600"  
> runasuser="nobody" runasgroup="nobody" cursors="1000" authtier="listener"  
> handoff="pass" deniedips="" allowedips="" debug="none">
> 		<users>
> 			<user user="user" password="pass"/>
> 		</users>
> 		<connections>
> 			<connection connectionid="impMysql"  
> string="user=user;password=pass;db=mytable;socket=/my/path/to/mysql.sock"  
> metric="15"/>
> 		</connections>
> 	</instance>

Try setting maxqueuelength to 0 or some small number.  The
maxqueuelength parameter is the number of clients that are allowed to
queue up before the server will spawn another connection to the
database.  With connections=15 and maxqueuelength=5000, 5015 clients
would need to be connected (5000 of which would be waiting) before
another database connection would be started.

Also you probably don't need 1000 cursors.  The cursors parameter is the
maximum number of nested queries that will be run by a connected client.

For example, if a client does this:

sqlrcur	cur1(&con);
cur1.setResultSetBufferSize(1);
cur1.sendQuery("select * from table1");
for (int i=0; i<100; i++) {
	sqlrcur cur2(&con);
	cur2.setResultSetBufferSize(1);
	cur2.sendQuery("select * from table2");
	for (int j=0; j<100; j++) {
		sqlrcur cur3(&con);
		cur3.setResultSetBufferSize(1);
		cur3.sendQuery("select * from table3");
	}
}

Then cursors will need to be set to 3.  A single client would have to
run 1000 queries simultaneously (without caching the rows locally, ie.
using setResultSetBufferSize(1)) to need cursors=1000.  Each cursor
requires some ram too, so if you have 1000 of them allocated and are
running 15 connections, that 15000 cursors.  It's possible that
allocating that much ram could lead to swapping.

Try these recommendations with your postgresql connection too.

Let me know how it goes.

David Muse
dav...@fi...

> 	<instance id="mainPostgres" port="9001" socket="/tmp/mainPostgres.socket"  
> dbase="postgresql" connections="15" maxconnections="200"  
> maxqueuelength="5000" growby="5" ttl="60" endofsession="commit"  
> sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="1000"  
> authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none">
> 		<users>
> 			<user user="user" password="pass"/>
> 		</users>
> 		<connections>
> 			<connection connectionid="mainPostgres"  
> string="socket=/my/path/postmaster.5432.pid;db=mtbase;user=user;passw  
> passsord=pass;port=5432;" metric="15"/>
> 		</connections>
> 	</instance>
> 
> I did some benchmark using sqlb with really defineltly much better result,  
> so I must have mis-setted something.
> 
> Regards
> 
> livingdead
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 

Re: [Sqlrelay-discussion] bind pear php module

[david f. <df...@vi...>]

Oups the modification was idiot, I was to fast, I finaly did that :
  if ($data) {
             while ($element = current($data)) {
                 $index = key($data);
                 $mark = (is_numeric($index))?"?":$index;
                 sqlrcur_inputBind($sqlrcursor->cursor, $mark,  
$data["$index"]);
                 next($data);
             }
         }
         .........

Without much success

livingdead

Le Fri, 20 May 2005 16:12:24 +0200, david forums <df...@vi...> a  
écrit:

> Hello
>
> so Here is the request
>
> $db->autoExecute("myTable", $lstField, DB_AUTOQUERY_INSERT);
>
> this build the several request (inside PEAR DB)
> INSERT INTO myTable (col1,col2,col3) VALUES (?,?,?)
>
> But it seems that when it reach the execute fonction, it's not able to  
> fill the ?, with the right data.
> Analysing the function I discover this :
>
>   function &execute(&$sqlrcursor, $data = false)
>      {
>      	sqlrcur_clearBinds($sqlrcursor->cursor);
>          if ($data) {
>              while ($element = current($data)) {
>                  $index = key($data);
>                  sqlrcur_inputBind($sqlrcursor->cursor, $index,  
> $data["$index"]);
>                  next($data);
>              }
>          }
> 	  ..........
>
> I try to modify it like :
>   function &execute(&$sqlrcursor, $data = false)
>      {
>      	sqlrcur_clearBinds($sqlrcursor->cursor);
>          if ($data) {
>              while ($element = current($data)) {
>                  $index = key($data);
>                  if(is_numeric($index)) $index = "?";
>                  sqlrcur_inputBind($sqlrcursor->cursor, $index,  
> $data["$index"]);
>                  next($data);
>              }
>          }
>          ..........
>
> without success.
>
> Please thanks for your help
>
> livingdead
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>

[Sqlrelay-discussion] bind pear php module

[david f. <df...@vi...>]

Hello

so Here is the request

$db->autoExecute("myTable", $lstField, DB_AUTOQUERY_INSERT);

this build the several request (inside PEAR DB)
INSERT INTO myTable (col1,col2,col3) VALUES (?,?,?)

But it seems that when it reach the execute fonction, it's not able to  
fill the ?, with the right data.
Analysing the function I discover this :

  function &execute(&$sqlrcursor, $data = false)
     {
     	sqlrcur_clearBinds($sqlrcursor->cursor);
         if ($data) {
             while ($element = current($data)) {
                 $index = key($data);
                 sqlrcur_inputBind($sqlrcursor->cursor, $index,  
$data["$index"]);
                 next($data);
             }
         }
	  ..........

I try to modify it like :
  function &execute(&$sqlrcursor, $data = false)
     {
     	sqlrcur_clearBinds($sqlrcursor->cursor);
         if ($data) {
             while ($element = current($data)) {
                 $index = key($data);
                 if(is_numeric($index)) $index = "?";
                 sqlrcur_inputBind($sqlrcursor->cursor, $index,  
$data["$index"]);
                 next($data);
             }
         }
         ..........

without success.

Please thanks for your help

livingdead

[Sqlrelay-discussion] performance

[david f. <df...@vi...>]

Hi

Concerning performance I still have some difficulties. But I suppose My  
settings need surely to be re-set.

So I'll give you my lines of settings, could you please help me to  
increase the perfomance

<instance id="impMysql" port="9000" socket="/tmp/impMysql.socket"  
dbase="mysql" connections="15" maxconnections="45" maxqueuelength="5000"  
growby="5" ttl="60" endofsession="commit" sessiontimeout="600"  
runasuser="nobody" runasgroup="nobody" cursors="1000" authtier="listener"  
handoff="pass" deniedips="" allowedips="" debug="none">
		<users>
			<user user="user" password="pass"/>
		</users>
		<connections>
			<connection connectionid="impMysql"  
string="user=user;password=pass;db=mytable;socket=/my/path/to/mysql.sock"  
metric="15"/>
		</connections>
	</instance>
	<instance id="mainPostgres" port="9001" socket="/tmp/mainPostgres.socket"  
dbase="postgresql" connections="15" maxconnections="200"  
maxqueuelength="5000" growby="5" ttl="60" endofsession="commit"  
sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="1000"  
authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none">
		<users>
			<user user="user" password="pass"/>
		</users>
		<connections>
			<connection connectionid="mainPostgres"  
string="socket=/my/path/postmaster.5432.pid;db=mtbase;user=user;passw  
passsord=pass;port=5432;" metric="15"/>
		</connections>
	</instance>

I did some benchmark using sqlb with really defineltly much better result,  
so I must have mis-setted something.

Regards

livingdead

Re: [Sqlrelay-discussion] php 5, oracle 9 and CLOBS

[Ionut C. <ion...@av...>]

It happened to us too. We were pretty happy at first that we could fetch=20
clobs without writing sepcial code for it.

Then we noticed exactly what's been happening to you, so now we fetch clo=
bs=20
the "right" oci way, in separate result sets with output binding.

The buffer size is ment for something else: fetching the results in separ=
ate=20
row chunks, one chunk after the other. It takes some extra-programming, b=
ut=20
it's a nice feature as it's a real memory-saver when dealing with large=20
result sets.
We also found another nifty usage for it with oracle: emulating limit and=
=20
offset in queries.

Ionut

----- Original Message -----=20
From: "Emmanuel Job" <emm...@gl...>
To: <sql...@li...>
Sent: Wednesday, May 18, 2005 1:18 AM
Subject: [Sqlrelay-discussion] php 5, oracle 9 and CLOBS


Im having problems when I retreive CLOBS from oracle and I was wondering =
if=20
anyone else noticed this. Im not using any output bindings and I find tha=
t=20
with small result sets everything works as I expect it to. However when I=
=20
increase the size of the data returned I sometimes dont get the full resu=
lt=20
set back and the application crashes. By changing the order of the column=
s=20
in my sql statement I sometimes get things working again however I can't=20
seem to find a consistent solution to retrieving large result sets that=20
contain CLOBS. I've tried messing with the result set buffer size which o=
nly=20
confuses me more because it returns only the number of rows that are aske=
d=20
for in the buffer.

Below is a code sample to show that Im not trying to do anything=20
complicated. The script usually crashes at about the 170th record in the=20
first for loop. If I change the buffer size to 50 it'll usually crash on =
the=20
51st record. Again, its all very weird because its very inconsistent.

$connection =3D sqlrcon_alloc("localhost", "9000",
    "",
    "test", "test", 0,1);

$cursor =3D sqlrcur_alloc($connection);
sqlrcur_dontGetColumnInfo($cursor);
sqlrcur_setResultSetBufferSize($cursor, 200);


// col1
$sql =3D "select id, col1, col2, col3, col4, col5  from test";

if (!sqlrcur_sendQuery($cursor, $sql)) {
    echo "Error: " . sqlrcur_errorMessage($cursor);
}

$results =3D array();
for ($i =3D 0; $i < 500; $i++) {
    echo "$i<br/>";flush();
    $results[] =3D sqlrcur_getRow($cursor, $i);
}



$count =3D 0;
foreach ($results as $result) {
    echo "ID $count: " . $result[0] . " <br/>";
    $count++;
}

echo "DONE";



I should also that I haven't used output binding because I cant seem to g=
et=20
CLOBs and other column types in the same result set.



-------------------------------------------------------
This SF.Net email is sponsored by Oracle Space Sweepstakes
Want to be the first software developer in space?
Enter now for the Oracle Space Sweepstakes!
http://ads.osdn.com/?ad_idt12&alloc_id=16344&op=3Dick
_______________________________________________
Sqlrelay-discussion mailing list
Sql...@li...
https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion=20

[Sqlrelay-discussion] php 5, oracle 9 and CLOBS

[Emmanuel J. <emm...@gl...>]

Im having problems when I retreive CLOBS from oracle and I was wondering =
if anyone else noticed this. Im not using any output bindings and I find =
that with small result sets everything works as I expect it to. However =
when I increase the size of the data returned I sometimes dont get the =
full result set back and the application crashes. By changing the order =
of the columns in my sql statement I sometimes get things working again =
however I can't seem to find a consistent solution to retrieving large =
result sets that contain CLOBS. I've tried messing with the result set =
buffer size which only confuses me more because it returns only the =
number of rows that are asked for in the buffer.

Below is a code sample to show that Im not trying to do anything =
complicated. The script usually crashes at about the 170th record in the =
first for loop. If I change the buffer size to 50 it'll usually crash on =
the 51st record. Again, its all very weird because its very =
inconsistent.

$connection =3D sqlrcon_alloc("localhost", "9000",=20
			    "",=20
			    "test", "test", 0,1);

$cursor =3D sqlrcur_alloc($connection);
sqlrcur_dontGetColumnInfo($cursor);
sqlrcur_setResultSetBufferSize($cursor, 200);


// col1
$sql =3D "select id, col1, col2, col3, col4, col5  from test";

if (!sqlrcur_sendQuery($cursor, $sql)) {
    echo "Error: " . sqlrcur_errorMessage($cursor);
}

$results =3D array();
for ($i =3D 0; $i < 500; $i++) {
    echo "$i<br/>";flush();
    $results[] =3D sqlrcur_getRow($cursor, $i);
}



$count =3D 0;
foreach ($results as $result) {
    echo "ID $count: " . $result[0] . " <br/>";
    $count++;
}

echo "DONE";



I should also that I haven't used output binding because I cant seem to =
get CLOBs and other column types in the same result set.=20

[Sqlrelay-discussion] SQL Relay 0.36.2

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

Hello all,

I just made a maintenance release of SQL Relay - version 0.36.2

The only change in this release is the addition of -lnnz10 to the list
of libraries linked against when building against Oracle instantclient.
Hopefully instantclient will work on all platforms now.  Let me know if
it doesn't :)

Dave

Re: [Sqlrelay-discussion] Can sqlrelay handle 1000 tx/s against oracle?

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

Simon,

I believe that SQL Relay can handle the load you're describing.  I've
gotten reports that it can handle thousands of queries per second and
I've seen a few cases firsthand.  You'll probably have to tune it a bit:

http://sqlrelay.sourceforge.net/sqlrelay/tuning.html

And you'll have to make sure the machine(s) it's running on has/have
enough ram, but it should do what you want.

Dave Muse
dav...@fi...


On Fri, 2005-05-13 at 11:07 -0400, simon martin wrote:
> Hi List,
> 
> I have a customer who has 150 branches, each branch has about 10 POS
> (Point Of Sales) terminals (PC + Software). I wrote the system that sits
> in between the POS and all the back end servers (JDE OneWorld, Credit
> System, external information providers, etc.). Both OneWorld and the
> Credit System use oracle 9i as a data store, the credit system is
> actually written in PL/SQL. The oracle database engine is running on an
> RS6000, 8 processor system using AIX 5.2.
> 
> At peek times we are pushing about 1000 transactions per second through
> the database (every POS service requires about 20 transactions, of which
> about 10% go to oracle). On average a transaction will take about 500ms
> to complete, except credit payments which touch about 30 tables and take
> about 1500ms under normal circumstances and up to 5s every 10th day of
> the month (by default the credit payments fall on the 5th, 15th, 25th
> day of the month).
> 
> To handle this transaction volume I wrote my own connection pooling
> software. It has it's problems (like all software) but it's stable
> (haven't had to touch it in the last 2 years). Unfortunately it only
> allows me to call stored procedures or functions, and I now have a
> requirement to perform queries directly on the underlying tables.
> 
> For a while I have been contemplating using sqlrelay with this
> customer's application, having used it successfully elsewhere, but I
> don't know whether it'll take this kind of punishment.
> 
> Can anyone shed any light on this? Anyone had any similar requirements?
> 
> TIA
> 

[Sqlrelay-discussion] Can sqlrelay handle 1000 tx/s against oracle?

[simon m. <sm...@mi...>]

Hi List,

I have a customer who has 150 branches, each branch has about 10 POS
(Point Of Sales) terminals (PC + Software). I wrote the system that sits
in between the POS and all the back end servers (JDE OneWorld, Credit
System, external information providers, etc.). Both OneWorld and the
Credit System use oracle 9i as a data store, the credit system is
actually written in PL/SQL. The oracle database engine is running on an
RS6000, 8 processor system using AIX 5.2.

At peek times we are pushing about 1000 transactions per second through
the database (every POS service requires about 20 transactions, of which
about 10% go to oracle). On average a transaction will take about 500ms
to complete, except credit payments which touch about 30 tables and take
about 1500ms under normal circumstances and up to 5s every 10th day of
the month (by default the credit payments fall on the 5th, 15th, 25th
day of the month).

To handle this transaction volume I wrote my own connection pooling
software. It has it's problems (like all software) but it's stable
(haven't had to touch it in the last 2 years). Unfortunately it only
allows me to call stored procedures or functions, and I now have a
requirement to perform queries directly on the underlying tables.

For a while I have been contemplating using sqlrelay with this
customer's application, having used it successfully elsewhere, but I
don't know whether it'll take this kind of punishment.

Can anyone shed any light on this? Anyone had any similar requirements?

TIA

--=20
    __ _   Debian GNU User   Simon Martin
   / /(_)_ __  _   ___  __   Project Manager
  / / | | '_ \| | | \ \/ /   Milliways=20
 / /__| | | | | |_| |>  <    mailto: sm...@mi...
 \____/_|_| |_|\__,_/_/\_\   ICQ: 81183862

Re: [Sqlrelay-discussion] Re:Oracle Instant Client

[Ionut C. <ion...@av...>]

Well,
With the 10.1.0.3 instant client, we got the following:

configure:20645: checking for oracle includes and libraries
configure:20915: result: yes
configure:20926: checking if Oracle has oci.h
configure:20960: g++ -o 
onftest   -Wall  -I/usr/include/oracle/10.1.0.3/client
 conftest.cc -L/usr/lib/oracle/10.1.0.3/client/lib -lclntsh  >&5
/usr/bin/ld: warning: libnnz10.so, needed by 
/usr/lib/oracle/10.1.0.3/client/lib
/libclntsh.so, not found (try using -rpath or -rpath-link)
/usr/lib/oracle/10.1.0.3/client/lib/libclntsh.so: undefined reference to 
`nzos_S
erviceWriteQueue'

Don't know about the previous version(s) though.

Ionut

----- Original Message ----- 
From: "Firstworks/4access" <dav...@fi...>
To: <sql...@li...>
Sent: Friday, May 13, 2005 5:00 PM
Subject: Re: [Sqlrelay-discussion] Re:Oracle Instant Client


> So, libclntsh requires libnnz10?
>
> That's strange.  I'd swear that it worked for me without that library.
> I'll update the configure script and release 0.36.2 ASAP.
>
> Dave
>
> On Fri, 2005-05-13 at 16:53 +0300, Ionut Ciocirlan wrote:
>> We had the same problem, using sqlrelay-0.36.1. and the oracle
>> instantclient-10.1.0.3 base and devel
>>
>> The thing is, it didn't work right out of the box; and the patch David
>> posted probably worked for the previous version of sqlrelay.
>>
>> We solved the problem by first creating the symlink you were talking 
>> about
>> (we usually just symlink all the .so-s that have the version number after
>> the extension, just to be sure):
>>
>> cd /usr/lib/oracle/$client_version/client/lib
>> #maybe someone can write this nicer
>> for i in `ls | grep -E '^(lib[a-z0-9_]+\.so)\.[0-9\.]+$'`; do echo $i |
>> gawk -F .so '{print "ln -s "$i" "$1".so"}' | /bin/bash; done
>>
>> Then, libclntsh needs libnnz10, but as that's not specified in the 
>> current
>> sqlrelay configure file, the ./configure would error at testing oci.h
>> availability.
>> Just go to line 20893 (note that this is the line no. for the 0.36.1.
>> version) of configure, and locate:
>> ORACLELIBS="-L/usr/lib/oracle/$version/client/lib -lclntsh"
>> and add -lnnz10 at the end of it:
>> ORACLELIBS="-L/usr/lib/oracle/$version/client/lib -lclntsh -lnnz10"
>>
>> That's it, configure and make should work nicely now.
>>
>> Ionut
>>
>> ----- Original Message ----- 
>> From: "A. Guy" <hot...@ya...>
>> To: <sql...@li...>
>> Sent: Sunday, May 01, 2005 4:41 PM
>> Subject: [Sqlrelay-discussion] Re:Oracle Instant Client
>>
>>
>> > If I am not mistaken, it will work "out of box".
>> > I might had to create an extra symlink - depending on
>> > the Linux distro.
>> >
>> > Art
>> >
>> >> Before I spend the time to make it work, has anyone
>> >> already created a
>> >> patch so that sqlrelay will compile using Oracle's
>> >> instant client
>> >> libraries?  (Specifically the
>> >> oracle-instantclient-devel RPM for linux).
>> >>
>> >>
>> >> David
>> >
>> > __________________________________________________
>> > Do You Yahoo!?
>> > Tired of spam?  Yahoo! Mail has the best spam protection around
>> > http://mail.yahoo.com
>> >
>> >
>> > -------------------------------------------------------
>> > This SF.Net email is sponsored by: NEC IT Guy Games.
>> > Get your fingers limbered up and give it your best shot. 4 great 
>> > events, 4
>> > opportunities to win big! Highest score wins.NEC IT Guy Games. Play to
>> > win an NEC 61 plasma display. Visit http://www.necitguy.com/?r=20
>> > _______________________________________________
>> > Sqlrelay-discussion mailing list
>> > Sql...@li...
>> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>> >
>>
>>
>>
>> -------------------------------------------------------
>> This SF.Net email is sponsored by Oracle Space Sweepstakes
>> Want to be the first software developer in space?
>> Enter now for the Oracle Space Sweepstakes!
>> http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
>> _______________________________________________
>> Sqlrelay-discussion mailing list
>> Sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion 

Re: [Sqlrelay-discussion] SQL RELAY suitability

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

David,

Are you sure those errors aren't the result of a programming error?
I've seen the first one before when I was first working with blobs I
think.  I didn't close or deallocate something before trying to run the
next query.  Or something like that.

If you do need a connection pooling system, then you could install SQL
Relay on the database server like you describe, but you won't be able to
access it from PHP's running on windows (yet).  SQL Relay's PHP API's
require the sqlrelay client library which currently doesn't work very
well on windows.  It compiles and you can run some programs under Cygwin
with it, but I haven't been able to get it to work with PHP yet.

Sorry for the bad news.

David Muse
dav...@fi...

On Fri, 2005-05-13 at 11:48 +0100, David Hills wrote:
> Good Morning
> 
> Would SQL RELAY be of help to me for the configuration and problem
> below.
> 
> I'm running apache and php on win200 server and oracle 9i on redhat
> linux.
> I'm getting some
> 
> OCISessionBegin: ORA-03127: 
> no new operations allowed until the active operation ends in
> and
> OCIFetch: ORA-01001: 
> invalid cursor 
> 
> errors which I think could be resolved using connection pooling.
> 
> SQL RELAY would go on the linux server with oracle, would this
> configuration
> make sense and work.
> 
> thanks
> 
> David Hills
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>