MariaDB: thread management
In MySQL, a thread is created for each client connected to the server: this method is called One Thread Per Connection. All SQL statements launched from a given connection are then handled by the same thread. In the context of websites, where MySQL is most used, this solution is not optimal. In fact, the instructions, for the majority, are insertions, changes or deletions of a single record, or very simple SELECT, which often extract a single line. Such instructions could be executed concurrently (simultaneously) by several threads. In addition, an open thread, in the interval between one request and another, remains unused, unnecessarily wasting resources that could be used to meet the needs of other clients.
That’s why in MariaDB there is a new method for managing threads, called Pool Of Threads. It provides a fixed number of open threads that divide the SQL statements to be executed, regardless of which client sent them, in order to optimize performance.
How to Enable the Pool Of Threads:
Also in MariaDB, the way to manage threads used by default is One Thread Per Connection. To use Pool Of Threads you must use an uncompiled version of the program and, when executing configure, specify the –with-libevents parameter.
Even so, the default method will be One Thread Per Connection. To launch the mysqld server using Pool Of Threads, the following parameter is used:
mysqld –thread-handling = pool-of-threads
If you explicitly want to indicate as a One Thread Per Connection method (not necessary):
mysqld –thread-handling = one-thread-per-connection
Specify the size of the pool:
It is also possible to specify how many threads must be present in the pool. The default is 20. You can change it using the following parameter:
mysqld --thread-handling = pool-of-threads --thread-pool-size = 15
The optimal value depends on the characteristics of the applications that connect to the database and its search may require several tests.
The extra door:
What would happen if all threads were waiting for a LOCK to be released, or if they were executing a very long instruction? In this case it would not be possible to establish a new connection until a thread is released; therefore it would not even be possible to try to solve the problem with the SHOW PROCESSLIST and KILL commands.
To allow for troubleshooting, if the server is launched with the Pool Of Threads method, there is an extra port to which you can connect with the old One Thread Per Connection method.
By default this port is the number 0. To modify this value:
mysqld --thread-handling = pool-of-threads --extra-port = 2000
By default, only one connection is accepted on the extra port, because it is sufficient for troubleshooting. However, it is possible to tell the server to accept multiple connections:
mysqld --thread-handling = pool-of-threads --extra-port = 2000 --extra-max-connections = 3
In this way it will be possible to make one or more connections with the One Thread Per Connection method, to perform operations that are more performing in this mode; however, you must always take care to leave a connection always free for troubleshooting.
We are not advocating mariaDB but this is just for a knowledge example. Dbametrix is providing only remote Database Administration support for Oracle and SQL database servers including cloud servers for an instance DBaaS DBA support. Stay connected with our Expert DBA team club blog for more knowledge gain articles on database from this blog or from here.