Ein Leistungsfehler bei MySQL-Partitionstabellen

1. Problembeschreibung

Kürzlich stieß ich auf ein Problem. Bei der Verwendung partitionierter Tabellen zum Abfragen/Laden von Daten sank die Leistung im Vergleich zu normalen Tabellen um etwa 50 %. Der Hauptengpass trat bei der CPU auf. Da es sich um den CPU-Engpass handelte, können wir perf top -a -g und pstack sammeln, um den Leistungsengpass zu finden. Gleichzeitig stellten wir im Vergleich zu normalen Tabellen fest, dass die CPU hauptsächlich in der Funktion build_template_field verbraucht wurde, wie in der folgenden Abbildung dargestellt:

2. Stapelanalyse mit pt-pmap

Um dies mit perf top -g -a zu überprüfen, haben wir zu diesem Zeitpunkt auch pstack erhalten. Aufgrund der großen Anzahl von Threads haben wir ihn über pt-pmap formatiert, um den Erhalt nützlicher Informationen wie folgt zu erleichtern:

Nach dem Formatieren haben wir den Idle-Wartestapel entfernt und wie oben gezeigt eine große Anzahl davon gefunden, was auch durch die Leistung in perf top -a -g bestätigt wurde.

3. Analyse der Engpässe in dieser Spalte

Wir sehen, dass hier viel cpu verbraucht wird

ha_innobase::build_template
 ->Build_Template_Feld
   ->dict_col_get_cluster_pos

template ist fast immer an eine bestimmte Abfrage gebunden, das heißt, für eine normale Anweisung ist mindestens eine template erforderlich. Seine Struktur ist row_prebuilt_t und enthält das Abfragetupel, die Abfragetabelle, den für die Abfrage verwendeten Index, transaktionsbezogene Informationen, den persistenten Cursor, die Länge der Abfragezeile der MySQL-Ebene, Auto-Inkrement-Informationen, ICP-bezogene Informationen, mysql_row_templ_t -Struktur und andere Informationen. Die mysql_row_templ_t -Information ist für jedes Feld eine. Ihre Hauptfunktion besteht darin, die zugehörigen Attribute der Feldinformationen auf der MySQL-Ebene und die Spalteninformationen auf der Innodb-Ebene aufzuzeichnen. Sie wird verwendet, um eine Datensatzzeile schnell zwischen der MySQL-Ebene und der Innodb-Ebene zu konvertieren. Um mysql_row_templ_t zu initialisieren, wird die obige Logik angezeigt.

Die Logik ist ungefähr wie folgt:

Durchlauf durch jedes Feld in der Tabelle (eine Schleifenebene) ha_innobase::build_template
Ist es ein Feld, auf das zugegriffen werden muss? build_template_needs_field
Dies umfasst alle Felder, die abgefragt und geschrieben werden. Je mehr Felder Sie abrufen müssen, desto langsamer wird es.
Wenn nicht, setzen Sie die Schleife nicht fort
Wenn Sie Zugriff benötigen
build_template_field (gefüllt mit mysql_row_templ_t-Struktur)
Durchlaufen Sie jedes Feld des Primärschlüssels (Schleife zweiter Ebene).
Einschließlich Pseudospalten sind alle Felder in der Tabelle der Primärschlüssel. Je mehr Felder in der Tabelle sind, desto langsamer ist sie) dict_col_get_clust_pos
Bestätigen Sie die Position dieses Feldes im Primärschlüssel
pos0 Primärschlüssel pos1 DB_TRX_ID pos2 DB_ROLL_PTR pos3 andere Felder für Benutzer
Durchlaufe jedes Feld des Index (zweistufige Schleife, aber es gibt normalerweise nicht zu viele Indexfelder, also wird es hier nicht langsam) dict_index_t::get_col_pos
Bestätigen Sie die Position dieses Feldes im Index. Wenn nicht, geben Sie NULL zurück.
Return pos Beispielsweise ist der Primärschlüssel id1 und der Sekundärindex id2 id3. Der Sekundärindex ist pos0 id2 pos1 id3 pos2 id1
Fahren Sie mit der Vervollständigung anderer Eigenschaften fort, beispielsweise MySQL-Null-Bitmap, MySQL-Anzeigelänge, MySQL-Zeichensatz usw.

