Trage query op MariaDB maar niet op MySQL

[RickyHeijnen]

Ik heb 2 losstaande VPS'en die redelijk gelijke omstandigheden draaien (zelfde OS, zelfde CPU, memory etc..). Op de ene draait MariaDB 10.0.30, op de andere draait MySQL 5.5.47 . Nu gaan beide omgevingen totaal anders om met een redelijk simpele query met het resultaat dat de query op MariaDB er 9 seconden over doet en op MySQL nog geen 0.05 seconde.

Dit is de query:

code:

SELECT cb.*, COUNT(DISTINCT sp.product_id) as 'num_products', MIN(sp.price) AS 'min_price'
FROM shop_product_category spc
INNER JOIN shop_product sp ON sp.product_id = spc.product_id
INNER JOIN shop_product_car spca ON spca.product_id = sp.product_id
INNER JOIN cars_model cm ON cm.model_id = spca.model_id
INNER JOIN cars_brand cb ON cb.brand_id = cm.brand_id
WHERE spc.category_id='241' AND cb.active AND sp.active AND !sp.deleted AND !cm.deleted
GROUP BY cb.brand_id
ORDER BY cb.name ASC

Resultaat EXPLAIN op MySQL:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	spc	ref	PRIMARY,category_id,product_id	PRIMARY	4	const	6272	Using index; Using temporary; Using filesort
1	SIMPLE	sp	eq_ref	PRIMARY	PRIMARY	4	hottnl_2016_dev.spc.product_id	1	Using where
1	SIMPLE	spca	ref	product_id_brand_id_model_id_type_id,product_id_type_id,product_id,model_id	product_id	5	hottnl_2016_dev.sp.product_id	2	Using where
1	SIMPLE	cm	eq_ref	PRIMARY,brand_id,model_id_active_deleted,brand_id_active_deleted	PRIMARY	4	hottnl_2016_dev.spca.model_id	1	Using where
1	SIMPLE	cb	eq_ref	PRIMARY	PRIMARY	4	hottnl_2016_dev.cm.brand_id	1	Using where

Resultaat EXPLAIN op MariaDB:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	spca	index	product_id_brand_id_model_id_type_id,product_id_type_id	product_id_brand_id_model_id_type_id	20		2349372	Using where; Using index; Using temporary; Using filesort
1	SIMPLE	cm	eq_ref	PRIMARY,brand_id,model_id_active_deleted,brand_id_active_deleted	PRIMARY	4	hottnl_2016.spca.model_id	1	Using where
1	SIMPLE	spc	eq_ref	PRIMARY,category_id,product_id	PRIMARY	8	const,hottnl_2016.spca.product_id	1	Using index
1	SIMPLE	sp	eq_ref	PRIMARY,active_deleted,deleted,deleted_e_timestamp	PRIMARY	4	hottnl_2016.spca.product_id	1	Using where
1	SIMPLE	cb	eq_ref	PRIMARY	PRIMARY	4	hottnl_2016.cm.brand_id	1	Using where

Ze geven beide een totaal ander resultaat. Mijn vraag is nu; ligt dit puur aan een andere omgeving: mariadb vs mysql? Dat lijkt mij namelijk niet... Of moet ik het eerder zoeken in de variabelen van MariaDB, en zo ja naar welke waardes moet ik dan kijken?

Als het nodig is om de structuur van de tabellen hier te posten hoor ik het wel, volgens mij is het niet nodig omdat het niet een query specifieke vraag is. Er zijn meerdere queries waarbij een groot verschil is tussen beide omgevingen. Maar het is niet zo dat MariaDB het voor alles verprutst. Sterker nog; op de MariaDB server draaien een aantal website helemaal vlekkeloos.

PS. Uiteraard zijn de tabellen op beide databases identiek gevuld, met dezelfde keys etc...

[Aegean]

Kan het zijn dat de ene VPS een SSD heeft en de ander een HDD?

Je kunt dit eenvoudig testen:

code:

dd if=/dev/zero of=/tmp/output bs=8k count=10k; rm -f /tmp/outputp

https://askubuntu.com/que...eck-hard-disk-performance

[Trucker Her]

Aegean schreef op maandag 15 mei 2017 @ 16:31:
Kan het zijn dat de ene VPS een SSD heeft en de ander een HDD?

Je kunt dit eenvoudig testen:

code:

dd if=/dev/zero of=/tmp/output bs=8k count=10k; rm -f /tmp/outputp

https://askubuntu.com/que...eck-hard-disk-performance

Moet je er hierbij wel even zeker weten dat /tmp geen tmpfs is (in werkgeheugen dus), maar daadwerkelijk een filesystem op de schijf.

[RickyHeijnen]

Zou dat het verschil kunnen zijn? Heb het even gecheckt voor de zekerheid en zie daar inderdaad een groot verschil.

MariaDB VPS

code:

root@cloud01 [~]# dd if=/dev/zero of=/tmp/output bs=8k count=10k; rm -f /tmp/out                                                           putp
10240+0 records in
10240+0 records out
83886080 bytes (84 MB) copied, 0.208914 s, 402 MB/s

MySQL VPS

code:

[root@mysql ~]# dd if=/dev/zero of=/tmp/output bs=8k count=10k; rm -f /tmp/outputp
10240+0 records in
10240+0 records out
83886080 bytes (84 MB) copied, 0.0826691 s, 1.0 GB/s

Ik ga eens contact opnemen met de de hostingprovider om wat uitleg te vragen hierover. Als dat de oplossing is: Bedankt voor de tip

[GlowMouse]

