January 2011 - developers - lists.mariadb.org

[Maria-developers] MWL#116: Efficient group commit, ready for code review
by Kristian Nielsen 04 Apr '11

04 Apr '11

Hi Serg, Please find attached the patch for code review of MWL#116: Efficient group commit. I went through all your comments in the architecture review and updated the worklog description and the code accordingly. I hope I got everything, otherwise I hope you have the patience to point out any omissions once again! The patch is pushed here if you want to see the complete code: lp:~maria-captains/maria/mariadb-5.1-mwl116 Once the code passes code review, I think the plan is to include this feature in MariaDB 5.3. Please let me know if you need anything else from me for the review. - Kristian.

2 3

[Maria-developers] Review of replication/group commit work
by Kristian Nielsen 28 Mar '11

28 Mar '11

Hi Monty, Thanks for offering to review my replication worklogs. Here are pointers to the various worklogs and patches, and suggestions for order to things in etc. Some background information is in this series on my blog: http://kristiannielsen.livejournal.com/12254.html http://kristiannielsen.livejournal.com/12408.html http://kristiannielsen.livejournal.com/12553.html http://kristiannielsen.livejournal.com/12810.html There is a tree on launchpad based on 5.2 with all the patches merged, if you want to look at the actual code in one piece: lp:~maria-captains/maria/mariadb-5.2-rpl ---- The main worklog, and the place to start, is MWL#116: http://askmonty.org/worklog/Server-Sprint/?tid=116 I think you'll have to read through the worklog/design first (it already had architecture review by Serg) in order to properly review the code. The patch for MWL#116 is here: https://lists.launchpad.net/maria-developers/msg03693.html It also has links to a lauchpad tree with just this patch. This worklog extends the storage engine API with a facility to efficiently coordinate commit order among transaction participants, and also implements group commit for binlog. ---- The next worklog is MWL#136: http://askmonty.org/worklog/Server-RawIdeaBin/?tid=136 Here is the commit mail: https://lists.askmonty.org/pipermail/commits/2010-November/000735.html (also this one, but it just renames some variables to bettter names): https://lists.askmonty.org/pipermail/commits/2010-November/000741.html This worklog makes START TRANSACTION WITH CONSISTENT SNAPSHOT, well, consistent. It also uses this for the binlog to provide non-blocking mysqldump --single-transaction --master-data. ---- The last worklog is MWL#163: http://askmonty.org/worklog/Server-Sprint/?tid=163 Here is the commit mail: https://lists.askmonty.org/pipermail/commits/2010-November/000711.html This is a re-write of a Facebook patch. Mark has mentioned this on several occasions as being important for them to fix a hot-spot. It implements an option to release InnoDB row locks earlier, before committing to binlog and storage engine (which is not safe in general, but can be safe in certain applications such as Facebook presumably). ---- That's it for now! - Kristian.

2 1

[Maria-developers] request for feedback on live schema changes patch
by Zardosht Kasheff 18 Mar '11

18 Mar '11

Hello all, In another thread, there was a discussion about what it would take to allow the storage engine to support live schema changes in mysql and mariadb. I said that I was working porting this existing feature from MySQL Cluster to MySQL 5.1.46. I would like to show the work I have done so far and solicit feedback. Attached is the diff based off of 5.1.46. Before looking at the patch, please be aware of the following things: - I am not an expert in this code. As a result, I had to use an approach of that minimizes the risk of introducing regressions. \ - My main interest is to get something safe that works and can be integrated in 5.1. As a result, kept the current mysql_alter_table functionality in place (as old_mysql_alter_table), and added MySQL Cluster's as new_mysql_alter_table. For an engine to get new_mysql_alter_table to be used, the engine must expose an alter table flag HA_GENERAL_ONLINE. - Another reason why I kept old_mysql_alter_table was that I did not want to deal with engines that implement handler::add_index and make sure that they work properly under the new API. Also, I did not want to port alter table changes related to partitioned tables. - I have run the current 5,1 mysql test suite (that is in the mysql-test directory of source tarballs), and verified that using old_mysql_alter_table and new_mysql_alter_table works fine with them. That being said, I would love to hear feedback on this patch, and would also like to understand what (if anything) can be done to get this into MariaDB 5.1. I realize that may not be possible, but if it is, I would like to try. Thanks -Zardosht

