Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to solve the problem that MySQL thread is in Opening tables, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

The content of this article is about MySQL thread in Opening tables problem solving (with examples), there is a certain reference value, friends in need can refer to, hope to help you.

Problem description

Recently, there is a MySQL5.6.21 server, after the release of the application, the concurrent thread Threads_running increased rapidly to about 2000, a large number of threads are waiting for Opening tables, closing tables state, application-side related logic access timed out.

[analysis process]

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

Looking at the pt-stalk log files crawled during the failure at that time, the value of the time point 2019-01-18 16-month-old-29-7-month-old OpenSecretLes is 3430, while 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 fail to hit the table cache and have to reopen the table. This reflects the phenomenon that there are a large number of threads in the opening tables state.

2. The tables under this example, coupled with a total of 851 tables under the system database, are far less than 2000 of table_open_cache, so why does the Open_tables reach 3430?

It can be explained 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 200concurrent running connections, specify a table cache size of at least 200N, 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 accessed 2 tables and the others were single tables, then the cache size would reach (1980-30%) = 2574.

3. QPS is relatively stable before and after the release, and there is no sudden increase in connection requests from external requests, but the threads_running has risen to a high of nearly 2000 after the release. The guess is that a published SQL statement triggered the problem.

4. Check the processlist information crawled at that time. There is a sentence with high concurrent access to SQL. Eight physical tables are queried. The SQL sample is as follows:

Select id,name,email from table1 left join table2

Union all

Select id,name,email from table3 left join table4

Union all

Select id,name,email from table5 left join table6

Union all

Select id,name,email from table7 left join table8

Where id in ('aaa')

5. Create the same 8 tables in the test environment, clear the table cache, and the value of Open_tables will increase by 8 before and after a single session executes SQL. In the case of high concurrency, the value of Open_tables will increase significantly.

Problem recurrence

The scenario of high concurrent access is simulated in the test environment, and 1000 threads execute the above SQL statement at the same time, which repeats the similar phenomenon in the production environment. Open_tables quickly reaches 3800, and a large number of processes are in the state of Opening tables and closing tables.

Optimization scheme

1. After locating the cause of the problem, we communicated with our developer colleagues and suggested to optimize the SQL, reduce the number of single-sentence SQL query tables or significantly reduce the concurrent access frequency of the SQL.

However, the development colleagues have not yet come and optimized, the failure in the production environment has appeared again. At that time, when DBA was troubleshooting, it increased the CPU utilization of table_open_cache from 2000 to 4000, but the effect was not obvious, and the problem of waiting for Opening tables still existed.

2. Analyze the pstack information captured during the failure. After aggregating with pt-pmp, you can see that a large number of threads are waiting for mutex resources during 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 is very serious.

Since the default value of the table_open_cache_instances parameter under MySQL5.6.21 is 1, the contention can be alleviated by increasing the table_open_cache_instances parameter and increasing the table cache partition.

3. In the test environment, we adjusted two parameters table_open_cache_instances=32,table_open_cache=6000, and also 1000 threads executed the problem SQL. This time, the threads waiting for Opening tables and closing tables disappeared, and the QPS of MySQL rose from 12000 to 55000.

Compared with the same situation, only adjusting table_open_cache=6000, the number of processes waiting for Opening tables decreased from 861 to 203.The problem has been alleviated. More than 600 processes have changed from waiting for Opening tables to running state, and QPS has risen to about 40000, but there is no cure.

Source code analysis

Check the logic of the code about table_open_cache:

1. The Table_cache::add_used_table function is as follows. When the table opened by the new connection does not exist in table cache, open the table and join 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); free_unused_tables_if_necessary (thd); return false;}

2. Each time add_used_table calls 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 to clear the redundant cache in the m_unused_tables list. The default configuration of table_cache_size_per_instance= table_cache_size / table_cache_instances,MySQL5.6 is 2000Universe 2000. When the m_table_count value is greater than 2000 and the m_unused_tables is not empty, remove_table is executed to empty the table cache in the m_unused_tables. In this way, the value of m_table_count is Open_tables, which normally stays 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= 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 the table_cache_instances to 32, when the Open_tables exceeds (2000 Open_tables 32: 62), the condition will be satisfied, and the cleaning of m_unused_tables in the above logic will be accelerated, so that the number of table cache will be further reduced, which will lead to the increase of Table_open_cache_overflows.

4. When the table_open_cache_instances increases from 1 to 32, one LOCK_open lock is distributed to the mutex of 32 m_lock, which greatly reduces the lock contention.

/ * 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);}

Solve the problem

By taking the following optimization measures in our production environment, the problem can be solved:

1. Separate read and write, increase read nodes, and disperse the pressure of master library.

2. Adjust table_open_cache_instances=16

3. Adjust table_open_cache=6000

Summary

When there is an Opening tables waiting problem

1. It is recommended to find out the SQL statements that open the table frequently, optimize the SQL, reduce the number of single-sentence SQL query tables or greatly reduce the concurrent access frequency of the SQL.

2. Set appropriate table cache and increase the values of table_open_cache_instances and table_open_cache parameters at the same time.

Thank you for reading this article carefully. I hope it will be helpful for everyone to share how to solve the problem of MySQL threads in Opening tables. At the same time, I also hope you can support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.