Analysera MySQL-logg för långsamma frågor i hosting: Tips för optimering

MySQL långsam frågelogg visar mig i hosting vilka frågor som tar tid, hur ofta de förekommer och varför de gör saker långsammare. Jag visar dig specifika steg för hur du aktiverar loggen, analyserar den och bygger om frågor så att sidorna laddas snabbare och serverresurserna arbetar mer effektivt.

Centrala punkter

• Aktivering och ställa in tröskelvärden på ett förnuftigt sätt
• Utvärdering med pt-query-digest och mysqldumpslow
• Mätetal tolka: Query_time, Lock_time, Rows_examined
• Tuning genom index, EXPLAIN och omskrivningar
• Automatisering och övervakning i hosting

Vad gör den långsamma frågeloggen i hosting?

Hosting innebär delade resurser, så varje millisekund per fråga räknas. Jag använder loggen för att hitta frågor som pågår längre än en definierad gräns och ser nyckeltal som Query_time, Lock_time, Rows_sent och Rows_examined för varje fråga. De här siffrorna visar mig om det är ett saknat index, en ogynnsam join eller en fullständig tabellsökning som ligger bakom. Speciellt på servrar med flera webbplatser kan en enda dålig fråga innebära en stor belastning på CPU och I/O. Jag prioriterar sedan de frågor som har den högsta totala tiden eftersom det är här den största hävstångseffekten på laddningstiden och serverbelastningen ligger.

Aktivering och förnuftiga tröskelvärden

StartJag kan köra runtime eller permanent via my.cnf, beroende på åtkomst i hostingen. För snabba tester slår jag på loggen temporärt och sätter long_query_time till ett värde som matchar trafiken och hårdvaran. Jag går ofta till 0,1 sekunder för hårt använda webbplatser, men håller ett öga på loggstorleken så att I/O inte växer i onödan. Om direkt filåtkomst är begränsad använder jag alternativen för prestandaschema i MySQL-skalet för att generera rapporter. Efter finjusteringarna skriver jag de slutliga inställningarna i konfigurationsfilen och startar om tjänsten.

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';

VISA VARIABLER SOM LIKNAR 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Permanent Jag ställer in alternativ som log_throttle_queries_not_using_indexes och log_slow_admin_statements så att loggen förblir användbar och inte exploderar. Jag dokumenterar varje värde, t.ex. varför long_query_time är 0,5 eller 0,1 sekunder. Detta gör att jag kan förfina det senare. I delade miljöer diskuterar jag ofta aktiveringen med leverantören eller använder deras panel. Jag kopplar varje aktivering till ett startdatum för att kunna jämföra effekter i övervakning och mätvärden på ett snyggt sätt.

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
lång_förfrågan_tid = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = 1

Utvärdera långsam logg effektivt

Rådata är bullriga, så jag sammanfattar dem med pt-query-digest och sorterar efter total tid under en meningsfull tidsperiod. Det är så jag känner igen mönster, mycket varierande frågor och frågefamiljer som bara varierar med parameter. Jag kontrollerar fördelningen, inte bara genomsnittet, eftersom avvikande värden orsakar verkliga användarproblem. För en snabb överblick hjälper mysqldumpslow mig att se de tio långsammaste grupperna. Jag får mer djup med hjälp av tidsfönster, databasfilter och en export till en textanalys.

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

Användbar är också en titt på andra loggar när applikations- eller PHP-funktioner kommer in i bilden. För att göra detta använder jag befintliga loggarbetsflöden och buntar ihop resultaten. Den här guiden ger mig ofta en introduktion: Analysera loggar. Jag synkroniserar tidsstämplarna så att jag kan jämföra trafiktoppar med frågetoppar. Detta gör att jag kan se om cachemissar, cron-jobb eller importjobb använder databasen samtidigt.

Tolka mätvärden på rätt sätt

Frågetid visar mig den rena körtiden; jag prioriterar frågor över en sekund först. Lock_time visar väntetider på grund av låsningar, som ofta beror på onödigt långa transaktioner eller stora batcher. Förhållandet Rows_examined till Rows_sent talar om för mig om frågorna visar för många rader och om index saknas. Om loggen innehåller många „No index use“-poster ställer jag in throttling och tittar närmare på de drabbade tabellerna. Det är fortfarande viktigt att alltid ta itu med orsaken snarare än symptomet: Ett index på rätt kolumn slår alla hårdvaruuppgraderingar.

