RE: [Sqlrelay-discussion] DBI Performance

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

On Wed, 2005-11-23 at 12:10 +0000, David Edwards wrote:
> 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?

That's one of those application-specific things.  It's generally most
efficient to run SQL Relay on a middle tier between the web servers and
database servers, where many web server machines talk to a few (or just
one) SQL Relay machines which in turn distribute load over multiple
backed database servers.  However, there are cases where that's overkill
and it ends up being more efficient to run SQL Relay directly on the web
or database tier.  It's generally more efficient to run SQL Relay on the
database tier than on the web tier, but again, that can vary with the
application.  In the end, you have to experiment with it.

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

This looks good, you're using the unix socket to talk to SQL Relay.

If you try running SQL Relay on the database tier, make sure you use the
"local connection" to talk to the database too.  See
http://sqlrelay.sourceforge.net/sqlrelay/tuning.html for details.

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

You'll probably see at least a minor improvement.  Plus, there are a few
tweaks that you can use from the native API that you can't access
through the DBI driver.  Again, see  
http://sqlrelay.sourceforge.net/sqlrelay/tuning.html for details.

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