- developers - lists.mariadb.org

[Maria-developers] Json Explain
by Peter Laursen 30 Sep '12

30 Sep '12

Is Explain output in Json format planned in MariaDB (refer http://glebshchepa.blogspot.in/2012/04/optimizer-new-explain-formatjson.html) ? If so when? and will the implementation details and output format be fully identical to Oracle/MySQL 5.6? (I did not find it mentioned here: http://monty-says.blogspot.dk/2012/09/now-is-good-time-to-be-part-of-future… http://blog.mariadb.org/mariadb-directions/ .. sorry if I overlooked it somewhere) -- Peter -- Webyog

4 8

Re: [Maria-developers] [Commits] Rev 3578: MDEV-495 backport --ignore-db-dir
by Sergei Golubchik 25 Sep '12

25 Sep '12

Hi, Holyfoot! On Sep 25, holyfoot(a)askmonty.org wrote: > revno: 3578 > revision-id: holyfoot(a)askmonty.org-20120925094024-yv0wpbl6k7b6bgg8 > parent: sanja(a)montyprogram.com-20120920094859-lcrxe8lr1mtg8h10 > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev495 > timestamp: Tue 2012-09-25 14:40:24 +0500 > message: > MDEV-495 backport --ignore-db-dir. > The feature was backported from MySQL 5.6. > Some code was added to make commands as > SELECT * FROM ignored_db.t1; > CALL ignored_db.proc(); > USE ignored_db; > to take that option into account. Just a few comments: 1. add a test case for CREATE DATABASE, CREATE DATABASE ignored_db should fail. 2. I'd prefer to get an error "db not found" instead of "incorrect db name", is it difficult to do? 3. add a test for the following: lost+found is ignored, but CREATE DATABASE `lost+found` should succeed! (indeed, it creates a directory lost@002bfound, which is not ignored), and later SELECT, CALL, USE, SHOW DATABASES, should show that new db. > === added file 'mysql-test/r/ignore_db_dirs_basic.result' > --- a/mysql-test/r/ignore_db_dirs_basic.result 1970-01-01 00:00:00 +0000 > +++ b/mysql-test/r/ignore_db_dirs_basic.result 2012-09-25 09:40:24 +0000 > @@ -0,0 +1,28 @@ > +select @@ignore_db_dirs; > +@@ignore_db_dirs > +e,lost+found,.mysqlgui,ignored_db > +# Check that SHOW DATABASES ignores all directories from > +# @@ignore_db_dirs and all directories with names starting > +# with '.' > +SHOW DATABASES; > +Database > +information_schema > +#mysql50#.otherdir > +mtr > +mysql > +test > +USE ignored_db; > +ERROR 42000: Incorrect database name 'ignored_db' > +SELECT * FROM ignored_db.t1; > +ERROR 42000: Incorrect database name 'ignored_db' > +CALL ignored_db.p1(); > +ERROR 42000: Incorrect database name 'ignored_db' > +SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='ignored_db'; > +COUNT(*) > +1 > +SET @@global.ignore_db_dirs = 'aha'; > +ERROR HY000: Variable 'ignore_db_dirs' is a read only variable > +SET @@local.ignore_db_dirs = 'aha'; > +ERROR HY000: Variable 'ignore_db_dirs' is a read only variable > +SET @@ignore_db_dirs = 'aha'; > +ERROR HY000: Variable 'ignore_db_dirs' is a read only variable When merging to 5.5 you'll need to move this test to sys_vars suite (otherwise sys_vars.all_vars will fail) > === modified file 'sql/sql_show.cc' > --- a/sql/sql_show.cc 2012-08-24 13:39:34 +0000 > +++ b/sql/sql_show.cc 2012-09-25 09:40:24 +0000 > @@ -379,6 +379,239 @@ bool mysqld_show_privileges(THD *thd) > } > > > +/** Hash of LEX_STRINGs used to search for ignored db directories. */ > +static HASH ignore_db_dirs_hash; > + > +/** > + An array of LEX_STRING pointers to collect the options at > + option parsing time. > +*/ > +static DYNAMIC_ARRAY ignore_db_dirs_array; I don't think that sql_show.cc is where all this code really belongs, but ok, as you like. Regards, Sergei

1 0

[Maria-developers] FYI: More ha_cassandra benchmark data
by Sergei Petrunia 24 Sep '12

24 Sep '12

Hi! For the record, posting some ha_cassandra benchmarks: <contents> Run #1: re-run previous benchmarks Run #2: evaluate max. network performance Run #3: Load two dbt3 databases at once Run #4: load two datasets into different nodes Results Conclusions </contents> === Run #1: re-run previous benchmarks === My results with - m1.large mysqld node (mysqld uses optimized binary) - Cassandra on 2*m1.large nodes - Loading data with the standard LOAD DATA INFILE command. - %CPU mysqld =14 sar -n DEV 10: 03:04:41 PM IFACE rxpck/s txpck/s rxkB/s txkB/s rxcmp/s txcmp/s rxmcst/s 03:04:51 PM lo 0.00 0.00 0.00 0.00 0.00 0.00 0.00 03:04:51 PM eth0 1575.20 156.60 81.47 4458.13 0.00 0.00 0.00 On cassandra node: - jsvc %CPU 70-170... 02:51:00 PM IFACE rxpck/s txpck/s rxkB/s txkB/s rxcmp/s txcmp/s rxmcst/s 02:51:05 PM lo 0.00 0.00 0.00 0.00 0.00 0.00 0.00 02:51:05 PM eth0 8325.20 7333.80 3376.25 1696.74 0.00 0.00 0.00 Total load time: time: 19 min. === Run #2: evaluate max. network performance === for comparison, sending /dev/zero on one host to /dev/null on another with nc: 03:39:24 PM IFACE rxpck/s txpck/s rxkB/s txkB/s rxcmp/s txcmp/s rxmcst/s 03:39:34 PM lo 0.00 0.00 0.00 0.00 0.00 0.00 0.00 03:39:34 PM eth0 16756.70 3688.50 851.91 87477.61 0.00 0.00 0.00 sending in both directions: SQL: 03:45:36 PM IFACE rxpck/s txpck/s rxkB/s txkB/s rxcmp/s txcmp/s rxmcst/s 03:45:46 PM lo 0.00 0.00 0.00 0.00 0.00 0.00 0.00 03:45:46 PM eth0 63308.20 4473.50 73002.80 81484.77 0.00 0.00 0.00 Cassandra: 03:47:32 PM IFACE rxpck/s txpck/s rxkB/s txkB/s rxcmp/s txcmp/s rxmcst/s 03:47:42 PM lo 0.20 0.20 0.01 0.01 0.00 0.00 0.00 03:47:42 PM eth0 60741.50 15241.70 84110.07 70712.32 0.00 0.00 0.00 === Run #3: Load two dbt3 databases at once === - Loading two DBT-3 databases at once. - The databases have their own key spaces, and mysql tables - Still, both loaders connect to the same cassandra host. Cassandra host: 04:27:57 PM IFACE rxpck/s txpck/s rxkB/s txkB/s rxcmp/s txcmp/s rxmcst/s 04:28:07 PM eth0 9979.10 8984.00 6006.77 2621.08 0.00 0.00 0.00 04:29:07 PM eth0 9181.80 7315.00 6416.25 2686.83 0.00 0.00 0.00 04:29:17 PM eth0 10277.00 9629.60 6047.00 2672.02 0.00 0.00 0.00 04:30:23 PM CPU %user %nice %system %iowait %steal %idle 04:30:33 PM all 34.56 24.80 24.71 0.18 0.05 15.69 04:30:53 PM all 32.08 11.20 24.25 1.31 0.08 31.07 04:31:03 PM all 35.59 22.55 22.28 0.27 0.00 19.31 it has periodical slow-downs like this: 04:32:38 PM all 53.25 1.30 19.05 3.03 0.00 23.38 04:32:39 PM all 65.84 10.40 21.78 0.00 0.00 1.98 04:32:40 PM all 37.14 33.81 18.10 0.95 0.00 10.00 04:32:41 PM all 42.65 25.98 26.96 0.00 0.00 4.41 04:32:42 PM all 33.17 39.42 19.23 0.00 0.00 8.17 04:32:43 PM all 36.89 36.89 19.90 0.00 0.00 6.31 04:32:44 PM all 39.81 35.55 13.74 0.00 0.00 10.90 04:32:45 PM all 34.45 33.97 22.01 0.00 0.00 9.57 04:32:46 PM all 35.58 36.06 21.15 0.00 0.00 7.21 04:32:47 PM all 31.05 35.16 15.07 0.00 0.00 18.72 04:32:48 PM all 41.31 26.29 20.66 0.00 0.00 11.74 04:32:49 PM all 39.02 32.20 25.37 0.00 0.00 3.41 04:32:50 PM all 26.64 34.50 13.97 0.00 0.00 24.89 04:32:51 PM all 56.93 27.72 12.87 0.00 0.00 2.48 04:32:52 PM all 71.14 15.42 11.44 0.00 0.00 1.99 04:32:53 PM all 64.22 8.82 23.04 0.00 0.00 3.92 04:32:54 PM all 76.73 1.98 19.31 0.00 0.00 1.98 04:32:55 PM all 61.24 0.00 30.14 0.96 0.00 7.66 04:32:56 PM all 29.20 0.00 32.40 0.00 0.00 38.40 04:32:57 PM all 30.29 0.00 29.88 0.00 0.00 39.83 04:32:58 PM all 23.19 0.00 20.29 0.00 0.36 56.16 04:32:59 PM all 23.34 0.00 16.38 0.00 0.35 59.93 04:33:00 PM all 20.07 0.00 21.90 0.00 0.00 58.03 04:33:01 PM all 24.20 0.00 18.86 0.00 0.00 56.94 04:33:02 PM all 20.07 0.00 22.18 0.00 0.70 57.04 Is this a compaction? mysqld host: 04:29:01 PM IFACE rxpck/s txpck/s rxkB/s txkB/s rxcmp/s txcmp/s rxmcst/s 04:29:01 PM eth0 1416.20 247.90 75.43 6578.11 0.00 0.00 0.00 04:29:11 PM eth0 1175.00 199.60 62.49 5292.67 0.00 0.00 0.00 04:29:21 PM eth0 1074.60 186.90 57.21 4952.19 0.00 0.00 0.00 04:30:30 PM CPU %user %nice %system %iowait %steal %idle 04:30:40 PM all 7.77 0.00 0.56 0.00 0.05 91.62 04:30:50 PM all 8.20 0.00 0.60 0.00 0.30 90.89 04:31:00 PM all 8.01 0.00 0.66 0.00 0.05 91.28 Conclusions: - mysqld is still idle most of the time. - Cassandra is busier. Sometimes, it is 1014/Sep/12 12:50 PM0% busy. - network use betwen mysqld and cassandra is 2x more, which is expected Run time: 24 minutes for both (compare to 19 minutes) === Run #4: load two datasets into different nodes === ==== Network ==== SQL node: 05:44:21 PM IFACE rxpck/s txpck/s rxkB/s txkB/s rxcmp/s txcmp/s rxmcst/s 05:44:41 PM eth0 1178.10 210.10 62.81 5510.03 0.00 0.00 0.00 05:44:51 PM eth0 1062.10 200.90 57.53 5152.63 0.00 0.00 0.00 05:45:01 PM eth0 965.80 194.00 52.53 5012.62 0.00 0.00 0.00 Cassandra1: 05:44:19 PM IFACE rxpck/s txpck/s rxkB/s txkB/s rxcmp/s txcmp/s rxmcst/s 05:44:49 PM eth0 10159.00 8659.20 4443.26 1798.75 0.00 0.00 0.00 05:44:59 PM eth0 9018.20 7750.10 3982.21 1578.30 0.00 0.00 0.00 05:45:09 PM eth0 8741.30 7521.70 3921.69 1541.55 0.00 0.00 0.00 Cassandra2: 05:44:56 PM IFACE rxpck/s txpck/s rxkB/s txkB/s rxcmp/s txcmp/s rxmcst/s 05:45:06 PM eth0 9305.60 7906.00 4121.10 1622.92 0.00 0.00 0.00 05:45:16 PM eth0 9186.50 7828.80 4258.12 1681.78 0.00 0.00 0.00 05:45:26 PM eth0 9675.70 8283.10 4306.89 1698.37 0.00 0.00 0.00 ==== CPU ==== SQL node: 05:46:45 PM CPU %user %nice %system %iowait %steal %idle 05:46:55 PM all 8.39 0.00 0.70 0.00 0.40 90.51 05:47:05 PM all 7.65 0.00 0.74 0.00 0.70 90.91 05:47:15 PM all 8.76 0.00 0.64 0.00 0.89 89.70 cassandra 1: 05:47:01 PM CPU %user %nice %system %iowait %steal %idle 05:47:11 PM all 27.28 28.24 27.55 0.00 0.05 16.89 05:47:21 PM all 31.45 24.44 27.56 1.38 0.05 15.13 05:47:31 PM all 29.46 19.60 23.19 2.35 0.04 25.36 05:47:41 PM all 38.47 0.04 14.21 0.15 0.07 47.06 05:47:51 PM all 31.04 9.92 17.97 0.39 0.08 40.61 05:48:01 PM all 24.73 28.07 21.77 0.26 0.04 25.12 05:48:11 PM all 19.79 11.67 19.23 1.83 0.07 47.41 05:48:21 PM all 20.29 0.00 11.07 0.06 0.13 68.44 05:48:31 PM all 24.08 0.00 11.96 0.13 0.13 63.69 Cassandra 2: 05:46:56 PM CPU %user %nice %system %iowait %steal %idle 05:47:06 PM all 31.97 27.14 26.91 0.09 0.05 13.85 05:47:16 PM all 32.79 20.43 23.39 2.34 0.04 21.01 05:47:26 PM all 26.43 27.07 27.29 0.14 0.09 18.98 05:47:36 PM all 17.72 8.59 12.00 2.97 0.21 58.52 05:47:46 PM all 13.63 0.00 6.77 0.06 0.30 79.23 05:47:56 PM all 27.19 3.92 8.52 0.11 0.25 60.01 05:48:06 PM all 19.94 27.83 18.47 0.13 0.08 33.54 05:48:16 PM all 13.69 16.01 14.24 2.69 0.15 53.22 05:48:26 PM all 18.13 0.00 9.57 0.25 0.38 71.66 === Results === The first client failed with ERROR 1928 (HY000) at line 145: Internal error: 'TimedOutException: Default TException.' real 4m3.463s user 0m0.008s sys 0m0.000s The second completed after: real 19m57.875s (which matches load time for loading one dataset). === Conclusions === - need to fix the Timeout error (filed as MDEV-535) - CPU time spent by mysqld is negligible now (I don't know whether this comes from using faster CPU for SQL node, or using a release build of mariadb, or both) - A confirmation of previous findings: if we use only one connection, the speed at which we can insert the data is very limited (see: with two connections, we have inserted 2x data in 20% more time. This is where we used two connections to connect to the *same* node) - I am not sure whether I should just start using multiple connections, or also automatically route the data (both writes and reads) to the right node? - need to check out Hector client. It seems to be able to connect to multiple nodes of Cassandra cluster. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

