RE: [Sqlrelay-discussion] DBI Performance

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Hi Dave,

I think that C vs Perl is quite likely to be the issue, unless it's simply
the overhead of communication via the SQL Relay listener. Our DB runs on
different servers, and we are running SQL Relay on our web servers. The
reason for this is that we wanted to use SQL Relay to pool connections to
two DB servers - a Read/Write master and a Read Only slave. Two instances of
SQL Relay would be set up on each of our four web servers, allowing us to
choose a R/W or R/O connection from our code, based on the type of queries
to be executed. We can then tweak the load across the DB servers to match
the ratio of R/W and R/O load by altering the number of R/O connections we
open to the master.

Would it be more efficient to run SQL Relay on one of the DB servers
instead?

We're using the following as one of our DBI connection strings:

DBI:SQLRelay:host=localhost;port=3307;socket=/tmp/sqlrelay-ro.socket

And the following as the corresponding SQLRelay instance tag:

<instance id="readonly" port="3307" socket="/tmp/sqlrelay-ro.socket"
dbase="mysql" connections="5" maxconnections="30" maxqueuelength
="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600"
runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener"
handoff="pass" den
iedips="" allowedips="" debug="none">

To migrate our code to SQL Relay, we're currently using the SQL Relay DBI
driver, but once we've refactored all our code to use our new wrapper, we
can rewrite the wrapper to use the standard SQL Relay API. Perhaps we'll see
a perforamnce increase then?

David

-----Original Message-----
From: Firstworks/4access [mailto:dav...@fi...]
Sent: 16 November 2005 17:10
To: sql...@li...
Subject: Re: [Sqlrelay-discussion] DBI Performance

If I'm not mistaken, the MySQL DBI driver is written in C, while the SQL
Relay DBI driver is written in perl.  It's possible that could account
for the difference.  The DBI driver should use the socket parameter.
What connect string are you using?  Also, is SQL Relay running on the
same machine as the database?  If so, are you sure it's configured to
use the unix socket?

Dave
dav...@fi...

