월: 2017 4월

MariaDB, MySQL, Percona Server Threadpool 정리

https://mariadb.com/kb/en/mariadb/thread-pool-in-mariadb/
1. 언제 쓰레드풀을 사용하면 효과적인가:
– 짧은 쿼리 위주, CPU바운드의 OLTP 워크로드에 유용

2. 쓰레드풀이 비효율적으로 동작하는 드문 경우들:
– 매우 몰리는 워크로드 (긴 유휴 액티비티와 짧은시간 동안 많이 동작하는 유저 액티비티 혼재)
– 쓰레드 생성 쓰로틀링부터 나오는 딜레이를 감당할 수 없을 때:
이런 경우라도, 쓰레드들을 retire 시키는 방식을 조정해서 성능은 향상될 수 있음
(e.g. MariaDB 의 thread_pool_idle_timeout, MySQL 의 thread_pool_max_unused_threads)
– 동시에 많고 길고, 양보하지 않는 쿼리들이 있을 때:
DW성 작업과 같이 풀을 독점하는 수행시간이 긴 쿼리가 발생할 때 다른 쿼리의 수행을 방해할 수 있음, 딜레이는 오직 지연stall 감지 매커니즘으로 조정할 수 있음
(e.g. thread_pool_stall_limit)
– 데이테베이스가 부하에 무관하게 언제나 빨리 끝나는 심플한 쿼리가 필요할 때:
데이터베이스 로드와 상관없이 심플한 쿼리가 언제나 빨리 수행될 수 있는 건 아님, 쓰레드풀이 다 차있을 때 SELECT 1 과 같은 쿼리도 큐로 들어가거나 나중에 수행되어 쓰레드풀 미사용보다 느려질 수 있음

3. MariaDB 로우레벨 구현 상세 문서에 있는 오라클 쓰레드풀의 단점 (2011년):
http://worklog.askmonty.org/worklog/Server-BackLog/?tid=246
– 리눅스에서만 효율적인 epoll() 을 씀, 다른 OS에서는 poll()을 사용
– 클라이언트 로그인, 로그인이 acceptor 쓰레드를 블락함, 그리고 네트워크 IO 가 싱크방식으로 완료됨, 이는 느린 클라이언트가 다른 클라이언트들의 로그인을 막게 됨
~~~> 요거 5.6.36 에서 어떻게 변했는지 확인 필요
– wait_timeout 기능이 MySQL 엔터프라이즈 쓰레드풀에서 동작하지 않음
– KILL 명령이 높은 오버헤드를 가지고 있고 효율적이지 못한 구현방식때문에 락 발생에 주의가 필요함
– 쓰레드풀에 대한 인터널정보가 information_schema 에 존재

4. MySQL 쓰레드풀에 있는 것:
– 동시 트랜잭션의 숫자를 제한 (부정적 측면의 이야기)
MariaDB 는 일반적으로 하이 큐 우선 구동시킴, MySQL은 (“BEGIN” 구문 이후) 트랜잭션이 한 번 시작하게 되면 커넥션의 우선순위를 낮춤 (로우큐 우선 사용)
아마도 특정 MySQL 버전에서 kernel_mutex 문제가 이 방식으로 해결되는 것일 수 있고, 지금까지는 sysbench 결과를 높이기 위한 트릭일 수 있다고 MariaDB 주장
– 쓰레드풀에 대한 인터널정보가 information_schema 에 존재

http://mikaelronstrom.blogspot.kr/2011/10/mysql-thread-pool-limiting-number-of_21.html
MySQL 쓰레드풀에 대해서는 Mikael Ronstrom 블로그에 나와있는데, MySQL 서버가 동시에 많은 트랜잭션을 핸들링 할 때 부하를 받으며, 너무 많은 트랜잭션이 병렬로 수행되는 상황을 피해야 한다고 함
쓰레드풀은 이미 트랜잭션을 시작했는지 혹은 아닌지에 따라 큐잉하는 쿼리의 우선순위를 핸들링하고
이러한 우선순위화는 너무 긴 트랜잭션이 존재할 때 livelock 이슈를 가지는데 이런 문제를 피하기위해 설정된 시간이 지난 후에 높은 우선순위큐로 쿼리를 이동시킴(thread_pool_prio_kickup_timer)
하지만 짧은 시간동안 너무 많은 이동이 발생되지 않도록, 쓰레드풀은 쓰레드그룹당 10ms 에 한 쿼리를 이동시킴 (초당 100쿼리가 timer에 걸리면 높은 우선순위큐로 이동)
서버에서의 쿼리 수행을 빠르게 하기 위해 유저커넥션을 항상 높은 우선 순위 큐에 존재하도록 설정 가능함

