Lösung des Problems, dass sich der MySQL-Thread beim Öffnen von Tabellen befindet (mit Beispielen)

Der Inhalt dieses Artikels befasst sich mit der Lösung des Problems des MySQL-Threads beim Öffnen von Tabellen. Er hat einen gewissen Referenzwert. Ich hoffe, er wird für Sie hilfreich sein.

Problembeschreibung

Vor Kurzem gibt es einen MySQL5.6.21-Server. Nach der Veröffentlichung der Anwendung nimmt die Anzahl gleichzeitiger Threads_running schnell zu und erreicht etwa 2000, was einer großen Anzahl entspricht der Threads warten auf den Status „Öffnen von Tabellen“, „Schließen von Tabellen“ oder auf Zeitüberschreitungen beim anwendungsseitigen logischen Zugriff.

[Analyseprozess]

1. Nach der Veröffentlichung der Anwendung um 16:10 Uhr steigt Opened_tables weiter an, wie in der folgenden Abbildung dargestellt:

Sehen Sie sich den Fehler zu diesem Zeitpunkt an. In der pt-stalk-Protokolldatei, die während des Zeitraums zum Zeitpunkt 2019-01-18 16:29:37 erfasst wurde, ist der Wert von Open_tables 3430 und der Konfigurationswert von table_open_cache ist 2000.
Wenn der Open_tables-Wert größer als der table_open_cache-Wert ist, können einige der Tabellen jedes Mal, wenn eine neue Sitzung die Tabelle öffnet, nicht auf den Tabellencache zugreifen und müssen die Tabelle erneut öffnen. Das darin zum Ausdruck kommende Phänomen besteht darin, dass sich im Eröffnungstabellenstatus eine große Anzahl von Threads befindet.

2. Die Tabellen in diesem Beispiel plus die Systemdatenbank betragen insgesamt 851, was weit weniger als die 2000 von table_open_cache ist.
Das lässt sich aus den offiziellen Dokumenten erklären.
https://dev.mysql.com/doc/refman/5.6/en/table-cache.html

table_open_cache is related to max_connections. For example, for 200 concurrent running connections, specify a table cache size of at least 200 * N, where N is the maximum number of tables per join in any of the queries which you execute.

Die Anzahl der gleichzeitigen Threads erreichte zu diesem Zeitpunkt 1980, wenn man davon ausgeht, dass 30 % dieser gleichzeitigen Verbindungen sind Zugriffstabellen auf 2 und die anderen sind alle Einzeltabellen, dann erreicht die Cachegröße (1980*30 %*2+1980*70 %*1) = 2574

3 ist vor und nach der Veröffentlichung relativ stabil. Den externen Anfragen zufolge gibt es keinen plötzlichen Anstieg der Verbindungsanfragen, aber nach der Veröffentlichung stieg threads_running auf einen Höchststand von fast 2.000 und hält an. Die Vermutung ist, dass eine bestimmte veröffentlichte SQL-Anweisung das Problem ausgelöst hat.

Überprüfen Sie die zu diesem Zeitpunkt erfassten Prozesslisteninformationen. Es gibt eine Aussage, dass der gleichzeitige SQL-Zugriff sehr hoch ist. Das SQL-Beispiel lautet wie folgt:

<code>select id,name,email from table1 left join table2<br/>union all<br/>select id,name,email from table3 left join table4<br/>union all<br/>select id,name,email from table5 left join table6<br/>union all<br/>select id,name,email from table7 left join table8<br/>where id in (&#39;aaa&#39;);</code>

5. Erstellen Sie in der Testumgebung dieselben 8 Tabellen, leeren Sie den Tabellencache und vergleichen Sie sie 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 wird deutlich zunehmen.

Reproduktion des Problems

Simulieren Sie das Szenario mit hohem gleichzeitigem Zugriff in der Testumgebung, führen Sie die obige SQL-Anweisung gleichzeitig mit 1000 Threads aus und reproduzieren Sie das Produktion Ein ähnliches Phänomen tritt in der Umgebung auf. Open_tables erreicht schnell 3800, und eine große Anzahl von Prozessen befindet sich im Status „Opening Tables“ und „Closing Tables“.

Optimierungsplan

1 Nachdem wir die Ursache des Problems ermittelt hatten, kommunizierten wir mit unseren Entwicklungskollegen und schlugen eine Optimierung des SQL vor, um die Anzahl der Einzelsatz-SQL zu reduzieren Abfragetabellen oder reduzieren Sie die Häufigkeit des gleichzeitigen SQL-Zugriffs erheblich.
Bevor die Entwicklungskollegen es jedoch optimieren konnten, trat der Fehler erneut in der Produktionsumgebung auf. Zu diesem Zeitpunkt wurde bei der Fehlerbehebung durch den DBA der Wert für table_open_cache von 2000 auf 4000 erhöht. Die CPU-Auslastung stieg, der Effekt war jedoch nicht offensichtlich. Das Problem des Wartens auf das Öffnen von Tabellen bestand weiterhin.

2. Nachdem wir die während des Fehlers erfassten Pstack-Informationen analysiert und mit pt-pmp aggregiert hatten, stellten wir fest, dass eine große Anzahl von Threads auf Mutex-Ressourcen wartete, als open_table:

#0  0x0000003f0900e334 in __lll_lock_wait () from /lib64/libpthread.so.0
#1  0x0000003f0900960e in _L_lock_995 () from /lib64/libpthread.so.0
#2  0x0000003f09009576 in pthread_mutex_lock () from /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 () from /lib64/libpthread.so.0
#14 0x0000003f088e893d in clone () from /lib64/libc.so.6

Dabei Zeitweise war der Mutex-Konflikt in table_cache_manager sehr ernst.
Da der Standardwert des Parameters table_open_cache_instances unter MySQL5.6.21 1 ist, dachte ich, dass eine Erhöhung des Parameters table_open_cache_instances und das Hinzufügen von Tabellen-Cache-Partitionen Konflikte lindern sollten.

3. In der Testumgebung haben wir die beiden Parameter table_open_cache_instances=32, table_open_cache=6000 angepasst und auch das problematische SQL mit 1000 Threads gleichzeitig ausgeführt. Diesmal sind die Threads, die auf das Öffnen von Tabellen und das Schließen von Tabellen warten, verschwunden , und MySQL QPS stieg ebenfalls von 12.000 auf 55.000.
Im Vergleich zur gleichen Situation sank die Anzahl der Prozesse, die auf das Öffnen von Tabellen warten, von 861 auf 203. Mehr als 600 Prozesse wurden vom Status „Warten auf das Öffnen von Tabellen“ in den laufenden Status geändert. und QPS stieg auf etwa 40.000, aber keine Heilung.

Quellcode-Analyse

Ich habe den Code auf die relevante Logik von table_open_cache überprüft:
1. Die Funktion Table_cache::add_used_table lautet wie folgt Verbindung öffnet die Tabelle. Wenn die Tabelle nicht im Cache vorhanden ist, öffnen Sie die Tabelle und fügen Sie sie zur Liste der verwendeten Tabellen hinzu:

bool Table_cache::add_used_table(THD *thd, TABLE *table)
{
  Table_cache_element *el;

  assert_owner();

  DBUG_ASSERT(table->in_use == thd);

  /*
    Try to get Table_cache_element representing this table in the cache
    from array in the TABLE_SHARE.
  */
  el= table->s->cache_element[table_cache_manager.cache_index(this)];

  if (!el)
  {
    /*
      If TABLE_SHARE doesn't have pointer to the element representing table
      in this cache, the element for the table must be absent from table the
      cache.

      Allocate new Table_cache_element object and add it to the cache
      and array in TABLE_SHARE.
    */
    DBUG_ASSERT(! my_hash_search(&m_cache,
                                 (uchar*)table->s->table_cache_key.str,
                                 table->s->table_cache_key.length));

    if (!(el= new Table_cache_element(table->s)))
      return true;

    if (my_hash_insert(&m_cache, (uchar*)el))
    {
      delete el;
      return true;
    }

    table->s->cache_element[table_cache_manager.cache_index(this)]= el;
  }

  /* Add table to the used tables list */  
  el->used_tables.push_front(table);

  m_table_count++;  free_unused_tables_if_necessary(thd);

  return false;
}

2. Jedes Mal, wenn add_used_table die Funktion Table_cache::free_unused_tables_if_necessary aufruft, wenn m_table_count > table_cache_size_per_instance &&m_unused_tables ist erfüllt, „remove_table“ wird ausgeführt und die Liste „m_unused_tables“ wird gelöscht. Unter ihnen ist table_cache_size_per_instance= table_cache_size / table_cache_instances 2000/1=2000. Wenn der m_table_count-Wert 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 ist m_table_count der Wert von Open_tables und bleibt normalerweise bei etwa 2000.

void Table_cache::free_unused_tables_if_necessary(THD *thd)
{
  /*
    We have too many TABLE instances around let us try to get rid of them.

    Note that we might need to free more than one TABLE object, and thus
    need the below loop, in case when table_cache_size is changed dynamically,
    at server run time.
  */
  if (m_table_count > table_cache_size_per_instance && m_unused_tables)
  {
    mysql_mutex_lock(&LOCK_open);
    while (m_table_count > table_cache_size_per_instance &&
           m_unused_tables)
    {
      TABLE *table_to_free= m_unused_tables;      
      remove_table(table_to_free);
      intern_close_table(table_to_free);
      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, wird die Bedingung erfüllt, was die Bereinigung von m_unused_tables in der obigen Logik beschleunigt und die Anzahl im Tabellencache weiter reduziert. was dazu führt, dass Table_open_cache_overflows ausgelöst wird.

4、当table_open_cache_instances从1增大到32时，1个LOCK_open锁分散到32个m_lock的mutex上，大大降低了锁的争用。

/** Acquire lock on table cache instance. */
  void lock() { mysql_mutex_lock(&m_lock); }
  /** Release lock on table cache instance. */
  void unlock() { mysql_mutex_unlock(&m_lock); }

解决问题

我们生产环境同时采取下面优化措施，问题得以解决：
1、 读写分离，增加read节点，分散master库的压力；
2、 调整table_open_cache_instances=16；
3、 调整table_open_cache=6000；

总结

当出现Opening tables等待问题时，
1、建议找出打开表频繁的SQL语句，优化该SQL，降低单句SQL查询表的数量或大幅降低该SQL的并发访问频率。

2、设置合适的table cache，同时增大table_open_cache_instances和 table_open_cache参数的值。

Das obige ist der detaillierte Inhalt vonLösung des Problems, dass sich der MySQL-Thread beim Öffnen von Tabellen befindet (mit Beispielen). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!