[MDEV-20646] 10.3.18 is slower than 10.3.17 Created: 2019-09-23  Updated: 2019-12-06  Resolved: 2019-11-13

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3.18
Fix Version/s: 10.3.21

Type: Bug Priority: Critical
Reporter: JaviN Assignee: Sergei Petrunia
Resolution: Fixed Votes: 3
Labels: performance, regression
Environment:

Centos 7

Attachments: File bomb-query.sql    
Issue Links:
Relates
relates to MDEV-21200 About 10x less insert performance wit... Closed
relates to MDEV-20109 Optimizer ignores distinct key create... Closed

 Description   

Hi,

I work in a hosting company; we mainly offer hosting for Prestashop sites.

We were using 10.3.17 and last week I updated to 10.3.18.

After the update, we experienced a slowdown in several Prestashop sites in different servers, I suspected the problem appeared with the MariaDB upgrade, so I downgraded to 10.3.17 and the problem was gone.

I have been debugging in a test server and I can confirm that one query generated by Prestashop was executing in 0m0.055s in 10.3.17 and in 1m16.021s in 10.3.18.

Here are my tests:

[root@server ~]# mysql -V
 
mysql  Ver 15.1 Distrib 10.3.17-MariaDB, for Linux (x86_64) using readline 5.1
 
 
 
[root@server ~]# mysql 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 32
 
Server version: 10.3.17-MariaDB-log MariaDB Server
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> SHOW VARIABLES LIKE "%version%";
 
+---------------------------------+------------------------------------------+
 
| Variable_name                   | Value                                    |
 
+---------------------------------+------------------------------------------+
 
| innodb_version                  | 10.3.17                                  |
 
| protocol_version                | 10                                       |
 
| slave_type_conversions          |                                          |
 
| system_versioning_alter_history | ERROR                                    |
 
| system_versioning_asof          | DEFAULT                                  |
 
| version                         | 10.3.17-MariaDB-log                      |
 
| version_comment                 | MariaDB Server                           |
 
| version_compile_machine         | x86_64                                   |
 
| version_compile_os              | Linux                                    |
 
| version_malloc_library          | system                                   |
 
| version_source_revision         | 4b5a14d0fe3d6945a0a9516261f563dcfd1f2c9c |
 
| version_ssl_library             | OpenSSL 1.0.2k-fips  26 Jan 2017         |
 
| wsrep_patch_version             | wsrep_25.24                              |
 
+---------------------------------+------------------------------------------+
 
13 rows in set (0.001 sec)
 
 
 
MariaDB [(none)]> quit
 
Bye
 
 
 
[root@server ~]# time mysql prestashop_1 <bomb-query.sql
 
id_product      id_supplier     id_manufacturer id_category_default     id_shop_default id_tax_rules_group      on_sale online_only     ean13   isbn    upc     ecotax  quantity        minimal_quantity        low_stock_threshold     low_stock_alert      price   wholesale_price unity   unit_price_ratio        additional_shipping_cost        reference       supplier_reference      location        width   height  depth   weight  out_of_stock    additional_delivery_times   quantity_discount        customizable    uploadable_files        text_fields     active  redirect_type   id_type_redirected      available_for_order     available_date  show_condition  condition       show_price      indexed visibility  cache_is_pack    cache_has_attachments   is_virtual      cache_default_attribute date_add        date_upd        advanced_stock_management       pack_stock_type state   id_product      id_shop id_category_default     id_tax_rules_group  on_sale  online_only     ecotax  minimal_quantity        low_stock_threshold     low_stock_alert price   wholesale_price unity   unit_price_ratio        additional_shipping_cost        customizable    uploadable_files        text_fields active   redirect_type   id_type_redirected      available_for_order     available_date  show_condition  condition       show_price      indexed visibility      cache_default_attribute advanced_stock_management       date_add        date_upd     pack_stock_type out_of_stock    quantity        description     description_short       available_now   available_later id_product_attribute    link_rewrite    meta_description        meta_keywords   meta_title      name    id_image     legend  manufacturer_name       new
 
1864    0       5       22      1       1       1       0                               0.000000        0       1       NULL    0       88906.006116    0.000000                0.000000        0.00    MBC300X140                      0.000000     0.000000        0.000000        0.000000        2       1       0       0       0       0       1               0       1       0000-00-00      0       new     1       1       both    0       0       0       0       2019-09-03 10:27:50  2019-09-03 10:27:50     0       3       1       1864    1       22      1       1       0       0.000000        1       NULL    0       88906.006116    0.000000                0.000000        0.00    0       0       0       1   01       0000-00-00      0       new     1       1       both    0       0       2019-09-03 10:27:50     2019-09-03 10:27:50     3       0       150             KwEI=0,"Kg/hvp=0,"Kcal/hG=0<br />3000x1400xh900+900,                    0   xxxxxxxx-piano-chef                 xxxxxxxx "PIANO CHEF"      xxxxxxxx "PIANO CHEF"      1881            DIAMOND 1
 
