Friday, October 30, 2020
dbametrix
More
    Home Database Technology MariaDB: 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.

    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:

    - Advertisement -
    dbametrix

    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):

    - Advertisement -
    dbametrix

    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.

    - Advertisement -
    dbametrix
    Previous articleDataBase Firewall
    Next articleMySQL and virtualization
    - Advertisment -
    dbametrix

    Most Popular

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    oracle 19c new features – Automatic Indexing

    A major Oracle 19c autonomous database new Feature automatic indexing using DBMS_AUTO_INDEX some details and how to article.

    Recent Comments