Comments - Query optimization
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
Hello. We tested the MariaDB 11.1.2 aswell of course, but it got few things right and many things wrong. MariaDB 11 is very good at many simple queries, even better than previous versions, but it is i dare to say aboslutely terrible at some more complex queries - worse than earlier MariaDB versions. MyIsam and Aria seem to be somewhat ok, but the performance/query plans are worse with InnoDB, compared ot older versions. Of course we would prefer to stay with a single database, MariaDB to be precise, but if things are not going to change, we will be forced to use anything else for some of our databases - where both PostgreSQL or MySQL seem to have what we need, but we are also willing to go with SQL server or other paid options - We need things to work, the cost is secondary, and MariaDB cannot perform even if it got a whole server reserved for a single SQL statement. As bad query plans take multiple times of execution time, it doesn't matter how much more optimized MariaDB is - as long as it cannot use the correct plan, it simply becomes the worst database. Personally i think the changes to optimizer in MariaDB in the last 5 years have been a waste of time for professional environment, while Oracle got things already figured out and PostgreSQL got community large enought to plug the holes with Oracle-style optimizer hints.
I spent a lot of time to 11.0 to fix all known optimizer things. I have verified that the new optimizer with all changed plans in the mtr test suite and also with several customer queries that caused problems in earlier MariaDB versions and in almost all cases 11.0 did give a better plan.
That said, i do not think that 11.0 is perfect yet. It just a better base for the optimizer than any earlier MariaDB versions. Thanks to the changes and cleanups, it is much easier to extend and improve things in the optimizer in 11.0.
The main problem is 11.0 has had very little testing from high level users and because of that I have not got any feedback about the 11.0 optimizer performance.
We are working on extending maxscale to be able to simultaneous replicate to multiple servers, like 10.11 and 11.0 and compare result and timings. This is done partly to allow high level users to test 11.0 and provide feedback from running 11.0 in production. This will help us to find and fix any issues in 11.0.
If you, or anyone else are experiencing problems with the optimizer in 11.0 or above, please provide a test case in a Jira task and we will do our best to fix the issue ASAP.
Can you give specific examples of some of the complex queries you refer to (table structures/queries/datasets), so that we can repeat the problem and try and see what is going wrong?