[Maria-developers] Updates to mytop (from MariaDB 5.5 distribution)
by Michael Widenius 21 Sep '12

21 Sep '12

Hi! I have done some extensions to mytop as part of porting, fixing and enhancing it for MariaDB. I have now merged my changes with 1.91 and thought it was time to share the changes with you. Sorry for not doing it sooner! I also have added Jean Weisbuch patches for --full-queries and reading of .my.cnf Here is a diff of my changes. Hope you find some of them useful! If you have any questions, feel free to ask! Best regards, Monty Creator of MySQL and MariaDB

1 0

[Maria-developers] Cassandra SE: problems including it into release
by Sergei Petrunia 21 Sep '12

21 Sep '12

Hello, I've figured there may be an additional challenge in getting Cassandra storage engine into a *MariaDB release*. The problem is packaging. <contents> Cassandra SE packaging problems Need Thrift library Need recent gcc How did Sphinx SE manage to avoid a similar mess? Possible way out </contents> == Cassandra SE packaging problems == === Need recent gcc === C++ code generated by Thrift uses boost, and so one needs a recent gcc to compile it with MySQL. We have the same issue with oqgraph. See MDEV-501 for details. I am not aware of crashing problems that ograph+boost combination has. Still, recent-gcc requirement may limit the range of platforms we can build for. For example, I never managed to compile on Ubuntu 9.04. === Need Thrift library === Cassandra SE uses Thrift protocol to talk to server. In order to do this, it needs to link againist libthrift.so. There are no Thrift packages in either Fedora or Ubuntu (actually I haven't seen Thrift packages for any OS). Thrift homepage only offers source tarballs. The source tarball uses automake and compiles into 1. thrift compiler binary (we dont actually need it. Source code generated by thrift needs to be modified anyway.) 2. thrift libraries for a dozen of languages, including libthrift.so, which we do need. == How did Sphinx SE manage to avoid a similar mess? == I've asked myself a question how do we manage to include Sphinx SE without having to mess with Sphinx packages. The answer is: Sphinx SE doesn't need any sphinx libraries, either at compile time, or at runtime. It implements its client protocol fully, except for INSERT commands, for which it uses the available mysql client library to talk to Sphinx server using mysql client-server protocol. == An unlikely but possible way out == Cassandra 1.2 (the next version that is promised to be released "in Q4 2012" will introduce native network protocol. A week ago they've pushed first documentation draft. The native network protocol should allow to do everything that Thrift API allows, and more. There are no C++ clients yet, though. If I am faced with a choice betwen 1. implementing a relatively simple network protocol 2. Making Thrift packages for various platforms (and potentially convincing downstream maintainers to accept them) then I will definitely pick #1 (I have implemented network protocols in the past). There is a big risk, though: native protocol support is very new in Cassandra, they pushed it about a week ago. It was pushed into their "trunk" which is under development. I don't know if the code in their trunk is sufficiently stable for us to develop againist. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

3 3

Re: [Maria-developers] New to MariaDB
by Giacomo Picchiarelli 21 Sep '12

21 Sep '12

Thanks! At beginning Il'll try to do some benchmarks! On 13/09/2012 22:11, Michael Widenius wrote: > Hi! > >>>>>> "Sergei" == Sergei Golubchik <serg(a)askmonty.org> writes: > Sergei> Hi, Giacomo! > Sergei> On Aug 11, Giacomo Picchiarelli wrote: >>> Hi, >>> >>> I'm new to MariaDB and I'm reading the knowledge base. >>> I'm interested in studying Aria Engine (and contribute, if I can :) ), >>> are there tasks for newbie developers? > Sergei> I'm afraid, there are no tasks in the Aria engine right now :( > > Sergei> If you're interested to look at other tasks - here is the list of task, > Sergei> feel free to pick anything from it: > > Sergei> https://mariadb.atlassian.net/secure/IssueNavigator.jspa?requestId=11300 > > Actually, there are some open bugs on Aria: > > https://mariadb.atlassian.net/browse/MDEV-333 > > You can go to: > https://mariadb.atlassian.net/secure/QuickSearch.jspa > And search for "Aria" > > Same goes for lanchpad: > > https://bugs.launchpad.net/maria?field.searchtext=aria&search=Search&field.… > > Note that many of the bugs in launchpad for Aria are already fixed. > However it would be quite valuable if you could help us go trough > them and mark which still are open and try to fix them yourself. > > Another thing that can be done on Aria is to run some benchmarks on it > and try to find ways to improve the performance on it. > > Regards, > Michael Widenius > -- Giacomo Picchiarelli Skype: g.picchiarelli

