January 2013 - developers - lists.mariadb.org

[Maria-developers] MDEV-520: consider parallel replication patch from taobao patches
by Kristian Nielsen 14 Jan '13

14 Jan '13

Hi xiaobin, I read through your patch found here: http://mysql.taobao.org/index.php/RLPR_for_MariaDB I am still a bit confused about the status of this patch. It would be useful if you could explain a bit about the status of the patch: - How have you tested the patch. - How are you currently using the patch? Or is it still just development/test? - What are your current plans for the patch? (You mentioned that you have some changes you are implementing at the moment). Anyway, I think I have a fair understanding of the overall idea, which seems very interesting! I will try to summarise here in a bit more detail, as well as give a few general comments on the patch. Please correct me if I misunderstood anything. ---- The basic idea is to build a set (implemented as a hash table) of every unique key in every row-event in each transaction received from the master. If two transactions have no overlapping keys, they can be applied in parallel (foreign_key_checks is set to OFF to make this work). There is a single thread that processes each event received in sequence. It checks the key set of the new transaction against the key sets of all transactions queued for each of N worker threads: - If there is no conflict with any already queued transaction, the new transaction can be queued to any worker thread (the one with fewest queued transaction is chosen). - If there is a conflict with just one thread, then the transaction can be queued for that thread - it will then execute in correct sequence with the conflicting transactions. - If there is a conflict with multiple threads, wait until the conflicting transactions have been processed. No new transactions are queued while waiting. Then there are N (default 16) threads that apply the queued transactions. Queued transactions are processsed in parallel within one thread, but transaction in different queues are processed in parallel with each other. If a transaction is received which is not using row events (statement-based replication or DDL), then we wait until all queued transactions are processed. Only after that is the new transaction queued, so that such events are processed correctly (but not in parallel, of course). Likewise, if the worker threads get too far behind, we wait with queuing any more transactions. ---- I think this is a very interesting approach, and I am happy that someone have actually tried implementing it. The biggest potential issue seems to be the performance of the thread that checks the transactions for conflicts and queues them for the appropriate thread: - Main issue will be huge transactions, if the size of hash tables of key values become too big for available memory. Maybe this could be solved by having some limit above which transactions are not tried in parallel. - There will be a lot of hash lookups of key values, all done in a single thread. This could become a limit I suppose - testing will be needed to understand this fully. But I think there will be many workloads where this will be useful. Overall, it seems to me this technique is very promising. It seems to be better at getting maximum parallelism on the slave than any of the other approaches that have been considered. As to the patch itself, it seems a bit immature in the present form, but I understood you are still working on it. Let us discuss what the plans could be for finishing it and get it ready for inclusion in MariaDB. The main criteria is that it must be reasonably complete - it must not crash or silently corrupt data in any usage. However, reasonable restrictions for what is supported is fine. (For example, we could say that LOAD DATA is not supported and give an error, but not crash or silently ignore such statement). But we can discuss this in more details. ---- Finally, a couple general comments on the code: 1. get_pk_value() does not seem to handle collations, eg. case insensitivity? It needs to understand that eg. "abab" and "ABAB" can conflict. I think this should be solved by copying in values with non-binary collations using the appropriate strxfrm() variant. 2. remote_query() retries the query in case of deadlock - but that seems wrrong, doesn't it need to retry the whole _transaction_, not just the query? 3. What about other events like User_var_event ? Will they be included in a later patch? 4. There is a my_sleep(10000) in the code when a transaction conflicts with two or more workers. It would be better to replace this with a condition variable so we can wake up as soon as the conflict is resolved. Same for some other sleeps like in wait_for_all_dml_done(). 5. Shouldn't there be some locking in dispatch_transaction()? To protect the hash lookups when checking conflicts against worker threads doing pk_hash_minus() ? 6. get_pk_value(). There seems to be a buffer overflow - st_hash_item::key is fixed size, but there seems no check against overrunning it. One solution is to just truncate if data does not fit - this can lead to false positives where potential parallelism is not exploited, but that is still safe and should be ok. Thanks! - Kristian.

5 21

Re: [Maria-developers] MDEV-318 IF (NOT) EXIST clauses for ALTER TABLE
by Sergei Golubchik 13 Jan '13

13 Jan '13