Vergeet dat met ssd/hdd, daar moet je pas naar kijken als de execution plans hetzelfde zijn. Draai ANALYZE TABLE eens op alle tabellen en kijk of de execution plans dan wel overeenkomen. Je kunt altijd nog STRAIGHT_JOIN gebruiken om een execution plan af te dwingen. Als je de query echt snel wilt hebben, zorg dan dat cb de eerste tabel is (in het execution plan, niet noodzakelijkerwijs in de query), vervang GROUP BY cb.brand_id door GROUP BY cb.name,cb.brand_id, en zet één index op de twee velden cb.name,cb_brand_id.

[ Voor 6% gewijzigd door GlowMouse op 15-05-2017 16:42 ]

[Vaan Banaan]

Probeer anders eens delen van de query volgorde te forceren
https://mariadb.com/kb/en...how-to-force-query-plans/

[Standeman]

MySQL rows: 6272 vs MariaDB rows : 2349372

MariaDB processed bijna 374.6 keer zoveel rows als MySQL. Kan het niet zijn dat je ergens een index mist o.i.d.? (Ik ben verder geen DBA of zo )

[ Voor 8% gewijzigd door Standeman op 15-05-2017 16:46 ]

[RickyHeijnen]

Standeman schreef op maandag 15 mei 2017 @ 16:44:
MySQL rows: 6272 vs MariaDB rows : 2349372

MariaDB processed bijna 374.6 keer zoveel rows als MySQL. Kan het niet zijn dat je ergens een index mist o.i.d.? (Ik ben verder geen DBA of zo)

Nee indexes zijn echt exact hetzelfde.

GlowMouse schreef op maandag 15 mei 2017 @ 16:42:
Draai ANALYZE TABLE eens op alle tabellen en kijk of de execution plans dan wel overeenkomen.

Done that, en je hebt gelijk. De execution plans zijn nu wel exact hetzelfde. Query snelheid is nu terug gebracht van 8 sec. naar 1.2 sec op MariaDB vs 0.05 op MySQL. Zou dat verschil dan wel te verklaren zijn door HDD/SSD ?

[GlowMouse]

Ik vermoed dat het grote verschil door de processorsnelheid komt, en wellicht door de hoeveelheid van de tabel die daadwerkelijk in het fysieke ramgeheugen zit. Storage is immers maar een factor 2.5 trager. Met de suggestie die ik deed, kun je hem nog wel fors sneller krijgen; het enkel toevoegen van de index en aanpassen van GROUP BY is daarvoor waarschijnlijk al voldoende.

[ Voor 34% gewijzigd door GlowMouse op 15-05-2017 16:53 ]

[RickyHeijnen]

GlowMouse schreef op maandag 15 mei 2017 @ 16:51:
Met de suggestie die ik deed, kun je hem nog wel fors sneller krijgen; het enkel toevoegen van de index en aanpassen van GROUP BY is daarvoor waarschijnlijk al voldoende.

Toch niet... met het toevoegen van de index en GROUP BY blijft hij toch de brand_id key pakken op de cb tabel. Hij neem dus niet de key van die 2 kolommen.

[GlowMouse]

Dan moet je met STRAIGHT_JOIN zorgen dat cb de eerste tabel is in het execution plan. Een nadeel dan is wel dat de query wat langer duurt bij zeldzame categorieën dan bij veelvoorkomende categorieën.

[ Voor 9% gewijzigd door GlowMouse op 15-05-2017 17:48 ]

[Voutloos]

Van de tabel spc wordt in beide gevallen de PK gebruikt, maar key_len is 4 vs 8. Weet je zeker dat de structuur hetzelfde is? Kan bijna niet zo zijn.

[GlowMouse]

Voutloos schreef op maandag 15 mei 2017 @ 20:36:
Van de tabel spc wordt in beide gevallen de PK gebruikt, maar key_len is 4 vs 8. Weet je zeker dat de structuur hetzelfde is? Kan bijna niet zo zijn.

Key_len is alleen het gebruikte deel van de index. In het eerste geval wordt in de index niet naar product_id gezocht (al wordt de waarde wel uit de index uitgelezen, zo blijkt uit using index)

[Voutloos]

Vooruit, alhoewel het geen kwaad had gekund om te zeggen dat deze PK op beide omgevingen dus (category_id, product_id) is.
(En de losse index op category_id zou daarmee redundant zijn).

[RickyHeijnen]

Oke, dat ga ik dan straks nog wel even proberen. Maargoed, afgezien daarvan hebben beide servers nu wel dezelfde execution plan, maar toch nog een groot verschil in execution time. 1.2 sec vs 0.05 sec. Hoe kom ik er nu achter waar dat grote verschil in zit? Kan me niet voorstellen dat het alleen HDD/SSD is.

[DJMaze]

RickyHeijnen schreef op dinsdag 16 mei 2017 @ 10:21:
Hoe kom ik er nu achter waar dat grote verschil in zit? Kan me niet voorstellen dat het alleen HDD/SSD is.

Zorg er eerst even voor dat de volgende meldingen er niet zijn:

code:

Using temporary
Using filesort
Using where

En kijk dan of er nog verschillen zijn

[ Voor 5% gewijzigd door DJMaze op 16-05-2017 13:14 ]

[Tazzios]

Zijn de tabellen misschien in verschillend formaten opgeslagen? (innodb, aria, mysam etc)

[emnich]

@RickyHeijnen Draai hem ook even zonder cache.

[mr_derk]

Even over die group by..

In Oracle is dit in ieder geval ongeldig. De kolommen zonder aggregatie (min,max,avg,sum,count) in je select moeten ook terugkomen in de group by clausule.

Blijkbaar is het in jouw voorbeelden wel legaal, kan het zijn dat één van beide er toch niet zo goed mee om kan zijn?