Welcome to Centmin Mod Community
Become a Member

Multithreading performance configuration

Discussion in 'MariaDB & General MySQL news & discussions' started by zyzzx, Sep 25, 2019.

  1. zyzzx

    zyzzx New Member

    2
    0
    1
    Sep 25, 2019
    Ratings:
    +0
    Local Time:
    8:34 AM
    10.0.34
    I have a Linux C application that makes use of the services of a MariaDB 10.0.34 server, also running under Linux. The application launches individual threads to do operations against the MariaDB server. My setup is the following:

    1. Using the MariaDB C API, each thread opens a separate connection to the server, carries out its operations, and then closes the connection when done.
    2. Each thread operates on the same database and on the same table.
    3. There are no thread locks in the code run in the threads - the MariaDB server is assumed to manage the simultaneous connections.
    4. The probability for each thread to be operating on the same element in table at the same time is negligible.
    5. The application and the MariaDB server run in the same system.
    6. The system where this is all running is a multicore one.


    The problem that I am facing is that the performance of this setup is very poor. The application (which is driven by external events the nature of which is not relevant to this discussion) launches its threads to interact with the MariaDB server. These threads seem to be running sequentially, rather than taking advantage of the many cores available in the system. In essence, the threads keep accumulating, because they seem to be created faster than they can complete. While each thread, while running on its own, should terminate in a matter of a few seconds, once they start accumulating each may make take many minutes to complete - they seem to be competing with each other for the MariaDB server's resources: there doesn't seem to be any parallelism involved, despite the fact that the system has plenty of resources for the job.

    I would like to make sure that my MariaDB server is configured adequately for the scenario I described above - which it is probably not, for I am a newbie to MariaDB. my /etc/my.cnf file includes all the files under /etc/my.cnf.d. One of them is named server.cnf, and it includes the following lines:

    [mariadb]
    thread_handling=pool-of-threads

    That's pretty much it, as far as my MariaDB server configuration is concerned.

    What I am asking for is feedback concerning how to configure my MariaDB server to address my scenario. The system is by no means stressed - when running, with tens of threads executing simultaneously, the system load never reaches 2.0, and most of the 16 cores available are idle most of the time. The MariaDB server, however, does not seem to be able to cope.
     
  2. eva2000

    eva2000 Administrator Staff Member

    54,389
    12,198
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,763
    Local Time:
    1:34 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    What cpu model and server hardware are you using ? output from
    Code (Text):
    lscpu
    cat /proc/cpuinfo
    

    or better yet on Centmin Mod LEMP stack run cminfo top stats command to get all server statistics for cpu, memory, disk, mysql etc
    Code (Text):
    cminfo top

    Centmin Mod is provide as is, so short of scripted related bugs or issues, any further optimisation to the web stack components - nginx, php-fpm, mariadb mysql, csf firewall etc or web app specific configurations are left to the Centmin Mod user to deal with. So I do not provide any free support for such.

    However, Centmin Mod users are free to help each other out and ask questions or give answers on this community forum. My hopes are that this community forum evolves so that more veteran long time Centmin Mod users help new Centmin Mod users out :)

    With that said though
    you need to properly profile such usage loads to see where the bottleneck is, but as stated you're on your own for that. Centmin Mod is provided as is. And if you're not using Centmin Mod LEMP stack, then this question would be better asked on MySQL/MariaDB centric community discussion forums/groups etc :)

    This is probably a key factor ;)