월: 2015 6월

MySQL Thread Pool

오라클 엔터프라이즈에디션: 쓰레드풀 슬라이드

발생하는 문제점들:
– 커넥션 당 하나의 쓰레드로 커넥션 매핑
– 수백 또는 수천의 커넥션들이 병렬적으로 수행될 때 메모리 사용량이 상당히 증가
– 한 커넥션에서 필요한 캐시된 데이터들이 다른 커넥션들의 수행으로 플러시되어flushed out 캐시 미스로 이어짐
– 각 쓰레드들이 (메모리의) “핫 스팟”에 대해 락을 잡으면서 락이 더 오래 점유되고 “핫 스팟들”에 대한 높은 경합으로 이어짐
– “핫 스팟들”에 대한 높은 경합은 확장성scalability 감소를 야기
– 높은 수치의 캐시 미스는 더 긴 수행 시간을 의미
– 커넥션들이 병렬적으로 더 수행될 수록 이런 문제들이 심화
– 쓰기 쿼리의 수행시 더 많은 “핫 스팟”이 발생하여 쓰기 위주의 워크로드는 문제의 영향을 더 받음
– 가장 좋지않은 케이스는 모든 액티브 커넥션들이 필요로 하는 총 메모리 용량이 서버의 메모리보다 높아질 때로, 이 때 MySQL 서버는 스와핑을 시작하며 큰 성능하락 발생
– 많은 커넥션들이 동시 수행될 때 MySQL 서버는 “핫 스팟”을 더 오랜 시간 점유하기 위해 필요한 (또 다른) “핫 스팟들”을 가짐
– 트랜잭션 리스트의 락 보호가lock protecting 하나의 예로, 트랜잭션에 관련된 커넥션별 엔트리가 포함되어 있음
– idle 커넥션들 마저 MySQL 서버의 확장성을 감소시키는 리소스를 소비할 수 있음

현재의 솔루션:
– innodb_thread_concurrency 옵션
– InnoDB 커널 내 동시 수행executing 쓰레드의 숫자를 제한
– 구현상 옵션 자체의 확장성 문제 존재 (static한 값을 기준으로 제한)
– InnoDB 동시 사용자가 너무 많다면 쿼리구문 수행을 시작하는 시점에 홀딩하는 대신에 스토리지 엔진에서 홀딩함 (리소스가 사용되기 전에 홀딩하는 것이 아님)
– InnoDB 내 너무 많은 동시 트랜잭션이 생길 때 발생하는 문제를 해결할 수는 없음

쓰레드풀 솔루션:
– MySQL 서버가 쿼리 수행을 위한 충분한 CPU와 메모리 리소스를 확보할 때까지 쿼리 수행을 기다림
– 커넥션에 대한 진행중인 트랜잭션 쿼리 우선순위를 매김
– 쓰레드를 그룹으로 나누어 그룹당 하나의 액티브 쓰레드를 관리하는데 목표를 둠
– 쿼리가 지연stalled 되거나 오랜시간 수행될 때 데드락을 피함

3. 쓰레드풀의 이점
– MySQL 서버가 쿼리 수행을 위한 충분한 CPU와 메모리 리소스를 확보할 때까지 쿼리 수행을 기다림
– 커넥션에 대한 진행중인 트랜잭션 쿼리의 우선순위를 매김
– 쓰레드를 그룹으로 나누어 그룹당 하나의 액티브 쓰레드를 관리하는데 목표를 두고 동작
– 쿼리가 지연stalled 되거나 오랜시간 수행될 때 데드락을 피함

4. 쓰레드풀 동작 방식
1) 확장성 이슈 최소화
– 쓰레드풀의 구현에 있어 하나의 풀에서 모든 쓰레드를 관리한다면 확장성 문제를 야기시키기 쉬움
– 이 문제를 피하기 위해 쓰레드들과 커넥션들을 쓰레드 그룹들로 나눔, 구현의 목표는 언제든지 쓰레드 그룹당 1개 또는 0개의 쿼리가 수행
– 커넥션들은 커넥션시 라운드 로빈 방식으로 할당되어 쓰레드 그룹에 묶임
– 각각의 쓰레드 그룹은 우선순위가 매겨진 수행을 기다리는 커넥션들의 큐를 유지
– Divide-and-conquer 테크닉을 사용하여 문제해결
http://en.wikipedia.org/wiki/Divide_and_conquer_algorithms

2) 동시 트랜잭션 최소화
– 너무 많은 동시 트랜잭션으로 인한 확장성 병목을 피하기 위해 동시 트랜잭션 수를 제한
– 수행을 기다리는 커넥션의 우선순위를 설정하는 것으로 처리, 진행되는 트랜잭션은 높은 우선순위로 큐에 들어가고 나머지는 낮은 우선순위로 큐에 들어감
– 낮은 우선순위 커넥션이 모든 높은 우선순위 커넥션들이 완료될 때까지 대기
– (시간이) 긴 트랜잭션의 우선순위는 짧은 트랜잭션에 부정적인 영향을 끼칠 수도 있음, 심지어 live lock 상황을 야기시킬 수 있으므로
주어진 시간동안 대기하면 쿼리들을 낮은 우선순위 큐에서 높은 우선순위 큐로 이동시킴
– 시간은 파라미터 –thread_pool_prio_kickup_timer 로 설정
– 쓰레드 그룹당 10ms 마다 하나의 쿼리 이상 이동시키지는 않음
– 특정한 커넥션을 위해 응답시간 확보가 필요한 유저라면, 커넥션을 높은 우선순위로 선언 가능

3) 수행 동시 쿼리 수 최소화
– live lock 이슈: 롱런 쿼리는 잠재적으로 쿼리가 있는 쓰레드 그룹 내 모든 다른 쿼리들을 영원히 블락
이렇게 오랜시간 수행되는 쿼리를 피하기 위해 문제 쿼리를 지연stalled 으로 선언
쿼리가 지연되는 시간은 설정 파라미터인 –thread_pool_stall_limit 으로 조정
쿼리가 지연으로 선언되면, 이 쓰레드 그룹 내 다른 쿼리가 시작되는 것이 허용
– 쿼리 블락: 테이블 락, row 락, 글로벌 읽기 락, 파일 I/O 등으로 발생하는 일시적인 쿼리 블락
쿼리 수행이 특정 시간동안 블락이 된 것으로 인지되면 쓰레드풀이 MySQL 서버로 부터 callback 을 획득
http://en.wikipedia.org/wiki/Callback_%28computer_programming%29
callback 획득 시 같은 쓰레드 그룹 내 다른 쿼리의 수행을 시작, 이후 수행 즉시 재개

쓰레드풀과 커넥션풀의 차이:
– 커넥션이 MySQL 에서 쓰레드로 동일하게 주어짐, 쓰레드풀과 커넥션풀이 혼동되기 쉬움
– 하지만 이 둘은 다른 문제들의 솔루션이며 커넥션 풀은 커넥션 재사용을 가능하게 하고 MySQL 서버 접속수행 비용을 절약
– 쓰레드풀은 동시적인 쿼리 수행 수를 제한하기 위해 서버측에서 동작
– 따라서 독립적으로 사용할 수 있음

쓰레드풀 언제 사용해야하는가:
– 모니터링을 위한 가장 중요한 변수는 threads_running
– 이 변수는 MySQL 서버에서 수행되는 동시 쿼리의 개수 정보를 제공
– 수치가 스파이크 치면 최적의 thread_pool_size 쓰레드풀 설정으로 충분히 유용
– 이러한 스파이크는 보통 과도한 로드 상황에서 발생함, 쓰레드풀은 과도한 로드 상황에서 발생되는 문제에 맞서 보호하는데 중요
– 만약 현재 innodb_thread_concurrency 를 동시 쿼리 수 제한을 위해 사용하고 있다면, 쓰레드풀 사용이 이점
– 또한 대부분의 쿼리들이 (수행 시간이) 짧은 쿼리라면 쓰레드풀이 이점
– 수행시간이 긴 쿼리들은 보통의 MySQL 쓰레딩 모델과 매우 비슷하게 동작하기 때문에 이러한 경우 쓰레드풀은 덜 이점이 있을 것

쓰레드풀 최적 설정:
– thread_pool_size (쓰레드 그룹의 수)
– InnoDB 읽기 워크로드는 보통 30-40
– InnoDB 쓰기 워크로드는 12-30 언더
– InnoDB 믹스된 워크로드는 16-36
– MyISAM 워크로드는 6-8
– 워크로드의 쿼리 타입에 따라 다를 수 있으나 기본설정인 16이 좋은 시작 값
– 7:3 워크로드의 프로덕션에서 코어수를 따져 24로 튜닝 시작하였음