15133   0       5       22      1       1       1       0                               0.000000        0       1       NULL    0       88906.006116    0.000000                0.000000        0.00    MBC300X140                      0.000000     0.000000        0.000000        0.000000        2       1       0       0       0       0       1               0       1       0000-00-00      0       new     1       1       both    0       0       0       0       2019-09-10 09:45:29  2019-09-10 09:45:29     0       3       1       15133   1       22      1       1       0       0.000000        1       NULL    0       88906.006116    0.000000                0.000000        0.00    0       0       0       1   01       0000-00-00      0       new     1       1       both    0       0       2019-09-10 09:45:29     2019-09-10 09:45:29     3       0       150             KwEI=0,"Kg/hvp=0,"Kcal/hG=0<br />3000x1400xh900+900,                    0   xxxxxxxx-piano-chef                 xxxxxxxx "PIANO CHEF"      xxxxxxxx "PIANO CHEF"      15289           DIAMOND 1
 
15132   0       5       22      1       1       1       0                               0.000000        0       1       NULL    0       69832.213140    0.000000                0.000000        0.00    MBC280X180                      0.000000     0.000000        0.000000        0.000000        2       1       0       0       0       0       1               0       1       0000-00-00      0       new     1       1       both    0       0       0       0       2019-09-10 09:45:29  2019-09-10 09:45:29     0       3       1       15132   1       22      1       1       0       0.000000        1       NULL    0       69832.213140    0.000000                0.000000        0.00    0       0       0       1   01       0000-00-00      0       new     1       1       both    0       0       2019-09-10 09:45:29     2019-09-10 09:45:29     3       0       150             KwEI=0,"Kg/hvp=0,"Kcal/hG=0<br />2800x1800xh900,                        0   xxxxxxxx-piano-chef                 xxxxxxxx "PIANO CHEF"      xxxxxxxx "PIANO CHEF"      15288           DIAMOND 1
 
1863    0       5       22      1       1       1       0                               0.000000        0       1       NULL    0       69832.213140    0.000000                0.000000        0.00    MBC280X180                      0.000000     0.000000        0.000000        0.000000        2       1       0       0       0       0       1               0       1       0000-00-00      0       new     1       1       both    0       0       0       0       2019-09-03 10:27:50  2019-09-03 10:27:50     0       3       1       1863    1       22      1       1       0       0.000000        1       NULL    0       69832.213140    0.000000                0.000000        0.00    0       0       0       1   01       0000-00-00      0       new     1       1       both    0       0       2019-09-03 10:27:50     2019-09-03 10:27:50     3       0       149             KwEI=0,"Kg/hvp=0,"Kcal/hG=0<br />2800x1800xh900,                        0   xxxxxxxx-piano-chef                 xxxxxxxx "PIANO CHEF"      xxxxxxxx "PIANO CHEF"      1880            DIAMOND 1
 
 
 
real    0m0.055s
 
user    0m0.006s
 
sys     0m0.004s
 
 
 
[root@server ~]# yum upgrade MariaDB*
 
Complementos cargados:fastestmirror
 
Loading mirror speeds from cached hostfile
 
 * atomic: www4.atomicorp.com
 
 * base: repo.nixval.com
 
 * epel: fedora.cu.be
 
 * extras: repo.nixval.com
 
 * updates: repo.nixval.com
 
Resolviendo dependencias
 
--> Ejecutando prueba de transacción
 
---> Paquete MariaDB-client.x86_64 0:10.3.17-1.el7.centos debe ser actualizado
 
---> Paquete MariaDB-client.x86_64 0:10.3.18-1.el7.centos debe ser una actualización
 
---> Paquete MariaDB-common.x86_64 0:10.3.17-1.el7.centos debe ser actualizado
 
---> Paquete MariaDB-common.x86_64 0:10.3.18-1.el7.centos debe ser una actualización
 
---> Paquete MariaDB-compat.x86_64 0:10.3.17-1.el7.centos debe ser actualizado
 
---> Paquete MariaDB-compat.x86_64 0:10.3.18-1.el7.centos debe ser una actualización
 
---> Paquete MariaDB-devel.x86_64 0:10.3.17-1.el7.centos debe ser actualizado
 
---> Paquete MariaDB-devel.x86_64 0:10.3.18-1.el7.centos debe ser una actualización
 
---> Paquete MariaDB-server.x86_64 0:10.3.17-1.el7.centos debe ser actualizado
 
---> Paquete MariaDB-server.x86_64 0:10.3.18-1.el7.centos debe ser una actualización
 
---> Paquete MariaDB-shared.x86_64 0:10.3.17-1.el7.centos debe ser actualizado
 
---> Paquete MariaDB-shared.x86_64 0:10.3.18-1.el7.centos debe ser una actualización
 
--> Resolución de dependencias finalizada
 
 
 
Dependencias resueltas
 
 
 
=============================================================================================================================================================================================================================================
 
 Package                                                    Arquitectura                                       Versión                                                             Repositorio                                         Tamaño
 
=============================================================================================================================================================================================================================================
 
Actualizando:
 
 MariaDB-client                                             x86_64                                             10.3.18-1.el7.centos                                                mariadb                                              11 M
 
 MariaDB-common                                             x86_64                                             10.3.18-1.el7.centos                                                mariadb                                              81 k
 
 MariaDB-compat                                             x86_64                                             10.3.18-1.el7.centos                                                mariadb                                             2.8 M
 
 MariaDB-devel                                              x86_64                                             10.3.18-1.el7.centos                                                mariadb                                             7.1 M
 
 MariaDB-server                                             x86_64                                             10.3.18-1.el7.centos                                                mariadb                                              24 M
 
 MariaDB-shared                                             x86_64                                             10.3.18-1.el7.centos                                                mariadb                                             112 k
 
 
 
Resumen de la transacción
 
=============================================================================================================================================================================================================================================
 
