- developers - lists.mariadb.org

[Maria-developers] Benchmarking index_merge sort_intersect
by Sergey Petrunya 26 Jan '11

26 Jan '11

Hi! Please find below results of my attempts to figure out if/when one should "index_merge_sort_intersection=on" setting. General setup information: * Experiment was done on my laptop * I picked "on time flight statistics" dataset, because - it has lots of columns, so one can't be expected to have all possible composite indexes - it allows for meaningful intersection queries - the data is real and I don't expect it to be extremely correlated (although some aiports/airlines are probably worse than others, so it's not totally uncrellated, either). * Storage engine is XtraDB * I loaded data for Q1 2009, which is 1.5M rows, and ontime.ibd file is 704M: MariaDB [ontime]> select count(*), min(FlightDate), max(FlightDate) from ontime; +----------+-----------------+-----------------+ | count(*) | min(FlightDate) | max(FlightDate) | +----------+-----------------+-----------------+ | 1578171 | 2009-01-01 | 2009-03-31 | +----------+-----------------+-----------------+ 1 row in set (15.92 sec) MariaDB [ontime]> set optimizer_switch='index_merge_sort_intersection=on'; MariaDB [ontime]> explain select count(*) from ontime where depdelay > 30 and OriginState IN ('WA', 'CA'); +----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+---------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+---------------------------------------------------------+ | 1 | SIMPLE | ontime | range | OriginState,DepDelay | DepDelay,OriginState | 5,3 | NULL | 41832 | Using sort_intersect(DepDelay,OriginState); Using where | +----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+---------------------------------------------------------+ 1 row in set (0.01 sec) ## (Run query several times until query time stabilizes) MariaDB [ontime]> select count(*) from ontime where depdelay > 30 and OriginState IN ('WA', 'CA'); +----------+ | count(*) | +----------+ | 18824 | +----------+ 1 row in set (2.37 sec) MariaDB [ontime]> set optimizer_switch='index_merge_sort_intersection=off'; MariaDB [ontime]> explain select count(*) from ontime where depdelay > 30 and OriginState IN ('WA', 'CA'); +----+-------------+--------+-------+----------------------+----------+---------+------+--------+-----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+----------------------+----------+---------+------+--------+-----------------------------------------------+ | 1 | SIMPLE | ontime | range | OriginState,DepDelay | DepDelay | 5 | NULL | 201894 | Using index condition; Using where; Using MRR | +----+-------------+--------+-------+----------------------+----------+---------+------+--------+-----------------------------------------------+ 1 row in set (0.01 sec) ## (Run query several times until query time stabilizes) MariaDB [ontime]> select count(*) from ontime where depdelay > 30 and OriginState IN ('WA', 'CA'); +----------+ | count(*) | +----------+ | 18824 | +----------+ 1 row in set (18.27 sec) ## For comparison, without indexes whatsoever: MariaDB [ontime]> select count(*) from ontime ignore index (depdelay, originstate) where depdelay > 30 and OriginState IN ('WA', 'CA'); +----------+ | count(*) | +----------+ | 18824 | +----------+ 1 row in set (15.50 sec) "iostat -x" showed disk utilization as 0% during all of the above listed queries, so all accessed data was either in cache or in buffer pool. For an idea about predicate selectivities and correlation: ROWS FRACTION whole table 1578171 100.0% depdelay>30 151806 9.6% OriginState IN ('WA', 'CA') 206576 13.1% both 18824 1.1% Conclusions: - sort-intersect gives about 7x improvement - Igor's statement that sort-intersect can't be useful when the load is not IO-bound is not supported by the experiment. My next step was to try with narrower ranges. I changed the predicates to - OriginState IN ('IL', 'VW') (100K rows instead of 200K we had with WA+CA) - depdelay > 60 (75 K rows instead of 150K we had with depdelay>30) MariaDB [ontime]> set optimizer_switch='index_merge_sort_intersection=on'; MariaDB [ontime]> explain select count(*) from ontime where depdelay > 60 and OriginState IN ('IL', 'VT'); +----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+---------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+---------------------------------------------------------+ | 1 | SIMPLE | ontime | range | OriginState,DepDelay | DepDelay,OriginState | 5,3 | NULL | 10105 | Using sort_intersect(DepDelay,OriginState); Using where | +----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+---------------------------------------------------------+ 1 row in set (0.02 sec) # Run a few times to stabilize MariaDB [ontime]> select count(*) from ontime where depdelay > 60 and OriginState IN ('IL', 'VT'); +----------+ | count(*) | +----------+ | 6283 | +----------+ 1 row in set (0.99 sec) MariaDB [ontime]> set optimizer_switch='index_merge_sort_intersection=off'; Query OK, 0 rows affected (0.00 sec) MariaDB [ontime]> explain select count(*) from ontime where depdelay > 60 and OriginState IN ('IL', 'VT'); +----+-------------+--------+-------+----------------------+----------+---------+------+-------+-----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+----------------------+----------+---------+------+-------+-----------------------------------------------+ | 1 | SIMPLE | ontime | range | OriginState,DepDelay | DepDelay | 5 | NULL | 99636 | Using index condition; Using where; Using MRR | +----+-------------+--------+-------+----------------------+----------+---------+------+-------+-----------------------------------------------+ 1 row in set (0.02 sec) # Run a few times to stabilize MariaDB [ontime]> select count(*) from ontime where depdelay > 60 and OriginState IN ('IL', 'VT'); +----------+ | count(*) | +----------+ | 6283 | +----------+ 1 row in set (8.63 sec) MariaDB [ontime]> select count(*) from ontime ignore index (OriginState,DepDelay) where depdelay > 60 and OriginState IN ('IL', 'VT'); +----------+ | count(*) | +----------+ | 6283 | +----------+ 1 row in set (14.00 sec) The picture is similar: sort-intersect is still about 8 times better than single-index range access plan. I'm not sure about the conclusions. I suspect that there is some threshold (#records to be read through the most selective index) after which sort-intersect becomes worse than using one of the indexes. I wasn't able to hit it, though. Then I've tried changing the OriginState predicate to being simple equality, to see if we could compare againist ref access: MariaDB [ontime]> set optimizer_switch='index_merge_sort_intersection=on'; MariaDB [ontime]> explain select count(*) from ontime where depdelay > 60 and OriginState='IL'; +----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+---------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+---------------------------------------------------------+ | 1 | SIMPLE | ontime | range | OriginState,DepDelay | DepDelay,OriginState | 5,3 | NULL | 10043 | Using sort_intersect(DepDelay,OriginState); Using where | +----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+---------------------------------------------------------+ MariaDB [ontime]> select count(*) from ontime where depdelay > 60 and OriginState='IL'; +----------+ | count(*) | +----------+ | 6215 | +----------+ 1 row in set (1.00 sec) MariaDB [ontime]> set optimizer_switch='index_merge_sort_intersection=off'; MariaDB [ontime]> explain select count(*) from ontime where depdelay > 60 and OriginState='IL'; +----+-------------+--------+------+----------------------+-------------+---------+-------+--------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+----------------------+-------------+---------+-------+--------+------------------------------------+ | 1 | SIMPLE | ontime | ref | OriginState,DepDelay | OriginState | 3 | const | 201600 | Using index condition; Using where | +----+-------------+--------+------+----------------------+-------------+---------+-------+--------+------------------------------------+ 1 row in set (0.01 sec) MariaDB [ontime]> select count(*) from ontime where depdelay > 60 and OriginState='IL'; +----------+ | count(*) | +----------+ | 6215 | +----------+ 1 row in set (0.95 sec) And the benefit was gone. sort_intersect happens to be just as fast as ref access. I've changed OriginState='IL' to OriginState IN ('IL','ZZ'), where 'ZZ' is the non existant state code: MariaDB [ontime]> explain select count(*) from ontime where depdelay > 60 and OriginState IN ('IL','ZZ'); +----+-------------+--------+-------+----------------------+----------+---------+------+-------+-----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+----------------------+----------+---------+------+-------+-----------------------------------------------+ | 1 | SIMPLE | ontime | range | OriginState,DepDelay | DepDelay | 5 | NULL | 99636 | Using index condition; Using where; Using MRR | +----+-------------+--------+-------+----------------------+----------+---------+------+-------+-----------------------------------------------+ The used index has changed, MariaDB [ontime]> select count(*) from ontime where depdelay > 60 and OriginState IN ('IL','ZZ'); +----------+ | count(*) | +----------+ | 6215 | +----------+ 1 row in set (9.10 sec) And the query time went up greatly. Using FORCE INDEX to use the index that ref access was using improved the situation slightly: MariaDB [ontime]> explain select count(*) from ontime force index(originstate) where depdelay > 60 and OriginState IN ('IL','ZZ'); +----+-------------+--------+-------+---------------+-------------+---------+------+--------+-----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+-------------+---------+------+--------+-----------------------------------------------+ | 1 | SIMPLE | ontime | range | OriginState | OriginState | 3 | NULL | 201601 | Using index condition; Using where; Using MRR | +----+-------------+--------+-------+---------------+-------------+---------+------+--------+-----------------------------------------------+ MariaDB [ontime]> select count(*) from ontime force index(originstate) where depdelay > 60 and OriginState IN ('IL','ZZ'); +----------+ | count(*) | +----------+ | 6215 | +----------+ 1 row in set (2.69 sec) Disabling MRR further improved the situation: MariaDB [ontime]> set optimizer_use_mrr='disable'; Query OK, 0 rows affected (0.00 sec) MariaDB [ontime]> explain select count(*) from ontime force index(originstate) where depdelay > 60 and OriginState IN ('IL','ZZ'); +----+-------------+--------+-------+---------------+-------------+---------+------+--------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+-------------+---------+------+--------+------------------------------------+ | 1 | SIMPLE | ontime | range | OriginState | OriginState | 3 | NULL | 201601 | Using index condition; Using where | +----+-------------+--------+-------+---------------+-------------+---------+------+--------+------------------------------------+ 1 row in set (0.01 sec) MariaDB [ontime]> select count(*) from ontime force index(originstate) where depdelay > 60 and OriginState IN ('IL','ZZ'); +----------+ | count(*) | +----------+ | 6215 | +----------+ 1 row in set (1.23 sec) But it seems we can't put all the blame on MRR. The supposedly best query plan without MRR is just as bad as it was with MRR: MariaDB [ontime]> explain select count(*) from ontime where depdelay > 60 and OriginState IN ('IL','ZZ'); +----+-------------+--------+-------+----------------------+----------+---------+------+-------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+----------------------+----------+---------+------+-------+------------------------------------+ | 1 | SIMPLE | ontime | range | OriginState,DepDelay | DepDelay | 5 | NULL | 99636 | Using index condition; Using where | +----+-------------+--------+-------+----------------------+----------+---------+------+-------+------------------------------------+ 1 row in set (0.02 sec) MariaDB [ontime]> select count(*) from ontime where depdelay > 60 and OriginState IN ('IL','ZZ'); +----------+ | count(*) | +----------+ | 6215 | +----------+ 1 row in set (8.96 sec) In any case, sort_intersect doesn't care about whether one of the predicates is an equality or IN: MariaDB [ontime]> set optimizer_switch='index_merge_sort_intersection=on'; Query OK, 0 rows affected (0.00 sec) MariaDB [ontime]> explain select count(*) from ontime where depdelay > 60 and OriginState IN ('IL','ZZ'); +----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+---------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+---------------------------------------------------------+ | 1 | SIMPLE | ontime | range | OriginState,DepDelay | DepDelay,OriginState | 5,3 | NULL | 10043 | Using sort_intersect(DepDelay,OriginState); Using where | +----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+---------------------------------------------------------+ 1 row in set (0.02 sec) MariaDB [ontime]> select count(*) from ontime where depdelay > 60 and OriginState IN ('IL','ZZ'); +----------+ | count(*) | +----------+ | 6215 | +----------+ 1 row in set (0.97 sec) Conclusions: I'm not sure if the "failure" to demonstrate speedup against ref access is a genuine symptom (I think - not). Perhaps, enabling all of our improvements at once (BKA, DS-MRR, sort-intersect) will produce more coherent results, because all of these strategies use rowid-ordered disk-sweep reads, which allows to make fairer comparison between them. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 2

[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