02. MySQL 시스템 변수
MySQL 시스템 변수
MySQL 서버는 기동하면서 설정 파일의 내용을 읽어 메모리나 작동 방식을 초기화하고, 접속된 사용자를 제어하기 위해 이러한 값을 별도로 저장해 둡니다.
MySQL 서버에서는 이렇게 저장된 값을 변수(Variable) 혹은 시스템 변수라고 합니다.
시스템변수의 값은 MySQL에 접속하여 아래의 명령어로 확인이 가능합니다.
글로벌 변수와 세션변수
MySQL의 시스템 변수는 적용 범위에 따라 글로벌 변수와 세션 변수로 나뉩니다.
세션 범위의 시스템 변수 가운데 MySQL 서버의 설정 파일(my.cnf 또는 my.ini) 에 명시해 초기화 할 수 있는 변수는 대부분 범위가 Both 라고 병시되어 있습니다. 이 변수는 MySQL 서버가 기억만 하고 있다가 클라이언트와 커넥션 생성되는 순간에 기본값으로 사용됩니다.
동적 변수와 정적 변수
MySQL 서버는 서버가 기동중인 상태에서 변수의 변경 가능 여부에 따라 동적 변수와 정적 변수로 구분됩니다. 디스크에 저장된 경우는 MySQL 서버가 재시작 전에는 적용되지 않지만, 메모리에 있는 설정값중 set 명령어를 통해 값을 바꾸는 경우는 현재 기동중인 MySQL의 인스턴스에서만 유효합니다.
만약 변수명을 정확히 모른다면 SQL 문장의 like 구문처럼 "%" 문자의 패턴을 이용한 검색이 가능합니다.
mysql > SHOW GLOBAL variables LIKE 'max_connections';
최초 설정시 대소문자 구분이 기본값이므로 입력시 대소문자에 주의토록 한다.
mysql > SET GLOBAL max_connections=300;
설정 파일의 위치는 /usr/local/mysql/support-files 하위에 있는 my-XXXX.cnf 파일을 참조하여 작성할 수 있습니다.
- my-huge.cnf MySQL 전용으로 메모리가 1-2G 일 경우
- my-large.cnf MySQL 전용으로 메모리가 512 이상일 경우
- my-medium.cnf MySQL 전용으로 메모리가 32-64M 정도일 경우나 메모리가 128 이상이면서 다른 데몬과 같이 서비스 될 경우
- my-small.cnf MySQL 전용으로 메모리가 64M 이하일 경우
콘솔을 열고 mysql --help 을 출력해보면 아래와 같이 실제 MySQL 서버가 설정 파일을 찾는 위치를 확인 해볼 수 있습니다.
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
대부분의 경우 /etc/ 밑에 my.cnf 를 위치하고(윈도우의 경우 my.ini) 그곳에 설정 정보를 읽어들이게 됩니다.
아래는 기본적인 설정 그룹별 설명입니다. (참조 : http://bluebreeze.co.kr/m/post/view/id/608)
[mysqld] # MySQL 서버 기본 옵션 datadir = /data/mysql socket = /var/lib/mysql/mysql.sock user=mysql init_connect=SET collation_connection = utf8_general_ci init_connect=SET NAMES utf8 character-set-server = utf8 collation-server = utf8_general_ci big-tables # 기본 엔진설정 default-storage-engine = InnoDB skip-host-cache # 역DNS 검색 비활성 skip-name-resolve # 외부(TCP/IP) 잠금 비활성 skip-external-locking max_connections = 500 table_cache = 256 wait_timeout = 50 ## General 로그를 사용하려면 아래 설정은 그대로 유지하고 ## MySQL 서버에 로그인한 후 “SET GLOBALgeneral_log=1″ 명령으로 활성화 # 0=제네럴로그 비활성 general_log = 1 # 제네럴로그 파일경로 general_log_file = /var/log/mysql/general_query.log ## MySQL 에서는 지정된 시간 이상으로 쿼리가실행되는경우 해당 쿼리를 별도의 로그 파일로 남길수있다.(슬로우 쿼리 로그) # DDL쿼리도 슬로우 쿼리에 기록 log_slow_admin_statements # 슬로우 쿼리로그 활성화 slow-query-log = 1 # 이 변수값보다 쿼리처리가 길게 걸린다면 에러로그에 기록 long_query_time = 1 # 슬로우 쿼리 로그파일 경로 slow_query_log_file = /var/log/mysql/slow_query.log ## MySQL 스케줄러를 사용하려면 아래 event-scheduler 옵션을 ON으로 변경 # event-scheduler = OFF # 이벤트 비활성 event-scheduler = ON # 함수 sysdate()와 now() 동일하게 처리 sysdate-is-now # 동시접속시 대기시킬수있는 커넥션 갯수 back_log = 100 # 최대 클라이언트 연결 갯수 max_connections = 300 max_connect_errors = 999999 # 다쓴 쓰레드를 스레드풀에 저장할 갯수 thread_cache_size = 50 # 각 쓰레드별 오픈할 테이블수 table_open_cache = 400 # 커넥션 최대 대기시간(초) wait_timeout = 28800 # 요청된 쿼리의 최대길이의 값 max_allowed_packet = 32M # MEMORY 테이블의 최대크기 max_heap_table_size = 32M # 메모리 내의 임시테이블 크기 초과시 디스크에 저장 tmp_table_size = 512K # 정렬에 필요한 버퍼의 크기 ORDER BY 또는 GROUP BY 연산 속도와 관련 # sort_buffer_size = 128K # 조인이 테이블을 풀스캔 하기위해 사용하는 버퍼크기 # join_buffer_size = 128K # 테이블 스캔에 필요한 버퍼크기 # read_buffer_size = 128K # 디스크 검색을 피하기위한 랜덤 읽기 버퍼크기 # read_rnd_buffer_size = 128K # 정렬에 필요한 버퍼의 크기 ORDER BY 또는 GROUP BY 연산 속도와 관련 sort_buffer_size = 512K # 조인이 테이블을 풀스캔 하기위해 사용하는 버퍼크기 join_buffer_size = 6K # 테이블 스캔에 필요한 버퍼크기 read_buffer_size = 64K # 디스크 검색을 피하기위한 랜덤 읽기 버퍼크기 read_rnd_buffer_size = 256K # 쿼리 결과를 캐싱라기 위해 할당된 메모리 크기 query_cache_size = 32M # 이 변수 값보다 큰 값은 캐싱이 안됨 query_cache_limit = 2M # GROUP_CONCAT()함수 사용시 컬럼값 최대크기 group_concat_max_len = 1024 # 쓰레스 갯수 thread_concurrency = 2 ## 마스터 MySQL 서버에서 “레코드 기반 복제”를 사용할 대는 READ-COMMIT 사용 가능 ## 복제에 참여하지 않는 MySQL 서버에서는 READ-COMMIT 사용 가능 ## 그 외에는 반드시 REPEATABLE-READ로 사용 transaction-isolation = REPEATABLE-READ # isolation 레벨 [READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE] # InnoDB 관련 옵션 # 테이블 단위로 테이블스페이스 할당, 활성시 확장자 .ibd 파일이 생성됨 innodb_file_per_table = 1 # innodb 홈디렉터리 경로 innodb_data_home_dir = /data/mysql # 파일명 : 초기용량 : 자동증가 : 최대사이즈 innodb_data_file_path = ibdata1:256M:autoextend #테이블 스페이스 자동 확장시 크기 innodb_autoextend_increment = 100 innodb_log_group_home_dir = /data/mysql # 로그 디렉터리 정보 innodb_log_arch_dir = /data/mysql # 데이터와 인덱스를 캐시하기 위해 사용하는 메모리 버퍼크기 innodb_buffer_pool_size = 10G # innodb에서 사용할 메모리 양으로 전체 메모리의 50~80% 정도로 설정 # 데이터 디렉토리 정보와 내부 데이타 구조를 저장하는 메모리 풀의 크기 innodb_additional_mem_pool_size = 16M # Redo 로그 버퍼크기 innodb_log_buffer_size = 16M # 로그 버퍼 사이즈로 성능에 맞춰 로그를 기록하는 경우 크게 설정 # 로그 파일 사이즈로 버퍼풀 사이즈의 25% 정도로 설정 innodb_log_file_size = 64M # 커밋 로그 옵션으로 성능 최적화로 1분마다 저장되도록 2로 설정 # 1=트랜젝션 실행할때마다 로그 파일에 기록되고 디스크 플러시가 실행 innodb_flush_log_at_trx_commit = 2 # 트렌젝션 two-phase commit 지원, 디스크 플러시 횟수를 줄여 성능항상 innodb_support_xa = OFF # InooDB내에 쓰레드 갯수, 변수 0은 쓰레드간 동시성 비활성화 # innodb_thread_concurrency = 0 # 롤백이 진행되기 전에 LOCK을 대기하는 시간(초) innodb_lock_wait_timeout = 20 # 크래시 복구 모드 설정 innodb_force_recovery = 0 # 성능을 위해 메모리에서 직접 액세스 하도록 설정 innodb_flush_method = O_DSYNC innodb_purge_threads = 1 innodb-read-io-threads = 2 innodb-write-io-threads = 2 innodb_thread_concurrency = 6 innodb-buffer-pool-instance = 3 # O_DIRECT=운영체제의 버퍼를 사용 않고 IO 실행, RAID 컨트롤러(캐시메모리 장착된)가 없거나 SAN 사용시 O_DIRECT를 사용 하지 않음 # 이중 쓰기 버퍼 비활성 innodb_doublewrite = 0 # 쓰레드가 지연되기 전에 (suspended) 풀어 주기 위해 InnoDB 뮤텍스 (mutex)를 기다리는 쓰레드의 대기 시간 innodb_sync_spin_loops = 20 # LOCK TABLES은 AUTOCOMMIT=0경우에, InnoDB로 하여금 내부적으로 테이블을 잠금 innodb_table_locks = 1 # InnoDB 큐를 조이닝 (joining)하기 전에 InnoDB 쓰레드가 일시 정지 (sleep)하는 시간 innodb_thread_sleep_delay = 1000 # 퍼지 연산 (purge operation)이 래깅 (lagging)될 때 INSERT, UPDATE 및 DELETE 연산을 지연 시키는 방법을 제어, # 디폴트값 0일시 지연 없음 innodb_max_purge_lag = 0 # 동시에 실행되는 쓰레드의 숫자. 이 값이 0이 되면 동시성 제어 (concurrency control)가 비활성화 innodb_commit_concurrency = 0 # InnoDB에 동시에 들어갈 수 있는 쓰레드의 숫자는 innodb_thread_concurrency 변수로 알아볼 수가 있다. # 여러 개의 쓰레드가 이미 컨커런시 한계에 도달하였다면, 하나의 쓰레드만이 큐에 들어갈 수 있다. # 하나의 쓰레드가 InnoDB에 들어가게 되면, innodb_concurrency_tickets의 값과 일치하는 “자유 티켓”의 숫자가 주어지고 # 쓰레드가 자신의 티켓을 사용하기 전 까지는 자유롭게 InnoDB에 들어가고 나올 수가 있다. # 이런 후에는, 쓰레드는 다시금 일관성 검사를 하고 InnoDB에 다시 들어가려고 시도하게 된다 innodb_concurrency_tickets = 500 [mysql] # MySQL 설정그룹 default-character-set=utf8 # 경고 발생시 메세지 자동 출력 show-warnings # SQL 프롬프트 설정 prompt=u@h:d_R:m:s> # 데이터 출력시 페이징처리 # pager=”less -n -i -F -X -E” # 자동 완성 기능 비활성화 # no-auto-rehash # 데이터 변경 또는 삭제시 where구문 필수입력 # safe-updates [myisam] # MyISAM 옵션 설정 기본 그룹 ## InnoDB를 사용하지 않고 MyISAM만 사용한다면 key_buffer_size를 4GB까지 설정 # 인텍스 캐시 크기 key_buffer_size = 32M # 대량 삽입에 사용된 트리 캐시의 크기, 쓰레드당 각각 할당됨 bulk_insert_buffer_size = 32M # 인덱스 정렬시 할당되는 버퍼의 크기 myisam_sort_buffer_size = 1M # 인덱스 재 생성시 사용할 임시 파일의 최대 크기 myisam_max_sort_file_size = 2G # 정렬 복구시 사용될 쓰레드 갯수 myisam_repair_threads = 1 # MyISAM 테이블을 열었을시 테이블 자동복구 myisam_recover # 하나의 FULLTEXT 인덱스에 포함 되는 단어의 최소 길이 값, ft_min_word_len = 3 [mysqld_safe] # 에러로그에 경고메세지 기록 log-warnings = 1 # 에러로그 파일경로 log-error=/var/log/mysqld.log # 프로세스 ID 파일경로 pid-file=/var/run/mysqld/mysqld.pid