InnoDB Buffer Pool gegen JSON-Bodies in drei Sprachen

Das Notizenraster begann auf MySQL zu warten, als die Bodies echte Artikel wurden. LaraBoom speichert title, excerpt und body als JSON-Objekte mit en, ru und de. Für das Admin-Formular und für Vue ist das die richtige Form. Für SELECT * auf einer Liste nicht.

Das Raster braucht id, Kategorie, Titel, Excerpt, Bild, Größe, Daten. Drei HTML-Artikel braucht es nicht. Auf einem frischen Seed sind das ein paar Kilobyte, und es stört niemanden. Als die Bodies wuchsen, zog eine Liste mit fünfzig Zeilen etwa 1.2 MB JSON in den InnoDB Buffer Pool. Die JSON-API warf den Großteil weg, bevor die Antwort PHP verließ. Vue SSR zahlte denselben Read bei jedem öffentlichen Raster-Render.

Nutzer spürten eine langsame Liste. MySQL spürte einen Buffer Pool, der wie ein Cache von Artikel-HTML aussah. Indizes für Stack und News verloren Seiten an Notiz-Bodies, die die Kachel nie druckte.

Was der Buffer Pool tat

InnoDB liest Seiten, keine Spalten. Eine 16-KB-Seite mit einem fetten Body bringt diesen Body in den RAM, auch wenn die Query nur den Titel wollte. Liegt die Seite im Pool, bleibt sie, bis jemand den Platz braucht. Listenverkehr ist häufig. Detailverkehr nicht. Der Pool sah aus wie ein Cache von Artikel-HTML, den der Listenendpunkt immer wieder las.

innodb_buffer_pool_size auf diesem Docker-Host ist 512 MB. Das klingt nach viel, bis Redis, Memcached, php-fpm und Node auf derselben Maschine sitzen. Ich habe Innodb_buffer_pool_reads gegen Innodb_buffer_pool_read_requests beobachtet, während ich die Notizen in drei Locales scrollte. Die Hit-Ratio war in Ordnung. Der verschwendete RAM nicht. Seiten, die Indizes für Stack und News halten sollten, hielten Notiz-Bodies.

EXPLAIN sah harmlos aus. Filter is_published, Sortierung sort_order, Covering Index auf dem Papier. Der Index coverd keine Query, die die Body-Spalte trotzdem selektiert. Die Listenendpunkte von LaraBoom sind für Admin-Tabellen gebaut. Das öffentliche Raster hat sie wiederverwendet.

Die fette Spalte abtrennen

Eine zweite Datenbank will ich nicht. Ich will eine kurze Listenzeile. Der Body wandert nach note_bodies, Key note_id, eine Zeile, dieselbe JSON-Form. Die öffentliche Listenquery endet hier:

SELECT id, category_id, title, excerpt, image,
       published_at, size, sort_order, is_published
FROM notes
WHERE is_published = 1
ORDER BY sort_order ASC

Der Detailendpunkt joined oder macht eine zweite Query per id. Eine Extra-Query auf der Artikelseite ist billiger als fünfzig Bodies bei jedem Raster-Render, inklusive Vue-SSR-Durchlauf.

Admin lädt den Body weiter, der Editor braucht ihn. Dieser Pfad ist selten und authentifiziert. Ich ließ ihn als einzelnes Lesen der Nebentabelle. Ein locale-spezifisches Excerpt in Extra-Spalten habe ich nicht denormalisiert. Das Excerpt-JSON ist klein. Das Body-JSON ist das Problem.

JSON_EXTRACT ist kein Ausweg

JSON_EXTRACT(body, '$.ru') sieht aus, als würde es das Ergebnis verkleinern. Die gelesene Seite verkleinert es nicht. InnoDB zieht trotzdem den ganzen Clustered Record. Du zahlst das HTML im RAM, wirfst dann zwei Sprachen in SQL weg, und vielleicht die dritte in PHP, wenn die Liste sie gar nicht braucht.

Eine Generated Column plus Secondary Index hilft Filtern. Einer Liste, die den Body nie sehen soll, hilft sie nicht. Generated Columns kopieren Daten. Drei stored generated columns für drei Locales verdreifachen den schlechtesten Teil der Zeile.

• Listenspalten bleiben auf notes.
• HTML lebt auf note_bodies.
• Die Liste wird in Redis nach der kurzen Query gecacht, nicht davor.

Nach dem Split kamen dieselben fünfzig Zeilen aus MySQL mit etwa 40 KB. Die Buffer-Pool-Seiten für Notes sahen wieder wie Indexseiten aus. Das SSR des Rasters wartete nicht mehr auf einen Blob, den es nie druckte. Die Detailseite wurde nicht messbar langsamer. Falls doch, ist das die eine Query, die ich cachen will, per id, mit einem TTL, das beim Speichern in Admin stirbt.

Was ich daraus mitnehme

Ich messe die Listen-Payload-Größe und welche Seiten im Buffer Pool liegen, nicht nur die Hit-Ratio. Eine gute Hit-Ratio bei 1.2 MB ungenutztem HTML ist trotzdem eine schlechte Listenquery.

JSON_EXTRACT und einem Covering Index auf dem Papier traue ich nicht, solange das SELECT noch body nennt. Admin-Listenendpunkte sind kein öffentliches Raster.

Die Regel, die ich behalte: steht eine Spalte nicht auf der Kachel, gehört sie nicht in die Listenquery. JSON für i18n ist in Ordnung. JSON für i18n, das den Artikel enthält, ist eine Detail-Resource. Cache in Redis nach der kurzen Query.