개발 공부 기록하기/02. DB & SQL
내맘대로 정리하는 Real MySQL #2장) 설치와 설정
lannstark
2020. 8. 5. 19:43
<내맘대로 정리하는> 시리즈는, 책을 읽으며 몰랐던 내용을 위주로 정리한 내용 그대로 포스팅하는 시리즈입니다 ^^
원문의 문맥이 궁금하면 (좋은 책이니) 이 참에 하나 장만하는 것은 어떤가요??
설치에 대해서는 정리하지 않는다.
MySQL 서버는 단 하나의 설정 파일만 사용한다. 유닉스 계열에서는 my.cnf 라는 이름을 사용하고 윈도우 계열에서는 my.ini라는 이름을 쓴다. MySQL 서버는 시작될 때만 이 설정 파일을 참조하며, 처음 발견된 my.cnf 파일을 사용하게 된다.
mysqld --verbose --help 혹은 mysql --help를 통해 설정 파일을 읽어들이는 경로 순서를 알 수 있다.
- mysqld : 서버 프로그램
- mysql : 클라이언트 프로그램
MySQL 서버는 가동하면서 설정 파일의 내용을 읽어 메모리나 작동 방식을 초기화하고, 접속된 사용자를 제어하기 위해 이러한 값을 별도로 저장해 둔다. 이런 값을 변수 (정확히는 시스템 변수) 라고 한다. SHOW VARIABLES 명령으로 확인할 수 있다.
각 변수가 글로벌 변수인지 세션 변수인지 구분할 수 있어야 한다.
- 글로벌 변수 : 시스템 변수가 영향을 미치는 곳이 서버 전체인 경우
- 세션 변수 : 시스템 변수가 영향을 미치는 곳이 서버-클라이언트 간의 커넥션인 경우. 각 클라이언트가 처음에 접속하면 기본적으로 부여하는 디폴트 값을 가지고 있으며, 클라이언트 필요에 따라 개별 커넥션 단위로 다른 값을 변경할 수 있다. 한번 연결된 커넥션 세션 변수는 서버에서 강제로 변경할 수 없다.
- 변수 설정 가능 범위가 Both인 경우는 설정파일에 세션 변수 기본 값을 설정해두고, 클라이언트 연결시 설정할 수 있으며, Session인 경우는 클라이언트가 연결할때만 설정할 수 있다.
가동 중인 상태에서 변경이 가능하면 동적변수, 불가능하면 정적변수이다.
설정 옵션들 (빠진 옵션이 있을 수 있다)
# mySQL이 내부적으로 자기 자신을 식별하는 값 (Master / Slave 구성에서 자신을 식별하는데 사용)
server-id = 1
# 운영체제에서 mysql 서버가 사용하는 계정
user = mysql
port = 3306
# 서버의 홈 dir. 다른 설정에 적힌 상대경로 및 파일을 찾는 기준 dir이다
basedir = /usr/local/mysql
# MyISAM의 데이터 파일이 저장되는 디렉토리
datadir = /usr/local/mysql/data
# MySQL 필요에 의해 생성하는 내부 테이블 임시 저장용 dir
tmpdir = /usr/local/mysql/tmp
socket = /usr/local/mysql/tmp/mysql.sock
# 서버의 기본 charset 및 collation
character-set-server = utf8
collation-server = utf8_general_ci
# 서버의 기본 storage engine (필요에 의한 임시테이블은 MyISAM으로 고정이다)
default-storage-engine = InnoDB
skip-external-locking
# MySQL 스케쥴러를 사용하려면 아래 event-scheduler 옵션을 ON으로 변경
event-scheduler = OFF
# sysdate()을 now()와 동일하게 동작시키는 옵션
sysdate-is-now
# 다수의 클라이언트가 동시에 인증 요청을 할 때 쌓아두는 Queue size
back_log = 100
# 서버가 허용할 수 있는 최대 클라이언트 수
max_connectiosn = 300
max_connect_errors = 999999
# 최대 몇 개의 thread를 thread pool에서 저장할 것인가
thread_cache_size = 50
thread_open_cahce = 400
# 서버에 연결된 connection에 wait timeout 만큼 아무런 요청없이 대기하는 경우 커넥션을 강제로 종료
wait_timeout = 28800
# 클라이언트 -> 서버의 쿼리길이보다 큰 값이 필요
max_allowed_packet = 32M
# 임시테이블 및 스토리지 엔진이 Moemory인 테이블들이 사용하는 메모리 크기
max_heap_table_size = 32M
# 메모리에 생성되는 임시 테이블의 최대값을 제어하는 값
tmp_table_size = 512K
####################################
# 4가지 buffer_size는 세션 범위 변수이다 #
####################################
# 인덱스를 사용할 수 없는 정렬에 메모리를 얼마나 할당할지 결정하는 값
sort_buffer_size = 128K
# Using Buffer Join을 쓸 때의 메모리 할당량
join_buffer_size = 128K
# Table Full Scan이 발생하는 경우 사용하는 버퍼 (스토리지엔진마다 다른 사용처가 존재한 애매한 옵션)
read_buffer_size = 128K
# Two-pass 알고리즘 사용이후 데이터를 읽을때 사용할 버퍼의 크기
read_rnd_buffer_size = 128K
# 쿼리 캐시에 대한 캐시 크기를 설정하는 값
query_cahce_size = 32M
query_cache_limit = 2M
# GROUP.CONCAT() 함수 결과의 최대 길이
group_concat_max_len = 1024
# 트랜잭션 격리수준 설정
transaction-isolation = REPEATABLE-READ
# LRU 리스트 -> MRU 리스트 대기시간 (ms)
innodb_old_blocks_time = 500
# (5.1버전 한정) built-in 버전의 InnoDB 무시
ignore_builtin_innodb
# 디스크의 데이터를 메모리에 캐싱함과 동시에 데이터의 변경을 버퍼링
innodb_buffer_pool_size = 10G
# InnoDB에서 관리하는 Table meta data를 위한 메모리 크기
innodb_additional_mem_pool_size = 16M
# 테이블마다 테이블 스페이스가 생기도록 하고 싶으면 1
innodb_file_per_table = 1
# InnoDB 테이블에 대한 데이터 파일 저장 위치
innodb_data_home_dir
# innodb_data_home_dir이 0일때 의미가 존재하는 옵션
innodb_data_file_path = ib_system:100M:autoextend
innodb_autoextend_increment = 100
# redo 로그 위치
innodb_log_group_home_dir = /usr/local/mysql/data
# 로그 출력을 위한 버퍼
innodb_log_buffer_size = 16M
# redo 로그 파일 크기 - 중요 설정
innodb_log_file_size = 1024M
# redo 로그 개수 - 중요 설정
innodb_log_files_in_group
innodb_support_xa = OFF
# LOCK을 위해 대기할 수 있는 max 시간
innodb_lock_wait_timeout = 60
# trx가 commit 될 때 disk에 flush를 바로 할 것인가
innodb_flush_log_at_trx_commit = 1
# disk에 data를 쓰는 방식 결정 (unix에서만 설정 가능)
innodb_flush_method = 0_DIRECT
# MyISAM 엔진 buffer size
key_buffer_size = 32M
# 실행된 모든 쿼리 기록 여부
general_log = 0
general_log_file = /uar/local/mysql/logs/general_query.log
# slow query 기록 여부
log_slow_admin_statements # slow DDL도 기록할 것인가
slow-query-log = 1
long_query_time = 1
slow_query_log_file = /uar/local/mysql/logs/slow_query.log
# bin 로그 prefix
log-bin = /uar/local/mysql/logs/binary_log
# binary 로그 기록시 사용되는 버퍼
binlog_cache_size = 128K
# binlog flush 여부 결정
sync_binlog = 1
# 마스터의 bin 로그를 가져와 슬레이브가 만드는 relay 로그
relay-log = /usr/local/mysql/logs/relay_log
relay_log_purge = TRUE
read_only- MySQL 서버가 내부적으로 생성하는 임시 테이블은 MyISAM 스토리지 엔진만 사용한다.
- 커넥션은 클라이언트와 서버와의 연결 그 자체를 의미하며, 스레드는 해당 커넥션으로부터 오는 작업 요청을 처리하는 주체이다.
- KeepAlive란 네트워크를 통해 만들어진 TCP 커넥션을 계속 유효한 상태로 유지하는 것을 의미한다.
- 일반적인 DBMS에서 가장 큰 부하를 일으키는 사용자 요청이 정렬(순수한 정렬이나 그룹핑 작업으로 인한 정렬)이다.
- innoDB에서 데이터는 크게 시스템 데이터와 사용자 데이터로 나눌 수 있다. 시스템 데이터는 사용자가 생성한 각 테이블에 대한 메타정보나 트랜잭션을 위한 Undo와 같이 InnoDB 스토리지 엔진이 임의적으로 만들어낸 것을 의미하며, 사용자 데이터는 일반적으로 SQL로 생성하고 변경하는 테이블의 데이터를 의미한다.
- Redo로그 : 트랜잭션을 지원하는 RDBMS에서 ACID 보장과 동시에 성능 향상을 목적으로 데이터의 변경 이력을 순차적으로 기록해도는 별도의 파일
- 대부분의 RDBMS에서 병목은 디스크이며, 디스크의 병목을 일으키는 주 원인이 빈번한 fsync()나 fdatasync() 시스템 콜이다.
- 어떤 OS에서든 디스크에 데이터를 쓰는 작업은 '운영체제의 버퍼로 기록'하는 작업과 '버퍼의 내용을 디스크로 복사'하는 2단계로 나뉜다.
- 동기 IO : 두 단계의 작업을 동시에 같이 실행하는 방식
- 비동기 IO : 1단계와 2단계의 작업을 각각 다른 시점에 실행하는 방식
- fsync : 파일과 파일의 메타데이터를 한 번에 변경하는 방식
- fdatasync : 파일의 메타 정보는 무시하고 순수하게 사용자의 데이터만 변경하는 방식
- direct IO : OS의 버퍼 기록 단계를 생략하고 바로 사용자의 data를 disk로 쓰는 경우
- MySQL에서 읽기 부하는 주로 인덱스나 데이터 파일에서 발생하며, 쓰기 부하는 binary 로그와 redo 로그에서 발생한다.
- Slave는 마스터의 바이너리 로그를 읽어와서 relay 로그를 생성한다.
- MySQL에서는 메모리 관련 설정이 중요하다. 스토리지 엔진별로 주요 메모리 공간이 공유되지 않기 때문에 사용하지 않는 스토리지 엔진에 맞게 메모리 사용을 제한하는 것이 중요하다.
MySQL에 존재하는 기본 DB
- information_schema : MySQL 서버에 존재하는 오브젝트(테이블, 칼럼, stored procedure) 의 정보를 담고 있는 메타 정보 테이블이 저장된 db
- mysql : information_schema와 중복된 정보도 조금 있지만 가장 중요한 보안과 관련된 내용이 주로 저장되어 있다.
복제 구성
- 슬레이브 MySQL이 마스터 MySQL 서버에 접속해 로그인하기 위해서는 복제용 계정이 필요하다.
전문 검색
전문검색이란 문자열 데이터에서 원하는 검색어가 포함돼 있는지 검색하고 검색어와 일치하는 결과를 가져오는 것을 말한다. 일반적으로 B-Tree나 해시 인덱스는 단어 형태나 문장 형태의 문자열 데이터를 전체 일치 또는 전방 일치 형태로 검색하는 방식에 적합한 구조이다.