Databases

MySQL Performance Schema op een zinvolle manier inzetten voor betere prestaties

Voor betere MySQL-prestaties Ik gebruik het Performance Schema om uitvoeringsgegevens over query’s, wachttijden, vergrendelingen, geheugen en I/O rechtstreeks met SQL te analyseren. Zo kan ik de oorzaken van trage statements sneller opsporen en gerichte maatregelen nemen voor Afstemmen en monitoring uit [2][3][15].

Centrale punten

De volgende aandachtspunten helpen mij om het Performance Schema effectief te gebruiken.

Activering en een gestroomlijnde configuratie met bijpassende instrumenten en verbruikers
Statement-digests gebruiken om dure patronen en hotspots te herkennen
Wachtgebeurtenissen, Locks en I/O gezamenlijk analyseren om echte knelpunten op te sporen
Sys-schema als afkorting voor snelle, bruikbare inzichten
Iteratief Werkwijze: meten, isoleren, aanpassen, opnieuw meten

Het prestatieschema activeren en op een zinvolle manier configureren

Ik controleer eerst of performance_schema is ingeschakeld, want in de huidige MySQL-versies is deze functie standaard ingeschakeld [1][12]. Als deze ontbreekt, stel ik in het [Mysqld]-blok van de my.cnf de variabele performance_schema=ON en start de server opnieuw op. Daarna stel ik instrumenten en verbruikers gericht in, in plaats van alles permanent op maximaal te zetten. Ik concentreer me op statement/%, wait/% en relevante I/O-paden, zodat ik zinvolle gegevens kan verzamelen zonder onnodige overhead [6]. Voor een nieuwe reeks metingen wis ik de betreffende historietabellen en begin ik met een schone Basis.

Snel resultaat met het Sys-schema

Om snel een overzicht te krijgen, pak ik vaak de sys-schema, omdat het de ruwe gegevens van het Performance-schema op een zinvolle manier samenvat [13]. Zo vind ik binnen enkele minuten de query’s die het grootste deel van de uitvoeringstijd voor hun rekening nemen. Ik begin met de top-statements, controleer de File-I/O-weergaven en bekijk de wait-summaries voor threads. Zodra ik een hotspot heb geïdentificeerd, ga ik terug naar de ruwe tabellen en verfijn ik de analyse. Wie queryplannen naloopt, kan met passende Tips voor de Optimizer vaak al na korte tijd merkbaar Winsten bereiken.

De juiste instrumenten en verbruikers kiezen

Ik begin breed, maar houd de observatie beheersbaar: eerst activeer ik de belangrijkste Instrumenten voor statements, wachttijden en I/O; daarna schakel ik alles uit wat geen informatie oplevert [6]. Toepassingen zoals event-history en summary-tabellen moeten de vragen ondersteunen die ik wil beantwoorden. Als het bijvoorbeeld om latentiepieken gaat, kijk ik in events_waits_summary_global_per_evenementnaam en vergelijk dat met overzicht_van_verklaringen_over_evenementen_per_samenvatting. Als er I/O-wachttijden optreden, controleer ik bestandssamenvatting_per_gebeurtenisnaam en table_io_waits_summary_per_tabel. Deze gerichte selectie houdt de overhead laag en levert toch betrouwbare Gegevens.

Statement-digests: patronen herkennen, belasting verminderen

Met statement-digests kan ik zien welke patronen op de lange termijn duur zijn, zelfs als afzonderlijke query’s variërende letterlijke waarden bevatten [17]. Ik sorteer op totale tijd, aantal uitvoeringen en gemiddelde latentie om prioriteiten vast te stellen. Daarbij maak ik aanvullend gebruik van de Het logboek met trage query's analyseren terug, om zeldzame uitschieters niet over het hoofd te zien. Als de samenvattingen pieken vertonen, controleer ik de indexen, JOIN-strategieën en de volgorde van de filters met UITLEGGEN. Vervolgens controleer ik het effect door opnieuw metingen uit te voeren in het Performance-schema, zodat de optimalisaties meetbaar blijven.

Wachtgebeurtenissen, locks en I/O interpreteren

Als er verzoeken vastlopen, raadpleeg ik de wait- en lock-tabellen om de daadwerkelijke Oorzaak te vinden [3]. Als er veel threads op dezelfde tabellen draaien, duidt dit erop table_lock-Ik wacht op concurrentie. Als er bij bestands-I/O-gebeurtenissen hoge latenties optreden, controleer ik de opslag en caching, evenals de opvraagpatronen met uitgebreide scans. Als ik InnoDB-rijvergrendelingen zie, analyseer ik hot records, transactieduur en indexdekking. Pas als deze puzzelstukjes in elkaar passen, ga ik aan de slag met serverparameters, het schema of de code.

