MySQL Open file descriptor limit

Can't open file: '.\test\mytable.frm' (errno: 24)
[root@localhost ~]# perror 24
OS error code 24: Too many open files

This is it. MySQL I don't have enough file descriptions . Let's start with the solution , Let's talk about the reasons behind it .

1. How to solve

First step : Set up OS Parameters ( If you have permission ):

file /etc/security/limits.conf New ones, such as downlink :

mysql soft nofile 65535
mysql hard nofile 65535

Configuration above , yes OS Limit the file descriptors that each user can open (hard soft See the difference man ulimit), Add the above two lines , Express mysql Users can open 65535 File descriptors

( have access to lsof -u mysql|wc -l View how many file descriptors are currently open )

[root@localhost ~]# lsof -u mysql|wc -l
63

The second step : modify MySQL Parameters :
stay MySQL The configuration file my.cnf Add the following line in

open_files_limit = 65535
innodb_open_files=65535
innodb_open_files:
This variable is relevant only if you use multiple InnoDB tablespaces. It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is . The default value is if innodb_file_per_table is not enabled, and the higher of and table_open_cache otherwise.
The file descriptors used for .ibd files are for InnoDB tables only. They are independent of those specified by the --open-files-limit server option, and do not affect the operation of the table cache.

open_files_limit :
 Change to mysqld Number of available file descriptors for . You should try to increase the value of this option , If mysqld It gives you too many open files .mysqld Use option values to keep and setrlimit() The descriptor of . In the internal , The maximum value of this option is the largest unsigned integer value , But the actual maximum is platform dependent . If the number of file descriptors requested cannot be allocated ,mysqld Write a warning to the error log .
mysqld Maybe try to allocate more descriptors for the request ( If available ), Use max_connections and table_open_cache To estimate whether more descriptors will be needed .
stay Unix On , The value set cannot be less than ulimit-n.

Then restart your MySQL The general problem is solved .

2. The problem behind it

The above method can generally solve the problem . But in practice , stay my.cnf Parameters set in open_files_limit Value is invalid , namely MySQL After starting open_files_limit Always use OS The file descriptor of .( edition MySQL5.1.45 RHEL5.4)

mysql> show global variables like "%open_files_limit%";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | |
+------------------+-------+
row in set (0.02 sec)
 

that my.cnf Parameters open_files_limit Is it really useless ? It's going to be a long one 、 It's very painful to study this problem , If you don't have a lot of time , I don't recommend going on .

3. How to set up in the source code open_files_limit
 
3.1 Experimental verification

Configuration in profile :open_files_limit = 10000;$ulimit -n 20000; Start database , Observe :


mysql> show global variables like "%open_files_limit%";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | |
+------------------+-------+
row in set (0.03 sec)
/etc/security/limits.conf
* soft nproc
* hard nproc
* soft nofile
* hard nofile

See the parameters open_files_limit It didn't work ( It's been tested many times ).limit -n 20000 It doesn't work , Direct use limits.conf value

Connection optimization

Connection optimization mainly refers to the parameter adjustment involved in the process of the client connecting to the database and the database opening the data table and index in response to the client's request . The original text can refer to here perhaps here .( Link to the original text http://ddbiz.com/?p=950)
Although different mysql The compilation and linking mode of the distribution version will also affect the link request of the client , But because most of my systems are installed directly mysql Distribution package for , And very few changes , So about manual compilation mysql In order to achieve the purpose of optimization , We can't talk about , Maybe there will be a chance to get involved in it in the future .

according to MySQL How to use memory Chinese saying , The following parameters affect every request of the client :

  1. open-files-limit

    Command line arguments : –open-files-limit=#
    ini/cnf Parameters : open-files-limit
    mysql Variable : open_files_limit
    Global variables , Not dynamically adjustable , Value range 0 To 65535.
    open_files_limit finger mysql Number of file handles that can be opened . When the value is insufficient , May trigger Too many open files error . How many file handles do you need , It also needs to be based on max_connections and table_open_cache To calculate .

  2. An interesting phenomenon is , In my 64bit linux in , –open-files-limit perhaps –open_files_limit You can set more than 64k, Such as :

    open-files-limit May be limited by the operating system , such as linux in ,/proc/sys/fs/file-max, This limits the maximum number of file handles that the system can open . image oracle stay linux Installation and operation requirements of , The minimum requirement is to exceed 64k. It can be modified by /etc/sysctl.conf, Add or modify fs.file-max=# To increase the maximum open value of the system , Don't forget to revise it , use sysctl -p To enable the new value ( The above operations are centos/rhel).
    stay linux in , There is also a parameter that may limit the maximum number of open files on the system , Namely /etc/security/limits.conf

    How to modify its value , Please refer to the system documentation

    Affected by the following parameters : Limited by the system
    The following parameters will be affected : max_connections table_open_cache
    Adjust the trigger conditions : When the system appears Too many open files You need to adjust this parameter .

  3. thread_stack

    Command line arguments : –thread_stack=#
    ini/cnf Parameters : thread_stack
    mysql Variable : thread_stack
    Global variables , Not dynamically adjustable .
    32bit The default value in the system is 192k, 64bit The default value in the system is 256k. Let's start with thread_stack It's because of what he's going to say next max_connections There are key factors .

    thread_stack Corresponding to... In the operating system level stack size,windows Of the default thread in stack size by 1M, linux It changes depending on the version , Generally in 8m perhaps 10m. In a few of my Centos 5.x/6.x in , default stack size All are 10M( This is better than windows Higher than 10 times )

    ulimit -s
    10240

    stack size stay 32bit Of OS Is a very important parameter , Reduce the cost of one thread stack size You can increase the number of threads , For instance from 10m Reduced to 64k.                                                                             But in 64bit Of Linux( Kernel version >= 2.6.x) in , If allowed /proc/sys/vm/overcommit_memory,stack size Maybe it's not that important .

    Affected by the following parameters : nothing
    The following parameters will be affected : max_connections
    Adjust the trigger conditions : max_connections It has reached the maximum allowed by the current system .

  4. max_connections

    Command line arguments : –max_connections perhaps –max-connections
    ini/cnf Definition : max_connections
    mysql Variable : max_connections
    Global variables , It can be adjusted dynamically

    MySQL The database allows Number of concurrent connections .
    For a person with a lot of visitors (pv High value ) For the website , Sometimes it can happen : Too many connections Error of . Consider increasing this value . about MySQL Come on , The maximum number of concurrent connections that can be supported , It depends on many factors , Include :

    1. Operating system thread model 、 Operating system version ( See  thread_size)
    2. The amount of memory available
    3. Memory usage per connection / The workload ( See  thread_size)
    4. Expected server response time

    If memory allows ,32bit windows Maximum support 2000 About concurrent requests ( Because the maximum memory supported by a single process is 2G, The default thread requires a resource of 1MB),64bit windows The number of threads that can be supported can also be calculated according to the memory .( About windows Thread estimation available in , You can refer to Mark Russinovich The article Pushing the Limits of Windows: Processes and Threads, Or refer to a Microsoft article sketch ( Process address space )).
    and Linux The factors in may be more complex , however stack_size It's still like windows In the same , Is an important factor that restricts the number of threads , The maximum number of threads is Liunx There are also default values under ,cat /proc/sys/kernel/threads-max, When this value is not adjusted ,MySQL Of max_connections It should be much smaller than it .

    in application , The number of concurrencies that can be supported will be far less than the theoretical value , Because each thread can't be disconnected just after an empty connection . When a thread is working CPU/ Memory loss , It will reduce the available resource allocation of the whole system . about MySQL Come on , It provides an adjustable stack size Parameters of : thread_stack.
    mysql Of max_connections * thread_stack Should be less than available memory ; according to mysql Official documents of (doc5.5),linux( perhaps solaris) Next , Can support 500 To 1000 Two concurrent connections , If the work per connection is small , And if the server memory is large , Then we can support 10k Link to . stay windows Next , There is one (open tables*2+open connection) < 2048 The limitation of . therefore :

    Affected by the following parameters : thread_stack    table_open_cache     open_file_limit
    The following parameters will be affected : nothing
    Adjust the trigger conditions : When threads_connected(show status like ‘threads_connected’) near max_connections when , Action should be taken to increase concurrency .

  5. thread_cache_size

    Command line arguments : –thread_cache_size
    ini/cnf Definition : thread_cache_size
    mysql Variable : thread_cache_size
    Global variables , It can be adjusted dynamically , The default value is 0, Maximum 16k

    mysql Use this parameter to limit... In the database service , Cache how many threads are used by the client . If the server has hundreds of new connections per second , So this value should be higher . Pass the evaluation connections and threads_created Determine whether it is necessary to add thread_cache_size.
    mysql> show status like ‘%connections%’; Output
    Connections Try to connect request ( Including requests that failed to establish a connection )
    Max_used_connections Maximum number of concurrent connections

    mysql> show variables like "%connections%";
    +----------------------+-------+
    | Variable_name | Value |
    +----------------------+-------+
    | max_connections | |
    | max_user_connections | |
    +----------------------+-------+
    rows in set (0.02 sec)

    mysql> show status like ‘threads_c%’; Output

    threads_cached Current number of cache threads
    threads_connected Current connections
    thread_created Number of threads created

    mysql> show status like "%threads_c%";
    +-------------------+-------+
    | Variable_name | Value |
    +-------------------+-------+
    | Threads_cached | |
    | Threads_connected | |
    | Threads_created | |
    +-------------------+-------+
    rows in set (0.03 sec)

    When connection cache utilization ( thread cache hit =(connections – threads_created)/connections*100%) When the value of is low , indicate mysql More threads need to be created ( Not enough thread cache ) To accept client requests .

    Affected by the following parameters : nothing
    The following parameters will be affected : nothing
    Adjust the trigger conditions : When thread cache hit When it's low , Action should be taken to improve thread_cache_size This value .

  6. table_open_cache/table_cache

    Command line arguments : –table-open-cache
    ini/cnf Definition : table_open_cache
    mysql Variable : table_open_cache
    Global variables , It can be adjusted dynamically , The default value is 400, Maximum 512k

    mysql Open the descriptor of the table , Cached in table_open_cache in ,table_open_cache >= max_connections * 2, This is because when some tables are open , Two filers are required , Such as myisam surface , And then there is index、 Temporary table, etc . Self linking query statements , One more file character of the table will be opened .

    A targeted setting is , Find all the most complex queries related to the database ( Including self Links ,left/right/outer join, as well as group And so on ) See how many tables these links will open , Set this value to N, be

    table_open_cache > max_connections * N

    eg:
    mysql> show variables like "%table_open%"; Table file descriptor ===》 Thread descriptor ===》 Always open file descriptors
    +----------------------------+-------+
    | Variable_name | Value |
    +----------------------------+-------+
    | table_open_cache | |
    | table_open_cache_instances | |
    +----------------------------+-------+
    rows in set (0.02 sec)

    Affected by the following parameters : max_connections    open_file_limit
    The following parameters will be affected : max_connections
    Adjust the trigger conditions : When opened_tables(show status like ‘opened_tables’) When it's worth a lot , Action should be taken to improve table_open_cache This value .

  7. net_buffer_length

    Command line arguments : –net_buffer_length
    ini/cnf Definition : net_buffer_length
    mysql Variable : thread_cache_size
    Global variables , It can be adjusted dynamically , The default value is 16k, Range 1k To 1m.

    Client connection buffer and result buffer , It can be adjusted dynamically ( Automatic adjustment , signify set net_buffer_length=xxx It's invalid ) To maximum max_allowed_packet Size . Every SQL After statement end , This value will return to the initial value . When there is not enough memory – It's rare , After all, memory is so cheap now – Or when the concurrent connection is large , The initial value can be reduced appropriately , such as 1k.

    Affected by the following parameters : max_allowed_packet
    The following parameters will be affected : nothing
    Adjust the trigger conditions : If you want to load / Import / When exporting large amounts of data ; When the query results contain large data fields , Such as TEXT,BLOB etc.

  8. max_allowed_packet

    Command line arguments : –max_allowed_packet
    ini/cnf Definition : max_allowed_packet
    mysql Variable : max_allowed_packet
    Global variables , It can be adjusted dynamically , The default value is 1m, Range 1k To 1g.

    Client side and server side max_allowed_packet Need to be consistent , Or on the client side max_allowed_packet It's bigger than the server max_allowed_packet.

    Affected by the following parameters : nothing
    The following parameters will be affected : nothing
    Adjust the trigger conditions : If you want to load / Import / When exporting large amounts of data ; When the query results contain large data fields , Such as TEXT,BLOB etc.

    Q:max_allowed_packet and net_buffer_length It will affect load data infile Do you ?
    A:No

  9. wait_timeout

    Command line arguments : –wait_timeout
    ini/cnf Definition : wait_timeout
    mysql Variable : wait_timeout
    Global variables , It can be adjusted dynamically , The default value is 8 Hours , Range 1 Seconds to 31536000.
    wait_timeout This paper defines the most frequent idle time of a connected client without any query action .
    Be careful : There will be no impact on established connections .
    Can pass show processlist To see the status of the current database connection , Such as :

    Affected by the following parameters :
    The following parameters will be affected : max_connections
    Adjust the trigger conditions : Short link 、 High concurrency system applications .

 

《open/close table on mysql》

We know mysql It's a multithreaded database , Especially in innodb After the storage engine appeared , Yes mysql The business of , Concurrent , Lock support has been greatly improved . In the application scenario of high concurrent access , A large number of concurrent process query database on application side , The data tables in the database are stored as data files on the disk , stay unix,linux In the system call of , Is dependent on the file descriptor . Different os The restrictions on file descriptors are different ( Not Unix/linux The operating system has no concept of file descriptors , stay windows A handle to a composition ), If in linux in /etc/security/limits.conf Set their file descriptor limit in the configuration file .

In understanding mysql Before opening a table , Need to know something :

table cache: For different storage engines ,table cache It's different , about MyISAM surface , Every client thread opens any MyISAM Table data files need to open a file descriptor , But if it's an index file , The descriptor of the same index file can be shared by multiple threads ,table cache The main purpose of should be to cache file descriptors , There is no need to reopen when there is a new request , You don't have to turn it off immediately at the end of use .

about InnoDB For the storage engine , The way to open a table is the same as myisam It's different :

Unlike MyISAM Innodb does not have to keep open file descriptor when table is open – open table is purely logical state and appropriate .ibd file may be open or closed,InnoDB uses a single, global file descriptor for each .ibd file.

InnoDB has its own per-table cache, variously called a table definition cache or data dictionary, which you cannot configure.When InnoDB opens a table, it adds a corresponding object to the data dictionary. Each table can take up 4 KB or more of memory(although much less space is required in MySQL 5.1). Tables are not removed from the data dictionary when they are closed.

On the engine ,innodb hold table cache It's called a data dictionary , Table definitions are cached in the data dictionary (data dictionary), Use a... On the file descriptor global file descriptor To deal with each ibd file , If you are using a shared table space to store data , There are fewer open file descriptors , But if you're using exclusive table space (innodb_file_per_table=1) More open file descriptors .

After knowing the above knowledge , Take a look at the following parameters :

Table_cache: stay MySQL 5.1.3 In version table_open_cache, The default value is 64, The explanation of this parameter in the official document is :

The number of open tables for all threads. Increasing this value increases the number of file descriptors thatmysqld requires.

all threads Number of open tables , To increase this parameter, you need to change the mysqld Add... At startup File descriptor ;

The first question is :mysql How to open and close tables

It's clearly described in the official documents :

MySQL is multi-threaded, so there may be many clients issuing queries for a given table simultaneously. To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session

Mysql When accessing a table , Put it in cache in , If there are many tables in the database , Usually put it in cache in , Help to improve performance .

So in the constant opening of new tables ,cache It's slowly filling up (table_open_cache—-full), If the newly opened table is not in cache in ,mysql I'll take some unused table Remove :

(1)

Session 1

mysql> show variables like "%table_open%";
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_open_cache | |
| table_open_cache_instances | |
+----------------------------+-------+

root@test 10:56:22>set global table_open_cache=2;

Query OK, 0 rows affected (0.00 sec)

root@test 11:07:50>flush tables;

Query OK, 0 rows affected (0.00 sec)

root@test 11:08:58>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name                | Value |

+————————–+——-+

| Open_table_definitions     | 0     |

| Open_tables                    | 0     |

| Opened_table_definitions | 28   |

| Opened_tables                | 28   |

(2)

Sessioin 2:

root@test 10:56:03>select * from t1;

session 3:

root@test 10:56:03>select * from t2;

session 1:

root@test 11:09:17>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name                 | Value|

+————————–+——-+

| Open_table_definitions     |  2    |

| Open_tables                    |  2    |

| Opened_table_definitions | 30   |

| Opened_tables                | 30   |

(3)

Session 4:

root@test 10:52:22>select * from t1;

Session1:

root@test 11:11:08>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name                | Value |

+————————–+——-+

| Open_table_definitions     | 2     |

| Open_tables                    | 2     |

| Opened_table_definitions | 30    |

| Opened_tables                | 30    |

(4)

Session5:

root@test 10:52:39>select * from test_1;

Session1:

root@test 11:13:03>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name                | Value |

+————————–+——-+

| Open_table_definitions     | 3     |

| Open_tables                    | 2     |

| Opened_table_definitions | 31   |

| Opened_tables                | 31   |

We can see , First step :session1: Start cache In the implementation of flush tables after ,open_tables by 0,Open_table_definitions by 0;

The second step :Session2,3: Perform a query of two tables ,session1 Query in open_tables, Open_table_definitions by 2;

The third step :session 4: perform session2 Query for ,session1 Query in open_tables, Open_table_definitions No change , keep 2;

Step four :session5: Execute new query ,session Query in open_tables by 2,Open_table_definitions by 3;

From the experimental point of view, it meets the above situation .

without table Can be released ,cache Will be temporarily expanded as needed , When there is table Close or unused,cache Will be released :

First step :Session1: root@test 11:26:58>flush tables;

root@test 11:33:35>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name                | Value |

+————————–+——-+

| Open_table_definitions     | 0     |

| Open_tables                    | 0     |

| Opened_table_definitions | 38    |

| Opened_tables                | 39    |

The second step :

Session2:

root@test 11:10:43>HANDLER t1 open;

session3

root@test 11:10:46>HANDLER t2 open;

The third step :session1

root@test 11:33:41>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name                | Value |

+————————–+——-+

| Open_table_definitions     | 2     |

| Open_tables                    | 2     |

| Opened_table_definitions | 40    |

| Opened_tables                | 41    |

Step four :

session4:

root@test 11:10:49>select * from t3;

Step five :

Session1:

root@test 11:34:06>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name                | Value |

+————————–+——-+

| Open_table_definitions     | 3     |

| Open_tables                    | 2     |

| Opened_table_definitions | 41    |

| Opened_tables                | 42    |

Step six :

Session5: root@test 11:29:59>HANDLER test_1 open;

Step seven :

root@test 11:34:19>show global status like ‘open%table%’;

+————————–+——-+

| Variable_name                | Value |

+————————–+——-+

| Open_table_definitions     | 4     |

| Open_tables                    | 3     |

| Opened_table_definitions | 42    |

| Opened_tables                | 43    |

There are two parameters in the above :

Open_table_definitions

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

Open_tables

The number of tables that are open.

We see the use of HANDLER open When I open my watch , The table It won't be mysql Clear out cache, When cache When it's filled up , In the use of HANDLER open,cache Will be expanded ; Until you use handler close Close and release .

that table_open_cache What is the reasonable value , The bigger it is table_open_cache Parameter setting will bring about a linear improvement in performance ? When we have thousands of tables in our database , Queries involve complex multi table joins , And there are multiple at the same time connection Connected to the mysql To perform these query, Then you may run out of file descriptors soon cache(table_open_cache),mysql Use LRU Algorithm , Turn off the least recently used descriptors , Used to store new descriptors . But in finding the descriptor to close , The search time goes by cache As the number of caches in increases (O(n),n by cache Of items Number ), The open time of the file is equal to the close time of the file , This leads to a decline in performance .

In the official document for table_open_cache Parameter setting is limited to os Of File descriptor Above , Of course, there are also some corresponding memory overhead , Usually in settings table_open_cache Parameter time , We'll be at the peak of our business , Check open_Tables Value , If open_Tables The value of is equal to table_open_cache The values are equal , also opened_tales The value of is increasing , This is the time to be right table_open_cache Has increased the value of ;

set global table_open_cache=M;

root@test 01:25:00>show global status like ‘open%tables’;

+—————+———+

| Variable_name | Value   |

+—————+———+

| Open_tables     | 56       |

| Opened_tables | 2139150 |

The second question is :os File descriptor restrictions on this parameter ,

When we're adjusting table_open_cache When , Another parameter to consider is os File descriptor for , If table_open_cache The parameter setting is very big ,mysql It's possible to run out of file descriptors , Lead to mysql Deny other connection requests , At this time, we need to base on os To set the value of the parameter .

Adjust file descriptors :open_files_limit and open-files-limit These two parameters :

The official document tells us that we can go back to mysqld_safe Start up with open-files-limit Parameters ,mysqld_safe –open-files-limit=N, To change open_files_limit value ;

In the configuration file , We can also see that open_files_limit Parameters , But if you set this parameter , After restarting the host , The value of this parameter is still based on the file descriptor of the system , So in installation mysql When , stay /etc/security/limits.conf Good configuration mysql User's limit on the maximum number of open files , It is recommended to set it to the maximum :

Mysql soft nofiles 65535

Mysql hard nofiles 65535.

table_open_cache And max_connections And open_files_limit

 

In general :

table_open_cache = max_connections * N  ,N Represents the maximum number of open tables in a query .

table_definition_cache = It's big enough .

about open_files_limit, It represents the maximum number of files that can be opened at the same time , Here are two articles :http://www.orczhou.com/index.php/2010/10/mysql-open-file-limit/,http://blog.csdn.net/fbjwying2/article/details/6323897, It specifically says open_files_limit What factors are involved . I'm just going to talk about my understanding here .

open_files_limit The value of depends on three values :open_files_limit,max_connections*5,10+max_connections+table_cache_size*2. The real value is the maximum of these three values .

In one case, the maximum value obtained above exceeds ulimit -n When setting the value of , It is possible to pass through show variables See values that are not real . Because it runs on os In the end, the process will be os Influence . So the best thing is to set up ulimit -n And show variables like 'open_files_limit' Same as in .

show global Inside open_files Represents the number of files currently open at this time , If the open_files_limit Very close to , Then we need to improve open_files_limit Value .

show global Inside opened_tables Represents the number of tables that have been opened , If the table_open_cache Very close to , Then we need to improve table_open_cache Value . But don't just add , It also depends on the current situation open_tables Value .

Be careful :show global in open_files Represents the number of files currently open at this time ,opened_files Represents the number of files opened since the service was last started .

open_tables Represents the table currently open at this time ,opened_tables Represents the table opened since the service was last started .

opened_tables -----> table_open_cache.

open_files       -----> open_files_limit.

 
 

MYSQL performance optimization

mysql> show variables like "table%";
+-------------------------+--------+
| Variable_name             | Value |
+-------------------------+--------+
| table_definition_cache  | 256    | 
| table_lock_wait_timeout    | 50     | 
table_open_cache        | 1024   | 
| table_type                      | InnoDB | 
+-------------------------+--------+
4 rows in set (0.00 sec)

table_open_cache   :mysql Every time I open a watch , Will read in some data to table_open_cache  cache in

When mysql When no corresponding information is found in this cache , To read directly from the disk .

mysql> show status like "open%";
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| Open_files                   | 66       | 
| Open_streams               | 0        | 
| Open_table_definitions     | 135      | 
Open_tables               | 302      | 
| Opened_files                 | 86399846 | 
| Opened_table_definitions | 0        | 
| Opened_tables               | 0        | 
+--------------------------+----------+
7 rows in set (0.00 sec)

Open_tables       Number of tables currently open 302 .table_open_cache   1024   . So there's no need to adjust the cache right now .

mysql> show variables like "thread%";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    81208905
Current database: ZXX

+-------------------+---------------------------+
| Variable_name     | Value                             |
+-------------------+---------------------------+
| thread_cache_size | 64                               | 
| thread_handling        | one-thread-per-connection | 
| thread_stack            | 196608                         | 
+-------------------+---------------------------+
3 rows in set (0.04 sec)

Whenever a client connects to mysql database ,mysql The database will be created to serve it with a thread

But first of all mysql Go back thread_cache Find available threads in , A new thread will be created if it cannot be found

Current thread status :

mysql> show status like "thread%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 61    | 
| Threads_connected  | 3       | 
| Threads_created      | 2371 | 
| Threads_running      | 3     | 
+-------------------+-------+

 
 
Parameter description :

table_open_cache(5.1 The previous version was table_cache)
table_open_cache Specifies the size of the table cache . whenever MySQL When accessing a table , If there is still space in the table buffer , The watch is opened and put into it , This allows faster access to table content

By checking the state value of the peak time Open_tables and Opened_tables, You can decide whether you need to add table_open_cache Value . If you find that open_tables be equal to table_open_cache, also opened_tables It's growing , Then you need to add table_open_cache The value of the

5.max_connections
maximum connection
6.back_bog
back_log The value is in MySQL How many requests can be stored in the stack in a short period of time before you temporarily stop answering new requests . Only if you expect a lot of connections in a short time , You need to add it , let me put it another way , It's worth the coming TCP/IP The size of the listening queue for the connection .
7.thread_cache
mySQL In order to improve the performance of the client request to create a connection process , Provides a connection pool, that is Thread_Cache pool , Put idle connection threads in the connection pool , Instead of destroying it immediately . The good thing is , When there's a new request ,mysql Will not immediately create a connection thread , But go first Thread_Cache Find idle connection threads in the , If it exists, use , Create a new connection thread when it doesn't exist .
thread_cache_size
Thread_Cache The maximum number of connection threads stored in . In short connection applications Thread_Cache It's very effective
thread_stack
When every connection is created ,mysql The memory allocated to it . This value is generally considered to be applicable to most scenarios by default
thread_cache Hit rate calculation
show variables like 'thread%';show status like '%connections%';show status like '%thread%'

Formula for :thread_cache_hit=(connections-thread_create)/connections*100%

mysql Optimization of connection optimization (open-files-limit And table_open_cache) More articles about

  1. MySQL experiment Internal connection optimization order by+limit And adding indexes to improve again

    MySQL experiment Internal connection optimization order by+limit And adding indexes to improve again In the process of sub query optimization, two parameters limit When I came up with the idea of testing more in line with the actual production needs ORDER BY + LIMIT Ideas , Maybe we can ...

  2. mysql Optimization of connection optimization

    Posted by Money Talks on 2012/02/23 | Chapter one Chapter two Connection optimization part 3 Index optimization part 4 Query optimization part 5 Go to the actual combat Connection optimization Connection optimization mainly refers to client connection to the database and ...

  3. MySql database 3【 Optimize 4】 Optimization of connection settings

    1.wait_timeout / interactive_timeout   Connection timeout The number of seconds the server waits for activity before closing the connection .MySQL The maximum number of connections supported is limited , Because each connection will consume memory , So we hope ...

  4. MySQL Optimization II ( Connection optimization and cache optimization )

    body { font-family: Helvetica, arial, sans-serif; font-size: 14px; line-height: 1.6; padding-top: 10 ...

  5. 【mysql Optimize 5】 Left link and right link optimization

    Original address :8.2.1.8 Left Join and Right Join Optimization mysql Implement a A left join B Connection condition : 1, surface B Set to table dependent A and A the ...

  6. [MySQL Reference Manual] 8 Optimize

    8. Optimize 8. Optimize 8.1 Optimization Overview 8.2 Optimize SQL sentence 8.2.1 Optimize SELECT sentence 8.2.1.1 SELECT The speed of statements 8.2.1.2 WHERE Clause optimization 8.2.1.3 Range optimal ...

  7. mysql Configuration and performance optimization ( turn )

    MySQL The configuration file my.cnf Detailed explanation of Chinese , attach mysql Performance optimization method sharing ================================================================= ...

  8. MySql Study ( 6、 ... and ) —— Database optimization theory ( Two ) —— Query optimization technology

    The technologies involved in logical query optimization 1) Sub query optimization  2) View rewriting  3) Equivalent predicate rewriting  4) Condition simplification  5) External connection elimination  6) Nested join elimination  7) Connection elimination  8) Semantic optimization 9) Not SPJ Optimize One . Sub query optimization 1. ...

  9. 101 individual MySQL Tips for regulation and optimization

    MySQL Is a powerful open source database . With more and more database driven applications , People have been pushing MySQL To its limit . Here is 101 Regulation and optimization MySQL Installation skills . Some of the techniques are specific to the installation environment , But these thoughts ...

