SQL query traag, kleine dataset, waarom?

[Twazerty]

Ik onderhoud een webshop die gebouwd is met Prestashop. Sinds een upgrade naar een recente versie hebben we regelmatig last van downtime. Middels een beheertool van Plesk heb ik de boosdoener gevonden. Er is een specifieke query die heel lang loopt (3 min+). Deze query wordt veroorzaakt door bezoekers van de webshop. Heb je meerdere bezoekers die deze query veroorzaken dan ben je het haasje. De query heb ik kunnen relateren aan de filters in de categorieën. Je kunt bv filteren op kleur, maat en andere eigenschappen. Ik ben erachter dat het nog goed gaat bij het selecteren van 3 verschillende filters. Zodra je de 4e filter erbij klikt ontstaat een query die minstens enkele minuten loopt. De site draait op MariaDB 10.5.10.

Dit is de betreffende query die wordt afgetrapt (deze duurt +- 3min). Hierin zijn nu 4 filters verwerkt in de query:

SQL:

SELECT Count(DISTINCT p.id_product) c
FROM   (SELECT p.id_product,
               p.id_manufacturer,
               Sum(sa.quantity) AS quantity,
               p.condition,
               p.weight,
               p.price,
               psales.quantity  AS sales,
               cp.position
        FROM   ps_product p
               LEFT JOIN ps_product_attribute pa
                      ON ( p.id_product = pa.id_product )
               LEFT JOIN ps_product_attribute_combination pac
                      ON ( pa.id_product_attribute = pac.id_product_attribute )
               LEFT JOIN ps_stock_available sa
                      ON ( p.id_product = sa.id_product
                           AND Ifnull(pac.id_product_attribute, 0) =
                               sa.id_product_attribute
                           AND sa.id_shop = 1
                           AND sa.id_shop_group = 0 )
               LEFT JOIN ps_product_sale psales
                      ON ( psales.id_product = p.id_product )
               INNER JOIN ps_category_product cp
                       ON ( p.id_product = cp.id_product )
               INNER JOIN ps_category c
                       ON ( cp.id_category = c.id_category
                            AND c.active = 1 )
               INNER JOIN ps_product_shop ps
                       ON ( p.id_product = ps.id_product
                            AND ps.id_shop = 1
                            AND ps.active = true )
               LEFT JOIN ps_feature_product fp
                      ON ( p.id_product = fp.id_product )
               LEFT JOIN ps_feature_product fp_1
                      ON ( p.id_product = fp_1.id_product )
               LEFT JOIN ps_feature_product fp_2
                      ON ( p.id_product = fp_2.id_product )
               LEFT JOIN ps_feature_product fp_3
                      ON ( p.id_product = fp_3.id_product )
        WHERE  (( fp.id_feature_value = 39 ))
               AND (( fp_1.id_feature_value = 149 ))
               AND (( fp_2.id_feature_value = 273 ))
               AND (( fp_3.id_feature_value IN ( 3555, 312 ) ))
               AND p.visibility IN ( 'both', 'catalog' )
               AND c.nleft >= 3
               AND c.nright <= 138
               AND ps.id_shop = '1'
        GROUP  BY p.id_product) p
       LEFT JOIN ps_feature_product fp
              ON ( p.id_product = fp.id_product )
       LEFT JOIN ps_feature_product fp_1
              ON ( p.id_product = fp_1.id_product )
       LEFT JOIN ps_feature_product fp_2
              ON ( p.id_product = fp_2.id_product )
       LEFT JOIN ps_feature_product fp_3
              ON ( p.id_product = fp_3.id_product )
WHERE  (( fp.id_feature_value = 39 ))
       AND (( fp_1.id_feature_value = 149 ))
       AND (( fp_2.id_feature_value = 273 ))
       AND (( fp_3.id_feature_value IN ( 3555, 312 ) ))  

