Shulou(Shulou.com)05/31 Report--

This article is mainly about "thread_running quantitative Analysis of mysql". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the thread_running quantitative Analysis of mysql.

The principle of thread pool:

Parsing has been completed in server layer

Layer creates sets of resident threads that receive query sent by client connections and execute them instead of creating a separate thread for each connection.

The layer judges the number of running thread, and if it reaches the threshold, it directly reports an error or sleep.

The state variable records the number of stmt/command currently executed concurrently. Add 1 before execution and subtract 1 after execution.

The cause of a sudden surge:

A surge in client connections

System performance bottleneck, such as CPU,IO or mem swap

Abnormal sql

Will show the illusion that hang lives.

To execute, introduce two thresholds, low_watermark and high_watermark, and the variable threads_running_ctl_mode (selects or all)

Before, check the thread_running

If it has reached the high_watermark threshold, it directly refuses to execute and returns an error: mysql server is too busy

If it is between low and high, then sleep 5ms, then continue to try and wait for 100ms to execute

No restrictions are imposed on transactions and super users that have been enabled

Control query type: SELECTS/ALL. Default is SELECTS, which means that only SELECT statements are affected.

For the source code, please see Note 1.

Optimized to FIFO-based cond-wait/signal (implementing 8 FIFO)

Limit flow at high water level (this remains the same)

Low water level optimization; other solutions: mariadb developer thread pool,percona implements priority queues on it

Advantages: the idea is the same as that of thread pool, but the code is more concise (less than 1000 lines); and filtering for specific query has been added

For the code, see Note 2.

New thread_active records the number of concurrent threads, which is located in mysql_execute_command (after sql parsing), while high water level is judged before query parsing.

Only count select/DML, while commit/rollback let it go.

Using FIFO, when thread_active > = thread_running_low_watermark, the process enters FIFO to wait, and other threads wake up FIFO after executing sql

At the same time, threads_running_wait_timeout is introduced to control the maximum waiting time of the thread in FIFO, and the timeout will directly report an error and return.

Eight FIFO are introduced to reduce lock contention in and out of FIFO. Threads are assigned to different fifo using RR, and each queue limits concurrent running threads to threads_running_low_watermark/8.

Start the execution of query, [determine the low water level after parsing, and execute if passed]. After the execution of the current sql, thread may initiate a new query, then repeat the [] process.

: longest queue time when entering FIFO. Sql is rejected after waiting for timeout. Default is 100. unit is millisecond ms.

Current number of threads executing concurrently with SELECT/INSERT/UPDATE/DELETE

The number of threads currently waiting in the FIFO

Unpatched version, set innodb_thread_concurrency=0

Unpatched version, innodb_thread_concurrency=32

Low water level current limit patch version (the number of active threads does not exceed 64)

Note 1

Http://www.gpfeng.com/wp-content/uploads/2013/09/threads_running_control.txt

+ static my_bool thread_running_control (THD * thd, ulong tr)

+ {

+ int slept_cnt= 0

+ ulong tr_low, tr_high

+ DBUG_ENTER ("thread_running_control")

+

+ / *

+ Super user/slave thread will not be affected at any time

+ transactions that have already started will continue.

+ * /

+ if (thd- > security_ctx- > master_access & SUPER_ACL | |-- No restrictions are imposed on users with super permissions and transactions that have been opened

+ thd- > in_active_multi_stmt_transaction () | |

+ thd- > slave_thread)

+ DBUG_RETURN (FALSE)

+

+ / *

+ To promise that tr_low will never be greater than tr_high

+ as values may be changed between these two statements.

+ eg.

+ (low, high) = (200500)

+ 1. Read low = 200

+ 2. Other sessions: set low = 20; set high = 80

+ 3. Read high = 80

+ Don't take a lock here to avoid lock contention.

+ * /

+ do

+ {

+ tr_low= thread_running_low_watermark

+ tr_high= thread_running_high_watermark

+

+} while (tr_low > tr_high)

+

+ check_buzy:

+

+ / * tr_high is promised to be non-zero.*/

+ if ((tr_low = = 0 & & tr

< tr_high) || (tr_low != 0 && tr < tr_low)) + DBUG_RETURN(FALSE); + + if (tr >

= tr_high)

+ {

+ int can_reject= 1

+

+ / * thread_running_ctl_mode: 0-> SELECTS, 1-> ALL. , /

+ if (thread_running_ctl_mode = = 0)

+ {

+ int query_is_select= 0

+ if (thd- > query_length () > = 8)

+ {

+ char * p = thd- > query ();-- read the first six characters of query text to determine whether it is select

+ if (my_toupper (system_charset_info, p [0]) = ='S' & &

+ my_toupper (system_charset_info, p [1]) = ='E' & &

+ my_toupper (system_charset_info, p [2]) = ='L'& &

+ my_toupper (system_charset_info, p [3]) = ='E' & &

+ my_toupper (system_charset_info, p [4]) ='C'& &

+ my_toupper (system_charset_info, p [5]) = ='T')

+

+ query_is_select= 1

+}

+

+ if (! query_is_select)

+ can_reject= 0

+}

+

+ if (can_reject)

+ {

+ inc_thread_rejected ()

+ DBUG_RETURN (TRUE)

+}

+ else

+ DBUG_RETURN (FALSE)

+}

+

+ if (tr_low! = 0 & & tr > = tr_low)

+ {

+ / *

+ If total slept time exceed 100ms and thread running does not

+ reach high watermark, let it in.

+ * /

+ if (slept_cnt > = 20)

+ DBUG_RETURN (FALSE)

+

+ dec_thread_running ()

+

+ / * wait for 5ms. , /

+ my_sleep (5000UL)

+

+ slept_cnt++

+ tr= inc_thread_running ()-1

+

+ goto check_buzy

+}

+

+ DBUG_RETURN (FALSE)

+}