Random recommendation

  1. 05_ Hands and brains String.equals() Method implementation code

    Question: Please check out String.equals() Method implementation code , Pay attention to its implementation method . Answer: java Medium String.equals() Method implementation code : equals() Law is the root class Obj ...

  2. 【 Luogu p3368】 Templates - Tree array 2( data structure )

    subject : A sequence of numbers is known , You need to do two things :1. Add... To each number in an interval x:2. Find the sum of a number . solution : Tree array + Prefixes and optimizations . The difference between the number of each memory in the array and the number of the previous one , In this way, only two digits are needed to modify the interval , A single point of inquiry is to find the sum of prefixes ...

  3. winform(ListView And database connection )

    One .ListView: The list shows the data 1. View -  Click the small arrow at the top right to change the view to Largelcon: Or right-click properties in the appearance View Change it to Details2. Set column header -  Click the small arrow at the top right to select the edit column , Then add ...

  4. Outlook receive qq The mail

    1. Go first qq mailbox , Set up , Account Turn on pop3 service , If you've opened it before , It's better to turn it off and on again The latest version must use a separate password of the mailbox to receive mail ( Otherwise, even if you opened it before , I can't use your qq The account number and password are used to collect e-mail ) 2 ...

  5. turn : Window enabled / Disable function EnableWindow Use

    In Africa MFC How to disable controls or windows in the environment ? At first I wanted to do it by sending messages , But looking around, there is a message that the control is disabled ( It's also because the blogger Mu you found it ), So we have to find another way , Use EnableWindow This function , This function ...

  6. Android- management Activity Life cycle - Stop and restart Activity

    Stop and restart activity stay activity It's important in the life cycle of , It makes users feel like you're app Always active and don't lose their progress .activity In the following cases, it will stop and restart : The user opens the common app Window and then from you ...

  7. Android View, Window,Activity Conceptual differentiation (2)

    (1)View: The most basic UI Components , Represents a rectangular area on the screen . (2)Window: Represents a window , It doesn't have to be the size of a screen , It can be big or small : It contains a View tree And the window layout Parameters .Vie ...

  8. dos2unix and unix2dos

    dos2unix take windows Format file to linux File format . unix2dos take linux Format file to windows File format . dos2unix and unix2dos Can convert windows and ...

  9. miui10 Fool installation google Framework approach

    miui10, Open your own Xiaomi app store , Search for youtube, Then look down and choose Baidu's search source , Click in and choose the first one to download . You will be reminded to install after downloading google The framework , It will be installed automatically after the point is confirmed , It's that simple ...

  10. java Basic advanced two :HashMap Implementation principle analysis

    HashMap Implementation principle analysis 1. HashMap Data structure of There are arrays and linked lists in the data structure to store the data , But the two are basically two extremes . Array The storage range of an array is continuous , Serious memory occupation , So the space is very complex . But two of the arrays ...