On Wed, 2005-11-09 at 10:07 +0000, David Edwards wrote:
> Thanks, David. Looking forward to 0.37!
> 
> On a slightly different topic, I have briefly tried benchmarking the SQL
> Relay DBI driver against the standard MySQL DBI driver. Obviously there
are
> a huge number of variables involved - I am running SQL Relay locally on
our
> web server connecting to a DB on a different machine, for example - but I
> was surprised to find SQL Relay marginally slower. On average,
transactions
> via SQL relay took 0.16 secs vs 0.13 secs direct. I was hoping for a
slight
> improvement through connection pooling. Are there any tweaks I could try
not
> listed on your performance tuning page? Does the socket parameter get used
> by the DBI driver? Or would I be best off rewriting my DB wrapper to use
the
> SQL Relay API directly rather than via DBI?
> 
> David
> 
> -----Original Message-----
> From: David Muse [mailto:dav...@fi...]
> Sent: 08 November 2005 04:04
> To: sql...@li...
> Subject: RE: [Sqlrelay-discussion] Perl compilation error with 0.36.4
> 
> 
> Hmmm, I'll have to look into that a bit deeper.  Adding a $ in front
> will have the effect of requiring the 3rd argument to be the string
> "SQLRelay::SQL_CLOB" to bind a clob.  I'm not sure that's right, but if
> you don't use clob's then go with it :)
> 
> I'll figure out what's what though and fix it in the 0.37 release.
> 
> Dave
> dav...@fi...
> 
> On Fri, 2005-11-04 at 15:03 +0000, David Edwards wrote:
> > Hi David,
> > 
> > Thanks for replying. I've tried the replacement file, but still got the
> same
> > compilation error. I did try inserting a $ in front of
> > DBD::SQLRelay::SQL_CLOB and DBD::SQLRelay::SQL_BLOB on lines 282 and
294,
> > and this did get rid of the compilation error. However, I don't know
> whether
> > the resulting code does what it's supposed to do, because I'm not using
> > CLOBs or BLOBs.
> > 
> > Regards,
> > 
> > David 
> > 
> > -----Original Message-----
> > From: Firstworks/4access [mailto:dav...@fi...]
> > Sent: 04 November 2005 14:48
> > To: sql...@li...
> > Subject: Re: [Sqlrelay-discussion] Perl compilation error with 0.36.4
> > 
> > 
> > Hmm, looks like DBD::SQLReay::SQL_CLOB and SQL_BLOB aren't defined.  Try
> > replacing /usr/lib/perl5/site_perl/5.6.1/DBD/SQLRelay.pm with the
> > attached file and see if it works.
> > 
> > David Muse
> > dav...@fi...
> > 
> > On Thu, 2005-11-03 at 10:56 +0000, David Edwards wrote:
> > > I am trying to use SQL Relay via the DBI driver from a simple Perl
> script,
> > > but have run into a problem. On our development environment - using
SQL
> > > Relay 0.33.1 - we have no problems. However, using SQL Relay 0.36.4 on
> our
> > > production environment, trying to run the same script results in the
> > > following Perl error:
> > > 
> > > install_driver(SQLRelay) failed: Bareword "DBD::SQLRelay::SQL_CLOB"
not
> > > allowed while "strict subs" in use at
> > > /usr/lib/perl5/site_perl/5.6.1/DBD/SQLRelay.pm line 282.
> > > Bareword "DBD::SQLRelay::SQL_BLOB" not allowed while "strict subs" in
> use
> > at
> > > /usr/lib/perl5/site_perl/5.6.1/DBD/SQLRelay.pm line 282.
> > > Bareword "DBD::SQLRelay::SQL_CLOB" not allowed while "strict subs" in
> use
> > at
> > > /usr/lib/perl5/site_perl/5.6.1/DBD/SQLRelay.pm line 294.
> > > Bareword "DBD::SQLRelay::SQL_BLOB" not allowed while "strict subs" in
> use
> > at
> > > /usr/lib/perl5/site_perl/5.6.1/DBD/SQLRelay.pm line 294.
> > > Compilation failed in require at (eval 4) line 3.
> > > 
> > > Is this an issue with our Perl, or a problem with the SQL Relay code?
If
> > the
> > > former, does anyone have any suggestions as to how to solve the
problem?
> > > 
> > > Thanks,
> > > 
> > > David
> > > 
> > > 
> > > 
> > > -------------------------------------------------------
> > > SF.Net email is sponsored by:
> > > Tame your development challenges with Apache's Geronimo App Server.
> > Download
> > > it for free - -and be entered to win a 42" plasma tv or your very own
> > > Sony(tm)PSP.  Click here to play: http://sourceforge.net/geronimo.php
> > > _______________________________________________
> > > Sqlrelay-discussion mailing list
> > > Sql...@li...
> > > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> > > 
> > 
> > 
> > -------------------------------------------------------
> > SF.Net email is sponsored by:
> > Tame your development challenges with Apache's Geronimo App Server.
> Download
> > it for free - -and be entered to win a 42" plasma tv or your very own
> > Sony(tm)PSP.  Click here to play: http://sourceforge.net/geronimo.php
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> > 
> 
> 
> 
> -------------------------------------------------------
> SF.Net email is sponsored by:
> Tame your development challenges with Apache's Geronimo App Server.
Download
> it for free - -and be entered to win a 42" plasma tv or your very own
> Sony(tm)PSP.  Click here to play: http://sourceforge.net/geronimo.php
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 
> 
> -------------------------------------------------------
> SF.Net email is sponsored by:
> Tame your development challenges with Apache's Geronimo App Server.
Download
> it for free - -and be entered to win a 42" plasma tv or your very own
> Sony(tm)PSP.  Click here to play: http://sourceforge.net/geronimo.php
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 

-------------------------------------------------------
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

This e-mail is only intended for the person(s) to whom it is addressed and
may contain confidential information. LB Icon does not accept responsibility
for any loss or damage caused by this email or any attachments. Unless
clearly stated to the contrary, any opinions or comments are personal to the
writer and are not made on behalf of LB Icon. If you have received this
e-mail in error, please notify us immediately at in...@lb... and then
delete this message from your system. Please do not copy it or use it for
any purposes, or disclose its contents to any other person. Thank you for
your co-operation.

LB Icon is the business name of Aspect Internet Holdings Limited, Aspect
Technologies Limited, LB Icon Limited, Object 1 Limited and Escador Limited.