MariaDB – Thread Pool

測試環境為 CentOS 8 x86_64 虛擬機.

預設在 SQL 使用上單一個 connect 只能使用單個 Thread,如果是比較複雜的指令的時候就會變慢. 參考文章 – https://mariadb.com/kb/en/thread-pool-in-mariadb/

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.28-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW VARIABLES LIKE 'thread_handling';
+-----------------+---------------------------+
| Variable_name   | Value                     |
+-----------------+---------------------------+
| thread_handling | one-thread-per-connection |
+-----------------+---------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> EXIT
Bye

我們是可以透過設定檔案 /etc/my.cnf.d/mariadb-server.cnf 改成 pool-of-threads

[root@localhost ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mariadb]
thread_handling=pool-of-threads

服務需重新啟動.

[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# systemctl status mariadb
● mariadb.service - MariaDB 10.3 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2023-05-18 16:50:38 CST; 5s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 41368 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
  Process: 41296 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mariadb.service (code=exited, status=0/SUCCESS)
  Process: 41269 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
 Main PID: 41334 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 33 (limit: 49322)
   Memory: 102.9M
   CGroup: /system.slice/mariadb.service
           └─41334 /usr/libexec/mysqld --basedir=/usr

回到 Mariadb

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.28-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

並檢視 thread_handling 已變更為 pool-of-threads

MariaDB [(none)]> SHOW VARIABLES LIKE 'thread_handling';
+-----------------+-----------------+
| Variable_name   | Value           |
+-----------------+-----------------+
| thread_handling | pool-of-threads |
+-----------------+-----------------+
1 row in set (0.001 sec)

這時候單一個 connect 可以使用多個 Threads 了,比較複雜的指令也能加快處理速度了.