Jonathon Hill wrote:
Hey guys,
I'm excited about Drizzle and look forward to its maturity!
I have just run into an issue with MySQL and was wondering how you might be
handling this problem in Drizzle. Basically, when you do a INSERT INTO...SELECT
query it has to lock the table being selected or replication doesn't work right
(see
http://www.mysqlperformanceblog.com/2006/07/12/insert-into-select-performance-with-innodb-tables/).
This is horrible for concurrence, because other clients are locked out until the
SELECT is finished, which can be a while, depending on the query.
Not in Drizzle.
MySQL 5.1 solves this with row-based replication. Will Drizzle support
replication? If so, how are you planning to handle this scenario?
I just added a test case to the replication suite for INSERT SELECT. As
you can see below, INSERT SELECT is translated in Drizzle to multiple
InsertRecord GPB messages (kinda like row-based replication, but in a
standardized serial protocol format).
jpi...@serialcoder:~/repos/drizzle/replication/tests$ cat
suite/command_log/r/insert_select.result
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (
id INT NOT NULL
, padding VARCHAR(200) NOT NULL
);
INSERT INTO t1 VALUES (1, "I love testing.");
INSERT INTO t1 VALUES (2, "I hate testing.");
CREATE TABLE t2 (
id INT NOT NULL
, padding VARCHAR(200) NOT NULL
);
INSERT INTO t2 SELECT * FROM t1;
Output from command log reader, which reconstructs the row-based events
into SQL statements, with some header info removed:
/* */
DROP TABLE IF EXISTS t1;
/* */
DROP TABLE IF EXISTS t2;
/* */
CREATE TABLE t1 ( id INT NOT NULL , padding VARCHAR(200) NOT NULL );
/* */
INSERT INTO `test`.`t1` (`id`, `padding`) VALUES ("1", "I love testing.");
/* */
INSERT INTO `test`.`t1` (`id`, `padding`) VALUES ("2", "I hate testing.");
/* */
CREATE TABLE t2 ( id INT NOT NULL , padding VARCHAR(200) NOT NULL );
/* */
INSERT INTO `test`.`t2` (`id`, `padding`) VALUES ("1", "I love testing.");
/* */
INSERT INTO `test`.`t2` (`id`, `padding`) VALUES ("2", "I hate testing.");
Cheers!
jay
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
