Databaser

Att använda MySQL Performance Schema på ett effektivt sätt för bättre prestanda

För bättre MySQL-prestanda Jag använder Performance Schema för att direkt med SQL analysera körningsdata om frågor, väntetider, lås, minne och I/O. På så sätt kan jag snabbare identifiera orsakerna till långsamma satser och vidta riktade åtgärder för Tuning samt övervakning enligt [2][3][15].

Centrala punkter

Följande fokusområden hjälper mig att använda Performance Schema på ett effektivt sätt.

Aktivering och en strömlinjeformad konfiguration med lämpliga instrument och förbrukare
Sammanfattningar av uttalanden använda för att upptäcka kostsamma mönster och hotspots
Väntningshändelser, utvärdera Locks och I/O tillsammans för att hitta verkliga flaskhalsar
Sys-schema som en förkortning för snabba, praktiskt användbara insikter
Iterativ Arbetsflöde: mäta, isolera, ändra, mäta på nytt

Aktivera och konfigurera prestandaschemat på ett lämpligt sätt

Jag kontrollerar först om performance_schema är aktiverad, eftersom de senaste versionerna av MySQL vanligtvis levereras med den aktiverad [1][12]. Om den saknas anger jag i [mysqld]-blocket av my.cnf variabeln performance_schema=ON och startar om servern. Därefter ställer jag in instrument och förbrukare på ett målinriktat sätt, istället för att ha allt på max hela tiden. Jag koncentrerar mig på uttalande/%, wait/% och relevanta I/O-vägar, så att jag kan samla in meningsfulla data utan onödig overhead [6]. Inför en ny mätomgång tömmer jag berörda historiktabeller och börjar med en ren Bas.

Snabba framgångar med Sys-schemat

För att snabbt få en överblick använder jag ofta sys-schemat, eftersom det på ett meningsfullt sätt sammanfattar rådata från Performance-schemat [13]. På så sätt kan jag på några minuter hitta de frågor som står för den största andelen av körtiden. Jag börjar med de mest använda satserna, granskar fil-I/O-vyer och tittar på väntesammanfattningar för trådar. Så snart jag har identifierat en flaskhals går jag tillbaka till råtabellerna och förfinar analysen. Den som granskar frågeplaner kan med lämpliga Tips för Optimizer ofta märkbara redan efter kort tid Vinster uppnå.

Att välja rätt instrument och förbrukare

Jag börjar i det stora hela, men ser till att hålla översikten: Först tar jag fasta på de viktigaste Instrument för satser, väntetider och I/O, därefter stänger jag av allt som inte ger någon information [6]. Användare som händelsehistorik och sammanfattningstabeller måste stödja de frågor som jag vill besvara. Om det till exempel handlar om latensspikar tittar jag i events_waits_summary_global_efter_händelsens_namn och jämför det med sammanfattning_av_uttalanden_om_evenemang_efter_sammanställning. Om det uppstår väntetider för I/O, kontrollerar jag filöversikt_efter_händelsenamn och table_io_waits_summary_by_table. Detta målinriktade urval minimerar omkostnaderna och ger ändå tillförlitliga Uppgifter.

Statement-Digests: Identifiera mönster, minska belastningen

Med hjälp av Statement-Digests kan jag se vilka mönster som är kostsamma på lång sikt, även om enskilda frågor innehåller varierande literaler [17]. Jag sorterar efter total tid, antal körningar och genomsnittlig latens för att fastställa prioriteringar. I detta sammanhang använder jag dessutom Analysera loggen över långsamma frågor tillbaka för att inte missa sällsynta avvikelser. När sammanfattningarna visar toppar kontrollerar jag index, JOIN-strategier och filterordning med FÖRKLARA. Därefter bekräftar jag effekten genom att göra nya mätningar i Performance Schema, så att optimeringarna förblir mätbara.

Tolka väntetillstånd, lås och I/O

När förfrågningar fastnar, kontrollerar jag tabellerna ”Wait” och ”Lock” för att hitta den egentliga Orsak finns [3]. Om många trådar körs på samma tabeller, tyder det på table_lock-Väntar på konkurrens. Om fil-I/O-händelser uppvisar höga latenser kontrollerar jag lagring och cachelagring samt sökmönster med omfattande genomsökningar. Om jag upptäcker InnoDB-radlås analyserar jag aktiva poster, transaktionstid och indextäckning. Först när dessa pusselbitar faller på plats börjar jag justera serverparametrar, schemat eller koden.

Övervakning av minnet: Minne och buffertpool

