Thursday, October 23, 2014

Troubleshooting blocking in MariaDB

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 a FLUSH TABLES WITH READ LOCK and then 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 a very high value, but this might be less than ideal, and negatively affect performance.

To workaround such a situation, MariaDB permits you to use a dedicated admin connection. To allow a dedicated admin connection, set the extra_port server variable to the TCP port for this connection (this must be different from the usual port), and connect using this port as a 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 the global lock (such a connection would be in the 'sleep' state).

No comments: