Performance meting/Analyseren waar het probleem zit van een bepaald rapport?

Door nkamp Kamp van de op 21-07-2017 21:47 gewijzigd op 21-07-2017 21:59

2.945 views

Hallo,

De titel is misschien niet helemaal duidelijk, ik weet ook niet goed hoe ik het anders in het kort moet duiden.

In ieder geval ik ben bezig met een project wat al enige jaren bestaat. Nu worden er aanpassingen gedaan, deze niet door mij, waarbij een calcutie 3 uur+ duurt wat natuurlijk onacceptabel is.

Er komen dan ook klachten van de gebruikers, wat begrijpelijk is natuurlijk.

Nu ben ik er ingedoken vooral door te te googlen.

Wat ik gedaan heb is een tabel:
- slow_log
- general_log
aangemaakt.

Vervolgens de volgende variabelen ingesteld:
# Variable_name, Value
'general_log', 'ON'
'log_output', 'TABLE'
'log_queries_not_using_indexes', 'ON'
'log_slow_admin_statements', 'OFF'
'log_throttle_queries_not_using_indexes', '0'
'long_query_time', '10.000000'
'min_examined_row_limit', '0'
'slow_query_log', 'ON'
'slow_query_log_file', 'TABLE'

Er draait verder niets op de server enkel dit rapport.

1. aantal records in de slow_log tabel:
Het eerste waar ik van schrik is dat er ruim 6800 records worden weggeschreven in de 'slow_log' tabel.
Dat betekent volgens mij dat er vanuit PHP ruim 6800 keer een query afgevuurd wordt! Waarbij ook heel vaak dezelfde query weer wordt aangeroepen.

Mijn eerste reactie: "Geen wonder dat dit 3 uur duurt, dat is veel te veel!!!" Maar is mijn conclusie gerechtvaardigd?
(Ik heb geen relevante ervaring hiermee en ook geen referentie kader. Ik zelf probeer altijd zo min mogelijk database access uit te voeren.)

2. max query time - indexen:
The max. query_time 00:00:14.91xxx, 5 met 12.xxx, 3 met 11.xxx en 5 met 10.xxx seconden.
Aangezien er geen queries zijn die 30min. duren of langer en dat er dan zo veel records weggeschreven worden, heeft dan te maken dat er geen indexen op de tabellen zitten of niet zodanig dat ze gebruikt worden in de queries.

dit concludeer ik op basis van dat de 'long_query_time' op 10 staat, wat volgens mij wil zeggen 10 sec. Er worden ruim 6800 records weggeschreven. Dit komt volgen mij doordat ik de 'log_queries_not_using_indexes' = ON staat, anders zouden er maar 14 records in de slow_log tabel weggeschreven worden.

Nu ben ik geen database expert maar ik heb via de slow_log queries bekeken via de sql_text. En heb ik bv. als er een query was die op datum zoekt in de where clause, een index op datum gezet. Even zo als er een where clause was die op WHERE datum =... AND jur = ... AND bu =... AND scenario = ... zocht, heb ik deze in een index gezet.
Echter dit brengt niets. Er worden nog bijna net zoveel records weggeschreven! En het duurt nog net zo lang.

3. wegschrijven van records
Daarnaast worden er ook veel records weggeschreven nadat er berekeningen zijn uitgevoerd. Telkens in sets van 500 volgens mij.
Nu weet ik op dit moment niet of in de code zo gedaan is of dat MySQL ze zelf ophakt in sets van 500.
Wat ik wel vreemd vindt, althans wat ik in de slow_log terug vindt is de syntax:


SELECT table (veld1, veld2,...)
INSERT INTO tabel (veld1, veld2,...) 
SELECT '20170630', waarde1',' waarde2',...
UNION ALL  SELECT '20170630', waarde1',' waarde2',...
UNION ALL  SELECT '20170630', waarde1',' waarde2',...
UNION ALL  SELECT '20170630', waarde1',' waarde2',...

Maakt MySQL dit zelf zo aan (in zijn execution_plan), UNION ALL heb ik nooit gezien zeker niet op deze manier voor INSERT INTO? (Eerlijk gezegd dit kan ook aan mij liggen)

