- developers - lists.mariadb.org

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

03 Feb '11

> -----Original Message----- > From: kahzoo(a)gmail.com [mailto:kahzoo@gmail.com] On Behalf Of Kazuhisa Ichikawa > Sent: Donnerstag, 3. Februar 2011 02:00 > To: Vladislav Vaintroub > Cc: maria-developers(a)lists.launchpad.net > Subject: Re: [Maria-developers] Windows installer MWL#55 finished. > Hello Kazuhisa, > > But to reiterate, this package is not really thought to be run by non-admin users. It installs into program files, and it writes to > > the HKLM registry hive, and it creates a service. All this stuff is not possible without being an administrator. I'm quite surprised > > Well, if that is how it is designed, that's fine with me. I just > thought that there might be many users who are new to MariaDB, not > sure about its quality and want to install/run it in a sandbox > environment. If switching to HKCU registry hive and disabling the > option to run it as a service when the installing user is non-admin > can satisfy the needs, implementing them might not be a bad idea. But > again, I do not have a strong opinion on that. (The user can choose to > install from the zipped package as long as zip version is also > distributed). I this the per-user install is a feature in NSIS which you discovered, but which is was not planned from the start on. From the start on, it was planned as an installation where you get a running service at the end, and that was not implemented in NSIS. if it was, it would not work in this sandboxed environment. With MSI I checked, and the rules for per-user install appear to be relatively complicated. Officially dual package authoring (single package that can be installed either per-user or per-machine) is supported with MSI 5.0 and Win7, so there is no hope to get that working in the same for unprivileged XP user. I might do some experimentation with it on Win7, however the benefit it provide compared to ZIP is low: is a menu shortcut with PATH including bin directory of the installation and Add/Remove Program entry. On a bright side, this is a valid bug that was simple to fix forcing per-machine install, so installer does not need to guess. I did that and refreshed the MSI on the download page. > One more thing that I noticed is, when installing 5.1 all the messages > in the dialogs are in Japanese (with the exception of GPL license > statement), while they are all in English for 5.2. Maybe this is a > bonus that came from NSIS. Maybe. NSIS guys deserve a compliment for that effort. With MSI dealing with multiple languages inside single MSI is not that simple. But, localization is definitely possible in Wix or generally MSI, I so far ignored that topic altogether;) But back to NSIS package .I believe, just like with per-user installation, you discovered hidden and untested feature, an Easter egg. It has more localization bugs that just GPL text. The feature tree is not localized, neither uninstall popup box. If that NSIS package had more functionality than plain unpack, and had own dialogs, there would be more localization bugs.

1 0

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

02 Feb '11

> -----Original Message----- > From: kahzoo(a)gmail.com [mailto:kahzoo@gmail.com] On Behalf Of Kazuhisa Ichikawa > Sent: Mittwoch, 2. Februar 2011 23:01 > To: Vladislav Vaintroub > Cc: maria-developers(a)lists.launchpad.net > Subject: Re: [Maria-developers] Windows installer MWL#55 finished. > > Vladislav, > > I tried the new 5.2.5 installer in Japanese Windows XP (32bit) > environment using a user account which does NOT have administrator > privilege. I wonder why it has started at all. It definitely needs administrator and installer on later Windows brings all the necessary UAC popups. > During the installation I encountered a lot of warning > dialogs (see below for an example) about not being able to write into > the registry, although I did not have any noticeable problem startnig > mysqld.exe after the installation is finished (No, the installation > was not aborted and it said "Completed the MariaDB 5.2 Setup WIzard.") > > (Example warning message) > "Could not write value InstallLocation to key > \Software\Microsoft\Windows\CurrenVersion\Uninstall\MariaDB 5.2. > Verify that you have sufficient access to that key, or contact your > support personnel." If you do not have that key, you do not have an entry in "Add/remove programs" > I think what this error message means is that the user account used > for the installation needs to have an administrator privilege, but the > current (older) installer for MariaDB 5.1.53 did not have this > problem. This installer should be run as administrator, there is no per-user option. > The other (related) issue is that I cannot uninstall mariadb 5.2 now > because it is not listed in the installed program list in the control > panel (most likely because the necessary information is not written > into the registry). You will need to run msiexec /x mariadb-5.2.5-win32.msi from the command line. This uninstalls installed package (I'm not really sure it was installed, but it does not hurt to try, it nothing is installed it will tell) > Again, the installer for 5.1 did not have this problem. Well, it is not installer for 5.1, the old one had more or less the same functionality as downloading and unpacking a zip file. It might even be that NSIS guys automatically switched to HKCU registry keys or so for a weak user. > Also, unlike the current installer for 5.1, the new installer > does not seem to install a standalone uninstaller (Uninstall.exe) on > the disk. So, I'm stuck and wondering how to uninstall 5.2 (Well, not > really, because if nothing is written into the registry, deleting all > the files and directories should 'cleanly' uninstall everything, > right?.). I would stick to documented stuff. msiexec /x mariadb-5.2.5-win32.msi But to reiterate, this package is not really thought to be run by non-admin users. It installs into program files, and it writes to the HKLM registry hive, and it creates a service. All this stuff is not possible without being an administrator. I'm quite surprised that MSI let you go that far actually, and that it would not rollback and cleanup after the error. On the other hand, thanks for an interesting error report, makes sense for me to have a test for anomalies like that. Can you tell service pack of your XP and MSI version (launch winver and msiexec /?) Thanks, Vladislav > - Kazuhisa >