Mätetal	Vad jag ser	Mått
Query_time hög	Lång speltid per version	Kontrollera EXPLAIN, skriv om frågan, lägg till index
Lock_time hög	Väntetid för lås	Förkorta transaktioner, minska batchstorleken, lämplig isolering
Rows_examined ≫ Rows_sent	Skannade för mycket, returnerade för lite	Indexfiltrera kolumner, skapa sargbarhet
Inget index används	Scanning av hela bordet	Skapa index, undvik uttryck i WHERE

Gränsvärden Jag justerar den efter den första veckan så att jag inte försvinner i bruset. Jag sänker long_query_time stegvis tills jag har tillräckligt många träffar för systematiska förbättringar. Jag dokumenterar varje justering med datum och orsak. På så sätt håller jag utvärderingen fokuserad. Värdefulla träffar besparar mig dubbelarbete senare.

Övning: Query tuning steg för steg

FÖRKLARA är min start innan jag ändrar kod. Jag letar efter „type: ALL“, „rows“ med stort antal och „Using filesort“ eller „Using temporary“. Funktioner på kolumner i WHERE eller JOIN förhindrar ofta indexanvändning. Istället formulerar jag sargbara villkor och kontrollerar sedan den nya planen. Varje steg måste utföra radreduktionen tidigt och målmedvetet.

EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- Bättre:
CREATE INDEX idx_orders_created ON orders(created_at);
VÄLJ * FRÅN beställningar
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

JOINs Jag optimerar genom att kontrollera länkordningen och matchande index på länknycklar. Jag kontrollerar om ett sammansatt index täcker WHERE + ORDER BY för att undvika filesort. Jag ställer in LIMIT där endast en förhandsgranskning är nödvändig. Jag sparar resultatcachelagring på applikationsnivå för upprepade, identiska frågor med låg ändringsfrekvens. Du hittar en mer djupgående introduktion till index och lås här: Index och låsning.

Indexstrategier för CMS och butiker

WordPress, WooCommerce- eller butikssystem skapar typiska mönster: mycket läsning, selektivt skrivande, ofta med meta- eller produkttabeller. Jag analyserar de vanligaste rutterna - startsida, kategori, sök, kassa - och placerar index specifikt på filter-, sorterings- och join-kolumner. Täckande index (t.ex. (status, created_at, id)) sparar mycket tid för att gå till tabellen. För att söka efter prefix använder jag lämpliga indexformer eller fulltext i stället för LIKE ‚%wort%‘. Jag mäter varje indexförändring före och efter livekörningen med samma belastningsprofiler.

Tillväxt Jag använder kardinalitet och histogram för att kontrollera datauppsättningar så att jag inte indexerar på sällsynta värden. Jag håller antalet index lågt för att hålla skrivbelastningen och minneskraven under kontroll. Konsoliderade sammansatta index ersätter flera enskilda index. Jag reglerar autovacuum-liknande uppgifter i MySQL genom att analysera dem regelbundet och bygga om dem endast när det är nödvändigt. Detta håller optimeraren tillförlitlig.

Serverinställningar, cachelagring och minne

InnoDB Jag bestämmer storleken på buffertpoolen på grundval av aktiva dataposter och indexstorlekar, inte enligt schablonvärden. Jag ökar den tills storleken på arbetsuppsättningen till stor del finns i minnet och sidmissfrekvensen sjunker. Jag ställer in tmp_table_size och max_heap_table_size så att färre temporära tabeller hamnar på disken. För skrivsäkerhet och latens balanserar jag innodb_flush_log_at_trx_commit på lämpligt sätt för applikationen. På applikationsnivå cachar jag frekventa resultat och använder HTTP-cachning så att databasen får färre förfrågningar.

Hårdvara och nätverkseffekter ingår i diagnosen: Långsam lagrings-I/O eller en överbelastad CPU känns omedelbart igen av förfrågningar. Jag mäter därför IO-wait parallellt med databasmätvärdena. Om du behöver fler reserver kan du planera vertikal eller horisontell skalning med ett mätbart mål. Den här guiden ger dig en kompakt översikt över flaskhalsar, tuning och resurser: Hårdvara och cache. På så sätt försäkrar jag mig om att jag inte vrider på fel knapp i blindo.

Samtidighet och låsning i hosting