Hier können wir sehen, dass es tatsächlich zwei Schichten von Schleifen gibt, d. h. Schleifen innerhalb von Schleifen (Zeitkomplexität O(M×N)), und die Schleifen haben an zwei Stellen die größte Auswirkung:

• Erste Ebene, die Anzahl der Felder in der Tabelle
• In der zweiten Schicht werden die Felder, auf die zugegriffen werden muss (sowohl lesend als auch schreibend), durch den Primärschlüssel (also alle Felder) geleitet.

Deshalb ist es hier langsam. Vorlagen werden jedoch normalerweise nicht mehrfach für eine Abfrage erstellt. Beispielsweise wird eine große Abfrage einer allgemeinen Tabelle nur erstellt, bevor die Anweisung zum ersten Mal Daten findet. Dies ist ein besonderer Punkt beim Vergleich zwischen partitionierten Tabellen und allgemeinen Tabellen. Lassen Sie es mich unten beschreiben.

4. Mehrfaches Anlegen von Vorlagen in der Partitionstabelle

Angenommen, wir haben die folgende Partitionstabelle:

Tabelle erstellen t(
    id1 int,
    id2 int,
    Primärschlüssel (id1),
    Schlüssel(id2)
)engine=innodb
Partition nach Bereich (id1)(
    Partition p0-Werte kleiner als (100),
    Partition p1 Werte kleiner als (200),
    Partition p2-Werte kleiner als (300));    

in t-Werte (1,1) einfügen;
in t-Werte (101,1) einfügen;
in t-Werte (201,1) einfügen;
in t-Werte (2,2) einfügen;
in t-Werte (3,2) einfügen;
in t-Werte (4,2) einfügen;
in t-Werte einfügen (7,2);
in t-Werte (8,2) einfügen;
in t-Werte einfügen (9,2);
in t-Werte (10,2) einfügen;

Wir verwenden die Anweisung „ select * from t where id2=1 “. Offensichtlich ist id2 ein sekundärer Index. Da alle sekundären MySQL-Indizes lokale Partitionen sind, werden die Werte hier auf drei Partitionen verteilt. Für eine solche Anweisung wird, wenn die normale Tabelle weiterhin auf die Position nach der letzten Positionierung ( next_same ) zugreifen soll, diese durch Kapselung der Partitionstabelle index read geändert, um sie erneut zu lokalisieren. Wir können deutlich sehen, dass dies die nächste Partition scannt und ihr Teil = 1 die zweite Partition ist, die unser p1 ist (der erste ist 0).

Auf diese Weise muss template für jede scan next partition ), was zum oben genannten Problem führt. Das ist verständlich. Die neue Partition ist eine neue InnoDB-Datei, daher ist der zuletzt gefundene persistente Cursor tatsächlich nutzlos, was einem neuen Tabellenzugriff gleichkommt. Hier ist eine weitere Beurteilung , ob template erstellt werden soll:

  wenn (m_prebuilt->sql_stat_start) {
    build_template(falsch);
  }

m_prebuilt->sql_stat_start wird nicht nur zu Beginn der Anweisung auf „true“ gesetzt, sondern auch jedes Mal, wenn die Partition wie folgt geändert wird:

ha_innopart::partition_einstellen:
m_prebuilt->sql_stat_start = m_sql_stat_start_parts.test(part_id);

5. Über ein spezielles Verfahren

Es gibt auch einen Stapel in unserem Fehler pstack wie folgt:

Dieser Stapel ist eigentlich nicht vollständig, aber Partition_helper::handle_ordered_index_scan erscheint darin. Diese Funktion ist eigentlich mit der Sortierung der Partitionstabelle verbunden. Wenn wir eine solche Situation betrachten, müssen wir für den sekundären Index select max(id2) from t zuerst auf jede Partition zugreifen, um den Maximalwert zu erhalten, und dann den Maximalwert jeder Partition vergleichen, um das Endergebnis zu erhalten. MySQL verwendet für die Verarbeitung eine Prioritätswarteschlange, die Teil der von dieser Funktion abgeschlossenen Funktion sein sollte (ich habe es mir nicht genau angesehen). Zweitens haben wir zunächst QUICK_RANGE_SELECT , das für Bereichsabfragen verwendet wird, also konstruieren wir es wie folgt:

wähle * aus t, wobei ID2<2, sortiere nach ID2;

Stapel:

Dies liegt daran, dass das Feld id2 lediglich innerhalb der Partition für eine Sortierung der Daten nach Größe sorgt, in der gesamten Tabelle jedoch durcheinander kommt und eine zusätzliche Verarbeitung erfordert.

6. Problemsimulation

Mit diesen Vorbereitungen können wir eine Partitionstabelle mit 300 Feldern und 25 Partitionen erstellen. Die neueste Testversion ist 8.0.26

Tabelle tpar300col erstellen(
    id1 int,
    id2 int,
    id3 int,
    id4 int,
...
    id299 varchar(20),
    id300 varchar(20),
    Primärschlüssel (id1),
    Schlüssel(id2)
)engine=innodb
Partition nach Bereich (id1)(
    Partition p0-Werte kleiner als (100),
    Partition p1 Werte kleiner als (200),
    Partition p3 Werte kleiner als (300),
 ...
    Partition p25-Werte kleiner als (2500));  

in tpar300col Werte einfügen (1, 1, 1,
.... fügen Sie ein Datenelement pro Partition in tpar300col values(2401,1,1

Konstruieren Sie dann eine andere Daten-ID2 ungleich 1 und erstellen Sie eine gespeicherte Prozedur:

Trennzeichen //

PROZEDUR ERSTELLEN test300col()
beginnen 
  Deklariere num int;
  setze Num = 1; 
während num <= 1000000
  Wählen Sie * aus tpar300col, wo ID2=1;
  setze Num = Num+1;
Ende während;
Ende //
implementieren:
 /opt/mysql/mysql3340/install/mysql8/bin/mysql -S--socket=/opt/mysql/mgr3315/data/mgr3315.sock -e'use test;call test300col();' > log.log

Dann stellt perf top Folgendes fest:

Dies bestätigte das Problem.

VII. Fazit

Dieses Problem hängt tatsächlich mit der Datenstreuung des Sekundärindex im Verhältnis zum Partitionsschlüssel zusammen, wir können jedoch die Daten des Sekundärindex nicht steuern und müssen den Index verwenden. Wir können nur versuchen, es auf irgendeine Weise zu vermeiden. Natürlich habe ich auch einen BUG gemeldet, und zwar wie folgt:

https://bugs.mysql.com/bug.php?id=104576

Ich weiß nicht, ob es eine Möglichkeit gibt, dieses Problem zu beheben. Beispielsweise sind bei einer partitionierten Tabelle die Felder jeder Partition tatsächlich gleich. Muss ich mysql_row_templ_t.clust_rec_field_no jedes Mal neu erstellen? Wenn dies nicht erforderlich ist, löst sich das Problem von selbst. Der Beamte hat bestätigt, dass dieses Problem besteht. So können Sie dies vermeiden:

• Die Partitionstabelle sollte nicht zu viele Felder haben
• Die zu erreichenden Felder sollten nicht immer select * verwenden.
• Vermeiden Sie die Verwendung einer Hash-Partitionierung, da diese dieses Problem verschlimmern kann.

Dies ist das Ende dieses Artikels über einen Leistungsfehler in MySQL-Partitionstabellen. Weitere Informationen zu einem Leistungsfehler in MySQL-Partitionstabellen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!