MariaDB EXPLAIN same query - different results on different servers

17 April,2020 by Jack Vamvas

Same data , same queries , different response time. 

Server 1 is MariaDB version 10.2.6  using the innodb version 5.7.14

Server 2 is MariaDB version 10.3.17 a using the innodb version 10.3.17

There is  a VIEW and within the VIEW there is a UNION covering two SELECT statements . The underlying dataset is  greater than 100 million rows.Note: Dataset is refreshed twice a year - 

Since the migration to Server 2 - the index from the table 1 is not used  , causing a significant drop is response time for certain queries. The Execution Plan is now forcing a Table Scan rather than the previous ref type. 

Clearly there has been a significant change in the Optimizer impacting DERIVED TABLES.

Troubleshooting included:

1) OPTIMIZE TABLE 

2) ANALYZE TABLE

3)  'SET max_seeks_for_key=100'

4)  Tested replacing the optimizer_switch values on Server 2 from the exact values on Server 1. 

5)  By default the innodb_stats_sample_pages are set at 8. Often , this can be insufficient to get a wide enough sample of data to optimize the statistics

SHOW VARIABLES LIKE 'innodb_stats_sample_pages%'

I changed it to 200 - and ran OPTIMIZE TABLE

 

The final solution\workaround was to materialize the data set into a BASE TABLE and create the same indexes over the data. The queries are now running as expected.

As the data set is only refreshed twice a year - it's just an extra couple of steps to place the values into a BASE TABLE. The data is used as read only  - so this was a fairly straightforward procedure .   i.e create a one-off INSERT into the base table , of the VIEW output. 

If the data need to be edited - this would require some extra customisations

MariaDB doesn't have a native materialization method , such as other DBMS. For example SQL Server has the concept of materialized views -  Use SQL Materialized view to eliminate DISTINCT and speed up response time   - SQL Server supports creating indexes on a VIEW .

 

 


Author: Jack Vamvas (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on MariaDB EXPLAIN same query - different results on different servers


dba-ninja.com