[Mysql/MariaDB] DB 시스템 변수 확인(show variable)

1. DB 시스템 변수 확인

[형식]

# SHOW VARIABLES
SHOW [GLOBAL | SESSION] VARIABLES
    [LIKE 'pattern' | WHERE expr]

-----------------------------------------------

# SELECT
SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES;

SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES;

 

MySQL Command-Line Client 접속하여 확인

# mysql -u root -p'계정 비밀번호';

mysql> SHOW VARIABLES;
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_log_checksums | ON    |
| performance_schema   | OFF   |
| ...                  | ...   |
+----------------------+-------+

mysql> SHOW VARIABLES LIKE 'max_connect%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 500   |
+--------------------+-------+

 

MySQL Command-Line Client 접속 없이 확인

# mysql -u root -p'계정 비밀번호' -e "SHOW VARIABLES;"
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_log_checksums | ON    |
| performance_schema   | OFF   |
| ...                  | ...   |
+----------------------+-------+

# mysql -u root -p'계정 비밀번호' -e "SHOW VARIABLES LIKE 'max_connect%';"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 500   |
+--------------------+-------+

 

[GLOBAL 변수와 SESSION 변수]

• 영향을 미치는 범위
• GLOBAL 변수 : MySQL 서버에서 단 하나의 값을 가지며, 시스템에 영향을 미치는 변수입니다.
• SESSION 변수 : 각 세션에만 적용되는 값으로 커넥션 단위로 값을 변경할 수 있습니다.
• GLOBAL, SESSION 둘 다 해당하는 변수도 존재합니다.

 

[Static변수와 Dynamic변수]

• 값을 변경할 때 서버를 재시작해야 하는지 여부에 따른 분류
• Static 변수 : 서버를 재시작해야지만 값이 변경됩니다.
• Dynamic 변수 : 서버를 재시작하지 않고도 값을 변경할 수 있습니다.

 

2. 주요 시스템 변수

[주의 사항]

변수의 범위가 GLOBAL일 경우 SHOW STATUS 쿼리를 사용할 경우 자동으로 SHOW GLOBAL STATUS 적용됩니다.

그러나 변수의 범위가 GLOBAL, SESSION 둘 다 일 경우 SHOW SESSION STATUS 적용됩니다.

 

더 많은 다양한 서버 상태 변수는 아래의 링크에 접속하면 설명되어있습니다.

MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables

 

• autocommit
  
  • GLOBAL Variable, SESSION Variable, Dynamic Variable 
  • Auto commit 여부

 

• basedir
  • GLOBAL Variable, Static Variable
  • MariaDB 설치 디렉터리의 경로

 

• datadir
  • GLOBAL Variable, Static Variable
  • 데이터가 저장된 디렉토리

 

• character_set_server
  • GLOBAL Variable, SESSION Variable, Dynamic Variable
  • 서버에서 사용하는 기본 문자 집합

 

• character_set_system
  • GLOBAL Variable, Static Variable
  • 서버가 식별자를 저장하기 위해 사용하는 문자 세트

 

• character_set_database
  • GLOBAL Variable, SESSION Variable, Dynamic Variable
  • 기본 데이터베이스에서 사용하는 문자 집합

 

• connect_timeout
  • GLOBAL Variable, Dynamic Variable
  • mysqld 서버가 응답하기 전에 연결 패킷을 기다리는 초 

 

• general_log
  • GLOBAL Variable, Dynamic Variable
  • 쿼리 로그 사용 유무(기본 값 OFF)

 

• general_log_file
  • GLOBAL Variable, Dynamic Variable
  • 쿼리 로그 파일 경로 및 파일 명 설정

 

• hostname
  • GLOBAL Variable, Static Variable
  • 서버 호스트 이름

 

• init_file
  • GLOBAL Variable, Static Variable
  • DB 서버 시작 시 실행할 SQL 쿼리 파일

 

• interactive_timeout
  • GLOBAL Variable, SESSION Variable, Dynamic Variable
  • 활동 중인 커넥션이 닫히기 전까지 서버가 대기하는 시간
  • mysql> 과 같은 콘솔이나 터미널 모드(대화형 클라이언트)에서 mysqld와 client가 연결을 맺은 다음 요청을 기다리는 최대 시간

 

• wait_timeout
  • GLOBAL Variable, SESSION Variable, Dynamic Variable
  • 종료 전까지 요청이 없이 기다리는 시간 ( TCP/IP 연결, Shell 상의 접속이 아닌 경우 ) 활동하지 않는 커넥션을 끊을 때까지 서버가 대기하는 시간
  • API를 이용한 client 프로그램(PHP, JDBC, ODBC...) 상에서 최대 연결 시간을 말하며, 설정된 시간 동안 아무 요청이 없으면 연결은 취소되고 다시 요청이 들어오면 자동으로 연결이 맺어집니다. 

 

