Solving the problem of MySQL thread in Opening tables (with examples)

The content of this article is about solving the problem of MySQL thread in Opening tables (with examples). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Problem description

There is a MySQL5.6.21 server recently. After the application is released, the concurrent thread Threads_running increases rapidly, reaching about 2000, and a large number of threads are waiting for Opening. Tables, closing tables status, application-side related logical access timeout.

[Analysis process]

1. After the application is released at 16:10, Opened_tables continues to increase, as shown in the following figure:

View the fault at that time In the pt-stalk log file captured during the period, at the time point 2019-01-18 16:29:37, the value of Open_tables is 3430, and the configuration value of table_open_cache is 2000.
When the Open_tables value is greater than the table_open_cache value, every time a new session opens the table, some of the tables cannot hit the table cache and have to reopen the table. The phenomenon reflected in this is that there are a large number of threads in the opening tables state.

2. The tables under this instance, plus the system database, total 851, which is far less than the 2000 of table_open_cache. Why does Open_tables reach 3430?
Explanations can be obtained from the official documents,
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.

At that time, the number of concurrent threads reached 1980, assuming that 30% of these concurrent connections were access 2 tables, and the others are all single tables, then the cache size will reach (1980*30%*2 1980*70%*1) = 2574

3. QPS is relatively stable before and after release. From the outside Judging from the requests, there is no sudden increase in connection requests, but after the release, threads_running rose to a high of nearly 2,000 and continues. The guess is that a certain published SQL statement triggered the problem.

4. Check the processlist information captured at that time. There is a statement that SQL concurrent access is very high. 8 physical tables were queried. The SQL sample is as follows:

<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. In the test environment Create the same 8 tables, clear the table cache, and compare before and after executing SQL in a single session. The value of Open_tables will increase by 8. If there is high concurrency, the value of Open_tables will increase significantly.

Reproduction of the problem

Simulate the scenario of high concurrent access in the test environment, execute the above SQL statement concurrently with 1000 threads, and reproduce the production A similar phenomenon occurs in the environment. Open_tables quickly reaches 3800, and a large number of processes are in the Opening tables and closing tables state.

Optimization plan

1. After locating the cause of the problem, we communicated with our development colleagues and suggested optimizing the SQL to reduce the number of single-sentence SQL query tables or significantly reduce the SQL concurrent access frequency.
However, before the development colleagues could optimize it, the fault occurred again in the production environment. At that time, when the DBA was troubleshooting, the table_open_cache was increased from 2000 to 4000. The CPU usage increased, but the effect was not obvious. The problem of waiting for Opening tables still existed.

2. After analyzing the pstack information captured during the failure and aggregating it with pt-pmp, we saw that a large number of threads were waiting for mutex resources when 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

At this time, the mutex conflict in table_cache_manager was very serious.
Since the default value of the table_open_cache_instances parameter under MySQL 5.6.21 is 1, I think increasing the table_open_cache_instances parameter and adding table cache partitions should alleviate contention.

3. In the test environment, we adjusted the two parameters table_open_cache_instances=32, table_open_cache=6000, and also executed the problematic SQL with 1000 threads concurrently. This time, the threads waiting for Opening tables and closing tables disappeared, and MySQL QPS also increased from 12,000 to 55,000.
Compared with the same situation, only adjusting table_open_cache=6000, the number of processes waiting for Opening tables dropped from 861 to 203. The problem has been alleviated. More than 600 processes have changed from waiting for Opening tables to running status, and QPS has increased to 40,000. around, but not a cure.

Source code analysis

I checked the code for the relevant logic of table_open_cache:
1. Table_cache::add_used_table function is as follows. When a new connection opens the table, When the table does not exist in the cache, open the table and add it to the used tables list:

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. Each time add_used_table will call the Table_cache::free_unused_tables_if_necessary function, when m_table_count > table_cache_size_per_instance &&m_unused_tables is satisfied, remove_table is executed and the m_unused_tables list is cleared. excess cache. Among them, table_cache_size_per_instance= table_cache_size / table_cache_instances. The default configuration of MySQL5.6 is 2000/1=2000. When the m_table_count value is greater than 2000 and m_unused_tables is not empty, remove_table is executed to clear the table cache in m_unused_tables. In this way, m_table_count is the value of Open_tables and will normally remain around 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. Increase table_cache_instances to 32. When Open_tables exceeds (2000/32=62), the condition will be met, accelerating the cleaning of m_unused_tables in the above logic, further reducing the number in the table cache, which will lead to Table_open_cache_overflows is raised.

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

The above is the detailed content of Solving the problem of MySQL thread in Opening tables (with examples). For more information, please follow other related articles on the PHP Chinese website!