2 5

Re: [Maria-developers] [Commits] Rev 2905: Merge Percona patch MWL#47 into mariadb 5.2-percona. in http://bazaar.launchpad.net/~maria-captains/maria/5.2
by Kristian Nielsen 02 Mar '11

02 Mar '11

knielsen(a)knielsen-hq.org writes: > message: > Merge Percona patch MWL#47 into mariadb 5.2-percona. > > This patch adds options to annotate the binlog (and the mysqlbinlog > output) with the original SQL query for queries that are logged > using row-based replication. Serg raised the very relevant issue about what to do about the similar feature in MySQL 5.6: http://forge.mysql.com/worklog/task.php?id=4033 http://forge.mysql.com/worklog/task.php?id=5404 The Oracle team has worked more on this since last I looked, and the two features are now very similar. Here are the main differences, as I see it: - The MySQL 5.6 feature is enabled by --binlog_rows_query_log_events, while the MWL#47 option is called --binlog-annotate-rows-events. - MWL#47 has --replicate-annotate-rows-events which makes the slave receive and binlog the annotations. The MySQL feature always receives annotation events if present in the binlog (so replication 5.6->pre-5.6 will break if annotations are enabled). The MySQL feature binlogs the annotations based on the same --binlog-annotate-rows-events option as the master. - MWL#47 has a mysqlbinlog option --skip-annotate-rows-events, MySQL 5.6 does not. - MySQL 5.6 mysqlbinlog can log the annotations with BINLOG statements so that they are preserved with `mysqlbinlog | mysql`; MWL#47 does not have this. - MWL#47 uses binlog event type 160, MySQL 5.6 uses an ignorable event of type 29. As far as I can tell, the actual event format is identical, except for the LOG_EVENT_IGNORABLE_F flag (which says it's ok for a slave to ignore this event if it doesn't know about it, however there is no server version that knows the LOG_EVENT_IGNORABLE_F but not the MySQL annotation event). - I think there are a couple of issues with the MySQL patch (newlines in queries breaking mysqlbinlog output, duplicated annotations for INSERT DELAYED). There might be issues with the MWL#47 patch also, of course. So the question is how to prepare for when we merge MySQL 5.6. An easy option could be to just ditch MWL#47 (and possibly backport the MySQL 5.6 patch). However, as I understand it, the original sponsor of MWL#47 is already using it, and so we may have to remain compatible with the existing MWL#47 implementation. Can we get some definite information on that? Another option might be to simply change the MWL#47 to use the same event type as MySQL (the event format is the same, and we can add the LOG_EVENT_IGNORABLE_F). And replace the MWL#47 options --binlog-annotate-rows-events and --replicate-annotate-rows-events with the MySQL --binlog_rows_query_log_events option (though I think the MySQL option name is not good). But again, I am not sure if this is a valid option given possible existing usage of MWL#47. The third option I can think of is to leave MWL#47 as it is. Then when we get to merge MySQL 5.6, we will basically drop the MySQL part, doing the following to remain backwards compatible with MySQL: - Make the --binlog_rows_query_log_events option set --binlog-annotate-rows-events and --replicate-annotate-rows-events. - Make a slave read a MySQL rows_query_log_event as a MWL#47 annotation event. - Merge over the bit of the MySQL patch that makes mysqlbinlog output annotation events using a BINLOG '...' statement. This should make upgrade from MySQL 5.6 -> MariaDB fully work. Using MySQL5.6+ with MariaDB will also work, only MySQL slaves will not receive MariaDB master annotation events. The third option seems to preserve the existing MWL#47 most faithfully and does not seem to need much work in terms of the eventual merge, so this is what I suggest as the best way forward. But I am open to other (justified) opinions. - Kristian.

2 1

[Maria-developers] MariaDB Binary Log File Corruption
by Daniel Bartholomew 20 Feb '11