2 1

Re: [Maria-developers] [Commits] Rev 3577: MDEV-521 fix. in file:///home/bell/maria/bzr/work-maria-5.3-MDEV-521/
by Sergei Petrunia 20 Sep '12

20 Sep '12

Hi Sanja, Ok to push. On Thu, Sep 20, 2012 at 12:49:03PM +0300, sanja(a)montyprogram.com wrote: > At file:///home/bell/maria/bzr/work-maria-5.3-MDEV-521/ > > ------------------------------------------------------------ > revno: 3577 > revision-id: sanja(a)montyprogram.com-20120920094859-lcrxe8lr1mtg8h10 > parent: timour(a)askmonty.org-20120917081346-nqd08d0v27w8zp5c > committer: sanja(a)montyprogram.com > branch nick: work-maria-5.3-MDEV-521 > timestamp: Thu 2012-09-20 12:48:59 +0300 > message: > MDEV-521 fix. > > After pullout item during single row subselect transformation it should be fixed properly. > === modified file 'mysql-test/r/subselect.result' > --- a/mysql-test/r/subselect.result 2012-08-25 06:15:57 +0000 > +++ b/mysql-test/r/subselect.result 2012-09-20 09:48:59 +0000 > @@ -6163,5 +6163,20 @@ a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) > 3 NULL > set optimizer_switch=@mdev367_optimizer_switch; > DROP TABLE t1; > +# > +# MDEV-521 single value subselect transformation problem > +# > +CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES ('u1'),('u2'); > +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); > +f1 > +u1 > +u2 > +FLUSH TABLES; > +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); > +f1 > +u1 > +u2 > +DROP TABLE t1; > # return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > > === modified file 'mysql-test/r/subselect_no_mat.result' > --- a/mysql-test/r/subselect_no_mat.result 2012-08-25 06:15:57 +0000 > +++ b/mysql-test/r/subselect_no_mat.result 2012-09-20 09:48:59 +0000 > @@ -6162,6 +6162,21 @@ a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) > 3 NULL > set optimizer_switch=@mdev367_optimizer_switch; > DROP TABLE t1; > +# > +# MDEV-521 single value subselect transformation problem > +# > +CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES ('u1'),('u2'); > +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); > +f1 > +u1 > +u2 > +FLUSH TABLES; > +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); > +f1 > +u1 > +u2 > +DROP TABLE t1; > # return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > set optimizer_switch=default; > > === modified file 'mysql-test/r/subselect_no_opts.result' > --- a/mysql-test/r/subselect_no_opts.result 2012-08-25 06:15:57 +0000 > +++ b/mysql-test/r/subselect_no_opts.result 2012-09-20 09:48:59 +0000 > @@ -6158,6 +6158,21 @@ a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) > 3 NULL > set optimizer_switch=@mdev367_optimizer_switch; > DROP TABLE t1; > +# > +# MDEV-521 single value subselect transformation problem > +# > +CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES ('u1'),('u2'); > +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); > +f1 > +u1 > +u2 > +FLUSH TABLES; > +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); > +f1 > +u1 > +u2 > +DROP TABLE t1; > # return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > set @optimizer_switch_for_subselect_test=null; > > === modified file 'mysql-test/r/subselect_no_scache.result' > --- a/mysql-test/r/subselect_no_scache.result 2012-08-25 06:15:57 +0000 > +++ b/mysql-test/r/subselect_no_scache.result 2012-09-20 09:48:59 +0000 > @@ -6169,6 +6169,21 @@ a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) > 3 NULL > set optimizer_switch=@mdev367_optimizer_switch; > DROP TABLE t1; > +# > +# MDEV-521 single value subselect transformation problem > +# > +CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES ('u1'),('u2'); > +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); > +f1 > +u1 > +u2 > +FLUSH TABLES; > +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); > +f1 > +u1 > +u2 > +DROP TABLE t1; > # return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > set optimizer_switch=default; > > === modified file 'mysql-test/r/subselect_no_semijoin.result' > --- a/mysql-test/r/subselect_no_semijoin.result 2012-08-25 06:15:57 +0000 > +++ b/mysql-test/r/subselect_no_semijoin.result 2012-09-20 09:48:59 +0000 > @@ -6158,6 +6158,21 @@ a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) > 3 NULL > set optimizer_switch=@mdev367_optimizer_switch; > DROP TABLE t1; > +# > +# MDEV-521 single value subselect transformation problem > +# > +CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES ('u1'),('u2'); > +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); > +f1 > +u1 > +u2 > +FLUSH TABLES; > +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); > +f1 > +u1 > +u2 > +DROP TABLE t1; > # return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > set @optimizer_switch_for_subselect_test=null; > > === modified file 'mysql-test/t/subselect.test' > --- a/mysql-test/t/subselect.test 2012-08-25 06:15:57 +0000 > +++ b/mysql-test/t/subselect.test 2012-09-20 09:48:59 +0000 > @@ -5229,5 +5229,18 @@ set optimizer_switch=@mdev367_optimizer_ > > DROP TABLE t1; > > +--echo # > +--echo # MDEV-521 single value subselect transformation problem > +--echo # > +CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES ('u1'),('u2'); > + > +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); > +FLUSH TABLES; > +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); > + > +# Cleanup > +DROP TABLE t1; > + > --echo # return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > > === modified file 'sql/item_subselect.cc' > --- a/sql/item_subselect.cc 2012-09-17 08:13:46 +0000 > +++ b/sql/item_subselect.cc 2012-09-20 09:48:59 +0000 > @@ -1007,11 +1007,9 @@ Item_singlerow_subselect::select_transfo > } > substitution= select_lex->item_list.head(); > /* > - as far as we moved content to upper level, field which depend of > - 'upper' select is not really dependent => we remove this dependence > + as far as we moved content to upper level we have to fix dependences & Co > */ > - substitution->walk(&Item::remove_dependence_processor, 0, > - (uchar *) select_lex->outer_select()); > + substitution->fix_after_pullout(select_lex->outer_select(), &substitution); > } > DBUG_RETURN(false); > } > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] Server-id patch
by Kristian Nielsen 18 Sep '12

