MySQL database thread buffer pool details

The knowledge about the MySQL database thread buffer pool is what we will introduce in this article. MySQL database supports thread caching. In multi-threaded connection mode, if the connection is closed, put this thread in the idle thread buffer. When there is a connection next time, go to the buffer pool to check whether there are Idle threads. If yes, use it. If no thread exists, create it. You can set the number of thread buffer pools at startup: Mysqld.exe -- thread_cache_size = 10.

When a connection is disconnected, the cache_thread function is called to add Idle threads to the cache for later use. The source code is as follows:

 

The following is a code snippet: Static bool cache_thread () { Safe_mutex_assert_owner (& LOCK_thread_count ); If ( Cached_thread_count <thread_cache_size && ! Abort_loop &&! Kill_cached_threads) { /* Don't kill the thread, just put it in cache for reuse */ DBUG_PRINT ("info", ("Adding thread to cache ")); Cached_thread_count ++; While (! Abort_loop &&! Wake_thread &&! Kill_cached_threads) (Void) pthread_cond_wait (& COND_thread_cache, & LOCK_thread_count ); Cached_thread_count --; If (kill_cached_threads) Pthread_cond_signal (& COND_flush_thread_cache ); If (wake_thread) { THD * thd; Wake_thread --; Thd = thread_cache.get (); Thd-> thread_stack = (char *) & thd; // For store_globals (Void) thd-> store_globals (); /* THD: mysys_var: abort is associated with physical thread rather Than with THD object. So we need to reset this flag before using This thread for handling of new THD object/connection. */ Thd-> mysys_var-> abort = 0; Thd-> thr_create_utime = my_micro_time (); Threads. append (thd ); Return (1 ); } } Return (0 ); }

The preceding startup parameter sets the thread buffer to 10. At this time, the corresponding code contains the thread_cache_size = 10 and cached_thread_count records.

The number of Idle threads in the cache at this moment. New Idle threads will be added to the buffer pool only when the cache is not full. Adding a line to the buffer is actually

 

The pthread_cond_wait function is the thread wait function. In this function, WaitForMultipleObjects is called to wait for the event. Source code

As follows:

 

The following is a code snippet: Int pthread_cond_timedwait (pthread_cond_t * cond, pthread_mutex_t * mutex, Struct timespec * abstime) Int result; Long timeout; Union ft64 now; If (abstime! = NULL) { GetSystemTimeAsFileTime (& now. ft ); /* Calculate time left to abstime -Subtract start time from current time (values are in 100ns units) -Convert to millisec by dividing with 10000 */ Timeout = (long) (abstime-> TV. i64-now. i64)/10000 ); /* Don't allow the timeout to be negative */ If (timeout <0) Timeout = 0L; /* Make sure the calucated timeout does not exceed original timeout Value which cocould cause "wait for ever" if system time changes */ If (timeout> abstime-> max_timeout_msec) Timeout = abstime-> max_timeout_msec; } Else { /* No time specified; don't expire */ Timeout = INFINITE; } /* Block access if previous broadcast hasn' t finished. This is just for safety and shoshould normally not Affect the total time spent in this function. */ WaitForSingleObject (cond-> broadcast_block_event, INFINITE ); EnterCriticalSection (& cond-> lock_waiting ); Cond-> waiting ++; LeaveCriticalSection (& cond-> lock_waiting ); LeaveCriticalSection (mutex ); Result = WaitForMultipleObjects (2, cond-> events, FALSE, timeout ); EnterCriticalSection (& cond-> lock_waiting ); Cond-> waiting --; If (cond-> waiting = 0) { /* We're re the last waiter to be notified or to stop waiting, so Reset the manual event. */ /* Close broadcast gate */ ResetEvent (cond-> events [BROADCAST]); /* Open block gate */ SetEvent (cond-> broadcast_block_event ); } LeaveCriticalSection (& cond-> lock_waiting ); EnterCriticalSection (mutex ); Return result = WAIT_TIMEOUT? ETIMEDOUT: 0; }

Here is the waiting time. Where can we notify you of the event? We will go to the Code mentioned in the previous article to create a thread for the new connection:

 

The following is a code snippet: Void create_thread_to_handle_connection (THD * thd) { If (cached_thread_count> wake_thread) { /* Get thread from cache */ Thread_cache.append (thd ); Wake_thread ++; Pthread_cond_signal (& COND_thread_cache ); } Else ... }

 

This article introduces the knowledge about the MySQL database thread buffer pool. I hope this introduction will be helpful to you!