Jag åtgärdar minnesproblem genom att analysera minnestabellerna och utnyttjandet av InnoDB-buffertarna. Om minnesbehovet för enskilda komponenter ökar justerar jag gränserna och kontrollerar om cacherna binder felaktiga data. Om InnoDB-cachen inte räcker till ökar jag andelen eller förbättrar jag frågornas lokalitet. Den som vill fördjupa sig ytterligare kan via Optimera buffertpoolen uppnå betydande fördröjningsvinster. Jag bekräftar effekten med hjälp av Sammanfattning-tabeller och följ upp om LRU-träffar och I/O-väntetider utvecklas i rätt riktning.

Iterativt diagnostiskt arbetsflöde för vardagen

Jag arbetar alltid i tydliga cykler för att inte slösa tid och för att förändringarna ska förbli mätbara [3]. Först återskapar jag problemet under kontrollerad belastning. Därefter samlar jag in mätvärden i ett fåtal, målinriktade tabeller och isolerar de mest uppenbara kandidaterna. Därefter ändrar jag det som lovar störst nytta: index, sökfråga, parametrar eller kod. Till sist mäter jag igen och dokumenterar kortfattat Före/efter-tabeller, så att teamet omedelbart kan se effekten.

Exempel på frågor: Från rådata till beslut

För vanliga frågor har jag skrivit ner korta SQL-kodsnuttar som jag använder direkt i det dagliga arbetet. Tabellen visar exempel som jag ofta använder och vad de står för. Jag anpassar filter som BEGRÄNSNING eller . ORDER BY beroende på tillämpningsfall. Det viktiga är: först en hypotes, sedan en målinriktad utvärdering och ett tydligt beslut. På så sätt håller jag analysen fokuserad och undviker onödiga Last.

Prestationsschematabell(er)	Mål	Viktiga kolumner	Exempel på en sökfråga
`sammanfattning_av_uttalanden_om_evenemang_efter_sammanställning`	Hitta dyra mönster	`digest_text`, `count_star`, `sum_timer_wait`	`SELECT digest_text, count_star, sum_timer_wait/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_digest ORDER BY sec_total DESC LIMIT 10;`
`events_waits_summary_global_efter_händelsens_namn`	Väntetidshotspots	`event_name`, `sum_timer_wait`	`SELECT event_name, sum_timer_wait/1e12 AS sec_total FROM performance_schema.events_waits_summary_global_by_event_name ORDER BY sec_total DESC LIMIT 10;`
`table_io_waits_summary_by_table`	Kontrollera tabell-I/O	`objektschema`, `objektnamn`, `read_timer_wait`	`SELECT object_schema, object_name, (read_timer_wait+write_timer_wait)/1e12 AS sec_total FROM performance_schema.table_io_waits_summary_by_table ORDER BY sec_total DESC LIMIT 10;`
`minnesöversikt_globalt_efter_händelsenamn`	Hitta minneskrävande program	`event_name`, `current_alloc`	`SELECT event_name, current_alloc/1024/1024 AS mb FROM performance_schema.memory_summary_global_by_event_name ORDER BY mb DESC LIMIT 10;`

Produktionsverksamhet: Minimera omkostnaderna, maximera effekten

Under liveframträdanden slår jag inte på instrumenten i blindo, utan väljer bara det som besvarar min fråga [6]. Jag hanterar händelser med hög frekvens försiktigt och håller historikfönstren korta. För längre observationer föredrar jag komprimerade sammanfattningar och sparar ögonblicksbilder externt. Jag är noga med att anteckna i performance_schema_setup_consumers, så att jag kan styra samlingarna istället för att låta dem sköta sig själva. Denna inriktning gör att analysen effektiv och skyddar servern.

Finjustering: Setup-instrument och -användare i praktiken

För att snabbt kunna få tillförlitliga resultat konfigurerar jag instrument och förbrukare på ett målinriktat sätt. Särskilt viktigt är uttalande/%, wait/%, wait/io/% och – vid behov – utvalda minne/%-banor. Jag aktiverar först bara det nödvändigaste och utökar sedan om jag fortfarande har konkreta frågor som är obesvarade. Timerna i Performance Schema mäter i pikosekunder; för att få sekunder delar jag latenskolumnerna med 1e12.

Typisk startpunkt vid körning:

-- Aktivera nyckelinstrument
UPDATE performance_schema.setup_instruments
  SET ENABLED='YES', TIMED='YES'
  WHERE NAME LIKE 'statement/%'
 OR NAME LIKE 'wait/io/%'
 OR NAME LIKE 'wait/lock/%';

-- Välj viktiga konsumenter
UPDATE performance_schema.setup_consumers
  SET ENABLED='YES'
  WHERE NAME IN ('global_instrumentation',
 'thread_instrumentation',
 'statements_digest',
 'events_statements_current',
                 'events_statements_history',
 'events_waits_current',
 'events_waits_history');

-- Töm sammanfattningarna för en ny mätsekvens
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
TRUNCATE TABLE performance_schema.events_waits_summary_global_by_event_name;
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table;