1 0

[Maria-developers] Benchmarking mrr_sort_keys=on|off.
by Sergey Petrunya 02 Feb '11

02 Feb '11

Hi! Following the discussion of benchmarks of mrr_sort_keys=on|off functionality, I've done a re-run. Please find the results below. ## Dataset: DBT-3, scale=10, xtradb, ## innodb_file_per_table=1 ## innodb_file_format=barracuda ## innodb_log_file_size=256M ## innodb_buffer_pool_size=4000M ## ## Platform: work.askmonty.org host ## Cold-cache is achieved by: ## - stopping the server ## - echo 1 > /proc/sys/vm/drop_caches ## - starting the server ## - running EXPLAIN {query} ## ## MariaDB: 5.3-current, release build (compile-pentium-max) ## Result summary: * mrr_sort_keys=on provides speedup (2.25 min vs 13.5 min) on cold cache. * on hot cache, there is no big difference (~1.3 sec in both cases). * I was able to repeat Igor's August results with the current 5.3. It is not clear why Igor can't repeat them anymore. (An evasive bug? or just mistake in experiments?) set join_buffer_size=1024*1024*32; set join_buffer_space_limit=1024*1024*32; set join_cache_level=6; ## ## Cold cache, mrr_sort_keys=on: ## [22:12] root@dbt3sf10>explain select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31'; +----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+------------------------------------+ | 1 | SIMPLE | orders | range | PRIMARY,i_o_orderdate | i_o_orderdate | 4 | NULL | 142680 | Using where; Using index | | 1 | SIMPLE | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4 | dbt3sf10.orders.o_orderkey | 1 | Using join buffer (flat, BKA join) | +----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+------------------------------------+ 2 rows in set (3.04 sec) [22:06] root@dbt3sf10>select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31'; +----------------------+ | avg(l_extendedprice) | +----------------------+ | 38229.0683265894 | +----------------------+ 1 row in set (2 min 13.40 sec) [22:09] root@dbt3sf10> show status like "%handler%"; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 2 | | Handler_read_key | 193396 | | Handler_read_next | 966535 | +----------------------------+--------+ 15 rows in set (0.29 sec) # # mrr_sort_keys=off, cold cache: # [22:20] root@dbt3sf10>explain select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31'; +----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+--------------------------+ | 1 | SIMPLE | orders | range | PRIMARY,i_o_orderdate | i_o_orderdate | 4 | NULL | 142680 | Using where; Using index | | 1 | SIMPLE | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4 | dbt3sf10.orders.o_orderkey | 2 | | +----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+--------------------------+ [22:20] root@dbt3sf10>select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31'; +----------------------+ | avg(l_extendedprice) | +----------------------+ | 38229.0683265898 | +----------------------+ 1 row in set (13 min 23.68 sec) [22:09] root@dbt3sf10> show status like "%handler%"; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 2 | | Handler_read_key | 193396 | | Handler_read_next | 966535 | +----------------------------+--------+ ## ## Hot caches (re-run the query until time stabilizes): ## mrr_sort_keys=off: 1.34 sec mrr_sort_keys=on: 1.38 sec BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

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