Actualizar  6 Paquetes
 
 
 
Tamaño total de la descarga: 46 M
 
Is this ok [y/d/N]: y
 
Downloading packages:
 
Delta RPMs disabled because /usr/bin/applydeltarpm not installed.
 
(1/6): MariaDB-common-10.3.18-1.el7.centos.x86_64.rpm                                                                                                                                                                 |  81 kB  00:00:00
 
(2/6): MariaDB-compat-10.3.18-1.el7.centos.x86_64.rpm                                                                                                                                                                 | 2.8 MB  00:00:03
 
(3/6): MariaDB-client-10.3.18-1.el7.centos.x86_64.rpm                                                                                                                                                                 |  11 MB  00:00:05
 
(4/6): MariaDB-devel-10.3.18-1.el7.centos.x86_64.rpm                                                                                                                                                                  | 7.1 MB  00:00:02
 
(5/6): MariaDB-shared-10.3.18-1.el7.centos.x86_64.rpm                                                                                                                                                                 | 112 kB  00:00:00
 
(6/6): MariaDB-server-10.3.18-1.el7.centos.x86_64.rpm                                                                                                                                                                 |  24 MB  00:00:01
 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Total                                                                                                                                                                                                        6.5 MB/s |  46 MB  00:00:07
 
Running transaction check
 
Running transaction test
 
Transaction test succeeded
 
Running transaction
 
  Actualizando  : MariaDB-compat-10.3.18-1.el7.centos.x86_64                                                                                                                                                                            1/12
 
  Actualizando  : MariaDB-common-10.3.18-1.el7.centos.x86_64                                                                                                                                                                            2/12
 
  Actualizando  : MariaDB-client-10.3.18-1.el7.centos.x86_64                                                                                                                                                                            3/12
 
  Actualizando  : MariaDB-server-10.3.18-1.el7.centos.x86_64                                                                                                                                                                            4/12
 
  Actualizando  : MariaDB-devel-10.3.18-1.el7.centos.x86_64                                                                                                                                                                             5/12
 
  Actualizando  : MariaDB-shared-10.3.18-1.el7.centos.x86_64                                                                                                                                                                            6/12
 
  Limpieza      : MariaDB-server-10.3.17-1.el7.centos.x86_64                                                                                                                                                                            7/12
 
  Limpieza      : MariaDB-client-10.3.17-1.el7.centos.x86_64                                                                                                                                                                            8/12
 
  Limpieza      : MariaDB-shared-10.3.17-1.el7.centos.x86_64                                                                                                                                                                            9/12
 
  Limpieza      : MariaDB-devel-10.3.17-1.el7.centos.x86_64                                                                                                                                                                            10/12
 
  Limpieza      : MariaDB-compat-10.3.17-1.el7.centos.x86_64                                                                                                                                                                           11/12
 
  Limpieza      : MariaDB-common-10.3.17-1.el7.centos.x86_64                                                                                                                                                                           12/12
 
  Comprobando   : MariaDB-devel-10.3.18-1.el7.centos.x86_64                                                                                                                                                                             1/12
 
  Comprobando   : MariaDB-common-10.3.18-1.el7.centos.x86_64                                                                                                                                                                            2/12
 
  Comprobando   : MariaDB-server-10.3.18-1.el7.centos.x86_64                                                                                                                                                                            3/12
 
  Comprobando   : MariaDB-shared-10.3.18-1.el7.centos.x86_64                                                                                                                                                                            4/12
 
  Comprobando   : MariaDB-client-10.3.18-1.el7.centos.x86_64                                                                                                                                                                            5/12
 
  Comprobando   : MariaDB-compat-10.3.18-1.el7.centos.x86_64                                                                                                                                                                            6/12
 
  Comprobando   : MariaDB-shared-10.3.17-1.el7.centos.x86_64                                                                                                                                                                            7/12
 
  Comprobando   : MariaDB-server-10.3.17-1.el7.centos.x86_64                                                                                                                                                                            8/12
 
  Comprobando   : MariaDB-common-10.3.17-1.el7.centos.x86_64                                                                                                                                                                            9/12
 
  Comprobando   : MariaDB-devel-10.3.17-1.el7.centos.x86_64                                                                                                                                                                            10/12
 
  Comprobando   : MariaDB-compat-10.3.17-1.el7.centos.x86_64                                                                                                                                                                           11/12
 
  Comprobando   : MariaDB-client-10.3.17-1.el7.centos.x86_64                                                                                                                                                                           12/12
 
 
 
Actualizado:
 
  MariaDB-client.x86_64 0:10.3.18-1.el7.centos   MariaDB-common.x86_64 0:10.3.18-1.el7.centos   MariaDB-compat.x86_64 0:10.3.18-1.el7.centos   MariaDB-devel.x86_64 0:10.3.18-1.el7.centos   MariaDB-server.x86_64 0:10.3.18-1.el7.centos
 
  MariaDB-shared.x86_64 0:10.3.18-1.el7.centos
 
 
 
¡Listo!
 
[root@server ~]# mysql -V
 
mysql  Ver 15.1 Distrib 10.3.18-MariaDB, for Linux (x86_64) using readline 5.1
 
 
 
[root@server ~]# mysql
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 12
 
Server version: 10.3.18-MariaDB-log MariaDB Server
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> SHOW VARIABLES LIKE "%version%";
 
+-----------------------------------+------------------------------------------+
 
| Variable_name                     | Value                                    |
 
