Connection Manager for MySQL source code learning

MySQLDatabaseConnection ManagerThe related knowledge is what we will introduce in this article. Let's take a look at it.

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, and 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_scheduler); /* 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_scheduler(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, namely named pipe, socket, 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 creates a network-based connection request on a TCP/IP connection. Generally, the client is on one server while the MySQL instance is 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, if two processes that need to communicate 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, you must enable the -- enable-named-pipe option in the configuration file.

In Versions later than MySQL 4.1, MySQL also provides a shared memory connection mode by adding -- shared-memory to the configuration file. 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 creates a new thread

Socket management is actually relatively simple, and its pseudo code is directly provided:

 
 

  
  
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. The thread buffer pool concept is also involved in multi-threaded connections, that is, if a connection is disconnected, the threads 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 buffer pool to check whether there are Idle threads. If yes, use them directly. If yes, create a new thread to manage the connection.

This article introduces the related knowledge of MySQL database Connection Manager. I hope this introduction will be helpful to you!