Naar mijn idee wordt er geredeneerd over een kleine dataset. Via een explain van bovenstaande query zijn er maar enkele 100'en records mee gemoeid. Of zie ik dat verkeerd?

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	fp_2	ref	id_feature_value, id_product	id_feature_value	4	const	233	Using index
1	PRIMARY	fp_3	ref	id_feature_value, id_product	id_product	4	presta_tst2.fp_2.id_product	3	Using where; Using index
1	PRIMARY	fp_1	ref	id_feature_value, id_product	id_product	4	presta_tst2.fp_2.id_product	3	Using where; Using index
1	PRIMARY	fp	ref	id_feature_value, id_product	id_product	4	presta_tst2.fp_2.id_product	3	Using where; Using index
1	PRIMARY	<derived2>	ref	key0	key0	4	presta_tst2.fp_2.id_product	2	""
2	LATERAL DERIVED	p	eq_ref	PRIMARY	PRIMARY	4	presta_tst2.fp_1.id_product	1	Using where
2	LATERAL DERIVED	fp_3	ref	id_feature_value, id_product	id_product	4	presta_tst2.p.id_product	3	Using where; Using index
2	LATERAL DERIVED	fp_2	ref	id_feature_value, id_product	id_product	4	presta_tst2.p.id_product	3	Using index
2	LATERAL DERIVED	fp_1	ref	id_feature_value, id_product	id_product	4	presta_tst2.p.id_product	3	Using index
2	LATERAL DERIVED	fp	ref	id_feature_value, id_product	id_product	4	presta_tst2.p.id_product	3	Using index
2	LATERAL DERIVED	c	range	PRIMARY, nleftrightactive, nright, activenleft, activenright	activenright	5	""	55	Using index condition; Using where; Using join buffer (flat, BNL join)
2	LATERAL DERIVED	ps	eq_ref	PRIMARY	PRIMARY	8	presta_tst2.p.id_product,const	1	Using where
2	LATERAL DERIVED	pa	ref	product_default, product_attribute_product	product_default	4	presta_tst2.p.id_product	1	Using index
2	LATERAL DERIVED	pac	ref	id_product_attribute	id_product_attribute	4	presta_tst2.pa.id_product_attribute	1	Using where; Using index
2	LATERAL DERIVED	cp	eq_ref	PRIMARY, id_product, id_category	PRIMARY	8	presta_tst2.c.id_category, presta_tst2.p.id_product	1	""
2	LATERAL DERIVED	sa	eq_ref	product_sqlstock, id_shop, id_shop_group, id_product, id_product_attribute	product_sqlstock	16	presta_tst2.p.id_product, func,const, const	1	Using where
2	LATERAL DERIVED	psales	eq_ref	PRIMARY	PRIMARY	4	presta_tst2.p.id_product	1	""

Met 3 filters duurt de query meestal minder dan een seconde, soms een paar seconden.

Om een idee van de omvang te geven. Aantal records per gerelevante tabel (afgerond):

Table	Aantal records
ps_product	4200
ps_feature_product	27000
ps_product_attribute	7500
ps_product_attribute_combination	7200
ps_stock_available	12000
ps_product_sale	2100
ps_category_product	11000
ps_category	350
ps_product_shop	4200

Als ik de eerste inner query (select op regel 2) los uitvoer dan krijg ik en maar een klein setje results, en de query is nagenoeg instant uitgevoerd.

Server specs:
VPS bij Transip, 4 cpu's, 8GB RAM.

MySQL settings:

code:

[mysqld]
table_open_cache                      = 1000
read_buffer_size                      = 2M
read_rnd_buffer_size                  = 1M
thread_cache_size                     = 80
join_buffer_size                      = 2M
sort_buffer_size                      = 2M
max_connections                       = 400
tmp_table_size                        = 32M
max_heap_table_size                   = 32M
table_definition_cache                = 1000
performance_schema                    = OFF

Deze query is opgebouwd door Prestashop en kan ik niet zomaar aanpassen. Het is namelijk geen platte query, maar wordt opgebouwd via een Prestashop library om de filters om te zetten naar een query. Ik wil eerst snappen wat er aan de hand is, daarna kan ik werken aan een fix in de prestashop code of MySQL configuratie.

