0

I have two MariaDB instances running on two different machines 1. iMac (Late 2012, 32GB) OSX 10.13.3 (High-Sierra) 2. Brand-new Intel-Server with 4 cores (VMWare) 64GB exclusive Database-Server

On both machines MariaDB 10.3.4 is installed (latest Beta of MariaDB 10.3)

I have created two views whereas the second uses the first one as shown below.

First view

select
    `a`.`artikel` AS `artikel`,
    group_concat(
        distinct `b`.`reference` separator '/'
    ) AS `referenz`
from
    (
        `prod_welu_pdm`.`appadaptive_pdm_domain_model_stammdaten_artikelreference` `a`
    join `prod_welu_pdm`.`appadaptive_pdm_domain_model_klassifizierung_reference` `b` on
        (
            `a`.`reference` = `b`.`persistence_object_identifier`
        )
    )
group by
    `a`.`artikel`

Second View

select
    `a`.`persistence_object_identifier` AS `persistence_object_identifier`,
    `a`.`artikelnr` AS `artikelnr`,
    `a`.`bezeichnung` AS `bezeichnung`,
    `b`.`referenz` AS `referenz`
from
    (
        `prod_welu_pdm`.`appadaptive_pdm_domain_model_stammdaten_artikel` `a`
    left join `prod_welu_pdm`.`vwelupdmartikelreferences` `b` on
        (
            `a`.`persistence_object_identifier` = `b`.`artikel`
        )
    )

When I run the second view on my 6 years old iMac where MariaDB has a maximum of 5GB as buffer-pool-size, the query takes 200ms for 200 rows.

On the brand-new Intel-Server with 32GB buffer-pool-size the identical query takes 6 to 10 seconds.

The databases on both machines are absolutely identical concerning indexes, constraints and all the rest. The only visible difference is the result of the queries done with EXPLAIN

iMac: enter image description here Intel-Server: enter image description here What might cause such a different behaviour on two MariaDB instances with the same version? enter image description here

Here the first EXPLAIN SELECT again as picture

disinfor
  • 10,865
  • 2
  • 33
  • 44
Reggaeny
  • 1
  • 1
  • Can't see anything in the first image. Anyway, try to run `ANALYZE` for the underlying tables, maybe statistics are off. Also, you said that indexes and constraints are the same, but didn't say that the config is identical -- is it? – elenst Feb 16 '18 at 18:17
  • Yes, except the purpose (iMac Multi-Purpose, Intel-Server dedicated Database Server) and the available RAM for MariaDB (iMac 5GB, Intel-Server 32GB) the two configs are the same. – Reggaeny Feb 17 '18 at 22:01
  • Run `ANALYZE` on all tables, and if you still experience the problem, paste or attach the output of `SHOW INDEX IN ` for each table on each server, and `SHOW VARIABLES` from both servers. At least `SHOW VARIABLES` output must be a text, not an image.
     – elenst Feb 17 '18 at 22:29
  • I think I found the reason: MariaDB 10.3.2 has a optimizer_switch variable "split_grouping_derived=on" this variable obviously is responsible for the very fast execution of the queries I mentioned. See also "This first patch prepared for the task MDEV-13369:" of the MariaDB issue-tracker. This variable is not available for MariaDB 10.3.4 – Reggaeny Feb 18 '18 at 16:02

0 Answers0