Geheugenmonitoring: geheugen en bufferpool

Ik pak geheugenproblemen aan door de bezettingsgraad van geheugentabellen en InnoDB-buffers te controleren. Als het geheugengebruik van afzonderlijke componenten toeneemt, pas ik de limieten aan en controleer ik of caches de verkeerde gegevens vasthouden. Als de InnoDB-cache niet toereikend is, verhoog ik het aandeel ervan of verbeter ik de query-localiteit. Wie zich hier verder in wil verdiepen, kan via De bufferpool optimaliseren aanzienlijke verbeteringen in de latentie realiseren. Ik bevestig het effect met de Samenvatting-Tabellen en houd bij of LRU-hits en I/O-wachttijden zich in de juiste richting ontwikkelen.

Iteratieve diagnose-workflow voor dagelijks gebruik

Ik werk altijd in duidelijke cycli, zodat ik geen tijd verlies en veranderingen meetbaar blijven [3]. Eerst reproduceer ik het probleem onder gecontroleerde belasting. Vervolgens verzamel ik meetwaarden in een paar gerichte tabellen en selecteer ik de meest opvallende kandidaten. Vervolgens pas ik datgene aan wat het grootste voordeel belooft: index, query, parameter of code. Tot slot meet ik opnieuw en documenteer ik kort Voor/Na-tabellen, zodat het team het effect meteen kan zien.

Voorbeelden van zoekopdrachten: van ruwe gegevens naar beslissingen

Voor veelvoorkomende vragen heb ik korte SQL-fragmenten opgeschreven die ik in de dagelijkse praktijk direct gebruik. De tabel toont voorbeelden die ik vaak gebruik en waarvoor ze dienen. Ik pas filters aan zoals LIMIET of ORDER BY afhankelijk van de situatie. Belangrijk blijft: eerst een hypothese, dan een gerichte evaluatie en een duidelijke beslissing. Zo houd ik de analyse gefocust en vermijd ik overbodige Belasting.

Tabel(len) met prestatieschema's	Doel	Belangrijke kolommen	Voorbeeldquery
`overzicht_van_verklaringen_over_evenementen_per_samenvatting`	Dure patronen vinden	`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_per_evenementnaam`	Wacht-hotspots	`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_per_tabel`	Tabel-I/O controleren	`object_schema`, `objectnaam`, `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;`
`memory_summary_global_per_gebeurtenisnaam`	Geheugenvreters opsporen	`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;`

Productiebedrijf: overhead minimaliseren, effect maximaliseren

Tijdens live-optredens schakel ik instrumenten niet zomaar in, maar kies ik alleen wat mijn vraag beantwoordt [6]. Ik ga voorzichtig om met gebeurtenissen met een hoge frequentie en houd de geschiedenisvensters kort. Voor langere observaties geef ik de voorkeur aan gecomprimeerde samenvattingen en sla ik momentopnames extern op. Ik let op de invoer in performance_schema_setup_consumers, zodat ik de collecties kan sturen in plaats van ze op hun beloop te laten. Deze focus houdt de analyse efficiënt en beschermt de server.

Fijnafstemming: setup-instrumenten en -gebruikers in de praktijk

Om snel betrouwbare resultaten te krijgen, stel ik instrumenten en verbruikers doelgericht in. Bijzonder belangrijk zijn statement/%, wait/%, wait/io/% en – indien nodig – geselecteerde memory/%-paden. Ik activeer eerst alleen het hoogstnodige en breid dit vervolgens uit als er nog concrete vragen onbeantwoord blijven. De timers in het Performance-schema meten in pikoseconden; voor seconden deel ik de latentiekolommen door 1e12.

Typisch startpunt tijdens de uitvoering:

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

-- Belangrijke consumers selecteren
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');

-- Samenvattingen leegmaken voor een nieuwe reeks metingen
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;

Als ik geheugenanalyses nodig heb, schakel ik deze selectief in memory/%-Instrumenten. Dat kost meer overhead, maar is de moeite waard bij lekken of bij zware belasting van de allocator.

Dimensies: inzicht in gebruikers, hosts en schema’s