4. Wat kan ik nog meer analyseren?
- Nu zit ik met: hoe nu verder? Mag ik de conclussie al maken "Dit moet herschreven worden"? Want 6800 keer een database access, dat gaat niet veel korter worden.
- Of is mijn analyse met de slow_log niet helemaal objectief, ik bedoel doe ik nog iets niet goed? Moet ik nog meer bekijken? moet ik nog meer analyseren?
- Of moet/kan ik met betere indexen nog veel tijdwinst halen en dan niet 30 min maar ruim 2,5 uur of meer?

Nico

Yoop Overmaat

22-07-2017 00:14

Je laatste conclusie, dit moet herschreven worden is de juiste. Plaats onder de pagina een php-script laat dat het rekenwerk doen & daarna wegschrijven naar de mysql server.

Ben van Velzen

22-07-2017 01:20

Goed, we gaan het rijtje af:

1. Dit zegt niets. Het feit dat een query vaak wordt uitgevoerd is in veel gevallen logisch. Dat de snelheid omhoog moet door optimalisatie is echter evident.
2. Op alle velden waar je op zoekt indexes zetten heeft zelden tot nooit zin. Kijk met de hulp van EXPLAIN waar je bottlenecks werkelijk zitten.
3. De eindconclusie moet hier zijn dat niet het aantal toegangen relevant is, maar de toegangsduur. Een normale query doet enkele milliseconden tot hooguit enkele seconden over het werk dat het moet doen. Dit kan deels te maken hebben met je queries, deels met je serverconfiguratie. Zonder werkelijke data is hier niets over te zeggen.

Thomas van den Heuvel

22-07-2017 16:08

Maar het rapport lijkt mij een soort van proces waarbij een scripting-/programmeertaal betrokken is? Misschien heb je wel een kathedraal van een database, maar als je via code deze op een super ineffficiënte manier benadert (de verkeerde vragen stelt of de vragen op een verkeerde manier stelt) dan is het resultaat nog steeds dat de rapportage super inefficiënt totstand komt, goed opgezette database of niet.

Het zou dus zomaar kunnen zijn dat de code van de rapportage brak is. Door bijvoorbeeld, ik noem maar een dwarsstraat, queries die in loops worden uitgevoerd (niet te verwarren met resultaten die in loops worden opgehaald).

Eerst zul je dus een analyse moeten maken in je rapportage(s) om na te gaan wat er precies zolang duurt, en daarna waarom.

Je zou het e.e.a. kunnen benchmarken in het script/de scripts zelf zodat je "live" ziet wat er gebeurt. Hierbij moet je wel waken dat deze benchmarking zelf de prestaties niet teveel beïnvloeden uiteraard.

Willem vp

22-07-2017 19:56

Het zou ook nog kunnen dat je een index hebt op een kolom, maar dat de query daar geen gebruik van kan maken. Als je bijvoorbeeld iets hebt als

WHERE datum = CONVERT_TZ(@eenofanderedatum, 'UTC', 'Europe/Amsterdam')

dan zorgt de convert_tz ervoor dat een index op het veld datum niet gaat werken. Door dergelijke constructies te herschrijven heb ik ook wel eens queries kunnen versnellen van 30 minuten naar 20 seconden.

De eenvoudigste manier om dit soort dingen te vinden is door je queries te EXPLAINen.

nkamp Kamp van de

22-07-2017 22:26 gewijzigd op 22-07-2017 22:32

Hallo,

Bedankt allemaal voor het mee denken. Ik was al via byte.nl bij EXPLAIN uitgekomen. Ik moet me daar dan meer in verdiepen. Toen ik het las had ik niet meteen het idee dat dit mij ging helpen dit probleem op te lossen maar ik hoop door mij er in te verdiepen dat ik toch meer te weten kom waar de bottleneck zit.

Waar ik ook nog aan dacht is de PHP code via xDebug te tracen. Ik heb dit een tijdje geleden ook al eens gedaan om mij in te werken en zien wat er allemaal wanneer aangeroepen wordt ed.
Echter dan krijg ik gelijk bestanden van een aantal gig die met geen editor meer te openen zijn.

Is het een idee om het hiermee te achterhalen?
Heeft iemand hier ervaring mee hoe je dit insteld zodat je niet van die enorme bestanden krijgt die je met geen editor meer kunt openen?

