Friday, August 14, 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.

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

    Most Popular

    ORA-01940: Cannot drop a user that is currently connected

    Detail explanation and solution of ORA-01940: Cannot drop a user that is currently connected error.

    Ubuntu and Oracle SQL Developer

    Blog post explains how to install and use of Oracle SQL Developer on Ubuntu linux server

    Create Schema in Oracle 18c XE

    Article explain how to create new user in Oracle 18c XE and grant some system privileges with connect as new schema user in database.

    Oracle Autonomous JSON database

    The blog article explains about What is the Oracle autonomous JSON database, usage and how to get it from Oracle Cloud.

    Recent Comments

    Margareta Combes on nosql Security Vulnerabilities
    Gordon Coleclough on Oracle Tuning Basics Part-1
    Gordon Coleclough on ASM Disk creation issue