Piekbelastingen zijn vaak niet algemeen, maar beperkt tot bepaalde Gebruiker, Hosts of een Regeling beperkt. Het prestatieschema biedt hiervoor overzichten per account en host. Daarnaast bevat de samenvatting de kolom schema_naam, om het aantal hotspots per database te beperken.

Voorbeelden die ik vaak gebruik:

Top-schema's op basis van totale looptijd: 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;
Gebruikers/hosts die de meeste latentie veroorzaken (via accounts): 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;
Discussies met de langste wachttijd: 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;

Met deze weergaven kan ik verkeerssegmenten gericht scheiden en per klant het verkeer beperken, in de cache opslaan of queryvarianten invoeren.

Lange transacties en metadata-vergrendelingen zichtbaar maken

Transacties die lang duren of inactief zijn, blokkeren checkpoints, purge en concurrerende DML. Daarom controleer ik regelmatig het transactieoverzicht en de MDL-waits:

Actieve transacties: SELECT thread_id, timer_wait/1e12 AS sec_running, state FROM performance_schema.events_transactions_current ORDER BY sec_running DESC LIMIT 10;
Metadata-vergrendelingen (DDL/DML-concurrentie) herkennen: 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;

Als MDL de overhand heeft, pas ik de DDL-vensters aan, beperk ik de lock-duur in de code (kortere transacties) en controleer ik of er overbodige AUTOCOMMIT=0-Sessies onnodig lang open laten staan.

Replicatie, back-ups en bijwerkingen in het oog houden

Replicatie- en back-upprocessen komen voor in de Waits- en I/O-overzichten. Vertragingen kunnen worden opgespoord aan de hand van de status van de workers en bestandswachttijden. Ik kijk naar de Applier-Worker, de SQL-thread en de File-I/O-gebeurtenissen:

Applier-Worker met hoge latentie: SELECT worker_id, THREAD_ID, APPLYING_TRANSACTION, APPLYING_STATE FROM performance_schema.replication_applier_status_by_worker;
Hotspots bij bestands-I/O tijdens back-ups: 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;

Als ik hier knelpunten zie, ontkoppel ik I/O-fasen (bijv. windowing, I/O-scheduler, back-up-throttling) of verhoog ik het aantal parallelle applicator-workers, mits de workload schaalbaar is.

Tijdvensters, momentopnames en resetstrategieën

Metingen vereisen duidelijke tijdsvensters. Voor „voor/na“ werk ik met gerichte resets en momentopnames:

Samenvattingen resetten om nieuwe intervallen te krijgen: TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
Een momentopname extern opslaan: CREATE TABLE IF NOT EXISTS perf_snapshot_digest AS SELECT NOW() AS captured_at, * FROM performance_schema.events_statements_summary_by_digest;
Korte historische gegevens bijhouden (consumenten), lange trends extern verzamelen.

Zo kan ik optimalisaties veilig vergelijken en documenteren, ongeacht de deployments, parameterwijzigingen of schemawijzigingen.

Overhead en geheugengebruik beheren

Een veelvoorkomend vooroordeel is dat het Performance Schema „te duur“ zou zijn. In de praktijk houd ik de overhead laag door drie maatregelen te nemen: alleen relevante instrumenten activeren, veelgebruikte history-consumers kort houden en de opslagparameters op de juiste manier instellen. Bij een hoge digest-variantie verhoog ik doelgericht performance_schema_digests_size en – indien nodig – performance_schema_max_sql_text_length, zodat identiteiten stabiel blijven. Als er geheugeninstrumenten nodig zijn, beperk ik die tot problematische subsystemen.

Typische instelschroeven in 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
# Optioneel bij veel patronen:
performance_schema_digests_size=10000
performance_schema_max_sql_text_length=4096

Bij elke wijziging controleer ik of de CPU, de latentie en het geheugengebruik stabiel blijven. Zodra de diagnose is afgerond, zet ik de configuratie weer terug naar het „minimum voor gebruik“.

Veelvoorkomende patronen en snelle oplossingen

