Re: [Sqlrelay-discussion] Solaris 10 x86 and multiple databases support

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

On Thu, 2006-01-05 at 14:32 -0500, NetComrade wrote:
> We are thinking of using sqlrelay in between our php enabled apaches and
> our multiple databases to minimize the # of connections between apaches
> and databases.
> 
> Quesions:
> 1) Can sqlrelay easily maintain multiple connections in various
> #'s? (e.g. 10 to db1, 12 to db2, 8 to db3, 11 to db11, etc), by a database
> here i mean a schema or username in the Oracle world.
> >From these lines, it would seem so:
> 
> The client will send a user and password to the sqlr-listener and
> sqlr-connection daemons when it connects to them. The authtier attribute
> indicates which daemon will actually pay attention to that user and
> password.

A single SQL Relay instance can maintain different numbers of
connections to various database servers, but it assumes that the
database servers are all replicated copies of each other or nodes in a
cluster and distributes client sessions over the database servers in
proportion to the number of connections that it maintains to each.

For instance, if 2 connections are maintained to db1 and only 1 is
maintained to db2, then when a client connects to SQL Relay, it is twice
as likely to end up running it's queries against db1 than db2.

You can set up multiple instances of SQL Relay against multiple database
servers.  Eg. one instance maintaining 10 connections to db1, a second
instance maintaining 12 connections to db2, etc. but each instance must
run on a seperate port/socket.  So the clients will need to use the
appropriate port/socket/user/password to connect to the appropriate
database.

There is currently no facility for SQL Relay to listen on a single
port/socket and route clients to different database servers based on the
user/password.

If you have a single database server with multiple schema's, you can set
up a single SQL Relay instance to maintain multiple connections to that
server and use the authtier="database".  When authtier="database", when
a client logs into SQL Relay, it is authenticated against the database
rather than against SQL Relay's list of users and if authentication is
successful, it ends up logged into the schema belonging to the username
that it used to log in as.  For example, one client could log in as
scott/tiger and it would end up logged in to the SCOTT schema, the next
client could log in as system/manager and would end up logged in to the
SYSTEM schema.  This requires setting up a proxy user though.  See
http://sqlrelay.sourceforge.net/sqlrelay/oraclentier.html
for more info on setting up proxy users and setting up SQL Relay to use
them.

> 
> 2) Can the tool be easily ported to Solaris x86? We are currently porting
> our application server from Solaris Sparc to Solaris x86, and it'll not
> run on Linux (uses lots of old libraries I am told). We are developing
> some of our newer products with php, and would like to be able to limit
> the # of persistent connections into the db. If this has been done, let me
> know, if it hasn't been done but should be possible, we'll be happy to
> share the steps we had to take with the community.

Solaris x86 is one of the platforms that I test against before making a
release.  When I released 0.36, it compiled on Solaris 9 x86 (that was
the current version then) with whatever the current gcc for Solaris 9
was at the time.  Since then, I've made some modifications, but I
believe they'll compile.  Version 0.37 is coming out soon, and I will
make sure it compiles on Solaris 8, 9 and 10 before releasing it.  I did
some work and accepted some patches to 0.37 to get it to compile using
Sun's native toolchain too.  If there are any modifications that need to
be made to SQL Relay to make it work with Solaris x86, I'd imagine
they'd be minor.

> 
> Thanks!

Thank you for your interest!

Let me know if you have any more questions.

David Muse
dav...@fi...