20 Feb '11

Hello everyone, I came across this question in the Knowledgebase: http://kb.askmonty.org/v/binary-log-file-corruption-happens-each-day The poster was able to solve the problem by increasing the slave max_allowed_packet size from 32M to 256M. Any thoughts from the list about this? My guess is that this not a MariaDB-specific thing; just a difference between 5.0 and 5.1 (and higher). But I'm asking anyway in case I'm mistaken and/or if a note should be added to the documentation for max_allowed_packet. Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

2 1

[Maria-developers] Windows installer MWL#55 finished.
by Vladislav Vaintroub 02 Feb '11

02 Feb '11

Hi all, I updated the MWL#55 (Windows installer) with the upgrade information. Now, people will be able to upgrade between prior MariaDB (or prior MySQL) versions using upgrade wizard, which is by default launched at the end of installation, if upgradable instances are found . Users will also be able to use Upgrade Wizard after the installation, at any the time convenient for them. Upgrade wizard will also be included into ZIP distribution. Besides GUI Wizard, there is a command line utility mysql_upgrade_service.exe to perform all operations necessary to upgrade an existing database instance registered as service (in fact GUI wizard uses this mysql_upgrade_service.exe as "backend"). Wizard is an MFC application. I'm sorry for that, but my Windows C/C++ GUI programming experience is not only rusty (prior to this WL last time I used it was more than a decade ago), it is also restricted to MFC only. Being MFC application, it will require at least VS Professional to build. There is no MFC in free Visual Studio Express edition, nor in free Windows SDK. Build and packaging process will handle missing MFC gracefully (build won't try to compile upgrade wizard, package won't include it , and installer won't start it at the end of installation). The repository that includes the final windows installer is lp:~maria-captains/maria/5.2-windows-packaging-upgrade Building installer is described in http://askmonty.org/worklog/Server-Sprint/?tid=55 (Section "Building installer", at the very start of the WL). Monty or Sergei (or volunteers if any): Can you please have a look the above repo and do a review . I tried to split the big patch into parts, but I did not find the result to be too satisfactory Philip: would you be able to test the upgrade wizard? You need a service running under some older mysqld.exe binary (e.g. install MySQL 5.1), you'll should get upgrade wizard launched at the end of install. You should also get Wizard at the end of installation any time you have an older version of Maria running as service. Thanks and best regards, Wlad

6 27

[Maria-developers] Buildbot Outage
by Adam M. Dutko 31 Jan '11

31 Jan '11

Sorry about adutko-centos5-amd64 being down. I've not finished changes to my monitoring script to tell me when a builder is offline. I'll try to get that done soon and send it to the list when I've updated it to work with the new buildbot status page format.

1 0

Re: [Maria-developers] replication improvements going on here and there
by Kristian Nielsen 28 Jan '11

28 Jan '11

