삶은 예배다.

서버설정

my.cnf (Linux)/ my.ini (Windows)

바이너리 프로그램 이름을 그룹명으로 사용한다.

[client], [mysql], [mysqldump], [mysql_safe], [mysqld] …

#] mysql --help

글로벌 변수와 세션 변수

동적 변수와 정적 변수

디스크에 저장돼 있는 설정파일(my.cnf, my.ini)을 변경하는 경우와

이미 가동 중인 MySQL 서버의 메모리에 있는 MySQL 서버의 시스템 변수를 변경하는 경우.

SET 명령 사용 변경 시 현재 가동 중인 MySQL의 인스턴스에만 유효. (동적 변수에 적용)

my.cnf

[mysqld]

server-id

user: MySQL 인스턴스 실행 계정. 기본은 MySQL서버를 실행한 운영체제 계정으로 MySQL 인스턴스 실행

basedir: MySQL 서버의 홈 디렉토리

datadir: MyISAM의 데이터 파일이 저장되는 디렉토리. (CSV 스토리지 엔진, Archive 스토리지 엔진)

InnoDB는 별도의 파리미터를 통해 데이터 파일 경로 지정

tmpdir: 정렬, 그룹핑 처리시 내부 임시 테이블의 데이터 파일이 저장되는 위치.

character-set-server, collation-server: MySQL 서버의 기본 문자집합을 설정.

default-storage-engine: MySQL 서버 내에서 기본적으로 사용할 스토리지 엔진을 정의.

skip-name-resolve: MySQL에 접속 가능한 클라이언트의 IP 확인 시 역DNS 검색을 하지 않는다.

GRANT .. ON .. TO ‘’@client.name.com에 IP를 사용해야 한다.

event-scheculer: MySQL5.1부터 일정 시간에 반복되는 작업을 목적으로 이벤트 스케쥴러 기능 사용. (ON/OFF)

sysdate-is-now: sysdate()와 now() 함수 관련

back_log: 많은 클라이언트가 MySQL 서버로 접속을 시도하면 MySQL 서버의 인증을 거칠 때까지 기다리게 되는데

이때 몇 개까지의 커넥션을 큐에 담아 둘지 결정하는 설정. 중요하지 않음.

max-connections: MySQL 서버가 최대한 허용할 수 있는 클라이언트의 연결 수를 제한하는 설정.

값을 늘릴수록 MySQL가 응답 불능 상태로 빠질 가능성이 높아지며, 이 설정값을 낮출수록 MySQL가

응답할 수 없게 될 확률이 줄어든다. 동적으로 변경할 수 있으므로 부족하면 그때 변경해도 된다.

thread_cache_size: MySQL 서버에서 스레드와 커넥션은 거의 같은 의미로 사용되곤 하지만 사실 커넥션은

           클라이언트와 서버와의 연결 그 자체를 의미하며, 스레드는 해당 커넥션으로부터 오는 작업 요청을

           처리하는 주체다. MySQL는 스레드를 풀에서 관리한다. 스레드 풀에서 관리할 스레드 개수를 결정.

            mysql> SET GLOBAL thread_cache_size = 8;

wait-timeout: MySQL에 연결된 클라이언트가 wait-timeout에 지정된 시간 동안 아무런 요청 없이 대기하는 경우

           MySQL는 해당 커넥션을 강제 종료한다.

max_allowed_packet: MySQL서버로 전달되는 패킷의 크기를 설정. BLOB 또는 TEXT 타입의 데이터를 저장해야 하는

           경우에 주로 발생.

max_heap_table_size: 메모리(Memory) 스토리지 엔진을 사용한 메모리 테이블은 힙(HEAP) 테이블이라고도 한다.

           사용자 생성 Meory 테이블과 MySQL가 내부적으로 생성한 임시 테이블도 포함된다.

tmp_table_size: 임시 테이블의 최대 크기를 제어하는 설정 값. 메모리에 저장되는 임시 테이블만을 제어한다.

           사용자가 CREATE TABLE .. ENGINE=MEMORY의 테이블은 임시 테이블이 아니므로 tmp_table_size

           설정과는 무관하다.

