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

Http://www.jb51.net/article/51828.htm

1. THREAD_CACHE

In order to improve the performance of the client request to create a connection, MySQL provides a connection pool, that is, Thread_Cache pool, which places idle connection threads in the connection pool instead of destroying them immediately. The advantage is that when there is a new request, mysql will not immediately create a connection thread, but first go to Thread_Cache to find free connection thread, if there is, then directly use, do not exist to create a new connection thread.

There are several important parameters of Thread_Cache in MySQL, which are briefly introduced as follows:

Thread_cache_size

The maximum number of connection threads stored in Thread_Cache. In the application of short connection, the effect of Thread_Cache is very obvious, because the connection and creation of database in the application is very frequent, if you do not use Thread_Cache, then the consumption of resources is very considerable! Although the improvement in long connections is not as obvious as that in short connections, the benefits are obvious. However, it is generally believed that the determination that the bigger the bigger, the better, but the waste of resources has something to do with physical memory, as follows:

The copy code is as follows:

1G-> 8

2G-> 16

3G-> 32

> 3G-> 64

If there are many short connections, you can increase them appropriately.

Thread_stack

The memory that mysql allocates to each connection when it is created. It is generally believed that this value can be applied to most scenarios by default, and do not touch it unless necessary.

Thread_handing

Use Thread_Cache to handle connections, new features added in 5.1.19. There are two values available [no-threads | one-thread-per-connection] literally, you should guess eight or nine points, hehe, no-threads server uses one thread, one-thread-per-connection server uses one thread for each client request. It is mentioned in the original manual that no-threads is downgraded and tried out by Linux.

The copy code is as follows:

Mysql > show variables like 'thread%'

+-+ +

| | Variable_name | Value |

+-+ +

| | thread_cache_size | 32 | |

| | thread_handling | one-thread-per-connection |

| | thread_stack | 196608 | |

+-+ +

3 rows in set (0.01sec)

Mysql > show status like'% connections%'

+-+

| | Variable_name | Value |

+-+

| | Connections | 199156 | |

| | Max_used_connections | 31 | |

+-+

2 rows in set (0.00 sec)

Mysql > show status like'% thread%'

+-+

| | Variable_name | Value |

+-+

| | Delayed_insert_threads | 0 | |

| | Slow_launch_threads | 0 | |

| | Threads_cached | 3 | |

| | Threads_connected | 6 | |

| | Threads_created | 8689 | |

| | Threads_running | 5 | |

+-+

6 rows in set (0.00 sec)

Through the above three commands, you can see that the server can store up to 32 connection threads in the thread_cache pool, using one thread for each client ball. Allocate 192 k of memory space for each connected thread.

The server has a total of 199156 connections, and the maximum number of concurrent connections is 31. Currently, there are 3 connections, 6 connections and 5 active connections in the thread_cashe pool. A total of 8689 connections have been created. Obviously, there are mainly short connections here. The hit rate of thread_cache can be calculated as follows:

The copy code is as follows:

Thread_Cache_Hit= (Connections-Thread_created) / Connections*100%

The Thread_cache hit rate of the current server is about 95.6%, which I am quite satisfied with. But you can see that it is more reasonable to change thread_cache_size to 16 or 8.

2. TABLE_CACHE (5.1.3 and later, also known as TABLE_OPEN_CACHE)

Because MySQL is a multithreaded mechanism, in order to improve performance, each thread opens the file descriptor of the table it needs, rather than sharing what it has already opened. Of course, there are different ways to deal with different storage engines.

In the myisam table engine, the descriptor (descriptor) of the data file is not shared, but the descriptor of the index file is shared by all threads. Innodb is related to the use of tablespace types. If it is a shared tablespace, then the actual data file will occupy fewer data file descriptors than independent tablespaces.

Personally, I feel a bit like fopen in php opening a connection. After manipulating the data, it doesn't close immediately, but caches it. Waiting for the next request to connect to this file, I don't have to reopen the file. I don't know if it's right.

There is a description in the manual about opening the table:

The copy code is as follows:

A MyISAM table is opened for each concurrent access. This means the table needs to be opened twice if two threads access the same table or if a thread accesses the table twice in the same query (for example, by joining the table to itself). Each concurrent open requires an entry in the table cache. The first open of any MyISAM table takes two file descriptors: one for the data file and one for the index file. Each additional use of the table takes only one file descriptor for the data file. The index file descriptor is shared among all threads.

If you are opening a table with the HANDLER tbl_name OPEN statement, a table will be assigned to the thread. The table is not shared by other threads and is closed only after the thread calls HANDLER tbl_name CLOSE or the thread terminates. After the table is closed, it is pulled back into the table cache (if the cache is not satisfied).

The recommended size given in the mysql manual is: table_cache=max_connections*n

N represents the maximum number of tables in the query statement, and some additional file descriptors need to be reserved for temporary tables and files.

There are a lot of questions about this data, as long as table_cache is enough, check the Opened_ tables value, if this value is large or growing rapidly, then you have to consider increasing table_cache.

Unused tables are closed and removed from the table cache under the following conditions:

When the cache is full and a thread tries to open a table that is not in the cache.

When the cache contains more than table_cache entries, and the tables in the cache are no longer used by any thread.

When the table refresh operation occurs. Occurs when a FLUSH TABLES statement or a mysqladmin flush-tables or mysqladmin refresh command is executed.

When the table cache is full, the server uses the following procedure to find a cache entry to use:

Currently unused tables are released in the order of least recent use.

If the cache is full and there are no tables to release, but a new table needs to be opened, the cache must be temporarily expanded.

If the cache is in a temporarily enlarged state and a table changes from being in use to not in use, it is closed and released from the cache.

Several status values about table_cache:

1. Table_cache: the number of tables opened by all threads. Increasing this value increases the number of file descriptors required by mysqld. The default value is 64.

2. Open_tables: the number of tables currently open.

3. Opened_tables: Number of table cache misses. If the opened_tables is large, the table_cache value may be too small.

4. Open_table_definitions: The number of cached .frm files. This variable was added in MySQL 5.1.3.

5. Opened_table_definitions: The number of .frm files that have been cached. This variable was added in MySQL 5.1.24.

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.