"Rasmus Johansson" <rasmus(a)montyprogram.com> writes: > I would need your help to understand the different replication topics that > are very active now: > > - The replication work that you have been doing As far as implementation goes, it is these four worklogs, which are in code review and available in the feature preview tree lp:~maria-captains/maria/mariadb-5.2-rpl: http://askmonty.org/worklog/Server-Sprint/?tid=116 http://askmonty.org/worklog/Server-RawIdeaBin/?tid=136 http://askmonty.org/worklog/Server-Sprint/?tid=163 http://askmonty.org/worklog/Server-Sprint/?tid=132 This work improves replication in a number of ways: - Implement working group commit for the replication binlog. This greatly improves performance when running with sync_binlog=1, which is necessary to ensure the ability to reliably recover replication state after a master crash. - Ensure same commit order between innodb and binlog, without the need for the expensive prepare_commit_mutex. This is needed by XtraBackup and InnoDB hot backup to do a non-blocking online backup that can be used to provision a slave. The other existing patches I have seen solve this by having the user need to turn on/off the prepare_commit_mutex depending on whether backup is going on or not. - Allow to obtain an InnoDB consistent read snapshot and the corresponding binlog position in a fully non-blocking way. This allows for example to provision a slave from a fully non-blocking `mysqldump --master-data --single-transaction`. Previously, this required FLUSH TABLES WITH READ LOCK, which blocks queries in the server for some time. - Port the Facebook patch to release InnoDB row locks early, already during the prepare phase. This can improve performance in the presense of hot-spot rows in busy OLTP applications. (This feature must be enabled explicitly, it is off by default). - Make the transaction coordinator inside MariaDB plugable, allowing to plug in different implementations. This is mostly preparatory work for pluggable replication, as the replication binlog functions as a transaction coordinator. So with this, a new binlog implementation can be plugged in and do the same transaction coordinator role that the legacy binlog normally handles. In addition to this, there was a long discussion on the MariaDB mailing list about various replication topics, and I wrote up the following specifications (but did not start on the implementation yet): http://askmonty.org/worklog/?tid=107 http://askmonty.org/worklog/?tid=120 http://askmonty.org/worklog/?tid=133 The idea with these is to gradually make the existing legacy MySQL replication and binlog pluggable, opening the way for writing completely new replication implementations. It builds on top of the implementation work above. I also worked on the specifications of the parallel replication worklogs MWL#169 and MWL#170 mentioned below. > - The parallel replication that Oracle is talking about for MySQL 5.6 Do you mean this? http://forge.mysql.com/wiki/ReplicationFeatures/ParallelSlave http://forge.mysql.com/worklog/task.php?id=4648 The idea is to take one transaction at a time, and run the operations inside it in parallel. That is a bit unusual, most other projects work by running multiple transaction in parallel. I think this is motivated by NDB (though it is just me reading between the lines, I do not know for sure). Since in NDB, multiple parallel transactions on the master are replicated as a single, bigger transaction called an epoch. So they need something on the slave that can take apart the epoch again and make it parallel. Frankly, when I first saw this, I dismissed it, thinking it was the wrong approach to the problem (outside of NDB replication at least). I am still quite sceptical. Though it may be possible to use this for normal OLTP workloads (with small transactions) by artificially combining multiple transactions, at the cost of higher delay between master and slave. > - The replication functionality that <customer> was interested in together > with <partner> and us This is a request for implementing another algorithm for parallel replication. I wrote up some designs for this: http://askmonty.org/worklog/Server-RawIdeaBin/?tid=169 http://askmonty.org/worklog/Server-RawIdeaBin/?tid=170 This overlaps with the above-mentioned parallel replication project in Oracle, however the chosen approach/algorithm is quite different between the two. In this work, it would be the responsibility of the application to ensure that parallel transactions modifying tables in two different databases are independent. Then such transactions can be replicated in parallel on the slave. > Could you just give a short description on each of those and then describe > how they connect to each other or are there something that duplicates? Hope this helps, - Kristian.

1 0

Re: [Maria-developers] [Commits] Rev 2896: Fixed LP bug #707827. in file:///home/igor/maria/maria-5.3-bug707827/
by Oleksandr Byelkin 28 Jan '11

28 Jan '11

Hi! On 28.01.2011 07:23, Igor Babaev wrote: > At file:///home/igor/maria/maria-5.3-bug707827/ > > ------------------------------------------------------------ > revno: 2896 > revision-id: igor(a)askmonty.org-20110128052302-09617460yxj1kck7 > parent: igor(a)askmonty.org-20110127044523-9kvx1t3x3166hjyr > committer: Igor Babaev<igor(a)askmonty.org> > branch nick: maria-5.3-bug707827 > timestamp: Thu 2011-01-27 21:23:02 -0800 > message: > Fixed LP bug #707827. > This bug could manifest itself when hash join over a varchar column > with NULL values in some rows was used. It happened because the > function key_buf_cmp erroneously returned FALSE when one of the joined > key fields was null while the second was not. > Also fixed two other bugs in the functions key_hashnr and key_buf_cmp > that could possibly lead to wrong results for some queries that > used hash join over several columns with nulls. > Also reverted the latest addition of the test case for bug #45092. It > had been already backported earlier. It looks good to be pushed. Thank you for fixing it!

1 0

[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