MySQL/MariaDB Connection 관련 설정 변수

 

  • Version : Mariadb 5.5.4.2-WinX64

 

MySQL/MariaDB 서버에서 커넥션 관련 설정 변수와 현재 설정되어 있는 값을 확인하고 이 항목이 어떤 의미를 뜻하지는지 알아본다.

 

설정 가능한 변수 목록은 show variables 명령을 사용하여 확인 할 수 있다.

show variables;

 

  • max_connections : MySQL 서버가 최대한 허용할 수 있는 클라이언트의 연결 수를 제한하는 설정이다. max_connection 값을 수천 수만으로 늘릴수록 MySQL 서버가 응답 불능 상태로 빠질 가능성이 높아지며 이 설정값을 낮출수록 MySQL 서버가 응답할 수 없게 될 확률이 줄어든다. 이 설정은 동적으로 변경할 수 있으므로 커넥션이 부족하다면 그때 변경해주면 된다.
  • thread_cache_size : 클라이언트와 서버와의 연결 그 자체를 의미하며 스레드는 해당 커넥션으로부터 오는 작업 요청을 처리하는 주체다. 최초 클라이언트로부터 접속 요청이 오면 MySQL 서버는 스레드를 준비해 그 커넥션에 작업 요청을 처리해 줄 스레드를 매핑하는 형태이다. 클라이언트가 종료되면 MySQL은 스레드를 스레드풀에 보관한다. Thread_cache_size 설정 변수는 최대 몇 개까지의 스레드를 스레드 풀에 보관할지 결정한다.
  • wait_timeout : MySQL 서버에 연결된 클라이언트가 wait_timeout에 지정된 시간 동안 아무런 요청 없이 대기하는 경우 MySQL 서버는 해당 커넥션을 강제로 종료해버린다. 이 설정값의 시간 단위는 초이며 기본값은 28800초(8시간)이다.

 

 

현재 설정되어 있는 상태 값은 show status 명렁어를 사용하여 확인할 수 있다.

show status like '%connect%';

 

  • Aborted_connects : MySQL 서버에 접속이 실패된 수
  • Max_used_connections : 최대로 동시에 접속한 수
  • Threads_connected : 현재 연결된 Thread 수

 

show status like '%clients%';

 

  • Aborted_clients : 클라이언트 프로그램이 비 정상적으로 종료된 수

 

show status like '%thread%';

 

  • Threads_cached : Threads Cache의 Thread 수
  • Threads_connected : 현재 연결된 Thread 수
  • Threads_created : 접속을 위해 생성된 Thread 수
  • Threads_running : sleeping 되어 있지 않은 Thread 수

 

 

커넥션을 모니터링 할 때 위에서 확인한 상태 값을 계산하여 튜닝 여부를 판단할 수 있다.

  • Cache Miss Rate(%) = Threads_created / Connections * 100
  • Connection Miss Rate(%) = Aborted_connects / Connections * 100
  • Connection Usage(%) = Threads_connected / max_connections * 100

 

Connection Usage(%)가 100%라면 max_connections 수를 증가시켜야 한다. Connection 수가 부족할 경우 Too Many Connection Error가 발생한다.

 

DB서버의 접속이 많은 경우 wait_timeout을 최대한 적게 설정하여 불필요한 연결을 빨리 정리하는 것이 좋다. 그러나 Connection Miss Rate(%)가 1% 이상이면 wait_timeout을 좀 더 길게 설정한다.

 

MySQL 서버는 외부로부터 접속 요청을 받을 경우 인증을 위해 IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속 시에 불필요한 부하가 발생한다. skip-name-resolve를 설정하고 접속시 IP기반으로 접속을 하게 되면 hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속할 수 있다.

 

[참고자료]

RealMySQL (위키북스)

 

2015-05-26 / 강성욱 / http://sqlmvp.kr

 

 

MariaDB, MySQL, MySQL Connection, MariaDB Connection, MySQL 글로벌 세션, MariaDB 글로벌 세션, 커넥션 관리, MySQL 커넥션 설정, MariaDB 커넥션 설정, MySQL 커넥션 튜닝, MairaDB 커넥션 튜닝

MySQL/MariaDB Memory 관련 설정 변수

 

  • Version : Mariadb 5.5.4.2-WinX64

 

MySQL/MariaDB 서버에서는 메모리 관련된 설정이 중요하다. MySQL에서 스토리지 엔진별로 주요 메모리 공간이 공유되지 않기 때문에 사용하는 스토리지 엔진에 맞게 메모리 사용을 제한하는 것이 중요하다.

 

여기에서는 중요한 변수 몇 가지만 소개한다. 자세한 내용은 공식 매뉴얼을 참고한다.

 

설정 가능한 변수 목록은 show variables 명령을 사용하여 확인 할 수 있다.

show variables;

 

 

  • innodb_buffer_pool_size : 디스크에서 데이터를 메모리에 캐싱함과 동시에 데이터의 변경을 버퍼링하는 역할을 수행한다. 일반적으로 전체 메모리의 50% ~ 80%까지 설정하며 낮은 값부터 조금씩 크기를 올려가며 적절한 값을 찾는 것이 것이 좋다. 정적 변수이기 때문에 설정 적용을 위해서는 MySQL 재시작이 필요하다.
  • join_buffer_size : 조인이 발생할 때마다 사용되는 버퍼가 아니다. 적절한 조인 조건이 없어서 드리븐 테이블의 검색이 풀 테이블 스캔으로 유도되는 경우 사용 된다.
  • key_buffer_size : MyISAM의 키 버퍼는 인덱스를 메모리에 저장하는 버퍼의 크기이다. 인덱스만 캐시하기 때문에 InnoDB의 버퍼 풀만큼 할당해서는 안된다. 일반 적으로 전체 메모리의 30~50% 할당하는 것이 좋다.
  • read_buffer_size : MySQL 매뉴얼에서는 풀 테이블 스캔이 발생하는 경우 사용하는 버퍼라고 설명하고 있지만 많은 스토리지 엔진에서 다른 용도로 사용하기도 하기 때문에 명확히 정의하기 어렵다.
  • read_rnd_buffer_size : 인덱스를 사용해 정렬할 수 없을 경우 정렬된 데이터를 메모리에 저장하여 디스크를 다시 한번 읽지 않도록 버퍼링한다. 이때 버퍼의 크기를 결정하는 역할을 한다.
  • sort_buffer_size : 인덱스를 사용할 수 없는 정렬에 메모리 공간을 얼마나 할당할지 결정하는 설정값이다. 이 크기가 너무 작으면 디스크 사용확률이 높아지고 높아지면 클라이언트 스레드가 사용하는 메모리의 양이 커져 메모리 낭비가 심해진다. 이 메모리 공간이 크다고 해서 무조건 정렬이 빨리 끝나는 것은 아니다.
  • tmp_table_size : 메모리에 생성되는 임시 테이블의 최대 크기를 설정한다.
  • Query_cache_size, query_cache_limit : 쿼리 캐시에 관련된 캐시의 크기를 설정한다. 쿼리 캐시는 무조건 크게 설정하는 것이 항상 좋은 것은 아니다.
  • key_cache_block_size – block 크기이며 기본값 1024(단위 byte) 이다.
  • myisam_sort_buffer_size : Repair table, Alter table, CREATE INDEX에 사용되는 버퍼 메모리 크기이다. 최대크기는 4GB이다.

 

[참고자료]

RealMySQL (위키북스)

 

2015-05-21 / 강성욱 / http://sqlmvp.kr

 

MariaDB, MySQL, MySQL 메모리, MariaDB 메모리, MySQL 글로벌 세션, MariaDB 글로벌 세션, 메모리 관리, MySQL 설정, MariaDB 설정, MySQL메모리 설정

+ Recent posts