sort_buffer_size: DBMS에서 가장 큰 부하를 일으키는 요청이 정렬(정렬, 그룹핑)작업인데, sort_buffer_size는 인덱스를

           사용할 수 없는 정렬에 메모리 공간을 얼마나 할당할지 결정하는 설정값.

           가장 적절한 설정값은 64KB~512KB.

join_buffer_size: 적절한 조인 조건이 없어서 드리븐 테이블의 검색이 풀 테이블 스캔으로 유되되는 경우에 사용되는

           버퍼. – Using join buffer 실행 계획. 128KB~512KB로 설정.

read_buffer_size: 정체를 명확히 하기 어려운 버퍼 중 하나. 128KB로 설정했을 때 가장 빠른 성능을 확인.

read_rnd_buffer_size: MySQL에서 인덱스를 사용해 정렬할 수 없을 경우 정렬 대상 데이터의 크기에 따라

Single-pass 또는 Two-pass 알고리즘 중 하나를 사용한다.

정렬을 위해서 읽어야 할 데이터 레코드를 버퍼링하는데 필요한 것이 read_rnd_buffer이며, 이 버퍼의

크기를 결정하는 것이 read_rnd_buffer_size이다.

정렬해야 할 대상 레코드가 크기 않으면 Single-pass 알고리즘으로 정렬되기 때문에 read_rnd_buffer가

사용되지 않는다. 64KB~128KB 수준으로 설정.

soft_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size: 4개의 버퍼 메모리는 SESSION 범위의 변수

           이므로 MySQL 서버는 커넥션(세션)별로 설정된 크기의 메모리 공간을 각각 할당한다.

           세션 단위로 할당되는 메모리 버퍼는 절대 불필요하게 크게 설정하지 않아야 한다.

           특정 커넥션에서만 대량의 레코드를 배치 형태로 처리해야 한다면 해당 세션에서만 버퍼 값을 변경하여

          사용.

query_cache_size, query_cache_limit: 쿼리 캐시에 관련된 캐시의 크기를 설정하는 값. 128MB가 적절. 읽기 전용인

           경우 조금씩 늘려가면서 성능 확인 후 확장. 메모리가 적은 경우 64M 설정.

group_concat_max_len: GROUP_CONCAT() 함수 사용시 사용되는 버퍼 크기.

transaction-isolation: 트랜잭션의 격리 수준을 결정하는 설정 값. 기본 값은 REPEATABLE-READ.

           REAPEATABLE-READ, SERIALIZABLE, REPEATABLE-UNCOMMITTED, READ-COMMITTED 값 사용.

plugin-load: MySQL5.1부터는 스토리지 엔진에 플러그인 개념 도입.

           MySQL5.5부터는 InnoDB 스토리지 엔진이 디폴트 스토리지 엔진으로 채택.

ignore_builtin_innodb(MySQL5.1에서만 사용): builtin InnoDB엔진과 plugin InnoDB엔진을 함께 포함하고 있는데

           둘 중에서 하나를 활성화

Innodb_buffer_pool_size: InnoDB 스토리지 엔진의 버퍼 풀은 디스크의 데이터를 메모리에 캐싱함과 동시에 데이터의

           변경을 버퍼링하는 역할을 수행한다. 일반적으로 innodb_buffer_pool_size는 운영체제나 MySQL클라이언트에

           대한 서버 스레드가 사용할 메모리를 제외하고 남는 거의 모든 메모리 공간을 설정.

           세션 단위의 메모리와 커넥션의 개수로 클라이언트를 위한 서버 스레드의 최대 메모리 사용량을 예측.

           Innodb_buffer_pool은 정적 변수. 적용 시 MySQL 재 시작 필요.

Innodb_additional_mem_pool_size: MySQL는 자체적으로 각 테이블의 메타 정보를 메모리에 관리하지만, InnoDB

           스토리지 엔진도 자체적으로 각 테이블의 메타 정보나 통계 정보를 내부적으로 별도로 관리.

           이러한 메타 정보나 통계 정보가 저장되는 공간의 크기를 설정. 16MB~32MB 설정.