Wie kan mij helpen waar ik de oorzaak moet zoeken?

[BCC]

Je explain zegt weinig over de query mbt tot indexen? Zijn er indexen of heb je die niet gepaste?

Al die joins met ps_feature_product fp_3
ON ( p.id_product = fp_3.id_product )
Hebben wel baat bij een index op ps_feature_product (product_id, feature_value) of iets dergelijks.


Hoeveel ram heeft mysql tot z’n beschikking want het klinkt alsof hij naar disk aan het swappen is oid (temp tables op disk). En wat zegt de slow query log?

[ Voor 58% gewijzigd door BCC op 15-05-2021 21:12 ]

[Twazerty]

De server heeft ongeveer 4GB ram in gebruik. Dus er is nog grofweg 4GB vrij voor processen om te pakken. Tijdens het draaien van deze query blijft het geheugengebruik van het mariadb proces op 8,4%. Dit stijgt of daalt niet. Enkel de CPU gaat naar 100% (1 core vol belast) voor een minuut of 3.

De explain heb ik in de openingspost toegevoegd als tabel. Als het daar nu niet in staat, waar haal ik die informatie vandaan?

[epic007]

Ik ben geen SQL guru, maar waarom wordt de tabel ps_feature_product 4x gejoind?
1x zou toch voldoende moeten zijn?

Je kan in je where steeds fp.id_feature_value gebruiken volgens mij.

En je joint ze nog een keer na de group by? En daar filter je de data nog een keer, terwijl je de juiste records al gefilterd hebt.

[ Voor 25% gewijzigd door epic007 op 15-05-2021 21:19 ]

[vegetoot]

Volgens mij maakt het uit of je het filter op de id_feature_value (regels 40 t/m 43) in de join doet, of zoals nu in de where clause. Je gaat nu eerst álle productfeatures op alle relevante ps-producten joinen en dan pas bepalen welke features je wil laten zien. Ik zou (als ik de query niet teveel wil herschrijven) regel 40 t/m 43 in opnemen als join on
LEFT JOIN ps_feature_product fp
ON p.id_product = fp.id_product
AND fp_1.id_feature_value = 149

[Twazerty]

SHOW INDEX from ps_feature_product:

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
ps_feature_product	0	PRIMARY	1	id_feature	A	72	""	""	""	BTREE	""	""
ps_feature_product	0	PRIMARY	2	id_product	A	26844	""	""	""	BTREE	""	""
ps_feature_product	0	PRIMARY	3	id_feature_value	A	26844	""	""	""	BTREE	""	""
ps_feature_product	1	id_feature_value	1	id_feature_value	A	13422	""	""	""	BTREE	""	""
ps_feature_product	1	id_product	1	id_product	A	8948	""	""	""	BTREE	""	""

De reden dat de query nu zo in elkaar zit komt omdat het door de ontwikkelaars van prestashop zou is gebouwd. Ik kan niet zomaar de query op zijn kop zetten. Als de query niet deugt kan ik wel een bug report inschieten bij prestashop (Open source)

[MainframeX]

Twazerty schreef op zaterdag 15 mei 2021 @ 21:12:
De server heeft ongeveer 4GB ram in gebruik. Dus er is nog grofweg 4GB vrij voor processen om te pakken. Tijdens het draaien van deze query blijft het geheugengebruik van het mariadb proces op 8,4%. Dit stijgt of daalt niet. Enkel de CPU gaat naar 100% (1 core vol belast) voor een minuut of 3.

De explain heb ik in de openingspost toegevoegd als tabel. Als het daar nu niet in staat, waar haal ik die informatie vandaan?

Kan je ook eens kijken waar de cpu precies druk mee is? Als je bijvoorbeeld op de cli kan komen zou je eens top kunnen draaien, vanuitgaande dat je linux draait. Met name de waarden 'us' 'sy' en 'wa' zijn interessant. Als het MySQL proces bijvoorbeeld hoog in cpu staat en dit tegelijkertijd een hoog getal bij 'sy' of 'wa', dan acht ik de kans groot dat er temp tabellen naar de disk geschreven worden (niet ondenkbaar met joins). Vooral 'wa' is een probleem; dat staat voor wait average en houdt in dat de cpu onder andere op io wacht (vanwege trage io).

