Let op! Deze informatie is bedoeld voor de meer technisch gevorderde lezer. Byte kan hierbij geen technische support leveren. Ben je zelf niet zo technisch? Neem dan contact op met één van onze partners, te vinden op onze Partnerpagina.

Is je database traag of merk je dat er een query automatisch is afgebroken? Dan is het verstandig om te kijken of je de database kunt optimaliseren. In dit artikel geven we tips & tricks hoe je dit soort problemen kunt opsporen en kunt oplossen. In het Service Panel kun je de slow querie logs inzien onder de button MySQL Slow Queries.

Afgebroken of trage database queries

Als je database query er te lang over doet loop je het risico dat je site vastloopt, omdat de andere queries hierdoor opgehouden worden. Ook kan de database server door te zware/slecht ontworpen queries overbelast raken, waardoor ook andere sites op ons cluster trager of onbereikbaar worden. Daarom worden zulke queries door ons afgebroken.

In het Service Panel kun je de slow querie logs inzien onder de button MySQL Slow Queries. Hier worden queries die langer dan 2 seconden nodig hadden gelogd en bevatten de volgende informatie:

  • database
  • use
  • query tijd
  • lock tijd
  • de query zelf
  • rows evaluated (rijen bekeken)
  • rows updated (rijen geupdate)
  • rows returned (rijen teruggegeven aan de client)

Mogelijke oorzaken van trage database queries

De meest voorkomende oorzaken van een trage database query zijn:

En in mindere mate:

Je kunt opvragen welke queries de server aan het draaien is met het SQL commando show processlist. Je kunt dit ook opvragen in PHPMyadmin door bovenaan op server te klikken en dan te klikken op het tabblad processes. Je krijgt dan een lijst met alle processen te zien. De status in de een na laatste kolom geeft je meer informatie over het proces.

Wanneer er staat Sending data is er teveel data, waarschijnlijk worden er vele gigabytes heen en weer gepompt. Dit duidt over het algemeen op een JOIN met te weinig (foute) condities. Staat er Copying to temp table? Dan zijn er zijn teveel joins. De-normaliseer naar minder tabellen.

Meer informatie over de query is te verkrijgen met de explain opdracht van MySQL.

Stel, je hebt onderstaande tabel hebben om de responsetijd van een aantal websites te monitoren:

mysql>describeresultaten;
Field Type Null Key Default Extra
datum datetime NO MUL 0000-00-00 00:00:00
site varchar(255) NO MUL
http_code char(3) YES NULL
responsetijd float YES NULL
online int(11) YES NULL

Als je nu alle resultaten wilt hebben met een responsetijd groter dan 4 seconden, voer je deze query uit:

select * from resultaten where responsetijd > 4;

Om de tien resultaten met de langste responsetijd te vinden, gebruik je dit commando:

select * from resultaten order by responsetijd desc limit 10;

Onderzoek de eerst query met het explain commando:

mysql>explain select * from resultaten where responsetijd > 4;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE resultaten ALL NULL NULL NULL NULL 1461450 Using where

Er is geen index op ‘responsetijd’. Je kan de query versnellen door een index toe te voegen:

mysql>alter table resultaten add index responsetijd_index (responsetijd);Query OK,1461450 rows affected (52.49sec) Records: 1461450 Duplicates: 0Warnings: 0

De eerste query doet er nu nog maar 0.3 seconden (ipv 2 seconden) over, de tweede doet er nu 0 seconden over (ipv 3,5 seconden). Dit omdat er nu een index wordt gebruikt:

mysql>explain select * from resultaten where responsetijd > 4;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE resultaten range responsetijd_index responsetijd_index 5 NULL 26673 Using where

Databasedenormalisatie

In het algemeen is het aan te raden een database ontwerp te normaliseren. Dit omdat anders gegevens meerdere keren in de database komen te staan en tegenstrijdig kunnen worden. Normalisatie zal in het algemeen leiden tot meer, maar kleinere tabellen en tot meer JOINS in queries.

De bovenstaande tabel kan je normaliseren door het attribuut ‘site’ in een aparte tabel sites (site_id, naam) op te slaan. In de tabel resultaten vervang je de kolom site door een kolom site_id. Voordelen hiervan zijn:

  • minder opslagruimte nodig
  • als de site van naam verandert, hoeft er maar één rij aangepast te worden.

Nadeel hiervan is dat iedere query waarbij de naam van de site van belang is, een JOIN moet doen met de tabel sites. Queries met te veel JOINS kunnen erg langzaam worden, dus in dat geval is het aan te raden weer te denormaliseren.

30