+

+ / * *

Perform one connection-level (COM_XXXX) command.

@ param command type of command to perform

@ @-1016Pol 7 + 1126Pol 8 @ @

Thd- > set_query_id (get_query_id ())

If (! (server_command_ commands [command] & CF_SKIP_QUERY_ID))

Next_query_id ()

-inc_thread_running ()

+ / * remember old value of thread_running for * thread_running_control*. , /

+ int32 tr= inc_thread_running ()-1

If (! (server_command_ commands [command] & CF_SKIP_QUESTIONS))

Statistic_increment (thd- > status_var.questions, & LOCK_status)

@ @-1129, 6 + 1240, 13 @ @

{

If (alloc_query (thd, packet, packet_length))

Break; / / fatal error is set

+

+ if (thread_running_control (thd, (ulong) tr))

+ {

+ my_error (ER_SERVER_THREAD_RUNNING_TOO_HIGH, MYF (0))

+ break

+}

+

MYSQL_QUERY_START (thd- > query (), thd- > thread_id, (char *) (thd- > db? Thd- > db: ""), & thd- > security_ctx- > priv_user [0])

Note 2

Http://www.gpfeng.com/wp-content/uploads/2014/01/tr-control.diff_.txt

+ / * *

Perform one connection-level (COM_XXXX) command.

@ param command type of command to perform

@ @-1177pc7 + 1401pd7 @ @

Command= COM_SHUTDOWN

}

Thd- > set_query_id (next_query_id ())

-inc_thread_running ()

+ int32 tr= inc_thread_running ()

If (! (server_command_ commands [command] & CF_SKIP_QUESTIONS))

Statistic_increment (thd- > status_var.questions, & LOCK_status)

@ @-1209, 6 + 143, 15 @ @

Goto done

}