Ik vind overigens de waarden voor 'tmp_table_size' en 'max_heap_table_size' wel wat aan de lage kant voor een ecommerce site. Het kan voldoende zijn, maar misschien ook niet. Om het checken kan je show global status like 'Created_tmp%' draaien in MySQL console. Als de waarden hoog oplopen bij aanroepen van de query, dan moeten deze variabelen omhoog.

[Twazerty]

@vegetoot als ik je suggestie doorvoer krijg ik wel instant resultaat op de query. Thanx voor de hint. Dat geeft mij een idee voor een oplossingsrichting (of gewoon een bug report).

@MainframeX, dit zijn de getallen tijdens het draaien van de query:

code:

top - 21:32:55 up 84 days, 18:03,  2 users,  load average: 1.39, 1.06, 0.80
Tasks: 183 total,   1 running, 182 sleeping,   0 stopped,   0 zombie
%Cpu(s): 36.0 us,  1.7 sy,  0.0 ni, 62.0 id,  0.0 wa,  0.0 hi,  0.2 si,  0.2 st
KiB Mem :  8008864 total,   284100 free,  2475080 used,  5249684 buff/cache
KiB Swap:  1048572 total,   592944 free,   455628 used.  4262272 avail Mem

Dit zijn de getallen in rust:

code:

top - 21:34:20 up 84 days, 18:05,  2 users,  load average: 0.73, 0.98, 0.80
Tasks: 180 total,   1 running, 179 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.7 us,  0.5 sy,  0.0 ni, 98.7 id,  0.0 wa,  0.0 hi,  0.1 si,  0.1 st
KiB Mem :  8008864 total,   351812 free,  2403628 used,  5253424 buff/cache
KiB Swap:  1048572 total,   592944 free,   455628 used.  4333644 avail Mem

Misschien zit je warm. Ik merk nu dat de betreffende query soms wel instant antwoord geeft, of antwoord geeft binnen 30 seconden ipv 3 minuten. De waarden lopen aardig op en dalen vervolgens niet:

Table	Aantal records
Created_tmp_disk_tables	5689
Created_tmp_files	5
Created_tmp_tables	22541

Nu wordt het getal niet enkel veroorzaakt door deze ene query. Ik zal de waarden is wat opkrikken.

Edit: beide waarden opgekrikt van 32M naar 128M, maar geen verschil.

[_JGC_]

Ik ga er even vanuit dat je MyISAM gebruikt ipv InnoDB, wat is je key buffer size?

[Twazerty]

@_JGC_ , de key buffer size staat op

code:

SELECT @@key_buffer_size;
134217728

zie dat alle tablleen op InnoDB staan. Kijk ik nog beter zie ik dat sommige tabellen op MyISAM staan. De tabel ps_feature_product staat op InnoDB

[ Voor 45% gewijzigd door Twazerty op 15-05-2021 22:33 ]

[_JGC_]

@Twazerty Mixen van engines zou ik niet doen. Je key buffer is nu de standaard 128MB, zou voldoende moeten zijn voor MyISAM. Voor InnoDB doet die niks.

Aangezien je InnoDB gebruikt, wat is je innodb buffer pool size?

Verder iets om rekening mee te houden: veel software schrijft bagger queries die daarnaast ook nog vaak uitgevoerd moeten worden. Bij oudere versies van MySQL kwam dat in de query cache. Sinds MySQL 5.7 staat query cache standaard uit.

[Twazerty]

@_JGC_ , dat snap ik, echter heb ik daar niet voor gekozen, dat is wat prestashop zelf doet.

code:

SELECT @@innodb_buffer_pool_size/1024/1024/1024;
1.000000000000

Ik heb al verschillende modulebouwers op hun vingers getikt vanwege heel veel duplicate queries. Enkele modulebouwers hebben verbeteringen doorgevoerd om het aantal (duplicate) queries terug te dringen. Ik denk dat mijn voorbeeld in ieder geval in de categorie inefficiënte query valt.