Hi, Holyfoot! On Jul 08, holyfoot(a)askmonty.org wrote: > At file:///home/hf/wmar/mdev-318/ > > ------------------------------------------------------------ > revno: 3552 > revision-id: holyfoot(a)askmonty.org-20120708152354-d7iu7yoqo3cohidz > parent: sanja(a)montyprogram.com-20120626184334-ptpg39ptq3t79dg5 > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev-318 > timestamp: Sun 2012-07-08 20:23:54 +0500 > message: > MDEV-318 IF (NOT) EXIST clauses for ALTER TABLE (MWL #252). > ALTER TABLE syntax modified and related implementations added to allow: > ALTER TABLE ADD/DROP COLUMN > ALTER TABLE ADD/DROP INDEX > ALTER TABLE ADD/DROP FOREIGN KEY > ALTER TABLE ADD/DROP PARTITION > ALTER TABLE CHANGE COLUMN > ALTER TABLE MODIFY COLUMN Please, find my comments below: > === modified file 'mysql-test/r/alter_table.result' > --- mysql-test/r/alter_table.result 2011-11-21 17:13:14 +0000 > +++ mysql-test/r/alter_table.result 2012-07-11 14:24:32 +0000 > @@ -1282,3 +1282,30 @@ > # Clean-up. > drop table t1; > End of 5.1 tests > +CREATE TABLE t1 ( > +id INT(11) NOT NULL, > +x_param INT(11) DEFAULT NULL, > +PRIMARY KEY (id), > +KEY x_param (x_param) > +); > +ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT, > +ADD COLUMN IF NOT EXISTS lol INT AFTER id; > +Warnings: > +Note 1060 Duplicate column name 'id' > +ALTER TABLE t1 ADD COLUMN IF NOT EXISTS lol INT AFTER id; > +Warnings: > +Note 1060 Duplicate column name 'lol' > +ALTER TABLE t1 DROP COLUMN IF EXISTS lol; > +ALTER TABLE t1 DROP COLUMN IF EXISTS lol; > +Warnings: > +Note 1091 Can't DROP 'lol'; check that column/key exists > +ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); > +Warnings: > +Note 1061 Duplicate key name 'x_param' > +ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); typo? you test ADD KEY IF NOT EXISTS twice. > +Warnings: > +Note 1061 Duplicate key name 'x_param' > +ALTER TABLE t1 MODIFY IF EXISTS lol INT; > +Warnings: > +Note 1054 Unknown column 'lol' in 't1' > +DROP TABLE t1; > === modified file 'sql/field.cc' > --- sql/field.cc 2012-03-13 14:38:43 +0000 > +++ sql/field.cc 2012-07-11 14:28:40 +0000 > @@ -9182,6 +9182,7 @@ void Create_field::init_for_tmp_table(en First: please, see the last section in http://kb.askmonty.org/en/how-to-get-more-out-of-bzr-when-working-on-mariad… and do as it suggests I had to apply your patch locally and re-run bzr diff to get the function names in the diff :( > (maybe_null ? FIELDFLAG_MAYBE_NULL : 0) | > (is_unsigned ? 0 : FIELDFLAG_DECIMAL)); > vcol_info= 0; > + create_if_not_exists= FALSE; > stored_in_db= TRUE; > } > > === modified file 'sql/sql_lex.h' > --- sql/sql_lex.h 2011-12-11 16:39:33 +0000 > +++ sql/sql_lex.h 2012-07-11 14:28:40 +0000 > @@ -1816,6 +1816,7 @@ typedef struct st_lex : public Query_tab > uint16 create_view_algorithm; > uint8 create_view_check; > bool drop_if_exists, drop_temporary, local_file, one_shot_set; > + bool check_exists; /* Enabled if the IF [NOT] EXISTS specified for ALTER */ I think, you can remove drop_if_exists and use check_exists instead > bool autocommit; > bool verbose, no_write_to_binlog; > > === modified file 'sql/sql_yacc.yy' > --- sql/sql_yacc.yy 2012-06-10 09:53:06 +0000 > +++ sql/sql_yacc.yy 2012-07-11 14:28:40 +0000 > @@ -4656,8 +4659,16 @@ table_option: > ; > > opt_if_not_exists: > - /* empty */ { $$= 0; } > - | IF not EXISTS { $$=HA_LEX_CREATE_IF_NOT_EXISTS; } > + /* empty */ > + { > + Lex->check_exists= FALSE; Let's initialize check_exists either before the grammar is parsed (like in the add_create_index_prepare and in the create rule) of in the grammar (here, in the opt_if_not_exists). But don't do it twice. > + $$= 0; > + } > + | IF not EXISTS > + { > + Lex->check_exists= TRUE; > + $$=HA_LEX_CREATE_IF_NOT_EXISTS; > + } > ; > > opt_create_table_options: > @@ -5879,6 +5891,18 @@ opt_ident: > | field_ident { $$=$1.str; } > ; > > +opt_if_not_exists_ident: > + opt_if_not_exists opt_ident > + { > + LEX *lex= Lex; > + if (lex->check_exists && lex->sql_command != SQLCOM_ALTER_TABLE) > + { > + my_parse_error(ER(ER_SYNTAX_ERROR)); > + MYSQL_YYABORT; > + } > + $$= $2; > + }; > + why did you create opt_if_not_exists_ident rule, instead of adding opt_if_not_exists before ident in the alter table grammar? > opt_component: > /* empty */ { $$= null_lex_str; } > | '.' ident { $$= $2; } > @@ -6455,6 +6482,11 @@ opt_column: > | COLUMN_SYM {} > ; > > +opt_column_if_exists: > + opt_column if_exists > + {} > + ; > + same as above - why a special rule? > opt_ignore: > /* empty */ { Lex->ignore= 0;} > | IGNORE_SYM { Lex->ignore= 1;} > @@ -10096,8 +10128,16 @@ table_alias_ref: > ; > > if_exists: please, rename this old rule to opt_if_exists > - /* empty */ { $$= 0; } > - | IF EXISTS { $$= 1; } > + /* empty */ > + { > + Lex->check_exists= FALSE; > + $$= 0; > + } > + | IF EXISTS > + { > + Lex->check_exists= TRUE; > + $$= 1; > + } > ; > > opt_temporary: you didn't change CREATE INDEX and DROP INDEX, did you? and ALTER TABLE DROP PRIMARY KEY isn't covered either > === modified file 'sql/sql_table.cc' > --- sql/sql_table.cc 2012-04-05 21:07:18 +0000 > +++ sql/sql_table.cc 2012-07-11 14:28:40 +0000 > @@ -5788,6 +5788,226 @@ is_index_maintenance_unique (TABLE *tabl > > > /* > + Preparation for table creation > + > + SYNOPSIS > + handle_if_exists_option() Why did you do that in a separate function? You have basically duplicated all checks. normal alter table code checks for duplicate columns/key names and for non-existant columns/keys for DROP. this code is still in place after your patch. so, basically, you'll check for duplicates and non-existant names twice. what's the point of that? I'd expect you to take check_exists flag into account in the existing checking code and convert errors into warnings as appropriate. > + thd Thread object. > + table The altered table. > + alter_info List of columns and indexes to create > + > + DESCRIPTION > + Looks for the IF [NOT] EXISTS options, checks the states and remove items > + from the list if existing found. > + > + RETURN VALUES > + NONE > +*/ > + > +static void > +handle_if_exists_options(THD *thd, TABLE *table, Alter_info *alter_info) > +{ > + Field **f_ptr; Regards, Sergei