+ if (command = = COM_QUERY & & alloc_query (thd, packet, packet_length)

+ goto endof_case; / / fatal error is set

+

+ if (thread_running_control_high (thd, tr))

+ {

+ my_error (ER_SERVER_THREAD_RUNNING_TOO_HIGH, MYF (0))

+ goto endof_case

+}

+

Switch (command) {

Case COM_INIT_DB:

{

@ @-1311pr 8 + 154pr 6 @ @

}

Case COM_QUERY:

{

-if (alloc_query (thd, packet, packet_length))

-break; / / fatal error is set

MYSQL_QUERY_START (thd- > query (), thd- > thread_id

(char *) (thd- > db? Thd- > db: "")

& thd- > security_ctx- > priv_user [0]

@ @-1751 @ 6 + 1982 7 @ @

My_message (ER_UNKNOWN_COM_ERROR, ER (ER_UNKNOWN_COM_ERROR), MYF (0))

Break

}

+ endof_case:

Done:

DBUG_ASSERT (thd- > derived_tables = = NULL & &

@ @-2502, 12 + 273, 4, 37 @ @

Opt_trace_array trace_command_steps (& thd- > opt_trace, "steps")

DBUG_ASSERT (thd- > transaction.stmt.cannot_safely_rollback () = = FALSE)

+ bool count_active= false

If (need_traffic_control (thd, lex- > sql_command))

{

Thd- > killed = THD::KILL_QUERY

Goto error

}

+

+ switch (lex- > sql_command) {

+

+ case SQLCOM_SELECT:

+ case SQLCOM_UPDATE:

+ case SQLCOM_UPDATE_MULTI:

+ case SQLCOM_DELETE:

+ case SQLCOM_DELETE_MULTI:

+ case SQLCOM_INSERT:

+ case SQLCOM_INSERT_SELECT:

+ case SQLCOM_REPLACE:

+ case SQLCOM_REPLACE_SELECT:

+ count_active= true

+ break

+ default:

+ break

+}

+

+ if (count_active & & thread_running_control_low_enter (thd))

+ {

+ my_error (ER_SERVER_THREAD_RUNNING_TOO_HIGH, myf (0))

+ goto error

+}

+

Status_var_increment (thd- > status_var.com_ stat [Lex-> sql_command])

Switch (gtid_pre_statement_checks (thd))

@ @-4990BEI 6 + 5247BI 9 @ @

Finish:

+ if (count_active)

+ thread_running_control_low_exit (thd)

+

DBUG_ASSERT (! thd- > in_active_multi_stmt_transaction () | |

Thd- > in_multi_stmt_transaction_mode ()

+ static my_bool thread_running_control_high (THD * thd, int32 tr)

+ {

+ int32 tr_high

+ DBUG_ENTER ("thread_running_control_high")

+

+ tr_high= (int32) thread_running_high_watermark

+

+ / * thread_running_ctl_mode: 0-> SELECTS, 1-> ALL. , /

+ if (! tr_high | | tr transaction.is_active () | |

+ thd- > get_command ()! = COM_QUERY | |

+ thd- > security_ctx- > master_access & SUPER_ACL | |

+ thd- > slave_thread)

+ DBUG_RETURN (FALSE)

+

+ const char * query= thd- > query ()

+ uint32 len= thd- > query_length ()

+

+ if ((! has_prefix (query, len, "SELECT", 6) & & thread_running_ctl_mode = = 0) |-- No longer judge on a character-by-character basis

+ has_prefix (query, len, "COMMIT", 6) | |

+ has_prefix (query, len, "ROLLBACK", 8)

+ DBUG_RETURN (FALSE)

+

+ / * confirm again*/

+ if (tr > tr_high & & get_thread_running () > tr_high)

+ {

+ _ sync_add_and_fetch (& thread_rejected, 1)

+ DBUG_RETURN (TRUE)

+}

+

+ DBUG_RETURN (FALSE)

+}

+

+ static my_bool thread_running_control_low_enter (THD * thd)

+ {

+ int res= 0

+ int32 tr_low

+ my_bool ret= FALSE

+ my_bool slept= FALSE

+ struct timespec timeout

+ Thread_conc_queue * queue

+ DBUG_ENTER ("thread_running_control_low_enter")

+

+ / * update global status * /

+ _ sync_add_and_fetch (& thread_active, 1)

+

+ tr_low= (int32) queue_tr_low_watermark

+ queue= thread_conc_queues + thd- > query_id N_THREAD_CONC_QUEUE

+

+ queue- > lock ();-- Q1: lock the FIFO before judging the low water level to avoid failing to acquire the FIFO lock and then putting it into the FIFO when the low water level verification fails.

+

+ retry:

+

+ if (! tr_low | | queue- > thread_active

< tr_low) || + (thd->

Lex- > sql_command! = SQLCOM_SELECT & & thread_running_ctl_mode = = 0) | |

+ (! slept & & (thd- > transaction.is_active ()) | |

+ thd- > security_ctx- > master_access & SUPER_ACL | | thd- > slave_thread)

+ {

+ queue- > thread_active++;-determine whether the conditions for entering FIFO are met. If not, update thread_active++, immediately to unlock queue and exit.

+ queue- > unlock ()

+ DBUG_RETURN (ret)

+}

+

+ if (! slept)

+ {

+ queue- > unlock ()

+

+ / * sleep for 500 us * /

+ my_sleep (500)

+ slept= TRUE

+ queue- > lock ()

+

+ goto retry

+}

+

+ / * get a free wait-slot * /

+ Thread_wait_slot * slot= queue- > pop_free ()

+

+ / * can't find a free wait slot, must let the query enter * /

+ if (! slot)-when the FIFO is full, that is, the current thread cannot be put in, the sql must be released for normal execution

+ {

+ queue- > thread_active++

+ queue- > unlock ()

+ DBUG_RETURN (ret)

+}

+

+ slot- > signaled= false

+ slot- > wait_ended= false

+

+ / * put slot into waiting queue. , /

+ queue- > push_back_wait (slot)

+ queue- > thread_wait++

+

+ queue- > unlock ()

+

+ / * update global status * /

+ thd_proc_info (thd, "waiting in server fifo")

+ _ sync_sub_and_fetch (& thread_active, 1)

+ _ sync_add_and_fetch (& thread_wait, 1)

+

+ / * cond-wait for at most thread_running_wait_timeout (ms) , /

+ set_timespec_nsec (timeout, thread_running_wait_timeout_ns)

+

+ mysql_mutex_lock (& slot- > mutex)

+ while (! slot- > signaled)

+ {

+ res= mysql_cond_timedwait (& slot- > cond, & slot- > mutex, & timeout)

+ / * no need to signal if cond-wait timedout * /

+ slot- > signaled= true

+}

+ mysql_mutex_unlock (& slot- > mutex)

+

+ queue- > lock ()

+ queue- > thread_wait--

+ queue- > thread_active++

+

+ / * remove slot from waiting queue. , /

+ queue- > remove_wait (slot)

+ / * put slot into the free queue for reuse. , /

+ queue- > push_back_free (slot)

+

+ queue- > unlock ()

+

+ / * update global status * /

+ _ sync_sub_and_fetch (& thread_wait, 1)

+ _ sync_add_and_fetch (& thread_active, 1)

+ thd_proc_info (thd, 0)

+

+ if (res = = ETIMEDOUT | | res = = ETIME)

+ {

+ ret= TRUE; / / indicate that query is rejected.

+ _ sync_add_and_fetch (& thread_rejected, 1)

+}

+

+ DBUG_RETURN (ret)

+}

At this point, I believe you have a deeper understanding of "mysql's thread_running quantity analysis". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.