Thread Pool

Thread Pool 이란 ( MySQL 서버용 스레드 풀링 플러그인(문서 ID 1358784.1) )

• 스레드 풀은 동시 처리 요청이 많더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버 자원 소모를 줄이는데 목적이 있습니다.
• 예를 들어, **스레드 풀의 스레드 개수를 10개로 정해놨다면 갑자기 요청이 100개 들어왔을 때 처음 10개의 요청에 대해서는 스레드를 배정하고 나머지는 큐에다 넣어서 대기시킨다.**
• 기본 스레드 처리 모델을 사용하면 더 많은 클라이언트가 서버에 연결하고 명령문을 실행할수록 시스템의 전체 성능이 저하됩니다. 스레드 풀링 플러그인은 많은 동시 연결을 갖는 오버헤드를 줄이는 데 도움이 되는 대체 스레드 처리 방법을 제공합니다.
• 활성 트랜잭션이 너무 많은 InnoDB의 성능 병목 현상을 방지합니다.
• MySQL Enterprise 상품으로 Community에서는 제공 X ( 하지만 Percona에서 플러그인 형태로 제공중 ) / MariaDB의 경우 Community 에서도 제공

 

 

언제 스레드 풀링을 사용해야 하는가?

• 매우 빠른 명령문을 실행하는 많은 수의 연결이 있는 경우

 

Test 목적

• 고객사 ( MariaDB ) 의 경우 Active Session 30일 때는 어느 정도 DB 처리가 된다. Active Session 100개 이상으로 붙어서 실행될 때 Resource Full과 함께 DB 처리가 되지 않는다.
• 하여, Thread Pool사용으로 30개 이상의 요청 Thread들은 대기 Queue에 담아두는 것은 어떠한가 테스트를 해볼 예정이다.
• 이러한 대기 처리를 DB 쪽에서만 한다면 Application 단에서 사용자가 대기 중인지를 알 수 없기 때문에 F5와 같은 새로고침을 하면 더 많은 세션이 생겨 날것이다 ( OOO ) ⇒ 이런 작업을 한다면 개발단에서 하는 것이 좋을 것으로 보인다. 하여 기존에도 CPU 때문의 이슈가 발생했기 때문에 Query의 실행시간이 아닌 Query의 CPU 사용률에 중점을 두고 튜닝을 해봐야 할 것으로 생각된다. 

 

오픈 카톡방에서 형님들이 한 말

• 오픈 정보방에서 물어본 결과 DB 쪽에서 대기 큐를 할 경우 Application 연결에 문제가 생기던 경우가 많았다고 한다.
• Thread pool 많이 사용하기도 한다.
• 0.1초 처리된 쿼리가 대량 수행될 경우 당연히 처리속도가 느려지고요. 이럴 때는 어플이케이션단에서 스로 트링 제어하셔야 합니다 애플리케이션에서 connection pool의 active값을 조절하시고 active를 줄임에 따라 wait time out 이 발생할 수 있으니 timeout값을 눌리셔야 합니다
• 맞습니다. OOO 님 이야기처럼 어플리케이션에서 스로틀링 제어하면 가장 좋습니다. 하지만 그렇지 못하는 환경도 많은 것 같아요. 오래 걸리는 쿼리와 빠른 쿼리들이 함께 공존하는 경우가 그럴 수 있을 것 같아요... 이러한 경우라면 thread pool을 사용하는것도 하나의 방법이 될 수 있을것 같습니다. 기무치님도 thread pool과 관련하여 질문하신 것 같아요.

 

Thread_handling 변경

• Thread_handling Parameter는 정적 파라미터여서 재기동이 필요하다.
• https://mariadb.com/kb/en/thread-pool-system-status-variables/

MariaDB [(none)]> \\! cat /etc/my.cnf
[mysqld]
datadir=/data/data
socket=/tmp/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in <http://fedoraproject.org/wiki/Systemd>
**thread_handling=pool-of-threads**

[mysqld_safe]
log-error=/data/log/mariadb.log
pid-file=/data/log/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

 

SYSBENCH 테스트

• SYSBENCH
• Thread Pool Size를 바꿔가면서 Sysbench는 100개의 Thread가 실행되도록 설정하고 Thread Pool이 어떻게 사용되는지 확인
• Thread Pool에 의해 대기

MariaDB [(none)]> show variables like 'thread_handling';
+-----------------+-----------------+
| Variable_name   | Value           |
+-----------------+-----------------+
| thread_handling | pool-of-threads |
+-----------------+-----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> set global thread_pool_size=10;

MariaDB [(none)]> show variables like '%thread_pool_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| thread_pool_size | 20    |
+------------------+-------+
1 row in set (0.00 sec)

