About MySQL user sessions and connection threads

0. Conceptual understanding: What is the relationship between user sessions and connection threads?

A user session and a user connection thread are one by one corresponding relationships, and a session is a user-connected thread.

Problem Description:

What if the system hangs on the system because it performs a very large DML or DDL operation, and we want to break the operation?

Workaround:

1. Kill thread: Kill user's session

But the time is long, the effect is not good: roll forward + rollback, if it has been for a long time, it takes more time to roll back

2. Kill mysqld Process: recommended , with this way of killing the process, fast

Kill-9 process Number (PS aux view process number)

The database roll, not the active rollback, directly to the external service, when read to which uncommitted transaction, then go back to slowly rollback.

One, kill user session and user connection thread

1. How to view user sessions, how to kill user sessions

[Email protected] ~]# NETSTAT-ANP |grep 3306TCP0      0:::3306:::* LISTEN17324/mysqld[[Email protected]~]#PS-ef |grep 'mysql-px x'Root20510 20483  0  the: -pts/1    xx:xx:xxMySQL-px Xroot20528 46646  0  the: -pts/4    xx:xx:xx grepMySQL-px Xroot45626 45565  0  to: -pts/3    xx:xx:xxMySQL-px X[[email protected]~]#Kill-9 20510 

# kill-9 <mysql Session process pid>// Quick Release Resources (recommended)

Note: do not kill mysqld, do not destroy the listen process

2, how to view the user connection thread, how to kill the user connection thread

Mysql>show Processlist;+--------+|Id|+--------+| 194850 || 194851 |+--------+MySQL> Kill 194850; MySQL>show Processlist;+--------+|Id|+--------+| 194851 || 194852 |+--------+

Note: If the user connection thread is killed and the session is not killed, the user session will reopen a user connection thread.

3, kill the user connection thread work process, the risk of abuse

1. Process:

Rollback---> Release Resources---> Kill Thread

2. Disadvantages and Risks:

1, the system may appear more busy situation (because of a large number of rollback)

2. Session release takes a long time

Q: Suppose there are 1000 users connected now, how to kill quickly?

A:

　　1. Write scripts using concat

Mysql> Select Concat (' Kill ', ID, '; ') into the Outpfile '/tmp/kill.txt ' from INFORMATION_SCHEMA. Processlist;

Shell> Cat Kill.txt

Kill 194850;

Kill 194851;

Then, copy to MySQL to execute and kill the user connection thread.

　　2. Use awk to remove user session process ID all kill

shell> netstat-anp|grep mysql|grep-v Mysqld|awk ' {print $8} ' |awk-f '/' {print '} ' |xargs kill-9

Note about the Mysqld_safe:

At the OS level, the user connection thread is killed, the MySQL-related process and the thread ID can be seen through pstree-p, and the #kill-9 MySQL session will cause the MYSQLD process to be killed, but through the Mysqld_safe security mechanism, It will restart a mysqld process, so the original user connection thread was killed with the original process.

Second, the MySQL client connection

1, the maximum number of connections

Mysql>Show variables like 'max_connections';+-----------------+-------+|Variable_name|Value|+-----------------+-------+|Max_connections| 151   |+-----------------+-------+

From the above, the maximum number of connections for the MySQL default client is 151, however, more than 100 connections under large concurrency will be insufficient, and you need to adjust the maximum number of connections to modify and write to the configuration file:

max_connections = 1000

Wait_timeout = 1000000 #超时时间

Restart MySQL Service

2. See how many connections are currently available

Mysql>Show status like '%threads_connected%';+-------------------+-------+|Variable_name|Value|+-------------------+-------+|threads_connected| 1     |+-------------------+-------+1Rowinch Set(0.01sec) MySQL>show Processlist;+-------+------+-----------+------+---------+------+----------+------------------+|Id| User |Host|Db|Command|Time|State|Info|+-------+------+-----------+------+---------+------+----------+------------------+| 17219 |Root|localhost| NULL |Query|    0 |Starting|Show Processlist|+-------+------+-----------+------+---------+------+----------+------------------+1Rowinch Set(0.01Sec

3, the maximum number of failed connections:max_connect_errors

Mysql>Show variables like 'max%errors';+--------------------+-------+|Variable_name|Value|+--------------------+-------+|Max_connect_errors|  -   |+--------------------+-------+1Rowinch Set(0.00Sec

is a security-related counter value in MySQL that is responsible for preventing excessive attempts by clients that have failed to brute-force passwords, and the size of the value is not much related to performance.

The default is 100, which means that if a client attempts to connect to this MySQL server but fails (such as a password error, etc.) 10 times, MySQL will unconditionally force this client connection to be blocked. If you want to reset the counter to a client's value, you must restart Mysqld or mysql> flush hosts, and when the client successfully connects Mysqld, Max_connect_errors for this client will be zeroed out.

If the settings for max_connect_errors are too small, the Web page may prompt that the database server cannot be connected.

In general, it is recommended that the database server does not listen for connections from the network, only through sock connections, which prevents most attacks against MySQL, and if it is necessary to turn on MySQL's network connection, it is best to set this value to prevent brute-code attacks.

Third, about the user's working space

Q: How can I tell if the user's session thread space (user workspace) is assigned too small?

A:

1, Sort_buffer_size: Need to sort the cache size of the session (default 256K), is for each connection, too large configuration + high concurrency may drain the system memory resources.

2, binlog_cache_size: Binary log buffer (default 32K), based on the session;

A transaction to make a change,

When the content is less than 4binlog_cache_size, all modifications are stored in binary log cache;

When the content is greater than binlog_cache_size, the contents are saved to the disk temporary table.

3, Join_buffer_size: Multi-table connection result set buffer, based on the session;

(each join operation calls My_malloc, My_free function requests/frees memory of join_buffer_size size)

That is , thecomposition of Sort_buffer, Binlog_cache, and Join_buffer forms the user session thread space . In general, when sort_merge_passes (disk sequencing) value is large per second, it indicates that the user's workspace assignment is too small, you should consider increasing the sort_buffer_size value.

About MySQL user sessions and connection threads