- developers - lists.mariadb.org

[Maria-developers] Performance problem: need index-based access when have only non-sargable conditions
by Sergey Petrunya 13 Oct '10

13 Oct '10

Hi! I was asked about this performance problem: consider a query select * from tbl where tbl.key like '%foo%' Table records are big, much larger than the length of the key. LIKE condition is very selective, however its pattern starts with '%', so we can't construct range access (and they are really searching for matches in word suffixes/infixes, so fulltext index isn't an option). The desired execution strategy is: - Scan the index on tbl.key and collect index tuples that match the LIKE condition. - For those index tuples, retrieve full records. One can simulate it by rewriting the query as a self join: select * from tbl A, tbl B where B.primary_key = A.primary_key AND B.key LIKE '%foo%'; However, in this particular case they have a problem doing such rewrites because the queries are generated by some ORM tool. Proposed solution from our side ------------------------------- It is not difficult to add an execution strategy like this get_next_record() { read index tuple; if (condition satisified) { read full record } } (with optional rowid-ordered retrieval for full records). The difficult part is to find a way to decided whether this strategy should be used. We have no meaningful selectivity estimates for 'column LIKE '%foo%'. The only way we could achieve this is by having the user manually specify condition selectivities. I can see one way to achieve this without getting into painful syntax modifications: select * from tbl where selectivity(tbl.key like '%foo%', 0.05) here 'selectivity(X,y)' will evaluate to value of X, but will also inform the optimizer that P(X is true)=y. Any thoughts about this? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

[Maria-developers] WL#157 New (by Frank): Option to show all (currently) marked as crashed tables
by worklog-noreply＠askmonty.org 11 Oct '10

11 Oct '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Option to show all (currently) marked as crashed tables CREATION DATE..: Mon, 11 Oct 2010, 09:10 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 157 (http://askmonty.org/worklog/?tid=157) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: We'd like to have a way to retrieve the currently "marked as crashed" tables. At the moment we're parsing the error log, but there should be an easier way of doing this. Parsing the log is not 100%, since the log may still contain tables already repaired. A command ("show crashed tables") could just show the currently crashed tables. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#156 New (by Dreas): Add mysqlcheck option to directly repair
by worklog-noreply＠askmonty.org 10 Oct '10

10 Oct '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add mysqlcheck option to directly repair CREATION DATE..: Sun, 10 Oct 2010, 12:58 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 156 (http://askmonty.org/worklog/?tid=156) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Currently when there are corrupt MyISAM tables, you need to run mysqlcheck with -- auto-repair to get the broken tables fixed. The repair action however occurs at the end, after ALL tables have first been checked. This is inconvenient cause you want to repair any broken tables ideally as soon as the corruption is detected so the table becomes fixed and accessible. So I'd like to get an option which will directly run the repair when corruption is detected, instead of waiting until all tables are checked. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] I am more than happy to test aria with large databases
by Mark Nielsen 09 Oct '10

09 Oct '10

I am not a coder, but I can probably help anyways for development. I have CentOS systems at work which I can happily run tests against with large real-world data. They are development systems I can trash. i can't change CentOS that easily (maybe I can use virtual machines), but I can put whatever version of MariaDB on my systems and runs tests against it. If someone wants me to run tests, let me know. When the Aria tables can be used with MERGE, I will be very very happy to test it using a terabyte of data. The Aria engine has some some advantages over MyISAM and InnoDB for some report servers I wish to setup. MERGE is one of the last things for me to recommend it. Anyways, any testing is fine with me. I only have CentOS systems though. Mark

2 2

[Maria-developers] WL#155 New (by Dreas): MariaDB loop with MyISAM tables marked as crashed
by worklog-noreply＠askmonty.org 08 Oct '10

