spockproxy-devel Mailing List for Spock Proxy (Page 2)
Status: Alpha
Brought to you by:
kaotao
Menu
▾
▴
spockproxy-devel — Development discussion
You can subscribe to this list here.
| 2008 |
Jan
|
Feb
|
Mar
|
Apr
(1) |
May
|
Jun
|
Jul
(5) |
Aug
(2) |
Sep
|
Oct
(5) |
Nov
|
Dec
(3) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2009 |
Jan
|
Feb
(2) |
Mar
(3) |
Apr
|
May
(1) |
Jun
(7) |
Jul
(5) |
Aug
|
Sep
|
Oct
|
Nov
(3) |
Dec
|
| 2014 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
|
Aug
(1) |
Sep
|
Oct
|
Nov
|
Dec
|
|
From: steve <iam...@gm...> - 2008-10-23 06:49:28
|
>> I'm looking at possibly moving to AWS/EC2, and was looking at scalr >> (http://code.google.com/p/scalr/) as a way to manage the spinning up >> of and auto-managing instances. It would be awesome if Spock Proxy >> were directly supported there. > > Indeed - we also use a lot of EC2 and have discussed things like this. But > so far just talk. If we do anything I'll let you know. Great. I just found the scalr project this week, and seems to be one of the only ones that is open source (I don't think RightScale is, for example). I would like to have shard migration from server to server, including having a shard run on a server already serving a shard. For either the mast or slave. I'm sure an algorithm could be created that did rebalancing, and could be plugged into scalr or similar to include creating (or terminating) new servers if the rebalancing required it. > Very interesting, we have discussed doing something similar but simpler > just one master and one slave on a single server. The issue is we use > replication for the universal data and you don't want to get into a spot > where you can't replicate (because MySQL will only support one master - at > least for now). Also in out case we want the master DB to have all the > machine resources (memory, disk IO, ...). I hear you on memory... but on IO I think that you find that you can ditch hardware raid for software mirrors and multiple mysql instances pointing to the different mirrors -- for orthogonal data. I don't know enough about EC2 to know how things will perform there though... But it is also helpful when mysql does not scale well to a lot of cores -- you can use CPU affinity for the master process (say 4 of 8 cores) and have four other mysql slaves running on the other 4 cores. This works well if you like many replicas of each shard. |
|
From: Frank F. <fr...@co...> - 2008-10-23 05:35:08
|
Steve, Great questions - see the answers below: On Oct 22, 2008, at 9:19 PM, steve wrote: > Hi all, > > Spock Proxy looks very interesting! I have a couple of questions: > > My 'primary key' for partitioning is in two columns, not one. What to > do? (Two is useful, by the way -- sometimes data is useful to a group > of shards and you can put that in the first column, sort of a shard > group id and then a shard id for that group and when combined, a > primary key of sorts for a shard). Currently we only shard by a single key (doesn't have to be primary or unique). I'm not sure if it is possible to work around this, I'll check and post further on it. > How do you handle failure of a shard, or is that handled elsewhere? It is handled elsewhere, and currently not quite a automatically as I'd like. Since we have two sets of shards (one master and one slave for each shard) we point both proxies at the single surviving DB. This works well but of course it will have less throughput. > I'm looking at possibly moving to AWS/EC2, and was looking at scalr > (http://code.google.com/p/scalr/) as a way to manage the spinning up > of and auto-managing instances. It would be awesome if Spock Proxy > were directly supported there. Indeed - we also use a lot of EC2 and have discussed things like this. But so far just talk. If we do anything I'll let you know. > Looking over our queries I see that we do on occasion have three > tables in a query. Often in the case of any join, one table is one in > which to shard upon, and the other (two) are what you call 'universal' > tables. There is no problem joining 2, 3, 4. 5, or more tables so long as you as joining so that all related data would be in the same shard (typically that would be on the shard key for sharded tables - non sharded tables, or 'universal' tables will always work). Even if the results come from more than one shard, which is typical, so long the data for any particular result row came from the same shard it will work. > By the look of the shard_database_directory, by having port_number in > there, it looks like you support having multiple shards running on the > same server. I am a fan of having a mysql master and mysql slaves on > the same server. Yes, it will work just as you expect. We do this in development but in production of course you'd loose the greater capacity that we did all this to get. > Background: In a basic setup of a single master and a single slave on > there own servers, you might run into issues of slave lag since the > mysql slave only has two threads (one each for io and for sql). If the > master was sharded into four mysql instances, as was the slave, then > the slave server would have eight threads running (4 IO and 4 SQL). A > more ideal setup would be that the master and slave(s) would be mixed. > In this case, two master shards on server A and two on server B and > the same for the slaves. To avoid a certain amount of IO contention, I > put each mysql server instance on a separate dirve/array on a > particular machine. Cheaper than raid cards for the same/better > performance. Very interesting, we have discussed doing something similar but simpler just one master and one slave on a single server. The issue is we use replication for the universal data and you don't want to get into a spot where you can't replicate (because MySQL will only support one master - at least for now). Also in out case we want the master DB to have all the machine resources (memory, disk IO, ...). Frank |
|
From: steve <iam...@gm...> - 2008-10-23 04:19:07
|
Hi all, Spock Proxy looks very interesting! I have a couple of questions: My 'primary key' for partitioning is in two columns, not one. What to do? (Two is useful, by the way -- sometimes data is useful to a group of shards and you can put that in the first column, sort of a shard group id and then a shard id for that group and when combined, a primary key of sorts for a shard). How do you handle failure of a shard, or is that handled elsewhere? I'm looking at possibly moving to AWS/EC2, and was looking at scalr (http://code.google.com/p/scalr/) as a way to manage the spinning up of and auto-managing instances. It would be awesome if Spock Proxy were directly supported there. Looking over our queries I see that we do on occasion have three tables in a query. Often in the case of any join, one table is one in which to shard upon, and the other (two) are what you call 'universal' tables. By the look of the shard_database_directory, by having port_number in there, it looks like you support having multiple shards running on the same server. I am a fan of having a mysql master and mysql slaves on the same server. Background: In a basic setup of a single master and a single slave on there own servers, you might run into issues of slave lag since the mysql slave only has two threads (one each for io and for sql). If the master was sharded into four mysql instances, as was the slave, then the slave server would have eight threads running (4 IO and 4 SQL). A more ideal setup would be that the master and slave(s) would be mixed. In this case, two master shards on server A and two on server B and the same for the slaves. To avoid a certain amount of IO contention, I put each mysql server instance on a separate dirve/array on a particular machine. Cheaper than raid cards for the same/better performance. -s |
|
From: Frank F. <fr...@co...> - 2008-10-15 23:15:24
|
Zach,
You're almost there. It sounds like you have everything set up
correctly but you are missing one piece. That is - in each shard
schema you need to create a view to every table in the local universal
schema. And I have a script for you that does the too.
The reason for doing it this way is so your app can join on all the
tables within each shard. So if you have a federated table which had
a 'status_id' field and then a universal table 'status' that has the
status_id and the status_name you need to be able to join the two; the
status table would need to be inside each shard.
Here is the shell script to fix this for you:
---------- rebuildViews.sh starts on the next line ------------
#! /bin/bash
# create all the views for the shards.
# WARNING - this will DROP ALL THE EXISTING VIEWS in the shard.
# so if you have any other views that are not related to the universal
DB
# you would need to recreate them
# ALSO you need to enter your name, password and the DB and server array
echo "starting at: "
date
NAME='frank'
PASSWORD='KC6evp01'
# for i in is expecting a 2x array of shards in the form of (quote "
marks are important):
# "schema_name server_name" "schema_name server_name" "schema_name
server_name"
# where schema_name and server_name are values that would work in a
mysql command
for i in "site_01_production s035" "site_02_production s036"
"site_03_production s037" "site_04_production s038"
do
set $i
echo $1 $2
/usr/bin/mysql -u $NAME -p$PASSWORD -h $2 -D $1 -e "SELECT
CONCAT('DROP VIEW ', TABLE_NAME, ';') AS '--' FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$1' AND TABLE_TYPE =
'VIEW' ;" > tmp.sql
/usr/bin/mysql -u $NAME -p$PASSWORD -h $2 -D $1 -e "SELECT
CONCAT('CREATE VIEW ', t.TABLE_NAME, ' AS SELECT ',
group_concat(COLUMN_NAME), ' FROM site_universal_production.',
t.TABLE_NAME, ';') AS '--' FROM INFORMATION_SCHEMA.TABLES t,
INFORMATION_SCHEMA.COLUMNS c WHERE t.TABLE_SCHEMA =
'site_universal_production' AND c.TABLE_SCHEMA =
'site_universal_production' and t.TABLE_NAME = c.TABLE_NAME GROUP BY
t.TABLE_NAME;" >> tmp.sql
# comment the next line if you do NOT what the script to actually drop
and create the views
/usr/bin/mysql -u $NAME -p$PASSWORD -h $2 -D $1 < tmp.sql
done
rm tmp.sql
echo "done at: "
date
------------------ end of shell script ---------------
Good luck,
Frank
On Oct 15, 2008, at 12:25 PM, Zach Garner wrote:
> I seem to be having trouble with Universal tables under read/write
> scenarios (all my earlier testing only involved writes on federated
> tables). After reading the spock section universal tables I thought
> I understood it, but my results aren't quite what I'd expected:
> We have solved it by having a 'universal' database which contains
> all of these tables as well as some directory tables. Each shard
> database is a slave of this universal database server and the
> universal database is replicated to each shard. There are two
> databases on each shard, the shard itself which is unique to that
> shard (and perhaps it's slaves) and the universal database which is
> identical to all other universal databases.
> So I now have two schemas on each shard. The configuration schema
> (site_universal_production) which in addition to the shard_* tables
> now contains each universal table as well and the federated schemas
> which only contain copies of the tables we'll be federating.
>
> I've setup replication for this schema (site_universal_production,
> to my 2 shard nodes) and have them successfully working as slave
> nodes; replicating updates to site_universal_production locally as
> I'd expect.
>
> So now things like updates work with universal tables and the
> changes ripple down nicely to each shard.
>
> The trouble I'm having now is that selects on universal tables are
> still looking for the tables in the federated shard databases (so
> they fail) and I can't seem to see where or how to explicitly map
> the universal tables to the site_universal_production database.
>
> Of course I could try table level replication and bypass the two
> databases per shard (and therefor the lookup issue, I probably will
> now for fun!) but since your notes seem to be pretty clear on the
> use of separate databases I figure I'm actually missing something
> very obvious.
>
> :-)
>
>
> --
> _______________________________________________________________________
> Zach Garner | Sr. Systems Administrator / Product Analyst | iiON
> Corporation | www.iion.com | Office: 858-713-0450 x 20
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> This message (and any associated files) is intended only for the
> individual named and may contain information that is confidential,
> subject to copyright or constitutes a trade secret. If you are not
> the named addressee you are hereby notified that any dissemination,
> copying or distribution of this message, or files associated with
> this message, is strictly prohibited. If you have received this
> message in error, please notify us immediately by replying to the
> message and deleting it from your computer. Messages sent to and
> from us may be monitored. Any views or opinions presented are solely
> those of the author and do not necessarily represent those of iiON
> Corporation.
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> -------------------------------------------------------------------------
> This SF.Net email is sponsored by the Moblin Your Move Developer's
> challenge
> Build the coolest Linux based applications with Moblin SDK & win
> great prizes
> Grand prize is a trip for two to an Open Source event anywhere in
> the world
> http://moblin-contest.org/redirect.php?banner_id=100&url=/_______________________________________________
> spockproxy-devel mailing list
> spo...@li...
> https://lists.sourceforge.net/lists/listinfo/spockproxy-devel
|
|
From: Zach G. <za...@ii...> - 2008-10-15 20:12:32
|
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> </head> <body bgcolor="#ffffff" text="#000000"> <font size="-1"><font face="Arial">I seem to be having trouble with Universal tables under read/write scenarios (all my earlier testing only </font></font><font size="-1"><font face="Arial">involved writes </font></font><font size="-1"><font face="Arial">on federated tables). After reading the spock section universal tables I thought I understood it, but my results aren't quite what I'd expected:<br> </font></font> <blockquote><font size="-1"><font face="Arial">We have solved it by having a 'universal' database which contains all of these tables as well as some directory tables. Each shard database is a slave of this universal database server and the universal database is replicated to each shard. There are two databases on each shard, the shard itself which is unique to that shard (and perhaps it's slaves) and the universal database which is identical to all other universal databases.</font></font><br> </blockquote> <font size="-1"><font face="Arial">So I now have two schemas on each shard. The configuration schema (site_universal_production) which in addition to the shard_* tables now contains each universal table as well and the federated schemas which only contain copies of the tables we'll be federating. <br> <br> I've setup replication for this schema (</font></font><font size="-1"><font face="Arial">site_universal_production, </font></font><font size="-1"><font face="Arial">to my 2 shard nodes) and have them successfully working as slave nodes; replicating updates to </font></font><font size="-1"><font face="Arial">site_universal_production locally as I'd expect.<br> <br> So now things like updates work with universal tables and the changes ripple down nicely to each shard.<br> <br> The trouble I'm having now is that selects on universal tables are still looking for the tables in the federated </font></font><font size="-1"><font face="Arial">shard </font></font><font size="-1"><font face="Arial">databases (so they fail) and I can't seem to see where or how to explicitly map the universal tables to the site_universal_production database.<br> <br> Of course I could try table level replication and bypass the two databases per shard (and therefor the lookup issue</font></font><font size="-1"><font face="Arial">, I probably will now for fun!</font></font><font size="-1"><font face="Arial">) but since your notes seem to be pretty clear on the use of separate databases I figure I'm actually missing something very obvious.<br> <br> :-)<br> </font></font><font size="-1"><font face="Arial"><br> <br> </font></font> <div class="moz-signature">-- <br> <font ptsize="10" family="SANSSERIF" color="#004080" face="Verdana" lang="0" size="2">_______________________________________________________________________</font><br> <font ptsize="8" family="SANSSERIF" color="#000000" face="Arial" lang="0" size="1"><b><span style="color: rgb(0, 0, 160);">Zach Garner</span></b> <span style="color: rgb(128, 128, 192);">| </span> Sr. Systems Administrator / Product Analyst <span style="color: rgb(128, 128, 192);">|</span> iiON Corporation <span style="color: rgb(128, 128, 192);">|</span> <a style="color: rgb(0, 0, 160); text-decoration: none;" target="_blank" href="http://www.iion.com/">www.iion.com</a> <span style="color: rgb(128, 128, 192);">|</span> Office: 858-713-0450 x 20<br> <br> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br> <div style="color: rgb(0, 64, 128); text-align: justify; width: 570px;">This message (and any associated files) is intended only for the individual named and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the named addressee you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Messages sent to and from us may be monitored. Any views or opinions presented are solely those of the author and do not necessarily represent those of iiON Corporation.</div> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- </font></div> </body> </html> |
|
From: Frank F. <fr...@co...> - 2008-08-05 18:38:32
|
On Aug 5, 2008, at 11:06 AM, Zach Garner wrote: > I think I had a bit of a misunderstanding with the universal tables. > I'd originally assumed that spock replicated writes/deletes/updates > to each shard *itself* when using a universal table; but it's > literally mysql replication with the configuration database a write > master to the shards? And that makes sense now that I think about it. Correct - Spockproxy uses MySQL replications (which you setup). This was easiest and most reliable way to get things to work quickly. I'll update the documentation to make it clearer. Frank |
|
From: Zach G. <za...@ii...> - 2008-08-05 18:07:47
|
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
On 7/29/2008 2:26 PM, Frank Flynn wrote:
<blockquote
cite="mid:5E9...@co..."
type="cite"><br>
On Jul 29, 2008, at 12:26 PM, Zach Garner wrote:
<br>
<br>
<blockquote type="cite">I've got a question. I've been working on a
configuration using spockproxy (which I'm really enjoying btw). So far
I've got everything working pretty well, which is exciting.
<br>
<br>
My question is, is there a way to return the incremented column value
after performing an insert? Or to retrieve the value just before
performing the insert? I'm working with an application that has been
using an incrementing value in combination with the last_insert_id()
function to track the data and continue making some changes to it.
<br>
</blockquote>
<br>
As Michael said the Spockproxy does not support the last_insert_id()
but there is a function get_next_id which will give you one or several
id for a particular table before you do the insert. Then you can use
these id(s) and the proxy will not change them.
<br>
<br>
Because it's called ahead of time nether the proxy nor the MySQL server
could possibly know which table you're about to insert into so you have
to tell it. You can also ask for several id's at once; if you know you
have 50 rows to insert just ask for 50 id's and they will be put aside
for you so you can just use them and not have to keep asking for new
ones.
<br>
<br>
The format is:
<br>
<br>
select get_next_id( < table name >, < number of id's you want
>);
<br>
<br>
</blockquote>
Thank you. I did end up replacing all the last_insert_id() call and
since the get_next_id generates the sequence and returns the id
immediately I think it actually works better.<br>
<br>
I'm still experimenting with the application (and still enjoying it a
lot!) but hope to do some testing soon. :)<br>
<br>
I think I had a bit of a misunderstanding with the universal tables.
I'd originally assumed that spock replicated writes/deletes/updates to
each shard *itself* when using a universal table; but it's literally
mysql replication with the configuration database a write master to the
shards? And that makes sense now that I think about it.<br>
<br>
Anyhow thanks for the responses and (once again) for sharing such an
interesting and well thought out implementation. I'm back to testing!<br>
<blockquote
cite="mid:5E9...@co..."
type="cite">and it will return the first id; if you asked for 50 then
the next 50 are yours, if you asked for 1 that one is yours. Be
careful because there is no error checking if you go over the id's you
were alloted.
<br>
<br>
There is a bug it the current function, it is not thread safe and if
you hit it massively from several clients you can get the same id
back. This is fixed in the next release which we should be posting
soon. If your still experimenting the current function is fine; if
you're ramping up of stress testing you'll want the new one.
<br>
<br>
<blockquote type="cite"><br>
Anyway, I'll keep playing around. Chances are I've missed something
fairly obvious.
<br>
<br>
It's been a real pleasure using such a nicely thought out
implementation like this.
<br>
<br>
Regards!
<br>
<br>
Zach
<br>
</blockquote>
<br>
Thanks,
<br>
<br>
Frank
<br>
<a class="moz-txt-link-freetext" href="http://www.spock.com/frank">http://www.spock.com/frank</a>
<br>
<br>
</blockquote>
<br>
<br>
<div class="moz-signature">-- <br>
<font ptsize="10" family="SANSSERIF" color="#004080" face="Verdana"
lang="0" size="2">_______________________________________________________________________</font><br>
<font ptsize="8" family="SANSSERIF" color="#000000" face="Arial"
lang="0" size="1"><b><span style="color: rgb(0, 0, 160);">Zach Garner</span></b>
<span style="color: rgb(128, 128, 192);">|
</span> Sr. Systems Administrator / Product Analyst <span
style="color: rgb(128, 128, 192);">|</span>
iiON
Corporation <span style="color: rgb(128, 128, 192);">|</span> <a
style="color: rgb(0, 0, 160); text-decoration: none;" target="_blank"
href="http://www.iion.com/">www.iion.com</a> <span
style="color: rgb(128, 128, 192);">|</span>
Office: 858-713-0450 x 20<br>
<br>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
<div style="color: rgb(0, 64, 128); text-align: justify; width: 570px;">This
message (and any associated files) is intended only for the individual
named and may contain information that is confidential, subject to
copyright or constitutes a trade secret. If you are not the named
addressee you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from
your computer. Messages sent to and from us may be monitored. Any views
or opinions presented are solely those of the author and do not
necessarily represent those of iiON Corporation.</div>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
</font></div>
</body>
</html>
|
|
From: Frank F. <fr...@co...> - 2008-07-29 21:26:24
|
On Jul 29, 2008, at 12:26 PM, Zach Garner wrote: > I've got a question. I've been working on a configuration using > spockproxy (which I'm really enjoying btw). So far I've got > everything working pretty well, which is exciting. > > My question is, is there a way to return the incremented column > value after performing an insert? Or to retrieve the value just > before performing the insert? I'm working with an application that > has been using an incrementing value in combination with the > last_insert_id() function to track the data and continue making some > changes to it. As Michael said the Spockproxy does not support the last_insert_id() but there is a function get_next_id which will give you one or several id for a particular table before you do the insert. Then you can use these id(s) and the proxy will not change them. Because it's called ahead of time nether the proxy nor the MySQL server could possibly know which table you're about to insert into so you have to tell it. You can also ask for several id's at once; if you know you have 50 rows to insert just ask for 50 id's and they will be put aside for you so you can just use them and not have to keep asking for new ones. The format is: select get_next_id( < table name >, < number of id's you want >); and it will return the first id; if you asked for 50 then the next 50 are yours, if you asked for 1 that one is yours. Be careful because there is no error checking if you go over the id's you were alloted. There is a bug it the current function, it is not thread safe and if you hit it massively from several clients you can get the same id back. This is fixed in the next release which we should be posting soon. If your still experimenting the current function is fine; if you're ramping up of stress testing you'll want the new one. > > Anyway, I'll keep playing around. Chances are I've missed something > fairly obvious. > > It's been a real pleasure using such a nicely thought out > implementation like this. > > Regards! > > Zach Thanks, Frank http://www.spock.com/frank |
|
From: Michael W. <mi...@co...> - 2008-07-29 19:49:52
|
So glad to know you like it. Any comments will be welcomed and appreciated. Currently we don't have function like last_insert_id(), but you may find this information in table site_universal_production.shard_table_directory which has a column "next_id". If you minus "next_id" value by 1, you will get exactly what you want. We may add this function in future version. For now, you can get last_insert_id by querying site_universal_production.shard_table_directory. _ Best Regards, Michael Wang On Jul 29, 2008, at 12:26 PM, Zach Garner wrote: > I've got a question. I've been working on a configuration using > spockproxy (which I'm really enjoying btw). So far I've got > everything working pretty well, which is exciting. > > My question is, is there a way to return the incremented column > value after performing an insert? Or to retrieve the value just > before performing the insert? I'm working with an application that > has been using an incrementing value in combination with the > last_insert_id() function to track the data and continue making some > changes to it. > > Anyway, I'll keep playing around. Chances are I've missed something > fairly obvious. > > It's been a real pleasure using such a nicely thought out > implementation like this. > > Regards! > > Zach > > On 7/21/2008 11:15 AM, Frank Flynn wrote: >> >> 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 >> >> >> >> ------------------------------------------------------------------------- >> This SF.Net email is sponsored by the Moblin Your Move Developer's >> challenge >> Build the coolest Linux based applications with Moblin SDK & win >> great prizes >> Grand prize is a trip for two to an Open Source event anywhere in >> the world >> http://moblin-contest.org/redirect.php?banner_id=100&url=/ >> >> _______________________________________________ >> spockproxy-devel mailing list >> spo...@li... >> https://lists.sourceforge.net/lists/listinfo/spockproxy-devel >> > > > -- > _______________________________________________________________________ > Zach Garner | Sr. Systems Administrator / Product Analyst | iiON > Corporation | www.iion.com | Office: 858-713-0450 x 20 > > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > This message (and any associated files) is intended only for the > individual named and may contain information that is confidential, > subject to copyright or constitutes a trade secret. If you are not > the named addressee you are hereby notified that any dissemination, > copying or distribution of this message, or files associated with > this message, is strictly prohibited. If you have received this > message in error, please notify us immediately by replying to the > message and deleting it from your computer. Messages sent to and > from us may be monitored. Any views or opinions presented are solely > those of the author and do not necessarily represent those of iiON > Corporation. > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > ------------------------------------------------------------------------- > This SF.Net email is sponsored by the Moblin Your Move Developer's > challenge > Build the coolest Linux based applications with Moblin SDK & win > great prizes > Grand prize is a trip for two to an Open Source event anywhere in > the world > http://moblin-contest.org/redirect.php?banner_id=100&url=/_______________________________________________ > spockproxy-devel mailing list > spo...@li... > https://lists.sourceforge.net/lists/listinfo/spockproxy-devel |
|
From: Zach G. <za...@ii...> - 2008-07-29 19:25:25
|
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
I've got a question. I've been working on a configuration using
spockproxy (which I'm really enjoying btw). So far I've got everything
working pretty well, which is exciting.<br>
<br>
My question is, is there a way to return the incremented column value
after performing an insert? Or to retrieve the value just before
performing the insert? I'm working with an application that has been
using an incrementing value in combination with the last_insert_id()
function to track the data and continue making some changes to it.<br>
<br>
Anyway, I'll keep playing around. Chances are I've missed something
fairly obvious.<br>
<br>
It's been a real pleasure using such a nicely thought out
implementation like this. <br>
<br>
Regards!<br>
<br>
Zach<br>
<br>
On 7/21/2008 11:15 AM, Frank Flynn wrote:
<blockquote
cite="mid:09F...@co..."
type="cite">
<div>
<div>
<div>
<div>Karel,</div>
<div>
<blockquote type="cite" class=""><span class="Apple-style-span"
style="color: rgb(0, 0, 0);">
<div style="">
<div>
<div>
<div>
<div><span class="Apple-style-span" style="color: rgb(20, 79, 174);"></span></div>
</div>
</div>
</div>
</div>
</span></blockquote>
<br>
<blockquote type="cite" class=""><span class="Apple-style-span"
style="color: rgb(0, 0, 0);">
<div style="">
<div>
<div>
<div>
<div><span class="Apple-style-span" style="color: rgb(20, 79, 174);">[Spock
Proxy Devel] Some spockproxy questions.</span></div>
</div>
</div>
</div>
</div>
</span></blockquote>
<blockquote type="cite" class=""><span class="Apple-style-span"
style="color: rgb(0, 0, 0);">
<div style="">
<div>
<div>
<div>From: Karel Vervaeke <a class="moz-txt-link-rfc2396E" href="mailto:karel@ou..."><karel@ou...></a> - 2008-07-18 15:24<br>
</div>
</div>
</div>
<div>
<div>Hi, I just have a couple of questions<br>
<br>
About transactions:<br>
Suppose I need to store data in two partitions in one transaction,<br>
how does spockproxy the cross-server transactions?</div>
</div>
</div>
</span></blockquote>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<br>
<blockquote type="cite" class=""><span class="Apple-style-span"
style="color: rgb(0, 0, 0);">
<div style="">
<div>
<div>About the dynamic application pool:<br>
The spockproxy homepage mentions: "Upon startup the proxy establishes a<br>
connection pool with the minimum number of connections. After that, each<br>
time a client uses a connection, it is his for the life of the<br>
connection"<br>
<br>
For the life of the connection... does that imply that a once a<br>
connection between spockproxy and the database is reserved for a client,<br>
that the client can only get data from that partition?</div>
</div>
</div>
</span></blockquote>
<div><br>
</div>
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</div>
<div><br>
<blockquote type="cite" class=""><span class="Apple-style-span"
style="color: rgb(0, 0, 0);">
<div style="">
<div>
<div>If I understand correctly, it means that the when a client
connects to<br>
spockproxy and it requests data from two partitions in that same<br>
connection, the two connections spockproxy<->database connections<br>
created will remain reserved to that client connection until the client<br>
connection is closed. </div>
</div>
</div>
</span></blockquote>
<div><br>
</div>
No, only during a transaction.</div>
<div><br>
<blockquote type="cite" class=""><span class="Apple-style-span"
style="color: rgb(0, 0, 0);">
<div style="">
<div>
<div>Would that mean that the client should not use a<br>
connection pool to spockproxy (otherwise each pooled connection will be<br>
assigned server-connections for all partitions after a while?</div>
</div>
</div>
</span></blockquote>
<div><br>
</div>
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.</div>
<div><br>
<blockquote type="cite" class=""><span class="Apple-style-span"
style="color: rgb(0, 0, 0);">
<div style="">
<div>
<div>About the choice of the 'federated' keyword.<br>
That does not imply 'federated' as in the FEDERATED storage engine<br>
right? (Like you would need for proper cross-server sharding with HScale<br>
on mysqlproxy?)</div>
</div>
</div>
</span></blockquote>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>From the HScale Documentation at: <a moz-do-not-send="true"
href="http://hscale.org/display/HSCALE/Home">http://hscale.org/display/HSCALE/Home</a></div>
<div><span class="Apple-style-span"
style="font-size: 13px; line-height: 17px;"> "HSCALE is a plugin
written for <a moz-do-not-send="true"
href="http://forge.mysql.com/wiki/MySQL_Proxy" rel="nofollow"
style="color: rgb(0, 51, 102);">MySQL Proxy</a> which allows you to
transparently split up tables into multiple tables called <em>partitions</em>. </span></div>
<div><span class="Apple-style-span"
style="font-size: 13px; line-height: 17px;"> In later versions you
will be able to put each partition on a different MySQL server."</span></div>
<div><font class="Apple-style-span" size="3"><span
class="Apple-style-span" style="font-size: 13px; line-height: 17px;"><br>
</span></font></div>
<div><font class="Apple-style-span" size="3"><span
class="Apple-style-span" style="font-size: 13px; line-height: 17px;">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.</span></font></div>
<div><font class="Apple-style-span" size="3"><span
class="Apple-style-span" style="font-size: 13px; line-height: 17px;"><br>
</span></font></div>
<div><font class="Apple-style-span" size="3"><span
class="Apple-style-span" style="font-size: 13px; line-height: 17px;">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.</span></font></div>
<div><br>
</div>
<div>Good Luck,</div>
<div>Frank</div>
<br>
</div>
<div><br>
</div>
</div>
</div>
</div>
<blockquote type="cite" class=""><span class="Apple-style-span"
style="color: rgb(0, 0, 0); font-family: arial; font-size: 16px;"></span></blockquote>
<pre wrap="">
<hr size="4" width="90%">
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
<a class="moz-txt-link-freetext" href="http://moblin-contest.org/redirect.php?banner_id=100&url=/">http://moblin-contest.org/redirect.php?banner_id=100&url=/</a></pre>
<pre wrap="">
<hr size="4" width="90%">
_______________________________________________
spockproxy-devel mailing list
<a class="moz-txt-link-abbreviated" href="mailto:spo...@li...">spo...@li...</a>
<a class="moz-txt-link-freetext" href="https://lists.sourceforge.net/lists/listinfo/spockproxy-devel">https://lists.sourceforge.net/lists/listinfo/spockproxy-devel</a>
</pre>
</blockquote>
<br>
<br>
<div class="moz-signature">-- <br>
<font ptsize="10" family="SANSSERIF" color="#004080" face="Verdana"
lang="0" size="2">_______________________________________________________________________</font><br>
<font ptsize="8" family="SANSSERIF" color="#000000" face="Arial"
lang="0" size="1"><b><span style="color: rgb(0, 0, 160);">Zach Garner</span></b>
<span style="color: rgb(128, 128, 192);">|
</span> Sr. Systems Administrator / Product Analyst <span
style="color: rgb(128, 128, 192);">|</span>
iiON
Corporation <span style="color: rgb(128, 128, 192);">|</span> <a
style="color: rgb(0, 0, 160); text-decoration: none;" target="_blank"
href="http://www.iion.com/">www.iion.com</a> <span
style="color: rgb(128, 128, 192);">|</span>
Office: 858-713-0450 x 20<br>
<br>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
<div style="color: rgb(0, 64, 128); text-align: justify; width: 570px;">This
message (and any associated files) is intended only for the individual
named and may contain information that is confidential, subject to
copyright or constitutes a trade secret. If you are not the named
addressee you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from
your computer. Messages sent to and from us may be monitored. Any views
or opinions presented are solely those of the author and do not
necessarily represent those of iiON Corporation.</div>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
</font></div>
</body>
</html>
|
|
From: Frank F. <fr...@co...> - 2008-07-21 18:15:26
|
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 |
|
From: Karel V. <ka...@ou...> - 2008-07-18 15:24:32
|
Hi, I just have a couple of questions Sometimes I just need to do inserts in two partitions in one transaction. (The application data is hard to partition, so we introduced two properties to partition on. Insert queries need to be performed twice (once on each partition), select queries always operate on at least one of both properties). About transactions: Suppose I need to store data in two partitions in one transaction, how does spockproxy the cross-server transactions? 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? 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. 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? 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?) Regards, Karel |
|
From: Michael W. <mi...@co...> - 2008-04-17 17:13:42
|
Hi guys.
Enjoy! :)
_
Best Regards,
Michael Wang
|