+-----------------------------------+------------------------------------------+
 
| in_predicate_conversion_threshold | 1000                                     |
 
| innodb_version                    | 10.3.18                                  |
 
| protocol_version                  | 10                                       |
 
| slave_type_conversions            |                                          |
 
| system_versioning_alter_history   | ERROR                                    |
 
| system_versioning_asof            | DEFAULT                                  |
 
| version                           | 10.3.18-MariaDB-log                      |
 
| version_comment                   | MariaDB Server                           |
 
| version_compile_machine           | x86_64                                   |
 
| version_compile_os                | Linux                                    |
 
| version_malloc_library            | system                                   |
 
| version_source_revision           | 604f80e77c054758aa449064cdc29dfa13a71922 |
 
| version_ssl_library               | OpenSSL 1.0.2k-fips  26 Jan 2017         |
 
| wsrep_patch_version               | wsrep_25.24                              |
 
+-----------------------------------+------------------------------------------+
 
14 rows in set (0.001 sec)
 
 
 
MariaDB [(none)]> quit
 
Bye
 
 
 
[root@server ~]# time mysql prestashop_1 <bomb-query.sql
 
id_product      id_supplier     id_manufacturer id_category_default     id_shop_default id_tax_rules_group      on_sale online_only     ean13   isbn    upc     ecotax  quantity        minimal_quantity        low_stock_threshold     low_stock_alert      price   wholesale_price unity   unit_price_ratio        additional_shipping_cost        reference       supplier_reference      location        width   height  depth   weight  out_of_stock    additional_delivery_times   quantity_discount        customizable    uploadable_files        text_fields     active  redirect_type   id_type_redirected      available_for_order     available_date  show_condition  condition       show_price      indexed visibility  cache_is_pack    cache_has_attachments   is_virtual      cache_default_attribute date_add        date_upd        advanced_stock_management       pack_stock_type state   id_product      id_shop id_category_default     id_tax_rules_group  on_sale  online_only     ecotax  minimal_quantity        low_stock_threshold     low_stock_alert price   wholesale_price unity   unit_price_ratio        additional_shipping_cost        customizable    uploadable_files        text_fields active   redirect_type   id_type_redirected      available_for_order     available_date  show_condition  condition       show_price      indexed visibility      cache_default_attribute advanced_stock_management       date_add        date_upd     pack_stock_type out_of_stock    quantity        description     description_short       available_now   available_later id_product_attribute    link_rewrite    meta_description        meta_keywords   meta_title      name    id_image     legend  manufacturer_name       new
 
1864    0       5       22      1       1       1       0                               0.000000        0       1       NULL    0       88906.006116    0.000000                0.000000        0.00    MBC300X140                      0.000000     0.000000        0.000000        0.000000        2       1       0       0       0       0       1               0       1       0000-00-00      0       new     1       1       both    0       0       0       0       2019-09-03 10:27:50  2019-09-03 10:27:50     0       3       1       1864    1       22      1       1       0       0.000000        1       NULL    0       88906.006116    0.000000                0.000000        0.00    0       0       0       1   01       0000-00-00      0       new     1       1       both    0       0       2019-09-03 10:27:50     2019-09-03 10:27:50     3       0       150             KwEI=0,"Kg/hvp=0,"Kcal/hG=0<br />3000x1400xh900+900,                    0   xxxxxxxx-piano-chef                 xxxxxxxx "PIANO CHEF"      xxxxxxxx "PIANO CHEF"      1881            DIAMOND 1
 
15133   0       5       22      1       1       1       0                               0.000000        0       1       NULL    0       88906.006116    0.000000                0.000000        0.00    MBC300X140                      0.000000     0.000000        0.000000        0.000000        2       1       0       0       0       0       1               0       1       0000-00-00      0       new     1       1       both    0       0       0       0       2019-09-10 09:45:29  2019-09-10 09:45:29     0       3       1       15133   1       22      1       1       0       0.000000        1       NULL    0       88906.006116    0.000000                0.000000        0.00    0       0       0       1   01       0000-00-00      0       new     1       1       both    0       0       2019-09-10 09:45:29     2019-09-10 09:45:29     3       0       150             KwEI=0,"Kg/hvp=0,"Kcal/hG=0<br />3000x1400xh900+900,                    0   xxxxxxxx-piano-chef                 xxxxxxxx "PIANO CHEF"      xxxxxxxx "PIANO CHEF"      15289           DIAMOND 1
 
1863    0       5       22      1       1       1       0                               0.000000        0       1       NULL    0       69832.213140    0.000000                0.000000        0.00    MBC280X180                      0.000000     0.000000        0.000000        0.000000        2       1       0       0       0       0       1               0       1       0000-00-00      0       new     1       1       both    0       0       0       0       2019-09-03 10:27:50  2019-09-03 10:27:50     0       3       1       1863    1       22      1       1       0       0.000000        1       NULL    0       69832.213140    0.000000                0.000000        0.00    0       0       0       1   01       0000-00-00      0       new     1       1       both    0       0       2019-09-03 10:27:50     2019-09-03 10:27:50     3       0       149             KwEI=0,"Kg/hvp=0,"Kcal/hG=0<br />2800x1800xh900,                        0   xxxxxxxx-piano-chef                 xxxxxxxx "PIANO CHEF"      xxxxxxxx "PIANO CHEF"      1880            DIAMOND 1
 