2 9

[Maria-developers] Order by speed optimization
by Matthew Lagoe 13 Jan '13

13 Jan '13

I was talking with a few people about a query such as select * from table order by X, Y, Z limit G; > > As the "ORDER BY" for me could change to any number of 12 values in any order I am not able to use a multicolumn index as such I was looking at how to optimize in such case where sorting is dynamic. On my dataset the query takes ~7 seconds for a full table scan but only .3 seconds when using the column index and returns ~80,000 records for the query (before the LIMIT) I was wondering if there is any reason that the order by syntax doesn't use the following logic, I was thinking of looking at the code but was told such a simple optimization must have a reason that its not already in the code, so I am asking here before I spend hours being stupid. This logic block will be based on the above query. 1. If index exists for column X and there is a limit on the query then use the index and sort the list, then return the first G values, continue past G until you hit a different value for X then the value that was at G 2. Use this smaller subset of data for then ordering the data based on Y 3. Order the data again based on Z 4. Return the sorted data like normal If there is a reason this optimization isn't in the code please someone let me know, if not I think I will take a look at adding it. -Botanic

2 2

[Maria-developers] Kubuntu 12.04.1 and MariaDB
by Giacomo Picchiarelli 11 Jan '13

11 Jan '13

Hi! I'm having troubles installing mariadb 10.0 in Kubuntu 12.04.1. When I try to install it, dependencies are getting enraged and apt (through synaptic or muon) suggests to remove kde-plasma-desktop. This happens when I try to install mariadb-server-core 10.0. Of course if I remove kde-plasma-desktop I can't use my kde desktop, because it depends on mysql packages. I was trying to install it for educational purpose, and try to build and make some tests. Do you have any suggestions? Thank you -- Giacomo Picchiarelli <gpicchiarelli(a)gmail.com>

3 3

[Maria-developers] External replication proceeding
by md＠rpzdesign.com 10 Jan '13

10 Jan '13