• license
  • GLOBAL Variable, Static Variable
  • 서버 라이센스

 

• log_error
  • GLOBAL Variable, Static Variable
  • 에러 로그 경로 및 파일 명

 

• port
  • GLOBAL Variable, Static Variable
  • 서버가 TCP/IP 연결을 수신 대기하는 포트

 

• read_only
  • GLOBAL Variable, Dynamic Variable
  • read_only 시스템 변수가 활성화된 경우 서버는 CONNECTION_ADMIN 권한이 있는 사용자(또는 사용되지 않는 SUPER 권한)를 제외한 클라이언트 업데이트를 허용하지 않습니다.

 

• socket
  • GLOBAL Variable, Static Variable
  • mysql.sock 경로 (소켓 파일 경로)

 

• time_zone
  • GLOBAL Variable, SESSION Variable, Dynamic Variable 

 

Connection 튜닝 관련 시스템 변수

• max_connections
  • GLOBAL Variable, Dynamic Variable
  • 최대 동시 클라이언트 연결 수

 

• wait_timeout
  • GLOBAL Variable, SESSION Variable, Dynamic Variable
  • 종료 전까지 요청이 없이 기다리는 시간 ( TCP/IP 연결, Shell 상의 접속이 아닌 경우 ) 활동하지 않는 커넥션을 끊을 때까지 서버가 대기하는 시간
  • API를 이용한 client 프로그램(PHP, JDBC, ODBC...) 상에서 최대 연결 시간을 말하며, 설정된 시간 동안 아무 요청이 없으면 연결은 취소되고 다시 요청이 들어오면 자동으로 연결이 맺어집니다. 

 

• thread_cache_size
  • GLOBAL Variable, Dynamic Variable
  • thread 재 사용을 위한 Thread Cache 수로써, Cache에 있는 Thread 수보다 접속이 많으면 새롭게 Thread를 생성한다.

 

Memory 튜닝 관련 시스템 변수

• key_buffer_size
  • GLOBAL Variable, Dynamic Variable
  • 인덱스를 메모리에 저장하는 버퍼의 크기(인덱스 블록에 사용되는 버퍼의 크기)

 

• table_open_cache
  • GLOBAL Variable, Dynamic Variable
  • 전체 Thread가 사용할 오픈 가능한 최대 테이블 수

 

• myisam_sort_buffer_size
  • GLOBAL Variable, SESSION Variable, Dynamic Variable
  • 테이블 repair, Alter table, load data에 사용되는 버퍼 메모리 크기
  • 복구 테이블 동안 인덱스를 정렬하거나 인덱스 만들기 또는 테이블 변경으로 인덱스를 만들 때 할당되는 버퍼의 크기

 

• join_buffer_size
  • GLOBAL Variable, SESSION Variable, Dynamic Variable
  • 조인을 위한 메모리 버퍼 크기

 

• sort_buffer_size
  • GLOBAL Variable, SESSION Variable, Dynamic Variable
  • 정렬(order by와 group by)에 사용되는 메모리 버퍼 크기

 

• tmp_table_size
  • GLOBAL Variable, SESSION Variable, Dynamic Variable
  • 스토리지 엔진에서 만든 내부 메모리 임시 테이블의 최대 크기
  • group by시 디스크를 사용하지 않고 임시 테이블을 만들기 위해 사용되는 메모리 크기

 

• key_cache_block_size
  • GLOBAL Variable, Dynamic Variable
  • 키 캐시의 블록 바이트의 크기(bytes, 기본값 1024)

 

3. 시스템 변수 변경 

1. 명령어

• Dynamic Variable를 변경할 수 있고 DB를 재시작하지 않아도 변경할 수 있습니다.
• DB 재시작할 경우 설정 값이 원래 값으로 복구됩니다.
• 임시 적용

# 형식
set 시스템 변수 이름 = 변경 값; // 세션으로 적용 됨
set global 시스템 변수 이름 = 변경 값;
set session 시스템 변수 이름 = 변경 값;

# Ex)
set global wait_timeout = 200;
set session wait_timeout = 200;

# mysql -u root -p

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 120   |
+---------------+-------+

mysql> set global wait_timeout = 250;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 250   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> exit

// 재시작 할 경우 유지가 되지 않습니다.
# systemctl restart mysql

# mysql -u root -p

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 120   |
+---------------+-------+
1 row in set (0.00 sec)

 

2. 설정 파일

• Static Variable를 변경할 수 있고 DB를 재시작해야 합니다.
• DB 재시작하더라도 값이 유지됩니다.
• 영구 적용

[설정 파일 경로]

mysql - /etc/my.cnf

mariadb - /etc/my.cnf.d/server.cnf

# vi /etc/my.cnf

// 파일 맨 밑에 작성 후 저장
wait_timeout=250

# systemctl restart mysql

# mysql -u root -p

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 250   |
+---------------+-------+
1 row in set (0.00 sec)