Specify database option?

[Devin]

In your examples, the database to be used for the query on a
particular host is not specified in the json that you send to
dbslayer. It seems you can only specify one database, or a group of
identical databases in the configuration file.

Assume I have a database named "shard1" on HOST1 and I have another
database named "shard2" which also runs under a mysql daemon on HOST1.

I wish to concurrently select from both shard1 and shard2 on HOST1.

I'm trying to determine the best approach. I could modify my SQL to
include the name of the database and issue two commands in parallel.

{"SQL":"select * from shard1.user order by stuff desc limit 100;"}

{"SQL":"select * from shard2.user order by stuff desc limit 100;"}

I can imagine a parameter which would be passed to dbslayer to specify
the database to be used. The alternative would look like this:

{"DB": "shard1", "SQL":"select * from user order by stuff desc limit
100;"}

{"DB": "shard2", "SQL":"select * from user order by stuff desc limit
100;"}

I noticed that in the source file dbaccess.c that you are using
mysql_real_connect and that you are passing in NULL where you could be
passing a DB parameter. So it seems a feature like the one I'm
suggesting here wouldn't be too hard to implement if it is generally
useful.

I don't really like the idea of embedding the database names in my
queries, but that's probably due to my own prejudices and use cases.
I'm guessing that you typically run one database per host, and so the
ability to select from multiple databases on a single host is not an
issue for you. But it may also be that I'm just missing something in
the documentation. So please let me know if that is the case.

I realize that, according to the documentation, that I can specify
multiple cloned databases for a particular host using the
configuration file. But I'm not sure what the results would look
like. Would I get a combined result set for each database specified
in the config file? Would the queries execute in parallel when
dbslayer receives the query? I was planning on going parallel by
executing multiple commands to many different shards and combining the
results in my own code.

Any tips or insights would be appreciated.

Thanks-
Devin

[Derek Gottfrid]

In general, we have one database per host and deal with things that
way. I would recommend my two blog post that cover lots of what you
are talking about

http://open.blogs.nytimes.com/2008/04/03/dbslayer-lives/
http://open.blogs.nytimes.com/2008/06/10/dbslayer-helps-you-get-parallel/

The second post really points out how to use dbslayer to execute
mutliple queries in parallel. We do this when we have mutile shards
and merge the results back on the clients side.

If this isn't helpful let me know.

d

[Devin Venable]

Derek,

I did find the articles.  In fact the "dbslayer helps you get parallel" was an AHAH article for me.  I had been trying to issue parallel queries from different threads and even processes, but something was blocking and throttling me down in either the mysql C client library or on the servers I was querying.   As of right now, dbslayer has solved my problem by effectively making it go away.

It did occur to me that I can push a "use" statement along with every query.  That's working okay and I can't detect a performance hit.

I do something like this to switch between databases on a single host.

"use testshard1; select * from ..."
"use testshard2; select * from ..."
"use testshard3; select * from ..."

Devin

--
Devin Venable
Mobile: 918-946-6806

[Derek Gottfrid]

One further refinement would be to just setup different stanzas in
your mysql config that dbslayer loads w/ names for the database

[test-shard-1]
database=test-shard-1
host=localhost
user=app
pass=secret

[test-shard-2]
database=test-shard-2
host=localhost
user=app
pass=secret

If that makes sense - then you don't need to use the use clause and
can pass in the HOST parameter HOST=test-shard-2 etc.
Glad to hear that dbslayer is working for you and the parallel trick is useful.

d