Lösung für das Problem des MySQL-Threads beim Öffnen von Tabellen

Problembeschreibung

Vor kurzem gab es einen MySQL5.6.21-Server. Nach der Veröffentlichung der Anwendung stiegen die gleichzeitig ausgeführten Threads Threads_running schnell auf etwa 2000. Eine große Anzahl von Threads wartete auf das Öffnen und Schließen von Tabellen, und der zugehörige logische Zugriff auf der Anwendungsseite wurde zeitlich überschritten.

【Analyseprozess】

1. Nachdem die Anwendung um 16:10 Uhr freigegeben wurde, steigt Opened_tables weiter an, wie in der folgenden Abbildung dargestellt:

Überprüfen Sie die während des Fehlers erfasste pt-stalk-Protokolldatei. Zum Zeitpunkt 18.01.2019 16:29:37 betrug der Wert von Open_tables 3430 und der Konfigurationswert von table_open_cache 2000.

Wenn der Wert „Open_tables“ größer ist als der Wert „table_open_cache“, können bei jedem Öffnen einer Tabelle durch eine neue Sitzung einige davon nicht auf den Tabellencache zugreifen und müssen erneut geöffnet werden. Dies spiegelt das Phänomen wider, dass sich eine große Anzahl von Threads im Zustand „Tabellen öffnen“ befindet.

2. Die Gesamtzahl der Tabellen in dieser Instanz plus der Systemdatenbank beträgt 851, was viel weniger ist als 2000 in table_open_cache. Warum erreicht Open_tables 3430?

Aus der offiziellen Dokumentation können wir eine Erklärung entnehmen.

https://dev.mysql.com/doc/refman/5.6/en/table-cache.html

table_open_cache ist mit max_connections verknüpft. Geben Sie beispielsweise für 200 gleichzeitig laufende Verbindungen eine Tabellencachegröße von mindestens 200 * N an, wobei N die maximale Anzahl von Tabellen pro Join in allen von Ihnen ausgeführten Abfragen ist.

Zu diesem Zeitpunkt erreichte die Anzahl gleichzeitiger Threads 1980. Unter der Annahme, dass 30 % dieser gleichzeitigen Verbindungen auf zwei Tabellen und der Rest auf eine einzelne Tabelle zugegriffen haben, würde die Cachegröße (1980*30 %*2+1980*70 %*1)=2574 erreichen.

3. QPS war vor und nach der Veröffentlichung relativ stabil. Aus Sicht der externen Anfragen gab es keinen plötzlichen Anstieg der Verbindungsanfragen. Nach der Veröffentlichung stieg threads_running jedoch auf einen Höchststand von fast 2000 und blieb weiter bestehen. Es wird vermutet, dass das Problem durch eine bestimmte SQL-Anweisung ausgelöst wurde.

4. Überprüfen Sie die zu diesem Zeitpunkt erfassten Prozesslisteninformationen. Es gibt eine SQL-Anweisung mit hohem gleichzeitigen Zugriff, die 8 physische Tabellen abfragt. Das SQL-Beispiel lautet wie folgt:

<code>Wählen Sie ID, Name und E-Mail aus Tabelle1 aus, und verbinden Sie Tabelle2.<br>Alles vereinigen<br>Wählen Sie ID, Name und E-Mail aus Tabelle3 aus, und verbinden Sie Tabelle4.<br>Alles vereinigen<br>Wählen Sie ID, Name und E-Mail aus Tabelle5 aus, und verbinden Sie Tabelle6.<br>Alles vereinigen<br>Wählen Sie ID, Name und E-Mail aus Tabelle7 aus, und verbinden Sie Tabelle8.<br>wobei ID in ('aaa');</code>

5. Erstellen Sie dieselben 8 Tabellen in der Testumgebung, löschen Sie den Tabellencache und vergleichen Sie die Ergebnisse vor und nach der Ausführung von SQL in einer einzelnen Sitzung. Der Wert von Open_tables erhöht sich um 8. Bei hoher Parallelität erhöht sich der Wert von Open_tables erheblich.

Reproduktion des Problems

In der Testumgebung wurde ein Szenario mit hohem Parallelzugriff simuliert und 1.000 Threads wurden verwendet, um die oben genannten SQL-Anweisungen gleichzeitig auszuführen. Dies reproduzierte ein ähnliches Phänomen wie in der Produktionsumgebung. Die Anzahl der Open_tables erreichte schnell 3.800, und eine große Anzahl von Prozessen befand sich in den Zuständen „Opening tables“ und „Closing tables“.

Optimierungsplan

