Would you trust a more advanced MySQL optimizer?
Much has been made of certain limitations of MySQL’s query optimizer (“planner”). Subqueries, lack of sort-merge joins, and so on. This is not MySQL-bashing and no one should be offended. Some people have worked to make things better, and that code is in branches that were intended for future releases. But if that work were available right now, would you trust it?
This question is important because the optimizer is complex and full of compromises and black magic. Even minor changes occasionally have weird edge cases that cause a regression in some workload. Are major changes trustworthy?
I’ll give a specific example. In version 5.0, MySQL gained the ability to use more than one index for a query. This is called index_merge in EXPLAIN, and sometimes people think it’s the best thing ever. In practice, I can say two general things about queries that use an index_merge plan:
- If the optimizer chooses an index_merge, it is a fair guess that there are no good indexes for the query, and it’s making the best of a bad situation.
- The optimizer has no way to model the cost of an index_merge operation, and sometimes underestimates the cost so badly that even a full table scan can be faster. Such queries are often much faster when rewritten, for example, as a UNION. (This was the workaround in pre-5.0 anyway.)
As a result, queries that use index_merge can usually be flagged as “bad queries” without much further thought. Queries that really benefit from index_merge are relatively rare edge cases. A “more advanced” optimizer tactic turns out, by and large, to be a heuristic for when you need to rewrite your queries to a simpler form that MySQL 4.1 could optimize well.
I think this question cannot be answered, since you don’t know in which way the optimizer would be more complicated.
Suppose it would make an evaluation plan based on checking up on currently pending queries?
So, for example, it would prefer to avoid using tmp disk tables, based on knowing someone else is already utilizing the tmp directory.
It *could* make for better execution plans, by avoiding IO collisions.
(I’m just throwing an example here)
Making the best of a bad situation is not such a bad idea. How about implementing hash join, or sort merge join? Does that count as “a more advanced optimizer”?
By the same line of thought, we could say we don’t want improvements to InnoDB, since that would make the InnoDB engine more complicated.
It could be right, it could be wrong. Depends on what you’re doing.
My 2 cents
Shlomi Noach
22 Apr 10 at 3:00 am
Isn’t the question if the optimizer should be sort of pluggable, replaceable and if customers would give an alternative optimizer a try. For example, an optimizer with a with a more sophisticated cost model? It may be a naive question, I know virtually nothing about the MySQL optimizer, however, it may be worth a thought.
Ulf Wendel
22 Apr 10 at 5:02 am
My first reaction is “Millions of Oracle and Microsoft SQL Server users trust a more advanced optimizer.”
But seriously….Is any major feature change trusted? I would trust it — after I tested it.
The real question is: “How much do I depend on the way the optimizer works, in the way I write my queries?” Using a new optimizer *can* gum up the works, but usually that’s due to having made assumptions as to how the query optimizer works.
For example, if there’s a bug in the optimizer and the workaround is to use an index hint, most folks don’t go back and take out the index hint after upgrading (if appropriate….most people don’t even test to see if it’s appropriate).
So it’s less a question of trusting the *optimizer* and more on managing the expectations, assumptions and dependencies.
After all, I’ve “trusted” the Percona server changes, and the XtraDB changes, but of course only after testing.
Sheeri K. Cabral
22 Apr 10 at 4:40 pm
Re-reading this…I understand that using index_merge is a flag for you that a better index probably exists…but the optimization *does* help out queries that previously used a worse index, or no index at all.
I can see a complaint that it’s a sense of false positivity, but the way it’s written you’re making it sound like the index_merge optimization doesn’t help — it DOES help.
I agree that the REAL solution is to fix the query. But this would be a MUCH different world if everyone’s queries were optimal and schemas included appropriate indexes and an appropriate level of normalization/denormalization.
This is why Pythian is starting to implement query reviews using mk-query-digest in many of our customers — because the slow query log just isn’t good enough, and often gives you the symptoms, not the cause.
You need to look at ALL queries, to see where queries can be optimized, and not just say “it’s using an index or 2 so it’s optimal”.
Sheeri K. Cabral
22 Apr 10 at 5:04 pm
It sometimes takes me a while to formulate my thoughts and I need feedback to see what I’m trying to say. The issue I’m concerned about is that there is no way to disable changes to the optimizer (in most cases). From what I know, Oracle generally lets you disable whatever improvements they make, by adding configuration settings. index_merge isn’t disable-able, and as I said it is sometimes slower than a full table scan. That’s not an optimization (in those cases).
Postgres goes the other way: you can’t control anything, no hints, no nothing. And the reasoning is sound, mostly forward-looking.
I want future improvements to the MySQL optimizer to be configurable with hints or settings. I’m struggling to remember what, but I think some recent changes are configurable with variables.
Xaprb
22 Apr 10 at 7:09 pm
I already don’t trust Mysql’s optimizer. I don’t allow a complex query to be put in the codebase with out testing against simulated production data. Adding a new feature, would might just mean that I might be wrong with my first optimization guess.
William Newton
22 Apr 10 at 7:09 pm
@Xaprb
You can disable many query execution types dynamically (including within a sessions transaction) in PostgreSQL:
http://www.postgresql.org/docs/8.4/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE
What you can not do is easily force a plan, though if memory servers this is an extension to give hints.
Given how often and effectively it sounds like PG leans on bitmap index scans it sounds to me like the MySQL index merge implementation is far less than optimal.
I think you are thinking of the MySQL optimizer becoming configurable based what Sergey Petrunia has blogged. His blog is down at the moment, but there is documentation at http://dev.mysql.com/doc/refman/5.1/en/switchable-optimizations.html
Rob Wultsch
23 Apr 10 at 1:22 am
“What you can not do is easily force a plan, though if memory servers this is an extension to give hints.”
should read
“What you can not do is easily force a plan, though if memory servers there is an extension to give hints.”
The PG planner hint contrib moodule can be found at http://www.sai.msu.su/~megera/wiki/plantuner
Rob Wultsch
23 Apr 10 at 1:42 am
“index_merge isn’t disable-able, and as I said it is sometimes slower than a full table scan. That’s not an optimization (in those cases).”
Right, and that’s why it’s important to test and EXPLAIN queries. It’s an optimization *most* of the time, and that’s what the optimizer strives for — “most cases”. With index_merge and with everything else.
As we both know, there are bugs in the optimizer that mean that “sometimes” the optimizations actually aren’t the optimal way to do things.
The answer isn’t to be able to turn off index_merge; because that would be done on a per-server basis. The answer is to fix the crappy optimization….and by the way, are you sure that the problem is the *optimizer* and not something like InnoDB’s crappy statistics?
You basically asked “do I trust MySQL to be bug-free?” Of course not! That’s why you test. And Drizzle is taking the approach that “any feature can be turned on or off” with their plugins.
Sheeri K. Cabral
23 Apr 10 at 10:41 am
I’m aware that I’m getting in over my head here, but I think that for index_merge to work well, the optimizer needs a much more sophisticated cost model. It’s not really a bug per se, just an overly simplistic view of the world. “All models are wrong, some models are useful.” Statistics aren’t the problem; the optimizer assumes that buffering and merging the results found from multiple passes through indexes is free. In fact, broadly speaking, the optimizer assumes that *everything* is free except for a disk read, and that’s not true.
Xaprb
23 Apr 10 at 10:50 am
I think that the way you’ve phrased the question is akin to asking if we’d be down to swap out the little piglet that is the current optimizer with some other whole hog. There are a number of improvements that could be made incrementally such as including more information about how the optimizer will actually carry out it’s query plan (nested loop you say?), flattening subqueries where possible, and something akin to Postgres’s EXPLAIN ANALYZE. Two of those three examples I just listed support the fact that I’d be a lot more likely to “trust” a “smarter” optimizer if I had a better view into what it actually does rather than replacing the current black box (when compared to other database planners out there) with another, bigger & fancier black box. You’re right about the index_merge “feature” actually being a bad idea in most cases; but the biggest problem isn’t that it’s there, it’s that we aren’t given enough information by EXPLAIN to know that without just running the query to see what happens.
I’ve spent the last 3.5 years banging my head against MySQL servers. I’ve got a lot more respect for it now than I did at the beginning of that stretch (largely due to the work you guys @ Percona have put into it and Oracle’s “let’s fix & improve what we have and slow down with the tacking on more new features onto existing broken features) but I still consider MySQL’s optimizer to be one of the server’s biggest weaknesses when viewed in the context of the greater RDBMS ecosystem so thanks for bringing this up and giving me the chance to chime in.
Erik Jones
1 Dec 11 at 11:47 pm
I think my opinions in April 2010 were influenced a lot by what was going on in the larger MySQL world at that time, and the initially buggy 5.1 releases in 2008-2009. I feel a lot better about the improvements Oracle is putting into MySQL 5.6 (and 5.5) now than I would have a couple of years ago.
Xaprb
2 Dec 11 at 10:11 am
@Xaprb:
You said “it is a fair guess that there are no good indexes for the query, and it’s making the best of a bad situation.”
What you say regarding index merge might be true if you are running say, a few types of queries that all could use a few indices to accelerate lookups. However, you’re leaving out the ad-hoc queries. In that case you’d need a ridiculous amount of composite queries (a subset of n!). So saying “your indexes are wrong” is not quite fair.
The cost model may be screwy, and I’m not sure how exactly an index merge in MySQL works, but I’d guess it builds N bitmap indices as it goes through each index, then does some cheap bit arithmetic to compute the final answer. If the table type is fixed length in MyISAM that’s some basic arithmetic. That could be pretty fast, and it’s why I wish MySQL had bitmap and bitmap join indices :)
But yeah, if you’re asking a database something ad hoc based on a subset of any or all 10 criteria, you couldn’t possibly catch all the cases with composite indices. In some cases, 1 good almost unique index (what b-trees do best) + a table scan would be great — but in some other cases an index merge would be much much better, esp. if you had a few indexes with so-so cardinality. Of course using bitmap indices itself might be better in that case.
Of course if the cost model is screwy, it will make the wrong decisions. I agree.
Am I missing something? Is this how index merge works? It’s the only way I’ve seen index merges implemented in the wild.
I’ve learned a lot more from your blog than you could learn from me, so if the above is wrong, please feel free to correct me :)
Jaimie Sirovich
30 Dec 11 at 1:26 am
The union and intersection operations are usually not expensive in my experience, although sometimes they can be. The problem sometimes comes afterwards, where we get a lot of random I/O to look up the rows that were selected from the indexes.
MySQL 5.6 is making important improvements in this area too.
Xaprb
23 Feb 12 at 9:33 am