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

Same Query extremely slower on MariaDB 10.3.4 than on MariaDB 10.3.2

    XMLWordPrintable

Details

    Description

      I have two identical databases running on two different machines:
      1. iMac (Late 2012, QuadCore i7, 32GB, OSX 10.13.3) - Development
      2. Intel-Server(Late 2017, 20-Core XEON, 64GB, RHEL 7.4) - Production

      The iMac is a multi-purpose machine with MariaDB-Server (homebrew) 10.3.2
      The second machine is a dedicated DB-Server running only MariaDB 10.3.4 as DBMS

      The following query takes 200ms for 200 rows on the iMac whereas it takes 6 to 8 seconds! on the RHEL Database Machine (which is 30 to 40 times slower on the far better machine).

      SELECT * FROM dev_welu_pdm.appadaptive_pdm_domain_model_stammdaten_artikel a LEFT JOIN (SELECT b.artikel, GROUP_CONCAT(c.reference) AS referenz FROM dev_welu_pdm.appadaptive_pdm_domain_model_stammdaten_artikelreference b
      INNER JOIN dev_welu_pdm.appadaptive_pdm_domain_model_klassifizierung_reference c ON b.reference = c.persistence_object_identifier GROUP BY b.artikel) AS x on a.persistence_object_identifier = x.artikel
      

      Running the query with EXPLAIN the result is different on the two machines. (See the attached file)

      CREATE TABLE `appadaptive_pdm_domain_model_stammdaten_artikel` (
        `persistence_object_identifier` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
        `artikelnr` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
        `bezeichnung` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `produkt` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `bonus` tinyint(1) NOT NULL,
        `warengruppe` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `supplychain` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `status` int(11) NOT NULL,
        `mindestbestellmenge` int(11) NOT NULL,
        `verpackungmenge` int(11) NOT NULL,
        `ekmindestbestellmenge` int(11) NOT NULL,
        `laenge` int(11) NOT NULL,
        `breite` int(11) NOT NULL,
        `hoehe` int(11) NOT NULL,
        `bestelllosgroesse` int(11) NOT NULL,
        `gewicht` double NOT NULL,
        `zolltarifnummer` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `gebuehrendsd` double NOT NULL,
        `markenanonym` tinyint(1) NOT NULL,
        `altartikelnr` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `mengeneinheit` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `durchmesser` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `standardlieferant` int(11) NOT NULL,
        `varkey` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `klassegruppe` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `branding` int(11) NOT NULL,
        `verpackung` int(11) NOT NULL,
        `zeichnungsnummer` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `eigenschaften` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:array)',
        `verwendungen` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:array)',
        `mkategorieen` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:array)',
        `gdimandant` int(11) NOT NULL,
        `statustype` int(11) NOT NULL,
        `statushistory` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:array)',
        `imagelink` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `qc` tinyint(1) NOT NULL,
        `qp` tinyint(1) NOT NULL,
        `artikelart` int(11) NOT NULL,
        `gdiwgr` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `gdiselektion` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `isoa2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `gdilazo` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `gdikzhsl` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `bulkprocessflag` int(11) NOT NULL,
        `verwendung` int(11) NOT NULL,
        `verkauflosgroesse` int(11) NOT NULL,
        `lieferzeit` int(11) NOT NULL,
        `pmchecked` tinyint(1) NOT NULL,
        `isstandard` tinyint(1) NOT NULL,
        `referenz` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `kzal` tinyint(1) NOT NULL,
        `kzsperre` tinyint(1) NOT NULL,
        PRIMARY KEY (`persistence_object_identifier`),
        UNIQUE KEY `UNIQ_307622BC79EBCBC` (`artikelnr`),
        KEY `altArtikelNr` (`altartikelnr`),
        CONSTRAINT `FK_9D3C6D1D21E3D446` FOREIGN KEY (`persistence_object_identifier`) REFERENCES `appadaptive_pdm_domain_model_stammdaten_abstractstammdatum` (`persistence_object_identifier`) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
      

      CREATE TABLE `appadaptive_pdm_domain_model_stammdaten_artikelreference` (
        `persistence_object_identifier` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
        `artikel` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
        `reference` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
        PRIMARY KEY (`persistence_object_identifier`),
        KEY `IDX_3062FD0DA4375C33` (`artikel`),
        KEY `IDX_3062FD0DAEA34913` (`reference`),
        CONSTRAINT `FK_FDC375A6A4375C33` FOREIGN KEY (`artikel`) REFERENCES `appadaptive_pdm_domain_model_stammdaten_artikel` (`persistence_object_identifier`),
        CONSTRAINT `FK_FDC375A6AEA34913` FOREIGN KEY (`reference`) REFERENCES `appadaptive_pdm_domain_model_klassifizierung_reference` (`persistence_object_identifier`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
      

      CREATE TABLE `appadaptive_pdm_domain_model_klassifizierung_reference` (
        `persistence_object_identifier` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
        `reference` int(11) NOT NULL,
        `bezeichnung` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        PRIMARY KEY (`persistence_object_identifier`),
        KEY `bezeichnung` (`bezeichnung`),
        KEY `UNIQ_586EC305AEA34913` (`reference`) USING BTREE,
        CONSTRAINT `FK_A17A64547A46B0A` FOREIGN KEY (`persistence_object_identifier`) REFERENCES `appadaptive_pdm_domain_model_stammdaten_abstractstammdatum` (`persistence_object_identifier`) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
      

      iMac - Server.cnf

      # Example MariaDB config file for very large systems.
      #
      # This is for a large system with memory of 1G-2G where the system runs mainly
      # MariaDB.
      #
      # MariaDB programs look for option files in a set of
      # locations which depend on the deployment platform.
      # You can copy this option file to one of those
      # locations. For information about these locations, do:
      # 'my_print_defaults --help' and see what is printed under
      # Default options are read from the following files in the given order:
      # More information at: http://dev.mysql.com/doc/mysql/en/option-files.html
      #
      # In this file, you can use all long options that a program supports.
      # If you want to know which options a program supports, run the program
      # with the "--help" option.
       
      # The following options will be passed to all MySQL clients
      [client]
      #password	= your_password
      port		= 3306
      socket		= /tmp/mysql.sock
       
      # Here follows entries for some specific programs
       
      # The MySQL server
      [mysqld]
      port		= 3306
      socket		= /tmp/mysql.sock
      #skip-external-locking
      #thread_stack = 128K
      #table_cache = 8192
      #tmp_table_size = 320M
      max_heap_table_size = 320M
      #query_cache_type = 1
      query_cache_limit = 64M
      #query_cache_min_res_unit = 2k
      query_cache_size = 64M
      #key_buffer_size = 384M
      max_allowed_packet = 1M
      #table_open_cache = 512
      #sort_buffer_size = 2M
      #read_buffer_size = 2M
      #read_rnd_buffer_size = 8M
      #myisam_sort_buffer_size = 64M
      #thread_handling = pool-of-threads
      #thread_cache_size = 8
      # Try number of CPU's*2 for thread_concurrency
      #thread_pool_size = 8
      #thread_pool_priority = auto
       
      # Point the following paths to a dedicated disk
      #tmpdir		= /tmp/
       
      # Skip reverse DNS lookup of clients
      skip-name-resolve
       
      # Don't listen on a TCP/IP port at all. This can be a security enhancement,
      # if all processes that need to connect to mysqld run on the same host.
      # All interaction with mysqld must be made via Unix sockets or named pipes.
      # Note that using this option without enabling named pipes on Windows
      # (via the "enable-named-pipe" option) will render mysqld useless!
      # 
      #skip-networking
       
      # Replication Master Server (default)
      # binary logging is required for replication
      # log-bin=mysql-bin
       
      # required unique id between 1 and 2^32 - 1
      # defaults to 1 if master-host is not set
      # but will not function as a master if omitted
      server-id	= 1
       
      # Replication Slave (comment out master section to use this)
      #
      # To configure this host as a replication slave, you can choose between
      # two methods :
      #
      # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
      #    the syntax is:
      #
      #    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
      #    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
      #
      #    where you replace <host>, <user>, <password> by quoted strings and
      #    <port> by the master's port number (3306 by default).
      #
      #    Example:
      #
      #    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
      #    MASTER_USER='joe', MASTER_PASSWORD='secret';
      #
      # OR
      #
      # 2) Set the variables below. However, in case you choose this method, then
      #    start replication for the first time (even unsuccessfully, for example
      #    if you mistyped the password in master-password and the slave fails to
      #    connect), the slave will create a master.info file, and any later
      #    change in this file to the variables' values below will be ignored and
      #    overridden by the content of the master.info file, unless you shutdown
      #    the slave server, delete master.info and restart the slaver server.
      #    For that reason, you may want to leave the lines below untouched
      #    (commented) and instead use CHANGE MASTER TO (see above)
      #
      # required unique id between 2 and 2^32 - 1
      # (and different from the master)
      # defaults to 2 if master-host is set
      # but will not function as a slave if omitted
      #server-id       = 2
      #
      # The replication master for this slave - required
      #master-host     =   <hostname>
      #
      # The username the slave will use for authentication when connecting
      # to the master - required
      #master-user     =   <username>
      #
      # The password the slave will authenticate with when connecting to
      # the master - required
      #master-password =   <password>
      #
      # The port the master is listening on.
      # optional - defaults to 3306
      #master-port     =  <port>
      #
      # binary logging - not required for slaves, but recommended
      #log-bin=mysql-bin
      #
      # binary logging format - mixed recommended 
      #binlog_format=mixed
       
      # Uncomment the following if you are using InnoDB tables
      #innodb_force_recovery = 0
      innodb_data_home_dir = /usr/local/var/mysql/
      #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
      innodb_file_per_table
      innodb_log_group_home_dir = /usr/local/var/mysql
      # You can set .._buffer_pool_size up to 50 - 80 %
      # of RAM but beware of setting memory usage too high
      innodb_buffer_pool_size = 5G
      # innodb_additional_mem_pool_size = 20M
      # Set .._log_file_size to 25 % of buffer pool size
      innodb_log_file_size = 2500M
      innodb_log_buffer_size = 8M
      #innodb_flush_log_at_trx_commit = 1
      #innodb_lock_wait_timeout = 50
      #innodb_large_prefix = 1
       
      [mysqldump]
      quick
      max_allowed_packet = 16M
       
      [mysql]
      no-auto-rehash
      # Remove the next comment character if you are not familiar with SQL
      #safe-updates
       
      [myisamchk]
      #key_buffer_size = 256M
      #sort_buffer_size = 256M
      #read_buffer = 2M
      #write_buffer = 2M
       
      [mysqlhotcopy]
      interactive-timeout
      

      Intel-Server (RHEL) - Server.cnf

      #
      # These groups are read by MariaDB server.
      # Use it for options that only the server (but not clients) should see
      #
      # See the examples of server my.cnf files in /usr/share/mysql/
      #
       
      [client]
      #password	= your_password
      port		= 3306
      socket		= /databases/mysql/mysql.sock
       
      # this is read by the standalone daemon and embedded servers
      [server]
       
      # this is only for the mysqld standalone daemon
      [mysqld]
       
      #
      # * Galera-related settings
      #
      [galera]
      # Mandatory settings
      #wsrep_on=ON
      #wsrep_provider=
      #wsrep_cluster_address=
      #binlog_format=row
      #default_storage_engine=InnoDB
      #innodb_autoinc_lock_mode=2
      #
      # Allow server to accept connections on all interfaces.
      #
      #bind-address=0.0.0.0
      #
      # Optional setting
      #wsrep_slave_threads=1
      #innodb_flush_log_at_trx_commit=0
       
      # this is only for embedded server
      [embedded]
       
      # This group is only read by MariaDB servers, not by MySQL.
      # If you use the same .cnf file for MySQL and MariaDB,
      # you can put MariaDB-only options here
      [mariadb]
       
      port		= 3306
      socket		= /databases/mysql/mysql.sock
      datadir 	= /databases/mysql
      #skip-external-locking
      #thread_stack = 128K
      #table_cache = 8192
      #tmp_table_size = 320M
      max_heap_table_size = 320M
      #query_cache_type = 1
      query_cache_limit = 64M
      query_cache_min_res_unit = 2k
      query_cache_size = 64M
      #key_buffer_size = 384M
      max_allowed_packet = 1M
      #table_open_cache = 512
      #sort_buffer_size = 2M
      #read_buffer_size = 2M
      #read_rnd_buffer_size = 8M
      #myisam_sort_buffer_size = 64M
      #thread_handling = pool-of-threads
      #thread_cache_size = 8
      # Try number of CPU's*2 for thread_concurrency
      #thread_pool_size = 4
      #thread_pool_priority = auto
       
      # Point the following paths to a dedicated disk
      #tmpdir		= /tmp/
       
      # Skip reverse DNS lookup of clients
      skip-name-resolve
       
      # Don't listen on a TCP/IP port at all. This can be a security enhancement,
      # if all processes that need to connect to mysqld run on the same host.
      # All interaction with mysqld must be made via Unix sockets or named pipes.
      # Note that using this option without enabling named pipes on Windows
      # (via the "enable-named-pipe" option) will render mysqld useless!
      # 
      #skip-networking
       
      # Replication Master Server (default)
      # binary logging is required for replication
      # log-bin=mysql-bin
       
      # required unique id between 1 and 2^32 - 1
      # defaults to 1 if master-host is not set
      # but will not function as a master if omitted
      server-id	= 1
       
      # Replication Slave (comment out master section to use this)
      #
      # To configure this host as a replication slave, you can choose between
      # two methods :
      #
      # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
      #    the syntax is:
      #
      #    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
      #    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
      #
      #    where you replace <host>, <user>, <password> by quoted strings and
      #    <port> by the master's port number (3306 by default).
      #
      #    Example:
      #
      #    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
      #    MASTER_USER='joe', MASTER_PASSWORD='secret';
      #
      # OR
      #
      # 2) Set the variables below. However, in case you choose this method, then
      #    start replication for the first time (even unsuccessfully, for example
      #    if you mistyped the password in master-password and the slave fails to
      #    connect), the slave will create a master.info file, and any later
      #    change in this file to the variables' values below will be ignored and
      #    overridden by the content of the master.info file, unless you shutdown
      #    the slave server, delete master.info and restart the slaver server.
      #    For that reason, you may want to leave the lines below untouched
      #    (commented) and instead use CHANGE MASTER TO (see above)
      #
      # required unique id between 2 and 2^32 - 1
      # (and different from the master)
      # defaults to 2 if master-host is set
      # but will not function as a slave if omitted
      #server-id       = 2
      #
      # The replication master for this slave - required
      #master-host     =   <hostname>
      #
      # The username the slave will use for authentication when connecting
      # to the master - required
      #master-user     =   <username>
      #
      # The password the slave will authenticate with when connecting to
      # the master - required
      #master-password =   <password>
      #
      # The port the master is listening on.
      # optional - defaults to 3306
      #master-port     =  <port>
      #
      # binary logging - not required for slaves, but recommended
      #log-bin=mysql-bin
      #
      # binary logging format - mixed recommended 
      #binlog_format=mixed
       
      # Uncomment the following if you are using InnoDB tables
      #innodb_force_recovery = 0
      innodb_data_home_dir = /databases/mysql/
      #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
      innodb_file_per_table
      innodb_log_group_home_dir = /databases/mysql
      # You can set .._buffer_pool_size up to 50 - 80 %
      # of RAM but beware of setting memory usage too high
      innodb_buffer_pool_size = 32G
      # innodb_additional_mem_pool_size = 20M
      # Set .._log_file_size to 25 % of buffer pool size
      innodb_log_file_size = 8G
      innodb_log_buffer_size = 8M
      #innodb_flush_log_at_trx_commit = 1
      #innodb_lock_wait_timeout = 50
      #innodb_large_prefix = 1
       
      [mysql]
      no-auto-rehash
       
      # This group is only read by MariaDB-10.1 servers.
      # If you use the same .cnf file for MariaDB of different versions,
      # use this group for options that older servers don't understand
      [mariadb-10.1]
      

      Attachments

        Activity

          People

            alice Alice Sherepa
            Reggaeny Regine Rosewich
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.