1. Number of connected threads
Mysql supports single-thread and multi-thread connection threads. If it is a single thread, only one connection can be connected to Mysql at the same time,
Other connections will be suspended. For multithreading, multiple connections can be connected to the server at the same time.
You can set the number of connection threads by setting the server startup parameters:
Mysqld.exe -- thread-handling = no-threads
Mysqld.exe -- thread-handling = one-thread-per-connection
How does the server choose which method to use through parameters? Check the branch code on the server:
# Ifdef EMBEDDED_LIBRARY
One_thread_scheduler (& thread_scheduler );
# Else
If (global_system_variables.thread_handling <=
SCHEDULER_ONE_THREAD_PER_CONNECTION)
One_thread_per_connection_scheduler (& thread_scheduler );
Else if (global_system_variables.thread_handling = SCHEDULER_NO_THREADS)
One_thread_scheduler (& thread_scheduler );
Else
Pool_of_threads_scheduler (& thread_schedcov);/* purecov: tested */
# Endif
This code appears in the get_options function. This function sets the mode of corresponding parameters based on the input server parameters. From this code, we can see that if the EMBEDDED_LIBRARY macro definition is defined (it should be embedded), one_thread_scheduler is called to use a single thread. If the parameter is smaller than or equal to SCHEDULER_ONE_THREAD_PER_CONNECTION, one_thread_per_connection_scheduler is called, that is, each connection has one thread, that is, multiple threads. How do I set global_system_variables.thread_handling? In fact, it is set based on the parameter -- thread-handling passed to the server. The parameter settings are unified in the get_options function. It calls mysqld_get_one_option, which has a branch and the code is as follows:
Case OPT_THREAD_HANDLING:
{
Global_system_variables.thread_handling =
Find_type_or_exit (argument, & thread_handling_typelib, opt-> name)-1;
Break;
}
If you are interested in parameter initialization, you can refer to the get_options function in detail. Let's take a look at the source code of one_thread_scheduler and one_thread_per_connection_scheduler. What have they done?
Void one_thread_scheduler (scheduler_functions * func)
{
Func-> max_threads = 1;
# Ifndef EMBEDDED_LIBRARY
Func-> add_connection = handle_connection_in_main_thread;
# Endif
Func-> init_new_connection_thread = init_dummy;
Func-> end_thread = no_threads_end;
}
Void one_thread_per_connection_schedctions (scheduler_functions * func)
{
Func-> max_threads = max_connections;
Func-> add_connection = create_thread_to_handle_connection;
Func-> end_thread = one_thread_per_connection_end;
}
It turns out that scheduler_functions parameters in a struct are set, but these parameters are some function pointers, that is, in the specific call,
You only need to call add_connection or end_thread. You don't need to know which function is called. This is probably a variant polymorphism.
2. initialize Network Configuration
The network configuration is simple, that is, to set the port, create a socket, bind the port, and listen to the port. All implementations are concentrated in the network_init function.
There is nothing to talk about. If you are not familiar with socket, you can search for related knowledge online. The corresponding pseudo code is provided here:
Network_init
{
Set_ports; // set the port number, # define MYSQL_PORT 3306
Socket; // create a socket
Bind; // bind the port number.
Listen; // listening port number
}
3. Connection Method
The methods for inter-process communication are not only SOCKET, but also many other methods. Mysql supports three connection modes: namepipe, socket, and shared memory,
That is, the method for naming pipelines, sockets, and shared memory. These three methods can coexist. By default, only sockets are used.
The TCP/IP socket method is the connection method provided by MySQL in any platform and the most widely used method in the network. This method is used to establish a TCP/IP connection.
A network-based connection request, usually on one server, while a MySQL instance on another server, the two machines are connected through a TCP/IP network.
For example, on a Windows Server, I can request a MySQL instance on a remote Linux server.
In Windows 2000, Windows XP, Windows 2003, Windows Vista, and later Windows operating systems
The process of the message is on the same Server, you can use the named pipe. The local connection after the SQL Server database is installed by default also uses the named pipe. In the MySQL database,
Enable the -- enable-named-pipe option in the configuration file. In Versions later than MySQL 4.1, MySQL also provides the shared memory connection mode. In the configuration file
Add -- shared-memory. To use shared memory, the Mysql client must also use the-protocol = memory option during connection.
You can set the parameters below during startup.
Mysqld.exe -- enable-named-pipe
Mysqld.exe -- shared-memory
In addition to setting parameters at startup, you can also modify the MY. ini file. Let's take a look at handle_connections_methods, the branch function that selects the connection method in the source code:
Handle_connections_methods ()
{
If (hPipe! = INVALID_HANDLE_VALUE)
{
Handler_count ++;
If (pthread_create (& hThread, & connection_attrib,
Handle_connections_namedpipes, 0 ))
{
SQL _print_warning ("Can't create thread to handle named pipes ");
Handler_count --;
}
}
If (have_tcpip &&! Opt_disable_networking)
{
Handler_count ++;
If (pthread_create (& hThread, & connection_attrib,
Handle_connections_sockets, 0 ))
{
SQL _print_warning ("Can't create thread to handle TCP/IP ");
Handler_count --;
}
}
If (opt_enable_shared_memory)
{
Handler_count ++;
If (pthread_create (& hThread, & connection_attrib,
Handle_connections_shared_memory, 0 ))
{
SQL _print_warning ("Can't create thread to handle shared memory ");
Handler_count --;
}
}
}
Because I don't know much about the communication methods between namepipe and memory share, I will only study the socket communication methods here. From the code, we can see that handle_connections_sockets is the socket setting. Let's look at it.
4. socket management is actually relatively simple to create a new thread socket management, and its pseudo code is provided directly:
Handle_connections_sockets
{
Select; // monitor the socket file descriptor
New_socket = accept; // process the incoming client connection
Thd = new THD; Create THD class
Vi_tmp = vi_new (new_socket, vi_type_tcpip, 0); // initialize the VIO struct
My_net_init (& thd-> net, vie_tmp); // initialize the net struct of thd.
Create_new_thread (thd); // create a new thread for this connection. If it is in single thread mode, no new thread will be created.
}
First, the select function monitors the socket port. If a connection is monitored, the accept function is used to accept the connection from the client, and a new THD class is created, set all connection parameters to THD class parameters and call the create_new_thread function. This function is the focus. Let's go to this function and see what we have done.
Create_new_thread
{
+ + Connection_count; // The number of global connections increases.
Thread_count ++; // auto-increment of the number of global threads
Thread_scheduler.add_connection (thd); // create a thread
}
So easy: first, add the number of global connections + 1, the number of global threads + 1, and then call the add_connection function. This function is the first step in which we set the connection
Among the threads, one parameter set in one_thread_scheduler and one_thread_per_connection_scheduler. The difference between the two is whether or not they have been created.
A new thread to process incoming connections. One_thread_scheduler is a single-thread method, with a newly created thread. We will focus on one_thread_per_connection_scheduler. The add_connection function is create_thread_to_handle_connection:
Create_thread_to_handle_connection (THD * thd)
{
Thread_created ++;
Threads. append (thd); // The number of created threads is auto-incrementing and added to the threads linked list.
Pthread_create (& thd-> real_id, & connection_attrib,
Handle_one_connection,
(Void *) thd); // This is where the thread is actually created. The function is handle_one_connection.
}
It can be seen that the pthread_create function is called at last. This function creates a new thread and the processing function of the new thread is handle_one_connection.
5. New thread processing process
The new thread processing function is handle_one_connection. At this point, a new connection is independently processed by a newly created thread. Let's take a look at it.
Is how to process.
Handle_one_connection (void * arg)
{
For (;;)
{
Lex_start (thd); // initialize the lexical analysis structure.
Login_connection (thd); // user authentication, error
Prepare_new_connection_state (THD * thd); // Initialize THD to handle queries
While (! Net-> error & net-> vio! = 0 & // cyclic Processing command
! (Thd-> killed = THD: KILL_CONNECTION ))
{
If (do_command (thd ))
Break; // handle failed jumps out
}
End_connection (thd); // close the connection
Close_connection (thd, 0, 1 );
Thread_scheduler.end_thread (thd, 1); // end the thread
Return 0;
}
}
First, initialize the lexical analysis structure, and then perform user authentication. After successful authentication, execute the commands sent from the client through the do_command loop.
6. Summary
The entire connection manager process is very clear, and single-threaded connections are rarely used. Most of them use multithreading. Thread buffering is also involved in multi-threaded connections.
The concept of a pool, that is, if a connection is disconnected, the thread it creates will not be destroyed, but will be placed in the buffer pool. Wait for the next new connection to arrive, first go to the thread
Check whether there are Idle threads in the buffer pool. If yes, use them directly. If yes, create a new thread to manage the connection. These contents belong to Thread Manage,
Next section describes how to learn Thread Manage.
PS. The first double break was held last Saturday. The result was still called a company BUG change. I am sorry. But BOSS, when will the overtime pay in the last few months be paid? Do not pay in arrears for migrant workers...
Walking down the fallen leaves and pursuing my dream. Reprinted please indicate the source
Excerpted with no code in mind