1. Nachdem wir die Ursache des Problems gefunden hatten, kommunizierten wir mit unseren Entwicklungskollegen und schlugen vor, das SQL zu optimieren, die Anzahl der SQL-Abfragetabellen mit einzelnen Anweisungen zu reduzieren oder die gleichzeitige Zugriffshäufigkeit des SQL deutlich zu verringern.

Doch bevor die Entwicklungskollegen Zeit zur Optimierung hatten, trat der Fehler in der Produktionsumgebung erneut auf. Während der Fehlersuche erhöhte der DBA table_open_cache von 2000 auf 4000. Die CPU-Auslastung stieg, aber die Auswirkung war nicht offensichtlich. Das Problem des Wartens auf das Öffnen von Tabellen bestand weiterhin.

2. Analysieren Sie die während des Fehlers erfassten pstack-Informationen und aggregieren Sie sie mit pt-pmp. Sie können sehen, dass beim Öffnen der Tabelle eine große Anzahl von Threads auf Mutex-Ressourcen wartet:

#0 0x0000003f0900e334 in __lll_lock_wait () von /lib64/libpthread.so.0
#1 0x0000003f0900960e in _L_lock_995 () von /lib64/libpthread.so.0
#2 0x0000003f09009576 in pthread_mutex_lock () von /lib64/libpthread.so.0
#3 0x000000000069ce98 in open_table(THD*, TABLE_LIST*, Open_table_context*) ()
#4 0x000000000069f2ba in open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) ()
#5 0x000000000069f3df in open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int) ()
#6 0x00000000006de821 in execute_sqlcom_select(THD*, TABLE_LIST*) ()
#7 0x00000000006e13cf in mysql_execute_command(THD*) ()
#8 0x00000000006e4d8f in mysql_parse(THD*, char*, unsigned int, Parser_state*) ()
#9 0x00000000006e62cb in dispatch_command(enum_server_command, THD*, char*, unsigned int) ()
#10 0x00000000006b304f in do_handle_one_connection(THD*) ()
#11 0x00000000006b3177 in handle_one_connection ()
#12 0x0000000000afe5ca in pfs_spawn_thread ()
#13 0x0000003f09007aa1 in start_thread () von /lib64/libpthread.so.0
#14 0x0000003f088e893d in Klon () von /lib64/libc.so.6

Derzeit ist der Mutex-Konflikt im table_cache_manager sehr schwerwiegend.

Da der Standardwert des Parameters table_open_cache_instances in MySQL 5.6.21 1 ist, sollte eine Erhöhung des Parameters table_open_cache_instances und der Tabellen-Cache-Partitionen die Konflikte lindern.

3. In der Testumgebung haben wir die beiden Parameter table_open_cache_instances=32 und table_open_cache=6000 angepasst und das problematische SQL gleichzeitig mit 1000 Threads ausgeführt. Diesmal verschwanden die Threads, die auf das Öffnen und Schließen von Tabellen warteten, und MySQLs QPS stieg ebenfalls von 12000 auf 55000.
In derselben Situation wurde nur table_open_cache=6000 angepasst und die Anzahl der Prozesse, die auf das Öffnen von Tabellen warten, sank von 861 auf 203. Das Problem wurde behoben. Mehr als 600 Prozesse wechselten vom Warten auf das Öffnen von Tabellen in den Ausführungsstatus und QPS stieg auf etwa 40.000. Das Problem kann jedoch nicht behoben werden.

Quellcodeanalyse

Überprüfen Sie den Code auf die Logik von table_open_cache:

1. Die Funktion Table_cache::add_used_table funktioniert wie folgt. Wenn die durch die neue Verbindung geöffnete Tabelle nicht im Tabellencache vorhanden ist, wird die geöffnete Tabelle der Liste der verwendeten Tabellen hinzugefügt:

bool Table_cache::used_table_add(THD *thd, TABLE *table)
{
 Tabellencacheelement *el;
 
 assert_owner();
 
 DBUG_ASSERT(Tabelle->in_Verwendung == thd);
 
 /*
 Versuchen Sie, Table_cache_element abzurufen, das diese Tabelle im Cache darstellt
 aus dem Array im TABLE_SHARE.
 */
 el = Tabelle->s->Cacheelement[Tabellen-Cachemanager.cache_index(dieses)];
 
 wenn (!el)
 {
 /*
  Wenn TABLE_SHARE keinen Zeiger auf das Element hat, das die Tabelle darstellt
  In diesem Cache muss das Element für die Tabelle in der Tabelle fehlen
  Cache.
 
  Neues Table_cache_element-Objekt zuweisen und zum Cache hinzufügen
  und Array in TABLE_SHARE.
 */
 DBUG_ASSERT(! my_hash_search(&m_cache,
         (uchar*)table->s->table_cache_key.str,
         Tabelle->s->table_cache_key.length));
 
 wenn (!(el= neues Table_cache_element(table->s)))
  gibt true zurück;
 
 wenn (my_hash_insert(&m_cache, (uchar*)el))
 {
  el löschen;
  gibt true zurück;
 }
 
 Tabelle->s->Cache-Element[table_cache_manager.cache_index(dieses)] = el;
 }
 
 /* Tabelle zur Liste der verwendeten Tabellen hinzufügen */ 
 el->used_tables.push_front(Tabelle);
 
 m_table_count++; gebe_unbenutzte_Tabellen_bei_Bedarf_frei(thd);
 
 gibt false zurück;
}

2. Bei jedem Aufruf von add_used_table wird die Funktion Table_cache::free_unused_tables_if_necessary aufgerufen. Wenn m_table_count > table_cache_size_per_instance &&m_unused_tables erfüllt ist, wird remove_table ausgeführt, um den redundanten Cache in der Liste m_unused_tables zu löschen. Darunter ist table_cache_size_per_instance = table_cache_size / table_cache_instances. Die Standardkonfiguration von MySQL5.6 ist 2000/1=2000. Wenn der Wert von m_table_count größer als 2000 ist und m_unused_tables nicht leer ist, wird remove_table ausgeführt, um den Tabellencache in m_unused_tables zu löschen. Auf diese Weise bleibt m_table_count, der Wert von Open_tables, normalerweise bei etwa 2000.

void Table_cache::unbenutzte_Tabellen_bei_Bedarf_freigeben(THD *thd)
{
 /*
 Wir haben zu viele TABLE-Instanzen. Versuchen wir, sie loszuwerden.
 
 Beachten Sie, dass wir möglicherweise mehr als ein TABLE-Objekt freigeben müssen.
 brauche die folgende Schleife, falls table_cache_size dynamisch geändert wird,
 zur Serverlaufzeit.
 */
 wenn (m_table_count > table_cache_size_per_instance und m_unused_tables)
 {
 mysql_mutex_lock(&LOCK_öffnen);
 während (m_table_count > table_cache_size_per_instance &&
   m_unbenutzte_Tabellen)
 {
  TABELLE *freizugebende_Tabelle= m_unbenutzte_Tabellen;  
  Tabelle entfernen (freizugebende Tabelle);
  intern_close_table(freizugebende_Tabelle);
  thd->status_var.table_open_cache_overflows++;
 }
 mysql_mutex_unlock(&LOCK_open);
 }
}

3. Erhöhen Sie table_cache_instances auf 32. Wenn Open_tables (2000/32=62) überschreitet, ist die Bedingung erfüllt, was die Bereinigung von m_unused_tables in der obigen Logik beschleunigt, die Anzahl der Tabellen-Caches weiter reduziert und dazu führt, dass Table_open_cache_overflows zunehmen.

4. Wenn table_open_cache_instances von 1 auf 32 erhöht wird, wird eine LOCK_open-Sperre auf 32 m_lock-Mutexe verteilt, was die Sperrkonflikte erheblich reduziert.

/** Sperre für Tabellen-Cache-Instanz erwerben. */
 void lock() { mysql_mutex_lock(&m_lock); }
 /** Sperre für Tabellen-Cache-Instanz aufheben. */
 void entsperren() { mysql_mutex_unlock(&m_lock); }

Lösung des Problems

Um das Problem zu lösen, haben wir außerdem folgende Optimierungsmaßnahmen in unserer Produktionsumgebung ergriffen:
1. Trennen Sie Lesen und Schreiben, erhöhen Sie die Anzahl der Leseknoten und verteilen Sie den Druck auf die Masterdatenbank.
2. Passen Sie table_open_cache_instances=16 an;
3. Passen Sie table_open_cache=6000 an;

Zusammenfassen

Wenn das Problem beim Öffnen von Tabellen auftritt,
1. Es wird empfohlen, die SQL-Anweisungen herauszufinden, die häufig Tabellen öffnen, das SQL zu optimieren, die Anzahl der von einer einzelnen SQL-Anweisung abgefragten Tabellen zu reduzieren oder die gleichzeitige Zugriffshäufigkeit des SQL deutlich zu reduzieren.

2. Legen Sie einen geeigneten Tabellencache fest und erhöhen Sie die Werte der Parameter table_open_cache_instances und table_open_cache.