MariaDB thread pool Source Code Analysis _ MySQL

MariaDB thread pool source code analysis MariaDB

BitsCN.com

MariaDB thread pool source code analysis without code 0 preface

The Enterprise version of MySQL5.5 introduces the thread pool in the form of plugin. when the number of concurrent requests reaches a certain number, the performance seems to be much better than the community version. you can see this performance comparison.

Before the thread pool is introduced, MySQL supports two thread processing methods (thread_handling parameter control): no-threads and one-thread-per-connection, the no-threads method means that at any time, only one connection can be connected to the server. it is generally used for experimental purposes. One-thread-per-connection is a pointer that creates a thread for each connection to process all requests of the connection until the connection is disconnected and the thread ends. It is the default thread_handling method.

One-thread-per-connection creates a new thread for each connection. when the number of concurrent connections reaches a certain level, the performance will decrease significantly, because too many threads will lead to frequent context switching, the CPU cache hit rate is reduced and the lock competition is more intense.

The solution to one-thread-per-connection is to reduce the number of threads, so that multiple connections are required to share the thread, which introduces the thread pool concept. The threads in the thread pool are Request-oriented rather than connection-oriented. that is to say, several connections may use the same thread to process their respective requests.

MariaDB introduced a dynamic thread pool solution in 5.5, which can automatically increase or decrease the number of threads based on the current request concurrency. Fortunately, MariaDB is fully open-source, this article introduces the implementation of thread pool based on MariaDB code. The MariaDB 10.0 code tree is used here.

1. related parameters

MySQL parameters are written inSys_vars.ccFile.

static Sys_var_uint Sys_threadpool_idle_thread_timeout(  "thread_pool_idle_timeout",  "Timeout in seconds for an idle thread in the thread pool."  "Worker thread will be shut down after timeout",  GLOBAL_VAR(threadpool_idle_timeout), CMD_LINE(REQUIRED_ARG),  VALID_RANGE(1, UINT_MAX), DEFAULT(60), BLOCK_SIZE(1));static Sys_var_uint Sys_threadpool_oversubscribe(  "thread_pool_oversubscribe",  "How many additional active worker threads in a group are allowed.",  GLOBAL_VAR(threadpool_oversubscribe), CMD_LINE(REQUIRED_ARG),  VALID_RANGE(1, 1000), DEFAULT(3), BLOCK_SIZE(1));static Sys_var_uint Sys_threadpool_size( "thread_pool_size", "Number of thread groups in the pool. " "This parameter is roughly equivalent to maximum number of concurrently " "executing threads (threads in a waiting state do not count as executing).",  GLOBAL_VAR(threadpool_size), CMD_LINE(REQUIRED_ARG),  VALID_RANGE(1, MAX_THREAD_GROUPS), DEFAULT(my_getncpus()), BLOCK_SIZE(1),  NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0),  ON_UPDATE(fix_threadpool_size));static Sys_var_uint Sys_threadpool_stall_limit( "thread_pool_stall_limit", "Maximum query execution time in milliseconds," "before an executing non-yielding thread is considered stalled." "If a worker thread is stalled, additional worker thread " "may be created to handle remaining clients.",  GLOBAL_VAR(threadpool_stall_limit), CMD_LINE(REQUIRED_ARG),  VALID_RANGE(10, UINT_MAX), DEFAULT(500), BLOCK_SIZE(1),  NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0),   ON_UPDATE(fix_threadpool_stall_limit));

The parameters are described accordingly.
Thread_pool_size: number of groups in the thread pool. The thread pool of MariaDB is not an entire large pool, but divided into different groups and grouped according to the order of incoming connections. for example, the first connection is allocated to group [0]. then the second connection is allocated to group [1], which is a Round Robin distribution method of Round Robin. The default value is the number of CPU cores.

Thread_pool_idle_timeout: maximum idle time of a thread. if the idle time of a thread is greater than this parameter, the thread exits.

Thread_pool_stall_limit: monitoring interval. the thread pool has a monitoring thread. at this time, it checks the available number of threads in each group and performs corresponding processing, for example, wake up or create thread.

Thread_pool_oversubscribe: Number of active threads in each group. Note that this is not the maximum number of threads in each group, but the number of threads that can process requests.