Lås_tid växer när långa transaktioner berör många rader eller när städjobb körs på bästa sändningstid. Jag förkortar skrivoperationer, delar upp stora uppdateringar i mindre satser och minskar därmed låsens hålltid. Lämpliga isoleringsnivåer minskar konflikterna utan att datakonsistensen äventyras. Jag avlastar hotspots med sekundära index och lämpliga WHERE-villkor så att färre rader påverkas. Jag schemalägger bakgrundsjobb i tidsfönster med låg trafik så att användaråtgärder prioriteras.

Dödlägen Jag analyserar dem med hjälp av återkommande mönster: samma tabeller, ändrad sekvens, identiska rader. Jag standardiserar åtkomstsekvensen i kod och lagrade procedurer. Retry-logik med jitter löser tillfälliga kollisioner. Där det är möjligt isolerar jag de dyraste operationerna i jobbköer. Detta minskar variansen märkbart och ökar den upplevda prestandan.

Automatiserade larm och arbetsflöden

Rutin beats actionism: Jag analyserar loggen dagligen eller varje vecka, beroende på trafik och utgivningsfrekvens. Ett litet skript räknar nya träffar under de senaste minuterna och skickar ett e-postmeddelande till mig om tröskelvärdet ökar. Jag genererar också regelbundna pt-query-digest-rapporter och håller alltid ett öga på topp 10. Jag övervakar utgivningsdagar mer noggrant. Det gör att jag kan upptäcka försämringar innan användarna märker dem.

#!/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 långsamma förfrågningar" | mail -s "MySQL Alert" [email protected]
fi

Öppenhet Jag skapar tydliga ansvarsområden: Vem reagerar på toppar, vem justerar index, vem testar releaser. Jag sammanfattar resultaten i korta changelogs. På så sätt förstår varje teammedlem varför en förändring gjordes och vilken effekt den hade. En strukturerad process sparar tid och förebygger falsklarm.

Felbilder och snabba korrigeringar

Fullständig Tabellskanningar utlöser en oproportionerligt hög belastning. Jag kontrollerar först om det saknas ett lämpligt index i filterkolumnen eller om ett uttryck blockerar indexet. Jag eliminerar hög lock_time genom att förkorta transaktioner och utjämna konkurrerande operationer. Jag desarmerar överfyllda loggar med log_throttle_queries_not_using_indexes och en realistisk long_query_time. Jag mäter omedelbart varje korrigering mot de ursprungliga siffrorna så att framgångarna förblir synliga.

Förvaring-Jag upptäcker flaskhalsar genom att öka IO-wait och high disc latency under frågetoppar. Jag minskar sedan onödiga skrivoperationer, till exempel genom att uppdatera oföränderliga fält mindre ofta. När tabeller växer planerar jag arkiverings- eller partitionsstrategier så att heta data stannar kvar i minnet. För administratörsutlåtanden vid toppar slår jag på log_slow_admin_statements för att identifiera tysta kostnadsdrivare. Här lönar sig små, riktade korrigeringar snabbare än stora omorganisationer.

Specialfunktioner i hanterade miljöer och molnmiljöer

Förvaltat webbhotell eller molntjänster begränsar ofta filåtkomsten. I sådana fall ställer jag in log_output till TABLE och utvärderar den långsamma loggen direkt från databasen. I MySQL 8.0 använder jag också SET PERSIST för att permanent ställa in inställningar utan direkt åtkomst till my.cnf. I molnparametergrupper (t.ex. för hanterade tjänster) anger jag samma variabler och schemalägger ett underhållsfönster för omstarten.

-- Om tillåtet: Beständiga inställningar utan omstart
SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 0,5;
SET PERSIST log_output = 'TABLE'; -- Alternativ till FILE för begränsad filåtkomst

-- Utvärdering med log_output=TABLE
SELECT start_time, user_host, query_time, lock_time, rows_sent, rows_examined, sql_text
FRÅN mysql.slow_log
ORDER BY query_time DESC
LIMIT 50;

Ledtråd: Vid tung trafik kan log_output=FILE vara mer effektivt, eftersom tabelloggning genererar extra overhead. I restriktiva miljöer är dock TABLE ofta den enda vägen att gå. Jag sätter då snävare gränser (t.ex. min_examined_row_limit) för att hålla volymen kontrollerbar.

Rotation, lagring och dataskydd