Een hoog bedrag in overzicht_van_verklaringen_over_evenementen_per_samenvatting, veel scans: Controleer indexen, filtervolgorde en sargability; bevestig met UITLEGGEN en herhaal de meting (de digestietijd moet zichtbaar afnemen).
Dominante table_io_waits in een paar tabellen: De I/O-lokalisatie verbeteren (toegang via geclusterde indexen, covering-indexen), de hoeveelheid gegevens per statement verminderen, indien nodig batchverwerking toepassen in plaats van bewerkingen op de volledige tabel.
Wachttijden voor wait/lock/innodb/%: Hot-records identificeren, schrijfconflicten oplossen door middel van kleinere transacties, geschikte indexen of wachtrijen.
Veel wait/lock/metadata/sql/mdl: DDL-vensters plannen, ONLINE-Geef de voorkeur aan operaties die dit ondersteunen, en ontkoppel Reader/Writer door kortere transacties te gebruiken.
Stijging van de opslag in memory_summary_global_per_gebeurtenisnaam: Limieten aanscherpen, query-caches gericht beperken, problematische componenten met memory/% gedetailleerd uitsplitsen.
„Spiky“ latentie bij verder onopvallende gemiddelde waarden: Gebruik Sys-weergaven met percentielen en meet indien nodig piekbelastingen afzonderlijk (kleiner venster, korte geschiedenis, gerichte instrumenten).

Correlatie: van thread naar statement en wait

Om oorzaken snel met elkaar in verband te brengen, koppel ik performance_schema.threads met de tabellen ‘Current’ en ‘History’ voor statements en waits. Zo kan ik zien wat een betreffende thread als laatste heeft gedaan en waarop hij wacht. Een beknopt overzicht:

Getroffenen PROCESSLIST_ID respectievelijk THREAD_ID van performance_schema.threads halen.
Laatste verklaring via evenementen_verklaringen_geschiedenis bepalen (volgens THREAD_ID en op tijd sorteren).
Parallelle wachttijden uit events_waits_history controleren om te zien of er sprake is van lock- of I/O-wachtredenen.

Dit „Drilldown & Join“-patroon is mijn standaardaanpak wanneer afzonderlijke sessies of webverzoeken uit de pas raken.

Kwaliteitscontroles en continue prestaties

Om te voorkomen dat optimalisaties verloren gaan, stel ik gestroomlijnde ‘quality gates’ in: voorafgaand aan en na elke release worden vooraf gedefinieerde query’s uit het Performance Schema uitgevoerd. Ik maak snapshots, vergelijk kengetallen (top-digests, top-waits, I/O per tabel) en documenteer afwijkingen. In CI/CD voeg ik representatieve belastingprofielen en drempelwaarden voor het 95e percentiel toe. Als een metriek buiten de norm valt, is er een duidelijk terugkoppelingskanaal: hypothese toetsen, instrumenten focussen, fix implementeren, opnieuw meten.

Foutbronnen vermijden

Op den duur te veel instrumenten: De diagnose is tijdelijk; laat in de normale bedrijfsmodus alleen de minimale set actief.
Gemengde meetperioden: Leeg de samenvattingen vóór nieuwe tests, anders verzwakken oude gegevens de zeggingskracht.
Verkeerde tijdseenheid: De timers zijn in pikoseconden; consequent door 1e12 delen.
Een stortvloed aan samenvattingen: Variabele letterlijke waarden kunnen patronen doorbreken; SQL normaliseren en performance_schema_max_sql_text_length controleren.
Geschiedenis te lang: Een hoge frequentie van gebeurtenissen + een lange geschiedenis zorgen voor druk; houd het geschiedenisvenster kort, maak extern momentopnames.

Praktische checklist

De vraag formuleren, de hypothese vastleggen.
De juiste instrumenten/consumenten inzetten, de overhead laag houden.
Samenvattingen leegmaken, een kort meetvenster selecteren.
Top-Digests, wachttijden en I/O controleren; hotspots bevestigen.
Index/query/code/parameters gericht aanpassen.
Opnieuw meten, momentopnames maken, de beslissing vastleggen.
De configuratie terugbrengen naar het minimale bedrijfsniveau.

Kort samengevat: mijn werkwijze in de praktijk

Ik activeer het prestatieschema doelgericht, begin breed en beperk het vervolgens tot de meest nuttige elementen Instrumenten [1][2][12]. Voor een snel overzicht maak ik gebruik van het Sys-schema en duik ik indien nodig in de ruwe gegevens [13]. Ik pak hotspots eerst aan bij digests en wait-events, voordat ik parameters aanpas [3][15][17]. Daarna bevestig ik elke wijziging met nieuwe metingen, zodat de vooruitgang zichtbaar en reproduceerbaar blijft. Zo zorg ik voor blijvend betrouwbare Reactietijden en bespaar jezelf onnodig werk.