MySQL 스레드가 테이블 열기에 있는 문제 해결(예제 포함)

이 문서의 내용은 테이블 열기(예제 포함)에서 MySQL 스레드의 문제를 해결하는 내용입니다. 필요한 친구가 참고할 수 있기를 바랍니다.

문제 설명

최근 MySQL5.6.21 서버가 출시된 후 동시 스레드 Threads_running이 급격히 증가하여 약 200개에 달했습니다. 2,000개, 많은 수의 스레드가 테이블 열기 및 테이블 닫기를 기다리고 있으며 애플리케이션 측의 관련 논리 액세스 시간이 초과됩니다.

【분석 과정】

1. 16시 10분에 애플리케이션이 출시된 후 Opened_tables는 다음 그림과 같이 계속 증가합니다.
#🎜 🎜##🎜🎜 #실패 시 캡쳐된 pt-stalk 로그 파일을 보면 2019-01-18 16:29:37 시점에서 Open_tables 값은 3430, 구성 값은 table_open_cache는 2000이었습니다.

Open_tables 값이 table_open_cache 값보다 크면 새 세션에서 테이블을 열 때마다 일부 테이블 캐시에 도달할 수 없어 테이블을 다시 열어야 합니다. 이에 반영된 현상은 테이블 오픈 상태에서 스레드가 많이 발생하는 현상입니다.

2. 이 예의 테이블과 시스템 데이터베이스는 총 851개입니다. 이는 table_open_cache의 2000개보다 훨씬 적습니다.

에서 설명할 수 있습니다.

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.

당시, 동시 스레드가 1980에 도달하고 연결의 30%가 2개의 테이블에 액세스하고 나머지는 단일 테이블이라고 가정하면 캐시 크기는 (1980*30%*2+1980*70%*1)=2574#🎜에 도달합니다. 🎜#

3 QPS는 출시 전후로 비교적 안정적이었지만, 출시 이후에는 thread_running이 2,000개에 가까운 최고치를 기록했습니다. 계속되는. 게시된 특정 SQL 문이 문제를 촉발한 것으로 추측됩니다.

4. 당시 캡처한 프로세스 목록 정보를 확인하면 8개의 물리적 테이블을 쿼리한다는 말이 있습니다.

#🎜 🎜#

<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. 테스트 환경에서 동일한 8개의 테이블을 생성하고 테이블 캐시를 삭제한 후 단일 세션에서 SQL 실행 전후를 비교하면 Open_tables의 값이 8만큼 증가합니다. 동시성이 높을 경우. , Open_tables의 값이 크게 증가합니다.

문제 재현

테스트 환경에서 높은 동시 접속 시나리오를 시뮬레이션하고, 그 이상의 1000개 스레드를 동시에 실행합니다. SQL 문을 재현합니다. 프로덕션 환경에서도 비슷한 현상이 발생하여 Open_tables가 빠르게 3800에 도달하고 많은 수의 프로세스가 열린 테이블 및 닫는 테이블 상태에 있습니다.

최적화 계획

1. 문제의 원인을 파악한 후 개발 동료들과 소통하여 SQL을 최적화하고 용량을 줄이는 것을 제안했습니다. 단일 문장 SQL 쿼리 테이블 이 숫자는 SQL의 동시 액세스 빈도를 크게 줄일 수 있습니다. 그런데 개발 동료들이 최적화를 하기도 전에 제작 환경에서 또 오류가 발생했습니다. 당시 DBA가 트러블슈팅을 하던 중 table_open_cache가 2000에서 4000으로 늘어났습니다. CPU 사용량은 늘어났지만, 테이블 열기를 기다리는 문제는 여전히 존재했습니다.

2 실패 중에 캡처된 pstack 정보를 분석하고 pt-pmp로 집계한 결과 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

#🎜 🎜#지금 table_cache_manager에서 뮤텍스 충돌이 매우 심각합니다.
MySQL5.6.21에서는 table_open_cache_instances 매개변수의 기본값이 1이므로 table_open_cache_instances 매개변수를 늘리고 테이블 캐시 파티션을 추가하면 경합이 완화될 것이라고 생각했습니다.

3 테스트 환경에서는 두 개의 매개변수 table_open_cache_instances=32, table_open_cache=6000을 조정했고, 이번에는 테이블 열기 및 닫기를 기다리는 스레드가 1000개로 동시에 실행되었습니다. 테이블이 사라졌습니다. MySQL의 QPS도 12,000에서 55,000으로 증가했습니다.

같은 상황에 비해 table_open_cache=6000만 조정하면 테이블 열기를 기다리는 프로세스 수가 861에서 203으로 줄었습니다. 600개 이상의 프로세스가 테이블 열기 대기에서 실행 상태로 변경되었습니다. , QPS는 약 40,000으로 증가했지만 치료할 수 없습니다.

소스 코드 분석

코드에서 table_open_cache 관련 로직을 확인하세요.

1 Table_cache::add_used_table 함수는 다음과 같습니다. 다음과 같이 새 연결로 열린 테이블이 테이블 캐시에 없으면 열린 테이블이 사용된 테이블 목록에 추가됩니다:

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. add_used_table이 Table_cache를 호출할 때마다: free_unused_tables_if_necessary 함수, m_table_count > table_cache_size_per_instance가 만족될 때 &&m_unused_tables일 때, Remove_table을 실행하여 m_unused_tables 목록의 중복 캐시를 삭제합니다. 그 중 table_cache_size_per_instance= table_cache_size / table_cache_instances입니다. MySQL5.6의 기본 구성은 2000/1=2000입니다. m_table_count 값이 2000보다 크고 m_unused_tables가 비어 있지 않으면 Remove_table이 실행되어 m_unused_tables의 테이블 캐시를 삭제합니다. 이런 방식으로 m_table_count는 Open_tables의 값이며 일반적으로 약 2,000으로 유지됩니다.

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. table_cache_instances를 32로 늘립니다. Open_tables가 (2000/32=62)을 초과하면 조건이 충족되고 위 논리에서 m_unused_tables 정리가 가속화되어 테이블 캐시로 인해 Table_open_cache_overflows가 증가합니다.

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参数的值。

위 내용은 MySQL 스레드가 테이블 열기에 있는 문제 해결(예제 포함)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!