remote dba support
    HomeDatabase TechnologyMariaDB: thread management

    MariaDB: thread management

    The article describes the characteristics and advantages of the MariaDB Thread management approach

    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.

    - Advertisement -

    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

    - Advertisement -

    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 performed 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 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.

    When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.

    This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

    - Advertisement -
    Previous article
    Next article
    - Advertisment -
    remote dba services

    Most Popular