18 Sep '12

Robert Hodges <robert.hodges(a)continuent.com> writes: > I think we need to take this out to one of the email threads. I hope other > people would be interested in these problems. Agree, I've quoted liberally and Cc:'ed maria-developers@ > On Aug 31, 2012, at 6:14 AM PDT, Kristian Nielsen wrote: >> Robert Hodges <robert.hodges(a)continuent.com> writes: >> >>> 1.) The server-id of which I was speaking is a very simple patch to allow us >>> to do the following client command: 'set session server-id=X' where X is >>> whatever we choose. Currently MySQL replication sets the server ID from an >>> internal structure (THD as I recall), which means that applications like >>> Tungsten cannot get the server-id into the binlog when applying updates on a >>> slave. Server-id is essential to prevent replication loops. Monty and I >>> discussed this feature briefly over dinner in April. I hacked in support >>> using a UDF about a year ago but it would be better to have this become a >>> dynamic session variable. >> Yes, it does not seem too hard to do (though as always some care is needed to >> introduce it in a clean way). >> >> I created an issue for this with some descriptions: >> >> https://mariadb.atlassian.net/browse/MDEV-500 > SET SESSION pseudo_server_id=236; > > Then the server_id 236 would to go into the binlog. This seems like a good > idea and avoids accidentally reseting the server_id, which is a pretty > significant variable. Seems there is hope we can have this in 10.0. >>> 2.) I agree with your criticisms of the MySQL 5.6 replication design. It's >>> surprisingly complex and looks as if it will have a lot of bugs. I felt at >> >> Yes. They need this complexity because their parallel replication (which is >> similar to Tungsten parallel replication I think?) can arbitrarily reorder >> transactions in a slave binlog compared to the order in the master binlog. > We just use the sequential log. The slaves are complex but it does not > pollute the log. In fact, everything up to the point of applying to the > slave is purely sequential. I think their design got this wrong. Well, the MySQL --log-slave-updates work by generating completely new events on the slave as the received events are executed by the SQL thread(s). Do you mean that Tungsten does not do this, but uses the original binlog with original events from the master? How is multi-master (one slave replicating from several masters) handled (if it is handled) ? >>> the time that the better approach on transaction IDs would be to put enough >>> information into logs to enable applications to compute a vector clock on >>> the stream of binlog events coming from multiple servers. This is a >> >> Interesting, I though about a concept of "vector clock" before, but did not >> know the terminology. >> >> Aha, so your point is that even though we will apply changes from multiple >> upstream master servers in parallel on a single slave, with a vector clock, we >> can still order them on a slave according to which transactions on one server >> where visible to which transactions on the other. And stuff like >> that. Interesting! >> >> But note that to be truly "theoretically sound", all queries (ie. SELECT) as >> well as all applications that interact directly or indirectly with multiple >> servers need to be included in the vector clock. So it is not without >> complexity. > Are you thinking about holding locks? For practical purposes it's enough to > do writes only. My point is that you have a global ID that is partially > ordered by originating server. Vector clocks are the traditional way of > handling that. This is the basis for most mechanisms of conflict > resolution--you check to see if the clocks are ordered and if they are not > (because two thing happened at the same time, hence are not comparable), it > signals a conflict that must be reconciled. Well, I was mostly just reacting to the phrase "truly theoretically sound". If readers are not considered in the vector clock, then they are able to observe different states of the database at different nodes in the cluster. But as you say, that is probably acceptable. >> This does not immediately apply to the MySQL design, as they work with a >> single master (applying in parallel transactions in different databases), and >> as they arbitrarily reorder between different databases, without any attempt >> to maintain inter-database consistency. But of course, my point is that this >> design is fundamentally wrong. >> >>> theoretically sound approach to enable both synchronous and asynchronous >>> multi-master replication. (Galera incidentally uses approximately this >>> approach for sync replication as you may be aware.) If you give us server >>> IDs we don't need anything else. Oops, I forgot. Making the binlog truly >>> crash safe in every possible case is also very helpful. >> >> I think it is getting closer to being truly crash safe - can you mention the >> situations where it is not? And btw., are you interested in the recent testing >> we (especially Elena) made for testing such crash safety, and the fixes made >> for it? We found some fairly interesting (and scary) stuff... > Actually your (MP's?) fix for group commit is the most important > improvement. Can we turn sync_binlog back on without killing performance? Yes, well at least performance is substantially improved with sync_binlog=1. With a parallel load you can see improvements of 10x or more, and even single-threaded load is improved in 10.0. >> I have been thinking about this for some time. Basically, the correct approach >> is to make the binlog into a true transaction log using standard textbook >> techniques. Pre-allocate the log, make it page based (this will also improve >> performance). Do ping-pong writes at the end so that we can recover even from >> torn pages. Stuff like that. Would be cool to do, but so far I have not really >> seen much interest for something like this. > It would be a lot of work. If group commit is fixed, then most on-premise > people can solve enough problems with RAID and BBU that they don't need much > more. Right, agree, I thought you had something more subtle than sync_binlog=0 in mind. (BTW, I fixed a couple binlog crash recovery bugs recently in MariaDB, which also affect MySQL). >>> 3.) I read the email you cited. I need some time to study it in order to >>> comment intelligently. The most immediate feedback is that I doubt you want >>> to make assumptions about how slaves will apply transactions. The role of >>> the master is to provide local transactions in serial order. Looking across >>> a set of masters you then have a set of easily identifiable streams of >>> partially ordered transactions. That in turn provides a mathematically >>> tractable model for reasoning about things like update order, dependencies, >>> and potential conflicts. Any metadata you can supply about dependencies, >>> e.g., which schema(s) transactions actually touch is useful but may or may >>> not be relevant on slaves depending on the application. It's up to slaves >>> to make decisions on reordering. >> >> But think about a three-level multi-master topology. Servers A and B are >> masters for server C, which is itself a master for D. The DBA/application >> developer will declare that transactions on A and B are independent, so C can >> apply them in any order, and in parallel. Clearly on D we will want to also >> apply them in parallel, and the only way we can do that is to use the >> different server ids A and B in the stream of events in the binlog of C. >> >> So now consider a simple two-level topology with just master C and slave >> D. Why not still let C put different "server_id"s in different events, as a >> way to tell server D that events are independent? For example, if the DBA runs >> a long-running ALTER TABLE, he can ensure that no transactions touch this >> table while it runs, and just SET SESSION SERVER_ID to something else, and it >> can run in parallel on D without delaying normal transactions. >> >> Or think about MySQL 5.6 style parallel replication, where the DBA declares >> that on server A, transactions against different databases are to be >> considered independent and applied in parallel on the slave. While on server B >> such transactions are not independent. Now we want to setup slave C to >> replicate from both A and B. Clearly, C is the wrong place to configure >> whether transactions in different databases are to be considered independent - >> this is a property of how the transactions are generated, not where they are >> replicated. But if we just let the server_id of transactions on master A be a >> hash of the affected database, then MySQL 5.6 style parallel replication just >> falls out of multi-master for tree, without any of their complexity. > I have not ever seen this topology arise in practice within MySQL, as people > tend to keep things pretty homogeneous. It seems to arise mostly in data > warehouse loading where multiple applications load data into the same store > for reporting. These are more heterogeneous but arallel information from > MySQL is of limited value here--data warehouses in Vertica or Hadoop don't > have constraints and you can just jam data into them in parallel. Ok. >> It is just a matter of not artificially restricting transactions generated on >> a given server to have just one server_id value. Which is in fact exactly what >> you asked me about originally with SET SESSION SERVER_ID :) >> >> On the other hand, the slave is free to try to discover more opportunities for >> independence / parallelism if it can, and use them. But automatically >> discovering opportunities for parallelism on the slave is a very hard problem >> in general, as I am sure you agree ... > Meanwhile, I don't like the idea of using server_id in the way you describe > because it overloads the meaning. Server_id just tells us which server > actually committed the transaction in the first place. The reason we need > the session variable was to be able to signal this properly. I was not > intending to use it more broadly. Ok, I've noted your concern. It would be interesting to learn how you use the knowledge that one mysqld instance can have at most one server id in generated transactions? > More generally, I believe that if you want to allow programmers to declare > that things are independent it would be best to add SQL-level features. For > instance, Facebook has a patch that prevents transactions from spanning > schemas. This is a very useful feature, because it ensures that all updates > in one schema are independent from others. That in turn means you can > parallelize without being afraid of deadlocks (a big problem in this space) > or constraint violations. > > You could generalize this into the notion of a shard, which is a scope for > independent transactions and presumably also referential integrity. This > would be a very useful feature for SQL databases. I don't know if you have > read it but Pat Helland has a great paper on this topic called "Life beyond > Distributed Transactions: an Apostate’s Opinion." It was visionary at the > time (and still is) and has never been properly implemented in relational > DBMS to my knowledge. Well, independent schemas are nice, but not enough. There are many applications that need the slave to apply transactions in parallel even within a single table. - Kristian.

