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

Please indicate the origin of the original.

1 、 max_user_connections

The parameter max_user_connections is the maximum number of sessions allowed to connect to a single user, and there is a similar limit when establishing a user. This parameter is only mentioned here.

The following is the official documentation:

The maximum number of simultaneous connections permitted to any given MySQL user account. A

Value of 0 (the default) means "no limit."

This variable has a global value that can be set at server startup or runtime. It also has a read-only

Session value that indicates the effective simultaneous-connection limit that applies to the account

Associated with the current session. The session value is initialized as follows:

? If the user account has a nonzero MAX_USER_CONNECTIONS resource limit, the session

Max_user_connections value is set to that limit.

? Otherwise, the session max_user_connections value is set to the global value.

The error is as follows:

Mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1203 (42000): User root already has more than 'max_user_connections' active connections

2 、 max_connections

Max_connections: this parameter is the maximum number of connection sessions allowed by the MYSQL server. There is nothing to say.

The following is the official documentation:

The maximum permitted number of simultaneous client connections. By default, this is 151. See

Section B.5.2.7, "Too many connections", for more information.

Increasing this value increases the number of file descriptors that mysqld requires. If the required

Number of descriptors are not available, the server reduces the value of max_connections. See

Section 9.4.3.1, "How MySQL Opens and Closes Tables", for comments on file descriptor limits.

The test is also very simple and the error report is as follows:

[root@testmy] # / mysqldata/mysql5.7/bin/mysql-- socket=/mysqldata/mysql5.7/mysqld3307.sock-utestmy-pGelc123123

Mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1040 (HY000): Too many connections

3 、 Max_used_connections 、 Max_used_connections_time

Let's just say a little bit here.

Mysql > show global status like'% max%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Max_used_connections | 7 | |

| | Max_used_connections_time | 2017-05-10 17:10:56 |

These two states refer to the maximum number of connections and the time when MYSQL SERVER was last started, and have nothing to do with the parameters mentioned above.

? Max_used_connections

The maximum number of connections that have been in use simultaneously since the server started.

? Max_used_connections_time

The time at which Max_used_connections reached its current value. This variable was added in

MySQL 5.7.5

4 、 back_log

Back_log: the interpretation of this parameter is relatively complex and difficult to understand because most DBA are not familiar with C programming under LINUX

This parameter refers to the use of the formal parameter back_log when calling listen in sokcet programming.

Function prototype

Int listen (int sockfd, int backlog)

Described in man page:

DESCRIPTION

Listen () marks the socket referred to by sockfd as a passive socket, that is, as a socket that will be used to accept incoming connection requests using accept (2).

The sockfd argument is a file descriptor that refers to a socket of type SOCK_STREAM or SOCK_SEQPACKET.

The backlog argument defines the maximum length to which the queue of pending connections for sockfd may grow. If a connection request arrives when the queue is full, the client may receive an error with

An indication of ECONNREFUSED or, if the underlying protocol supports retransmission, the request may be ignored so that a later reattempt at connection succeeds.

RETURN VALUE

On success, zero is returned. On error,-1 is returned, and errno is set appropriately.

The scoket file descriptor returned when sockfd has nothing to say about create. The backlog here is actually a queue of pending connections. If it exceeds the possible value, it will return an error of ECONNREFUSED, but if the underlying protocol supports retransmission

This error will be ignored and try again to know the success It depends on the small value of the LINUX system setting / proc/sys/net/core/somaxconn and the function backlog, of course the LINUX system defaults to 128.

The following is a description of the official MYSQL documentation:

The number of outstanding connection requests MySQL can have. This comes into play when the

Main MySQL thread gets very many connection requests in a very short time. It then takes some

Time (although very little) for the main thread to check the connection and start a new thread. The

Back_log value indicates how many requests can be stacked during this short time before MySQL

Momentarily stops answering new requests. You need to increase this only if you expect a large number

Of connections in a short period of time.

In other words, this value is the size of the listen queue for incoming TCP/IP connections. Your operating

System has its own limit on the size of this queue. The manual page for the Unix listen () system

Call should have more details. Check your OS documentation for the maximum value for this variable.

Back_log cannot be set higher than your operating system limit

In fact, it says that such a problem may occur when there are a large number of connections. Here, a short time is used to describe it, and it also specifically tells you this.

The parameter is related to the Unix listen () system call.

The MYSQL listen function call stack is

Click (here) to collapse or open

# 0 0x0000003ca5ee9880 in listen () from / lib64/libc.so.6

# 1 0x00000000016e3482 in inline_mysql_socket_listen (src_file=0x21c5f30 "/ root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/socket_connection.cc", src_line=522, mysql_socket=..., backlog=2)

At / root/mysql5.7.14/percona-server-5.7.14-7/include/mysql/psi/mysql_socket.h:1084

# 2 0x00000000016e5b1b in TCP_socket::get_listener_socket (this=0x7fffffffdb40) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/socket_connection.cc:522

# 3 0x00000000016e41d8 in Mysqld_socket_listener::setup_listener (this=0x339d550) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/socket_connection.cc:808

# 4 0x0000000000ecefed in Connection_acceptor::init_connection_acceptor (this=0x2fd4a90) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_acceptor.h:55

# 5 0x0000000000ec089d in network_init () at / root/mysql5.7.14/percona-server-5.7.14-7/sql/mysqld.cc:1864

