- developers - lists.mariadb.org

[Maria-developers] Optimization about count(*) of mysql 5.1.38
by Lichao Xie(谢立超) 19 Jan '11

19 Jan '11

hi all: I have a question about the optimization of count(*) in a range. Sql for mysql like:select count(*) from t where id > 10 and id < 1000000; In table t, there is an B-tree index on id field, this sql will read hundreds of thousands of records from the storage engine, that is a time-consuming operation. I am developing a storage engine, I want to know, is there a good method to caculate the records in a range, rather than read all records or keys in the range? For example, does mysql call an API like records_in_range() to get the records in a range? After all, the range could be caculated by reading twice index.

2 1

[Maria-developers] [psergey@askmonty.org: No "Using index" support in sort-intersect - intentional?]
by Sergey Petrunya 19 Jan '11

19 Jan '11

Another question: why is type='range' for sort_intersect? We have type=index_merge for intersect and [sort]_union, so it seems rather confusing that we have type=range for sort_intersect? ----- Forwarded message from Sergey Petrunya <psergey(a)askmonty.org> ----- Date: Wed, 19 Jan 2011 11:11:14 +0300 From: Sergey Petrunya <psergey(a)askmonty.org> To: igor(a)askmonty.org Cc: Maria Developers <maria-developers(a)lists.launchpad.net> Subject: No "Using index" support in sort-intersect - intentional? Hello Igor, I was looking at sort-intersect and noticed the following: Consider this table: CREATE TABLE `t1` ( `k1` int(11) DEFAULT NULL, `k2` int(11) DEFAULT NULL, `filler` char(100) DEFAULT NULL, KEY `k1` (`k1`), KEY `k2` (`k2`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Let's try a sort-intersect query: MariaDB [timerge1]> EXPLAIN select 1 from t1 where k1<30 and k2 < 30\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: range possible_keys: k1,k2 key: k1,k2 key_len: 5,5 ref: NULL rows: 1 Extra: Using sort_intersect(k1,k2); Using where 1 row in set (0.01 sec) The query doesn't use any columns not covered by the used indexes, however, we don't see "using index". Analogous ROR-intersect query will use "Using index": MariaDB [timerge1]> EXPLAIN select 1 from t1 where k1=30 and k2 = 30\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index_merge possible_keys: k1,k2 key: k1,k2 key_len: 5,5 ref: NULL rows: 1 Extra: Using intersect(k1,k2); Using where; Using index 1 row in set (0.00 sec) BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ----- End forwarded message ----- -- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] No "Using index" support in sort-intersect - intentional?
by Sergey Petrunya 19 Jan '11

19 Jan '11

Hello Igor, I was looking at sort-intersect and noticed the following: Consider this table: CREATE TABLE `t1` ( `k1` int(11) DEFAULT NULL, `k2` int(11) DEFAULT NULL, `filler` char(100) DEFAULT NULL, KEY `k1` (`k1`), KEY `k2` (`k2`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Let's try a sort-intersect query: MariaDB [timerge1]> EXPLAIN select 1 from t1 where k1<30 and k2 < 30\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: range possible_keys: k1,k2 key: k1,k2 key_len: 5,5 ref: NULL rows: 1 Extra: Using sort_intersect(k1,k2); Using where 1 row in set (0.01 sec) The query doesn't use any columns not covered by the used indexes, however, we don't see "using index". Analogous ROR-intersect query will use "Using index": MariaDB [timerge1]> EXPLAIN select 1 from t1 where k1=30 and k2 = 30\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index_merge possible_keys: k1,k2 key: k1,k2 key_len: 5,5 ref: NULL rows: 1 Extra: Using intersect(k1,k2); Using where; Using index 1 row in set (0.00 sec) BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] review: [Commits] Rev 2875: Fix problem of making on-disk cache table. in file:///home/bell/maria/bzr/work-maria-5.3-subquerycache/
by Michael Widenius 17 Jan '11

17 Jan '11

Hi! Here is the review: >>>>> "sanja" == sanja <sanja(a)askmonty.org> writes: <cut> sanja> Fix problem of making on-disk cache table. sanja> Removed 'uniques=1' which lead to creating unneeded unique index creation. <cut> ---------------------------------------------------------------------- === modified file 'sql/sql_expression_cache.cc' --- a/sql/sql_expression_cache.cc 2010-11-02 09:47:36 +0000 +++ b/sql/sql_expression_cache.cc 2011-01-05 19:37:56 +0000 <cut> @@ -123,7 +126,6 @@ void Expression_cache_tmptable::init() goto error; } cache_table->s->keys= 1; - cache_table->s->uniques= 1; ref.null_rejecting= 1; ref.disable_cache= FALSE; ref.has_record= 0; The above should now already be in 5.3 tree as part of Sergei's push of my patch). (Just for your information) @@ -195,10 +197,12 @@ Expression_cache::result Expression_cach if (res) { subquery_cache_miss++; + miss++; DBUG_RETURN(MISS); } subquery_cache_hit++; + hit++; *value= cached_result; DBUG_RETURN(Expression_cache::HIT); As subquery_cache_miss and hit are global statistics variables, please use statistic_increment(subquery_cache_miss, &LOCK_status) with these. } @@ -225,7 +229,7 @@ my_bool Expression_cache_tmptable::put_v DBUG_ENTER("Expression_cache_tmptable::put_value"); DBUG_ASSERT(inited); - if (!cache_table) + if (!cache_table || frozen) { Is it not eonugh to test for 'frozen'? (You should set frozen to 0 if cache_table is not set) @@ -239,12 +243,22 @@ my_bool Expression_cache_tmptable::put_v if ((error= cache_table->file->ha_write_row(cache_table->record[0]))) { /* create_myisam_from_heap will generate error if needed */ + /* + TODO: add estimation if ondisk table is efficient if (cache_table->file->is_fatal_error(error, HA_CHECK_DUP) && create_internal_tmp_table_from_heap(table_thd, cache_table, cache_table_param.start_recinfo, &cache_table_param.recinfo, error, 1)) goto err; + */ Please change comment to #ifdef OVERFLOW_CACHE_TO_DISK_BASED_TABLE + if (cache_table->file->is_fatal_error(error, HA_CHECK_DUP)) + goto err; + /* should we leave the table */ + if ((((ulonglong)hit) * 100) / miss < EXPRASSION_TMPTABLE_ALLOWED_HITRATE) What happens if miss is 0 Shouldn't the above be? ((ulonglong) hit*100) / (hit + miss))... + goto err; // remove the temporary table + else Remove else + frozen= TRUE; // leave the temporary table } Try to move the above to before #ifdef and make it in such that one can just define the ifdef if one wants overflow to disk. === modified file 'sql/sql_expression_cache.h' <cut> @@ -50,6 +50,8 @@ class Item_field; class Expression_cache_tmptable :public Expression_cache { + ulong hit, miss; + bool frozen; ulong -> ulonglong (in a join you can have a LOT of row combinations) public: Expression_cache_tmptable(THD *thd, List<Item*> &dependants, Item *value); virtual ~Expression_cache_tmptable(); Ok to push after above changes. Regards, Monty

2 1

Re: [Maria-developers] test this list
by Vladislav Vaintroub 17 Jan '11

17 Jan '11

> -----Original Message----- > From: Vladislav Vaintroub [mailto:wlad@montyprogram.com] > Sent: Montag, 17. Januar 2011 13:12 > To: maria-developers(a)lists.launchpad.net > Subject: test this list > > Please ignore

1 0

[Maria-developers] lp: url support in firefox
by Sergei Golubchik 15 Jan '11

15 Jan '11

Hi. I've just written a small firefox add-on that adds support for lp: URI scheme to firefox. Nothing too magical, the add-on simply rewrites the url, replacing lp: with http://code.launchpad.net/ but at least now one can paste bzr urls (e.g. from bzr info, or email) directly into firefox and go to the corresponding branch page. https://addons.mozilla.org/en-US/firefox/addon/lp-uri-handler/ Regards, Sergei

1 0

[Maria-developers] test please ignore
by Vladislav Vaintroub 14 Jan '11

14 Jan '11

Checking mailing list access

1 0

Re: [Maria-developers] [Commits] Rev 2876: Fixed LP bug #698882. in file:///home/igor/maria/maria-5.3-bugs/
by Oleksandr Byelkin 14 Jan '11

14 Jan '11

Hi! On 13.01.2011 01:11, Igor Babaev wrote: > At file:///home/igor/maria/maria-5.3-bugs/ > > ------------------------------------------------------------ > revno: 2876 > revision-id: igor(a)askmonty.org-20110112231059-prhv2vncj6y2cizn > parent: igor(a)askmonty.org-20110105230330-2nzlm8641wcrwzio > committer: Igor Babaev<igor(a)askmonty.org> > branch nick: maria-5.3-bugs > timestamp: Wed 2011-01-12 15:10:59 -0800 > message: > Fixed LP bug #698882. > Made sure that the optimal fields are used by TABLE_REF objects > when building index access keys to joined tables. > Fixed a bug in the template function that sorts the elements of > a list using the bubble sort algorithm. The bug caused poor > performance of the function. Also added an optimization that > skips comparison with the most heavy elements that has been > already properly placed in the list. > Made the comparison of the fields belonging to the same Item_equal > more granular: fields belonging to the same table are also ordered > according to some rules. The patch as OK. But I'd prefer to have comment that the lists are mostly short and because of this used bubble sort algorithm (or you could use more detailed explanation from your letter to me). Also in big 'if (!cmp)' used tabulation (it is visible if you check diff where layout looks not correct, but it is correct in the result file), if it is not difficult it would be better to remove them. [skip]

1 0

[Maria-developers] WL#174 New (by Monty): Safety improvements for HANDLER commands
by worklog-noreply＠askmonty.org 14 Jan '11

14 Jan '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Safety improvements for HANDLER commands CREATION DATE..: Fri, 14 Jan 2011, 12:43 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 174 (http://askmonty.org/worklog/?tid=174) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 20 (hours remain) ORIG. ESTIMATE.: 20 PROGRESS NOTES: DESCRIPTION: There is some known problems with HANDLER implementation in some storage engines, that could potentially cause crashes that should be fixed ASAP. We should also do some trivial fixes to ensure that one gets Error 1020 (ER_CHECKREAD) Record has changed since last read in table '...' in so few cases as possible. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] Documentation for Percona patch for row-based replication of pk-less tables
by Kristian Nielsen 13 Jan '11

13 Jan '11

Hi Daniel, I merged the Percona patch row_based_replication_without_primary_key.patch into 5.2-percona. This will later be merged into MariaDB 5.3. Here is some documentation, I was not sure where to put it, so hopefully you can help. The patch adds a new feature (performance improvement), and also fixes a bug. Incidentally, do we have a list of all bugs fixed in MariaDB over MySQL? I think that would be quite useful, even if it might get a bit out of date as MySQL also fixes some bugs. I suppose it could mostly be compiled from your release notes. This would also provide motivation for developers to actually make a proper bug report on issues fixed and proper commit messages etc :-) Anyway, just a suggestion. - Kristian. ----------------------------------------------------------------------- Row-based replication of tables with no primary key. MariaDB improves on row-based replication of tables which have no primary key but do have some other index. This is based in part on the original Percona patch "row_based_replication_without_primary_key.patch", with some additional fixes and enhancements. When row-based replication is used with UPDATE or DELETE, the slave needs to locate each replicated row based on the value in columns. If the table contains at least one index, an index lookup will be used (otherwise a table scan is needed for each row, which is extremely inefficient for all but the smallest table and generally to be avoided). >From MariaDB 5.3, the slave will try to choose a good index among any available: - The primary key is used, if there is one. - Else, the first unique index without NULL-able columns is used, if there is one. - Else, a choice is made among any normal indexes on the table (eg. a FULLTEXT index is not considered). The choice of which of several non-unique indexes to use is based on the cardinality of indexes; the one that is most selective (has the smallest average number of rows per distinct tuple of column values) is prefered. Note that for this choice to be effective, it is for most storage engines (like MyISAM, InnoDB) necessary to make sure ANALYZE TABLE has been run on the slave, otherwise statistics about index cardinality will not be available. In the absense of index cardinality, the first unique index will be chosen, if any, else the first non-unique index. Prior to MariaDB 5.3, the slave would always choose the first index without considering cardinality. The slave could even choose an unusable index (like FULLTEXT) if no other index was available (Bug #58997), causing row-based replication to break in this case; this is also fixed in MariaDB 5.3.

2 1