** EDIT**
Ik zie nu een artikel op sitepoint mbt. EXPLAIN en ik heb het gisteren verkeerd begrepen! en ook iets verkeerd gedaan!!!!

Bedankt.

Nico

Nico

nkamp Kamp van de

26-07-2017 15:41 gewijzigd op 26-07-2017 15:52

Hallo,

Eergisteren ben ik erachter gekomen waar het probleem in zit, het ophalen van data via 3 geneste Foreach:
<?php
Foreach($this->Jur as Jur) { //5X
Foreach($this->BX as BX) { //6X
Foreach($this->Scene as SceneName) { // 49X
// voor ieder scene wordt er uit drie tabellen data gehaald.
// Deze worden dan in een array geschreven die dan weer bij de aggregatie/totalen gebruikt worden. Het aantal records loopt uiteen van enkele honderden tot 30.000 bv per jur/bx/scene

// Aggregatie van de gegevens van de 3 queries - Dit is niet de bottleneck
// Totalen bepaald - Dit is niet de bottleneck
}
}
}
?>

Mijn conclusie is dat het zit in iedere keer het ophalen van de data voor een specifike jur-bx-Scene data 5x6x49.
Dit zijn relatief 3 eenvoudige queries, enkelvoudige tabel, gewoon op discrete waarden.

WHERE JUR like 'JUR%' // De LIKE is ook niet het probleem.
AND BX like 'BX%'
AND Scene = 'xxxxxx'

Wel met in iedere query meerder malen dat de sum functie in de select gebruikt wordt.

ALTERNATIEF (althans waar ik aan denk):
Alles in één keer op te halen. De executie tijd van het PHP script gaat drastisch omlaag: van 3 uur naar rond de 10 min op mijn VDI omgeving! (Op de ONT server duurt het een uur ongeveer)

WHERE JUR in ('value',...)
AND BX in ('value',...)
AND Scene in('value',...)

Vervolg stappen nu:
Probleem heeft zich nu verplaatst van de vele min of meer 'unieke' queries naar het array. Nu staat alles in 3 grote afzonderlijke array's (met tienduizenden elementen).

voorheen was de dataset opgesplitst per:Jur-BX-Scene, dan waren het honderd enkele duizend array elementen)

Hoe kan ik nu snel in het array zoeken op jur-bx-scene en dan de bewerkingen uitvoeren?
Het WAS voorheen dit bv.:
<?php
while ($row = ...)
$arrayxxxx[$index]['Fixed_value'] = $row['Fixed_value'];
$arrayxxxx[$index]['Var_value'] = $row['Var_value'];
etc.

$index= $index + 1;
?>

wat ik heb omgezet naar:
<?php
while ($row = ...)
$arrayxxxx[$jur][$bx][$scene][$index]['Fixed_value'] = $row['Fixed_value'];
$arrayxxxx[$jur][$bx][$scene][$index]['Var_value'] = $row['Var_value'];

$index= $index + 1;
etc.
?>

Op het huidige array wordt een bewerking gedaan, die ik (nog) niet weet hoe ik die moet omzetten naar de nieuwe situatie met [jur][bx][scene].:
<?php
Foreach(array_keys($arrayxxxx) as $i) {
$Tot += $arrayxxxx[$i][Fixed_Value']
}
?>

Het gaat dus om een array met 40.000 elementen. Wat ik mij nu afvraag:
- is dit de juiste weg die ik ben ingeslagen? Ook wat ik toegevoegd heb Jur-bx-scene om array elementen te kunnen onderscheiden.
- Het hoe kun je snel door zo'n array itteren als je bepaalde gegevens zoekt?

Ik hoop dat ieman mij iets meer licht kan verschaffen in deze situatie zodat ik het goed kan oplossen.

Bedankt,

Nico

Ben van Velzen

26-07-2017 16:14

Zo'n geneste foreach oplossing voelt in combinatie met de WHERE die je als voorbeeld geeft als iets dat je gewoon in 1 query zou kunnen oplossen. Dat zou voor mij altijd het eerste zijn: zoveel mogelijk data op een logische manier joinen ipv voor elke combinatie aparte queries draaien.

Reageren

Inloggen om te reageren