5. DB별 쓰레드풀 파라미터의 차이:
MySQL에만 있는 것//
thread_pool_high_priority_connection # 동시 트랜잭션 수 제한과 관련(기본값은 낮은 우선순위 큐 먼저 사용)
thread_pool_prio_kickup_timer # 동시 트랜잭션 수 제한과 관련(낮은 우선순위 큐에서 높은 우선순위 큐로 이동)
thread_pool_max_unused_threads # MySQL 쓰레드풀만의 쓰레드 종료 로직, MariaDB 에서는 thread_pool_idle_timeout 를 사용
thread_pool_algorithm # 실험적인 변수, 벤치마킹 점수용?

MariaDB에만 있는 것//
thread_pool_max_threads # 풀 내 최대 쓰레드 수 설정, 최대값에 도달하면 추가로 쓰레드가 생성되지 않음, MariaDB 10.2.4 부터 디폴트 65536
thread_pool_idle_timeout # idle worker 쓰레드에 대한 timeout 설정, 기본값은 60
MySQL, MariaDB에 모두 존재하나 동작 방식이 다른 것//
thread_pool_stall_limit  # 큐에서 지연으로 판단하는 임계치, MySQL 쓰레드풀에서는 10 ms 단위로 체크, MariaDB 에서는 1ms 단위로 체크

6. MariaDB 와 Percona 의 차이:
– MariaDB 의 thread_pool_priority=auto,high,low https://mariadb.com/kb/en/mariadb/thread-pool-system-and-status-variables/#thread_pool_priority 는
Percona 의 thread_pool_high_prio_mode=transactions,statements,none https://www.percona.com/doc/percona-server/5.7/performance/threadpool.html#thread_pool_high_prio_mode 와 일치
– Percona 에는 thread_pool_high_prio_tickets 세팅으로 새로운 커넥션 쿼리를 하이 큐에 얼마나 들어오게 할건지 지정 (기본값: 4294967295)
https://www.percona.com/doc/percona-server/5.7/performance/threadpool.html#thread_pool_high_prio_tickets
MariaDB 는 thread_pool_prio_kickup_timer 피쳐가 있음
https://mariadb.com/kb/en/mariadb/thread-pool-system-and-status-variables/#thread_pool_prio_kickup_timer

7. MySQL 쓰레드풀 사용시 쿼리 수행이 지연되는 최악의 케이스 예시
로우큐에 200개 구문이 존재
하이큐에 10개 구문이 존재
thread_pool_prio_kickup_timer=500 (500ms)
thread_pool_stall_limit=30 (300ms)

최악의 케이스로 하이큐에 지속적으로 장시간 수행되는 10개 트랜잭션이 있어 로우큐에서 하이큐로 옮겨갈 수 있는 구문이 없다면,
thread_pool_prio_kickup_timer=500ms 이후, 새로운 구문이 하이큐로 옮겨갈 수 있게 됨
로우큐에서 하이큐로 이동하는 속도는 최대 초당 100개(10ms 당 1개)로 고정되어있어 200개 구문이 모두 하이큐로 옮겨가는데 2초가 걸림
옮겨지는 구문들이 하이큐에 도달했을 때 하이큐에 이미 많은 롱런 구문들이 먼저 존재할 수 있음
이때 모든 구문들이 지연이되고 다음 구문이 하이큐로부터 받기 전 매 구문당 thread_pool_stall_limit=30(300ms)가 걸림
그리하여, 이 시나리오에서 새 구문이 수행을 시작하기 전 토탈..

(하이큐이동대기 100개*2회=1초 + 하이큐이동 100개*2회=2초) + 하이큐 전체가 다 차있어서 이동한 쿼리가 모두 지연되면 200개*300ms=60초 = 63초가 소요 (로우큐에서만 최소 3초가 걸림)

MySQL 5.6.36 & Threadpool Fix

http://worklog.askmonty.org/worklog/Server-BackLog/?tid=246
MariaDB 쪽 쓰레드풀 개발 백로그 문서를 보면 오라클이 구현한 쓰레드풀의 단점에 대해서 기록되어있고 acceptor 쓰레드의 블락킹 문제가 언급되어 있음