1 0

[Maria-developers] Review/help needed with MDEV-532
by Kristian Nielsen 14 Sep '12

14 Sep '12

Hi Serg! I've pushed the MDEV-181/MDEV-225/MDEV-232 to 10.0-base, thanks for review! As you remember, we discussed extending it with a separate patch for doing the checkpoint inside InnoDB in an asynchroneous manner. I have created MDEV-532 for this. It was not hard to create the patch, and it seems to work. But I could use some help with one last test failure that I am somewhat mystified by: rpl.rpl_binlog_index 'row' w7 [ fail ] Found warnings/errors in server log file! Test ended at 2012-09-13 15:44:01 line 120913 15:43:48 [ERROR] mysqld: Table './test/t1' is marked as crashed and should be repaired 120913 15:43:48 [Warning] Checking table: './test/t1' 120913 15:43:48 [ERROR] test.t1: 1 client is using or hasn't closed the table properly I assume this is related to how I create a background thread for the binlog, something that I do wrong or fail to do, so any suggestions on where to look would be appreciated. I have pushed the code here: lp:~maria-captains/maria/10.0-knielsen - Kristian.

1 1

[Maria-developers] New to MariaDB
by Giacomo Picchiarelli 13 Sep '12

13 Sep '12

Hi, I'm new to MariaDB and I'm reading the knowledge base. I'm interested in studying Aria Engine (and contribute, if I can :) ), are there tasks for newbie developers? Thank you. Regards. -- Giacomo Picchiarelli <gpicchiarelli(a)gmail.com> Skype: g.picchiarelli

2 1