2 thread handling settings

The thread pool mode is actually a new thread_handling method, that is, set in the configuration file:

[mysqld]thread_handling=pool-of-threads.....

MySQL has a scheduler_functions struct. no matter which method thread_handling is used, different scheduling is performed by setting the function in this struct.

/** Scheduler_functions struct */struct scheduler_functions {uint max_threads, * connection_count; ulong * max_connections; bool (* init) (void); bool (* callback) (void ); void (* add_connection) (THD * thd); void (* thd_wait_begin) (THD * thd, int wait_type); void (* thd_wait_end) (THD * thd ); void (* post_kill_notification) (THD * thd); bool (* end_thread) (THD * thd, bool cache_thread); void (* end) (void );}; static int get_options (int * argc_ptr, char *** argv_ptr ){... /** select different processing methods based on the thread_handling option setting */if (thread_handling <= handle)/** one thread per connection mode */one_thread_per_connection_scheduler (thread_schedons, & max_connections, & connection_count); else if (thread_handling = SCHEDULER_NO_THREADS)/** no thread mode */one_thread_scheduler (thread_schedpool); else/** thread pool mode */thread (thread_scheduler, & max_connections, & connection_count );...} static scheduler_functions tables = {0, // max_threads NULL, NULL, tp_init, // init NULL, // init_new_connection_thread tp_add_connection, // add_connection tp_wait_begin, // thd_wait_waitin tpwait_end, // thd_wait_end post_kill_notification, // post_kill_notification NULL, // end_thread tp_end // end}; void evaluate (struct functions * func, ulong * arg_max_connections, uint * arg_connection_count) {/** set the scheduler_functions struct to worker */* func = worker; func-> max_threads = worker; func-> max_connections = arg_max_connections; func-> connection_count = arg_connection_count; scheduler_init ();}

As shown above, the processing function of thread_schedctions is set to tp_scheduler_functions, that is, the thread pool mode. The initial function corresponding to this method is tp_init, and the function for creating a new connection is tp_add_connection, the start function is tp_wait_begin, and the end function is tp_wait_end. the meaning of the waiting function is described here. the waiting function is generally used to call wait_begin while waiting for disk I/O, waiting for lock resources, SLEEP, or waiting for network messages, and wait_end after waiting for the end, so why call the wait function while waiting? This will be introduced later.

In fact, the above description does not have a great relationship with the thread pool. next we will introduce the thread pool process. The source code involved in the thread pool is in emphsql/threadpool_common.cc and emphsql/threadpool_unix.cc. for windows, there is also emphsql/threadpool_win.cc.

3. thread pool initialization -- tp_init

>tp_init| >thread_group_init| >start_timer

Tp_init is very simple. first, thread_group_init is called to initialize the group, and then start_timer is called to enable the monitoring thread timer_thread. So far, only one monitoring thread is started in the thread pool, without any working threads until a new connection comes.

4. add a new connection -- tp_add_connection

void tp_add_connection(THD *thd){  DBUG_ENTER("tp_add_connection");    threads.append(thd);  mysql_mutex_unlock(&LOCK_thread_count);  connection_t *connection= alloc_connection(thd);  if (connection)  {    thd->event_scheduler.data= connection;          /* Assign connection to a group. */    thread_group_t *group=       &all_groups[thd->thread_id%group_count];        connection->thread_group=group;          mysql_mutex_lock(&group->mutex);    group->connection_count++;    mysql_mutex_unlock(&group->mutex);        /*       Add connection to the work queue.Actual logon        will be done by a worker thread.    */    queue_put(group, connection);  }  else  {    /* Allocation failed */    threadpool_remove_connection(thd);  }   DBUG_VOID_RETURN;}

However, when the server's main listening thread listens to connect with a client, it will call the tp_add_connection function for processing. First, perform the modulo operation on group_count according to thread_id, find the group to which it belongs, and call queue_put to put the connection into the queue of the group. Two new struct types are involved: connection_t and thread_group_t.

Struct connection_t {THD * thd; thread_group_t * thread_group; connection_t * next_in_queue; connection_t ** timeout; ulonglong abs_wait_timeout; // bool logged_in; // check whether the bool bound_to_poll_descriptor is logged on; // check whether the bool bound_to_poll_descriptor is added to the epoll to listen to the bool waiting; // whether it is waiting, such as I/O, sleep }; struct thread_group_t {mysql_mutex_t mutex; connection_queue_t queue; // connection request linked list worker_list_t waiting_threads; // The group is waiting for the thread worker_thread_t * listener to be awakened; // the thread pthread_attr_t * pthread_attr used for listening in the current group; int pollfd; // epoll file descriptor, used to bind all connections in the group int thread_count; // number of threads int active_thread_count; // Number of active threads int connection_count; // number of connections/* Stats for the deadlock detection timer routine. */int io_event_count; // number of events generated by epoll int queue_event_count; // number of events digested by the worker thread ulonglong last_thread_creation_time; int shutdown_pipe [2]; bool shutdown; bool stalled; // whether the working thread is in the stuck State} MY_ALIGNED (512 );

These parameters are described above. only by understanding the meaning of these parameters can we understand the management mechanism of this dynamic thread pool, because each parameter will affect the growth or contraction of the thread pool.

After introducing the struct, continue to return to the new connection. then, the queue_put function is called to put the connection in the queue of the group.

static void queue_put(thread_group_t *thread_group, connection_t *connection){  DBUG_ENTER("queue_put");  mysql_mutex_lock(&thread_group->mutex);  thread_group->queue.push_back(connection);  if (thread_group->active_thread_count == 0)    wake_or_create_thread(thread_group);  mysql_mutex_unlock(&thread_group->mutex);  DBUG_VOID_RETURN;}

Note: There is an active_thread_count judgment at this time. if there is no active thread, this new request cannot be processed. in this case, we need to call wake_or_create_thread, this function will first try to wake up the threads in the group wait thread linked list waiting_threads. if there is no waiting thread, you need to create a thread. So far, the new connection is mounted to the queue of the group, so a connection is added to the queue. how can this connection be handled? Let's continue.

5 worker threads -- worker_main

As the first connection arrives, there is certainly no waiting_threads. at this time, the create_worker function will be called to create a working thread. Let's look at the working thread.

static void *worker_main(void *param){  ...  DBUG_ENTER("worker_main");    thread_group_t *thread_group = (thread_group_t *)param;  /* Run event loop */  for(;;)  {    connection_t *connection;    struct timespec ts;    set_timespec(ts,threadpool_idle_timeout);    connection = get_event(&this_thread, thread_group, &ts);    if (!connection)      break;    this_thread.event_count++;    handle_event(connection);  }  ....  my_thread_end();  return NULL;}

The above is the logic of the entire working thread. we can see that it is a loop. get_event is used to obtain the new connection to be processed, and then handle_event is called to process the corresponding connection. In one thread per connection, each thread is also a loop body. The difference between the two is that the thread pool waits for an available event, it is not limited to a fixed connection event, but the loop wait of one thread per connection is waiting for the event on the fixed connection, which is the biggest difference between the two.

6. get the event -- get_event

The worker thread obtains the connection to be processed through get_event,

Connection_t * get_event (worker_thread_t * current_thread, thread_group_t * thread_group, struct timespec * abstime ){... for (;;){... /** get connection */connection = queue_get (thread_group) from QUEUE; if (connection) {fprintf (stderr, "Thread % x get a new connection. /n ", (unsigned int) pthread_self (); break ;}... /** listen to epoll */if (! Thread_group-> listener) {thread_group-> listener = current_thread; thread_group-> active_thread_count --; mysql_mutex_unlock (& thread_group-> mutex); fprintf (stderr, "Thread % x waiting for a new event. /n ", (unsigned int) pthread_self (); connection = listener (current_thread, thread_group); fprintf (stderr," Thread % x get a new event for connection % p. /n ", (unsigned int) pthread_self (), connection); mysql_mutex_lock (& thread_group-> mutex); thread_group-> active_thread_count ++;/* There is no listener anymore, it just returned. */thread_group-> listener = NULL; break ;}...}

The get_event function logic is a bit more. here we only extract the two vertices for the event. we then explain the arrival of the first connection according to the situation where the first connection is coming, a connection exists in the queue. get_event obtains a connection from the queue and returns it to the worker_main thread. Worker_main then calls handle_event to process the event.

After each new connection is connected to the server, its socket is bound to the epoll of the group. Therefore, if there is no connection in the queue, you need to obtain it from the epool, all connected sockets of each group are bound to the group's epool. Therefore, at any time point, only one thread can listen to epoll. if epoll listens to an event, the corresponding connection is returned, and then handle_event is called for processing.

7. event processing -- handle_event

The logic of handle_event is relatively simple, that is, to perform branch operations based on whether the connection_t has been logged on. if the connection is not logged on, it indicates that the connection is a new connection and verification is performed. Otherwise, the request is processed directly.

Static void handle_event (connection_t * connection) {DBUG_ENTER ("handle_event"); int err; if (! Connection-> logged_in) // process the login {err = threadpool_add_connection (connection-> thd); connection-> logged_in = true ;} else // process the request {err = threadpool_process_request (connection-> thd);} if (err) goto end; set_wait_timeout (connection ); /** set the socket-to-epoll listener */err = start_io (connection); end: if (err) connection_abort (connection); DBUG_VOID_RETURN;} static int start_io (connection_t * connection) {int fd = mysql _ Socket_getfd (connection-> thd-> net. vio-> mysql_socket);.../* bind to epoll *. If (! Connection-> bound_to_poll_descriptor) {connection-> response = true; return io_poll_associate_fd (group-> pollfd, fd, connection);} return io_poll_start_read (group-> pollfd, fd, connection );}

Note: After handle_event, start_io will be called. This function is very important. This function will bind the new connection socket to the epoll of the group for listening.

8 threads waiting

When no task is executed in the group thread, all threads will wait at get_event, but there are two waiting methods: one is to wait for the event on epoll, only one thread in each group will do this, and it will wait until a new event arrives. The other is to wait for a certain period of time, that is, the thread_pool_idle_time parameter. if this time is exceeded, the get_event of the current thread will return null, and the worker_main thread will exit. If the thread is awakened during the waiting process, it will continue to loop in get_event and wait for new events.

9 wake up the waiting thread

There are two ways to wake up the waiting thread. one is to monitor the thread timer_thread, and the other is to call tp_wait_begin when some active threads need to wait, if this function determines that there is no active thread and no thread listens to epoll, it will call wake_or_create_thread.

The monitoring thread timer_thread is used to regularly monitor the thread usage in the group. The specific check function is check_stall.

Void check_stall (thread_group_t * thread_group ){... /** if no thread monitors epoll and no new event events have been generated since the last check, it means that all the active threads are busy with executing the tasks, then you need to wake up or create a working thread */if (! Thread_group-> listener &&! Thread_group-> io_event_count) {wake_or_create_thread (thread_group); mysql_mutex_unlock (& thread_group-> mutex); return;}/* Reset io event count */thread_group-> io_event_count = 0; /** if there are requests in the queue and the requests in the queue have not been digested since the last check, all the active threads are busy with the execution of the task, need to wake up or create a working thread */if (! Thread_group-> queue. is_empty ()&&! Thread_group-> queue_event_count) {thread_group-> stalled = true; wake_or_create_thread (thread_group);}/* Reset queue event count */thread_group-> queue_event_count = 0; mysql_mutex_unlock (& thread_group-> mutex );}

10 Summary

The implementation of MariaDB's thread pool is relatively simple. In general, the socket of all connections in the group is hung on the epoll_fd of the group for event listening, and the listening events or events are executed by the current thread, or the queue pushed to the group is executed by other threads.

The monitoring thread timer_thread regularly wakes up the waiting thread or creates a new thread as needed to dynamically add the thread. The thread contraction is completed by waiting for the event timeout.

Btw, in the process of tracking code, also found the use of thread pool caused server crash, submitted a bug to MariaDB, found that there was a reply on the day, and immediately fixed the push to the source tree. it seems that the MariaDB team was quick enough to reflect it.

References

[1]

Thread pool in MariaDB 5.5

[3]

The Thread Pool Plugin

[3]

Thread Pool Worklog

File translated fromTEXby TTH, version 4.03.
On 25 May 2013.

BitsCN.com