Innodb_file_per_table: InnoDB 스토리지 엔진을 사용하는 테이블은 “*.ibd”라는 확장자로 생성되는데, 오라클의

           테이블 스페이스라는 개념을 사용. 오라클처럼 모든 테이블을 하나의 테이블 스페이스에 모아서 생성하는

           방법도 가능하며, 테이블 단위로 테이블 스페이스를 할당하는 방법도 가능하다.

-       1: 테이블 단위로 각각 1개씩 데이터 파일과 테이블 스페이스를 생성해 데이터 저장

           테이블 삭제 시 해당 공간이 반납 – 주로 사용

-       0: 하나의 테이블 스페이스에 모든 테이블의 데이터를 저장. 테이블 삭제 시 테이블 스페이스가

점유하던 공간을 운영체제로 반납하지 않는다.

Innodb_data_home_dir: InnoDB 스토리지 엔진을 사용하는 테이블에 대한 데이터 파일이 저장될 위치를 설정하는

           옵션. 일반적으로 MySQL의 기본 데이터 저장 디렉토리와 동일하게 설정해서 사용.

Innodb_data_file_path: InnoDB에서 데이터는 시스템 데이터와 사용자 데이터로 나눌 수 있다.

           시스템 데이터는 사용자가 생성한 각 테이블에 대한 메타 정보나 트랜잭션을 위한 Undo와 같이 InnoDB

           스토리지 엔진이 임의적으로 만들어낸 것을 의미.

           사용자 데이터는 SQL로 생성하고 변경하는 테이블의 데이터를 의미.

           시스템 데이터는 항상 시스템 테이블 스페이스에 저장되며, 시스템 테이블 스페이스는

innodb_data_file_path에 명시된 파일에 생성된다.

Innodb_log_group_home_dir: 트랜잭션을 지원하는 RDBMS는 ACID 보장과 동시에 성능 향상을 목적으로 데이터

           변경 이력을 별도의 파일에 순차적으로 기록해 두는데, 이를 트랜잭션로그 또는 Redo로그라고 한다.

           이러한 InnoDB로 해당 파일의 저장 경로를 설정하는 값.

Innodb_log_buffer_size: InnoDB 스토리지 엔진에서 데이터가 변경될 때 해당 변경사항을 바로 Redo로그에 기록하면

           디스크의 입출력 요청이 빈번해져서 효율성이 떨어진다. 메모리에 일시적으로 로그를 버퍼링하게 되는데,

           이때 사용할 버퍼의 크기를 설정하는 옵션. 세션 단위가 아니기 때문에 MySQL에서 하나의 메모리 버퍼만

           생성된다. 16~32MB로 설정.

Innodb_log_file_size, innodb_log_files_in_group: InnoDB의 Redo로그 파일은 1개 이상의 파일로 구성되며 순환되면서

           사용된다. innodb_log_file_size는 Redo 로그 파일 하나의 크기이고, innodb_log_files_in_group은 이러한

           파일을 몇 개나 사용할지 설정하는 값.

           이 값이 너무 작게 설정되면 InnoDB의 버퍼 풀로 설정된 메모리 공간이 아무리 많아도 제대로 활용하지

           못하게 되며 InnoDB 전체를 비효율적으로 작동하게 한다.

           InnoDB의 버퍼 풀이 10GB이상이면 Redo로그 파일의 개수에 관계없이 전체 공간이 2GB~4GB로 설정.

           버퍼 풀이 10GB이하라면 Redo로그 파일의 전체 크기는 2GB이하로 설정.

Innodb_lock_wait_timeout: InnoDB에서 잠금 획득을 위해 최대 대기할 수 있는 시간을 설정.

           MySQL5.1이상에서는 언제든지 SET 명령으로 변경가능.

Innodb_flush_log_at_trx_commit: InnoDB에서 트랜잭션이 commit될 때마다 Redo로그를 디스크에 flush할지 결정하는

           옵션. 디스크의 데이터를 동기화하지 않으면 유사시에 사용자가 commit한 데이터가 손실되거나 손상될

           가능성이 있다. 트랜잭션 보장을 위해서는 반드시 commit할 때 디스크의 데이터 동기화가 필요하다.

           상당히 많은 양의 데이터를 처리하지만 조금 손실돼도 무방한 경우라면 이 값을 0으로 설정해서

           디스크 입출력의 성능을 개선할 수 있다.