Rotation hindrar loggar från att fylla upp skivan. Jag roterar dagligen eller efter storlek, komprimerar gamla filer och upprätthåller en tydlig lagringspolicy (t.ex. 14 dagar). Efter rotation utlöser jag en loggspolning så att MySQL skriver rent till den nya filen. Detta håller analys och drift stabil.

# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow-query.log {
  dagligen
  rotera 14
  storlek 100M
  komprimera
  missingok
  notifempty
  skapa 640 mysql adm
  postrotate
    test -x /usr/bin/mysqladmin || exit 0
    /usr/bin/mysqladmin spola-loggarna
  Slutskript
}

Uppgiftsskydd är obligatorisk: långsamma loggar kan innehålla parametervärden. Jag begränsar åtkomsten strikt (filbehörigheter, grupper) och kontrollerar om känsliga data loggas. Om det behövs arbetar jag med parameterbindning i applikationen så att ingen personlig klartext visas i loggen. För teamdelning föredrar jag att dela aggregerade rapporter snarare än råa loggar.

Använd prestandaschema och sys-schema

Prestationsordning ger mätvärden även utan en aktiverad långsam logg. Jag aktiverar de relevanta konsumenterna för uttalanden och analyserar sedan systemvyerna. Fördel: Jag kan se de bästa sammanställningarna och latensfördelningen nästan i realtid, grupperade över liknande frågor.

-- Aktivera konsument för kontoutdragshistorik (så långt det är möjligt vid körning)
UPDATE performance_schema.setup_consumers
SET ENABLED = 'JA'
WHERE NAME IN ('events_statements_history', 'events_statements_history_long');

-- Snabb översikt över dyra frågegrupper
SELECT schema_name, 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) AS pmax_ms
FROM sys.statement_analysis
ORDER BY sum_timer_wait DESC
BEGRÄNSNING 10;

Kombination från slow log (slow outliers) och performance scheme (width, frequency) visar mig både enskilda fall och systematiska kostnadsdrivare. Jag jämför båda vyerna med trafikmönster för att skapa prioriterade att-göra-uppgifter.

EXPLAIN ANALYZE och Optimiser Trace

EXPLAIN ANALYZE (från och med MySQL 8.0.18) kompletterar uppskattningar med uppmätta tider. Jag jämför raduppskattningar med faktiska värden och avslöjar felbedömningar av optimeraren. När det gäller motsägelsefulla planer analyserar jag optimeringsspåret för att se varför ett index inte valdes.

-- Plan med uppmätta värden
FÖRKLARA ANALYSERA
SELECT o.id, o.skapad_tid
FRÅN order o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'DE' AND o.status = 'paid'
ORDER BY o.created_at DESC
BEGRÄNSNING 50;

-- Spåra optimeringsbeslut
SET optimizer_trace="enabled=on";
SELECT ...; -- fråga som ska analyseras
SELECT TRACE FRÅN information_schema.OPTIMIZER_TRACE\G
SET optimiser_trace="enabled=off";

ResultatOm uppskattningarna är långt ifrån korrekta uppdaterar jag statistiken (ANALYZE TABLE), lägger till histogram eller omformar index/frågor så att selektiviteten får effekt tidigt.

Skriv om mönster som nästan alltid fungerar

ELLER till UNION ALLFlera OR-villkor på olika kolumner förhindrar ofta indexanvändning. Jag delar upp dem i två selektiva frågor och slår samman resultaten om dubbletter kan uteslutas.

-- Innan:
SELECT * FROM t WHERE a = ? ELLER b = ?

-- Bättre:
(SELECT * FROM t WHERE a = ?)
UNION ALL
(SELECT * FROM t WHERE b = ? AND a  ?);

PagineringOFFSET/LIMIT blir dyrt med ökande OFFSET. Jag byter till keyset-paginering och använder en lämplig sorteringsnyckel (helst indexerad och monoton).

-- Dyrt:
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 50 OFFSET 5000;

-- Bättre (nyckeluppsättning):
Välj id, titel
FRÅN inlägg
WHERE created_at < :markör
ORDER BY created_at DESC
BEGRÄNSNING 50;

Sammansatta index: Order räknas. Jag sorterar kolumner i indexet enligt selektivitet och frågemönster (WHERE-filter först, sedan sortera kolumner). Målet är ett täckande index som undviker filesort och tabelluppslagningar.

Funktionella och genererade index i MySQL 8