[root@tobe-mysql8 sysbench]# sysbench --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench --threads=100 --table-size=100000 --mysql-socket=/tmp/mysql.sock --tables=5  /usr/share/sysbench/oltp_insert.lua prepare

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest3'...
Creating table 'sbtest1'...
Creating table 'sbtest2'...
Creating table 'sbtest5'...
Creating table 'sbtest4'...
Inserting 100000 records into 'sbtest2'
Inserting 100000 records into 'sbtest1'
Inserting 100000 records into 'sbtest3'
Inserting 100000 records into 'sbtest5'
Inserting 100000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest1'...

[root@tobe-mysql8 sysbench]# sysbench --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench --threads=100 --table-size=100000 --mysql-socket=/tmp/mysql.sock --tables=5  /usr/share/sysbench/oltp_insert.lua run

**####THREAD_POOL_SIZE = 20  ==> 대기했다가 실행 Queue가 끝나면 대기 Queue에서 배서 넣고 하는 것으로 보인다.**
+----------+
| count(*) |
+----------+
|        6 |
+----------+
(생략)
+----------+
| count(*) |
+----------+
|       16 |
+----------+
+----------+
| count(*) |
+----------+
|       17 |
+----------+
+----------+
| count(*) |
+----------+
|       18 |
+----------+
+----------+
| count(*) |
+----------+
|       19 |
+----------+
+----------+
| count(*) |
+----------+
|       20 |
+----------+
+----------+
| count(*) |
+----------+
|       21 |
+----------+
+----------+
| count(*) |
+----------+
|       22 |
+----------+
+----------+
| count(*) |
+----------+
|       23 |
+----------+
+----------+
| count(*) |
+----------+
|       23 |
+----------+
(생략)
+----------+
| count(*) |
+----------+
|        6 |
+----------+

**## Thread_pool_size 사용 X  => 바로 100까지 쭉 올라감**
[root@mysql-5-7 ~]# while true ; do mysql -uroot -proot -e "select count(*) from information_schema.processlist where command!='Sleep';"; sleep 1; done
+----------+
| count(*) |
+----------+
|        6 |
+----------+
+----------+
| count(*) |
+----------+
|        6 |
+----------+
+----------+
| count(*) |
+----------+
|        6 |
+----------+
+----------+
| count(*) |
+----------+
|       15 |
+----------+
+----------+
| count(*) |
+----------+
|      104 |
+----------+
+----------+
| count(*) |
+----------+
|      102 |
+----------+
+----------+
| count(*) |
+----------+
|      103 |
+----------+
+----------+
| count(*) |
+----------+
|      105 |
+----------+
+----------+
| count(*) |
+----------+
|      105 |
+----------+
+----------+
| count(*) |
+----------+
|      106 |
+----------+
+----------+
| count(*) |
+----------+
|      101 |
+----------+
+----------+
| count(*) |
+----------+
|      101 |
+----------+
+----------+
| count(*) |
+----------+
|      106 |
+----------+
+----------+
| count(*) |
+----------+
|        6 |
+----------+
+----------+
| count(*) |
+----------+
|        6 |
+----------+

성능

• Write