När jag behöver minnesanalyser aktiverar jag dem selektivt minne/%-Verktyg. Det medför högre omkostnader, men lönar sig vid läckor eller stor belastning på allokatorn.

Dimensioner: Att förstå användare, värd och schema

Effekttoppar är ofta inte globala, utan begränsade till vissa Användare, Värdar eller en Schema begränsad. Prestandaschemat tillhandahåller sammanfattningar per konto och värd. Dessutom innehåller sammanfattningen kolumnen schema_name, för att begränsa antalet hotspots per databas.

Exempel som jag ofta använder:

De bästa schemana efter total körtid: SELECT schema_name, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_digest GROUP BY schema_name ORDER BY sec_total DESC LIMIT 10;
Användare/värdar som orsakar mest fördröjning (via konton): SELECT user, host, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_account_by_event_name GROUP BY user, host ORDER BY sec_total DESC LIMIT 10;
Trådar med längst väntetid: SELECT thread_id, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_waits_summary_by_thread_by_event_name GROUP BY thread_id ORDER BY sec_total DESC LIMIT 10;

Med hjälp av dessa vyer kan jag selektivt separera trafiksegment och begränsa, cacha eller införa olika sökvariationer för varje kund.

Synliggöra långa transaktioner och metadataspärrar

Transaktioner som pågår länge eller är inaktiva blockerar kontrollpunkter, rensning och konkurrerande DML. Därför kontrollerar jag regelbundet transaktionsvyn och MDL-väntetiderna:

Aktiva transaktioner: SELECT thread_id, timer_wait/1e12 AS sec_running, state FROM performance_schema.events_transactions_current ORDER BY sec_running DESC LIMIT 10;
Identifiera metadataspärrar (DDL/DML-konflikter): SELECT event_name, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_waits_summary_global_by_event_name WHERE event_name LIKE 'wait/lock/metadata/sql/mdl%' GROUP BY event_name ORDER BY sec_total DESC;

Om MDL dominerar planerar jag om DDL-fönstren, minimerar låstiden i koden (kortare transaktioner) och kontrollerar om onödiga AUTOCOMMIT=0-Att hålla sessioner öppna onödigt länge.

Replikering, säkerhetskopiering och biverkningar i fokus

Replikations- och säkerhetskopieringsprocesser syns i väntetids- och I/O-vyer. Fördröjningar kan avgränsas med hjälp av arbetarstatus och filväntetider. Jag tittar på Applier-arbetare, SQL-trådar och fil-I/O-händelser:

Applier-Worker med hög latens: SELECT worker_id, THREAD_ID, APPLYING_TRANSACTION, APPLYING_STATE FROM performance_schema.replication_applier_status_by_worker;
Fil-I/O-flaskhalsar under säkerhetskopiering: SELECT event_name, (sum_timer_read+sum_timer_write)/1e12 AS sec_total FROM performance_schema.file_summary_by_event_name ORDER BY sec_total DESC LIMIT 10;

Om jag upptäcker flaskhalsar här, separerar jag I/O-faserna (t.ex. fönsterhantering, I/O-schemaläggare, begränsning av säkerhetskopiering) eller ökar antalet parallella Applier-arbetare, förutsatt att arbetsbelastningen skalar.

Tidsfönster, ögonblicksbilder och återställningsstrategier

Mätningar kräver tydliga tidsfönster. För „före/efter“ arbetar jag med riktade återställningar och ögonblicksbilder:

Återställ sammanfattningarna för att få nya intervall: TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
Säkerhetskopiera ögonblicksbilden externt: CREATE TABLE IF NOT EXISTS perf_snapshot_digest AS SELECT NOW() AS captured_at, * FROM performance_schema.events_statements_summary_by_digest;
Spara korta historikfönster (konsument), samla in långa trender externt.

På så sätt kan jag på ett säkert sätt jämföra och dokumentera optimeringar över olika distributioner, parameterändringar eller schemaändringar.

Styra overhead och minnesbehov

En vanlig fördom är att Performance Schema är „för dyrt“. I praktiken håller jag overheadkostnaden låg genom tre åtgärder: att endast aktivera relevanta instrument, att hålla historikförbrukare med hög belastning korta och att välja lämpliga lagringsparametrar. Vid stor varians i sammanställningarna ökar jag målinriktat performance_schema_digests_size samt – vid behov – performance_schema_max_sql_text_length, så att identiteterna förblir stabila. Om minnesverktyg behövs begränsar jag deras användning till problematiska delsystem.

Typiska inställningsskruvar i my.cnf:

[mysqld]
performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-instrument='wait/io/%=ON'
performance-schema-instrument='wait/lock/%=ON'
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-waits-history=ON
# Valfritt om det finns många mönster:
performance_schema_digests_size=10000
performance_schema_max_sql_text_length=4096