Edit: we hebben gisteren een vervangende module aangeschaft voor de filters, echter die heeft precies hetzelfde probleem , is gewoon een kopie van de versie die prestashop levert. Ik heb daar nu een support ticket aangemaakt met de suggestie voor een betere query. Zij hebben er meer baat bij dat hun versie beter werkt dan de standaard module. Ik hoop dat ze aan de slag gaan ermee.

Als ik het probleem alvast kan oplossen door bv database tuning dan heeft dat de voorkeur.

[ Voor 34% gewijzigd door Twazerty op 15-05-2021 23:57 ]

[Falcon]

epic007 schreef op zaterdag 15 mei 2021 @ 21:14:
Ik ben geen SQL guru, maar waarom wordt de tabel ps_feature_product 4x gejoind?
1x zou toch voldoende moeten zijn?

Je kan in je where steeds fp.id_feature_value gebruiken volgens mij.

En je joint ze nog een keer na de group by? En daar filter je de data nog een keer, terwijl je de juiste records al gefilterd hebt.

@Twazerty Kijk hier ook eens naar.

[DJMaze]

De query opbouw rammelt aan alle kanten.
Dit is trouwens maar 1 query van 2.
Dit is de "N produkten gevonden", en dan is er natuurlijk nog de zelfde voor het tonen van N produkten op pagina X.

De dubbele where is natuurlijk ook zinloos.

Zit er een DBAL/ORM bug in?

[Twazerty]

DJMaze schreef op zondag 16 mei 2021 @ 11:22:
De query opbouw rammelt aan alle kanten.
Dit is trouwens maar 1 query van 2.
Dit is de "N produkten gevonden", en dan is er natuurlijk nog de zelfde voor het tonen van N produkten op pagina X.

De dubbele where is natuurlijk ook zinloos.

Zit er een DBAL/ORM bug in?

Voor de geinteresseerde is dit de broncode waar de query opgebouwd wordt:
https://github.com/Presta...7.1/src/Adapter/MySQL.php
en wordt vanuit hier aangestuurd voor de filters (regel 147):
https://github.com/Presta....1/src/Product/Search.php

het koste mij ook wat moeite om te volgen wat daar allemaal gebeurd.

De beste hint voor een verbetering is momenteel de hint van vegetoot. Als dat ingebouwd kan worden door een andere modulebouwer, die een verbeterde versie heeft van bovenstaande module, zijn wij gered. Ik ga het zelf ook proberen, maar ik ben geen prestashop expert, geen php guru en ook geen SQL guru

[Voutloos]

epic007 schreef op zaterdag 15 mei 2021 @ 21:14:
Ik ben geen SQL guru, maar waarom wordt de tabel ps_feature_product 4x gejoind?
1x zou toch voldoende moeten zijn?

Je kan in je where steeds fp.id_feature_value gebruiken volgens mij.

Dat kan niet. Een poging voor de niet-gurus : Je zoekt dus 4x op een waarde. Die waardes staan weliswaar in dezelfde kolom, maar niet in dezelfde cel. Met de joins zet je data 4x naast elkaar en kan je de filters tegelijk toepassen.

En daar filter je de data nog een keer, terwijl je de juiste records al gefilterd hebt.

Ja, dat is bij deze count query pure verspilling. Wellicht komt hierna een query die de details wel ophaalt en dan wil je wel correct joinen.

vegetoot schreef op zaterdag 15 mei 2021 @ 21:22:
Volgens mij maakt het uit of je het filter op de id_feature_value (regels 40 t/m 43) in de join doet, of zoals nu in de where clause. Je gaat nu eerst álle productfeatures op alle relevante ps-producten joinen en dan pas bepalen welke features je wil laten zien. Ik zou (als ik de query niet teveel wil herschrijven) regel 40 t/m 43 in opnemen als join on
LEFT JOIN ps_feature_product fp
ON p.id_product = fp.id_product
AND fp_1.id_feature_value = 149

Dat moet een INNER JOIN zijn voor hetzelfde gedrag. Doorgaans doet mysql/mariadb deze tweak (condities naar join halen) al automagisch voor je overigens. Dat dat niet gebeurt is een teken dat de engine een heel ander ‘beter’ plan denkt te hebben.

[Hydra]

Ik heb geen antwoord voor je maar ik vind de manier van queries opbouwen nogal raar.

Bij een join tussen 2 tabellen bouw je een Cartesisch product op, je vermenigvuldigt dus 2 sets met elkaar tot een matrix. Een join van een tabel met 4 elementen met een tabel met 3 elementen levert een matrix op met 4*3 = 12 elementen. Elke join is dus een vermenigvuldiging.

Databases zijn hier over het algemeen vrij slim in, en gaan dus proberen om in elke 'stap' eerst het resulterende product terug te brengen (dus wat niet matched weg te laten) via de IDs waarop je joined. Maar als je het te complex maakt, of zaken niet opgehaald kunnen worden via een index, kan dit toch nog steeds lastig worden.

Ik vermoed dat dit het is, dat het ook met 3 filters als een 'slechte' query is, maar elke stap extra doordat het exponentieel stijgt, je er dan pas last van krijgt. Hoe lang duurt 3 filters? Paar seconden?

Ik weet niet in hoeverre je de hier iets aan kunt aanpassen, maar de stijging van complexiteit van extra filters zou linair moeten zijn (extra AND expresses in de where) en niet extra joins op moeten leveren. Het lijkt mij dus logischer om eerst een sub-select te doen om het resultaat terug te filteren en pas daarna de tabellen in te joinen om tot het volledige resultaat te komen.

Een DISTINCT in je query is sowieso al een code smell. Dan is je DB vaak heel veel data aan 't ophoesten waarvan je er veel weggooit.

[_JGC_]

Waarschijnlijk is het sneller om voor elk filter de product_ids uit die feature_values tabel te halen, die per filter in een array zetten en dan in PHP die arrays aan array_intersect op te voeren.
Dat werkt naar mijn weten beter dan 4x de filter tabel joinen.

Ja het kost je per filter een extra query, maar de manier waarop dit gebeurt zadelt MySQL met een heleboel werk en mogelijke temporary tables op.

Verder, mocht je de huidige manier wel voortzetten: LEFT JOIN vervangen door een gewone (INNER) JOIN. Mogelijk doet MySQL dat al automatisch overigens.

[pedorus]

Twazerty schreef op zaterdag 15 mei 2021 @ 22:30:
Kijk ik nog beter zie ik dat sommige tabellen op MyISAM staan.

Werk je in een museum, of waarom kunnen deze tabellen niet naar InnoDB (of anders Aria)? https://mariadb.com/kb/en...es-from-myisam-to-innodb/

De query is niet heel logisch, maar daar kun je kennelijk lastig iets aan veranderen. Is er een reden dat er handmatig random settings zijn getuned, zoals join_buffer_size, in plaats van dat MariaDB dat lekker zelf mag bepalen? (En wat is dan de waarde van optimize_join_buffer_size ?) Er is waarschijnlijk een reden dat die setting niet genoemd staat op https://mariadb.com/kb/en...-for-optimal-performance/

Kennelijk werkt de query wel snel tot 3 verschillende filters, wat is er anders aan het query plan met meer filters? Doet misschien optimize/analyze table nog iets?

En als niets werkt, dan is het een kwestie van langlopende queries van dit type automatisch snel killen.

[mbe81]

In één van de eerste reacties stond het volgende:

BCC schreef op zaterdag 15 mei 2021 @ 21:02:
Al die joins met ps_feature_product fp_3
ON ( p.id_product = fp_3.id_product )
Hebben wel baat bij een index op ps_feature_product (product_id, feature_value) of iets dergelijks.

Heb je dat nog geprobeerd?

Edit:
Wellicht zou het zelfs een index op (feature_value, product_id) moeten zijn?

[ Voor 10% gewijzigd door mbe81 op 16-05-2021 23:37 ]