Uttryck i WHERE/JOIN blockerar ofta index. I MySQL 8.0 indexerar jag specifikt uttryck eller arbetar med genererade kolumner för att skapa sargability. Detta är särskilt användbart för CAST för numeriska metavärden eller JSON-fält.

-- Exempel: numerisk sortering på textfält
ALTER TABLE product ADD COLUMN price_num DECIMAL(10,2)
  GENERATED ALWAYS AS (CAST(pris AS DECIMAL(10,2))) LAGRAS;
CREATE INDEX idx_product_price_num ON product(price_num);

-- Fråga utan CAST och med index
Välj * från produkt
WHERE price_num BETWEEN 10 AND 50
ORDER BY pris_nummer;

ÖvningJag testar om det nya indexet verkligen fungerar (EXPLAIN) och mäter effekten i den långsamma loggen. Genererade kolumner hjälper också till att filtrera prefix eller normaliserade varianter (LOWER(email)) på ett effektivt sätt.

En mer målinriktad strategi för CMS/shop-mönster

Meta-tabeller (t.ex. wp_postmeta) drar nytta av kombinerade index på (post_id, meta_key) eller (meta_key, meta_value). För frekventa filter på meta_value_numeric använder jag genererade kolumner enligt ovan istället för CASTing i varje fråga. Jag snabbar upp söksidor genom att ta bort redundans (denormalisering light) och göra läsåtkomst indexvänlig.

-- Typiskt för WordPress: snabb åtkomst till metadata för ett inlägg
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));

Checka utJag optimerar sökvägarna för minsta möjliga blockeringstid: korta transaktioner, bara de rader som behövs och index exakt för de WHERE-villkor som används. För rapporter planerar jag asynkron aggregering (mellanliggande tabeller) så att användarflödena inte saktas ned.

Gränser för den långsamma loggen och kompletterande mätvärden

Många små, snabba frågor märks inte i den långsamma loggen, men bidrar till belastningen. Därför följer jag även upp genomströmning (queries/sek), 95:e/99:e percentilen och andelen queries utan index. I Performance Schema eller APM-verktyg känner jag igen N+1-mönster, som jag sedan löser specifikt genom joins, batchladdningsprocesser eller cachelagring.

Provtagning är användbart när loggarna blir för stora. Jag ökar long_query_time något eller ställer in min_examined_row_limit för att bara inkludera relevanta frågor. Viktigt: Notera alltid förändringar så att tidsserierna förblir jämförbara.

Arbetsmetod: Från resultat till hållbar förbättring

Baslinje Först: Jag sparar en tidigare rapport (tidsfönster, trafik, konfiguration). Sedan optimerar jag den ena frågefamiljen efter den andra och jämför identiska tidsfönster. Varje åtgärd dokumenteras i repository (Vad? Varför? Uppmätt värde före/efter?). På så sätt förblir framgångarna spårbara och regressionssäkra.

# Grov procedur (exempel)
1) pt-query-digest --sedan '7d' slow-query.log > baseline.txt
2) Välj topp 3 frågesammanställningar (efter total tid)
3) EXPLAIN/EXPLAIN ANALYZE, utarbeta index och omskrivningsförslag
4) Generera testdata, simulera belastningsprofil
5) Utrullning med övervakning (strängare gränser under 48 timmar)
6) Jämförelserapport: pt-query-digest --since '48h' > after.txt
7) Dokumentera resultat, planera nästa del

Planens stabilitet Jag övervakar optimeraren över tid: om planerna ändras (nya versioner, ändrad statistik) kontrollerar jag histogram, ANALYZE TABLE och indexlandskapet. Jag ställer bara in hintar selektivt och på ett dokumenterat sätt för att inte binda upp optimeraren permanent.

Sammanfattning i tydliga steg

Start innebär: aktivera loggen, ställa in förnuftiga gränsvärden, samla in data för den första veckan. Sedan sammanfattar jag med pt-query-digest, prioriterar enligt total tid och varians och väljer ut de bästa drivkrafterna. Jag optimerar frågor med EXPLAIN, sätter upp rimliga villkor och lämpliga index och kontrollerar låsning med kortare transaktioner. På serversidan ställer jag in buffertar, temporära tabeller och spolningsstrategier på lämpligt sätt. Slutligen automatiserar jag larm och upprepar cykeln regelbundet - på så sätt hålls databasen snabb, även när trafiken och datavolymerna växer.