15132   0       5       22      1       1       1       0                               0.000000        0       1       NULL    0       69832.213140    0.000000                0.000000        0.00    MBC280X180                      0.000000     0.000000        0.000000        0.000000        2       1       0       0       0       0       1               0       1       0000-00-00      0       new     1       1       both    0       0       0       0       2019-09-10 09:45:29  2019-09-10 09:45:29     0       3       1       15132   1       22      1       1       0       0.000000        1       NULL    0       69832.213140    0.000000                0.000000        0.00    0       0       0       1   01       0000-00-00      0       new     1       1       both    0       0       2019-09-10 09:45:29     2019-09-10 09:45:29     3       0       150             KwEI=0,"Kg/hvp=0,"Kcal/hG=0<br />2800x1800xh900,                        0   xxxxxxxx-piano-chef                 xxxxxxxx "PIANO CHEF"      xxxxxxxx "PIANO CHEF"      15288           DIAMOND 1
 
 
 
real    1m16.021s
 
user    0m0.006s
 
sys     0m0.003s

Attached is the query.

Thank you in advance

 Comments   
Comment by Christian Ciach [ 2019-10-11 ]

I can confirm this extreme performance issue after updating from 10.3.17 to 10.3.18. We had to do an immediate downgrade of all systems because of this (CentOS 7).

I cannot give you concrete example queries at the moment, but I suspect that issue lies with the very long ` WHERE IN(...)` expressions that we're using a lot.

EDIT: Every query that is slow with 10.3.18 does indeed include a very long list of IN-parameters.

Comment by Axel Schwenke [ 2019-10-15 ]

I tried to reproduce the problem, but the provided query is of no use for me because I don't have the data it is running on. So I tried with some synthetic data, but could not reproduce the slowness you're seeing.

My guess is, that the optimizer executes the IN () predicate differently in 10.3.18. Or maybe it's using a different table order and this has nothing to do with the long IN () list.

Please provide the output from EXPLAIN FORMAT=JSON SELECT ... (insert your problem SELECT here) for both MariaDB 10.3.17 and 10.3.18.

This kind of problem might be very hard to pinpoint even with EXPLAIN output. Is it possible that you share the data with us? This needn't be complete or live data. Just a dataset that demonstrates the problem.

Comment by JaviN [ 2019-10-15 ]

Can I send you the data by email? I do not want to share it here.

Comment by Axel Schwenke [ 2019-10-15 ]

Hi javii I suggest you upload your data to ftp://ftp.mariadb.com/uploads. You can also use ftps://.. to get transport encryption. This is a write-only location so nobody except MariaDB staff can read it.
Please add a comment with the file name once the upload is complete. I suggest you use "MDEV-20646" somewhere in the file name.

Comment by JaviN [ 2019-10-16 ]

Hi, I have uploaded the data to the ftp with MDEV-20646 in the name.

More information:

Output from EXPLAIN FORMAT=JSON SELECT with MariaDb 10.3.17:
EXPLAIN
{\n "query_block": {\n "select_id": 1,\n "read_sorted_file": {\n "filesort": {\n "sort_key": "product_shop.price desc",\n "table":

{\n "table_name": "product_shop",\n "access_type": "ALL",\n "possible_keys": ["PRIMARY"],\n "rows": 5060,\n "filtered": 75,\n "attached_condition": "product_shop.active = 1 and product_shop.show_price = 1 and product_shop.id_shop = 1 and product_shop.visibility in ('both','catalog')"\n }

\n }\n },\n "duplicates_removal": {\n "table":

{\n "table_name": "cp",\n "access_type": "ref",\n "possible_keys": ["PRIMARY", "id_product", "id_category"],\n "key": "id_product",\n "key_length": "4",\n "used_key_parts": ["id_product"],\n "ref": ["prestashop_1.product_shop.id_product"],\n "rows": 1,\n "filtered": 100,\n "using_index": true\n }

,\n "table":

{\n "table_name": "cg",\n "access_type": "eq_ref",\n "possible_keys": ["PRIMARY", "id_category", "id_group"],\n "key": "PRIMARY",\n "key_length": "8",\n "used_key_parts": ["id_category", "id_group"],\n "ref": ["prestashop_1.cp.id_category", "const"],\n "rows": 1,\n "filtered": 100,\n "using_index": true\n }

,\n "table":

{\n "table_name": "p",\n "access_type": "eq_ref",\n "possible_keys": ["PRIMARY"],\n "key": "PRIMARY",\n "key_length": "4",\n "used_key_parts": ["id_product"],\n "ref": ["prestashop_1.product_shop.id_product"],\n "rows": 1,\n "filtered": 100\n }

\n },\n "table":

{\n "table_name": "product_attribute_shop",\n "access_type": "ref",\n "possible_keys": ["id_product"],\n "key": "id_product",\n "key_length": "10",\n "used_key_parts": ["id_product", "id_shop", "default_on"],\n "ref": ["prestashop_1.product_shop.id_product", "const", "const"],\n "rows": 1,\n "filtered": 100,\n "using_index": true\n }

,\n "table": {\n "table_name": "<subquery3>",\n "access_type": "eq_ref",\n "possible_keys": ["distinct_key"],\n "key": "distinct_key",\n "key_length": "4",\n "used_key_parts": ["_col_1"],\n "ref": ["func"],\n "rows": 1,\n "filtered": 100,\n "attached_condition": "product_shop.id_product = tvc_0._col_1",\n "materialized": {\n "unique": 1,\n "query_block": {\n "select_id": 3,\n "table": {\n "table_name": "<derived4>",\n "access_type": "ALL",\n "rows": 25742,\n "filtered": 100,\n "materialized": {\n "query_block": {\n "union_result": {\n "table_name": "<unit4>",\n "access_type": "ALL",\n "query_specifications": [\n {\n "query_block": {\n "select_id": 4,\n "table":

{\n "message": "No tables used"\n }

\n }\n }\n ]\n }\n }\n }\n }\n }\n }\n },\n "table":

{\n "table_name": "stock",\n "access_type": "eq_ref",\n "possible_keys": [\n "product_sqlstock",\n "id_shop",\n "id_shop_group",\n "id_product",\n "id_product_attribute"\n ],\n "key": "product_sqlstock",\n "key_length": "16",\n "used_key_parts": [\n "id_product",\n "id_product_attribute",\n "id_shop",\n "id_shop_group"\n ],\n "ref": ["prestashop_1.product_shop.id_product", "const", "const", "const"],\n "rows": 1,\n "filtered": 100\n }

,\n "table":

{\n "table_name": "pl",\n "access_type": "eq_ref",\n "possible_keys": ["PRIMARY", "id_lang"],\n "key": "PRIMARY",\n "key_length": "12",\n "used_key_parts": ["id_product", "id_shop", "id_lang"],\n "ref": ["prestashop_1.product_shop.id_product", "const", "const"],\n "rows": 1,\n "filtered": 100\n }

,\n "table":

{\n "table_name": "image_shop",\n "access_type": "ref",\n "possible_keys": ["id_product", "id_shop"],\n "key": "id_product",\n "key_length": "10",\n "used_key_parts": ["id_product", "id_shop", "cover"],\n "ref": ["prestashop_1.product_shop.id_product", "const", "const"],\n "rows": 1,\n "filtered": 100,\n "using_index": true\n }

,\n "table":

{\n "table_name": "il",\n "access_type": "eq_ref",\n "possible_keys": ["PRIMARY", "id_image"],\n "key": "PRIMARY",\n "key_length": "8",\n "used_key_parts": ["id_image", "id_lang"],\n "ref": ["prestashop_1.image_shop.id_image", "const"],\n "rows": 1,\n "filtered": 100,\n "attached_condition": "trigcond(trigcond(image_shop.id_image is not null))"\n }

,\n "table":

{\n "table_name": "m",\n "access_type": "eq_ref",\n "possible_keys": ["PRIMARY"],\n "key": "PRIMARY",\n "key_length": "4",\n "used_key_parts": ["id_manufacturer"],\n "ref": ["prestashop_1.p.id_manufacturer"],\n "rows": 1,\n "filtered": 100,\n "attached_condition": "trigcond(trigcond(p.id_manufacturer is not null))"\n }

\n }\n}

