Pool di Thread in MariaDB 5.5

Il problema che il Pool di Thread risolve

L'obiettivo di avere un server software scalabile (e un DBMS è un esempio di tali software) comporta mantenere le migliori performance quando il numero di client aumenta. MySQL tradizionalmente assegnava un thread ad ogni connessione da parte dei client, così con l'aumentare degli utenti concorrenti le performance calavano. Molti thread attivi sembrano essere un grosso problema per le performance, perché l'aumento del numero di connessioni comporta un context switching (cambiamento di contesto) sempre più intensivo e un peggior incapsulamento delle cache della CPU, mentre i lock aumentano. Perciò la soluzione ideale che aiuta a ridurre il cambiamento di contesto è mantenere il numero dei thread inferiore alla quantità di client, anche se questa quantità non deve essere troppo bassa, perché al contempo è desiderabile usare le CPU al meglio - idealmente, c'è un singolo thread attivo per ogni CPU di cui la macchina è dotata.

Cosa c'è di nuovo nel Pool di Thread di MariaDB 5.5

MariaDB era in grado di eseguire i thread in un pool anche nella versione 5.1. Lo svantaggio principale della precedente soluzione era che il pool era statico, cioè il numero di thread era fisso. I pool di thread statici hanno dei meriti, perché vi è un limitato numero di casi in cui le callback eseguite dal pool non si bloccano mai reciprocamente e non dipendono l'una dall'altra (si immagini qualcosa come un server echo). Ma i client dei DBMS sono più sofisticati, perché le azioni eseguite da uno devono attendere il completamento delle operazioni di un altro, si bloccano con diversi lock e con l'I/O, pertanto è impossibile o molto difficile predire quale sia il numero ideale (o anche solo sufficiente) di thread per prevenire i deadlock. La versione 5.5 implementa un pool dinamico/adattivo che si occupa da solo della creazione di nuovi thread quando la domanda aumenta, e di ritirarli quando non hanno niente da fare. Si tratta di una re-implementazione completa del vecchio pool-of-threads, che si pone i seguenti obiettivi:

• Rendere il pool dinamico (cresce e diminuisce al bisogno)
• Minimizzare il carico di lavoro per mantenere il pool stesso
• Ottimizzare l'uso delle capacità dell'OS (usando il threadpool nativo se esiste, altrimenti usando il multiplexing dell'IO)
• Limitare l'uso delle risorse da parte dei thread (thread_pool_max_threads)

Attualmente vi sono due differenti implementazioni di basso livello, che dipendono dall'OS – una per Windows, che usa il threadpool nativo, l'altra per i sistemi Unix-like. Per questa ragione, alcuni parametri di ottimizzazione differiscono da Windows a Unix.

Quando usare il threadpool

I threadpool sono più efficienti in quelle situazioni in cui le query sono relativamente brevi e il carico è legato soprattutto alla CPU (OLTP). Se il carico di lavoro non è sulla CPU, si potrebbe preferire limitare il numero di thread per risparmiare memoria per i buffer del database.

When threadpool is less efficient

There are special cases where threadpool is likely to be less efficient.

• very bursty workload (long periods of inactivity mixed with short periods of high activity by many users), and also you cannot tolerate delays coming from thread creation throttling. Even in this situation, performance can be improved by tweaking the way threads are retired, e.g. thread_pool_idle_timeout on Unix, or thread_pool_min_threads on Windows.

• many concurrent long, non-yielding queries. Non-yielding here means that thread never waits while executing query, or does not indicate waits to threadpool. Such workloads are mostly used in data warehouse scenarios. In this case long running queries will delay execution of other queries, and the delay is fixed only with stall detection/preemption mechanism (s. description thread_pool_stall_limit). You can still connect trough the extra-port TCP/IP port.

• you rely on the fact that simple queries always finish quickly, no matter how loaded you database server is. With loaded threadpool, queries might be queued for later execution, such that even a simple SELECT 1, might take more time than with thread-per-connection.

Using threadpool scheduler

The options for the variable/startup option thread-handling are:

On Unix, add thread_handling=pool-of-threads to the my.cnf config file.

On Windows, the default for thread_handling is already preset to pool-of-threads, so nothing needs to be done here. If you want to use one thread per connection add thread_handling=one-thread-per-connection to the my.cnf config file.

Threadpool server variables

Generally there should be no need to tweak the parameters, since the goal was to provide good performance out-of-the box. We encourage submitting bug reports, if defaults do not appear to work well in your specific situation. Still, the parameters are not hardcoded and we expose as much as underlying implementation allows, so you can tweak them as you see fit. All server variables documented below are dynamic, i.e can be changed on the running server.

Optimizing server variables on Windows

The native threadpool implementation allows setting minimum and maximum number of threads in the pool. Thus following variables are exposed:

• thread_pool_min_threads - minimum number of threads in the pool. Default is 1. This applicable in a special case of very “bursty” workloads. Imagine having longer periods of inactivity after periods of high activity. While threadpool is idle, Windows would decide to retire pool threads (based on experimentation, this seems to happen after thread had been idle for 1 minute). Next time high load comes, it could take some milliseconds or seconds until threadpool size stabilizes again to optimal value. To avoid thread retirement, one could set the parameter to a higher value.
• thread_pool_max_threads – maximum number of threads in the pool. Threads are not created when this value is reached. Default is 500. This parameter can be used to prevent creation of new threads if the pool can have short periods where many or all clients are blocked (for example, with “FLUSH TABLES WITH READ LOCK”, high contention on row locks or similar). Pool does create new threads if blocking situation occur (after some throttling interval), but sometimes you’ll want to cap the number of threads, if you’re familiar with the application, e.g to save memory. If your application constantly pegs at 500 threads, it might be a strong indicator for high contention in the application, and threadpool does not help much.

Optimizing server variables on Unix

Following variables are exposed by underlying implementation

• thread_pool_size – number of thread groups. Default is number of processors. This is the parameter with the most visible performance effect. It is roughly equivalent to number of threads that can run at the same time (where run means use CPU, rather than sleep or wait). The implementation partitions all clients into groups, with the goal of having one runnable thread per group. One reason to change it to a lower value could be running the server on dedicated processors (e.g. with taskset utility on Linux). Increase the value if you find that despite your workload is CPU-bound, CPUs are still underutilized.

• thread_pool_stall_limit – number of milliseconds before a running thread is considered stalled. Default is 500. Threadpool will wake up or create another thread if this limit is reached. This is the preemption mechanism that prevents long-running query from monopolizing the pool, and temporarily allowing several queries to run in parallel.
• thread_pool_max_threads – maximum number of threads in the pool. New threads are not created when this value is reached. Default is 500. The variable has the same meaning as on Windows.
• thread_pool_idle_timeout- number of seconds before an idle worker thread exits. Default is 60. If there is currently no work to do, how long should an idle thread wait before exiting?
• thread_pool_oversubscribe – internal parameter. Change at your own risk. Default is 3. Here is a approximate description of the behavior : there is a tradeoff between letting more than 1 runnable thread per CPU versus putting thread into sleep and awake almost instantly after it. The higher the value for this parameter, the more threads could run at the same time. Lower value can lead to more sleeps and wakeups.

Monitoring pool activity

Currently there are two status variables exposed to monitor pool activity

Troubleshooting blocking situations

Even with thread_pool_max_threads set high (recall the default for it is 500), if global locks are involved, it is possible to block the entire pool. Imagine a situation where one client performs FLUSH TABLES WITH READ LOCK and pauses. If 500 other clients start a write operation, the maximum number of threads allowed in the pool is reached and then it is impossible to UNLOCK TABLES anymore. One way to solve the problem would be to set thread_pool_max_threads to an very high value, but this might be less than ideal, and negatively affect performance.

To workaround such situation, MariaDB permits you to use a dedicated admin connection. To allow dedicated admin connection, set extra_port server variable to the TCP port for this connection (this must be different from usual port), and connect using this port as superuser. Once connected, you can either increase thread_pool_max_threads, or kill the offending connection - in the example above that would be the connection that holds global lock (such connection would be in the 'sleep' state).

MariaDB threadpool vs Oracle MySQL Enterprise Threadpool

Commercial editions of MySQL since 5.5 include Oracle MySQL Enterprise threadpool implemented as a plugin, which delivers similar functionality. Official documentation of the feature can be found in the Reference Manual and the detailed discussion about design of the feature in Mikael Ronstrom's blog. Here is the summary of similarities and differences, based on above materials.

Similarities

• On Unix, both MariaDB and Oracle MySQL Enterprise Threadpool will partition client connections into groups. thread_pool_size parameter thus has the same meaning in for both MySQL and MariaDB.

• Also, both implementations use similar schema checking for thread stalls, and both have the same parameter name for thread_pool_stall_limit (though in MariaDB it is measured using millisecond units, not 10ms units like in Oracle MySQL).

Differences

• Windows implementation is completely different - MariaDB's is using native Windows threadpooling, while Oracle's implementation convenience function WSAPoll() (provided for convenience to port Unix application). As a consequence of relying on WSAPoll(), Oracle's implementation will not work with named pipes and shared memory connections.
• MariaDB is using the most efficient IO multiplexing facilities for all of the OS Windows (IO completion port is used internally by the native threadpool), Linux (epoll), Solaris (event ports),FreeBSD and OSX (kevent). Oracle uses optimized IO multiplexing only on Linux, with epoll, and uses poll() otherwise.
• Unlike Oracle MySQL Enterprise Threadpool, MariaDB's threadpool is not trying minimize number of concurrent transactions.
• Unlike Oracle MySQL Enterprise Threadpool, MariaDB's one is builtin, not a plugin.

Threadpool internals

Low-level implementation details are documented in the WL#246