MySQL: 대량 접속 처리하기
이번 컬럼에서는 MySQL 시스템에서 10,000개 이상의 접속이 일어날 때의 가장 좋은 처리 방법에 대해 알아보기로 한다. 이 글은 MySQL AB의 엔지니어인 Peter Zaitsev가 작성한 것을 번역한 것이다. 원문은 http://peter-zaitsev.livejournal.com/14954.html에 수록되어 있다.
우선 이러한 요구가 주로 어떤 환경에서 발생하는지를 살펴 보자. 40대의 아파치 웹 서버가 있고 각각은 200개의 클라이언트를 가지고 있다면 8000개의 영구 접속(persistent)이 이루어진다. 이럴 경우, 두 가지 질문 사항이 생긴다 - 실제로 영구 접속이 필요하며 200개의 클라이언트가 실제로 필요한가?
영구 접속은 페이지를 생성하는 쿼리가 거의 없고 데이터 베이스의 로드(load)가 응답 시간에 한정될 경우에만 성능상 잇점으로 사용될 수 있다. 현대의 대부분의 어플리케이션들은 여기에 해당되지 않는다. 이러한 이유로 PHP를 위한 “MySQLi”는 디폴트로 영구 접속을 비활성화 시켜놓았다.
자바 어플리케이션의 경우에는 이런 까닭으로 영구 접속 보다는 접속 풀링(connection pooling)을 사용하는데, 이 방식이 보다 효과적이기 때문이다.
이제 200개의 아파치 클라이언트에 대해서 생각해 보자. 왜 이렇게 많은 숫자가 필요한가? 대부분의 경우에는 이렇게 웹을 구성하는 것은 잘못된 것이다. 200개의 클라이언트 중의 90%는 응석받이(spoon feed) 슬로우 클라이언트이며, 이것들은 mod_php, mod_perl 또는 다른 것을 가지고 메모리만 점유하게 된다. 한 가지 해결책은 이러한 클라이언트 앞에 가벼운 프록시(proxy)를 설치하는 것이다. 여러분은 아파치 모듈을 이러한 목적으로 사용할 수 있을 것인데, 나는 이러한 목적으로 아파치에 비해 가벼운 lighttpd를 사용하곤 한다. 여기에서 중요한 사항은 접속 당 프로세스/쓰레드를 사용하지 않는다는 것이며 네트워크를 다룰 때에는 select() 또는 pool() 을 사용하지 않고 이벤트를 기준으로 한다는 점이다. 이렇게 하면 여러분은 보다 작은 수의 아파치 클라이언트를 가지고 박스(시스템) 당 10,000개 이상의 접속을 가질 수가 있게 된다. 다른 옵션으로는 lingerd를 사용하는 것인데, 나로서는 시도해 보지 않았다. 마지막으로 가장 기본적인 옵션으로는 빠른 -cgi 인터페이스를 사용하는 것인데, 이를 통해 여러분은 웹 서버를 보다 광범위하게 선택할 수 있게 된다.
200개의 프로세스가 필요한가? 이것은 어플리케이션에 달려 있다. 예를 들면 어플리케이션이 웹 서비스를 내부적으로 사용하거나 또는 다른 원격 네트워크를 호출할 경우라면 그럴 것이다. 이와 같은 경우에, 기대하는 성능을 얻기 위해서는 많은 수의 프로세스가 필요하게 된다. 하지만 대부분의 일반적인 경우에는 CPU 바운드(bound)안에서 업무를 처리하고 로컬의 데이터 베이스를 호출하기 때문에 이 보다 작은 수의 프로세스만으로도 충분하게 된다.
각각의 "디바이스(device)"는 동시에 처리할 수 있는 최적의(optimal) 요청 숫자를 가지고 있다. CPU의 경우에는 코어 당 하나의 쓰레드를 구동 시키는 것이 최적이다. HDD의 경우에는, 드라이브 당 적은 수의 요청만을 받아 들이지만(스케쥴링을 위해), 만일 여러분이 디스크를 많이 가지고 있다면, 랜덤 I/O와 같은 것에 의해 인터럽트가 되는 시퀀셜 스캔을 할 수가 있을 것이다.
또한 여러분은 메모리와 CPU 캐시를 계산해야 한다; 동시에 구동되는 프로세스가 많을수록 CPU 캐시와 OS 디스크 캐시는 각각에 대해 적게 할당된다. 프로세스의 최적화 숫자는 로드(load)에 달려 있는데, 만일 이것이 CPU 바운드(CPU bound)라면 보다 많은 수의 쓰레드가 구동되는 것을 볼 수 있을 것이며, I/O 바운드(I/O bound)라면 요청할 수 있는 숫자가 매우 많음을 알 수 있을 것이다.
나로서는 보통 (NumCPUs+NumDisks)*2 값을 사용한다. 어플리케이션, OS, 하드웨어 그리고 MySQL의 버그에 따라서 이 숫자는 달라질 수가 있다.
이 숫자에 대해 흥미로운 사실은 - 이 숫자가 항상 42는 아니라고 하더라도 보편적으로 42가 된다는 것이다. 만일 여러분이 “내부적으로 구동되는 쓰레드”를 살펴 본다면, 로컬 CPU에서 구동되는 쓰레드 또는 로컬 자원에서 블록된 쓰레드에 대해서 이 숫자를 사용할 수 있을 것이다 - 웹 서버, 어플리케이션 서버, 또는 데이터 베이스 서버일 경우에 해당함. 만일 프로세스가 네트워크 상에서 대기 중이라면, 이것은 로컬 자원이 아니기 때문에 이런 것들은 많이 있어도 상관이 없다.
그렇다면 얼마나 많이 필요한가? 나는 20~40개 정도가 웹/어플리케이션 서버에서 구동 시킬 수 있는 최적의 액티브 쓰레드 숫자라고 추정하면서 시작을 했다. 그런 다음에 실제로 MySQL에 적용을 해 본 결과는 이 보다 숫자가 적게 나왔다. 전형적인 경우에, CPU 바운드에 대해서는 10개 미만의 워크 로드(workload)와 I/O바운드에 대해서는 30개 미만의 숫자가 최적이다. 물론 하드웨어와 워크 로드에 따라서 숫자는 달라지게 된다.
MySQL 입장에서 보면 대량 접속이 어떤 문제를 가지고 있는가?
- 메모리를 허비한다. 각각의 접속은 일정 자원을 요구하며 접속이 “sleep” 단계가 되더라도
자원을 해제하지 않는다.
- OS상의 로드(Load)가 발생한다 - OS는 1000개의 쓰레드를 TCP/IP 접속을 통해서는 거의
처리하지 못한다
- 많은 수의 쓰레드가 생긴다 - CPU 캐시의 쓰래싱(thrashing)이 발생하며 이에 따른 CPU 캐시 의
허비가 발생된다.
- 많은 수의 쓰레드가 구동된다 - mutex ping-pong. 특히 Innodb 테이블에 대해서 좋지 않게
된다.
- "show processlist"를 사용하기 힘들 정도의 수 많은 프로세스가 생기며, "SHOW INNODB
STATUS"의 오류를 만들어 냄.
- Innodb는 최대 1024까지의 undo log 엔트리를 지원하기 때문에 이 보다 많은 수의 액티브
트랜젝션 innodb_thread_concurrency를 처리할 수가 없음.
업데이트 사항:
3,000~5,000개의 접속을 성공적으로 한 사람들도 있지 않은가? 라는 질문을 하기도 한다. 여기에서 중요한 것은 이것이 동작을 하지 않는다는 것이 아니고, 최적화 상태로 구동되지 않는다는 것이다.
그렇다면 실제로 여러분이 대량 접속을 하길 원하고 있다면 어떻게 해야 하는가? 여러분은 MySQL이 메모리 영역을 벗어나거나 또는 스와핑을 시작하지 못하도록 만들기 위해 쓰레드 당 버퍼를 조정해 두는 것이 좋다. 최적의 값을 찾는다는 것은 그 자체가 넘겨 집는 질문(tricky question)이며 이 방법에 대해서는 다른 컬럼에서 쓰기로 한다. 여러분은 innodb_thread_concurrency를 올바르게 사용할 수 있어야 한다.
액티브 프로세스의 숫자를 증가 시키는 프로세싱 동안의 슬로우 네트워크 요청에 대해서는 어떻게 해야 하는가? 만일 여러분이 접속 숫자를 줄이고자 한다면, 접속을 끊고 다시 접속하도록 해야 한다. 특히 여러분이 하고 있는 일에 대해서 확실히 알고 있지 않으면 연산 시간 동안 테이블을 잠근 채로 또는 트랜젝션을 열어 놓은 채로 두지 말아야 한다.
어플리케이션을 수정할 수 없다면 영구 접속의 숫자를 줄일 수가 있는가? 여러분은 대기_타임 아웃(wait_timeout)의 값을 작게 해서 MySQL로 하여금 아이들(idle)접속을 빨리 닫도록 할 수 있을 것이다. 하지만 너무 작게 설정을 해서 중간에 접속을 닫히게끔 하지는 말도록 한다.
빈번한 접속으로 인한 위험은 무엇이 있는가?
몇몇 특이한 경우를 제외하고는 잘 동작을 한다. 만일 여러분이 동일한 박스(시스템)으로부터 초 당 수백번의 접속을 갖는다면, 로컬 포트 번호가 부족하게 되는 경우가 발생할 것이다. 이런 문제를 해결하는 방법은 리눅스에서는, "/proc/sys/net/ipv4/tcp_fin_timeout"의 값을 줄이고(이것은 표준 TCP/IP에는 위반되는 것이지만 로컬 네트워크에서는 문제가 없을 것이다), _client_ 의 "/proc/sys/net/ipv4/ip_local_port_range"를 증가 시키면 된다. 다른 OS에서도 이와 유사하게 설정을 한다.
이러한 문제의 배경에는 다음과 같은 것이 있다: TCP/IP 접속은 localip:localport remoteip:remote 포트에 의해 지정된다(identified). 우리는 이런 경우에는 고정 클라이언트 IP뿐만 아니라 MySQL IP 와 포트를 가지고 있기 때문에, 우리는 확실하게 정해진 범위를 갖는 로컬 포트만을 사용할 수 있게 된다. 여러분이 접속을 닫은 후라고 하더라도 TCP/IP 스택은 일정 시간 동안 지정된 포트를 유지한다는 점을 알아두어야 하는데, 이렇게 유지 되는 시간은 tcp_fin_timeout에서 정해진다.
이번 컬럼에서는 MySQL 시스템에서 10,000개 이상의 접속이 일어날 때의 가장 좋은 처리 방법에 대해 알아보기로 한다. 이 글은 MySQL AB의 엔지니어인 Peter Zaitsev가 작성한 것을 번역한 것이다. 원문은 http://peter-zaitsev.livejournal.com/14954.html에 수록되어 있다.
우선 이러한 요구가 주로 어떤 환경에서 발생하는지를 살펴 보자. 40대의 아파치 웹 서버가 있고 각각은 200개의 클라이언트를 가지고 있다면 8000개의 영구 접속(persistent)이 이루어진다. 이럴 경우, 두 가지 질문 사항이 생긴다 - 실제로 영구 접속이 필요하며 200개의 클라이언트가 실제로 필요한가?
영구 접속은 페이지를 생성하는 쿼리가 거의 없고 데이터 베이스의 로드(load)가 응답 시간에 한정될 경우에만 성능상 잇점으로 사용될 수 있다. 현대의 대부분의 어플리케이션들은 여기에 해당되지 않는다. 이러한 이유로 PHP를 위한 “MySQLi”는 디폴트로 영구 접속을 비활성화 시켜놓았다.
자바 어플리케이션의 경우에는 이런 까닭으로 영구 접속 보다는 접속 풀링(connection pooling)을 사용하는데, 이 방식이 보다 효과적이기 때문이다.
이제 200개의 아파치 클라이언트에 대해서 생각해 보자. 왜 이렇게 많은 숫자가 필요한가? 대부분의 경우에는 이렇게 웹을 구성하는 것은 잘못된 것이다. 200개의 클라이언트 중의 90%는 응석받이(spoon feed) 슬로우 클라이언트이며, 이것들은 mod_php, mod_perl 또는 다른 것을 가지고 메모리만 점유하게 된다. 한 가지 해결책은 이러한 클라이언트 앞에 가벼운 프록시(proxy)를 설치하는 것이다. 여러분은 아파치 모듈을 이러한 목적으로 사용할 수 있을 것인데, 나는 이러한 목적으로 아파치에 비해 가벼운 lighttpd를 사용하곤 한다. 여기에서 중요한 사항은 접속 당 프로세스/쓰레드를 사용하지 않는다는 것이며 네트워크를 다룰 때에는 select() 또는 pool() 을 사용하지 않고 이벤트를 기준으로 한다는 점이다. 이렇게 하면 여러분은 보다 작은 수의 아파치 클라이언트를 가지고 박스(시스템) 당 10,000개 이상의 접속을 가질 수가 있게 된다. 다른 옵션으로는 lingerd를 사용하는 것인데, 나로서는 시도해 보지 않았다. 마지막으로 가장 기본적인 옵션으로는 빠른 -cgi 인터페이스를 사용하는 것인데, 이를 통해 여러분은 웹 서버를 보다 광범위하게 선택할 수 있게 된다.
200개의 프로세스가 필요한가? 이것은 어플리케이션에 달려 있다. 예를 들면 어플리케이션이 웹 서비스를 내부적으로 사용하거나 또는 다른 원격 네트워크를 호출할 경우라면 그럴 것이다. 이와 같은 경우에, 기대하는 성능을 얻기 위해서는 많은 수의 프로세스가 필요하게 된다. 하지만 대부분의 일반적인 경우에는 CPU 바운드(bound)안에서 업무를 처리하고 로컬의 데이터 베이스를 호출하기 때문에 이 보다 작은 수의 프로세스만으로도 충분하게 된다.
각각의 "디바이스(device)"는 동시에 처리할 수 있는 최적의(optimal) 요청 숫자를 가지고 있다. CPU의 경우에는 코어 당 하나의 쓰레드를 구동 시키는 것이 최적이다. HDD의 경우에는, 드라이브 당 적은 수의 요청만을 받아 들이지만(스케쥴링을 위해), 만일 여러분이 디스크를 많이 가지고 있다면, 랜덤 I/O와 같은 것에 의해 인터럽트가 되는 시퀀셜 스캔을 할 수가 있을 것이다.
또한 여러분은 메모리와 CPU 캐시를 계산해야 한다; 동시에 구동되는 프로세스가 많을수록 CPU 캐시와 OS 디스크 캐시는 각각에 대해 적게 할당된다. 프로세스의 최적화 숫자는 로드(load)에 달려 있는데, 만일 이것이 CPU 바운드(CPU bound)라면 보다 많은 수의 쓰레드가 구동되는 것을 볼 수 있을 것이며, I/O 바운드(I/O bound)라면 요청할 수 있는 숫자가 매우 많음을 알 수 있을 것이다.
나로서는 보통 (NumCPUs+NumDisks)*2 값을 사용한다. 어플리케이션, OS, 하드웨어 그리고 MySQL의 버그에 따라서 이 숫자는 달라질 수가 있다.
이 숫자에 대해 흥미로운 사실은 - 이 숫자가 항상 42는 아니라고 하더라도 보편적으로 42가 된다는 것이다. 만일 여러분이 “내부적으로 구동되는 쓰레드”를 살펴 본다면, 로컬 CPU에서 구동되는 쓰레드 또는 로컬 자원에서 블록된 쓰레드에 대해서 이 숫자를 사용할 수 있을 것이다 - 웹 서버, 어플리케이션 서버, 또는 데이터 베이스 서버일 경우에 해당함. 만일 프로세스가 네트워크 상에서 대기 중이라면, 이것은 로컬 자원이 아니기 때문에 이런 것들은 많이 있어도 상관이 없다.
그렇다면 얼마나 많이 필요한가? 나는 20~40개 정도가 웹/어플리케이션 서버에서 구동 시킬 수 있는 최적의 액티브 쓰레드 숫자라고 추정하면서 시작을 했다. 그런 다음에 실제로 MySQL에 적용을 해 본 결과는 이 보다 숫자가 적게 나왔다. 전형적인 경우에, CPU 바운드에 대해서는 10개 미만의 워크 로드(workload)와 I/O바운드에 대해서는 30개 미만의 숫자가 최적이다. 물론 하드웨어와 워크 로드에 따라서 숫자는 달라지게 된다.
MySQL 입장에서 보면 대량 접속이 어떤 문제를 가지고 있는가?
- 메모리를 허비한다. 각각의 접속은 일정 자원을 요구하며 접속이 “sleep” 단계가 되더라도
자원을 해제하지 않는다.
- OS상의 로드(Load)가 발생한다 - OS는 1000개의 쓰레드를 TCP/IP 접속을 통해서는 거의
처리하지 못한다
- 많은 수의 쓰레드가 생긴다 - CPU 캐시의 쓰래싱(thrashing)이 발생하며 이에 따른 CPU 캐시 의
허비가 발생된다.
- 많은 수의 쓰레드가 구동된다 - mutex ping-pong. 특히 Innodb 테이블에 대해서 좋지 않게
된다.
- "show processlist"를 사용하기 힘들 정도의 수 많은 프로세스가 생기며, "SHOW INNODB
STATUS"의 오류를 만들어 냄.
- Innodb는 최대 1024까지의 undo log 엔트리를 지원하기 때문에 이 보다 많은 수의 액티브
트랜젝션 innodb_thread_concurrency를 처리할 수가 없음.
업데이트 사항:
3,000~5,000개의 접속을 성공적으로 한 사람들도 있지 않은가? 라는 질문을 하기도 한다. 여기에서 중요한 것은 이것이 동작을 하지 않는다는 것이 아니고, 최적화 상태로 구동되지 않는다는 것이다.
그렇다면 실제로 여러분이 대량 접속을 하길 원하고 있다면 어떻게 해야 하는가? 여러분은 MySQL이 메모리 영역을 벗어나거나 또는 스와핑을 시작하지 못하도록 만들기 위해 쓰레드 당 버퍼를 조정해 두는 것이 좋다. 최적의 값을 찾는다는 것은 그 자체가 넘겨 집는 질문(tricky question)이며 이 방법에 대해서는 다른 컬럼에서 쓰기로 한다. 여러분은 innodb_thread_concurrency를 올바르게 사용할 수 있어야 한다.
액티브 프로세스의 숫자를 증가 시키는 프로세싱 동안의 슬로우 네트워크 요청에 대해서는 어떻게 해야 하는가? 만일 여러분이 접속 숫자를 줄이고자 한다면, 접속을 끊고 다시 접속하도록 해야 한다. 특히 여러분이 하고 있는 일에 대해서 확실히 알고 있지 않으면 연산 시간 동안 테이블을 잠근 채로 또는 트랜젝션을 열어 놓은 채로 두지 말아야 한다.
어플리케이션을 수정할 수 없다면 영구 접속의 숫자를 줄일 수가 있는가? 여러분은 대기_타임 아웃(wait_timeout)의 값을 작게 해서 MySQL로 하여금 아이들(idle)접속을 빨리 닫도록 할 수 있을 것이다. 하지만 너무 작게 설정을 해서 중간에 접속을 닫히게끔 하지는 말도록 한다.
빈번한 접속으로 인한 위험은 무엇이 있는가?
몇몇 특이한 경우를 제외하고는 잘 동작을 한다. 만일 여러분이 동일한 박스(시스템)으로부터 초 당 수백번의 접속을 갖는다면, 로컬 포트 번호가 부족하게 되는 경우가 발생할 것이다. 이런 문제를 해결하는 방법은 리눅스에서는, "/proc/sys/net/ipv4/tcp_fin_timeout"의 값을 줄이고(이것은 표준 TCP/IP에는 위반되는 것이지만 로컬 네트워크에서는 문제가 없을 것이다), _client_ 의 "/proc/sys/net/ipv4/ip_local_port_range"를 증가 시키면 된다. 다른 OS에서도 이와 유사하게 설정을 한다.
이러한 문제의 배경에는 다음과 같은 것이 있다: TCP/IP 접속은 localip:localport remoteip:remote 포트에 의해 지정된다(identified). 우리는 이런 경우에는 고정 클라이언트 IP뿐만 아니라 MySQL IP 와 포트를 가지고 있기 때문에, 우리는 확실하게 정해진 범위를 갖는 로컬 포트만을 사용할 수 있게 된다. 여러분이 접속을 닫은 후라고 하더라도 TCP/IP 스택은 일정 시간 동안 지정된 포트를 유지한다는 점을 알아두어야 하는데, 이렇게 유지 되는 시간은 tcp_fin_timeout에서 정해진다.
