Week 6 : Trage Query’s en Array Caching
Om Ingelicht te optimaliseren en ervoor te zorgen dat bezoekers hun pagina’s sneller zouden laden, heb ik deze week alle trage query’s aangepast en caching op verschillende plaatsen ingebouwd.
Slow Query Log
Om trage query’s te vinden bestaat er de mogelijk om met MySQL deze te loggen in een aparte Slow Query Log File. Hiervoor moet je enkel 2 regels toevoegen in het “my.cnf” configuratie bestand van MySQL.
log-slow-queries = /var/log/mysql/mysql-slow.log long_query_time = 1
In de eerste regel geef je de locatie op waar je het log-bestand wil opslaan. De tweede regel geeft aan hoelang query’s maximaal mogen duren vooraleer ze gelogd worden. In dit geval worden alle query’s gelogd die langer duren dan 1 seconde. MySQL zal dan een logbestand opbouwen waarin je de trage query’s kan terugvinden. Hierbij kan je ook zien hoelang deze query’s geduurd hebben en hoe dikwijls deze uitgevoerd geweest zijn.
Na verschillende uren testing had ik al enkele tragere query’s in mijn logfile staan. Zo had ik een query met subquery’s die meer dan 2 seconden duurde en ik heb kunnen ombouwen door middel van JOIN’s tot een maximale duur van 0,065 seconden. Zo heb ik toch een 10-tal verschillende query’s kunnen aanpassen waardoor je ook wel merkt dat de website iets sneller zal laden.
Omdat de MySQL Slow Query Log je database iets kan vertragen, kan je deze logging best afzetten nadat je query’s gecontroleerd en aangepast zijn. Vergeet dit daarom niet tijdig terug uit te schakelen!
Array Caching
Omdat we toch nog zeer veel query’s moeten uitvoeren op één pagina konden we deze best cachen. Zo haal ik bijvoorbeeld één artikel op en worden hier de bijhorende tags, afbeeldingen en zoektermen etc. bij opgehaald. Al deze gegevens worden in één array geplaatst, bijvoorbeeld $artikel. Hierna wordt deze array geserialized en opgeslagen in een cache folder op de webserver. Als bestandsnaam gebruik ik een uniek cache-id opgebouwd aan de hand van de url van de huidige pagina.
Net voor we onze query’s uitvoeren doe ik een controle of er een cache-bestand bestaat aan de hand van het uniek cache-id. Indien er een cache-bestand bestaat, haal ik de gegevens terug op en unserialize ik de array. Indien er geen cache bestaat, start ik weer van vooraf aan door het serializen en opslaan van de array.
In de webadmin verwijderen we onze caches zodra we een artikel aanpassen. Je kan er natuurlijk ook voor zorgen dat je cache automatisch verwijderd wordt indien deze ouder zijn dan x-aantal minuten of uren.
En we leren weer bij!
Binnenkort mijn queries ook eens onder de loep nemen.
Optimalisatie wordt maar al te vaak vergeten, goed bezig!
Zeer interessant onderwerp !!
Wist zelfs nog niet dat mySQL zo’n functie had … dus ga het zeker eens gebruiken in de toekomst :)
Wordt er trouwens nog andere zaken gecached op de site of enkel de artikel-details?
@Jente: Optimalisatie wordt inderdaad te vaak vergeten … durf zelfs toe te geven dat het bij ons (spijtig genoeg) te weinig wordt toe gepast.
@Thomas: Er worden uiteraard nog andere pagina’s gecached buiten de artikel details. Denk hierbij aan de tag-pagina’s, het archief, categorie-pagina’s, etc…
Ik probeer zoveel mogelijk (tot bijna alle) pagina’s van caching te voorzien, omdat ik toch wel merk dat de website hierdoor een stuk sneller laadt.
Wat voor mij het interessantste was in je zoektocht naar trage queries zijn de zogenaamde “best practices” die je in boeken en op school leert. Ga er zeker niet van uit dat de queries die het meest logisch lijken of het meest aanleunen bij de boeken over SQL ook direct de beste zijn. Vaak zal je verschillende combinaties moeten uitproberen op vlak van INNER- en LEFT JOINS en het sorteren eventueel eens via PHP afhandelen. Ook voor het leggen van indexen moet je soms buiten het boekje om denken.
Probeer steeds met zo realistisch mogelijke testdata te werken. Veel optimalisaties kan je pas efficient uitvoeren op tabellen van 10.000 rijen of meer.
@Dirk: Inderdaad dat is mijzelf ook opgevallen … De manier zoals we het op school hebben geleerd is daarom zeker niet altijd de beste/efficiëntste manier.