Vid varje ändring kontrollerar jag om CPU, latens och minnesanvändning förblir stabila. Så snart diagnosen är klar återställer jag konfigurationen till „driftminimum“.

Vanliga mönster och snabba lösningar

Stort belopp i sammanfattning_av_uttalanden_om_evenemang_efter_sammanställning, många skannade bilder: Kontrollera index, filterordning och sorterbarhet; bekräfta med FÖRKLARA och upprepa mätningen (digesttiden måste synbart minska).
Dominant table_io_waits i några tabeller: Förbättra I/O-lokaliseringen (klusterindexåtkomst, täckande index), minska datamängden per sats och, vid behov, använda batchbearbetning istället för bearbetning av hela tabellen.
Väntetider på wait/lock/innodb/%: Identifiera ”hot records” och mildra skrivkonflikter genom mindre transaktioner, lämpliga index eller köhantering.
Många wait/lock/metadata/sql/mdl: Planera DDL-fönster, ONLINE-prioritera operationer som stöder detta, avkoppla läsare/skrivare genom kortare transaktioner.
Lagringsökning i minnesöversikt_globalt_efter_händelsenamn: Justera gränsvärdena, begränsa query-cachen på ett målinriktat sätt, identifiera problematiska komponenter med minne/% redovisa i detalj.
„Spiky“ latens vid i övrigt normala medelvärden: Använd Sys-vyer med percentiler och mät vid behov belastningstoppar separat (snävare fönster, kort historik, fokuserade instrument).

Korrelera: Från tråd till uttalande och väntetid

För att snabbt kunna koppla samman orsakerna länkar jag samman performance_schema.threads med flikarna ”Current” och ”History” för uttalanden och väntetider. På så sätt kan jag se vad en berörd tråd gjorde senast och vad den väntar på. En kortfattad genomgång:

Berörda personer PROCESSLIST_ID resp. THREAD_ID från performance_schema.threads hämta.
Senaste uttalandet via händelser_uttalanden_historik beräkna (enligt THREAD_ID och sortera efter tid).
Parallella väntetider från events_waits_history kontrollera för att se om det finns lock- eller I/O-väntetillstånd.

Det här „Drilldown & Join“-mönstret är min standard när enskilda sessioner eller webbförfrågningar hamnar ur takt.

Kvalitetskontroller och kontinuerlig prestanda

För att optimeringarna inte ska gå till spillo inför jag smidiga kvalitetskontroller: definierade frågor från Performance Schema körs före och efter varje release. Jag sparar ögonblicksbilder, jämför nyckeltal (Top-Digests, Top-Waits, I/O per tabell) och dokumenterar avvikelser. I CI/CD lägger jag till representativa belastningsprofiler och gränsvärden för 95:e percentilen. Om en mätvärde avviker från normen finns det en tydlig åtgärdsväg: testa hypotesen, fokusera på verktygen, distribuera korrigeringen, mäta på nytt.

Undvika felkällor

För många instrument på sikt: Diagnosen är tillfällig; låt endast minimalsatsen vara aktiv under normal drift.
Blandade mätperioder: Töm sammanfattningarna inför nya tester, annars försvagar gamla data resultatens betydelse.
Felaktig tidsenhet: Tidtagarna anges i pikosekunder; konsekvent genom 1e12 andel.
En flod av sammanfattningar: Variabla literaler kan bryta mönster; normalisera SQL och performance_schema_max_sql_text_length check.
Historiken är för lång: Hög händelsefrekvens + lång historik skapar tryck; håll historikfönstret kort, ta ögonblicksbilder externt.

Checklista för praktiken

Definiera frågan, formulera en hypotes.
Aktivera lämpliga verktyg/konsumenter, håll omkostnaderna nere.
Töm sammanfattningarna, välj ett kort mätfönster.
Kontrollera Top-Digests, väntetider och I/O; bekräfta hotspots.
Anpassa index, frågor, kod och parametrar på ett målinriktat sätt.
Mät på nytt, spara ögonblicksbilder, dokumentera beslutet.
Minska konfigurationen till det absoluta minimumet.

I korthet: Hur jag går tillväga i praktiken

Jag aktiverar prestandaschemat på ett målinriktat sätt, börjar med ett brett urval och begränsar sedan till de mest användbara Instrument [1][2][12]. För att få en snabb överblick använder jag Sys-schemat och går vid behov ner på rådatnivå [13]. Jag åtgärdar först flaskhalsar i Digests och Wait-Events innan jag justerar parametrarna [3][15][17]. Därefter bekräftar jag varje ändring med nya mätningar, så att framstegen förblir synliga och reproducerbara. På så sätt säkerställer jag varaktigt tillförlitliga Svarstider och slipper onödigt arbete.