02 Feb '11

From: Peter Laursen [mailto:peter_laursen@webyog.com] Sent: Mittwoch, 2. Februar 2011 17:14 To: Vladislav Vaintroub Cc: maria-developers(a)lists.launchpad.net Subject: Re: [Maria-developers] Windows installer MWL#55 finished. Hi Peter, >Your default install path is \program files [(x86)]. You copy the /datadir as a subfolder of /basedirr. >This will cause problems on Vista+ with UAC enabled if the server is ever started from commandline as a non-service (also if >the user doing so is an administrator user). For the user who installed MySQL, it won't cause problems. After you stop the service you can start mysqld --defaults-file=path\to\datadir\my.ini --console from the non-elevated command line, with/without UAC. No file system virtualization will happen. That bug http://bugs.mysql.com/bug.php?id=38373 (user can not from mysqld from command line after installation) was filed by myself 2.5 years ago , I could not leave that unfixed now. I know, writing to ProgramFiles might not be kosher, but it works if ACLs on our tree are set. (And Microsoft should not tell people that writing to program files is bad, they are doing it with SQLServer, and also document it http://msdn.microsoft.com/en-us/library/ms143547.aspx , so we are again as good/bad as SQLServer here) >2) There is no my.ini generated with a fresh install as far as I can see - so the server probably runs (it does!) with very low default buffer settings etc. Also latin1 charset and MyISAM will be default. I think a my.ini with settings fit for a 1GB-2GB RAM machine should be generated - and with a few configuration options. And a my.ini will be required to solve 1) anyway. There is a my.ini, it is in database directory. It is spartanic, it has port and datadir, nothing else. You can edit it using non-elevated notepad (with or without UAC) and there is a shortcut for it created in start menu, MariaDB 5.2 folder (rsp MariaDB 5.2 (x64) if for 64 bit package). There are zero performance related options, which means in fact it won't run on full speed. I have no strong opinion on it, nor I feel qualified for configuring "optimal performance out of the box" . If there is a strong demand on specific parameters, they should probably go there. I would like to hear more opinions on this, one thing that will definitely not be in the first incarnation of this installer is fully-featured ConfigWizard like stuff. Where I see differences between this my.ini and MySQL installer's default is * sql_mode (MySQL's default is "strict transactional tables") * default storage engine (MySQL default is innodb) * innodb buffer pool size (IIRC, MySQL's default is something like 12% of RAM) Those are trivial to change modifying my.ini Stuff like innodb log file size (calculated from innodb buffer pool size) not trivial to change with simple my.ini modification, there must be a clean shutdown and exiting files need to be removed. > But still it is a good installer (and there is a 64 bit one as well), so this is an important step in the right direction. So thanks for your effort! Thanks! -- Peter On Wed, Feb 2, 2011 at 14:37, Peter Laursen <peter_laursen(a)webyog.com> wrote: I am a little bit busy now, but will confirm tonight. -- Peter On Wed, Feb 2, 2011 at 14:12, Vladislav Vaintroub <wlad(a)montyprogram.com> wrote: From: Peter Laursen [mailto:peter_laursen@webyog.com] Sent: Dienstag, 1. Februar 2011 20:31 To: Vladislav Vaintroub Cc: maria-developers(a)lists.launchpad.net Subject: Re: [Maria-developers] Windows instller MWL#55 finished. >It is a Danish Windows (Home Premium 64 bit). Hi Peter, As it turns out, installer had the same "localization" problem registering service on German Windows. Fixed now, I uploaded the msis to the same place http://cid-ff0c950417b4f8a4.office.live.com/browse.aspx/share Thanks, Wlad

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] IMPORTANT: LP bug #611648 does not allow running benchmarks for DS-MRR normally
by Igor Babaev 02 Feb '11

02 Feb '11

Hi, I've just reported bug #611648 that probably explains the strange results I had when trying to run my benchmark queries for DS-MRR about which I talked at today's optimizer meeting. Philip, please assign this bug to SergeyP. Regards, Igor.

2 1

[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 27 Jan '11

27 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