MySQL 정적 변수와 동적 변수

1. 개념

MySQL DB 서버의 시스템 변수는 MySQL이 가동 중인 상태에서 변경이 가능한지 여부에 따라 동적 변수 및 정적 변수로 나뉜다. MySQL DB의 파라미터 값(변수)은 디스크에 저장된 My.cnf, my.ini 파일을 변경하는 경우와 가동 중인 MySQL DB의 메모리에 있는 MySQL DB 시스템 파라미터를 변경하는 경우로 구분 가능하다. 디스크에 저장된 설정 파일의 설정이 변경되더라도 MySQL DB 서버를 재시작하기 전에는 적용되지 않는다. 하지만 SHOW 명령어로 MySQL DB 서버에 적용된 변숫값을 확인(LIKE 검색처럼 %를 사용해서 검색 가능), SET 명령어로 변숫값을 변경할 수도 있다. 

예) SHOW GLOBAL VARIABLES 'wait_timeout';

      SHOW GLOBAL VARIABLES '%timeout%';

 

      SET GLOBAL wait_timeout='300';

 

SET 명령어를 통해 변경된 변숫값은 my.cnf, my.ini 파일에 반영되는 것은 아니므로 현재 가동 중인 MySQL DB의 인스턴스에서만 유효된다. MySQL DB를 재시작하면 다시 설정 파일의 내용으로 초기화되기 때문에 설정을 영구적으로 적용시키려면 my.cnf 파일의 내용도 변경해야 한다. MySQL8.0 버전부터는 SET PERSIST 명령어를 이용해서 가동 중인 MySQL 서버의 시스템 변수를 변경하고 자동으로 설정 파일로도 기록되게 할 수 있다.

 

상식적으로 글로벌 시스템 변숫값은 MySQL DB가 가동 중일 때는 변경이 불가한 것이 맞지만 실시간으로 변경이 가능한 사항도 있다. my.cnf 설정 파일을 변경할 때 MySQL 서버를 재시작하는 경우가 많은데,  변경하고자 하는 값이 동적 변수라면 SET 명령어로 간단하게 변숫값을 변경할 수 있다. (MySQL DB 재시작 불필요) SET PERSIST 명령어를 사용하는 경우 변경된 시스템 변수는 my.cnf 파일이 아닌 별도의 파일에 기록된다.

 

시스템 변수의 범위가 'Both'인 경우(글로벌이면서 세션 변수)에는 글로벌 시스템 변수의 값을 변경해도 이미 존재하는 커넥션의 세션 변숫값은 변경되지 않고 그대로 유지된다. 

예) SHOW GLOBAL VARIABLES LIKE 'join_buffer_size';

      SHOW VARIABLES LIKE 'join_buffer_size';

      SET GLOBAL join_buffer_size=524288;

      SHOW GLOBAL VARIABLES LIKE 'join_buffer_size';

      SHOW VARIABLES LIKE 'join_buffer_size';

 

위 명령어를 순차적으로 실행 시, join_buffer_size라는 글로벌 변숫값은 524288로 변경됐지만, 현재 커넥션의 세션 변수는 과거 값 262144를 그대로 유지하고 있다. MySQL DB의 변수 가운데 동적인 변수만 이렇게 SET 명령어를 이용해서 변경 가능한다. SET 명령으로 새로운 값을 설정할 때는 설정 파일에서처럼 MB나 GB같이 단위 표기법을 사용할 수 없지만 2*1024*1024 같은 수직 사용은 가능하다.

 

2. SET PERSIST

앞서 설명한 것처럼 동적 변수는 MySQL DB에서 SET GLOBAL 명령어로 변경하면 즉시 반영이 된다. 하지만 이런 식으로 변경할 시 문제가 발생할 수 있다. MySQL DB의 설정 파일에 내용을 변경한 후 관리자가 잊어버릴 때가 있다. 한 참 후에 MySQL DB 서버를 재시작하면 다시 예정의 설정값으로 재시작이 되고 장애가 발생한다.

MySQL8.0 버전은 이런 문제를 보완하기 위해 SET PERSIST 명령어를 도입했다.

SET PERSIST는 변수를 변경하면 MySQL DB는 변경된 값을 즉시 적용하고 별도의 설정 파일인 mysqld-auto.cnf에 변경 내용을 기록한다. 만약 MySQL DB가 재시작됐을 때 기본 설정 파일인 my.cnf에서 뿐만 아니라, mysqld-auto.cnf 파일을 같이 참조해서 변수를 적용하는 원리이다. 

예) SET PERSIST_ONLY wait_timeout=500;

      SHOW GLOBAL VARIABLES 'wait_timeout';

 

SET PERSIST 명령은 세션 변수에는 적용되지 않는다. SET PERSIST 명령으로 시스템 변수를 변경하면 서버는 자동으로 GLOBAL 시스템 변수의 변경으로 인식하고 변경하므로 주의해야 한다. 현재 가동 중인 MySQL 변수 설정값을 적용하지 않고 다음 재시작 때 mysqld-auto.cnf 파일에만 변경 내용을 기록하고자 한다면 SET PERSIST_ONLY 명령을 사용하면 된다.

SET PERSIST_ONLY 명령어는 정적인 변숫값을 영구적으로 변경할 때 사용할 수도 있다. SET PERSIST 명령은 현재 실행 중인 MySQL DB의 동적 변숫값을 변경하고 mysqld-auto.cnf 파일에도 기록하는데, 정적인 변수는 가동 중인 서버에서는 변경이 불가능하다. 정적인 변수를 mysqld-auto.cnf 파일에 기록하고자 할 때 SET PERSIST_ONLY 명령어를 유용하게 사용할 수 있다.

 

*참고: SET PERSIST, SET PERSIST_ONLY 명령어로 변수를 변경하면 JSON 포맷으로 mysqld-auto.cnf 파일이 생성된다. mysqld-auto.cnf 파일에 변경된 시스템 변수의 이름과 설정값, 변경자, 날짜 등의 정보도 기록된다.

SET PERSIST, SET PERSIST_ONLT 명령으로 변경된 변수의 메타데이터는 다음과 같이 performance_schema.vatiables_info 뷰와 performance_schema.persisted_variables 테이블을 통해 확인할 수도 있다.