08 Oct '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: MariaDB loop with MyISAM tables marked as crashed CREATION DATE..: Fri, 08 Oct 2010, 19:54 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 155 (http://askmonty.org/worklog/?tid=155) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: On several servers (3) MariaDB 5.1 crashed after an upgrade from from MySQL 5.0. Log shows: === Version: '5.1.50-MariaDB-mariadb84-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (MariaDB - http://mariadb.com/) 101008 14:35:17 [ERROR] mysqld: Table './database123/table123' is marked as crashed and should be repaired 101008 14:35:17 [Warning] Checking table: './database123/table123' 101008 14:35:17 mysqld_safe Number of processes running now: 0 101008 14:35:17 mysqld_safe mysqld restarted === This is looping, so things only get worse every time with more crashed tables. Using myisamchk to repair all MYI tables actually "fixes" the issue, but I guess it can come back after an unclean shutdown. This appears to be a major bug. I have a copy of such corrupt database available on request (information is confidential). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#154 New (by Dreas): Replication breaks when upgrading to MariaDB
by worklog-noreply＠askmonty.org 08 Oct '10

08 Oct '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Replication breaks when upgrading to MariaDB CREATION DATE..: Fri, 08 Oct 2010, 06:08 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 154 (http://askmonty.org/worklog/?tid=154) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: When upgrading a MySQL 5.0 master to MariaDB 5.1, replication breaks on the slave(s): Last_Error: Error 'Table 'tablename' already exists' on query. Default database: 'greylist'. Query: 'RENAME TABLE `#mysql50#tablename` TO `tablename`' The RENAME TABLE shouldn't be replicated. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

Re: [Maria-developers] [Commits] Rev 2827: Ported the fix for bug #57024 (a performance issue for outer joins). in file:///home/igor/maria/maria-5.3-mwl128-bug57024/
by Sergey Petrunya 06 Oct '10

06 Oct '10

Hello Igor, Please find some minor comments below. Ok to push after they are addressed. On Wed, Oct 06, 2010 at 05:14:08AM -0700, Igor Babaev wrote: > At file:///home/igor/maria/maria-5.3-mwl128-bug57024/ > > ------------------------------------------------------------ > revno: 2827 > revision-id: igor(a)askmonty.org-20101006121408-ra44i6o7eich5hrf > parent: igor(a)askmonty.org-20101004014546-xirepv04xs7txxhw > committer: Igor Babaev <igor(a)askmonty.org> > branch nick: maria-5.3-mwl128-bug57024 > timestamp: Wed 2010-10-06 05:14:08 -0700 > message: > Ported the fix for bug #57024 (a performance issue for outer joins). > Employed the same kind of optimization as in the fix for the cases > when join buffer is used. > The optimization performs early evaluation of the conditions from > on expression with table references to only outer tables of > an outer join. ... > === modified file 'sql/sql_join_cache.cc' > @@ -1604,6 +1625,12 @@ > uchar *init_pos= pos; > CACHE_FIELD *copy= field_descr; > CACHE_FIELD *copy_end= copy+flag_fields; > + if (with_match_flag) > + { > + copy->str[0]= test((Match_flag) pos[0] == MATCH_FOUND); > + pos+= copy->length; > + copy++; > + } > for ( ; copy < copy_end; copy++) > { > memcpy(copy->str, pos, copy->length); TODO where does the above copy to? > @@ -1754,30 +1781,68 @@ > > > /* > - Skip record from join buffer if its match flag is on: default implementation > - > - SYNOPSIS > - skip_recurrent_match() > - > - DESCRIPTION > - This default implementation of the virtual function skip_record_if_match > - skips the next record from the join buffer if its match flag is set on. > - If the record is skipped the value of 'pos' is set to points to the position > - right after the record. > - > - RETURN VALUE > - TRUE the match flag is on and the record has been skipped > - FALSE the match flag is off > -*/ > - > -bool JOIN_CACHE::skip_recurrent_match() > -{ > - DBUG_ASSERT(with_length); > - uint offset= size_of_rec_len; > - if (prev_cache) > - offset+= prev_cache->get_size_of_rec_offset(); > - /* Check whether the match flag is on */ > - if (get_match_flag_by_pos(pos+offset)) > + Skip record from join buffer if's already matched: default implementation > + > + SYNOPSIS > + skip_as_matched() > + > + DESCRIPTION > + This default implementation of the virtual function skip_as_matched > + skips the next record from the join buffer if its match flag is set to 1. > + If the record is skipped the value of 'pos' is set to points to the position > + right after the record. > + > + RETURN VALUE > + TRUE the match flag is set to 1 and the record has been skipped > + FALSE the match flag is not 1 Please s/1/MATCH_FOUND/ > +*/ > + > +bool JOIN_CACHE::skip_as_matched() Please rename the function to skip_if_matched(), because that what it actually does: checks *if* the records is matched and skips it if it is so. > +{ > + DBUG_ASSERT(with_length); > + uint offset= size_of_rec_len; > + if (prev_cache) > + offset+= prev_cache->get_size_of_rec_offset(); > + /* Check whether the match flag is MATCH_FOUND */ > + if (get_match_flag_by_pos(pos+offset) == MATCH_FOUND) > + { > + pos+= size_of_rec_len + get_rec_length(pos); > + return TRUE; > + } > + return FALSE; > +} > + > + > +/* > + Skip record from join buffer if the match isn't needed: default implementation > + > + SYNOPSIS > + skip_as_unneeded_match() > + > + DESCRIPTION > + This default implementation of the virtual function skip_as_unneeded_match > + skips the next record from the join buffer if its match flag is not > + MATCH_NOT_FOUND, and, either its value is MATCH_FOUND and join_tab is the > + first inner table of an inner join, or, its value is MATCH_IMPOSSIBLE > + and join_tab is the first inner table of an outer join. > + If the record is skipped the value of 'pos' is set to points to the position Typo, s/points/point/ > + right after the record. > + > + RETURN VALUE > + TRUE the record has to be been skipped ^^^^^^^^^^^ wrong grammar. > + FALSE otherwise > +*/ > + > +bool JOIN_CACHE::skip_as_unneeded_match() > +{ > + DBUG_ASSERT(with_length); > + enum Match_flag match_fl; > + uint offset= size_of_rec_len; > + if (prev_cache) > + offset+= prev_cache->get_size_of_rec_offset(); > + > + if ((match_fl= get_match_flag_by_pos(pos+offset)) != MATCH_NOT_FOUND && > + (join_tab->check_only_first_match() == (match_fl == MATCH_FOUND)) ) > { > pos+= size_of_rec_len + get_rec_length(pos); > return TRUE; > @@ -1990,9 +2055,9 @@ > { > int error; > enum_nested_loop_state rc= NESTED_LOOP_OK; > + join_tab->table->null_row= 0; > bool check_only_first_match= join_tab->check_only_first_match(); > - > - join_tab->table->null_row= 0; > + bool outer_join_first_inner= join_tab->is_first_inner_for_outer_join(); > > /* Return at once if there are no records in the join buffer */ > if (!records) > @@ -2042,9 +2107,11 @@ > /* > If only the first match is needed, and, it has been already found for > the next record read from the join buffer, then the record is skipped. > + Also those records that must be null complemented are not considered > + as candidates for matches. > */ > - if (!(check_only_first_match && > - skip_recurrent_candidate_for_match(rec_ptr))) > + if ((!check_only_first_match && !outer_join_first_inner) || > + !skip_next_candidate_for_match(rec_ptr)) > { > read_next_candidate_for_match(rec_ptr); > rc= generate_full_extensions(rec_ptr); > @@ -2268,7 +2335,6 @@ > uint cnt; > enum_nested_loop_state rc= NESTED_LOOP_OK; > bool is_first_inner= join_tab == join_tab->first_unmatched; > - bool is_last_inner= join_tab == join_tab->first_unmatched->last_inner; > > /* Return at once if there are no records in the join buffer */ > if (!records) > @@ -2289,7 +2355,7 @@ > goto finish; > } > /* Just skip the whole record if a match for it has been already found */ > - if (!is_first_inner || !skip_recurrent_match()) > + if (!is_first_inner || !skip_as_matched()) > { > get_record(); > /* The outer row is complemented by nulls for each inner table */ > @@ -2527,6 +2593,8 @@ > is attached to the key entry. The key value is either placed in the hash > element added for the key or, if the use_emb_key flag is set, remains in > the record from the partial join. > + If the match flag field of a record contains MATCH_IMPOSSIBLE the key is > + not created for this record. > > RETURN VALUE > TRUE if it has been decided that it should be the last record > @@ -2550,6 +2618,9 @@ > link= prev_cache->get_curr_rec_link(); > write_record_data(link, &is_full); > > + if (last_written_is_null_compl) > + return is_full; > + > if (use_emb_key) > key= get_curr_emb_key(); > else > @@ -2634,26 +2705,55 @@ > > > /* > - Skip record from a hashed join buffer if its match flag is on > - > - SYNOPSIS > - skip_recurrent_match() > - > - DESCRIPTION > - This implementation of the virtual function skip_recurrent_match does > - the same as the default implementation does, but it takes into account > - the link element used to connect the records with the same key into a chain. > - > - RETURN VALUE > - TRUE the match flag is on and the record has been skipped > + Skip record from a hashed join buffer if its match flag is set to 1 > + > + SYNOPSIS > + skip_as_matched() > + > + DESCRIPTION > + This implementation of the virtual function skip_as_matched does > + the same as the default implementation does, but it takes into account > + the link element used to connect the records with the same key into a chain. > + > + RETURN VALUE > + TRUE the match flag is MATCH_FOUND and the record has been skipped > + FALSE the match flag is not MATCH_FOUND > +*/ > + > +bool JOIN_CACHE_HASHED::skip_as_matched() > +{ > + uchar *save_pos= pos; > + pos+= get_size_of_rec_offset(); > + if (!this->JOIN_CACHE::skip_as_matched()) > + { > + pos= save_pos; > + return FALSE; > + } > + return TRUE; > +} > + > + > +/* > + Skip record from a hashed join buffer if its match flag dictates to do so > + > + SYNOPSIS > + skip_as_uneeded_match() > + > + DESCRIPTION > + This implementation of the virtual function skip_as_uneeded_match does > + the same as the default implementation does, but it takes into account > + the link element used to connect the records with the same key into a chain. > + > + RETURN VALUE > + TRUE the match flag dictates to skip the record > FALSE the match flag is off > */ > > -bool JOIN_CACHE_HASHED::skip_recurrent_match() > +bool JOIN_CACHE_HASHED::skip_as_unneeded_match() > { > uchar *save_pos= pos; > pos+= get_size_of_rec_offset(); > - if (!this->JOIN_CACHE::skip_recurrent_match()) > + if (!this->JOIN_CACHE::skip_as_unneeded_match()) > { > pos= save_pos; > return FALSE; > @@ -2780,7 +2880,7 @@ > last element of this chain. > > RETURN VALUE > - TRUE if each retrieved record has its match flag set on > + TRUE if each retrieved record has its match flag set to MATCH_FOUND > FALSE otherwise > */ > > @@ -2792,7 +2892,7 @@ > { > next_rec_ref_ptr= get_next_rec_ref(next_rec_ref_ptr); > uchar *rec_ptr= next_rec_ref_ptr+rec_fields_offset; > - if (!get_match_flag_by_pos(rec_ptr)) > + if (get_match_flag_by_pos(rec_ptr) != MATCH_FOUND) > return FALSE; > } > while (next_rec_ref_ptr != last_rec_ref_ptr); > @@ -3000,25 +3100,28 @@ > Check whether the matching record from the BNL cache is to be skipped > > SYNOPSIS > - skip_recurrent_candidate_for_match > + skip_next_candidate_for_match > rec_ptr pointer to the position in the join buffer right after the prefix > of the current record > > DESCRIPTION > This implementation of the virtual function just calls the > - method skip_recurrent_match to check whether the record referenced by > - ref_ptr has its match flag set on and, if so, just skips this record > - setting the value of the cursor 'pos' to the position right after it. > + method skip_as_unneded_match to check whether the record referenced by > + ref_ptr has its match flag set either to MATCH_FOUND and join_tab is the > + first inner table of a semi-join, or it's set to MATCH_IMPOSSIBLE and > + join_tab is the first inner table of an outer join. > + If so, the function just skips this record setting the value of the > + cursor 'pos' to the position right after it. > > RETURN VALUE > TRUE the record referenced by rec_ptr has been skipped > FALSE otherwise > */ > > -bool JOIN_CACHE_BNL::skip_recurrent_candidate_for_match(uchar *rec_ptr) > +bool JOIN_CACHE_BNL::skip_next_candidate_for_match(uchar *rec_ptr) > { > pos= rec_ptr-base_prefix_length; > - return skip_recurrent_match(); > + return skip_as_unneeded_match(); > } > > > @@ -3162,7 +3265,7 @@ > The methods performs the necessary preparations to read the next record > from the join buffer into the record buffer by the method > read_next_candidate_for_match, or, to skip the next record from the join > - buffer by the method skip_recurrent_candidate_for_match. > + buffer by the method skip_next_candidate_for_match. > This implementation of the virtual method moves to the next record > in the chain of all records from the join buffer that are to be > equi-joined with the current record from join_tab. > @@ -3187,23 +3290,25 @@ > Check whether the matching record from the BNLH cache is to be skipped > > SYNOPSIS > - skip_recurrent_candidate_for_match > + skip_next_candidate_for_match > rec_ptr pointer to the position in the join buffer right after > the previous record > > DESCRIPTION > This implementation of the virtual function just calls the > method get_match_flag_by_pos to check whether the record referenced > - by ref_ptr has its match flag set on. > + by ref_ptr has its match flag set to MATCH_FOUND. > > RETURN VALUE > - TRUE the record referenced by rec_ptr has its match flag set on > + TRUE the record referenced by rec_ptr has its match flag set to > + MATCH_FOUND > FALSE otherwise > */ > > -bool JOIN_CACHE_BNLH::skip_recurrent_candidate_for_match(uchar *rec_ptr) > +bool JOIN_CACHE_BNLH::skip_next_candidate_for_match(uchar *rec_ptr) > { > - return get_match_flag_by_pos(rec_ptr); > + return join_tab->check_only_first_match() && > + (get_match_flag_by_pos(rec_ptr) == MATCH_FOUND); > } > > > @@ -3364,7 +3469,7 @@ > int JOIN_TAB_SCAN_MRR::next() > { > char **ptr= (char **) cache->get_curr_association_ptr(); > - uint rc= join_tab->table->file->multi_range_read_next(ptr) ? -1 : 0; > + int rc= join_tab->table->file->multi_range_read_next(ptr) ? -1 : 0; > if (!rc) > { > /* > @@ -3482,7 +3587,8 @@ > { > DBUG_ENTER("bka_range_seq_skip_record"); > JOIN_CACHE_BKA *cache= (JOIN_CACHE_BKA *) rseq; > - bool res= cache->get_match_flag_by_pos((uchar *) range_info); > + bool res= cache->get_match_flag_by_pos((uchar *) range_info) == > + JOIN_CACHE::MATCH_FOUND; > DBUG_RETURN(res); > } > > @@ -3560,7 +3666,7 @@ > The method performs the necessary preparations to read the next record > from the join buffer into the record buffer by the method > read_next_candidate_for_match, or, to skip the next record from the join > - buffer by the method skip_recurrent_match. > + buffer by the method skip_as_unneeded_match. > This implementation of the virtual method get_next_candidate_for_match > just decrements the counter of the records that are to be iterated over > and returns the value of curr_association as a reference to the position > @@ -3584,23 +3690,25 @@ > Check whether the matching record from the BKA cache is to be skipped > > SYNOPSIS > - skip_recurrent_candidate_for_match > + skip_next_candidate_for_match > rec_ptr pointer to the position in the join buffer right after > the previous record > > DESCRIPTION > This implementation of the virtual function just calls the > method get_match_flag_by_pos to check whether the record referenced > - by ref_ptr has its match flag set on. > + by ref_ptr has its match flag set to MATCH_FOUND. > > RETURN VALUE > - TRUE the record referenced by rec_ptr has its match flag set on > + TRUE the record referenced by rec_ptr has its match flag set to > + MATCH_FOUND > FALSE otherwise > */ > > -bool JOIN_CACHE_BKA::skip_recurrent_candidate_for_match(uchar *rec_ptr) > +bool JOIN_CACHE_BKA::skip_next_candidate_for_match(uchar *rec_ptr) > { > - return get_match_flag_by_pos(rec_ptr); > + return join_tab->check_only_first_match() && > + (get_match_flag_by_pos(rec_ptr) == MATCH_FOUND); > } > > > @@ -3691,7 +3799,9 @@ > If is assumed that the functions starts reading at the position of > the record length which is provided for each records in a BKA cache. > After the key is built the 'pos' value points to the first position after > - the current record. > + the current record. > + The function just skips the records with MATCH_IMPOSSIBLE in the > + match flag field if there is any. > The function returns 0 if the initial position is after the beginning > of the record fields for last record from the join buffer. > > @@ -3708,6 +3818,8 @@ > uchar *init_pos; > JOIN_CACHE *cache; > > +start: > + > /* Any record in a BKA cache is prepended with its length */ > DBUG_ASSERT(with_length); > > @@ -3727,6 +3839,13 @@ > > /* Read all flag fields of the record */ > read_flag_fields(); > + > + if (with_match_flag && > + (Match_flag) curr_rec_pos[0] == MATCH_IMPOSSIBLE ) > + { > + pos= init_pos+rec_len; > + goto start; > + } > > if (use_emb_key) > { > > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2010-10-01 18:05:27 +0000 > +++ b/sql/sql_select.cc 2010-10-06 12:14:08 +0000 > @@ -7090,9 +7090,13 @@ > used_tables2|= current_map; > COND *tmp_cond= make_cond_for_table(on_expr, used_tables2, > current_map, FALSE); > + add_cond_and_fix(&tmp_cond, tab->on_precond); > if (tmp_cond) > { > JOIN_TAB *cond_tab= tab < first_inner_tab ? first_inner_tab : tab; > + Item **sel_cond_ref= tab < first_inner_tab ? > + &first_inner_tab->on_precond : > + &tab->select_cond; > /* > First add the guards for match variables of > all embedding outer join operations. > @@ -7115,15 +7119,15 @@ > tmp_cond->quick_fix_field(); > /* Add the predicate to other pushed down predicates */ > DBUG_PRINT("info", ("Item_cond_and")); > - cond_tab->select_cond= !cond_tab->select_cond ? tmp_cond : > - new Item_cond_and(cond_tab->select_cond, > - tmp_cond); > + *sel_cond_ref= !(*sel_cond_ref) ? > + tmp_cond : > + new Item_cond_and(*sel_cond_ref, tmp_cond); > DBUG_PRINT("info", ("Item_cond_and 0x%lx", > - (ulong)cond_tab->select_cond)); > - if (!cond_tab->select_cond) > - DBUG_RETURN(1); > - cond_tab->select_cond->update_used_tables(); > - cond_tab->select_cond->quick_fix_field(); > + (ulong)(*sel_cond_ref))); > + if (!(*sel_cond_ref)) > + DBUG_RETURN(1); > + (*sel_cond_ref)->quick_fix_field(); > + (*sel_cond_ref)->update_used_tables(); > if (cond_tab->select) > cond_tab->select->cond= cond_tab->select_cond; > } > @@ -13246,7 +13250,7 @@ > DBUG_RETURN(nls); > } > int error; > - enum_nested_loop_state rc; > + enum_nested_loop_state rc= NESTED_LOOP_OK; > READ_RECORD *info= &join_tab->read_record; > > if (join_tab->flush_weedout_table) > @@ -13279,18 +13283,21 @@ > > /* Set first_unmatched for the last inner table of this group */ > join_tab->last_inner->first_unmatched= join_tab; > - } > + if (join_tab->on_precond && !join_tab->on_precond->val_int()) > + rc= NESTED_LOOP_NO_MORE_ROWS; > + } > join->thd->row_count= 0; > > if (join_tab->loosescan_match_tab) > join_tab->loosescan_match_tab->found_match= FALSE; > > - error= (*join_tab->read_first_record)(join_tab); > - > - if (join_tab->keep_current_rowid) > - join_tab->table->file->position(join_tab->table->record[0]); > - > - rc= evaluate_join_record(join, join_tab, error); > + if (rc != NESTED_LOOP_NO_MORE_ROWS) > + { > + error= (*join_tab->read_first_record)(join_tab); > + if (join_tab->keep_current_rowid) > + join_tab->table->file->position(join_tab->table->record[0]); > + rc= evaluate_join_record(join, join_tab, error); > + } > } > > /* > > === modified file 'sql/sql_select.h' > --- a/sql/sql_select.h 2010-10-01 18:05:27 +0000 > +++ b/sql/sql_select.h 2010-10-06 12:14:08 +0000 > @@ -161,6 +161,8 @@ > KEYUSE *keyuse; /**< pointer to first used key */ > SQL_SELECT *select; > COND *select_cond; > + COND *on_precond; /**< part of on condition to check before > + accessing the first inner table */ > QUICK_SELECT_I *quick; > /* > The value of select_cond before we've attempted to do Index Condition > @@ -678,6 +680,14 @@ > */ > uchar *curr_rec_link; > > + /* > + This flag is set to TRUE if join_tab is the first inner table of an outer > + join and the latest record written to the join buffer is detected to be > + null complemented after checking on conditions over the outer tables for > + this outer join operation > + */ > + bool last_written_is_null_compl; > + > /* > The number of fields put in the join buffer of the join cache that are > used in building keys to access the table join_tab > @@ -792,8 +802,11 @@ > /* Read a referenced field from the join buffer */ > bool read_referenced_field(CACHE_FIELD *copy, uchar *rec_ptr, uint *len); > > - /* Shall skip record from the join buffer if its match flag is on */ > - virtual bool skip_recurrent_match(); > + /* Shall skip record from the join buffer if its match flag is set to 1 */ > + virtual bool skip_as_matched(); Please s/1/MATCH_FOUND/; > + > + /* Shall skip record from the join buffer if its match flag is not-zero */ > + virtual bool skip_as_unneeded_match(); > > /* > True if rec_ptr points to the record whose blob data stay in > @@ -836,11 +849,11 @@ > virtual uchar *get_next_candidate_for_match()= 0; > /* > Shall check whether the given record from the join buffer has its match > - flag set on and, if so, skip the record in the buffer. > + flag settings commands to skip the record in the buffer. > */ > - virtual bool skip_recurrent_candidate_for_match(uchar *rec_ptr)= 0; > + virtual bool skip_next_candidate_for_match(uchar *rec_ptr)= 0; > /* > - Shall read the given record from the join buffer into the > + Shall read the given record from the join buffer intTCo the Is the above change a typo ^^ > the corresponding record buffer > */ > virtual void read_next_candidate_for_match(uchar *rec_ptr)= 0; > @@ -868,6 +881,21 @@ > > public: > > + /* > + The enumeration type Match_flag describes possible states of the match flag > + field stored for the records of the first inner tables of outer joins and > + semi-joins in the cases when the first match strategy is used for them. > + When a record with match flag field is written into the join buffer the > + state of the field usually is MATCH_NOT_FOUND unless this is a record of the > + first inner table of the outer join for which the on precondition (the > + condition from on expression over outer tables) has turned out not to be > + true. In the last case the state of the match flag is MATCH_IMPOSSIBLE. > + The state of the match flag field is changed to MATCH_FOUND as soon as > + the first full matching combination of inner tables of the outer join or > + the semi-join is discovered. > + */ > + enum Match_flag { MATCH_NOT_FOUND, MATCH_FOUND, MATCH_IMPOSSIBLE }; > + > /* Table to be joined with the partial join records from the cache */ > JOIN_TAB *join_tab; > > @@ -920,7 +948,7 @@ > virtual void get_record_by_pos(uchar *rec_ptr); > > /* Shall return the value of the match flag for the positioned record */ > - virtual bool get_match_flag_by_pos(uchar *rec_ptr); > + virtual enum Match_flag get_match_flag_by_pos(uchar *rec_ptr); > > /* Shall return the position of the current record */ > virtual uchar *get_curr_rec() { return curr_rec_pos; } BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

Re: [Maria-developers] Latest libmysqld.so patch
by Kristian Nielsen 05 Oct '10

05 Oct '10

Sergei Golubchik <serg(a)askmonty.org> writes: Thanks for review! What I would now like to do is to discuss how to proceeed (where to push this basically). On the one hand, the patch got rather more intrusive than I first wanted. The main issue is that now the mysys/mystrings/dbug code is linked dynamically by default, so these additional libmysys.so etc. libraries must be installed by packagers etc. for things to work; similarly extra -lmysys etc. is needed when using libmysqld.a. On the other hand, I think this is generally an improvement, linking code dynamically rather than statically. Saves duplicating code and so on, and dynamic linking is where the world is moving (has moved, really). I even tested the overhead of dynamic linking, it is two clock cycles per function call into a different library (I plan to write a short blog about that). So I think it is mainly a question on where we want to risk pushing this: 5.1, 5.2, or 5.3? We could even add it to the list of patches in the .debs for 5.1. (BTW, Gentoo people seems to have picked up the patch already in their MySQL and MariaDB 5.1 packages). And btw, I would like your help/hints on how to extend mysql_config to provide the right information for how to link. With those general remarks, here follow comments, inline: > Can I branch this your tree from somewhere ? bzr+ssh://bazaar.launchpad.net/~knielsen/maria/mariadb-5.1-mwl74-libmysqld.… >> The plugin may optionally specify @plugin_static_if_no_embedded@ in >> CFLAGS/CXXFLAGS for the static target; this will avoid needlessly >> compiling with -fPIC if no libmysqld is being built. > > I failed to understand this paragraph :( With this patch, every source file for a plugin is compiled twice, once with -fPIC and once without. However, if we are not building libmysqld, then the -fPIC versions will never be used. In this case, @plugin_static_if_no_embedded@ will add -static to CFLAGS, avoiding the needless compilations with -fPIC. Otherwise it will be empty. Hope this was clearer. [There is BTW another similar issue: when building a convenience library, libtool will again compile sources both with and without -fPIC. But it only ever uses the -fPIC objects in the convenience library, the objects without -fPIC are not used. I even tested the newest libtool version, and also found an email thread discussing this (and agreeing that the problem is there)]. >> This patch only does what is necessary to build libmysqld.so. There >> are some more cleanups that are possible now that we are using libtool >> more fully, which will be done in subsequent patches: >> >> - In libmysql_r/, we should be able to just link libmysys.la etc, >> instead of symlinking and re-compiling sources into the directory. >> >> - In libmysql/, we can similarly avoid symlinking and recompiling >> sources if we instead build a libmysys_nothread.la library with >> appropriate CFLAGS and link that. > > I'd rather not build libmysql at all. > Or, at least, not by default. Right, let's add this on top of this patch once we find out where we want to push it? If we link libmysql_r with libmysys, it will also solve another issue I have (with conflicts between C and assembler versions of bmovXXX); I postponed this issue until we decided about linking libmysql_r with mysys etc (it seems you agree we should do this). >> === modified file 'config/ac-macros/plugins.m4' >> --- config/ac-macros/plugins.m4 2010-09-18 07:53:48 +0000 >> +++ config/ac-macros/plugins.m4 2010-09-27 12:41:05 +0000 >> @@ -115,18 +115,32 @@ dnl ------------------------------------ >> dnl Macro: MYSQL_PLUGIN_STATIC >> dnl >> dnl SYNOPSIS >> -dnl MYSQL_PLUGIN_STATIC([name],[libmyplugin.a]) >> +dnl MYSQL_PLUGIN_STATIC([name],[libmyplugin.a],[libmyplugin_embedded.a]) >> dnl >> dnl DESCRIPTION >> -dnl Declare the name for the static library >> +dnl Declare the name for the static library >> +dnl >> +dnl Third argument is optional, only needed for special plugins that depend >> +dnl on server internals and have source files that must be compiled specially >> +dnl with -DEMBEDDED_LIBRARY for embedded server. If specified, the third >> +dnl argument is used to link embedded server instead of the second. > > Does that mean that all plugin files will be rebuilt with > -DEMBEDDED_LIBRARY if libmyplugin_embedded.a is specified ? No. It means that the plugin Makefile.am has the possibility to specify a different library to be used for embedded. Later in the patch I modify all plugins that need it (ie. they before specified MYSQL_PLUGIN_DEPENDS_ON_MYSQL_INTERNALS) so that they build a convenience library (once) with all the source files that do not depend on internals. And compile the few source files that do depend on internals in two versions with or without -DEMBEDDED_LIBRARY. And then link those together to two different libraries, one for server, one for embedded. With this, it's basically up to the plugin Makefile.am to do what it wants. >> -AC_DEFUN([MYSQL_PLUGIN_DEPENDS_ON_MYSQL_INTERNALS],[ >> - MYSQL_REQUIRE_PLUGIN([$1]) >> - m4_define([MYSQL_PLUGIN_DEPENDS_ON_MYSQL_INTERNALS_]AS_TR_CPP([$1]), [$2]) >> -]) > > I thought we agreed on IRC to keep it. > Do you mean, you tried it and it didn't work? Yes. I tried quite hard to find a way to keep compatibility. But failed :-( In the end I could not think of anything sensible to do for MYSQL_PLUGIN_DEPENDS_ON_MYSQL_INTERNALS(), so I removed it. This is related to your question below, about keeping compatibility so that eg. PBXT could still work unmodified between MySQL and MariaDB. I thought a lot about how to do this, but in the end failed. The hardest problem is that to link libmysqld.so with libtool, we need plugins to provide libXXX.la files in MYSQL_PLUGIN_STATIC(). And those libXXX.la simply will not work with the magic loop in MySQL sources that extracts objects from libXXX.a, removes duplicates, and links them back again. At least I couldn't see how to do it. So in the end I gave up on this compatibility. Sad, however a small comfort is that - This only hits plugins that say MYSQL_PLUGIN_DEPENDS_ON_MYSQL_INTERNALS(), which ideally they shouldn't anyway. - This only hits static plugins, which in any case need an incompatible maria_declare_plugin() added in the source (though this can be handled with #ifdef; I wonder if there is m4/automake equivalent ifdef that could help here?) If you have an idea on how to achieve compatibility and do something sensible for MYSQL_PLUGIN_DEPENDS_ON_MYSQL_INTERNALS(), then let me know ... >> +# __MYSQL_EMIT_CHECK_PLUGIN arguments: >> +# >> +# 1 - plugin identifying name >> +# 2 - plugin identifying name, with `-' replaced by `_' >> +# 3 - plugin long name >> +# 4 - plugin description >> +# 5 - mysql_plugin_define (eg. WITH_xxx_STORAGE_ENGINE) >> +# 6 - directory >> +# 7 - static target (if supports static build) >> +# 8 - dynamic target (if supports dynamic build) >> +# 9 - mandatory flag >> +# 10 - disabled flag >> +# 11 - static target for libmysqld (if different from mysqld) > > perhaps "if different from $7" ? Fixed, thanks. >> +# 12 - actions > > better use 'dnl' here, not '#' > > because '#'-lines will go into configure script, where they will mean > nothing, as the macro will be already expanded. Fixed >> +dnl If not building libmysqld embedded server, then there is no need to build >> +dnl shared object versions of static plugins. > > heh :) > There, on the contrary, I'd rather use '#' for comments, not 'dnl' Ok, I see, thanks for helping an m4 newbee :-) Fixed. >> === modified file 'configure.in' >> --- configure.in 2010-08-27 14:12:44 +0000 >> +++ configure.in 2010-09-13 20:17:09 +0000 >> @@ -2846,9 +2845,6 @@ if test "$with_server" != "no" -o "$THRE >> then >> AC_DEFINE([THREAD], [1], >> [Define if you want to have threaded code. This may be undef on client code]) >> - # Avoid _PROGRAMS names >> - THREAD_LOBJECTS="thr_alarm.o thr_lock.o thr_mutex.o thr_rwlock.o my_pthread.o my_thr_init.o mf_keycache.o mf_keycaches.o waiting_threads.o" >> - AC_SUBST(THREAD_LOBJECTS) > > What was that? Definitions for mysys/Makefile.am to know what to build > (or not) depending on whether threads are enabled ? Yes. This is fixing an inconsistency in the original sources. These source files should only be compiled when threads are enabled. There was code to deal with this in two places: here in configure.in, and in mysys/Makefile.am. Each place handled a different set of files, and there was overlap (maybe due to merge error?) Anyway, with pure static libraries, this overlap did not cause problems apparently, but with libtool and .so linking, it caused a failure. So I decided to clean it up to be done in one place only. >> === modified file 'dbug/Makefile.am' >> --- dbug/Makefile.am 2010-08-27 14:12:44 +0000 >> +++ dbug/Makefile.am 2010-09-28 11:23:14 +0000 >> @@ -16,10 +16,10 @@ >> # MA 02111-1307, USA >> >> INCLUDES = -I$(top_builddir)/include -I$(top_srcdir)/include >> -LDADD = libdbug.a ../mysys/libmysys.a ../strings/libmystrings.a >> -pkglib_LIBRARIES = libdbug.a >> +LDADD = libdbug.la ../mysys/libmysys.la ../strings/libmystrings.la > > better to use $(top_builddir)/libmysys.la etc > (here and below) Fixed (throughout the patch) > How's the build time - new, libmysql.so vs. the old libmysqld.a ? Thanks for remind me, I checked. These are on a quad-core with no ccache: BUILD/compile-pentium64-valgrind-max: Before: 3m19.454s After: 4m18.947s So build time _is_ increased somewhat ... > Paul would apparently want his engine to work both with MySQL and > MariaDB. Could we somehow make it possible (applies to other engines > too)? (See discussion above) Thanks, - Kristian.

2 1

[Maria-developers] MWL#89 "Cost-based choice between Materialization and IN->EXISTS" rev1 for review
by Timour Katchaounov 05 Oct '10

05 Oct '10

Igor, Please find attached a combined patch for the first implementation of: MWL#89 Cost-based choice between Materialization and IN->EXISTS The tree is in Lanunchpad under buildbot: https://code.launchpad.net/~maria-captains/maria/5.3-mwl89 Timour

1 0

[Maria-developers] WL#153 New (by Dreas): Permission denied message doesn't tell which database was involved
by worklog-noreply＠askmonty.org 05 Oct '10

05 Oct '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Permission denied message doesn't tell which database was involved CREATION DATE..: Tue, 05 Oct 2010, 08:43 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 153 (http://askmonty.org/worklog/?tid=153) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: When there are grants missing, the output shows for example: SELECT command denied to user 'user'@'localhost' for table 'my_test_table' This doesn't tell you what current database was involved, making it hard(er) to find out which grant is exactly missing. Would be great if the relevant database is also shown in the error response. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0