Monty: I received your email and am re-posting to the maria developers list. It is truly amazing that maria developers are HAPPY with replication as it stands right now. binary logs, Single SLAVE reading from individual masters, bla-bla-bla..... All the current replication code is like walking on the edge of a cliff, anything goes wrong and the replication process STOPS with an error. And a HUMAN BEING must wake up from their blissful sleep to get the process started again and figure out what happened. And I just LOVE having to: LOCK TABLES; RESET MASTER SET SLAVE OFF; MYSQLDUMP; COPY FILES TO SLAVE; SET MASTER TO 'sql_log_bin=45678945"; SET SLAVE ON; SECONDS BEHIND MASTER... WRITE A THESIS FOR MY PHD IN ROCKET SCIENCE Oh my GOD!!!!!! What an absolute nightmare of a design, it works, just not well. I have not made an in depth study of galera, but I refuse to make replication another research project, replication should be EASY, auto-healing, self analyzing, N-MASTER and with simple source code. I will open source the files that I use to change the MYSQLD daemon. The best way to debug MariaDB on Linux is to run the program in Netbeans 7.1.2. You can even give it command line parameters to work against a currently installed system with the data/conf files in place. Your trace idea is very nice, but there is nothing like STEPPING THROUGH the code in NetBeans/GDB in a nice IDE instead of cryptic command line GDB. Cheers, md On 1/2/2013 5:21 AM, Michael Widenius wrote: > Hi! > > A better place to send emails like this is to > maria-developers(a)lists.launchpad.net; That way you can get all > MariaDB developers to answer you questions. > >>>>>> "md" == md <md(a)rpzdesign.com> writes: > md> Hello: > md> Does anybody know the name(s) of the source files I could find the code > md> that processes the Table/Procedure Drop/Create/Update, Alter Table, etc > md> statements. > > md> I want to intercept these SQL statement calls in mysqld before > md> they get sent to the specific storage engine like Aria or Xtradb. > > sql_parse.cc proably contains the code you are looking for. > Another place to look is in the the current replication code, > ie the sql_repl.cc > > md> I am working on an asynchronous external replication daemon > md> that handles all of the data and structure synchronization > md> replication issues (N-master-master, Hot Insert Master additions, DDL > md> changes, etc) > > Sounds very intersting. > Is this a open source project ? > > md> This is one of the missing pieces of the puzzle for MariaDB. > md> (oh yeah, mysql does not exist to me anymore, only Aria/Xtradb) > > :) > > md> I need to have the mysqld daemon issue a signal that a structural change > md> has taken place and the external replication daemon will pick it up > md> and handle all the changes processing across ALL the masters (WITHOUT A > md> HUMAN BEING INVOLVED!!!!) > > md> I figured I would ask since most of you are fluent in the source > md> code and just know where everything is, you would know right away. > > That's ok, but best way to get quick responce is to ask on the > maria-developers list or at #maria channel on Freenode. > > md> I am using MariaDB 5.5.28a from source. > > On thing you can do is: > > ./BUILD/compile-pentium-debug-max > mysqld --debug > > And then start 'mysql' and execute a CREATE TABLE command. > > You can find then find a full trace of all executed functions in: > /tmp/mysqld.trace > > That's probably one of the best ways to learn how MariaDB works. > > See: > > https://kb.askmonty.org/en/creating-a-trace-file/ > > Regards, > Monty >

3 2

[Maria-developers] High Priest of Humility
by md＠rpzdesign.com 08 Jan '13

08 Jan '13

Arjen: I would not go so far as to call it "raving" or boasting about anything. It was a private email message to you only. Not broadcast publicly. For a MariaDB captain, that is pretty bold offering a negative opinion to a potential contributor. Negative comments like that are not the best support for any level of enthusiasm for openness, collaboration, and sharing. Hint: Stop giving hints. marco On 1/7/2013 5:05 PM, Arjen Lentz wrote: > Hi Marco > >> Nice to see your response. >> >> I will be providing the code that I use to modify the MariaDB core >> daemon soon. > I presume you'll put it up in a branch on Launchpad? > > >> You will be amazed how little I am changing the core code. > Hint: modesty/humility are a good traits for an OSS developer. Let other people be amazed and impressed when you deliver code - raving about yourself beforehand is less cool... > > >> Where is the open source for Galera? (could not find it readily) > At Codership, but also in the 5.5 Galera repository of MariaDB. > > > Regards, > Arjen. > >

1 0

[Maria-developers] Missing man/mysql_plugin.1 in tar ball
by Honza Horak 07 Jan '13

07 Jan '13

Hi all, I've noticed that man page for mysql_plugin utility (man/mysql_plugin.1) is not present in mariadb-5.5.28a.tar.gz while it is present in mysql-5.5.28.tar.gz. However, I failed to find the reason why in case there is one. Or is that a mistake? Does anybody know? Regards, Honza

2 1