January 2011 - developers - lists.mariadb.org

[Maria-developers] Buildbot will be down start of next week due to upgrade
by Kristian Nielsen 24 Jan '11

24 Jan '11

Next week, on Monday, I plan to take down Buildbot and start the upgrade process to version 0.8.x. This is a somewhat major upgrade, as Buildbot 0.8 switches to storing all its state and history in a MySQL (well MariaDB probably ;-) database rather than gazilions of Python-encoded files. Hopefully this will speed up our Buildbot, which has frankly gotten painfully slow. I plan to stop Buildbot, take a full backup, then attempt the upgrade and data conversion. Buildbot will be down this time, which I estimate could be 1-2 days, but it is hard to tell for sure without knowing what issues could arise. If the upgrade fails in some way, I will revert from the backup (but I hope of course to get it running on the new version). >From reading the docs, it looks like we will loose the history of past builds. I will see if there is some way to avoid that, but if not I suppose we can still live with it (note that the cross-reference search of old test failures is not affected by the upgrade, and will not loose any history). - Kristian.

2 3

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

24 Jan '11

> Hi, Lichao! > > On Jan 10, Lichao Xie wrote: >> 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? > > Yes, see how MyISAM does it - it's pretty straightforward, and only > requires two index lookups. MyISAM seemingly read all keys in the range. And I test MyISAM table use mysqlslap, the test case is: ./mysqlslap --query="select count(xn_id) as num from roman1.r_paihang where dengji > 4 and dengji < 11" --number-of-queries=10000000 --concurrency=30 -uroot --host=10.134.11.17 --port=10001 the result is : 10.134.11.17:/data/mqq_data$ mysqladmin extended-status -i 1 -r -uroot -S /tmp/mysql-tdmdb-14.sock | grep Com_select | Com_select | 41115084 | | Com_select | 9 | | Com_select | 14 | | Com_select | 23 | | Com_select | 25 | | Com_select | 36 | | Com_select | 8 | | Com_select | 26 | | Com_select | 34 | | Com_select | 24 | | Com_select | 16 | | Com_select | 10 | | Com_select | 8 | the table scheme is: CREATE TABLE `r_paihang` ( `xn_id` int(11) NOT NULL DEFAULT '0', `company` int(8) DEFAULT NULL, `dengji` int(6) DEFAULT NULL, `position` tinyint(3) DEFAULT '1', `tili` int(11) DEFAULT '0', `zhili` int(11) DEFAULT '0', `meili` int(11) DEFAULT '0', `worth` int(11) DEFAULT '0', `sex` int(1) DEFAULT '0', `online_time` int(11) DEFAULT NULL, `sign_time` datetime DEFAULT NULL, `last_time` datetime DEFAULT NULL, `yq_id` int(12) NOT NULL DEFAULT '0', PRIMARY KEY (`xn_id`), KEY `company` (`company`), KEY `worth` (`worth`), KEY `zhili` (`zhili`), KEY `position` (`position`), KEY `meili` (`meili`), KEY `tili` (`tili`), KEY `sex` (`sex`), KEY `dengji` (`dengji`), KEY `sign_time` (`sign_time`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; the case runs too slow.... If there is a storage engine api like records_in_range() to get the exact records in the range from the storage engine, the result will be 10^4 qps for this case... > > Regards, > Sergei > 2011/1/20 Sergei Golubchik <serg(a)askmonty.org>: > Hi, Lichao! > > Please send questions like this to maria-developers(a)lists.launchpad.net. > In your replies make sure send replies to the list, e.g. use "Reply to All". > It's a public mailing list dedicated to MySQL and MariaDB internals, source > code, and related things. I am subscribed, so I'll see you mail there, and you > may be sure I will, because it won't be accidentally catched by my spam filter, > or sorted out in some obscure folder. Furthermore other subscribers will see > your question and could reply if I will be not available (e.g. I could be > travelling). > Thank you. Sarry, my fault! I am resending the email. > > On Jan 20, Lichao Xie wrote: >> Hi, Sergei >> >> 2011/1/20 Sergei Golubchik <serg(a)askmonty.org>: >> > >> > On Jan 10, Lichao Xie wrote: >> >> 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? >> > >> > Yes, see how MyISAM does it - it's pretty straightforward, and only >> > requires two index lookups. >> > Regards, > Sergei >

2 6

Re: [Maria-developers] [Commits] Rev 3014: New table creation/renaming block added if old encoded table present. in file:///home/bell/maria/bzr/work-maria-5.1-CREATE/
by Sergei Golubchik 20 Jan '11

20 Jan '11

Hi, sanja! On Jan 13, sanja(a)askmonty.org wrote: > revno: 3014 > revision-id: sanja(a)askmonty.org-20110113180607-e5hc8zuugxxjtn6k > parent: psergey(a)askmonty.org-20110112083018-90rw058f00uslvoz > committer: sanja(a)askmonty.org > branch nick: work-maria-5.1-CREATE > timestamp: Thu 2011-01-13 20:06:07 +0200 > message: > New table creation/renaming block added if old encoded table present. First: there is no test case for the new (as compared with the previous patch) change. Second: I think "old_path" does not belong to the check_table_file_presence() function. The function simply checks if the file exists, there is no place for the concept of "old path" here. You need to take care of the renaming for upgrade and old path outside of check_table_file_presence(). Regards, Sergei

2 1

[Maria-developers] index_merge, @@sort_buffer_size, and Sort_XXX counters
by Sergey Petrunya 20 Jan '11

20 Jan '11

Hello, index_merge uses @@sort_buffer_size as a guide of how much space it can use for sorting. I think there is an issue with the way it is done. Before index_merge was introduced, @@sort_buffer_size was used only by filesort (also for couple of other cases like GROUP_CONCAT with sorting, but I consider them to be too rare to be of practical importance). This allowed the following: 1. One can set @@sort_buffer_size to control how much space is to be allocated 2. SHOW STATUS will produce these numbers: Sort_merge_passes Sort_range Sort_scan Sort_range+Sort_scan gives the total number of filesort invocations. Sort_merge_passes is the number of merges. One can get average amount of merges per sort operation by calculating: Sort_merge_passes / (Sort_range + Sort_scan) (1) If the number is high, then it means that it could be useful to increase @@sort_buffer_size. Now, if we take index_merge into the picture, the counters are much less useful. index_merge causes Sort_merge_passes to be incremented, but we dont have a counter for index_merge operations, so formula (1) becomes invalid. Possible solutions are: Option#1: introduce a counter Sort_index_merge_count (name subject to discussion), which will mean "number of index_merge sort union/intersection scans". Option#2: make it so that index_merge does not cause Sort_merge_passes to be incremented. I think that in this case we'll need a separate variable to control index_merge's buffer size. it could have @@sort_buffer_size value as default. Any comments? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

[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