– thread_pool_stall_limit
– 벤치를 위해서는 보통 적어도 1초 설정 (=100)
– 너무 많은 쿼리들이 대기상태가 아닌지 쓰레드풀 Information Schema 테이블을 체크
– 기본설정은 많은 롱 쿼리들의 영향을 피하기 위한 보수적 설정 (=6, 60ms)
– 프로덕션에서 기본값 6(=60ms) 보다 큰 50(=500ms) 으로 튜닝 시작하였음

– thread_pool_prio_kickup_timer
– 벤치를 위해서 보통 적어도 10초 설정 (=10000).
– 롱런 쿼리에 의한 livelock 의 리스크 때문에 세팅
– 대부분의 케이스에서 기본 설정인 1초 OK, 롱 런 쿼리가 매우 적은 환경에서는 값을 올릴 수 있음
– 프로덕션에서 기본값 1000(1초) 로 튜닝 시작하였음

벤치
http://www.mysql.com/products/enterprise/scalability.html
https://blog.mariadb.org/mariadb-5-5-thread-pool-performance/

Advertisements

MySQL 5.6 New Feature pt. 2

MySQL 5.6 내부적인 향상점
https://www.percona.com/blog/2013/01/27/mysql-5-6-improvements-in-the-nutshell/

1. 확장성
Scalable Read Only Transactions
InnoDB가 readonly 트랜잭션 판단(싱글트랜잭션으로 간주되는 autocommit=1 등)하고 transaction ID(TRX_ID) 세팅하는 오버헤드 피함
Concurrent Innodb data file extension
Non-Recursive Deadlock Detection
Faster Locking Primitives
Improved Innodb Thread Concurrency
동시 스레드 부분에서 더 세부적인 설정
Large (over 4GB) redo logs support
2TB까지 리두로그 사이즈를 증가(ib_logfile)
사용량이 많은 DB에서 성능 향상
4k, 8k Pages
innodb_page_size 옵션 SSD에서 성능향상 기대
낮아질 수록 반으로 데이터 길이 한계가 줄어듬
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_page_size
https://blogs.oracle.com/mysqlinnodb/entry/innodb_5_6_4_supports
Group Commit for Binary Log
http://mysqlmusings.blogspot.kr/2012/06/binary-log-group-commit-in-mysql-56.html
http://dimitrik.free.fr/blog/archives/2012/06/mysql-performance-binlog-group-commit-in-56.html
Fight Cache Coherence and False Sharing issues
Reduced Innodb Memory Fragmentation
Reduced Locking for Partitioned tables
Reduced Contention for LOCK_open
Multiple table_open_cache instances
테이블 오픈 시 경합 해소
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_open_cache_instances
http://dimitrik.free.fr/blog/archives/2012/09/mysql-performance-table-open-cache-in-56.html

2. 투명성
Improved EXPLAIN
Handling INSERT/UPDATE/DELETE queries
JSON output with more information
DML모두 플랜 가능, JSON 포맷으로 출력
Optimizer Tracing
http://jorgenloland.blogspot.kr/2011/10/optimizer-tracing-query-execution-plan.html
Deadlock Logging
가장 최근 뿐만이 아니라 모든 데드락을 에러로그에 로깅
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks
GET DIAGNOSTICS
진단, SP에서 에러코드등 받을 수 있음
http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html

3. 사용성 개선
Separate Tablespaces for Innodb Undo Logs
언두로그 경로 분리(다른 디스크로)
http://dev.mysql.com/doc/refman/5.6/en/innodb-undo-tablespace.html
Innodb Buffer Pool Preloading
서버 리스타트 시 버퍼풀 웜업
http://dev.mysql.com/doc/refman/5.6/en/innodb-preload-buffer-pool.html
Online DDL
온라인 ALTER 등 가능
http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
Innodb Transportable Tablespaces
테이블 단위의 파셜백업 가능
http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html
User Defined DATA DIRECTORY for Innodb Tables
Connection Attributes (퍼포먼스 스키마)
http://dev.mysql.com/doc/refman/5.6/en/performance-schema-connection-attribute-tables.html

4. 개발
Microsecond TIME precision
DATETIME(6)등 사용
http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

5. 보안
Password hashes instead of plain passwords in Query Logs
SHA256 hashing with Salt for Authentication
Support obfuscated password storage for command line tools
Policy Based password validation
플러그인 사용
http://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html
Plugin authentication support for Replication