MySQL 8 - 시스템변수정리
RealMySQL 8.0이라는 책을 학습한 내용입니다.
시스템변수, 글로벌 변수, 세션 변수
Mysql을 사용하다분명 MySQL의 서버에 필요한 설정들이 있을 것이다. 예를 들어서 커넥션 풀이나 버퍼사이즈 등을 조절할 수 있을텐데 이러한 정보들은 MySQL에서는 시스템 변수라고 칭한다.
이는 SHOW GLOBAL VARIABLES;명령어를 통해 확인할 수 있다.
1
2
3
4
5
6
7
8
SHOW GLOBAL VARIABLES;
...
| version_compile_machine | aarch64
| version_compile_os | Linux
| windowing_use_high_precision | ON
...
이 시스템 변수는 글로벌 변수 또는 세션 변수라는 속성을 하나 또는 두 개의 값을 가진다. 글로벌 변수는 말그대로 하나의 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수를 의미한다. 예를 들어서 innodb_buffer_pool이라는 버퍼 풀 크기 등이 있다.
세션 변수는 클라이언트와 연결된 커넥션(세션)에만 적용되는 시스템 변수를 칭한다. 예를 들어서 autocommit이라는 자동 커밋모드 설정이 있다. 이를 세션별로 다르게 설정해서 어떤 세션에서는 자동 커밋이 되고 어떤 세션에서는 자동 커밋이 안되게 할 수 있다.
세션 변수를 가지고 있다해서 글로벌 변수가 아니다라는 것은 아니고, 둘의 속성 모두 지닐 수 있다.
시스템 변수가 어떤 속성을 지니는지는 공식 홈페이지에 나와있다.
정적변수, 동적 변수
위의 값을 MySQL 서버 구동 상태에서 변경할 수 있냐 없냐를 가지고 정적 변수 또는 동적 변수랴고 칭한다. 디스크에 저장되어 있는 설정파일(my.cnf, my.ini)을 변경하는 경우와 이미 가동중인 MySQL 서버의 메모리에 있는 시스템 변수를 변경하는 경우로 구분할 수 있다.
디스크에 저장되어 있는 내용을 바꾸더라도 서버를 재시작하지 않으면 적용되지 않는다. 하지만 SET이라는 명령어를 통해 값을 바꿀 수 있다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> SHOW VARIABLES LIKE '%max_connections%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_connections | 151 |
| mysqlx_max_connections | 100 |
+------------------------+-------+
2 rows in set (0.03 sec)
mysql> SET GLOBAL max_connections=500;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW VARIABLES LIKE '%max_connections%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_connections | 500 |
| mysqlx_max_connections | 100 |
+------------------------+-------+
2 rows in set (0.04 sec)
위에서 사용한
GLOBAL은 시스템 변수를 칭한다. 이를 없애면 세션 변수를 칭하게 된다.
당연하겠지만 이는 디스크에 쓰는 작업이 아니기에 서버를 재시작한다해서 바뀐값으로 적용되지 않는다.
때문에 바꾼 값을 영구적으로 사욯하려면 설정파일에 추가해줘야하는데, 이 명령어를 사용해서 값을 바꾸고 사용하다보면 분명 개발자가 까먹고 설정파일에 설정추가를 누락할 가능성이 매우 크다.
이를 보완하기 위해 SET PERSIST라는 명령어가 나왔는데 이 명령어를 사용하면 실행중인 MySQL서버에 변경된 내용을 적용하고, 서버를 재시작할 떄도 바뀐값을 적용해서 재시작할 수 있다. 이는 밑에서 또 다뤄볼 예정이다.
참고로 글로벌 변수와 세션변수 특성을 둘 다 가지고 있는 변수에 대한 글로벌 값을 수정한다해서 세션이 지니고 있는 값은 바뀌지 않는다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- global
mysql> SHOW GLOBAL VARIABLES LIKE 'join_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.02 sec)
-- session
mysql> SHOW VARIABLES LIKE 'join_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.02 sec)
-- change
mysql> SET GLOBAL join_buffer_size=524288;
Query OK, 0 rows affected (0.00 sec)
-- global
mysql> SHOW GLOBAL VARIABLES LIKE 'join_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| join_buffer_size | 524288 |
+------------------+--------+
1 row in set (0.02 sec)
-- session
mysql> SHOW VARIABLES LIKE 'join_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.02 sec)
SET PERSIST
위에서 대강 다루었지만 좀 더 얘기를하자면 동적변수는 MySQL 서버 재기동없이 변수값을 바꿀 수 있지만 서버를 재기동하면 그 값이 초기화된다. 이를 해결하기 위해 SET PERSIST가 나왔고, 이 명령어를 사용하면 값을 즉시 적용할 뿐만 아니라 mysqld-auto.cnf파일에 변경 내용을 추가로 기록해둔다. 그러면 재기동할때 해당 파일도 같이 읽으면서 시스템 변수를 적용한다.
만약 MySQL서버에는 변경 내용을 적용하지 않고 다음 재시작을 위해 mysqld-auto.cnf 파일에만 기록해두고자 한다면 SET PERSIST_ONLY명령을 사용한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 500 |
+-----------------+-------+
1 row in set (0.03 sec)
mysql> SET PERSIST_ONLY max_connections=5000;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 500 |
+-----------------+-------+
1 row in set (0.00 sec)
이 명령어를 사용하는 이유는 SET PERSIST는 변수값변경 + 기록용이지만 SET PERSIST_ONLY 기록용이다. SET PERSIST를 사용하더라도 정적변수를 바꿀 수 없다. 따라서 결국 정적변수는 재시작할때 값이 변경되는데, 이런용도로 사용할 때 SET PERSIST_ONLY를 사용한다.
해당 명령어를 사용하면 mysqld-auto.cnf파일이 생성되고 해당 파일에 변경 로그가 추가된다.
1
2
bash-5.1# cat /var/lib/mysql/mysqld-auto.cnf
{"Version": 2, "mysql_dynamic_parse_early_variables": {"max_connections": {"Value": "5000", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1727795811200672}}}}
또한 sql로 performance_schema.variables_info, performance_schema.persisted_variables 를 통해 확인할 수도 있다.
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT a.variable_name, b.variable_value,
-> a.set_time, a.set_user, a.set_host
-> FROM performance_schema.variables_info a
-> INNER JOIN performance_schema.persisted_variables b
-> ON a.variable_name=b.variable_name
-> WHERE b.variable_name LIKE 'max_connections'
-> ;
+-----------------+----------------+----------------------------+----------+-----------+
| variable_name | variable_value | set_time | set_user | set_host |
+-----------------+----------------+----------------------------+----------+-----------+
| max_connections | 5000 | 2024-10-01 12:03:36.295055 | root | localhost |
+-----------------+----------------+----------------------------+----------+-----------+
1 row in set (0.02 sec)
SET PERSIST, SET PERSIST_ONLY로 적용한 내용을 삭제해야할 때가 있을 수 있다. 파일을 직접 건드는건 서버 재기동때 문제를 일으킬 확률이 너무나도 높으므로 RESET PERSIST를 통해 삭제하는걸 권장한다.
1
2
3
4
5
6
7
8
9
mysql> RESET PERSIST max_connections;
Query OK, 0 rows affected (0.01 sec)
mysql> RESET PERSIST IF EXISTS max_connections;
Query OK, 0 rows affected (0.01 sec)
# mysqld-auto.cnf 파일의 모든 시스템 변수 삭제
mysql> RESET PERSIST
Query OK, 0 rows affected (0.01 sec)