Ga naar de inhoud 
MySQL traag zoeklogboek laat me in hosting zien welke query's tijd opslokken, hoe vaak ze voorkomen en waarom ze de boel vertragen. Ik laat je specifieke stappen zien om het logboek te activeren, te analyseren en query's opnieuw op te bouwen zodat pagina's sneller laden en serverresources efficiënter werken.
Centrale punten
- Activering en stel drempelwaarden verstandig in
- Evaluatie met pt-query-digest en mysqldumpslow
- Metriek interpreteren: Query_time, Lock_time, Rows_onderzocht
- Afstemmen door indexen, EXPLAIN en herschrijven
- Automatisering en monitoring in hosting
Wat doet het logboek voor langzame query's in hosting?
Hosting betekent gedeelde bronnen, dus elke milliseconde per query telt. Ik gebruik het logboek om query's te vinden die langer lopen dan een gedefinieerde limiet en ik zie kerncijfers zoals Query_time, Lock_time, Rows_sent en Rows_examined voor elke query. Deze cijfers laten me zien of er een ontbrekende index, een ongunstige join of een volledige tabelscan achter zit. Vooral op servers met meerdere sites kan een enkele slechte query de CPU en I/O zwaar belasten. Vervolgens geef ik prioriteit aan de query's met de hoogste totale tijd, omdat daar de grootste invloed op laadtijd en serverbelasting ligt.
Activerings- en zinnige drempelwaarden
StartIk kan runtime of permanent via my.cnf, afhankelijk van de toegang in de hosting. Voor snelle tests zet ik het logboek tijdelijk aan en stel ik long_query_time in op een waarde die past bij het verkeer en de hardware. Ik ga vaak naar 0,1 seconden voor zwaar gebruikte sites, maar houd de loggrootte in de gaten zodat I/O niet onnodig groeit. Als directe bestandstoegang beperkt is, gebruik ik de prestatie schema opties van de MySQL shell om rapporten te genereren. Na de fijnafstelling schrijf ik de definitieve instellingen naar het configuratiebestand en herstart de service.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
Permanent Ik stel opties in zoals log_throttle_queries_not_using_indexes en log_slow_admin_statements zodat het logboek bruikbaar blijft en niet explodeert. Ik documenteer elke waarde, bijvoorbeeld waarom long_query_time 0,5 of 0,1 seconden is. Hierdoor kan ik het later verfijnen. In gedeelde omgevingen bespreek ik de activering vaak met de provider of gebruik ik hun panel. Ik koppel elke activering aan een startdatum om de effecten in monitoring en metrics netjes te kunnen vergelijken.
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
lange_query_tijd = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = 1
Traag logboek effectief evalueren
Ruwe gegevens zijn lawaaierig, dus ik vat ze samen met pt-query-digest en sorteer ze op totale tijd over een zinvolle tijdsperiode. Zo herken ik patronen, zeer variabele queries en queryfamilies die alleen per parameter variëren. Ik controleer de verdeling, niet alleen het gemiddelde, omdat uitschieters echte gebruikersproblemen veroorzaken. Voor een snel overzicht helpt mysqldumpslow me om de tien traagste groepen te zien. Ik krijg meer diepte door gebruik te maken van tijdvensters, databasefilters en een export naar een tekstanalyse.
pt-query-digest /var/log/mysql/slow-query.log
pt-query-digest --since '24h' /var/log/mysql/slow-query.log
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log
Handig is ook een blik op andere logs wanneer applicaties of PHP-functies in het spel komen. Hiervoor gebruik ik bestaande logworkflows en bundel ik de resultaten. Deze gids geeft me vaak een inleiding: Logboeken analyseren. Ik synchroniseer de tijdstempels zodat ik verkeerspieken kan vergelijken met querypieken. Hierdoor kan ik zien of cache misses, cron jobs of import jobs de database op hetzelfde moment gebruiken.
Metriek correct interpreteren
Vraag_tijd toont me de pure runtime; ik geef voorrang aan queries van meer dan een seconde. Lock_time geeft wachttijden aan als gevolg van locks, die vaak het gevolg zijn van onnodig lange transacties of grote batches. De verhouding van Rows_examined tot Rows_sent vertelt me of queries te veel rijen bekijken en indices missen. Als het logboek veel „No index use“ vermeldingen bevat, stel ik throttling in en bekijk ik de betreffende tabellen van dichterbij. Het blijft belangrijk om altijd de oorzaak aan te pakken in plaats van het symptoom: Een index op de juiste kolom verslaat elke hardware-upgrade.
| Metriek | Wat ik zie | Maatregel |
|---|---|---|
| Query_tijd hoog | Lange looptijd per versie | EXPLAIN controleren, query herschrijven, index toevoegen |
| Slot_tijd hoog | Wachttijd voor sloten | Transacties verkorten, batchgrootte verkleinen, geschikte isolatie |
| Rijen_onderzocht ≫ Rijen_verzonden | Te veel gescand, te weinig teruggestuurd | Index filterkolommen, maak maakbaarheid |
| Geen index gebruikt | Volledige tabelscan | Index maken, uitdrukking in WHERE vermijden |
Grenswaarden Ik pas het na de eerste week aan zodat ik niet verdwaal in de ruis. Ik verlaag long_query_time in stappen totdat ik genoeg hits heb voor systematische verbeteringen. Ik documenteer elke aanpassing met de datum en reden. Dit houdt de evaluatie gefocust. Waardevolle hits besparen me later dubbel werk.
Praktijk: Query tuning stap voor stap
UITLEGGEN is mijn start voordat ik code verander. Ik zoek naar „type: ALL“, „rijen“ met grote aantallen en „Gebruik filesort“ of „Gebruik tijdelijk“. Functies op kolommen in WHERE of JOIN voorkomen vaak het gebruik van indexen. In plaats daarvan formuleer ik sargable voorwaarden en controleer dan het nieuwe plan. Elke stap moet de rijreductie vroeg en doelgericht uitvoeren.
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- Beter:
CREATE INDEX idx_orders_created ON orders(created_at);
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
JOINs Ik optimaliseer door de join-volgorde te controleren en indices op join-sleutels te matchen. Ik controleer of een samengestelde index WHERE + ORDER BY dekt om filesort te vermijden. Ik stel LIMIT in waar alleen een voorbeeld nodig is. Ik bewaar resultaatcaching op applicatieniveau voor herhaalde, identieke queries met een lage wijzigingsfrequentie. Je kunt hier een uitgebreidere introductie tot indexen en sloten vinden: Indices en vergrendeling.
Indexstrategieën voor CMS en winkels
WordPress, WooCommerce of shopsystemen creëren typische patronen: veel lezen, selectief schrijven, vaak met meta- of producttabellen. Ik analyseer de meest voorkomende routes - startpagina, categorie, zoeken, afrekenen - en plaats indices specifiek op filter-, sorteer- en join-kolommen. Dekkende indices (bijv. (status, created_at, id)) besparen veel gebruik van de tabel. Om naar voorvoegsels te zoeken, gebruik ik geschikte indexvormen of volledige tekst in plaats van LIKE ‚%wort%‘. Ik meet elke indexwijziging voor en na de live run met dezelfde belastingsprofielen.
Groei Ik gebruik kardinaliteit en histogrammen om datasets te controleren zodat ik niet indexeer op zeldzame waarden. Ik houd het aantal indices laag om de schrijfbelasting en geheugenvereisten onder controle te houden. Geconsolideerde samengestelde indices vervangen verschillende individuele indices. Ik regel autovacuüm-achtige taken in MySQL door ze regelmatig te analyseren en ze alleen opnieuw op te bouwen als dat nodig is. Dit houdt de optimiser betrouwbaar.
Serverinstellingen, caching en geheugen
InnoDB Ik bepaal de grootte van de bufferpool op basis van actieve gegevensrecords en indexgroottes, niet op basis van algemene waarden. Ik verhoog deze totdat de grootte van de werkset grotendeels in het geheugen is en de page miss rate daalt. Ik stel tmp_table_size en max_heap_table_size zo in dat er minder tijdelijke tabellen op schijf belanden. Voor schrijfveiligheid en latentie balanceer ik innodb_flush_log_at_trx_commit op de juiste manier voor de applicatie. Op applicatieniveau cache ik frequente resultaten en gebruik ik HTTP caching zodat de database minder aanvragen te zien krijgt.
Hardware en netwerkeffecten worden meegenomen in de diagnose: Trage storage I/O of een overbelaste CPU worden direct herkend door queries. Daarom meet ik IO-wacht parallel met de databasemetriek. Als je meer reserves nodig hebt, plan dan verticale of horizontale schaling met een meetbaar doel. Deze handleiding geeft een compact overzicht van knelpunten, tuning en resources: Hardware en cache. Zo weet ik zeker dat ik niet blindelings aan de verkeerde knop draai.
Concurrency en vergrendeling in hosting
Slot_tijd groeit wanneer lange transacties veel regels raken of wanneer opruimtaken op prime time worden uitgevoerd. Ik verkort schrijfoperaties, splits grote updates op in kleinere batches en verkort zo de wachttijd van locks. Geschikte isolatieniveaus verminderen conflicten zonder de gegevensconsistentie in gevaar te brengen. Ik verlicht hotspots met secundaire indices en geschikte WHERE-voorwaarden zodat minder rijen worden beïnvloed. Ik plan achtergrondtaken in tijdsvensters met weinig verkeer zodat gebruikersacties voorrang krijgen.
Deadlocks Ik analyseer ze aan de hand van terugkerende patronen: dezelfde tabellen, veranderende volgorde, identieke regels. Ik standaardiseer de toegangsvolgorde in code en opgeslagen procedures. Retry logica met jitter lost tijdelijke botsingen op. Waar mogelijk isoleer ik de duurste bewerkingen in wachtrijen. Dit vermindert de variantie aanzienlijk en verhoogt de waargenomen prestaties.
Geautomatiseerde alarmen en workflows
Routine Slaat op actionisme: Ik analyseer het log dagelijks of wekelijks, afhankelijk van het verkeer en de frequentie van releases. Een klein script telt nieuwe hits in de laatste paar minuten en stuurt me een e-mail als de drempelwaarde stijgt. Ik genereer ook regelmatig pt-query-digest rapporten en houd altijd de top 10 in de gaten. Ik houd releasedagen nauwlettender in de gaten. Hierdoor kan ik regressies herkennen voordat gebruikers ze opmerken.
#!/bin/bash
LOG_FILE="/var/log/mysql/slow-query.log".
THRESHOLD=100
RECENT_COUNT=$(awk -v cutoff="$(date -d '5 minutes ago' '+%Y-%m-%dT%H:%M')" '/^# Time:/ { if ($3 >= cutoff) count++ } END { print count+0 }' "$LOG_FILE")
if [ "$RECENT_COUNT" -gt "$THRESHOLD" ]; then
echo "ALERT: $RECENT_COUNT trage query's" | mail -s "MySQL Alert" [email protected]
fi
Transparantie Ik creëer duidelijke verantwoordelijkheden: Wie reageert op pieken, wie past indexen aan, wie test releases. Ik vat de resultaten samen in korte changelogs. Zo begrijpt elk teamlid waarom een wijziging is doorgevoerd en wat het effect ervan was. Een gestructureerd proces bespaart tijd en voorkomt vals alarm.
Foutbeelden en snelle correcties
Volledig Tabelscans veroorzaken een onevenredig hoge belasting. Ik controleer eerst of er een geschikte index ontbreekt in de filterkolom of dat een expressie de index blokkeert. Ik elimineer hoge lock_time door transacties in te korten en concurrerende operaties gelijk te maken. Overvolle logs maak ik onschadelijk met log_throttle_queries_not_using_indexes en een realistische long_query_time. Ik meet elke correctie direct af tegen de oorspronkelijke cijfers zodat successen zichtbaar blijven.
Opslag-Ik herken knelpunten door IO-wachttijden en hoge schijflatentie te verhogen tijdens query-pieken. Vervolgens verminder ik onnodige schrijfbewerkingen, bijvoorbeeld door onveranderlijke velden minder vaak bij te werken. Als tabellen groeien, plan ik archiverings- of partitioneringsstrategieën zodat hete gegevens in het geheugen blijven. Voor adminstatements op piekmomenten schakel ik log_slow_admin_statements in om stille kostenveroorzakers te identificeren. Kleine, gerichte reparaties leveren hier sneller resultaat op dan grote reorganisaties.
Speciale functies in beheerde en cloudomgevingen
beheerde beheerde hosting of cloudservices beperken vaak de toegang tot bestanden. In zulke gevallen stel ik log_output in op TABLE en evalueer ik het langzame log direct vanuit de database. In MySQL 8.0 gebruik ik ook SET PERSIST om instellingen permanent in te stellen zonder directe toegang tot my.cnf. In cloud parametergroepen (bijvoorbeeld voor beheerde services) voer ik dezelfde variabelen in en plan ik een onderhoudsvenster voor de herstart.
-- Indien toegestaan: Permanente instellingen zonder herstart
SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 0.5;
SET PERSIST log_output = 'TABLE'; -- Alternatief voor FILE voor beperkte bestandstoegang
-- Evaluatie met log_output=TABLE
SELECT start_time, user_host, query_time, lock_time, rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 50;
TipMet veel verkeer kan log_output=FILE beter presteren, omdat loggen met tabellen extra overhead genereert. In beperkende omgevingen is TABLE echter vaak de enige manier. Ik stel dan strakkere limieten in (bijv. min_examined_row_limit) om het volume beheersbaar te houden.
Rotatie, opslag en gegevensbescherming
Rotatie voorkomt dat logs de schijf vullen. Ik roteer dagelijks of op grootte, comprimeer oude bestanden en hanteer een duidelijk bewaarbeleid (bijv. 14 dagen). Na de rotatie activeer ik een logspoeling zodat MySQL netjes naar het nieuwe bestand schrijft. Dit houdt de analyse en werking stabiel.
# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow-query.log {
dagelijks
14 roteren
grootte 100M
comprimeren
missingok
notifempty
640 mysql adm aanmaken
postrotate
test -x /usr/bin/mysqladmin || exit 0
/usr/bin/mysqladmin logboek spoelen
endscript
}
Gegevensbescherming is verplicht: slow logs kunnen parameterwaarden bevatten. Ik beperk de toegang strikt (bestandsrechten, groepen) en controleer of er gevoelige gegevens worden gelogd. Indien nodig werk ik met parameterbinding in de applicatie zodat er geen gepersonaliseerde platte tekst in het log verschijnt. Voor het delen van teams deel ik liever geaggregeerde rapporten dan onbewerkte logs.
Prestatieschema en systeemschema gebruiken
Prestatieschema geeft statistieken, zelfs zonder een geactiveerd traag logboek. Ik activeer de relevante afnemers voor verklaringen en analyseer dan de sysweergaven. Voordeel: ik kan bijna in realtime top digests en latency distributie zien, gegroepeerd over vergelijkbare queries.
-- Activeer consumer voor statementgeschiedenis (voor zover mogelijk tijdens runtime)
UPDATE prestatie_schema.setup_consumenten
SET INGESCHAKELD = 'JA'
WHERE NAME IN ('events_statements_history', 'events_statements_history_long');
-- Snel overzicht van dure querygroepen
SELECT schema_naam, digest_text, count_star,
ROUND(sum_timer_wait/1e12, 3) AS total_s,
ROUND(avg_timer_wait/1e9, 3) AS avg_ms,
ROUND(max_timer_wait/1e9, 3) ALS pmax_ms
FROM sys.statement_analyse
ORDER BY sum_timer_wait DESC
LIMIT 10;
Combinatie uit het trage logboek (trage uitschieters) en het prestatieschema (breedte, frequentie) laat me zowel individuele gevallen als systematische kostenveroorzakers zien. Ik vergelijk beide weergaven met verkeerspatronen om geprioriteerde to-dos te maken.
EXPLAIN ANALYZE en optimalisatiespoor
EXPLAIN ANALYZE (vanaf MySQL 8.0.18) vult schattingen aan met gemeten tijden. Ik vergelijk rij-schattingen met werkelijke waarden en ontdek verkeerde inschattingen door de optimizer. In het geval van tegenstrijdige plannen analyseer ik de trace van de optimizer om te zien waarom een index niet is geselecteerd.
-- Plan met gemeten waarden
ANALYSE UITLEGGEN
SELECT o.id, o.created_at
VAN orders o
JOIN klanten c OP c.id = o.customer_id
WHERE c.land = 'DE' EN o.status = 'betaald
ORDER BY o.created_at DESC
LIMIT 50;
-- Optimaliseer beslissingen traceren
SET optimizer_trace='enabled=on";
SELECT ...; -- query die moet worden geanalyseerd
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G
SET optimiser_trace="enabled=off";
ResultaatAls schattingen er ver naast zitten, werk ik statistieken bij (ANALYZE TABLE), voeg ik histogrammen toe of geef ik indices/queries een andere vorm zodat selectiviteit eerder effect heeft.
Herschrijf patronen die bijna altijd werken
OF naar ALLES VERENIGENMeerdere OR-condities op verschillende kolommen verhinderen vaak het gebruik van indexen. Ik scheid ze in twee selectieve queries en voeg de resultaten samen als duplicaten kunnen worden uitgesloten.
-- Voor:
SELECT * FROM t WHERE a = ? OF b = ?;
-- Beter:
(SELECT * FROM t WHERE a = ?)
ALLE SAMENVOEGEN
(SELECT * FROM t WHERE b = ? AND a ?);
PagineringOFFSET/LIMIT wordt duur met toenemende OFFSET. Ik schakel over naar keyset paginering en gebruik een geschikte sorteersleutel (idealiter geïndexeerd en monotoon).
-- Duur:
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 50 OFFSET 5000;
-- Beter (keyset):
SELECT id, title
VAN berichten
WHERE gecreeerde_at < :cursor
ORDER BY created_at DESC
LIMIT 50;
Samengestelde indices: Volgorde telt. Ik sorteer kolommen in de index volgens selectiviteit en querypatroon (eerst WHERE filteren, dan kolommen sorteren). Het doel is een dekkende index die filesort en tabel lookups vermijdt.
Functionele en gegenereerde indices in MySQL 8
Uitdrukkingen in WHERE/JOIN blokkeren vaak indexen. In MySQL 8.0, indexeer ik specifiek expressies of werk ik met gegenereerde kolommen om de sargabiliteit te creëren. Dit is vooral handig voor CASTs voor numerieke meta-waarden of JSON velden.
-- Voorbeeld: numeriek sorteren op tekstveld
ALTER TABLE product COLUMN price_num DECIMAL(10,2) toevoegen
GENERATED ALWAYS AS (CAST(price AS DECIMAL(10,2)) OPGESLAGEN;
CREËER INDEX idx_product_price_num OP product(price_num);
-- Query zonder CAST en met index
SELECT * FROM product
WHERE price_num TUSSEN 10 EN 50
ORDER BY price_num;
PraktijkIk test of de nieuwe index echt werkt (EXPLAIN) en meet het effect in het langzame logboek. Gegenereerde kolommen helpen ook om prefixen of genormaliseerde varianten (LOWER(email)) efficiënt te filteren.
Een meer gerichte benadering van CMS/winkelpatronen
Metatabellen (bijv. wp_postmeta) profiteren van gecombineerde indices op (post_id, meta_key) of (meta_key, meta_value). Voor frequente filters op meta_value_numeric gebruik ik gegenereerde kolommen zoals hierboven in plaats van CASTing in elke query. Ik versnel zoekpagina's door overtolligheden te verwijderen (denormalisatie light) en leestoegang indexvriendelijk te maken.
-- Typisch voor WordPress: snelle toegang tot meta-gegevens van een post
CREATE INDEX idx_postmeta_postid_metakey ON wp_postmeta(post_id, meta_key);
CREATE INDEX idx_postmeta_metakey_metavalue ON wp_postmeta(meta_key, meta_value(100));
KassaIk optimaliseer de paden voor minimale blokkeringstijden: korte transacties, alleen de benodigde rijen en indices precies voor de gebruikte WHERE-voorwaarden. Voor rapporten plan ik asynchrone aggregatie (tussentabellen) zodat gebruikersstromen niet worden vertraagd.
Grenzen van het langzame logboek en aanvullende statistieken
Veel kleine, snelle zoekopdrachten zijn niet zichtbaar in het langzame logboek, maar dragen wel bij aan de belasting. Daarom houd ik ook de doorvoer (queries/sec), 95e/99e percentielen en het aandeel queries zonder index bij. In Performance Schema- of APM-tools herken ik N+1-patronen, die ik vervolgens specifiek oplos door middel van joins, batch-laadprocessen of caching.
Bemonstering is nuttig wanneer logs te groot worden. Ik verhoog long_query_time iets of stel min_examined_row_limit in om alleen relevante queries op te nemen. Belangrijk: Noteer altijd wijzigingen zodat tijdreeksen vergelijkbaar blijven.
Werkwijze: Van bevindingen naar duurzame verbetering
Basislijn Ten eerste: ik sla een voorrapport op (tijdvenster, verkeer, configuratie). Vervolgens optimaliseer ik de ene queryfamilie na de andere en vergelijk ik identieke tijdvensters. Elke fix wordt gedocumenteerd in het archief (Wat? Waarom? Gemeten waarde voor/na?). Op deze manier blijven successen traceerbaar en regressiebestendig.
# Ruwe procedure (voorbeeld)
1) pt-query-digest --since '7d' slow-query.log > baseline.txt
2) Selecteer top 3 query digests (op totale tijd)
3) EXPLAIN/EXPLAIN ANALYZE, werk index- en herschrijfvoorstellen uit
4) Genereer testgegevens, simuleer belastingsprofiel
5) Uitrollen met monitoring (strakkere limieten voor 48 uur)
6) Vergelijkingsrapport: pt-query-digest --since '48h' > after.txt
7) Documenteer resultaat, plan volgende tranche
Plan stabiliteit Ik houd de optimiser in de gaten: als plannen veranderen (nieuwe versies, gewijzigde statistieken), controleer ik histogrammen, ANALYZE TABLE en het indexlandschap. Ik stel alleen selectief en op een gedocumenteerde manier hints in om de optimiser niet permanent vast te zetten.
Samenvatting in duidelijke stappen
Start Betekent: het logboek activeren, verstandige grenswaarden instellen, de eerste week gegevens verzamelen. Dan vat ik samen met pt-query-digest, prioriteer op basis van totale tijd en variantie en selecteer de topdrivers. Ik optimaliseer queries met EXPLAIN, sargable conditions en geschikte indices en controleer locking met kortere transacties. Aan de serverkant stel ik buffers, tijdelijke tabellen en spoelstrategieën op de juiste manier in. Tot slot automatiseer ik alarmen en herhaal ik de cyclus regelmatig - dit houdt de database snel, zelfs wanneer het verkeer en de datavolumes groeien.