[[root@mysql-5-7 ~]# sysbench  --mysql-db=sysbench  --mysql-host=localhost --mysql-socket=/tmp/mysql.sock  --mysql-user=sysbench  --mysql-password=sysbench  --threads=100 --report-interval=10 --max-time=60 --max-requests=0 /usr/share/sysbench/oltp_insert.lua run
-time is deprecated, use --time insteadWARNING: --max-time is deprecated, use --time instead
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 14867.07 qps: 14867.07 (r/w/o: 0.00/14867.07/0.00) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 15530.22 qps: 15530.22 (r/w/o: 0.00/15530.22/0.00) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 17482.15 qps: 17482.15 (r/w/o: 0.00/17482.15/0.00) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 16768.41 qps: 16768.41 (r/w/o: 0.00/16768.41/0.00) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 16278.88 qps: 16278.88 (r/w/o: 0.00/16278.88/0.00) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 16928.46 qps: 16928.46 (r/w/o: 0.00/16928.46/0.00) lat (ms,95%): 10.27 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           978856
        other:                           0
        total:                           978856
    **transactions:                        978856 (16307.60 per sec.)
    queries:                             978856 (16307.60 per sec.)**
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0229s
    total number of events:              978856

Latency (ms):
         min:                                    0.38
         avg:                                    6.13
         max:                                  140.61
         95th percentile:                       11.45
         sum:                              5998792.50

Threads fairness:
    events (avg/stddev):           9788.5600/159.02
    execution time (avg/stddev):   59.9879/0.01

**### Thread Pool 20개**
[root@mysql-5-7 ~]# sysbench  --mysql-db=sysbench  --mysql-host=localhost --mysql-socket=/tmp/mysql.sock  --mysql-user=sysbench  --mysql-password=sysbench  --threads=100 --report-interval=10 --max-time=60 --max-requests=0 /usr/share/sysbench/oltp_insert.lua run
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 12663.82 qps: 12663.82 (r/w/o: 0.00/12663.82/0.00) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 13597.69 qps: 13597.69 (r/w/o: 0.00/13597.69/0.00) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 14275.47 qps: 14275.47 (r/w/o: 0.00/14275.47/0.00) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 14532.00 qps: 14532.00 (r/w/o: 0.00/14532.00/0.00) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 14200.72 qps: 14200.72 (r/w/o: 0.00/14200.72/0.00) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 99 tps: 15171.79 qps: 15171.79 (r/w/o: 0.00/15171.79/0.00) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           844591
        other:                           0
        total:                           844591
    **transactions:                        844591 (14069.67 per sec.)
    queries:                             844591 (14069.67 per sec.)**
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0277s
    total number of events:              844591

Latency (ms):
         min:                                    0.76
         avg:                                    7.10
         max:                                   92.79
         95th percentile:                       12.75
         sum:                              5999334.39

Threads fairness:
    events (avg/stddev):           8445.9100/754.99
    execution time (avg/stddev):   59.9933/0.01

• Read Only

[root@mysql-5-7 ~]#  sysbench --mysql-db=sysbench  --mysql-host=localhost --mysql-socket=/tmp/mysql.sock --mysql-user=sysbench  --mysql-password=sysbench --table-size=5444444 --tables=5  --threads=100 --report-interval=10 --max-time=60 /usr/share/sysbench/oltp_read_only.lua run
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 2786.15 qps: 44676.13 (r/w/o: 39094.13/0.00/5581.99) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 2860.68 qps: 45771.40 (r/w/o: 40050.04/0.00/5721.36) lat (ms,95%): 39.65 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 2850.25 qps: 45592.23 (r/w/o: 39891.82/0.00/5700.40) lat (ms,95%): 39.65 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 2885.46 qps: 46174.40 (r/w/o: 40403.19/0.00/5771.21) lat (ms,95%): 39.65 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 2865.53 qps: 45840.21 (r/w/o: 40109.75/0.00/5730.46) lat (ms,95%): 38.94 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 2890.88 qps: 46257.53 (r/w/o: 40475.46/0.00/5782.07) lat (ms,95%): 38.94 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            2400902
        write:                           0
        other:                           342986
        total:                           2743888
    **transactions:                        171493 (2856.05 per sec.)
    queries:                             2743888 (45696.83 per sec.)**
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0439s
    total number of events:              171493

Latency (ms):
         min:                                    0.51
         avg:                                   35.00
         max:                                 1054.47
         95th percentile:                       39.65
         sum:                              6001701.69

Threads fairness:
    events (avg/stddev):           1714.9300/14.31
    execution time (avg/stddev):   60.0170/0.01

**#### Thread Pool 20개**
[root@mysql-5-7 ~]# sysbench --mysql-db=sysbench  --mysql-host=localhost --mysql-socket=/tmp/mysql.sock --mysql-user=sysbench  --mysql-password=sysbench --table-size=5444444 --tables=5  --threads=100 --report-interval=10 --max-time=60 /usr/share/sysbench/oltp_read_only.lua run
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 3226.59 qps: 51704.19 (r/w/o: 45241.71/0.00/6462.49) lat (ms,95%): 38.94 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 3276.82 qps: 52434.38 (r/w/o: 45880.54/0.00/6553.83) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 3287.42 qps: 52591.46 (r/w/o: 46016.81/0.00/6574.64) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 3321.63 qps: 53146.17 (r/w/o: 46502.81/0.00/6643.36) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 3311.89 qps: 52982.42 (r/w/o: 46358.94/0.00/6623.48) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 3257.88 qps: 52138.91 (r/w/o: 45622.56/0.00/6516.35) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            2756992
        write:                           0
        other:                           393856
        total:                           3150848
    transactions:                        196928 (3279.29 per sec.)
    queries:                             3150848 (52468.61 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0505s
    total number of events:              196928

Latency (ms):
         min:                                    0.82
         avg:                                   30.47
         max:                                 5480.86
         95th percentile:                       37.56
         sum:                              6000856.11

Threads fairness:
    events (avg/stddev):           1969.2800/40.04
    execution time (avg/stddev):   60.0086/0.03