Innodb_flush_method: 모든 운영체제는 디스크에 데이터를 쓰는 작업은 “운영체제의 버퍼로 기록”하는 작업과

           “버퍼의 내용을 디스크로 복사”하는 2단계로 나눈다.

-       두 단계의 작업을 동시에 실행하는 방식을 동기(Sync) IO라고 하며, 각각 다른 시점에 실행하는 방식을

           비동기(Async) IOfkrh 한다.

-       데이터가 변경되면 그와 동시에 파일의 변경 일시와 같은 메타 정보도 함께 변경하게 되는데, 이렇게

           데이터와 메타 데이터를 한꺼번에 변경하는 방식을 fsync라고 하며, 파일의 메타 정보는 무시하고 순수하게

           사용자의 데이터만 변경하는 방식을 fdatasync라고 한다.

-       디스크 쓰기의 두 단계 작업 중에서 “운영체제의 버퍼 기록” 단계를 생략하고 바로 사용자의 데이터를

           디스크에 쓰는 경우도 있는데, 이를 특별히 다이렉트(direct) IO라고 한다.

           Innodb_flush_method는 InnoDB 스토리지 엔진이 로그 파일과 데이터 파일을 어떤 방식으로 디스크에

           기록하고 동기화할지 결정한다.

           디스크로부터 읽혀지는 데이터는 운영체제의 캐시에 담아두고 다시 읽기 요청 발생시 캐시의 내용을

           반환하는 형태로 성능을 개선하게 되지만, InnoDB는 운영체제의 캐시보다 더 체계적이고 효율적인 캐시

           (InnoDB 버퍼 풀)를 사용하기 때문에 운영체제의 캐시가 별로 도움이 되지 않는다.

           Innodb_flush_method를 O_DIRECT로 설정하면 운영체제의 불필요한 캐시를 막고 캐시를 위한 메모리

           낭비도 제거할 수 있다.

Innodb_old_blocks_time:

Key_buffer_size: MyISAM 스토리지 엔진에서 가장 중요한 설정 값. InnoDB의 버퍼 풀은 인덱스와 모든 데이터

           페이지에 대해 캐시와 버퍼의 역할을 동시에 수행하지만, MyISAM의 키 버퍼는 주로 인덱스에 대해서만

           캐시 역할을 한다. Key_buffer_size는 인덱스만 캐시하기 때문에 InnoDB의 버퍼 풀만큼 크게 메모리를

           할당하지 않는다. 일반적으로 전체 메모리의 30%~50% 정도로 설정.

           MyISAM 테이블도 사용하고 InnoDB 테이블도 사용하다면 innodb_buffer_pool과 key_buffer_size를 위한

           메모리 공간을 적절히 나눠서 설정.

General_log, general_log_file: MySQL에는 DBMS 서버에서 실행되는 모든 쿼리를 로그 파일로 기록하는 기능이 있다.

           이 로그를 쿼리 로그 또는 General 로그라고 한다.

Slow_query_log, long_query_time, slow_query_log_file: MySQL에서는 지정된 시간 이상으로 쿼리가 실행되는 경우

           해당 쿼리를 별도의 로그 파일로 남긴다. 이러한 쿼리를 slow query라고 한다. Slow_query_log는 슬로우

           쿼리로그의 활성화 상태를 설정. Long_query_time는 초 단위 값을 설정하고, 설정된 시간 이상 소요되는

           쿼리는 모두 slow_query_log_file에 설정된 로그 파일로 기록한다.

Log_slow_admin_statements: ALTER TABLE… 등과 같이 DDL 문장의 슬로우 쿼리 로그 기록 여부를 설정.

           이 설정을 활성화하면 언제 어떤 DDL 문장이 실행됐는지 슬로우 쿼리 로그 파일을 통해 확인 가능.

Log-bin, max_binlog_size, expire_logs_days: MySQL에서 복제를 구축하려면 반드시 바이너리 로그를 사용해야 한다.

           바이너리 로그는 마스터 MySQL 서버에서만 기록하며, 슬레이브 MySQL 서버는 기록된 바이너리 로그를

           가져와서 재실행하는 형태로 마스터와 슬레이브 간의 데이터를 동기화한다.

           Log-bin 옵션에는 바이너리 로그의 파일명을 설정한다. Max_binlog_size는 최대 바이너리 로그 파일의

           크기를 제한하고, expire_logs_days는 쓸모 없는 오래된 바이너리 로그의 보관 날짜를 결정.

Binolg_cache_size: 바이너리 로그의 내용도 즉시 디스크에 기록하는 것이 아니라 메모리의 임시 공간에 잠깐

           버퍼링 했다가 디스크로 기록한다. Binlog_cache_size는 이 버퍼링용 메모리의 크기를 설정하는 변수.

           56KB~256KB 설정. Binlog_cache_size도 커넥션 별로 생성.

Log_bin_trust_function_creators: 바이너리 로그가 활성화된 MySQL에서 스토어드 함수를 생성하는 경우와 관련된

           내용.

Sync_binlog: MySQL에서 성능 문제가 발생하는 두 요소는 InnoDB Redo 로그의 동기화와 바이너리 로그의 동기화

           이다. 그 중에서도 바이너리 로그의 동기화는 상당한 부하를 만든다.

-       1: 트랜잭션이 commit 될 때마다 바이너리 로그를 디스크에 flush 한다.

바이너리 로그의 손실은 줄어들지만 마스터 MySQL의 성능은 떨어진다.

-       0: 디스크에 기록은 하지만 MySQL서버가 flush를 하지 않기 때문에 운영체제의 버퍼까지만 기록하고

           즉시 처리 완료한다. MySQL에서 flush를 하지 않으면 운영체제에서 4~5초 간격으로 데이터를 flush한다.

           MySQL의 성능은 상당히 좋아지지만 마스터 MySQL서버가 다운되거나 장애발생시 바이너리 로그가

           손실되어 마스터와 슬레이브의 데이터가 달라질 가능성이 커진다.

-       0이나 1이 아닌 양의 수를 설정하면 그 수만큼의 트랜잭션을 모아 바이너리 로그를 디스크에 flush한다.

Relay_log, relay_log_purge: 마스터 MySQL에서 바이너리 로그를 생성한하면 슬레이브 MySQL에서는 마스터의

           바이너리 로그를 읽어와서 릴레이 로그라는 파일을 생성한다.

           Relay_log_pure 옵션을 TRUE 또는 1로 설정하면 필요하지 않은 오래된 릴레이 로그를 자동 삭제한다.

Log-slave-updates: MySQL 서버 복제 구성에서 하나의 MySQL 서버가 슬레이브이면서 동시에 마스터가 될 수 있다.

           이러한 경우에 다른 마스터로부터 바이너리 로그를 가져와서 재실행되는 쿼리가 자신의 바이너리 로그에

           기록되게 할지 여부를 결정하는 옵션.

Read_only: MySQL 복제가 사용된 경우, 데이터의 변경은 마스터 MySQL에서 실행하고 단순히 조회만 하는

트랜잭션은 슬레이브 장비에서 실행하게 한다. 복제가 구성된 상태에서 슬레이브의 데이터가 마스터와

관계없이 변경되면 서로 충돌할 수 있기 때문에 슬레이브 MySQL 서버는 읽기 전용으로 만든다.

이럴 때 사용하는 변수. 동적 변수라서 SET 명령으로 설정 가능.

[client]

mysql이나 mysqldump 등과 같이 MySQL 클라이언트 프로그램이 공통적으로 사용하는 옵션을 기록

[mysql]

MySQL 클라이언트를 사용하는 경우 유용한 설정 정보

No-auto-rehash:

Show-warnings: 경고가 발생했을 때 경고 메시지를 출력

           Show warnings; 명령으로 확인하는 내용

Prompt: MySQL 클라이언트 사용시 사용하고 있는 DB, 실행 시간 등을 확인할 수 있는 프롬프트 설정.

Pager: 서버로부터 전달되는 결과를 페이징해서 확인할 수 있다.