MARIADB Thread Pool VS Oracle MySQL Enterprise

Oracle MySQL Enterprise Section

Thread_pool_algorithm:

Connection concurrency scheduling algorithm, the default value of 0 using a conservative low-level concurrency algorithm, tested performance results are good. A value of 1, the concurrent number will increase, the use of more aggressive algorithm performance, the number of threads in a certain time performance is 5-10%, with a larger number of connections, performance will decrease.

Thread_pool_high_priority_connection:

This parameter affects how the statement is executed in the order that 0,statement uses the low priority and high-priority two queues, which, if equal to 1, will only use a high priority queue.

Thread_pool_prio_kickup_timer:

Statement the wait time to move from the low priority queue to the high-priority. Unit is milliseconds

Thread_pool_max_unused_threads

This parameter restricts the memory used by the sleep thread. The default value is 0, that is, no limit, when N (n>1), 1 consumer thread, n-1 reserve threads. When thread at sleeping reaches its maximum value, and a new thread is about to sleep, the thread can only exit directly.

A sleeping thread has two roles consumer and reserve, thread pool allows only one thread to be consumer thread, and if a thread is going to sleep and there is no co in the thread pool Nsumer the thread of the role, that thread becomes consumer thread; When a thread needs to be awakened, consumer thread is preferred and the reserve role thread is selected only if the thread of the role consumer thread does not exist

Number of Thread_pool_size:thread groups

Thread_pool_stall_limit:thread executes the time interval for the next new statement.

Parameter recommendation configuration:

Thread_pool_size A read-only variable,

The main storage engine is: InnoDB, with a value of---36 the best value is---36 for write-intensive applications, sometimes less than 36.

The main storage engine is: MyISAM: Best 4-8, set too high to have no significant impact on performance.

Thread_pool_stall_limit: It has a great effect on Long-running statement and blocked statements. In the case of blocked, if the thread pool detects a new thread that is not detected for the thread pool, the timeout time can only be set by this parameter.

If the value is too high, there will be long-running statement blocking more short queries.

Example:

When a statement arrives, what are the maximum time it can be delayed before it actually starts? Suppose that the following conditions apply:

There are statements queued in the low-priority queue.

There are statements queued in the high-priority queue.

Thread_pool_prio_kickup_timer is set to 10000 (seconds).

Thread_pool_stall_limit is set to (1 second).

In the worst case, the ten High-priority statements represent transactions that continue-for-a long time. T Hus, in the worst case, no statements would be moved to the high-priority queue because it would always already contain Ements awaiting execution. After ten seconds, the new statement is eligible to being moved to the high-priority queue. However, before it can be moved and all the statements before it must is moved as. This could take another 2 seconds because a maximum the statements per second are to the moved queue. Now, the statement reaches the high-priority queue, there could potentially is many long-running statements ahead of I T. In the worst case, every one of those would become stalled and it'll take 1 second for each statement the next Statement is retrieved from the high-priority queue. Thus, in this scenario, it would take 222 seconds before the new statement starts.

This is example shows a worst case for a application. How to handle it depends on the application. If the application has requirements for the response time, it should most likely users at a throttle level it Elf. Otherwise, it can use the thread pool configuration parameters to set some kind of maximum.