Re: [Spock Proxy Devel] Some spockproxy questions.

[Frank F. <fr...@co...>]

Karel,

> [Spock Proxy Devel] Some spockproxy questions.
> From: Karel Vervaeke <karel@ou...> -	2008-07-18 15:24
> Hi, I just have a couple of questions
>
> About transactions:
> Suppose I need to store data in two partitions in one transaction,
> how does spockproxy the cross-server transactions?

When  you issue the command "BEGIN TRANSACTION" the proxy sends this  
command to each database shard and holds those connections for you.   
Then you can issues whatever commands you want and the proxy will send  
them to one or all the proxys (queries that do not have the sharding  
key in them will go to all the shards).  When you issue the COMMIT or  
ROLL BACK the proxy sends that to each shard and releases the  
connections.


> About the dynamic application pool:
> The spockproxy homepage mentions: "Upon startup the proxy  
> establishes a
> connection pool with the minimum number of connections. After that,  
> each
> time a client uses a connection, it is his for the life of the
> connection"
>
> For the life of the connection... does that imply that a once a
> connection between spockproxy and the database is reserved for a  
> client,
> that the client can only get data from that partition?

Not at all.  The proxy looks at each query  and will send it to one or  
all of the shards depending on what it thinks is appropriate (based on  
your set up in the shard_range_directory and shard_table_directory  
tables).  The idea that a connection is reserved and dedicated to a  
particular client is only during transactions

> If I understand correctly, it means that the when a client connects to
> spockproxy and it requests data from two partitions in that same
> connection, the two connections spockproxy<->database connections
> created will remain reserved to that client connection until the  
> client
> connection is closed.

No, only during a transaction.

> Would that mean that the client should not use a
> connection pool to spockproxy (otherwise each pooled connection will  
> be
> assigned server-connections for all partitions after a while?

No, connections are pooled at the proxy level.  When you connect to  
the proxy the database connection are already established so there is  
not the same overhead.  You can use connection pooling at the client  
level too with no particular problems, although since the proxy is  
pooling for you it might not give you the same advantages.  It depends  
on your client I suppose.

> About the choice of the 'federated' keyword.
> That does not imply 'federated' as in the FEDERATED storage engine
> right? (Like you would need for proper cross-server sharding with  
> HScale
> on mysqlproxy?)

When we say 'federated' we mean it in the same sense as the federated  
storage engine; that is you see what looks like one database but it is  
in fact several databases running on different servers.  But  
Spockproxy does not require any particular storage engine and although  
you could use a federated table with the proxy it's probably pointless  
since the proxy is federating it already.  We use the INNODB storage  
engine primarily and have tested the MyISAM storage engine, but none  
of the others.  There is no reason they wouldn't work but we have not  
tested them.

 From the HScale Documentation at: http://hscale.org/display/HSCALE/Home
   "HSCALE is a plugin written for MySQL Proxy which allows you to  
transparently split up tables into multiple tables called partitions.
    In later versions you will be able to put each partition on a  
different MySQL server."

Spockproxy splits all of your data into different databases (typically  
on different servers) and allows you to access these database shards  
as one database.  So it's more like their future version; we do not do  
'partitioning' as in MySQL 5.1 where within a single database a table  
is divided into multiple shards.

Finally the version in Sourceforge is a bit old - look for us to push  
a new version in the next day or two; no new features just fewer bugs.

Good Luck,
Frank