Output from EXPLAIN FORMAT=JSON SELECT with MariaDb 10.3.18:
EXPLAIN
{\n "query_block": {\n "select_id": 1,\n "filesort": {\n "sort_key": "product_shop.price desc",\n "temporary_table": {\n "table": {\n "table_name": "<subquery2>",\n "access_type": "ALL",\n "possible_keys": ["distinct_key"],\n "rows": 17250,\n "filtered": 100,\n "materialized": {\n "unique": 1,\n "query_block": {\n "select_id": 2,\n "table":

{\n "table_name": "cg",\n "access_type": "ref",\n "possible_keys": ["PRIMARY", "id_category", "id_group"],\n "key": "id_group",\n "key_length": "4",\n "used_key_parts": ["id_group"],\n "ref": ["const"],\n "rows": 230,\n "filtered": 100,\n "using_index": true\n }

,\n "table":

{\n "table_name": "cp",\n "access_type": "ref",\n "possible_keys": ["PRIMARY", "id_product", "id_category"],\n "key": "PRIMARY",\n "key_length": "4",\n "used_key_parts": ["id_category"],\n "ref": ["prestashop_1.cg.id_category"],\n "rows": 75,\n "filtered": 100,\n "using_index": true\n }

\n }\n }\n },\n "table":

{\n "table_name": "p",\n "access_type": "eq_ref",\n "possible_keys": ["PRIMARY"],\n "key": "PRIMARY",\n "key_length": "4",\n "used_key_parts": ["id_product"],\n "ref": ["prestashop_1.cp.id_product"],\n "rows": 1,\n "filtered": 100\n }

,\n "duplicates_removal": {\n "table": {\n "table_name": "<derived4>",\n "access_type": "ref",\n "possible_keys": ["key0"],\n "key": "key0",\n "key_length": "4",\n "used_key_parts": ["_col_1"],\n "ref": ["prestashop_1.cp.id_product"],\n "rows": 10,\n "filtered": 100,\n "attached_condition": "cp.id_product = tvc_0._col_1",\n "materialized": {\n "query_block": {\n "union_result": {\n "table_name": "<unit4>",\n "access_type": "ALL",\n "query_specifications": [\n {\n "query_block": {\n "select_id": 4,\n "table":

{\n "message": "No tables used"\n }

\n }\n }\n ]\n }\n }\n }\n }\n },\n "block-nl-join": {\n "table":

{\n "table_name": "product_shop",\n "access_type": "ALL",\n "possible_keys": ["PRIMARY"],\n "rows": 5060,\n "filtered": 75,\n "attached_condition": "product_shop.active = 1 and product_shop.show_price = 1 and product_shop.id_shop = 1 and product_shop.visibility in ('both','catalog')"\n }

,\n "buffer_type": "flat",\n "buffer_size": "512Kb",\n "join_type": "BNL",\n "attached_condition": "product_shop.id_product = cp.id_product"\n },\n "table":

{\n "table_name": "product_attribute_shop",\n "access_type": "ref",\n "possible_keys": ["id_product"],\n "key": "id_product",\n "key_length": "10",\n "used_key_parts": ["id_product", "id_shop", "default_on"],\n "ref": ["prestashop_1.cp.id_product", "const", "const"],\n "rows": 1,\n "filtered": 100,\n "using_index": true\n }

,\n "table":

{\n "table_name": "stock",\n "access_type": "eq_ref",\n "possible_keys": [\n "product_sqlstock",\n "id_shop",\n "id_shop_group",\n "id_product",\n "id_product_attribute"\n ],\n "key": "product_sqlstock",\n "key_length": "16",\n "used_key_parts": [\n "id_product",\n "id_product_attribute",\n "id_shop",\n "id_shop_group"\n ],\n "ref": ["prestashop_1.cp.id_product", "const", "const", "const"],\n "rows": 1,\n "filtered": 100\n }

,\n "table":

{\n "table_name": "pl",\n "access_type": "eq_ref",\n "possible_keys": ["PRIMARY", "id_lang"],\n "key": "PRIMARY",\n "key_length": "12",\n "used_key_parts": ["id_product", "id_shop", "id_lang"],\n "ref": ["prestashop_1.cp.id_product", "const", "const"],\n "rows": 1,\n "filtered": 100\n }

,\n "table":

{\n "table_name": "image_shop",\n "access_type": "ref",\n "possible_keys": ["id_product", "id_shop"],\n "key": "id_product",\n "key_length": "10",\n "used_key_parts": ["id_product", "id_shop", "cover"],\n "ref": ["prestashop_1.cp.id_product", "const", "const"],\n "rows": 1,\n "filtered": 100,\n "using_index": true\n }

,\n "table":

{\n "table_name": "il",\n "access_type": "eq_ref",\n "possible_keys": ["PRIMARY", "id_image"],\n "key": "PRIMARY",\n "key_length": "8",\n "used_key_parts": ["id_image", "id_lang"],\n "ref": ["prestashop_1.image_shop.id_image", "const"],\n "rows": 1,\n "filtered": 100,\n "attached_condition": "trigcond(trigcond(image_shop.id_image is not null))"\n }

,\n "block-nl-join": {\n "table":

{\n "table_name": "m",\n "access_type": "ALL",\n "possible_keys": ["PRIMARY"],\n "rows": 3,\n "filtered": 100\n }

,\n "buffer_type": "flat",\n "buffer_size": "512Kb",\n "join_type": "BNL",\n "attached_condition": "trigcond(m.id_manufacturer = p.id_manufacturer)"\n }\n }\n }\n }\n}

Thank you. Kind regards

Comment by Axel Schwenke [ 2019-10-17 ]

javii thanks for the data set. I reproduced the problem. The query executes in ~100ms on 10.3.17, but needs ~5 sec on 10.3.18.

Comment by Axel Schwenke [ 2019-10-17 ]

I traced this regression to this commit

ef76f81c982 (HEAD) MDEV-20109: Optimizer ignores distinct key created for materialized...

The execution plan of the query changed completely. And it takes now 50 times longer on my test system and more than 1000 times longer on the customer system.

Comment by Sergei Petrunia [ 2019-11-04 ]

So far I haven't been able to reproduce, for me the query runs in ~0.8 sec (on a debug build) both before and after the patch:
https://gist.github.com/spetrunia/23b008562d4da63abd3ba13fd690e3e9
https://gist.github.com/spetrunia/39ef8938361bf0a841e84be4a56d0b2f

Comment by Sergei Petrunia [ 2019-11-05 ]

Taking EXPLAIN outputs provided in this comment https://jira.mariadb.org/browse/MDEV-20646?focusedCommentId=136050&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-136050
and making them readable:

https://gist.github.com/spetrunia/fec6c0f54339964f2a11461f78f1f8e1
https://gist.github.com/spetrunia/b77e64b14038c425f79ec02d69b2ab46

Comment by Sergei Petrunia [ 2019-11-05 ]

The query's outline

SELECT
 
...
 
FROM 
  `prstshp_product` p
 
  INNER JOIN prstshp_product_shop product_shop ON ...
 
  LEFT JOIN `prstshp_product_attribute_shop` product_attribute_shop ON ...
 
  LEFT JOIN prstshp_stock_available stock ON ...
 
  LEFT JOIN `prstshp_product_lang` pl ON ...
 
  LEFT JOIN `prstshp_image_shop` image_shop
 
  LEFT JOIN `prstshp_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 1)
 
  LEFT JOIN `prstshp_manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
 
WHERE 
 ...
 
 AND p.`id_product` IN (large_list)
 
 AND EXISTS(SELECT 1 
            FROM 
              `prstshp_category_product` cp JOIN 
              `prstshp_category_group` cg ON (...)
 
            WHERE cp.`id_product` = p.`id_product`)
 
ORDER BY product_shop.price desc
 
LIMIT 0, 4;

Comment by Sergei Petrunia [ 2019-11-05 ]

Outline of the EXPLAIN posted for 10.3.17:

 
 
filesort {
 
   "table_name": "product_shop", 
   "rows": 5060,
 
}
 
duplicates_removal : {
 
 "table_name": "cp", "rows": 1,
 
 "table_name": "cg", "rows": 1, 
  "table_name": "p", "rows": 1,
 
}
 
 
 
"table_name": "product_attribute_shop", "rows": 1, 
"table_name": "<subquery3>", "rows": 1,
 
  materialized: {
 
      "table_name": "<derived4>",
 
      "rows": 25742, 
            "table_name": "<unit4>",
 
  }
 
"table_name": "stock", "rows": 1, 
"table_name": "pl", "rows": 1, 
"table_name": "image_shop", "rows": 1, 
"table_name": "il", "rows": 1, 
"table_name": "m", "rows": 1,

Note that the plan may short-circuit the ORDER BY ... LIMIT execution.

Most of the tables have rows:1, with exception of "Materialized" node which is created from a long IN-list (which is likely to be materialized anyhow).

Comment by Sergei Petrunia [ 2019-11-05 ]

Outline of the posted EXPLAIN from 10.3.18: 

filesort : {
 
 
 
  "table_name": "<subquery2>", "rows": 17250, materialized : {
 
         "table_name": "cg", "rows": 230, 
         "table_name": "cp", "rows": 75, 
  }
 
  "table_name": "p", "rows": 1, 
  duplicates_removal : { 
      "table_name": "<derived4>", "rows": 10, 
               "table_name": "<unit4>", 
  }
 
  block-nl-join: { "table_name": "product_shop", "rows": 5060 }
 
   "table_name": "product_attribute_shop", "rows": 1, 
   "table_name": "stock", "rows": 1, 
    "table_name": "pl", "rows": 1, 
    "table_name": "image_shop", "rows": 1, 
    "table_name": "il", "rows": 1, 
    block-nl-join: { "table_name": "m", "rows": 3}
 
}

Comment by Sergei Petrunia [ 2019-11-05 ]
  • After carefully repeating the steps on 10.3.17 and *18 (the exact csets for the release), I was able to reproduce.
  • ORDER BY ... LIMIT clause has an effect but it's not the cause: if I remove it, I still get 15 sec vs 1.22 sec time difference.
Comment by Sergei Petrunia [ 2019-11-06 ]

The questionable line is this one in Sj_materialization_picker::check_qep:

    *record_count= prefix_rec_count / mat_info->rows_with_duplicates;

Comment by Sergei Petrunia [ 2019-11-06 ]

The optimizer constructs a join prefix:

(gdb) p join->positions[0].table->table->alias.Ptr
 
  $389 = 0x7fff6c358488 "tvc_0"
 
(gdb) p join->positions[1].table->table->alias.Ptr
 
  $391 = 0x7fff6c5e86b0 "p"

enters advance_sj_state, enters Sj_materialization_picker::check_qep.

Now,

  • prefix_rec_count=25742,
  • mat_info->rows_with_duplicates=25742.

The above line just removes mat_info->rows_with_duplicates from the fanout. What is left is the fanout of table p, which uses eq_ref access, and and has fanout=1.
But the total output of (tvc_0, p) is not 1. it is much higher.

(The logic used by the problematic line would have applied if table p used an "independent" access method. But it fails when it refers to the fields in the subquery )

Comment by Sergei Petrunia [ 2019-11-13 ]

1. Problem description

Consider a query 

select * from ot1, ... otN 
where 
  expr(ot1,...otk) IN (select ... from it1, itN where ...)

and this (possibly partial) join order:

   SJ-Materialization-Scan (it1 ... itN), ot1, ... otk

What is its output cardinality? This question is important, because in MySQL/MariaDB optimizer the cost of query plan has this component:

  current_record_count / (double) TIME_FOR_COMPARE

and this component often is a big (or even biggest) part of the query plan cost.

2. Removing subquery's fanout

As we saw, the output cardinality of this join prefix

   SJ-Materialization-Scan(it1 ... itN), ot1, ... otk

must not include subquery's fanout.

This is trivial when tables ot_i do not use ref access that refers to the materialized table. We can just compute Product{over_i}(ot_i.records_read) .
This is not a practically interesting case though, because in this case, a better join order would be ot1, ... otK, SJ-Materialization(it1... itN).

A more interesting case is when ot_i does use ref access(es) that refer to the materialized table.

2.1. A wrong approach

An approach that seems tempting is to take the output cardinality of the join order of

 ot1, ... otk

(without the subquery in front) and use that. But this is not a good idea, consider this example:

select * from people P where P.name in (select owner from airplane);

and the join order

SJ-Materialization-Scan(airplane), ref(people.name=...)

Here, just taking the cardinality of "people" will be a vast over-estimation as most people dont own airplanes.

2.2. Towards something better

So, the code we had before the patch for MDEV-20109 was actually a good starting point:

  • it produced a reasonable estimate when SJ-Materialization-Scan is good (when the subquery produces fewer rows than the outer select)
  • it produced a bad estimate in the reverse case (when subquery has a huge fanout w.r.t the outer select). But in this case Sj-Materialization-Lookup would probably be a better strategy anyway.
Comment by Sergei Petrunia [ 2019-11-13 ]

ANALYZE output for the example query on the patched version: https://gist.github.com/spetrunia/b4cb8d7d7a625ed51b86448f731ba50b

Generated at Thu Feb 08 09:01:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.