Comparison with MySQL enterprise threadpool
===========================================
Here are the downsides of Oracle’s implementation.
– MySQL/Oracle is using efficient epoll() on Linux and inefficient poll()
everywhere else.
– Client login. Login just blocks the acceptor thread, and network IO is done
synchronously, which means slow client will prevent logons of other clients.
클라이언트 로그인. 로그인이 acceptor 쓰레드를 블락함, 그리고 네트워크 IO 가 싱크방식으로 완료됨, 이는 느린 클라이언트가 다른 클라이언트들의 로그인을 막게 됨
– wait_timeout does not work with MySQL Enterprise threadpool.
– KILL seems to have a high overhead and require careful locking due to
inefficient implementation.

https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-36.html
이번 5.6.36 업데이트 에서 쓰레드풀의 acceptor 쓰레드 부분에서 변경이 있는 것으로 보이며 읽기쓰기 워크로드가 들어오는 마스터에서 Threadpool 사용시 로그인할때 심각한 지연을 경험했던 바 테스트가 필요할 것으로 보임

쓰레드 풀//
쓰레드풀 플러그인 퍼포먼스 향상을 위해 커넥션 인증authentication과 초기화initialization 부분이 acceptor 쓰레드에서 클라이언트 커넥션을 핸들링하는 쓰레드풀 worker 쓰레드로 이동되어 짐
acceptor 쓰레드가 감소된 레이턴시로 더 높은 rate 에서 새 커넥션을 핸들링하게 해 줌
커넥션 인증과 초기화에 대해 진행하거나 대기하고 있는 쓰레드의 숫자를 INFORMATION_SCHEMA.TP_THREAD_GROUP_STATE 테이블에 추가된 CONNECT_THREAD_COUNT 컬럼에서 알 수 있음
쓰레드 그룹에서 최대 4개의 worker 쓰레드가 존재할 수 있고; 이 쓰레드들은 얼마간의 미사용 기간이 지나면 만료됨 (Bug #17159742)

그밖의 관심가는 5.6.36 버그 픽스//
– InnoDB: restart 시 st_my_thread_var 구조체를 초기화시키는 백그라운드 쓰레드를 위한 my_thread_init() 와 my_thread_exit() functions 누락으로 server exit 이 발생되었던 것을 픽스 (Bug #25167032)

– InnoDB: row_search_mvcc() function 이 레코드가 트랜잭션 읽기 뷰에 없을 때 range query 에서 불필요하게 전체 테이블을 스캔하는 경우가 있었던 것을 픽스 (Bug #84202, Bug #23481444, Bug #25251375)

– mysqldump 가 덤프를 쓸때 올바르게 어떤 식별자identifiers 에 quote 를 달지 못하는 오류 픽스 (Bug #25717383)

https://bugs.mysql.com/bug.php?id=78777 픽스 후 발생한 사이드이펙트로 P_S 을 쓰지 않을 때 Query state 가 NULL 로 보여지는 문제에 대한 픽스 (Bug #25309017, Bug #84305) https://bugs.mysql.com/bug.php?id=84305

– libmysqlclient 에 링크된 client 가 prepared statement 사용 동안 잘못된 메모리 엑세스를 발생될 수 있었던 문제 픽스 (Bug #25164932)

– WHERE 절에 dependent subquery 가 있는 쿼리에서 테이블이 셀렉트리스트에 있는 컬럼과 서브쿼리에 사용되는 컬럼이 복합으로 구성된 세컨더리 인덱스가 있을 때, GROUP BY 또는 DISTINCT 쿼리가 Loose Index Scan (플랜상 Using index for group-by 로 표기) 을 사용하게 되어 쿼리가 잘못된 결과를 생산할 수 있었던 문제 픽스 https://bugs.mysql.com/bug.php?id=83005

– DISTINCT 와 집계aggregate 함수를 사용하는 쿼리가 잘못된 결과를 생산할 수 있었던 문제 픽스 (Bug #22686994, Bug #80310) https://bugs.mysql.com/bug.php?id=80310

– 빈로그포맷 statement 일 때 서버사이드 prepared statement (useServerPrepStmts=true) 사용 시 빈로그에 fractional part 가 드랍되어 기록됨, 마스터에서는 round 되고 슬레이브에서는 truncate 되어 데이터가 들어가 데이터 정합성이 맞지 않게 되는 문제 픽스 서버사이드 prepared statment 미사용시 timestamp 의 fractional part 가 유지되어 기록됨 (Bug #74550, Bug #19894382, Bug #25187670) https://bugs.mysql.com/bug.php?id=74550