17 April,2020 by Rambler
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 .
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |