Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20646

10.3.18 is slower than 10.3.17

    XMLWordPrintable

    Details

      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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergey Sergei Petrunia
              Reporter:
              javii JaviN
              Votes:
              3 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: