책너두 6기 4일차
백은빈, 이성욱의 Real MySQL8.0 1권 p.36 ~ p.51
4일차
02 설치와 설정
2.4 서버 설정
MySQL 서버는 일반적으로 단 하나의 설정 파일을 사용하는데 유닉스 계열은 my.cnf, 윈도우 계열에서는 my.ini를 사용한다.
mysqld --verbose --help
위 코드는 설치된 mySQL 서버가 어느 디렉토리에서 my.cnf 파일을 읽는지 궁금할 때 실행해 보면 된다. mysqld 프로그램은 MySQL서버의 실행 프로그램을 ㅗ서비스용으로 사용되는 서버에서 이미 MySQL 서버가 실행 중인데 다시 mysqld 프로그램을 시작한다거나 하지 않도록 주의해야한다.
mysql --help
따라서 위처럼 mysql 클라이언트 프로그램(mysqld 서버 프로그램이 아닌)으로 확인해보는 것이 좋다.
나는 mysql 8.0 command line client에서 위 두개를 실행해 봤는데
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘mysql –help’ at line 1
라는 오류가 발생했다. 알아보니까 오류 내용은 MySQL 서버의 SQL 쿼리 인터페이스에서 실행했기 떄문이란다. 즉, CLC를 키고 비밀번호를 입력해서 로그인 한 상태에서 실행하니 안되는 것이였다. cmd(명령 프롬프트)에서 입력해봤더니 겁나 길쭉하게 많이도 나온다.
책에 나온대로 ‘Default options are read …’ 부분을 찾아봤다.
Default options are read from the following files in the given order: C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 8.0\my.ini C:\Program Files\MySQL\MySQL Server 8.0\my.cnf
난 윈도우 계열이니 my.cnf가 아니라 my.ini다. 이 부분이 의미하는 것은 MySQL 서버가 어느 디렉토리의 my.ini 파일을 먼저 읽는지 확인 하는 것이다.
2.4.1 설정 파일의 구성
MySQL 설정 파일은 하나의 my.ini 파일에 여러 개의 설정 그룹을 담을 수 있다. 그리고 보통 실행 프로그램 이름은 그룹명을 사용한다.(mysqldump프로그램은 [mysqldump] 설정 그룹을 사용하고 참조)
일반적으로 각 그룹을 사용하는 프로그램은 성격이 다르며, 각 프로그램이 필요로 하는 설정 내용이 상이하므로 중복 설정이나 나열이 거의 없지만 socket이나 port 등은 공통으로 필요한 설정값이라 각 설정 그룹에 여러번 설정된다.
2.4.2 MySQL 시스템 변수의 특징
MySQL 서버가 가동하면서 메모리나 작동 방식을 초기화하고 접속된 사용자를 제어하기 위해 설 정파일의 내용을 별도로 저장해 두는데 이걸 시스템 변수(System Variables)라고 한다.
show variables
혹은
show global variables
로 확인할 수 있다.
시스템 변수 값이 미치는 영향에 대해 판단할려면 글로벌 변수와 세션 변수를 구분하고 무엇이며 어떤 관계인지 이해해야 한다.
시스템 변수가 가지는 5가지 속성은 다음과 같다.
- Cmd-Line : MySQL 서버의 명령행 인자로 설정될 수 있는지 여부를 나타냄
- Option file : MySQL의 설정 파일인 my.ini로 제어할 수 있는지 여부를 나타냄
- System Var : 시스템 변수인지 아닌지 나타냄
- Var Scope : 시스템 변수의 적용 범위를 나타냄. Global인지, MySQL 서버와 클라이언트 간의 커넥션(session)만인지 구분. ‘Both’ 도 있는데 아래에 설명.
- Dynamic : 시스템 변수가 동적인지 정적인지 구분하는 변수
2.4.3 글로벌 변수와 세션 변수
MySQL의 시스템 변수는 적용 범위에 따라 글로벌과 세션으로 나뉘는데 일반적으로 세션별로 적용되는 시스템 변수의 경우 글로벌 변수 뿐만아니라 세션 변수에도 동시에 존재한다. 이럴 경우 Var Scope는 ‘Both’라고 표시된다
- 글로벌 : 하나의 MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수를 의미. 보통 MySQL 서버 자체에 관련된 설정
- 세션 : MySQL 클라이언트가 MySQL 서버에 접속할 때 기본으로 부여하는 옵션의 기본값 제어에 사용. 기본적으로 부여하는 기본 값이 있으며 클라이언트의 필요에 따라 변경할 수 있음. 커넥션 별로 설정값을 다르게 지정할 수 있으며, 한번 연결된 커넥션의 세션 변수는 서버에서 강제로 변경할 수 없다.
- 세션 범위의 시스템 변수 가운데 MySQL 서버의 설정 파일(my.ini)에 명시해 초기화할 수 있는 변수 대부분은 범위가 ‘Both’라고 명시되어 있다. 이 시스템 변수들은 MySQL 서버가 기억만 하고 있다가 실제 클라이언트와의 커넥션이 생성되는 순간에 해당 커넥션의 기본값으로 사용되는 값이다. 범위가 세션으로 명시된 시스템 변수는 서버의 설정 파일에 초깃값을 명시할수 없고 커넥션이 만들어지는 순간부터 유효하다.
2.4.4 정적 변수와 동적 변수
MySQL 서버가 기동 중인 상태에서 변경 가능한지에 따라 동적 변수와 정적 변수로 구분한다. 디스크에 저장돼 있는 설정 파일(my.ini)을 변경하는 경우와 이미 기동중인 서버의 메모리에 있는 시스템 변수를 변경하는 경우로 구분되는 것이다. my.ini을 변경해도 서버 재시작 전에는 적용되지 않는다. show 명령으로 서버에 적용된 변숫값을 확인하거나 set으로 값을 바꿀 수 있다. 정확히 변수명을 모를땐 SQL 문장의 like처럼 show명령에서 % 문자를 이용해 패턴 검색할 수 있음.
MySQL 8.0 Command Line Client를 키고 접속하여
show global variables like '%max_connection%';
를 입력해보았다. 그 결과
+————————+——-+ | Variable_name | Value | +————————+——-+ | max_connections | 151 | | mysqlx_max_connections | 100 | +————————+——-+ 2 rows in set, 1 warning (0.01 sec)
라고 뜬다.
set global max_connections=500;
를 입력하니
Query OK, 0 rows affected (0.00 sec)
가 떴고 다시
show global variables like 'max_connections';
를 입력하여
+—————–+——-+ | Variable_name | Value | +—————–+——-+ | max_connections | 500 | +—————–+——-+ 1 row in set, 1 warning (0.00 sec)
위와 같이 적용된 것을 확인했다.
set명령을 통해 변경되는 시스템 변숫값이 my.ini파일에 반영되는 것은 아니기 때문에 현재 기동 중인 MySQL의 인스턴스에서만 유효하다. 재시작하면 초기화되기 떄문에 영구히 적용하려면 my.ini 파일도 변경해야 한다. set persist명령을 이용하면 현재 실행 중인 서버도 변경하고 자동으로 설정 파일로도 기록된다. show나 set명령에서 global키워드를 사용하면 글로벌 시스템 변수의 목록과 내용을 읽고 변경할 수 있으며, 이 키워드를 빼면 자동으로 세션 변수를 조회하고 변경한다.
글로벌 시스템 변수는 동적 변수라면 set 명령으로 간단히 변경할 수 있으며, 굳이 서버를 재시작해도 되지 않는다. set 명령은 my.ini 설정 파일에는 기록되지 않으며 set persist 명령을 사용해야 한다. 사용하면 변경된 시스템 변수는 my.ini가 아니라 별도의 파일에 기록된다.
범위가 ‘Both’인 경우 글로벌 시스템 변수의 값을 변경해도 이미 존재하는 커넥션의 세션 변숫값은 변경되지 않고 유지된다. join_buffer_size는 Both 타입이며 동적으로 변경 가능한데 확인해보자.
show global variables like 'join_buffer_size';
+——————+——–+ | Variable_name | Value | +——————+——–+ | join_buffer_size | 262144 | +——————+——–+ 1 row in set, 1 warning (0.00 sec)
show variables like 'join_buffer_size';
+——————+——–+ | Variable_name | Value | +——————+——–+ | join_buffer_size | 262144 | +——————+——–+ 1 row in set, 1 warning (0.01 sec)
set global join_buffer_size=524288;
Query OK, 0 rows affected (0.00 sec)
show global variables like 'join_buffer_size';
+——————+——–+ | Variable_name | Value | +——————+——–+ | join_buffer_size | 524288 | +——————+——–+ 1 row in set, 1 warning (0.00 sec)
글로벌 변수는 변했다!
show variables like 'join_buffer_size';
+——————+——–+ | Variable_name | Value | +——————+——–+ | join_buffer_size | 262144 | +——————+——–+ 1 row in set, 1 warning (0.00 sec)
세션 변수는 안 변하고 그대로다.
set명령으로 새로운 값을 설정할 때는 설정파일에서처럼 MB나 GM와 같이 단위 표기법을 사용할 수 없지만 2*1024*1024와 같은 수식은 사용할 수 있다.
2.4.5 set persist
동적 변수는 set global명령으로 변경하면 즉시 서버에 반영된다. max_connections 라는 시스템 변수는 MySQL 서버로 접속할 수 있는 최대 커넥션의 개수를 제한하는 동적 시스템 변수이다. 아까 위에서 500으로 설정했었다.
set global max_connections=5000;
Query OK, 0 rows affected (0.00 sec)
확인해보자.
show global variables like 'max_connections';
+—————–+——-+ | Variable_name | Value | +—————–+——-+ | max_connections | 5000 | +—————–+——-+ 1 row in set, 1 warning (0.00 sec)
반영 되었다.
하지만 MySQL 서버의 설정 파일에서도 이 내용을 적용해야 한다. 바쁘다 보면 잊을 수 있다. 또한 시간이 지나 서버를 재시작하면 max_connections의 예전 값으로 서버가 시작되고 문제가 반복될 것이다. 이를 위해 set persist명령을 사용한다.
잠시 원래 값으로 돌려보자.
set global max_connections=500;
Query OK, 0 rows affected (0.00 sec)
확인해보자.
show global variables like 'max_connections';
+—————–+——-+ | Variable_name | Value | +—————–+——-+ | max_connections | 500 | +—————–+——-+ 1 row in set, 1 warning (0.00 sec)
다시 500으로 되돌아왔다. 이제 set persist를 이용하여 바꿔보자.
set persist max_connections=5000;
Query OK, 0 rows affected (0.01 sec)
show global variables like 'max_connections';
+—————–+——-+ | Variable_name | Value | +—————–+——-+ | max_connections | 5000 | +—————–+——-+ 1 row in set, 1 warning (0.00 sec)
서버에 즉시 반영되었다.
이와 동시에 별도의 설정 파일(mysqld-auto.ini)에 변경 내용을 추가로 기록해두었다가 서버가 다시 시작될 때 기본 설정 파일(my.ini)과 자동 생성된 mysqld-auto.ini 파일을 같이 참조해서 시스템 변수를 적용한다.
set persist 명령은 세션 변수에는 적용되지 않는다. 이 명령을 사용하면 MySQL 서버는 자동으로 global 시스템 변수의 변경으로 인식하고 변경한다. 만약 현재 실행중인 서버는 건들지 않고 다음 재시작을 위해 mysqld-auto.ini 파일에만 기록하고자 하면 set persist_only를 사용하면 된다.
set persist_only max_connections=4000;
Query OK, 0 rows affected (0.01 sec)
show global variables like 'max_connections';
+—————–+——-+ | Variable_name | Value | +—————–+——-+ | max_connections | 5000 | +—————–+——-+ 1 row in set, 1 warning (0.00 sec)
4000으로 변경되지 않고 그대로 유지된다. 다만, 다음 서버 재시작시에는 4000으로 적용될 것이다.
이 set persist_only는 정적인 변수의 값을 영구적으로 변경할 떄도 사용할 수 있다. 정적인 변수는 실행 중인 MySQL 서버에서 변경할 수없고 서버가 재시작될 때만 변경될 수 있다. 정적 변수를 mysqld-auto.ini파일에 기록해두려면 이 명령을 활용할 수 있다.
set persist innodb_doublewrite=ON;
ERROR 1238 (HY000): Variable ‘innodb_doublewrite’ is a non persistent variable
set persist_only innodb_doublewrite=on;
ERROR 1238 (HY000): Variable ‘innodb_doublewrite’ is a non persistent variable
둘 다 안돼는데..?
MySQL Documetary를 확인해보았는데
innodb_doublewrite 라는 변수는 Dynamic System Variable이라고 되어있다.. 동적 변수였다. variable scope는 Global이였고.. 왜안되냐!
변경된 내용은 JSON 포맷의 mysqld-auto.ini 파일에 기록된다. 변경된 시스템 변수의 메타데이터는 performance_schema.variables_info뷰와 performance_schema.persistend_variables테이블을 통해 참조할 수 있다.
set persist나 set persist_only로 추가된 시스템 변수의 내용을 삭제할때는 reset persist 명령을 사용하는 것이 안전하다.
특정 시스템 변수만 삭제할 때
reset persist max_connections;
reset persist if exists max_connections;
파일의 모든 시스템 변수를 삭제
reset persist;
2.4.6 my.ini 파일
모든 시스템 변수를 공부해야하진 않지만 서버를 제대로 사용하려면 이해가 필요하긴 하다. 잘못 건드리면 오히려 성능이 떨어질 수 있다.
책에 적힌대로 설정하되, PC에서 테스트용으로 MySQL 서버를 실행할 때는 아래 설정들을 낮은 수치로 조정해서 사용하자
max_Connections=100
innodb_sort_buffer_size=5M
innodb_log_files_in_group=2
innodb_log_file_size=1024M
innodb_buffer_pool_size=200M
innodb_buffer_pool_instances=1
innodb_io_capacity=100
innodb_io_capacity_max=400
우선 서버를 다 끄고 my.ini를 바꿔야 할것 같아 cmd를 켰다.
mysqladmin -u root -p shutdown
를 입력하고 비밀번호를 입력하여 서버를 종료했다.
notepad "C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"
를 입력하니 my.ini가 메모장으로 열렸다.
책에 적힌대로 설정을 어떻게 넣어야 하나 고민하다가 하나하나 기존에 있는지, 없으면 바꿔주고 있으면 새로 넣고 해서 다 바꿨다… 어떤건 있고 어떤건 없고 후… 힘들었다.
읽고 나서
기존 책에는 정적 변수라고 되어있는데 현재는 동적 변수가 되있다던가, 작동이 안되는 변수들이 많다. 시간이 걸리더라도 왜 안되는지 찾아보다보면 도움이 되겠지..시스템 설정하느라 개고생했다.
