MariaDB 11.0 Brings a New Optimizer Cost Model and More

Ten years after the last release, MariaDB Server 11.0 has been released. This version has several changes, including a new optimizer cost model that aims to predict more accurately the actual cost of each query execution plan and the removal of the InnoDB change buffer.

According to the MariaDB foundation, most optimizer problems, such as reporting performance issues about bad query plans, had been informed by the community users.

The flagship feature of MariaDB 11.0 is the new optimizer cost model, which is aimed at being able to predict more accurately the cost of each query execution plan. However, there is no guarantee that it will be better in all scenarios; some queries can be even slower. The idea is to increase the MariaDB major version as a signal.

Before MariaDB 11.0, the MariaDB query optimizer used a "basic cost" of 1 for one disk access, fetching a key, fetching a row based on the rowid from the key, besides other small costs. Those costs are reasonable when finding the best index to use, but not so good for a table scan, an index scan, or a range lookup.

MariaDB 11.0 changed the basic cost for "storage engine operations" to be one millisecond, which means that for most queries, the cost should be close to the time the server spends in the storage engine + join_cache + sorting.

The engine costs have been separated into small pieces to improve accuracy. The disk read costs now assume by default an SSD disk with 400/second, which can be changed by modifying the OPTIMIZER_DISK_READ_COST.

All engine-specific costs can be found in the information_schema.optimizer_costs. To see the default costs for an engine, the following query is available:

select * from information_schema.optimizer_costs where engine="default"\G

And the results will be:

*************************** 1. row ***************************
                         ENGINE: default
       OPTIMIZER_DISK_READ_COST: 10.240000
OPTIMIZER_INDEX_BLOCK_COPY_COST: 0.035600
     OPTIMIZER_KEY_COMPARE_COST: 0.011361
        OPTIMIZER_KEY_COPY_COST: 0.015685
      OPTIMIZER_KEY_LOOKUP_COST: 0.435777
   OPTIMIZER_KEY_NEXT_FIND_COST: 0.082347
      OPTIMIZER_DISK_READ_RATIO: 0.020000
        OPTIMIZER_ROW_COPY_COST: 0.060866
      OPTIMIZER_ROW_LOOKUP_COST: 0.130839
   OPTIMIZER_ROW_NEXT_FIND_COST: 0.045916
   OPTIMIZER_ROWID_COMPARE_COST: 0.002653
      OPTIMIZER_ROWID_COPY_COST: 0.002653

To change the default costs, it is only required to specify the cost as an argument, such as set global optimizer_disk_read_cost=20.

To check the default costs for InnoDB storage engine, just run

select * from information_schema.optimizer_costs where engine="innodb"\G

Following the results:

*************************** 1. row ***************************
                         ENGINE: InnoDB
       OPTIMIZER_DISK_READ_COST: 10.240000
OPTIMIZER_INDEX_BLOCK_COPY_COST: 0.035600
     OPTIMIZER_KEY_COMPARE_COST: 0.011361
        OPTIMIZER_KEY_COPY_COST: 0.015685
      OPTIMIZER_KEY_LOOKUP_COST: 0.791120
   OPTIMIZER_KEY_NEXT_FIND_COST: 0.099000
      OPTIMIZER_DISK_READ_RATIO: 0.020000
        OPTIMIZER_ROW_COPY_COST: 0.060870
      OPTIMIZER_ROW_LOOKUP_COST: 0.765970
   OPTIMIZER_ROW_NEXT_FIND_COST: 0.070130
   OPTIMIZER_ROWID_COMPARE_COST: 0.002653
      OPTIMIZER_ROWID_COPY_COST: 0.002653

Let's take a look at the meaning of the different cost variables.

More information on the costs and how they were calculated can be found in the Docs/optimizer_costs.txt file in the MariaDB Source distributions.

Let's have a look at other MariaDB optimizer cost changes:

• When counting disk accesses, it is assumed that all rows and index data are cached for the duration of the query.

• The cost of sorting (filesort) is now more accurate, allowing the optimizer to better choose between an index scan and a filesort for ORDER BY/GROUP BY queries.

The new optimizer changes usually matter, and probably should be able to find a better plan in the following scenarios:

• When running queries with more than two tables
• Indexes with a lot of identical values
• Ranges that cover more than 10% of a table (WHERE key between 1 and 1000 -- Table has values 1-2000)
• Complex queries when not all used columns are or can be indexed
• Queries mixing different storage engines, like using tables from both InnoDB and Memory in the same query
• When needing to use FORCE INDEX to get a good plan
• If using ANALYZE TABLE made the plans worse (or not good enough)
• Queries that have lots of derived tables (subselects)
• Using ORDER BY / GROUP BY that could be resolved via indexes

More details are available on how to upgrade between Major MariaDB versions, as well as how to upgrade from Maria DB 10.11 to 11.0.