# 6 0x0000000000ec6594 in mysqld_main (argc=52, argv=0x2e97438) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/mysqld.cc:5103

# 7 0x0000000000ebd344 in main (argc=9, argv=0x7fffffffe3f8) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/main.cc:25 We usually write this when programming:

Click (here) to collapse or open

If ((listen (listenfd,1)) =-1) / / set back_log to 1 here

{

Perror ("listen")

Return-1

}

Printf ("Accepting connections...\ n")

While (1)

{

/ / cliaddr_len = sizeof (cliaddr)

/ / 4. Accept connections from the client to generate data interaction socket fd

If ((confd = accept (listenfd, (SCOK_ADD) & cliaddr,&cliaddr_len)) =-1)

{

Return 1

}

Else

{

Printf ("Client ip:%s Port:%d\ n", inet_ntop (AF_INET, & cliaddr.sin_addr, str, sizeof (str)), ntohs (cliaddr.sin_port))

If ((ret = pthread_create (& tid,NULL,do_work, (void*) & confd)! = 0)

{

Printf ("% s\ n", strerror (ret))

Return 1

}

If ((ret = pthread_detach (tid))! = 0)

{

Printf ("% s\ n", strerror (ret))

Return 1

}

Printf ("thread% lu is create and detach\ n", tid)

}

/ write (confd, "a", 1)

/ / close (confd)

}

Close (listenfd)

Return 0

}

This is probably to let you know exactly how listen () and accpet () are used, otherwise it doesn't seem easy to understand.

But what exactly does it represent? we still have to refer to the LINUX system programming manual and the actual programming model. Let's take a look at this picture first.

I deliberately say that part of the Chinese content is intercepted to make it easier for you to understand.

When the server is busy with other clients, the time consumed by this unatomized process between listen () and accept () will be magnified, generally speaking, it is a very short period of time, that is,

The short time mentioned in the MYSQL official manual, where the client connect () comes, will enter the pending connection request queue before the server accept () function is called, with this foundation.

Let's sort out the process.

MYSQL connect ()-- > > server listen ()-- > > enter the pending connection queue-- > > server accpet ()-- > > establish connection transport interaction

So this parameter actually controls a kind of blocking. If I set back_log to 2, then the connection that enters the pending connection queue at the same time is 2. At this time, if both connections do not have accpet ()

After completion, the new connection will only have to wait to enter the queue, which is a kind of blocking.

This kind of problem is really difficult to test. I used the program back_log setting 1 to simulate a large number of connections. It is true that there may be congestion problems, but I did not find out how to check the output of the current number of outstanding connections in back_log.

So the persuasive power is limited, so put it here first, if you find a way to simulate it later.

The following information is transferred from the Internet.

Http://blog.chinaunix.net/uid-24782829-id-3456109.html

The listen function is called only by the TCP server, and it does two things:

1. When the socket function creates a socket, it is assumed to be an active socket, that is, a client socket that will call connet to initiate the connection. The listen function converts an unconnected socket into a passive socket, indicating that the kernel should accept connection requests to that socket. According to the TCP state transition diagram, calling listen causes the socket to transition from the CLOSED state to the LISTEN state.

2. The second parameter of this function specifies the maximum number of connections that the kernel should queue for the corresponding socket.

To better understand the backlog parameters, we must realize that the kernel maintains two queues for any given listening socket:

1. Incomplete connection queue (incomplete connection queue). Each such SYN section corresponds to one of the items: it has been sent by a customer and arrived at the server, and the server is waiting for the corresponding TCP three-way handshake process to be completed. These sockets are in the SYN_RCVD state.

2. The connection queue (completed connection queue) has been completed, and each customer who has completed the TCP three-way handshake process corresponds to one of them. These sockets are in the ESTABLISHED state.

When the SYN from the customer arrives, the TCP creates a new entry in the incomplete connection queue and then responds to the second section of the three-way handshake: the server's SYN response, with a slight ACK to the customer's SYN (that is, SYN+ACK). This item remains in the queue for incomplete connections until the third section of the three-way handshake (the customer's ACK to the server SYN) arrives or the item times out (implementations that once originated from Berkeley set a timeout value of 75 seconds for these outstanding connections). If the three-way handshake completes normally, the item never completes the connection queue and moves to the end of the queue that has completed the connection queue. When a process calls accept, the queue header item in the completed connection queue is returned to the process, or if the queue is empty, the process is put to sleep until TCP puts an item in the queue to wake it up.

The length of the unfinished queue (incomplete connection queue) is now set by / proc/sys/net/ipv4/tcp_max_syn_backlog, which is now defaulted to 512 in most recent linux kernels. This setting is valid only if the system's syncookies function is disabled, and if the system's syncookies function is enabled, then this setting is invalid. Syncookies is set when the kernel is compiled to see if syncookies starts:

Cat / proc/sys/net/ipv4/tcp_syncookies

If "1" is enabled, "0" means it is not enabled.

So what do you do for syncookies and why it has something to do with the unfinished queue. To put it simply, it is designed to prevent SYN Flood attacks. For details, please refer to the introduction to syncookies (http://baike.baidu.com/view/9033755.htm).

Moving on to backlog, if we set the backlog parameter of listen to exceed / proc/sys/net/core/somaxconn, then the value of the backlog parameter is automatically overwritten to the value of / proc/sys/net/core/somaxconn, and its default size is 128.

Author Wechat:

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.