spockproxy-devel Mailing List for Spock Proxy
Status: Alpha
Brought to you by:
kaotao
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: Bibek D. <bib...@nt...> - 2014-08-01 11:29:43
|
Hi All, I have downloaded and extracted the spockproxy tarball release from http://spockproxy.sourceforge.net/. I am using windows 7. I don't know how to run spockproxy . any help would be appreciable. Any other tool do I need to run this? Thank You, Bibek |
From: Graham F. <gr...@si...> - 2009-11-12 02:07:04
|
For item 3), in a MySQL-MMM environment, I made the following change to SpockProxy and this fixed issues that I was having when switching roles (i.e. Failing over). Index: C:/project/trunk/SimplifyMedia/SpockProxy/src/network-mysqld-proxy.cpp =================================================================== --- C:/project/trunk/SimplifyMedia/SpockProxy/src/network-mysqld-proxy.cpp (revision 21011) +++ C:/project/trunk/SimplifyMedia/SpockProxy/src/network-mysqld-proxy.cpp (revision 21071) @@ -404,6 +404,8 @@ * remove us from the connection pool and close the connection */ network_connection_pool_del_byconn(pool, server); + network_socket_free(server); + network_connection_pool_create_conns(get_network_mysqld()); } } else if (events == EV_TIMEOUT) { if (time(NULL) - server->last_write_time >= get_config_max_conn_idle_time()) { This has only had limited testing, and has not been deployed in a production environment. Graham > -----Original Message----- > From: Frank Flynn [mailto:fr...@co...] > Sent: Wednesday, November 11, 2009 4:53 AM > To: Pavel Gushcha > Cc: spo...@li... > Subject: Re: [Spock Proxy Devel] building & running problems > > > On Nov 10, 2009, at 7:02 AM, Pavel Gushcha wrote: > > > But as i understand, it is not thread-safe (if some spockproxy > > intances work simulateniously with meta database). > > > > Some missing basic feautures/things, about i thinked: > > 1) After looking at logs, i see that spockproxy opens 50 connections > > to meta database, this too many (for shards this is ok) > > 2) I can't specify port for meta database > > You can do this in the config file or on the command line I believe (I > only use the 3306 port so I'm not sure) > > > So, i have following questions: > > 1) Do you plan to release in nearly future next version of > > spockproxy (may be with fixes for compilation process)? > > I don't think we have a new version planned anytime soon. > > > 2) get_next_id() function is not present in sample medatbase dump, > > so is good idea to add it, can you give me code for it? > > It is much like you wrote - but we have added locks to make is thread > safe. But remember that since the proxy will request many next id's > at a time it is unlikely that threads would collide (the proxy will > ensure this because it hands out the id's) > > > 3) Spockproxy support failover for high availability? I plan to have > > 2 servers for each shard with master-master replication. > > You have to make your own failover - but this is also much easier than > you might think. Once configured the Spockproxy only takes a second > to restart even for the biggest, busiest DB's. For HA the same proxy > can be told to connect to a different master but currently you will > have to restart the proxy. > > > 4) Can i contribute code for spockproxy (patches for compilation, > > etc)? > > Yes - because of some changes at Spock we have not been working on our > proxy very much lately. > > Frank > > > ----------------------------------------------------------------------- > ------- > Let Crystal Reports handle the reporting - Free Crystal Reports 2008 > 30-Day > trial. Simplify your report design, integration and deployment - and > focus on > what you do best, core application coding. Discover what's new with > Crystal Reports now. http://p.sf.net/sfu/bobj-july > _______________________________________________ > spockproxy-devel mailing list > spo...@li... > https://lists.sourceforge.net/lists/listinfo/spockproxy-devel |
From: Frank F. <fr...@co...> - 2009-11-11 06:03:14
|
On Nov 10, 2009, at 7:02 AM, Pavel Gushcha wrote: > But as i understand, it is not thread-safe (if some spockproxy > intances work simulateniously with meta database). > > Some missing basic feautures/things, about i thinked: > 1) After looking at logs, i see that spockproxy opens 50 connections > to meta database, this too many (for shards this is ok) > 2) I can't specify port for meta database You can do this in the config file or on the command line I believe (I only use the 3306 port so I'm not sure) > So, i have following questions: > 1) Do you plan to release in nearly future next version of > spockproxy (may be with fixes for compilation process)? I don't think we have a new version planned anytime soon. > 2) get_next_id() function is not present in sample medatbase dump, > so is good idea to add it, can you give me code for it? It is much like you wrote - but we have added locks to make is thread safe. But remember that since the proxy will request many next id's at a time it is unlikely that threads would collide (the proxy will ensure this because it hands out the id's) > 3) Spockproxy support failover for high availability? I plan to have > 2 servers for each shard with master-master replication. You have to make your own failover - but this is also much easier than you might think. Once configured the Spockproxy only takes a second to restart even for the biggest, busiest DB's. For HA the same proxy can be told to connect to a different master but currently you will have to restart the proxy. > 4) Can i contribute code for spockproxy (patches for compilation, > etc)? Yes - because of some changes at Spock we have not been working on our proxy very much lately. Frank |
From: Pavel G. <pa...@gm...> - 2009-11-10 15:03:12
|
Hello, spockproxy developers! I'm very impressed with spockproxy and tried to use it. But i got some problems: 1. I can't compile spockproxy with gcc 4.4.x from unmodified sources. for solving this i did: a) delete "-Werror" from src/Makefile.am, because new gcc prints many warnings. b) modify some compilation errors (mostly errors was about missing #include <stdio.h>) c) link spockproxy by hands because make libtool prints many errors and i don't know how to fix them (i'm not very familiar with libtool): /bin/sh ../libtool --tag=CXX --mode=link g++ -g -O2 -o spockproxy spockproxy-spockproxy.o spockproxy-network-mysqld.o spockproxy-network-mysqld-proto.o spockproxy-network-mysqld-proxy.o spockproxy-network-mysqld-server.o spockproxy-network-mysqld-table.o spockproxy-network-conn-pool.o spockproxy-network-socket.o spockproxy-sql-tokenizer.o spockproxy-glib-ext.o spockproxy-mysql_wrapper.o spockproxy-partition.o spockproxy-sql-parser.o spockproxy-perf_monitor.o spockproxy-messages.o spockproxy-resultset_merge.o -levent -lglib-2.0 -L/usr/lib/mysql -lmysqlclient_r -lz -lpthread -lcrypt -lnsl -lm -lpthread -lrt -L/usr/lib -lssl -lcrypto ../libtool: line 849: X--tag=CXX: command not found ../libtool: line 882: libtool: ignoring unknown tag : command not found ../libtool: line 849: X--mode=link: command not found ../libtool: line 1016: *** Warning: inferring the mode of operation is deprecated.: command not found ../libtool: line 1017: *** Future versions of Libtool will require --mode=MODE be specified.: command not found gcc: no input files gcc: no input files gcc: no input files gcc: no input files ../libtool: line 2254: X-g: command not found ../libtool: line 2254: X-O2: command not found ../libtool: line 1973: X-L/usr/lib/mysql: No such file or directory ../libtool: line 1973: X-L/usr/lib: No such file or directory ../libtool: line 2423: Xspockproxy: command not found After that i tried to run spockproxy and it started successfully! after playing with selects i tried to insert record in database. But it failed, after examining spockproxy logs i found, that get_next_id() function in meta database i missing. I wrote a simple substitute: CREATE FUNCTION `get_next_id`(table_name_ VARCHAR(100), ids_count_ INTEGER(11)) RETURNS bigint(20) NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT '' begin declare id bigint; select next_id from shard_table_directory where table_name=table_name_ into id; update shard_table_directory set next_id=next_id+ids_count_ where table_name=table_name_; return id; end; But as i understand, it is not thread-safe (if some spockproxy intances work simulateniously with meta database). Some missing basic feautures/things, about i thinked: 1) After looking at logs, i see that spockproxy opens 50 connections to meta database, this too many (for shards this is ok) 2) I can't specify port for meta database So, i have following questions: 1) Do you plan to release in nearly future next version of spockproxy (may be with fixes for compilation process)? 2) get_next_id() function is not present in sample medatbase dump, so is good idea to add it, can you give me code for it? 3) Spockproxy support failover for high availability? I plan to have 2 servers for each shard with master-master replication. 4) Can i contribute code for spockproxy (patches for compilation, etc)? PS: Thanks for reading this long letter. Sorry for my english, it is not my native language :-) |
From: Frank F. <fr...@co...> - 2009-07-15 18:41:58
|
Actually the easiest thing is to avoid that in the first place (see below) but sometimes it might be unavoidable so to answer your question: Reassigning the ranges is straight forward although it might (will) take some time to move the data around. To reassign a range you need to: 1 - decide on the new ranges and layout (obvious step but really important) 2 - stop writes 3 - export the data from ALL FEDERATED tables that is in the wrong shard - here you can be claver and dump the data according to the shards it will be going into so you can load it directly and not through the proxy. 4 - load the data from step 3 into the correct shard(s) 5 - delete the data from the wrong shards (the data you dumped in #3) 6 - update the shard_range_directory table in the universal DB. 7 - restart the proxy and enable writes Notice that you can write the SQL to do #3 ahead of time and run it simultaneously on all shards; you can also run #4 simultaneously on all shards. That will save considerable time. Also 4, 5 & 6 are somewhat interchangeable. if you delete the data (#5) right away it will not show up when it's searched for, if you load it (#4) before it's deleted you could see it twice (two rows for the same record) depending on the query and if the proxy is searching one or all shards. If you update the shard_range_directory (#6) first you would not see records when you searched by the shard key but you would see those records when you searched by some other means. Best bet is to go off the air during this time or fix this on some slaves and switch them to drive the site. -- OK but to avoid the problem in the first place. Suppose we have a Twitter like application where the tweets are loaded into 10 shards in blocks of 10 million. I think what would happen is the current shard (the one where the new tweets are going) would be very busy and the others would be largely idle because there's nothing so old as last months tweets. So shard by user_id still we might have a similar situation where the typical user signs up tweets for a few weeks and gets bored and stops. Here again the current shard is busy holding most of the active users and the first shard approaches idle. To avoid this make the slices (ranges) smaller and have them wrap around so before shard 1 had id's 1 to 10,000,000 now it holds id's 1 to 1,000,000 and 10,000,001 to 11,000,000 and 20,000,001 to 21,000,000 and 30,000,001 to 31,000,000 and 40,000,001 to 41,000,000 and 50,000,001 to 51,000,000... This arrangement works well and it better distributes the new records to all the shards. Of course this example might not match your specific problem but the basic idea is to prevent some shards form holding stale data. Good Luck, Frank On Jul 15, 2009, at 9:06 AM, Mario Menti wrote: > Hi there, > > we're looking at options for sharding a MySql db, and Spock Proxy > looks > promising. I was hoping someone could answer a quick question: if > existing shards become idle over time (which may often be the case > with > range-based sharding), is there a way to assign new ranges to > existing/old shards? And more generally, are there any other pain > points > users are aware of with a Rails/Spock Proxy setup? > > Cheers, > Mario. > > ------------------------------------------------------------------------------ > Enter the BlackBerry Developer Challenge > This is your chance to win up to $100,000 in prizes! For a limited > time, > vendors submitting new applications to BlackBerry App World(TM) will > have > the opportunity to enter the BlackBerry Developer Challenge. See > full prize > details at: http://p.sf.net/sfu/Challenge > _______________________________________________ > spockproxy-devel mailing list > spo...@li... > https://lists.sourceforge.net/lists/listinfo/spockproxy-devel |
From: Frank F. <fr...@co...> - 2009-07-15 18:40:48
|
I suspect you have some client and server settings that are mismatched and the proxy it trying to set them and not succeeding. Can you verify the the proxy has connected to all the databases? (SHOW PROCESSLIST on each DB - is the proxy connected?) Can you run the proxy, the database server and client all with the same my.cnf file? Can you use a minor one (just as a test) and try not setting any client settings. Frank On Jul 14, 2009, at 12:20 PM, Danny Lockard wrote: > Hello, > > I have been invoking spockproxy with the command "spockproxy > --partition-info-host=10.0.96.50 --partition-info-database=db_mapping > --db-user=spock --db-user-password=spock > --proxy-address=10.0.96.56:4040 --admin-address=10.0.96.56:4041" It > shortly thereafter tells me INFO loading db mapping info ... then INFO > done db mapping info loading in 0 seconds. It then hangs. I try to > connect to the mysql server at 10.0.96.56:4040 but it too just hangs > there. Spockproxy is hung so badly that i have to kill -9 it. > > -Daniel Lockard > > ------------------------------------------------------------------------------ > Enter the BlackBerry Developer Challenge > This is your chance to win up to $100,000 in prizes! For a limited > time, > vendors submitting new applications to BlackBerry App World(TM) will > have > the opportunity to enter the BlackBerry Developer Challenge. See > full prize > details at: http://p.sf.net/sfu/Challenge > _______________________________________________ > spockproxy-devel mailing list > spo...@li... > https://lists.sourceforge.net/lists/listinfo/spockproxy-devel |
From: Daniel L. <dan...@gm...> - 2009-07-15 18:16:30
|
I resolved this issue, but now am having issues with it says, "bad handshake" and something about having trouble getting the default DB. I have since decided against using spock-proxy though, so no big deal. -Danny Lockard On Jul 15, 2009, at 1:13 PM, Frank Flynn wrote: > I suspect you have some client and server settings that are > mismatched and the proxy it trying to set them and not succeeding. > > Can you verify the the proxy has connected to all the databases? > (SHOW PROCESSLIST on each DB - is the proxy connected?) > > Can you run the proxy, the database server and client all with the > same my.cnf file? Can you use a minor one (just as a test) and try > not setting any client settings. > > Frank > > On Jul 14, 2009, at 12:20 PM, Danny Lockard wrote: > >> Hello, >> >> I have been invoking spockproxy with the command "spockproxy >> --partition-info-host=10.0.96.50 --partition-info-database=db_mapping >> --db-user=spock --db-user-password=spock >> --proxy-address=10.0.96.56:4040 --admin-address=10.0.96.56:4041" It >> shortly thereafter tells me INFO loading db mapping info ... then >> INFO >> done db mapping info loading in 0 seconds. It then hangs. I try to >> connect to the mysql server at 10.0.96.56:4040 but it too just hangs >> there. Spockproxy is hung so badly that i have to kill -9 it. >> >> -Daniel Lockard >> >> ------------------------------------------------------------------------------ >> Enter the BlackBerry Developer Challenge >> This is your chance to win up to $100,000 in prizes! For a limited >> time, >> vendors submitting new applications to BlackBerry App World(TM) >> will have >> the opportunity to enter the BlackBerry Developer Challenge. See >> full prize >> details at: http://p.sf.net/sfu/Challenge >> _______________________________________________ >> spockproxy-devel mailing list >> spo...@li... >> https://lists.sourceforge.net/lists/listinfo/spockproxy-devel > |
From: Mario M. <ma...@me...> - 2009-07-15 16:27:49
|
Hi there, we're looking at options for sharding a MySql db, and Spock Proxy looks promising. I was hoping someone could answer a quick question: if existing shards become idle over time (which may often be the case with range-based sharding), is there a way to assign new ranges to existing/old shards? And more generally, are there any other pain points users are aware of with a Rails/Spock Proxy setup? Cheers, Mario. |
From: Danny L. <dan...@gm...> - 2009-07-14 19:20:21
|
Hello, I have been invoking spockproxy with the command "spockproxy --partition-info-host=10.0.96.50 --partition-info-database=db_mapping --db-user=spock --db-user-password=spock --proxy-address=10.0.96.56:4040 --admin-address=10.0.96.56:4041" It shortly thereafter tells me INFO loading db mapping info ... then INFO done db mapping info loading in 0 seconds. It then hangs. I try to connect to the mysql server at 10.0.96.56:4040 but it too just hangs there. Spockproxy is hung so badly that i have to kill -9 it. -Daniel Lockard |
From: Anand K. <an...@en...> - 2009-06-30 19:27:34
|
I have been trying to get spock to work. However, i am facing some issues. The configuration is simple and as per your example. (1) using mysql root user with proper password. (2) Installed spock proxy and all associated packages needed. (3) configured using the config file #listening address:port of the proxy-server ,default is ":4040" PROXY_ADDRESS=:4040 #listening address:port of internal admin-server, default is ":4041" ADMIN_ADDRESS=:4041 #host of table partition information tables, this MUST be set PARTITION_INFO_HOST=127.0.0.1 #database name of the table partition info. this MUST be set PARTITION_INFO_DB=db_mapping #db user for connecting to back end servers and connecting to the proxy, #this MUST be set DB_USER=root #password for the DB_USER DB_PASSWD=tiger (4) DB created and populated using the example script provided. +-------------+-----------+-------------+---------------+ | database_id | host_name | port_number | database_name | +-------------+-----------+-------------+---------------+ | 1 | 127.0.0.1 | 3306 | db_sample_1 | | 2 | 127.0.0.1 | 3306 | db_sample_2 | +-------------+-----------+-------------+---------------+ Rest of the tables are populated with proper data. Looks like the proxy starts off initially by reading the config and creating the connection pool. Until this the logs seems to be ok, the info messages say there isnt any problems. But these are followed by a lot of errors. The 2 main errors that i see are (a) Tue Jun 30 13:58:57 2009 WARNING network-mysqld-proxy.cpp.5204: handshake packet error Tue Jun 30 13:58:57 2009 WARNING ^H.2762: CON_STATE_ASYNC_READ_HANDSHAKE con_read_handshake failed returned an error and (b) Tue Jun 30 13:58:57 2009 WARNING network-mysqld.cpp.3069: error in selecting default database: Bad handshake Tue Jun 30 13:58:57 2009 WARNING network-mysqld.cpp.3069: error in selecting default database: Bad handshake And after a while, i start seeing these messages Tue Jun 30 14:53:08 2009 ERROR network-mysqld.cpp.540: socket(127.0.0.1:3306) failed: Too many open files Tue Jun 30 14:53:08 2009 ERROR network-mysqld.cpp.540: socket(127.0.0.1:3306) failed: Too many open files If i connect through mysql command mysql -uroot -ptiger -h127.0.0.1 -P3306 db_mapping, i can select and operate on the tables etc. So, looks like the mysql is running fine and accepting connections. Im using Linux version 2.6.18-92.el5PAE (moc...@bu...) (gcc version 4.1.2 20071124 (Red Hat 4.1.2-42)) #1 SMP Tue Jun 10 19:22:41 EDT 2008 mysql Ver 14.12 Distrib 5.0.45 Any pointers would be greatly appreciated. |
From: Graham F. <gr...@si...> - 2009-06-12 14:26:29
|
Frank, Thanks for the detailed answer. > > This is less useful than you might think. Consider a schema with a > 'users' table sharded as you describe, name starting with 'a' or 'b' > in shard 1, 'c' or 'd' in shard 2 and so on. This works well for the > users table, but only that table. If we have a 'payment' table which > records each user payment that payment table must have the shard key > (user_name) in it or the proxy cannot know where to insert a new > payment row. That is the same for all the other tables; you would do > much better to shard on user_id and index users.name, users.id so that > the query to lookup a users id from a name is quick. Now it will be > fast to either join this to other tables or to retrieve the users.id > and query using this value. > I can see that this is not as useful in the general case, but it is still useful in my specific case, where I have a very simple schema. (There is basically a single sharded table). > There is another aspect about this where I would be very cautious - I > once worked for a library who had a reservation system where they > would store your requested material by last name. Turned out there > that 'm' was almost 1/3 of their customers. I don't have any kind of > metrics of how typical this is - perhaps they had a large Scottish > population in their customer base but my point is that it can be hard > to balance your shards when your key is an attribute that you don't > control. An arbitrary key such as an id column is easier to manipulate. > Being from Scotland (as I think you have guessed) I found this a useful example! > > My thought is that Spockproxy should become a plugin as MySQL proxy > starts to support plugins (shortly after version 1 according to Jan > Kneschke). Originally I thought this would be very complex but in > writing this answer I'm thinking it might not be too horrible. Let's > say we have some number of shards (say 4) and the same number of read > slaves for a total of 8 databases - and this is a simple case it gets > more complex if you have several sets of read slaves. > > When a query comes in the Spockproxy / Replication Monitor will have > to follow some logic like this: > > Given the pool of all the data servers (except the universal DB which > is only ever used for universal writes) > > Query contains a shard key? > YES -> Reduce the pool of available DB servers to only those serving > the one needed shard > NO -> do nothing > Query is READ or WRITE? > READ - choose the least busy server(s) from the pool one for each > shard in the pool > WRITE - Are you writing UNIVERSAL or SHARDED data? > UNIVERSAL - choose the one universal db (this is never in the > pool) > SHARDED - choose the active master server(s) from the pool > one for > each shard in the pool > > The issues is Spock proxy does not have any of the Replication Monitor > features and as it is we've probably removed some vital components to > implement this in our code base (remember we're a fork off of 0.6). > This is why I mentioned the plug in as a goal; so folks could mix and > match features in order to create a solution to meet their specific > needs. > > In the short term you might run both in series - for each shard pair > run an MMM and present these to the Spockproxy as the database > shards. That ought to work (let us know if it does). > I have run this configuration in a test. It was working fairly well until I used MMM to set one of the Shards offline and then back online. At that point, it appeared that SpockProxy ran out of available database connections. I haven't delved deeply into the code to look for the reconnect logic that SpockProxy has for the connections to the shards. It may also be that I have some errors in the experimental changes I made to SpockProxy, so I intend to back my changes out and retry the test at some point. In the meantime, if you know of any issues there may be with the connection pool and reconnecting, I would be keen to know about these. > > I am willing to help with any implementation/testing of these > > features, but would want to make sure that the design of these > > features would be acceptable before I proceed. > > We are not doing much development on Spockproxy right now; as I said > my thought was to move into a simpler plugin but feel the freedom if > you like or as MySQL proxy supports plugins help us then move to a > plug in. > I will probably make the changes that I require locally, and monitor this list for any news of the MySQL Proxy plugin. Graham |
From: Frank F. <fr...@co...> - 2009-06-11 20:18:01
|
On Jun 10, 2009, at 3:16 AM, Graham Finlayson wrote: > First of all, thanks for the development that has gone into this, as > it looks to be just what I was looking for. I have a couple of > suggestions which I think would be useful additions > > 1. I want to create shards based on a character database key. E.g. > All users from 'a' to 'b' are stored in shard1. The current > implementation only allows the sharding key to be an integer value. > I can easily 'hack' this in by mapping the first character of the > key to an integer value, but it would be nicer to have this properly > supported. Are there any plans to do this? This is less useful than you might think. Consider a schema with a 'users' table sharded as you describe, name starting with 'a' or 'b' in shard 1, 'c' or 'd' in shard 2 and so on. This works well for the users table, but only that table. If we have a 'payment' table which records each user payment that payment table must have the shard key (user_name) in it or the proxy cannot know where to insert a new payment row. That is the same for all the other tables; you would do much better to shard on user_id and index users.name, users.id so that the query to lookup a users id from a name is quick. Now it will be fast to either join this to other tables or to retrieve the users.id and query using this value. There is another aspect about this where I would be very cautious - I once worked for a library who had a reservation system where they would store your requested material by last name. Turned out there that 'm' was almost 1/3 of their customers. I don't have any kind of metrics of how typical this is - perhaps they had a large Scottish population in their customer base but my point is that it can be hard to balance your shards when your key is an attribute that you don't control. An arbitrary key such as an id column is easier to manipulate. > 2. I am looking to use SpockProxy in conjunction with the MySQL > Master Master Replication Monitor, where all the Shards are Master/ > Master Active/Passive MySQL servers (MMM). MMM can define a > writerRole, to write to the currently active MySQL server, and two > readerRoles to allow reads to be done on both MySQL servers. > SpockProxy supports this out of the box if you configure the > host_name to be the MMM writerRole. However, this means that the > Passive MySQL server sits fairly idle, which is a waste of resources > in my mind. Therefore, a useful extention, I think, to SpockProxy > would be to allow the readerRoles to be configured, and have > SpockProxy round robin any "read" requests between the Active and > Passive MySQL servers to balance the load a little when both servers > are active. My thought is that Spockproxy should become a plugin as MySQL proxy starts to support plugins (shortly after version 1 according to Jan Kneschke). Originally I thought this would be very complex but in writing this answer I'm thinking it might not be too horrible. Let's say we have some number of shards (say 4) and the same number of read slaves for a total of 8 databases - and this is a simple case it gets more complex if you have several sets of read slaves. When a query comes in the Spockproxy / Replication Monitor will have to follow some logic like this: Given the pool of all the data servers (except the universal DB which is only ever used for universal writes) Query contains a shard key? YES -> Reduce the pool of available DB servers to only those serving the one needed shard NO -> do nothing Query is READ or WRITE? READ - choose the least busy server(s) from the pool one for each shard in the pool WRITE - Are you writing UNIVERSAL or SHARDED data? UNIVERSAL - choose the one universal db (this is never in the pool) SHARDED - choose the active master server(s) from the pool one for each shard in the pool The issues is Spock proxy does not have any of the Replication Monitor features and as it is we've probably removed some vital components to implement this in our code base (remember we're a fork off of 0.6). This is why I mentioned the plug in as a goal; so folks could mix and match features in order to create a solution to meet their specific needs. In the short term you might run both in series - for each shard pair run an MMM and present these to the Spockproxy as the database shards. That ought to work (let us know if it does). > I am willing to help with any implementation/testing of these > features, but would want to make sure that the design of these > features would be acceptable before I proceed. We are not doing much development on Spockproxy right now; as I said my thought was to move into a simpler plugin but feel the freedom if you like or as MySQL proxy supports plugins help us then move to a plug in. Frank |
From: Graham F. <gr...@si...> - 2009-06-10 12:04:51
|
First of all, thanks for the development that has gone into this, as it looks to be just what I was looking for. I have a couple of suggestions which I think would be useful additions 1. I want to create shards based on a character database key. E.g. All users from 'a' to 'b' are stored in shard1. The current implementation only allows the sharding key to be an integer value. I can easily 'hack' this in by mapping the first character of the key to an integer value, but it would be nicer to have this properly supported. Are there any plans to do this? 2. I am looking to use SpockProxy in conjunction with the MySQL Master Master Replication Monitor, where all the Shards are Master/Master Active/Passive MySQL servers (MMM). MMM can define a writerRole, to write to the currently active MySQL server, and two readerRoles to allow reads to be done on both MySQL servers. SpockProxy supports this out of the box if you configure the host_name to be the MMM writerRole. However, this means that the Passive MySQL server sits fairly idle, which is a waste of resources in my mind. Therefore, a useful extention, I think, to SpockProxy would be to allow the readerRoles to be configured, and have SpockProxy round robin any "read" requests between the Active and Passive MySQL servers to balance the load a little when both servers are active. I am willing to help with any implementation/testing of these features, but would want to make sure that the design of these features would be acceptable before I proceed. Graham |
From: Alexey K. <al...@ko...> - 2009-06-03 17:23:16
|
Here is what I get: [root@master-db etc]# cat texts.conf | egrep -v '^(#|$)' PROXY_ADDRESS=:33060 ADMIN_ADDRESS=:44040 PARTITION_INFO_HOST=master-db.local PARTITION_INFO_DB=spock_config DB_USER=spock DB_PASSWD=Qy05Owhm DEFAULT_SELECT_LIMIT=1000000 LOGFILE=/opt/spock-proxy/var/texts.log SKIP_PROFILING=1 FIX_BUG_25371=0 PID_FILE=/opt/spock-proxy/var/texts.pid LOG_DEBUG_MSG=1 LOG_ALL_QUERIES=1 MAX_CONN_IDLE_TIME=3600 MAX_CONN_POOL_SIZE=5 LOG_RAW_DATA=1 [root@master-db etc]# mysql -u spock -pQy05Owhm -h master-db.local spock_config Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15722181 Server version: 5.0.77-percona-b13-log MySQL Percona Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show tables; +--------------------------+ | Tables_in_spock_config | +--------------------------+ | shard_database_directory | | shard_range_directory | | shard_table_directory | +--------------------------+ 3 rows in set (0.00 sec) mysql> select * from shard_database_directory; +-------------+-----------------+-------------+------------------------+ | database_id | host_name | port_number | database_name | +-------------+-----------------+-------------+------------------------+ | 4 | master-db.local | 3306 | collegelist_production | +-------------+-----------------+-------------+------------------------+ 1 row in set (0.10 sec) mysql> Bye [root@master-db etc]# mysql -u spock -pQy05Owhm -h master-db.local collegelist_production Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15722290 Server version: 5.0.77-percona-b13-log MySQL Percona Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> So, the password, login, db are ok. On Wed, Jun 3, 2009 at 12:50 PM, Frank Flynn <fr...@co...> wrote: > Check the login, password, host and database name in your config (or from > your command line). Try connecting directly to the universal DB from that > machine with the same account. > > And the permissions of that user account (it must be able to read the 3 > shard_*_tables) > > I suspect that it cannot get this far. > > > On Jun 3, 2009, at 2:11 AM, Alexey Kovyrin wrote: > >> Hello, >> >> I work for scribd.com and we're trying to use spockproxy for some of >> our projects here... I've set it up locally, developed all the changes >> in our code that'd allow us to use the proxy, tested it and everything >> was fine... until we decided to put this configuration on our >> production servers. >> >> So, the problem is the following: when spockproxy is trying to connect >> to our mysql servers we see the following: >> >> Wed Jun 3 05:00:51 2009 WARNING network-mysqld.cpp.3090: error in >> selecting default database: Bad handshake >> >> Our production servers are on 5.0.77. >> >> I've tested the same configuration on a server with 5.1.30 and the >> result was even more interesting: >> >> Wed Jun 3 03:14:17 2009 WARNING network-mysqld.cpp.3090: error in >> selecting default database: Got packets out of order. >> >> Changing FIX_BUG_25371 option does not change anything. >> >> So, the question is: what should we do here? :-/ >> >> -- >> Alexey Kovyrin > > -- Alexey Kovyrin http://kovyrin.info/ |
From: Frank F. <fr...@co...> - 2009-06-03 17:21:21
|
Check the login, password, host and database name in your config (or from your command line). Try connecting directly to the universal DB from that machine with the same account. And the permissions of that user account (it must be able to read the 3 shard_*_tables) I suspect that it cannot get this far. On Jun 3, 2009, at 2:11 AM, Alexey Kovyrin wrote: > Hello, > > I work for scribd.com and we're trying to use spockproxy for some of > our projects here... I've set it up locally, developed all the changes > in our code that'd allow us to use the proxy, tested it and everything > was fine... until we decided to put this configuration on our > production servers. > > So, the problem is the following: when spockproxy is trying to connect > to our mysql servers we see the following: > > Wed Jun 3 05:00:51 2009 WARNING network-mysqld.cpp.3090: error in > selecting default database: Bad handshake > > Our production servers are on 5.0.77. > > I've tested the same configuration on a server with 5.1.30 and the > result was even more interesting: > > Wed Jun 3 03:14:17 2009 WARNING network-mysqld.cpp.3090: error in > selecting default database: Got packets out of order. > > Changing FIX_BUG_25371 option does not change anything. > > So, the question is: what should we do here? :-/ > > -- > Alexey Kovyrin |
From: Alexey K. <al...@ko...> - 2009-06-03 09:12:03
|
Hello, I work for scribd.com and we're trying to use spockproxy for some of our projects here... I've set it up locally, developed all the changes in our code that'd allow us to use the proxy, tested it and everything was fine... until we decided to put this configuration on our production servers. So, the problem is the following: when spockproxy is trying to connect to our mysql servers we see the following: Wed Jun 3 05:00:51 2009 WARNING network-mysqld.cpp.3090: error in selecting default database: Bad handshake Our production servers are on 5.0.77. I've tested the same configuration on a server with 5.1.30 and the result was even more interesting: Wed Jun 3 03:14:17 2009 WARNING network-mysqld.cpp.3090: error in selecting default database: Got packets out of order. Changing FIX_BUG_25371 option does not change anything. So, the question is: what should we do here? :-/ -- Alexey Kovyrin http://kovyrin.info/ |
From: Xu, Y. <yu...@co...> - 2009-05-28 13:19:40
|
Hi Sir/Madam, I basicly follow your instruction ./autogen.sh, ./configure --without-lua, and make install. but I got the follow error when I excute "make install". How could I resolve this issue? Could you tell me how to make a binary build for 64 bit under 32 bit machine. [root@yudixu spockproxy]# ./configure -V spockproxy configure 0.8.7 generated by GNU Autoconf 2.59 Copyright (C) 2003 Free Software Foundation, Inc. This configure script is free software; the Free Software Foundation gives unlimited permission to copy, distribute and modify it. [root@yudixu spockproxy]# make install Making install in src make[1]: Entering directory `/root/sw/spockproxy/src' if g++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include/mysql -I/usr/include/glib-2.0 -I/usr/lib/glib-2.0/include -Werror -Wall -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -D_LARGE_FILES -g -O2 -MT spockproxy-spockproxy.o -MD -MP -MF ".deps/spockproxy-spockproxy.Tpo" -c -o spockproxy-spockproxy.o `test -f 'spockproxy.cpp' || echo './'`spockproxy.cpp; \ then mv -f ".deps/spockproxy-spockproxy.Tpo" ".deps/spockproxy-spockproxy.Po"; else rm -f ".deps/spockproxy-spockproxy.Tpo"; exit 1; fi if g++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include/mysql -I/usr/include/glib-2.0 -I/usr/lib/glib-2.0/include -Werror -Wall -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -D_LARGE_FILES -g -O2 -MT spockproxy-network-mysqld.o -MD -MP -MF ".deps/spockproxy-network-mysqld.Tpo" -c -o spockproxy-network-mysqld.o `test -f 'network-mysqld.cpp' || echo './'`network-mysqld.cpp; \ then mv -f ".deps/spockproxy-network-mysqld.Tpo" ".deps/spockproxy-network-mysqld.Po"; else rm -f ".deps/spockproxy-network-mysqld.Tpo"; exit 1; fi if g++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include/mysql -I/usr/include/glib-2.0 -I/usr/lib/glib-2.0/include -Werror -Wall -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -D_LARGE_FILES -g -O2 -MT spockproxy-network-mysqld-proto.o -MD -MP -MF ".deps/spockproxy-network-mysqld-proto.Tpo" -c -o spockproxy-network-mysqld-proto.o `test -f 'network-mysqld-proto.cpp' || echo './'`network-mysqld-proto.cpp; \ then mv -f ".deps/spockproxy-network-mysqld-proto.Tpo" ".deps/spockproxy-network-mysqld-proto.Po"; else rm -f ".deps/spockproxy-network-mysqld-proto.Tpo"; exit 1; fi if g++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include/mysql -I/usr/include/glib-2.0 -I/usr/lib/glib-2.0/include -Werror -Wall -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -D_LARGE_FILES -g -O2 -MT spockproxy-network-mysqld-proxy.o -MD -MP -MF ".deps/spockproxy-network-mysqld-proxy.Tpo" -c -o spockproxy-network-mysqld-proxy.o `test -f 'network-mysqld-proxy.cpp' || echo './'`network-mysqld-proxy.cpp; \ then mv -f ".deps/spockproxy-network-mysqld-proxy.Tpo" ".deps/spockproxy-network-mysqld-proxy.Po"; else rm -f ".deps/spockproxy-network-mysqld-proxy.Tpo"; exit 1; fi network-mysqld-proxy.cpp: In function `int proxy_read_query_result_is_finished(network_socket*, int*)': network-mysqld-proxy.cpp:3688: error: case label ` COM_QUIT' not within a switch statement network-mysqld-proxy.cpp:3689: error: case label ` COM_STATISTICS' not within a switch statement network-mysqld-proxy.cpp:3693: error: break statement not within loop or switch network-mysqld-proxy.cpp:3695: error: case label ` COM_PREPARE' not within a switch statement network-mysqld-proxy.cpp:3764: error: break statement not within loop or switch network-mysqld-proxy.cpp:3766: error: case label ` COM_EXECUTE' not within a switch statement network-mysqld-proxy.cpp:3767: error: case label ` COM_QUERY' not within a switch statement network-mysqld-proxy.cpp:3933: error: break statement not within loop or switch network-mysqld-proxy.cpp:3935: error: case label ` COM_BINLOG_DUMP' not within a switch statement network-mysqld-proxy.cpp:3941: error: break statement not within loop or switch network-mysqld-proxy.cpp:3943: error: case label not within a switch statement network-mysqld-proxy.cpp:3947: error: break statement not within loop or switch network-mysqld-proxy.cpp: At global scope: network-mysqld-proxy.cpp:3950: error: expected unqualified-id before "return" network-mysqld-proxy.cpp:3951: error: expected declaration before '}' token network-mysqld-proxy.cpp: In function `int proxy_read_query_result_is_finished(network_socket*, int*)': network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_SLEEP' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_QUIT' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_QUERY' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_CREATE_DB' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_DROP_DB' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_REFRESH' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_STATISTICS' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_PROCESS_INFO' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_CONNECT' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_TIME' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_DELAYED_INSERT' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_BINLOG_DUMP' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_TABLE_DUMP' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_CONNECT_OUT' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_REGISTER_SLAVE' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_PREPARE' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_EXECUTE' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_LONG_DATA' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_CLOSE_STMT' not handled in switch network-mysqld-proxy.cpp:3687: warning: enumeration value `COM_END' not handled in switch network-mysqld-proxy.cpp: At global scope: network-mysqld-proxy.cpp:145: warning: 'plugin_con_state* plugin_con_state_init()' defined but not used network-mysqld-proxy.cpp:329: warning: 'plugin_srv_state* plugin_srv_state_init()' defined but not used network-mysqld-proxy.cpp:2270: warning: 'retval_t proxy_read_handshake(network_mysqld*, network_mysqld_con*)' defined but not used network-mysqld-proxy.cpp:2492: warning: 'retval_t proxy_multiserver_read_handshake(network_mysqld*, network_mysqld_con*)' defined but not used network-mysqld-proxy.cpp:2926: warning: 'retval_t proxy_read_auth_result(network_mysqld*, network_mysqld_con*)' defined but not used network-mysqld-proxy.cpp:3003: warning: 'retval_t proxy_multiserver_read_auth_result(network_mysqld*, network_mysqld_con*)' defined but not used network-mysqld-proxy.cpp:3248: warning: 'retval_t proxy_read_query(network_mysqld*, network_mysqld_con*)' defined but not used network-mysqld-proxy.cpp:3274: warning: 'retval_t proxy_get_server_list(network_mysqld*, network_mysqld_con*)' defined but not used network-mysqld-proxy.cpp:3381: warning: 'retval_t proxy_get_server_connection_list(network_mysqld*, network_mysqld_con*)' defined but not used network-mysqld-proxy.cpp:3469: warning: 'retval_t proxy_send_query_result(network_mysqld*, network_mysqld_con*)' defined but not used make[1]: *** [spockproxy-network-mysqld-proxy.o] Error 1 make[1]: Leaving directory `/root/sw/spockproxy/src' make: *** [install-recursive] Error 1 Thanks, Yudi |
From: Burak B. <bur...@gm...> - 2009-03-23 22:18:11
|
Hi, I downloaded the tarball, untarred and did ./autogen.sh. I get /usr/share/automake-1.10/am/depend2.am: am__fastdepCXX does not appear in AM_CONDITIONAL /usr/share/automake-1.10/am/depend2.am: The usual way to define `am__fastdepCXX' is to add `AC_PROG_CXX' /usr/share/automake-1.10/am/depend2.am: to `configure.in' and run `aclocal' and `autoconf' again. src/Makefile.am: C++ source seen but `CXX' is undefined src/Makefile.am: The usual way to define `CXX' is to add `AC_PROG_CXX' src/Makefile.am: to `configure.in' and run `autoconf' again. configure.in: installing `./ylwrap' Any ideas? Any help will be much appreciated.. |
From: Burak B. <bur...@gm...> - 2009-03-21 01:48:36
|
Ok, I added AC_PROG_CXX to configure.in that worked, but now I get the following. I have lua5.1 installed. I am on Ubuntu 8.1. BTW: Would it be possible to make binary release like MySQL Proxy guys? ---- burak@bizdik:~/Desktop/0.8.0$ ./configure checking for g++... g++ checking for C++ compiler default output file name... a.out checking whether the C++ compiler works... yes checking whether we are cross compiling... no checking for suffix of executables... checking for suffix of object files... o checking whether we are using the GNU C++ compiler... yes checking whether g++ accepts -g... yes checking build system type... i686-pc-linux-gnu checking host system type... i686-pc-linux-gnu checking target system type... i686-pc-linux-gnu checking for a BSD-compatible install... /usr/bin/install -c checking whether build environment is sane... yes checking for a thread-safe mkdir -p... /bin/mkdir -p checking for gawk... gawk checking whether make sets $(MAKE)... yes checking for style of include used by make... GNU checking dependency style of g++... gcc3 checking whether to enable maintainer-specific portions of Makefiles... no checking for gcc... gcc checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking dependency style of gcc... gcc3 ./configure: line 3995: AC_PROG_LD: command not found checking for a BSD-compatible install... /usr/bin/install -c checking for gawk... (cached) gawk checking for flex... no checking for lex... no checking how to run the C preprocessor... gcc -E checking whether ln -s works... yes checking whether make sets $(MAKE)... (cached) yes checking whether gcc and cc understand -c and -o together... yes checking for grep that handles long lines and -e... /bin/grep checking for egrep... /bin/grep -E checking for AIX... no checking for library containing strerror... none required checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking minix/config.h usability... no checking minix/config.h presence... no checking for minix/config.h... no checking for function prototypes... yes checking for string.h... (cached) yes ./configure: line 5573: AC_DISABLE_STATIC: command not found ./configure: line 5575: AC_PROG_LIBTOOL: command not found checking for ANSI C header files... (cached) yes checking for sys/wait.h that is POSIX.1 compatible... yes checking arpa/inet.h usability... yes checking arpa/inet.h presence... yes checking for arpa/inet.h... yes checking netinet/in.h usability... yes checking netinet/in.h presence... yes checking for netinet/in.h... yes checking sys/filio.h usability... no checking sys/filio.h presence... no checking for sys/filio.h... no checking sys/socket.h usability... yes checking sys/socket.h presence... yes checking for sys/socket.h... yes checking sys/time.h usability... yes checking sys/time.h presence... yes checking for sys/time.h... yes checking sys/un.h usability... yes checking sys/un.h presence... yes checking for sys/un.h... yes checking signal.h usability... yes checking signal.h presence... yes checking for signal.h... yes checking fcntl.h usability... yes checking fcntl.h presence... yes checking for fcntl.h... yes checking valgrind/valgrind.h usability... no checking valgrind/valgrind.h presence... no checking for valgrind/valgrind.h... no checking for an ANSI C-conforming const... yes checking for inline... inline checking whether char is unsigned... no checking return type of signal handlers... void checking whether lstat dereferences a symlink specified with a trailing slash... yes checking whether stat accepts an empty string... no checking for strftime... yes checking for library containing socket... none required checking for library containing gethostbyname... none required checking for library containing hstrerror... none required checking for socklen_t... yes checking for MySQL support... yes checking for mysql_config... /usr/bin/mysql_config checking for MySQL includes at... -I/usr/include/mysql checking errmsg.h usability... yes checking errmsg.h presence... yes checking for errmsg.h... yes checking mysql.h usability... yes checking mysql.h presence... yes checking for mysql.h... yes checking for mysqltest binary... /usr/bin/mysqltest checking for mysql libs... -Wl,-Bsymbolic-functions -rdynamic -L/usr/lib/mysql -lmysqlclient_r checking for pkg-config... /usr/bin/pkg-config checking for lua... yes checking pkg-config is at least version 0.9.0... yes checking for LUA... no checking for LUA... configure: error: Package requirements (lua5.1 >= 5.1) were not met: No package 'lua5.1' found Consider adjusting the PKG_CONFIG_PATH environment variable if you installed software in a non-standard prefix. Alternatively, you may set the environment variables LUA_CFLAGS and LUA_LIBS to avoid the need to call pkg-config. See the pkg-config man page for more details. Burak Bayramli wrote: > Hi, I downloaded the tarball, untarred and did ./autogen.sh. I get > > /usr/share/automake-1.10/am/depend2.am: am__fastdepCXX does not appear > in AM_CONDITIONAL > /usr/share/automake-1.10/am/depend2.am: The usual way to define > `am__fastdepCXX' is to add `AC_PROG_CXX' > /usr/share/automake-1.10/am/depend2.am: to `configure.in' and run > `aclocal' and `autoconf' again. > src/Makefile.am: C++ source seen but `CXX' is undefined > src/Makefile.am: The usual way to define `CXX' is to add `AC_PROG_CXX' > src/Makefile.am: to `configure.in' and run `autoconf' again. > configure.in: installing `./ylwrap' > > Any ideas? Any help will be much appreciated.. |
From: Burak B. <bur...@gm...> - 2009-03-21 01:15:40
|
Hi, I downloaded the tarball, untarred and did ./autogen.sh. I get /usr/share/automake-1.10/am/depend2.am: am__fastdepCXX does not appear in AM_CONDITIONAL /usr/share/automake-1.10/am/depend2.am: The usual way to define `am__fastdepCXX' is to add `AC_PROG_CXX' /usr/share/automake-1.10/am/depend2.am: to `configure.in' and run `aclocal' and `autoconf' again. src/Makefile.am: C++ source seen but `CXX' is undefined src/Makefile.am: The usual way to define `CXX' is to add `AC_PROG_CXX' src/Makefile.am: to `configure.in' and run `autoconf' again. configure.in: installing `./ylwrap' Any ideas? Any help will be much appreciated.. |
From: Frank F. <fr...@co...> - 2009-02-11 20:22:55
|
Indeed - there have been some significant improvements to MySQL cluster just in the last month; and I'm not completely familiar with all the improvements with the new MySQL Proxy. I'm looking forward the the MySQL users conference to get caught up. But I would say that being focused on sharding and connection pooling - Spockproxy is easier to set up to do these things. In Spockproxy there are 3 tables you need to populate: -shard_range_directory (the list of low and high id's for each shard) -shard_table_directory (the list of tables and are they 'universal' or 'federated') -shard_database_directory (the list of the database servers and connection info for the shards) Fill out these tables, load your schema in each shard and Spockproxy will work, there's nothing else you need to do; no LUA Scripts, nothing else to customize. (OK there's probably some tuning to your schema to optimize the DB for sharding but you'd have to do this for any sharding solution). In fact I'm giving a talk on Spockproxy at the MySQL users conf http://en.oreilly.com/mysql2009/public/schedule/detail/6867 I will explore the differences between Spockproxy and the current MySQL proxy. If you can't make that check out my blog where I will also cover some of these issues: http://www.frankf.us/wp/?cat=4 It future I do think it would be great to merge the Spockproxy back into the MySQL proxy for a whole variety of reasons but I should stress we're not particularly close to doing that. If you do shard with either solution I'd like to know your experiences - either email me personally or post here. Frank On Feb 11, 2009, at 9:21 AM, Jacques-Olivier Goussard wrote: > Hi > You mention on your site that > "MySQL Cluster was designed for high availability and performance, > not for sharding. All indices and the main data are stored in main > memory, which causes problems if your combined dataset is larger > than memory. It also requires changing storage engines." > > AFAIK, starting with MySQLCluster 5.1, only indexes are required to > stay in memory and support for user-defined distribution algorithms > was > added. > So now - how does SpockProxy compare with this solution ? It looks > to me that putting your distribution algo (from the spock table) into > MySQL cluster would remove the need for this proxy - but I'm no > expert. > > /jog > ------------------------------------------------------------------------------ > Create and Deploy Rich Internet Apps outside the browser with > Adobe(R)AIR(TM) > software. With Adobe AIR, Ajax developers can use existing skills > and code to > build responsive, highly engaging applications that combine the > power of local > resources and data with the reach of the web. Download the Adobe AIR > SDK and > Ajax docs to start building applications today-http://p.sf.net/sfu/adobe-com_______________________________________________ > spockproxy-devel mailing list > spo...@li... > https://lists.sourceforge.net/lists/listinfo/spockproxy-devel |
From: Jacques-Olivier G. <jog...@gm...> - 2009-02-11 17:21:09
|
Hi You mention on your site that "MySQL Cluster <http://www.mysql.com/products/database/cluster/> was designed for high availability and performance, not for sharding. All indices and the main data are stored in main memory, which causes problems if your combined dataset is larger than memory. It also requires changing storage engines." AFAIK, starting with MySQLCluster 5.1, only indexes are required to stay in memory and support for user-defined distribution algorithms was added. So now - how does SpockProxy compare with this solution ? It looks to me that putting your distribution algo (from the spock table) into MySQL cluster would remove the need for this proxy - but I'm no expert. /jog |
From: Zach G. <za...@ii...> - 2008-12-16 19:45:20
|
<!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'd sent them a pointer in October, since they do the mysqlperformanceblog and what some might consider the MySQL bible, the O'Reilly published High Performance MySQL.<br> <br> <a href="http://www.percona.com/files//presentations/Scaling-Web-Sites-by-Sharding-and-Replication-Meetup.pdf">Link</a><br> <br> Anyway, just thought I'd pass that along in case you guys hadn't see it. Hope you all have a great holiday!<br> <br> Zach<br> <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-12-04 19:40:49
|
On Dec 3, 2008, at 2:39 PM, Niv wrote: > > I have a problem with Spock Proxy. > I'm trying to run a query on all shards, and the query contains an > index hint ("USE INDEX"): > And the sharding key is actually none (shard_range_directory is > empty), the idea here is to run the query on all shards. OK currently we don't support the USE INDEX - we should (and will in our next build -whenever that is) because it's noting to to proxy to pass it along to each shard. A work around for today might be to build the indexes such that mysql would use them by default. Try adding all the columns you select into them (make it a covered query). > Since we're already on the subject, I have a related question. > > 1. If my query would look like > SELECT * FROM shard1.table WHERE key IN (x, y, z) > UNION ALL > SELECT * FROM shard2.table WHERE key IN (p, q, s); > > Will the two queries run in parallel and their results will be > combined by the proxy? Well no, that is you can't do that at all. That is you cannot specify the shard in the query - the whole point of the proxy is to hide all the sharding from your application (hide it from your programmers SQL) - things will just work. But two ways do occur to me to do what you appear to be trying to do. First off this is only useful if your have sharded data in an intelligent way - that is there is some sort of qualitative difference in the data in shard 1 and the data in shard 2. BEWARE the problem here is supposing you grow and need a shard 3 - you cannot do that, you'll have to add two new shards and change all your code to support this (changing your code is exactly what we're trying to avoid). To see an article on why what I'll call "hard loading" your data is a bad idea read http://qntm.org/?gay an amazingly in depth analysis of how to data model marriage (nothing to do with your issue but the idea that something changes - same sex marriage for example and the fields you called "husband" and "wife" are meaningless). Still if you insist: 1 - combine your query and add the shard key to the where clause (let's say your shard key is sk_id and 1 to 100000 is in shard 1 and 100001 to 200000 is shard 2) your single query becomes: SELECT * FROM shard1.table WHERE ( key IN (x, y, z) AND sk_id BETWEEN 1 AND 100000 ) OR (key IN (p, q, s) AND sk_id BETWEEN 100001 AND 200000); This works but depending on how the tables are indexed and how the optimizer looks at this it may not be very fast. 2 - Create a view in each shard that is a little bit different. in shard 1: CREATE VIEW foo AS SELECT * FROM table WHERE key IN (x, y, z); in shard 2: CREATE VIEW foo AS SELECT * FROM table WHERE key IN (p, q, s); Now through the proxy you can run: SELECT * FROM table --- this will return all data SELECT * FROM foo --- this will return key IN (x, y, z) from shard 1 and key IN (p, q, s) from shard 2 in one results set SELECT * FROM foo WHERE other_key = 10 --- same as above but with the additional other_key = 10 for both shards. > > 2. Is SQL_CALC_FOUND_ROWS supported? Will a post-query of SELECT > FOUND_ROWS() return the total number of rows that would have > returned from all shards without LIMIT? No, sorry but not supported and difficult to support because the proxy also does connection pooling which is great but any kind of post query function will not work because you're not guaranteed the same connection for the next query. > Thank you very much for your assistance and for releasing Spock > Proxy. It looks like it's going to help me a lot, and I would also > be glad to contribute back (for instance, if the features above are > not supported, I would love to help and implement them myself). > > Regards, > Niv Singer > Tracx > > On Wed, Dec 3, 2008 at 7:11 PM, Frank Flynn <fr...@co...> > wrote: > Niv, > > Interesting - I've used "USE INDEX" but I don't use it often. Let > me check with the developer - also it might help if you can send me > the exact SQL so we can try to parse it. Send it to me directly not > to the list if you'd prefer not to post it (probably safer). We > don't need the whole schema but do tell me the sharding key. > > We'll answer on the list when we have one. > > Frank > > > On Dec 3, 2008, at 6:39 AM, Niv wrote: > > Hello. > > I have a problem with Spock Proxy. > I'm trying to run a query on all shards, and the query contains an > index hint ("USE INDEX"): > > SELECT * > FROM <table> > USE INDEX (index1, index2) > WHERE <conditions> > ORDER BY <order> > LIMIT 0, 10 > > In the spockproxy log file, I see the warning "could not parse table > alias, using default db". > When I remove the "USE INDEX", the query works (but takes forever). > > Can you please offer any assistance? > > Thanks, > Niv Singer > ------------------------------------------------------------------------- > 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: Niv <ni...@tr...> - 2008-12-03 14:39:35
|
Hello. I have a problem with Spock Proxy. I'm trying to run a query on all shards, and the query contains an index hint ("USE INDEX"): SELECT * FROM <table> USE INDEX (index1, index2) WHERE <conditions> ORDER BY <order> LIMIT 0, 10 In the spockproxy log file, I see the warning "could not parse table alias, using default db". When I remove the "USE INDEX", the query works (but takes forever). Can you please offer any assistance? Thanks, Niv Singer |